Generische Formel
=FILTER(list1,COUNTIF(list2,list1))
Zusammenfassung
Um zwei Listen zu vergleichen und gemeinsame Werte zu extrahieren, können Sie eine Formel verwenden, die auf den Funktionen FILTER und COUNTIF basiert. In dem gezeigten Beispiel lautet die Formel in F5:
=FILTER(list1,COUNTIF(list2,list1))
Dabei sind Liste1 (B5: B15) und Liste2 (D5: D13) benannte Bereiche. Das Ergebnis, Werte, die in beiden Listen angezeigt werden, fällt in den Bereich F5: F11.
Erläuterung
Die FILTER-Funktion akzeptiert ein Array von Werten und ein "include" -Argument, das das Array basierend auf einem logischen Ausdruck oder Wert filtert.
In diesem Fall wird das Array als benannter Bereich "list1" bereitgestellt, der alle Werte in B5: B15 enthält. Das include- Argument wird von der COUNTIF-Funktion geliefert, die in FILTER verschachtelt ist:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF wird mit list2 als Bereich und list1 als Kriterien eingerichtet . Da wir COUNTIF elf Kriterienwerte geben, gibt COUNTIF elf Ergebnisse in einem Array wie dem folgenden zurück:
(1;1;0;1;0;1;0;1;0;1;1)
Beachten Sie, dass die Einsen den Elementen in Liste2 entsprechen, die in Liste1 angezeigt werden.
Dieses Array wird als "include" -Argument direkt an die FILTER-Funktion übergeben:
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Die Funktion FILTER filtert list1 anhand der von COUNTIF bereitgestellten Werte. Mit Null verknüpfte Werte werden entfernt. andere Werte bleiben erhalten.
Das Endergebnis ist ein Array von Werten, die in beiden Listen vorhanden sind und in den Bereich F5: F11 übergehen.
Erweiterte Logik
In der obigen Formel verwenden wir die Rohergebnisse von COUNTIF als Filter. Dies funktioniert, weil Excel jeden Wert ungleich Null als WAHR und Null als FALSCH auswertet. Wenn COUNTIF eine Anzahl größer als 1 zurückgibt, funktioniert der Filter weiterhin ordnungsgemäß.
Um die Ergebnisse TRUE und FALSE explizit zu erzwingen, können Sie "> 0" wie folgt verwenden:
=FILTER(list1,COUNTIF(list2,list1)>0)
Duplikate entfernen oder sortieren
Um Duplikate zu entfernen, verschachteln Sie einfach die Formel in der UNIQUE-Funktion:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Verschachteln Sie die Ergebnisse, um die Ergebnisse zu sortieren:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Listenwerte fehlen in Liste2
Um Werte in Liste1 auszugeben, die in Liste2 fehlen, können Sie die Logik folgendermaßen umkehren:
=FILTER(list1,COUNTIF(list2,list1)=0)
