Generische Formel
=SUMPRODUCT(--(A1=things))>0
Zusammenfassung
Wenn Sie eine Zelle testen möchten, um festzustellen, ob sie einem von mehreren Dingen entspricht, können Sie dies mit einer Formel tun, die die Funktion SUMPRODUCT verwendet.
Kontext
Angenommen, Sie haben eine Liste von Textzeichenfolgen im Bereich B5: B11 und möchten jede Zelle mit einer anderen Liste von Dingen im Bereich E5: E9 testen. Mit anderen Worten, für jede Zelle in B5: B11 möchten Sie wissen: Entspricht diese Zelle einem der Dinge in E5: E9?
Sie könnten eine große Formel erstellen, die auf verschachtelten IF-Anweisungen basiert, aber eine Array-Formel, die auf SUMPRODUCT basiert, ist ein einfacherer und sauberer Ansatz.
Lösung
Die Lösung besteht darin, eine Formel zu erstellen, die auf mehrere Werte prüft und eine Liste mit TRUE / FALSE-Werten zurückgibt. Sobald wir das haben, können wir diese Liste (eigentlich ein Array) mit SUMPRODUCT verarbeiten.
Die Formel, die wir verwenden, sieht folgendermaßen aus:
=SUMPRODUCT(--(B5=things))>0
Erläuterung
Der Schlüssel ist dieses Snippet:
--(B5=things)
Das vergleicht einfach den Wert in B5 mit jedem Wert im genannten Bereich "Dinge". Da wir B5 mit einem Array vergleichen (dh dem benannten Bereich "Dinge", E5: E11), ist das Ergebnis ein Array von TRUE / FALSE-Werten wie folgt:
(WAHR; FALSCH; FALSCH; FALSCH; FALSCH)
Wenn wir nur einen TRUE im Array haben, wissen wir, dass B5 mindestens einer Sache in der Liste entspricht. Um die TRUE / FALSE-Werte auf 1s und 0s zu setzen, verwenden wir ein doppeltes Negativ (-, auch Double genannt einstellig). Nach diesem Zwang haben wir Folgendes:
(1; 0; 0; 0; 0)
Jetzt verarbeiten wir das Ergebnis mit SUMPRODUCT, wodurch die Elemente im Array addiert werden. Wenn wir ein Ergebnis ungleich Null erhalten, haben wir mindestens eine Übereinstimmung, daher verwenden wir> 1, um ein Endergebnis von entweder WAHR oder FALSCH zu erzwingen.
Mit einer fest codierten Liste
Es ist nicht erforderlich, dass Sie einen Bereich für Ihre Liste von Dingen verwenden. Wenn Sie nur nach einer kleinen Anzahl von Dingen suchen, können Sie eine Liste im Array-Format verwenden, die als Array-Konstante bezeichnet wird. Wenn Sie beispielsweise nur nach den Farben Rot, Blau und Grün suchen, können Sie Folgendes verwenden ("Rot", "Blau", "Grün"):
--(B5=("red","blue","green"))
Umgang mit zusätzlichen Räumen
Wenn die zu testenden Zellen zusätzlichen Speicherplatz enthalten, stimmen sie nicht richtig überein. Um den gesamten zusätzlichen Speicherplatz zu entfernen, können Sie die Formel so ändern, dass die TRIM-Funktion folgendermaßen verwendet wird:
=SUMPRODUCT(--(TRIM(A1)=things))>0
