Ich bin es gewohnt, mit SVERWEIS zu arbeiten, aber dieses Mal stehe ich vor einer Herausforderung.Ich möchte nicht den ersten passenden Wert, sondern denzuletzt.Wie? (Ich arbeite mit LibreOffice Calc, aber eine MS Excel-Lösung dürfte genauso nützlich sein.)
Der Grund dafür ist, dass ich zwei Textspalten mit Tausenden von Zeilen habe. Nehmen wir an, eine ist eine Liste der Zahlungsempfänger (Amazon, Ebay, Arbeitgeber, Lebensmittelgeschäft usw.) und die andere eine Liste der Ausgabenkategorien (Löhne, Steuern, Haushalt, Miete usw.). Einige Transaktionen haben nicht immer dieselbe Ausgabenkategorie, und ich möchte die zuletzt verwendete auswählen. Beachten Sie, dass die Liste nach keiner der Spalten sortiert ist (tatsächlich nach Datum), und ich möchte die Sortierreihenfolge nicht ändern.
Was ich habe (abgesehen von der Fehlerbehandlung), ist die übliche „First-Match“-Formel:
=VLOOKUP(
[payee field] , [payee+category range] , [index of category column] ,
0 )
Ich habe gesehenLösungenso, aber ich bekomme #DIV/0!Fehlermeldungen:
=LOOKUP(2 , 1/( [payee range] = [search value] ) , [category range] )
Die Lösung kann jede beliebige Formel sein, nicht unbedingt SVERWEIS. Ich kann auch die Spalten für Zahlungsempfänger/Kategorie vertauschen. Nur bitte keine Änderung in der Sortierspalte.
Bonuspunkte für eine Lösung, die denam häufigstenWert statt Letzter!
Antwort1
Sie können eine Arrayformel verwenden, um Daten aus dem letzten übereinstimmenden Datensatz abzurufen.
=INDEX(IF($A$1:$A$20="c",$B$1:$B$20),MAX(IF($A$1:$A$20="c",ROW($A$1:$A$20))))
Geben Sie die Formel mit Ctrl+ Shift+ ein Enter.
Dies funktioniert wie die INDEX/ MATCH-Konstruktion eines , jedoch wird anstelle von VLOOKUPein Bedingungssatz verwendet .MAXMATCH
Beachten Sie, dass hierbei davon ausgegangen wird, dass Ihre Tabelle in Zeile 1 beginnt. Wenn Ihre Daten in einer anderen Zeile beginnen, müssen Sie den ROW(...)Teil anpassen, indem Sie die Differenz zwischen der obersten Zeile und 1 abziehen.
Antwort2
(Antwort hier, da es für sortierte Daten keine separate Frage gibt.)
Wenn die Datenwarsortiert, können Sie VLOOKUPmit dem range_lookupArgument verwenden TRUE(oder weglassen, da es die Standardeinstellung ist), was für Excel offiziell als „Suche nach ungefährer Übereinstimmung“ beschrieben wird.
Mit anderen Worten, für sortierte Daten:
- Das Setzen des letzten Arguments auf
FALSEgibt denErsteWert und - Das Setzen des letzten Arguments auf
TRUEgibt denzuletztWert.
Dies ist weitgehend undokumentiert und unklar, stammt aber aus VisiCalc (1979) und gilt heute zumindest in Microsoft Excel, LibreOffice Calc und Google Sheets. Es ist letztlich auf die anfängliche Implementierung von LOOKUPin VisiCalc (und daher VLOOKUPund HLOOKUP) zurückzuführen, als es keinen vierten Parameter gab. Der Wert wird durchbinäre Suche, unter Verwendung einer inklusiven Linksgrenze und einer exklusiven Rechtsgrenze (eine gängige und elegante Implementierung), was zu diesem Verhalten führt.
Technisch bedeutet dies, dass man die Suche mit dem Kandidatenintervall beginnt [0, n), wobei ndie Länge des Arrays ist, und die schleifeninvariante Bedingung ist, dass A[imin] <= key && key < A[imax](die linke Grenze ist <= das Ziel, die rechte Grenze, die eine nach dem Ende beginnt, ist > das Ziel; zur Validierung entweder die Werte an den Endpunkten vorher prüfen oder das Ergebnis nachher prüfen) und sukzessive halbiert und die Seite auswählt, die diese Invariante beibehält: durch Ausschluss wird eine Seite, bis man zu einem Intervall mit 1 Term kommt, [k, k+1)und der Algorithmus gibt dann zurück k. Dies muss keine exakte Übereinstimmung sein (!): es ist nur die engste Übereinstimmung von unten. Im Fall von doppelten Übereinstimmungen führt dies zur Rückgabe deszuletztmatch, da es erfordert, dass der nächste Wertgrößerals der Schlüssel (oder das Ende des Arrays). Im Falle von Duplikaten müssen SiemancheVerhalten, und dies ist sinnvoll und einfach umzusetzen.
Dieses Verhalten wird in diesem alten Microsoft Knowledge Base-Artikel (Hervorhebung hinzugefügt) explizit beschrieben: „XL: So geben Sie die erste oder letzte Übereinstimmung in einem Array zurück“ (Q214069):
Mit der Funktion LOOKUP() können Sie in einem Array sortierter Daten nach einem Wert suchen und den entsprechenden Wert an dieser Position in einem anderen Array zurückgeben. Wenn der Suchwert im Array wiederholt wird,Es gibt die letzte gefundene Übereinstimmung zurück.Dieses Verhalten gilt für die Funktionen VLOOKUP(), HLOOKUP() und LOOKUP().
Es folgt die offizielle Dokumentation für einige Tabellenkalkulationen. In keiner der beiden wird das „letzte Übereinstimmung“-Verhalten angegeben, es wird jedoch in der Google Sheets-Dokumentation angedeutet:
-
WAHRgeht davon aus, dass die erste Spalte der Tabelle entweder numerisch oder alphabetisch sortiert ist und sucht dann nachder nächste Wert.
-
Wenn oder weggelassen
is_sortedwirdTRUE,Die nächste Übereinstimmung(weniger als oder gleichzum Suchschlüssel) wird zurückgegeben
Antwort3
Wenn die Werte im Sucharray sequenziell sind (d. h. Sie suchen nach dem größten Wert, z. B. dem neuesten Datum), müssen Sie nicht einmal die Funktion INDIREKT verwenden. Versuchen Sie diesen einfachen Code:
=MAX(IF($A$1:$A$20="c",$B$1:$B$20,)
Geben Sie die Formel erneut mit STRG + UMSCHALT + EINGABE ein.
Antwort4
Ich habe es mit dem häufigsten Wert versucht. Ich bin nicht sicher, ob es in LibreOffice funktioniert, aber in Excel scheint es zu funktionieren.
=INDEX($B$2:$B$9,VERGLEICH(MAX(--($A$2:$A$9=D2)*ZÄHLENWENNS($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,D2)),--($A$2:$A$9=D2)*ZÄHLENWENNS($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,D2),0))
Spalte A wäre der Zahlungsempfänger, Spalte B wäre die Kategorie, D2 ist der Zahlungsempfänger, nach dem Sie filtern möchten. Ich bin nicht sicher, warum in der obigen Funktion zusätzliche Zeilenumbrüche eingefügt werden.
Meine Funktion zum Finden der letzten Zelle wäre folgende:
=INDIREKT("B" & MAX(--($A$2:$A$9=D2)*ZEILE($A$2:$A$9)))
Mit „Indirekt“ kann ich die Spalte angeben, die ich zurückgeben möchte, und die Zeile direkt suchen (ich muss also nicht die Anzahl der Kopfzeilen abziehen).
Beide Funktionen müssen eingegeben werden mitStrg+Umschalt+Eingabe