Generische Formel
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Zusammenfassung
Um alle Übereinstimmungen basierend auf einer Teilübereinstimmung zu extrahieren, können Sie eine Array-Formel verwenden, die auf den Funktionen INDEX und AGGREGATE basiert und von ISNUMBER und SEARCH unterstützt wird. Im gezeigten Beispiel lautet die Formel in G5:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
mit den folgenden benannten Bereichen: "Suche" = D5, "ct" = D8, "Daten" = B5: B55.
Hinweis: Dies ist eine Array-Formel, die jedoch keine Steuerung + Umschalt + Eingabe erfordert, da AGGREGATE Arrays nativ verarbeiten kann.
Erläuterung
Der Kern dieser Formel ist die INDEX-Funktion, wobei AGGREGATE verwendet wird, um die "n-te Übereinstimmung" für jede Zeile im Extraktbereich zu ermitteln:
INDEX(data,nth_match_formula)
Fast die gesamte Arbeit besteht darin, herauszufinden und zu melden, welche Zeilen in "Daten" mit der Suchzeichenfolge übereinstimmen, und die Position für jeden übereinstimmenden Wert an INDEX zu melden. Dies geschieht mit der AGGREGATE-Funktion, die wie folgt konfiguriert ist:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Das erste Argument, 15, weist AGGREGATE an, sich wie SMALL zu verhalten und den n-ten kleinsten Wert zurückzugeben. Das zweite Argument, 6, ist eine Option zum Ignorieren von Fehlern. Das dritte Argument ist ein Ausdruck, der ein Array übereinstimmender Ergebnisse generiert (siehe unten). Das vierte Argument, F5, verhält sich in SMALL wie "k", um den "n-ten" Wert anzugeben.
AGGREGATE arbeitet mit Arrays, und der folgende Ausdruck erstellt ein Array für das dritte Argument in AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Hier wird die ROW-Funktion verwendet, um ein Array relativer Zeilennummern zu generieren, und ISNUMBER und SEARCH werden zusammen verwendet, um die Suchzeichenfolge mit den Werten in den Daten abzugleichen, wodurch ein Array von TRUE- und FALSE-Werten generiert wird.
Das Schlaue ist, die Zeilennummern durch die Suchergebnisse zu teilen. In einer solchen mathematischen Operation verhält sich TRUE wie 1 und FALSE wie Null. Das Ergebnis ist, dass Zeilennummern, die einer positiven Übereinstimmung zugeordnet sind, durch 1 geteilt werden und die Operation überleben, während Zeilennummern, die nicht übereinstimmenden Werten zugeordnet sind, zerstört werden und zu # DIV / 0-Fehlern werden. Da AGGREGATE so eingestellt ist, dass Fehler ignoriert werden, werden die # DIV / 0-Fehler ignoriert und die "n-te" kleinste Zahl in den verbleibenden Werten zurückgegeben, wobei die Zahl in Spalte F für "n-te" verwendet wird.
Leistung verwalten
Wie alle Array-Formeln ist diese Formel in Bezug auf Ressourcen mit einem großen Datensatz "teuer". Um die Auswirkungen auf die Leistung zu minimieren, wird die gesamte INDEX- und MATCH-Formel wie folgt in IF eingeschlossen:
=IF(F5>ct,"",formula)
wobei der benannte Bereich "ct" (D8) diese Formel enthält:
=COUNTIF(data,"*"&search&"*")
Diese Prüfung verhindert, dass der Teil INDEX und AGGREGATE der Formel ausgeführt wird, sobald alle übereinstimmenden Werte extrahiert wurden.
Array-Formel mit SMALL
Wenn Ihre Excel-Version nicht über die AGGREGATE-Funktion verfügt, können Sie eine alternative Formel verwenden, die auf SMALL und IF basiert:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
