Wiederverwendbare SVERWEIS (SVERWEIS)

Wir haben eine Bestellliste mit Nummern und Warenbezeichnungen. Ich möchte zum Beispiel alle Waren, die darin enthalten sind, nach Bestellnummer aus der Tabelle herausziehen. Mehr oder weniger so:

Wunderbare Funktion SVERWEIS (SVERWEIS) In einer solchen Situation wird es nur teilweise helfen, weil es Daten nur durch die erste gefundene Übereinstimmung extrahieren kann, dh uns nur geben wird Äpfel. Um alle Elemente aus der Tabelle zu finden und zu extrahieren, ist es besser, eine Matrixformel zu verwenden. Wie dieser:

=INDEX($B$2:$B$16;AM WENIGSTEN(IF($ E $ 2=A2: A16;LINE(B2:B16)-1;»»);LINE()-5))

Es muss wie folgt eingegeben werden:

1. Wählen Sie die Zellen aus, in denen die Ergebnisse angezeigt werden sollen (in unserem Beispiel ist dies der Bereich D6:D20)
2. eingeben (Formel in die erste Zelle kopieren) des Bereichs
3. Presse Ctrl + Shift + Enter

Einheitensubtraktion im Fragment STRING(B2:B16)-1 erfolgt aufgrund des Tabellenkopfes. Aus dem gleichen Grund wird, um die Verschiebung des resultierenden Bereichs relativ zum ursprünglichen auszugleichen, die Zahl fünf im Fragment subtrahiert STRING()-5

Um die #NUM! Fehler, der in leeren Zellen im resultierenden Bereich D6:D20 erscheint, können Sie die Fehlerprüffunktionen IF und EOSH verwenden und unsere Formel durch eine etwas komplexere ersetzen:

=WENN(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))

In Excel 2007 erschien eine bequemere IFERROR-Funktion, mit der Sie das Problem kompakter lösen können:

=IFEHLER(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)

PS

In der englischen Version von Excel sehen diese Funktionen so aus:

=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))

=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))

=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)

• Verwenden der VLOOKUP-Funktion zum Suchen von Daten in einer Tabelle
• Eine verbesserte Version der SVERWEIS2-Funktion, die in jeder Spalte und nicht nur im ersten Wert suchen kann
• SVERWEIS2- und SVERWEIS3-Funktionen aus dem PLEX-Add-on
• Was sind Matrixformeln und wofür werden sie verwendet?