Ich verwende Microsoft Excel 2016. Und ich habe 2 Tabellen auf verschiedenen Blättern.
sheet1.tbl1 {
key1,
key2,
val1,
fun1,
fun2
}
sheet2.tbl2 {
key1,
key2,
val1,
val2
}
Ich möchte Werte mithilfe einer Formel oder anderer Methoden von nach nach bringen. Genauer gesagt, „join by“ sheet1.tbl1und „bring from“ von nach nach nach bringen . Die Schlüssel sind in beiden Tabellen eindeutig, also reicht die erste Übereinstimmung aus.sheet2.tbl2key1, key2val1, val2sheet2.tbl2fun1, fun2sheet1.tbl1
Ich habe Formel versucht
=INDEX(rls, MATCH(tbl1[[key1]:[key2]], tbl2[key1]:[key2]], 0),3)
Aber es funktioniert nicht.
Ich möchte auch keine Erweiterungen verwenden, sondern dies mithilfe integrierter Funktionen erreichen. Und zwar vorzugsweise mithilfe der Tabellenspaltensyntax ( tbl1[[key1]:[key2]]).
Antwort1
Die Schwierigkeit liegt in der Tastenkombination in der zweiten Tabelle. Sie erschwert beispielsweise die Verwendung . Für oder VLOOKUP()ist dies jedoch kein besonderes Problem .INDEX/MATCHXLOOKUP()
Zum Beispiel:
=XLOOKUP($A1:$A9&"|"&$B1:$B9, $H$20:$H$28&"|"&$I$20:$I$28, J20:J28,,0)
Sie kombinieren die Schlüssel mit dem Vereinigungsoperator &und trennen sie durch ein unerwartetes Zeichen, das in den vorhandenen Daten nicht vorhanden ist, wie z. B. |. (Sie können ein Zeichen auswählen und dann in den Schlüsseln danach suchen, um sicherzustellen, dass es nicht verwendet wird.) Auf diese Weise werden unerwartete Duplikate vermieden. Beispiel: Ein Schlüsselpaar ist Addund , resswährend ein anderes Adund ist dress. Mit einem Zeichen dazwischen erhält man Add|ressund Ad|dressstatt zwei Instanzen von Address.
Das Kombinieren ist für XLOOKUP()und MATCH()(die Hälfte von , INDEX/MATCHin der Sie es tun) unkompliziert. Beide können auch problemlos mit den Arrays für diese Teile umgehen, sodass Sie eine einzelne Formel erstellen können.
In aktuellen Versionen ist das der Fall, SPILLwas gut funktioniert. In älteren Versionen (Sie haben mit 2016 getaggt) ist das nicht der Fall, sodass Sie entweder {CSE}einen Eintrag verwenden oder je nach Bedarf die Spalte kopieren und einfügen müssen.
Für neuere Versionen XLOOKUP()gibt es eine einfache und offensichtliche Formel (gut für die Wartung). Für die älteren Versionen INDEX/MATCHgibt es nur wenige Abstriche:
=INDEX($J$20:$J$28, MATCH($A1:$A9&"|"&$B1:$B9, $H$20:$H$28&"|"&$I$20:$I$28,,0))
Wenn Sie beides handhaben müssen, sollten Sie diese Option verwenden.
Keine der Formeln unterstützt einen Zielbereich mit zwei Spalten, daher ist (in diesen Formeln) J20:K28 nicht verfügbar.
(Aber natürlich kennt vielleicht jemand eine nette Methode, es verfügbar zu machen!)
Es gibt eine nette Methode, FILTER/FILTERdie Sie aber nicht mit Excel-2016-Benutzern verwenden können, sodass sie Ihnen heute nicht weiterhilft. Könnte jedoch eine hilfreiche Technik in zukünftigen Arbeitsmappen sein:
=FILTER(FILTER(H20:K28,SORT(A1:A9&"|"&B1:B9)=SORT(H20:H28&"|"&I20:I28)),{0,0,1,1})
Das Innere FILTER()wählt die Daten aus. Natürlich sind die Schlüssellisten mit ziemlicher Sicherheit nicht in derselben Reihenfolge (oder "=F12, =F13, =F14 usw. würde fast funktionieren, oder?). Verwenden Sie also SORT()für jedes Schlüsselkombinationsarray, um sie in dieselbe Reihenfolge zu bringen. Wenn es in der zweiten Tabelle Schlüsselpaare gibt, die nicht in der ersten sind, müssen Sie diese entweder löschen oder einen anderen Pfad wählen.
Das Äußere FILTERfunktioniert dann ähnlich wie INDEX()bei Verwendung einer Array-Konstante zum Auswählen der auszugebenden Spalten (oder Zeilen). Sie könnten INDEX()stattdessen verwenden, aber nicht so einfach. FILTER()verwendet die einfache Array-Konstante als Funktion „Spalte anzeigen/Spalte nicht anzeigen“. Sie geben also nur die beiden Spalten aus, die Sie ausgeben möchten.
Es hat den Vorteil, dass es eine mehrdimensionale Ausgabe bereitstellt, einschließlich SPILL-ing, also einer Formel in einer Zelle und Sie haben Ihre gesamte Ausgabe.
Sie könnten sogar das verwenden FILTERXML(), was Benutzer von 2016 getan hätten, aber obwohl es eine sehr schöne, clevere Verwendung davon ist (insbesondere, da die Methode die Arrays beider Spalten im einzelnen XPATH erstellen würde) usually, wäre es in diesem Fall nur eine sehr einfache Übung zum Zahlenrechnen auf niedriger Ebene. Kludgy... „bis zum Maximum“. Und es könnte zu Problemen mit der internen Array-String-Länge kommen.
Dabei ist noch nicht einmal die Hilfsspaltenroute (für die kombinierten Schlüssel) oder ihre Verwandte, die die kombinierten Schlüssel in benannte Bereiche einfügt, um sie leicht adressierbar zu machen, erwähnt.
Ich würde die INDEX/MATCHMethode für deinen Benutzermix empfehlen.