Live-Forum - Die aktuellen Beiträge
Datum
Titel
10.05.2024 22:28:14
Formel Suche nur genauen Wert ausgeben
10.05.2024 19:12:28
Suche Formel für Monatsberechnung
10.05.2024 18:20:03
Status/Typ-Werte automatisch setzen in Abhängigkeit
10.05.2024 16:34:03
Excel auf Smartphone: Vorsicht beim Wischen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1828to1832
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1828to1832
1828to1832
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel Ermittlung lfdNr je Vertrag

Formel Ermittlung lfdNr je Vertrag
27.04.2021 13:16:46
Markus
Hallo zusammen,
ich habe eine Frage, die mich schon seit einiger Zeit quält, und wofür ich keine befriedigende Lösung finde.
Ich verwalte in einer Excel-Tabelle einige Mobilfunkverträge. Diese Tabelle ist wie folgt aufgebaut:
Spalte A : Name = Name der Person
Spalte B : Handynummer = Handynummer der Person
Spalte C: LfdNr = eine laufende Nummer je Person - die höchste Zahl je Person ist der derzeit aktuelle Vertrag
Spalte D: Aktuell = hier steht ein x, wenn es sich um den derzeit aktuellen Vertrag handelt
Spalte E: Vertragsbeginn = selbsterklärend
Spalte F: Vertragsende = selbsterklärend
Die Formel für die Spalte C2 ist =WENN(A2="";"";WENN(A2=A1;C1+1;1))
Die Formel für die Spalte E2 ist =WENN(A3=[@Name];"";"x")
Das Problem ist nun, dass bei dieser Art die Formeln nur dann richtig funktionieren, wenn die Sortier-Reihenfolge der Tabelle wie folgt aufgebaut ist:
Zuerst Sortieren nach Name (aufsteigend) - dann sortieren nach LfdNr (aufsteigend)
Sobald ich die Sortierung z.B nach Nummer mache, dann haut es mir die Spalte lfdNr und die Spalte "aktuell" um die Ohren. Wie bekomme ich das in Griff?
https://www.herber.de/bbs/user/145815.xlsx

