Generische Formel
=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")
Zusammenfassung
Eine Möglichkeit, mehrere Übereinstimmungen in Excel zu extrahieren, besteht darin, INDEX und MATCH mit einer Hilfsspalte zu verwenden, die übereinstimmende Daten kennzeichnet. Dies vermeidet die Komplexität einer fortgeschritteneren Array-Formel. In dem gezeigten Beispiel lautet die Formel in H6:
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
Dabei werden ct (G3), Daten (B3: E52) und Helfer (E3: E52) als Bereiche bezeichnet.
Erläuterung
Die Herausforderung bei Suchformeln, die mehr als eine Übereinstimmung abrufen, besteht darin, Duplikate (dh mehrere Übereinstimmungen) zu verwalten. Suchformeln wie VLOOKUP und INDEX + MATCH können die erste Übereinstimmung leicht finden, aber es ist viel schwieriger, "alle Übereinstimmungen" zu suchen, wenn Kriterien mehr als eine Übereinstimmung finden.
Diese Formel behandelt diese Herausforderung mithilfe einer Hilfsspalte, die einen numerischen Wert zurückgibt, mit dem auf einfache Weise mehrere Übereinstimmungen extrahiert werden können. Die Formel in der Hilfsspalte sieht folgendermaßen aus:
=SUM(E2,AND(C3=$I$3,D3=$J$3))
Die Hilfsspalte testet jede Zeile in den Daten, um festzustellen, ob die Abteilung in Spalte C mit dem Wert in I3 und das Gebäude in Spalte D mit dem Wert in J3 übereinstimmt. Beide logischen Tests müssen TRUE zurückgeben, damit AND TRUE zurückgibt.
Für jede Zeile wird das Ergebnis der UND-Funktion zum "Wert oben" in der Hilfsspalte hinzugefügt, um eine Zählung zu generieren. Der praktische Effekt dieser Formel ist ein inkrementeller Zähler, der sich nur ändert, wenn eine (neue) Übereinstimmung gefunden wird. Dann bleibt der Wert gleich, bis die nächste Übereinstimmung gefunden wird. Dies funktioniert, weil die TRUE / FALSE-Ergebnisse, die von AND zurückgegeben werden, als Teil der Summenoperation auf 1/0 Werte gezwungen werden. FALSE-Ergebnisse addieren nichts und TRUE-Ergebnisse addieren 1.
Zurück im Extraktionsbereich sieht die Suchformel für Name in Spalte H folgendermaßen aus:
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
Der INDEX + MATCH-Teil der Formel arbeitet von innen nach außen und sucht den Namen für die erste gefundene Übereinstimmung, wobei die Zeilennummer in Spalte G als Übereinstimmungswert verwendet wird:
INDEX(data,MATCH($G6,helper,0),1)
INDEX empfängt alle 3 Datenspalten als Array (benannter Bereich "Daten"), und MATCH ist so konfiguriert, dass es mit der Zeilennummer in der Hilfsspalte (benannter Bereich "Helfer") im exakten Übereinstimmungsmodus übereinstimmt (3. Argument auf Null gesetzt). .
Hier zeigt sich die Klugheit der Formel. Die Hilfsspalte enthält offensichtlich Duplikate, aber das spielt keine Rolle, da MATCH nur mit dem ersten Wert übereinstimmt. Jeder "erste Wert" entspricht standardmäßig der richtigen Zeile in der Datentabelle.
Die Formeln in den Spalten I und J sind bis auf die Spaltennummer, die jeweils um eins erhöht wird, die gleichen wie H.
Die IF-Anweisung, die die INDEX / MATCH-Formel umschließt, führt eine einfache Funktion aus: Sie überprüft jede Zeilennummer im Extraktionsbereich, um festzustellen, ob die Zeilennummer kleiner oder gleich dem Wert in G3 (benannter Bereich "ct") ist die Gesamtzahl aller übereinstimmenden Datensätze. In diesem Fall wird die INDEX / MATCH-Logik ausgeführt. Wenn nicht, gibt IF eine leere Zeichenfolge ("") aus.
Die Formel in G3 (benannter Bereich "ct") ist einfach:
=MAX(helper)
Da der Maximalwert in der Hilfsspalte der Gesamtzahl der Übereinstimmungen entspricht, ist die MAX-Funktion alles, was wir brauchen.
Hinweis: Der Extraktionsbereich muss manuell konfiguriert werden, um so viele Daten wie nötig zu verarbeiten (dh 5 Zeilen, 10 Zeilen, 20 Zeilen usw.). In diesem Beispiel ist es nur auf 5 Zeilen beschränkt, um das Arbeitsblatt kompakt zu halten.
Ich habe diese Technik in Mike Girvins Buch Control + Shift + Enter gelernt.
Die FILTER-Funktion
Wenn Sie über die Dynamic Array-Version von Excel verfügen, können Sie mit der FILTER-Funktion alle übereinstimmenden Daten viel einfacher extrahieren.
