Generische Formel
=FILTER(list1,COUNTIF(list2,list1))
Zusammenfassung
Um Daten zu filtern, um übereinstimmende Werte in zwei Listen zu extrahieren, können Sie die Funktion FILTER und die Funktion COUNTIF oder COUNTIFS verwenden. In dem gezeigten Beispiel lautet die Formel in F5:
=FILTER(list1,COUNTIF(list2,list1))
Dabei sind Liste1 (B5: B16) und Liste2 (D5: D14) benannte Bereiche. Das von FILTER zurückgegebene Ergebnis enthält nur die Werte in Liste1 , die in Liste2 angezeigt werden .
Hinweis: FILTER ist eine neue dynamische Array-Funktion in Excel 365.
Erläuterung
Diese Formel basiert auf der FILTER-Funktion, um Daten basierend auf einem logischen Test abzurufen, der mit der COUNTIF-Funktion erstellt wurde:
=FILTER(list1,COUNTIF(list2,list1))
Mit der COUNTIF-Funktion wird von innen nach außen der eigentliche Filter erstellt:
COUNTIF(list2,list1)
Beachten Sie, dass wir list2 als Bereichsargument und list1 als Kriterienargument verwenden. Mit anderen Worten, wir bitten COUNTIF, alle Werte in Liste1 zu zählen, die in Liste2 erscheinen . Da wir COUNTIF mehrere Werte für Kriterien geben, erhalten wir ein Array mit mehreren Ergebnissen zurück:
(1;1;0;1;0;1;0;0;1;0;1;1)
Beachten Sie, dass das Array 12 Zählungen enthält, eine für jeden Wert in Liste1 . Ein Nullwert gibt einen Wert in Liste1 an , der in Liste2 nicht gefunden wird . Jede andere positive Zahl gibt einen Wert in Liste1 an , der in Liste2 gefunden wird . Dieses Array wird als include-Argument direkt an die FILTER-Funktion zurückgegeben:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Die Filterfunktion verwendet das Array als Filter. Jeder Wert in Liste1 , der einer Null zugeordnet ist, wird entfernt, während jeder Wert, der einer positiven Zahl zugeordnet ist, überlebt.
Das Ergebnis ist ein Array von 7 übereinstimmenden Werten, die in den Bereich F5: F11 fallen. Wenn sich Daten ändern, berechnet FILTER neu und gibt eine neue Liste übereinstimmender Werte basierend auf den neuen Daten zurück.
Nicht übereinstimmende Werte
Um nicht übereinstimmende Werte aus Liste1 zu extrahieren (dh Werte in Liste1 , die nicht in Liste2 erscheinen ), können Sie die NOT-Funktion wie folgt zur Formel hinzufügen:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Die NOT-Funktion kehrt das Ergebnis von COUNTIF effektiv um - jede Zahl ungleich Null wird FALSE und jeder Nullwert wird TRUE. Das Ergebnis ist eine Liste der Werte in Liste1 , die in Liste2 nicht vorhanden sind .
Mit INDEX
Es ist möglich, eine Formel zum Extrahieren übereinstimmender Werte ohne die Funktion FILTER zu erstellen, die Formel ist jedoch komplexer. Eine Möglichkeit besteht darin, die INDEX-Funktion in einer Formel wie der folgenden zu verwenden:
Die kopierte Formel in G5 lautet:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden, außer in Excel 365.
Der Kern dieser Formel ist die INDEX-Funktion, die list1 als Array-Argument empfängt . Der größte Teil der verbleibenden Formel berechnet einfach die Zeilennummer, die für übereinstimmende Werte verwendet werden soll. Dieser Ausdruck generiert eine Liste der relativen Zeilennummern:
ROW(list1)-ROW(INDEX(list1,1,1))+1
Dies gibt ein Array von 12 Zahlen zurück, die die Zeilen in Liste1 darstellen :
(1;2;3;4;5;6;7;8;9;10;11;12)
Diese werden mit der IF-Funktion und derselben Logik gefiltert, die oben in FILTER verwendet wurde, basierend auf der COUNTIF-Funktion:
COUNTIF(list2,list1) // find matching values
Das resultierende Array sieht folgendermaßen aus:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Dieses Array wird direkt an die SMALL-Funktion übergeben, mit der die nächste übereinstimmende Zeilennummer abgerufen wird, während die Formel in die Spalte kopiert wird. Der k-Wert für SMALL (think nth) wird mit einem erweiterten Bereich berechnet:
ROWS($G$5:G5) // incrementing value for k
Die IFERROR-Funktion wird verwendet, um Fehler abzufangen, die auftreten, wenn die Formel kopiert wird und keine übereinstimmenden Werte mehr vorhanden sind. Ein weiteres Beispiel für diese Idee finden Sie in dieser Formel.
