Inhalte
Alle klassischen Such- und Typersetzungsfunktionen VPR (SVERWEIS), GPR (WVERWEIS), AUSGESETZTER (PASSEN) und solche haben eine wichtige Eigenschaft – sie suchen von Anfang bis Ende, also von links nach rechts oder von oben nach unten in den Quelldaten. Sobald die erste passende Übereinstimmung gefunden wird, stoppt die Suche und nur das erste Vorkommen des benötigten Elements wird gefunden.
Was tun, wenn wir nicht das erste, sondern das letzte Vorkommen finden müssen? Zum Beispiel die letzte Transaktion für den Kunden, die letzte Zahlung, die letzte Bestellung usw.?
Methode 1: Finden der letzten Zeile mit einer Array-Formel
Wenn die ursprüngliche Tabelle keine Spalte mit einem Datum oder einer fortlaufenden Nummer einer Zeile (Bestellung, Zahlung …) hat, dann besteht unsere Aufgabe tatsächlich darin, die letzte Zeile zu finden, die die gegebene Bedingung erfüllt. Dies kann mit der folgenden Array-Formel erfolgen:
Hier:
- Funktion IF (WENN) überprüft nacheinander alle Zellen in einer Spalte Kundenfälle und zeigt die Zeilennummer an, wenn sie den benötigten Namen enthält. Die Zeilennummer auf dem Blatt wird uns von der Funktion gegeben LINE (DIE ZEILE), aber da wir die Zeilennummer in der Tabelle brauchen, müssen wir zusätzlich 1 abziehen, weil wir eine Überschrift in der Tabelle haben.
- Dann die Funktion MAX (maximal) wählt den maximalen Wert aus der gebildeten Menge von Zeilennummern aus, dh die Nummer der letzten Zeile des Clients.
- Funktion INDEX (INDEX) gibt den Inhalt der Zelle mit der gefundenen letzten Zahl aus einer beliebigen anderen erforderlichen Tabellenspalte zurück (Bestellcode).
All dies muss als eingegeben werden Array-Formeldh:
- In Office 365 mit den neuesten installierten Updates und Unterstützung für dynamische Arrays können Sie einfach drücken Enter.
- In allen anderen Versionen müssen Sie nach Eingabe der Formel die Tastenkombination drücken Ctrl+Shift+Enter, wodurch in der Bearbeitungsleiste automatisch geschweifte Klammern hinzugefügt werden.
Methode 2: Reverse Lookup mit der neuen LOOKUP-Funktion
Ich habe bereits einen langen Artikel mit einem Video über ein neues Feature geschrieben ANZEIGEN (XVERWEIS), das in den neuesten Versionen von Office erschien, um das alte VLOOKUP zu ersetzen (SVERWEIS). Mit Hilfe von BROWSE ist unsere Aufgabe ganz elementar gelöst, denn. für diese Funktion (im Gegensatz zu SVERWEIS) können Sie die Suchrichtung explizit festlegen: von oben nach unten oder von unten nach oben – dafür ist ihr letztes Argument (-1) verantwortlich:
Methode 3. Suchen Sie nach einer Zeichenfolge mit dem neuesten Datum
Wenn wir in den Quelldaten eine Spalte mit einer Seriennummer oder einem Datum haben, das eine ähnliche Rolle spielt, wird die Aufgabe geändert – wir müssen nicht die letzte (unterste) Zeile mit einer Übereinstimmung finden, sondern die Zeile mit dem neuesten ( maximal) Datum.
Ich habe bereits ausführlich besprochen, wie man dies mit klassischen Funktionen macht, und jetzt wollen wir versuchen, die Leistungsfähigkeit der neuen dynamischen Array-Funktionen zu nutzen. Für mehr Schönheit und Komfort wandeln wir die Originaltabelle auch mithilfe einer Tastenkombination in eine „intelligente“ Tabelle um Ctrl+T oder Befehle Startseite – Als Tabelle formatieren (Home — Als Tabelle formatieren).
Mit ihrer Hilfe löst dieses „Killerpaar“ unser Problem sehr elegant:
Hier:
- Funktion zuerst FILTER (FILTER) wählt nur die Zeilen aus unserer Tabelle aus, in denen sich die Spalte befindet Kundenfälle – den Namen, den wir brauchen.
- Dann die Funktion GRADE (SORTIEREN) sortiert die ausgewählten Zeilen nach Datum in absteigender Reihenfolge, wobei das neueste Geschäft ganz oben steht.
- Funktion INDEX (INDEX) extrahiert die erste Zeile, dh gibt den letzten Trade zurück, den wir brauchen.
- Und schließlich entfernt die externe FILTER-Funktion die zusätzliche 1. und 3. Spalte aus den Ergebnissen (Bestellcode и Kundenfälle) und hinterlässt nur das Datum und den Betrag. Dazu wird ein Array von Konstanten verwendet. {0;1;0;1}, die definieren, welche Spalten angezeigt werden sollen (1) oder nicht (0).
Methode 4: Finden der letzten Übereinstimmung in Power Query
Schauen wir uns der Vollständigkeit halber eine Lösung für unser Problem mit der umgekehrten Suche mit dem Power Query-Add-In an. Mit ihrer Hilfe ist alles sehr schnell und schön gelöst.
1. Lassen Sie uns unsere ursprüngliche Tabelle mithilfe einer Tastenkombination in eine „intelligente“ Tabelle umwandeln Ctrl+T oder Befehle Startseite – Als Tabelle formatieren (Home — Als Tabelle formatieren).
2. Laden Sie es mit der Schaltfläche in Power Query Aus Tabelle/Bereich Tab Datum (Daten – Aus Tabelle/Bereich).
3. Wir sortieren (über die Dropdown-Liste des Filters in der Kopfzeile) unsere Tabelle in absteigender Reihenfolge des Datums, sodass die neuesten Transaktionen oben stehen.
4… In der Registerkarte Transformation wähle ein Team Gruppiere nach (Umwandeln — Gruppieren nach) und stellen Sie die Gruppierung nach Kunden ein und wählen Sie als Aggregationsfunktion die Option aus Alle Zeilen (Alle Reihen). Sie können die neue Spalte beliebig benennen – zum Beispiel Details.
Nach der Gruppierung erhalten wir eine Liste mit eindeutigen Namen unserer Kunden und in der Spalte Details – Tabellen mit allen Transaktionen von jedem von ihnen, wobei die erste Zeile die letzte Transaktion ist, was wir brauchen:
5. Fügen Sie mit der Schaltfläche eine neue berechnete Spalte hinzu Benutzerdefinierte Spalte Tab Spalte hinzufügen (Spalte hinzufügen – Benutzerdefinierte Spalte hinzufügen)und geben Sie die folgende Formel ein:
Hier Details – Dies ist die Spalte, aus der wir Tabellen von Kunden entnehmen, und 0 {} ist die Nummer der Zeile, die wir extrahieren möchten (die Zeilennummerierung in Power Query beginnt bei Null). Wir erhalten eine Spalte mit Datensätzen (Rekord), wobei jeder Eintrag die erste Zeile aus jeder Tabelle ist:
Es bleibt, den Inhalt aller Datensätze mit der Schaltfläche mit Doppelpfeilen in der Spaltenüberschrift zu erweitern Letzter Deal Auswahl der gewünschten Spalten:
… und dann die nicht mehr benötigte Spalte löschen Details indem Sie mit der rechten Maustaste auf den Titel klicken – Spalten entfernen (Spalten entfernen).
Nach dem Hochladen der Ergebnisse auf das Blatt durch Home — Schließen und laden — Schließen und laden (Home — Schließen & Laden — Schließen & Laden nach…) wir erhalten so eine schöne Tabelle mit einer Liste der letzten Transaktionen, wie wir es wollten:
Wenn Sie die Quelldaten ändern, dürfen Sie nicht vergessen, die Ergebnisse zu aktualisieren, indem Sie mit der rechten Maustaste darauf klicken – den Befehl Aktualisieren & Speichern (Aktualisierung) oder Tastenkürzel Ctrl+Andere+F5.
- Die LOOKUP-Funktion ist ein Nachkomme von SVERWEIS
- Verwendung der neuen dynamischen Array-Funktionen SORT, FILTER und UNIC
- Suchen der letzten nicht leeren Zelle in einer Zeile oder Spalte mit der LOOKUP-Funktion