30
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
2 Schritte
27.04.2021 13:29:38
lupo1
1) Spalte doppeln
2) Gedoppelte Spalte mit ihren Werten überschreiben und dann danach sortieren
Dieses bei Änderungen immer wiederholen.
AW: 2 Schritte
27.04.2021 13:46:15
Markus
Das wäre zwar möglich, ist aber leider nicht akzeptabel
1) das könntest Du auch automatisieren
27.04.2021 13:49:30
lupo1
... mit einem Change-Ereignis.
2) Mit xl365 könntest Du per Formel sortieren. Dann blieben in-sich-bezogene Berechnungen in der Quelle unangetastet.
AW: mit 2 Formeln ...
27.04.2021 13:34:45
neopa
Hallo Markus,
... z.B. so:
In C2: =ZÄHLENWENNS([Name];[@Name];[Vertragsbeginn];"&lt="&[@Vertragsbeginn])
und in D2: =WENN(ZÄHLENWENNS([Name];[@Name];[Vertragsbeginn];"&lt="&[@Vertragsbeginn])=AGGREGAT(14;6;[LfdNr]/([Name]=[@Name]);1);"x";"")
Gruß Werner
.. , - ...
Anzeige
Falls das mit exponentiellem Aufwand rechnet, ...
27.04.2021 13:37:21
lupo1
... ist das keine gute Empfehlung.
(Hinweis: Ich habe die Datei nicht geöffnet)
www.decisionmodels.com/optspeedg.htm
AW: Falls das mit exponentiellem Aufwand rechnet, ...
27.04.2021 13:49:33
Markus
Hallo Lupo1,
bitte entschuldige, aber das verstehe ich nicht. Was meinst du mit "rechnen mit exponentiellem Aufwand"
Gruß,
Markus
Ganz einfach
27.04.2021 13:54:43
lupo1
Wird ein Saldo B3: =B2+A3 gebildet, gibt es bei n Salden n*2 Berechnungen.
Verwendest Du jedoch über die n Einträge in A:A jeweils eine Komplettanalyse mit ZÄHLENWENN, dann landest Du bei n^2 Berechnungen.
AW: spielt bei der vorh. max Menge keine Rolle owT
27.04.2021 13:57:13
neopa
Gruß Werner
.. , - ...
Foren sind zur Erziehung da.
27.04.2021 14:00:31
lupo1
ZÄHLENWENN ist eine Auswertungsfunktion. Wenn Du die in (bzw. an!) die Daten "ballerst", begehst Du EDV-technisch Basis-Sünden.
Anzeige
AW: Theorie und Praxis ...
27.04.2021 14:34:42
neopa
Hallo lupo,
... vorab ich bestreite nicht Deine theoretischen Ausführungen dazu.
Aber ich halte einfach mit meinem mehr laienhaften Wissen gewisse Praxiserfahrung dagegen. Danach spielen Deine Ausführungen nur dann eine maßgebliche Rolle, wenn sehr viele weitere andere Rechenoperationen in der Arbeitsmappe (womöglich noch dazu volatile) stattfinden oder sehr viele tausende Datensätze auszuwerten sind und evtl. dazu nur ein altersschwacher PC zur Verfügung steht
Alles dies ist hier aber nicht zu erwarten.
Ich hab jetzt mal meinen Formelansatz in einer Datei mit reichlich 3.000 Datensätzen getestet. Die Formel in die erste Zelle der formatierten Tabelle eingeben, hat nur einen Bruchteil einer Sekunde gebraucht, um alle tausende Ergebniswerte zu ermitteln. Desgleichen die Neuberechnung bei Sortierungen in anderen Spalten. Markus kann das gerne in seiner Datei mal in seiner Datei testen.
Außerdem besteht ja hier immer auch die Möglichkeit, die Daten mit einen Formelansatz zu berechnen und anschließend diese Datenwerte fix über die Formeln zu kopieren.
Auf jeden Fall ist das mE alles praktikabler, als immer auf andere XL-Versionen zu verweisen, die der TE nicht als vorhanden angibt.
Gruß Werner
.. , - ...
Anzeige
Damit spielst Du auf SORTIEREN an ...
27.04.2021 14:38:38
lupo1
... und das steht auch Dir kostenlos zur Verfügung.
AW: nein darauf spielte ich nicht an ...
27.04.2021 14:50:10
neopa
Hallo,
... auch wenn "es" mir zur Verfügung stehen könnte, nutze ich ausschließlich meine installierten XL2010 und XL2016. Und mit diesen hab ich auch getestet.
Und auch wenn Vergleiche hinken, hier (m)einer: Nur weil ich älteres normales Fahrrad mit einer 3-Gang-Schaltung habe und nutze, werde ich mir nicht ein E-Bike kaufen oder das meines Nachbarn nutzen, welches er mir sogar kostenfrei zur Verfügung stellen würde. Für alles wozu ich ein Fahrrad brauche reicht mir bisher mein vorhandenes.
Gruß Werner
.. , - ...
Anzeige
Aber Du ziehst Deinen Autoanhänger auch nicht ...
27.04.2021 15:03:48
lupo1
... am Ledergürtel hinter Dir her.
In einem Kfz-Reparatur-Forum bekommt man übrigens auch keine Antworten, die zu exponentiellem Verlauf führen. Denn dort muss der Mensch seine Arbeitskraft ohne Rechner einsetzen. Man achtet also drauf. Beim Rechnereinsatz verschiebt es sich zwar, aber der Zusammenhang ist nicht grundsätzlich aufgehoben.
AW: Theorie und Praxis ...
27.04.2021 15:03:59
Markus
Herzlichen Dank, Werner
AW: bitteschön owT
27.04.2021 15:19:12
neopa
Gruß Werner
.. , - ...
AW: mit 2 Formeln ...
27.04.2021 13:48:20
Markus
Hallo neopa C
vielen Dank, das sieht auf den ersten Blick ganz gut aus, ich werde das mal in die eigentliche Tabelle mit mittlerweile knapp 600 Datensätzen übertragen und schauen, ob das da dann auch noch so passt...
Gruß Markus
Anzeige
AW: sollte es auch bei gleicher Datenstruktur owT
27.04.2021 13:51:24
neopa
Gruß Werner
.. , - ...
Meine Befürchtung bewahrheitet sich ...
27.04.2021 13:51:56
lupo1
... "mittlerweile 600" klingt wie "bald 1.000".
Dann benötigen diese 1.000 Sätze für den Sort schon 500.000 Einzelberechnungen. Bei 2.000 Sätzen sind es 2.000.000.
AW: Meine Befürchtung bewahrheitet sich ...
27.04.2021 13:54:12
Markus
was wäre dann Deiner Meinung nach eine alternative Lösung für dieses Problem?
Bitte behalte immer den ganzen Beitrag im Blick
27.04.2021 13:55:29
lupo1
AW: Bitte behalte immer den ganzen Beitrag im Blick
27.04.2021 14:19:06
Markus
bitte entschuldige lupo1, aber was du von Dir gibst ist für niemanden hilfreich. Du ballerst - egal in welchem Thread Du was von Dir gibst - irgendwelche Phrasen raus und gehst nicht, oder nur völlig unzureichend auf die eigentlichen Fragen ein. Mag sein, dass das alles richtig ist, was du sagst, aber wenn Du nicht konkret wirst, ist es einfach nicht hilfreich! Statt dessen orakelst Du irgendwelche kanaanäischen Betreffzeilen und der Fragende kann sich dann seine Antwort aus x teilen deiner Antworten zusammenbauen.
Antworte bitte konkret und vollständig, oder lass es sein!
vielen Dank!
Anzeige
Das ist unzutreffend
27.04.2021 14:21:56
lupo1
Ich habe Dir als erster geantwortet.
Wenn Du dort sagst, dass es für Dich nicht in Frage kommt, musst Du trotzdem weiterhin den Ast beachten.
AW: Formel Ermittlung lfdNr je Vertrag
27.04.2021 13:55:29
Daniel
Hi
den aktuell gültigen Vertrag würde ich mit dieser Formel ermitteln:

