Excel-Formel für letztes Datum in Abhängigkeit eines Ereignisses?
In Spalte A habe ich ein fortlaufendes Datum mit Termindaten.
In Spalte B ist der Name des Kunden.
Wie kann ich in Spalte C den letzten Termin des Kunden anzeigen?
Also wenn der Kunde heute einen Termin hat, soll in der Spalte C des heutigen Datums der vorhergegangene Termin des Kunden angezeigt werden.
3 Antworten
in C4: =Index($A$1:$A3;Max(($B$1:$B3=$B4)*Zeile($B$1:$B3)))
{Matrixformel}, also nicht mit Enter, sondern mit Strg+Shift+Enter abschließen.
bei Bedarf runterziehen
is kein vorheriger Eintrag vorhanden, wird mir in Libre Office der allererste Eintrag in A wiedergegeben, wahrscheinlich auch in xl.
Wichtig: Bereiche mit Zeile1 beginnen, ansonsten Korrekturen erforderlich.
Prinzip: im Bereich B bis 1 drüber(=B3) nach der grössten Zeile mit Übereinstimmung zur Zelle links daneben(=Müller) suchen und diese Zeile im indexBereich A1:eins drüber(=A3) wiedergeben (15.9.2018)
funktioniert auch beim vorherigen Besuch zur Suche des Vorvorherigen.
Wenn Du mehr über Matrixformeln wissen willst, gugl mal nach Haserodt Matrixformel.
Der erklärt das "handwerklich" recht anschaulich.
Zum Prinzip: Kannst Du Dir so vorstellen: Da wird virtuell (also nicht sichtbar oder adressierbar eine Spalte (Zeile) mit Zwischenberechnungen erstellt, ähnlich einer Hilfsspalte. Beispiel von oben:
($B$1:$B3=$B4)*Zeile($B$1:$B3) => {0.1.0}*{1.2.3}
also da wird der (Text)Wertvergleich durchgeführt und ergibt {falsch.wahr.falsch}
Das wird multipliziert mit der Zeile {1.2.3}, wobei falsch zu 0 und wahr zu 1 wird.
Das Ergebnis ist eine neue Matrix
Max({0.2.0}) ist 2, was als Zeile des Indexbereichs A1:A3 einfließt. Also der Wert in A2.
Man kann es häufig mit Hilfsspalten/Zeilen oder Kombinationen draus auch rechnen, aber 1. nicht immer und zweitens brauchen Hilfsspalten optisch Platz.
Weiteres siehe beim o.g. Peter Haserodt. Musst allerdings schon einige Stunden reinstecken, wenns fruchten soll.
Schreib mal in C2 diese Formel:
{=WENN(MAX((A$1:A1)*(B$1:B1=B2))=0;"";MAX((A$1:A1)*(B$1:B1=B2)))}
und kopiere sie runter.
**ACHTUNG!**
Das ist eine Matrixformel.
Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben, sondern die Eingabe der Formel NICHT mit ENTER abschließen, sondern mit:
STRG & SHIFT & ENTER (alle drei gleichzeitig).
DAS erzeugt die { } und macht die Formel zu einer Matrixformel.
Hast Du das so gemeint?
Ähnliches taucht hier öfter auf. Aber eine Arrayformel mit MAX hatte ich sicher schon lange nicht mehr.
Vielen Dank für die schnelle Antwort! Werde ich morgen gleich ausprobieren. Btw: was ist eine Matrixformel!?
Das ist ein SEHR mächtiges Instrument (für Fortgeschrittene) in Excel.
Wenn Du Dich näher dafür interessierst, schau Dir mal Peters Tutorials dazu an:
https://www.online-excel.de/excel//singsel.php?f=26
Im Beispiel oben suche ich mit dem Teil
MAX((A$1:A1)*(B$1:B1=B2))
der, wenn man ihn runterkopiert in z.B. C4 so aussieht:
=MAX((A$1:A3)*(B$1:B3=B4))
das Maximum der Datümer aus A1:A3, wenn B1:B3 dem ges. Namen entspricht.
Dazu baue ich mir in den Klammern eine Matrix, sprich eigentlich eine kleine Tabelle auf, die das Datum (das eigentlich auch nur eine Zahl ist, für heute z.B. 43419) aus Spalte A multipliziert mit dem Ausdruck:
B$1:B3=B4
Der gibt mir Zeile für Zeile WAHR oder FALSCH zurück, je nachdem ob der Name dem in B4 entspricht oder nicht. Für Excel sind das aber auch nur Zahlen, wobei WAHR der Zahl 1 entspricht und FALSCH der Zahl 0.
Was ich also aus diesem Produkt zurück bekomme ist:
43346 * 0 = 0
43358 * 1 = 43358
43405 * 0 = 0
und somit die Matrix {0 ; 43358 ; 0}
Daraus wird das Maximum gesucht, also 43358, was wegen der Formatierung als Datum angezeigt wird als: 15.09.2018.
So weit wollte ich eigentlich gar nicht ausholen, aber wenn ich mal ins Schwadronieren gerate ...
Ich hoffe, ich habe Dich jetzt nicht zu sehr verwirrt.
Sonst schau Dir nur den Link zu Peters HP an, der kann das viel besser erklären als ich.
Puh, das habe ich mir einfacher vorgestellt...
aber mit deiner Formel funktioniert es perfekt!
Vielen Dank dafür, das hat mir sehr viel Zeit und Nerven gespart!
Gern geschehen 🙂
Ja, Matrixformeln sind anfangs nicht einfach zu verstehen, aber wenn es dann einmal "Klick" gemacht hat, sind sie wirklich ein mächtiges Instrument in Excel und erweitern dessen Möglichkeiten erheblich.
Probier mal als Matrixformel KGRÖSSTE in Verbindung mit WENN.
Mega! Vielen Dank!