=WENN(UND([@Vertragsbeginn]HEUTE());"x";"")
das ist dann nur auf die Zeile bezogen und somit von einer Sortierreihenfolge unabhängig.
das würde auch ermöglichen, dass zum aktuellen Datum kein- oder mehrere Verträge akutell sind.
Laufende Nummer ist schwierig.
am einfachsten ist, du berechnest die nicht, sondern trägst die als festen Werte ein.
sollte das Reihenfolgenunabhängig berechnet werden, müsstest du eine Formel anwenden, die den kompletten Datenbereich heran zieht, Theoretisch dies:

=RANG.GLEICH([@Vertragsbeginn];WENN([Handynummer]=[@Handynummer];[Vertragsbeginn]);1)
also die Rangnummer des jeweiligen Vertragsbeginns in der Liste der Vertragsbeginne, die dem jeweiligen Namen zugeordnet sind.
Das ganze muss als Matrixformel eingeben werden.
Ist aber theoretisch, da mein Excel 2016 diese Matrixformel mit einem #Wert-Fehler quittiert.
die Rangfolgenermittlung scheint nur mit dem kompletten Zellbereich als Eingangsgröße zu funktionieren.
Gruß Daniel
Anzeige
AW: Formel Ermittlung lfdNr je Vertrag
27.04.2021 14:22:42
Markus
Hallo Daniel.
vielen Dank! Die Formel für den gültigen Vertrag funktioniert sehr gut, bei der Rang-Formel erhalte ich einen "überlauf" - da muss ich mal noch nachschauen, was da das Problem ist...
AW: Formel Ermittlung lfdNr je Vertrag
27.04.2021 14:39:34
Daniel
Hi
Überlauf sollte nicht sein, da die Formel eigentlich nur einen Wert als Ergebnis ausgeben darf.
und wie gesagt, bei mir hat's nicht funktioniert.
ich würde die Tabelle so sortieren, dass sie Laufende Nummer richtig berechnet wird und dann die Formel kopieren und als Wert einfügen und dann damit arbeiten.
dh bei neuen Verträgen müsstest du dann die Laufende Nummer von Hand weiterschreiben (wenns wenige sind) oder den Vorgang wieder holen, dh sortieren, die Formel zur Berechnung einfügen, kopieren und als Wert einfügen, aber das hatte dir im Prinzp ja schon Lupo ganz zu Anfang vorgeschlagen)
selbst wenn die Berechnung der Laufenden Nummer mit unsortierten Daten funktionieren würde, es währe sehr rechenintensiv, weil bei so einer Berechnung jede einzelne Formel immer die komplette Spalte durchrechnen muss, während in der Sortierten Variante immer nur der Vergleich mit der Nachbarzelle ausreicht.
Gruß Daniel
Anzeige
AW: Formel Ermittlung lfdNr je Vertrag
27.04.2021 15:01:27
Markus
Hallo Daniel,
danke. Das hab ich jetzt glaub kapiert. Die Rechenzeit ist aber glaub ich vertretbar - immerhin muss ich keine Rechenzeit mehr bei IBM im RZ beantragen :-) Und ob die Sortierung nun ein paar Millisekunden länger dauert ist bei knapp 600 Datensätzen glaub ich wurscht :-) Und mehr Daten werden's nicht.
herzlichen Dank!
Idee zu den "festen Werten"
27.04.2021 14:37:11
lupo1
Nimmt man statt derer (also: lfdNr manuell) die Kombi aus Strg-. und Strg-:
(ein gemeinsamer Shortcut DatumZeit fällt mir grad nicht ein oder es gibt ihn nicht; mir gelang nur Strg-. LEER Strg-:),
entsteht die "wiederherstellbare korrekte Reihenfolge" ebenfalls, ohne dass man bei einer "Disorder" erst nach der höchsten lfdNr suchen muss.
Die lfdNr selbst bekommt man dann über die Excel-Zeilen-Nr. bei korrektem Sort.
Anzeige
AW: Idee zu den "festen Werten"
27.04.2021 14:42:20
Markus 

Die lfdNr selbst bekommt man dann über die Excel-Zeilen-Nr. bei korrektem Sort.
nur wenn die lfdNr eine fortlaufende Zahl über die ganze Tabelle wäre, sie ist ja aber abhängig je User.
Ich denke, dass ich die Lösungsvorschläge von Daniel (Aktueller Vertrag) und neopa c (laufende Nummer) verwenden werde. Wenn meine CPU siebenundzwanzig mikrosekunden länger rechnen muss, als es mit einer optimaleren Formel machbar wäre, ist das für mich vertretbar :-)
trotzdem vielen Dank und nix für ungut :-)
Markus
user-abh.: dann zusätzlich FILTER()
27.04.2021 14:53:10
lupo1
Also entweder das richtige Excel kaufen oder das kostenlose verwenden.
AW: user-abh.: dann zusätzlich FILTER()
27.04.2021 18:24:53
Daniel
Es gibt halt Menschen, die können weder noch.
Und für die meisten, die hier fragen stellen, dürfte das zutreffen.

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige