Generische Formel
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Zusammenfassung
Wenn Sie eine Zelle testen möchten, um festzustellen, ob sie alle Elemente in einer Liste enthält, können Sie dies mit einer Formel tun, die die SEARCH-Funktion mithilfe der Funktionen ISNUMBER, SUMPRODUCT und COUNTA verwendet.
Kontext
Angenommen, Sie haben eine Liste von Textzeichenfolgen im Bereich B5: B8 und möchten herausfinden, ob diese Zellen alle Wörter in einem anderen Bereich enthalten, E5: E7.
Sie könnten eine Formel erstellen, die verschachtelte IF-Anweisungen verwendet, um nach jedem Element zu suchen. Diese Formel lässt sich jedoch nicht gut skalieren, wenn Sie viele Dinge suchen müssen. Jedes Mal, wenn Sie ein zu suchendes Wort hinzufügen, müssen Sie eine weitere verschachtelte IF hinzufügen und Klammern anpassen.
Lösung
Die Lösung besteht darin, eine Formel zu erstellen, die alle Übereinstimmungen gleichzeitig zählt. Sobald wir das haben, vergleichen wir diese Anzahl einfach mit der Anzahl der Artikel, nach denen wir suchen. Wenn sie übereinstimmen, wissen wir, dass eine Zelle alle Elemente enthält.
Im gezeigten Beispiel lautet die Formel, die wir verwenden:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Erläuterung
Der Schlüssel ist dieses Snippet:
ISNUMBER(SEARCH(things,B5)
Dies basiert auf einer anderen Formel (hier ausführlich erläutert), die einfach eine Zelle auf einen einzelnen Teilstring überprüft. Wenn die Zelle den Teilstring enthält, gibt die Formel TRUE zurück. Wenn nicht, gibt die Formel FALSE zurück.
Wenn wir jedoch derselben Formel eine Liste von Dingen geben (in diesem Fall verwenden wir einen benannten Bereich namens "Dinge", E5: E7), erhalten wir eine Liste von TRUE / FALSE-Werten zurück, einen für jedes Element in . Das Ergebnis ist ein Array, das folgendermaßen aussieht:
(WAHR; WAHR; WAHR)
Dabei steht jeder TRUE für einen gefundenen Gegenstand und jeder FALSE für einen nicht gefundenen Gegenstand.
Wir können die TRUE / FALSE-Werte mit einem doppelten Negativ (-, auch als doppelt unär bezeichnet) auf 1s und 0s zwingen:
--ISNUMBER(SEARCH(things,B5))
was ein Array wie folgt ergibt:
(1; 1; 1)
Als nächstes verarbeiten wir dieses Array mit SUMPRODUCT, wodurch wir eine Gesamtsumme erhalten. Wenn diese Summe der Anzahl der Elemente im genannten Bereich "Dinge" entspricht, wissen wir, dass wir alle Dinge gefunden haben und TRUE zurückgeben können. Wir vergleichen die beiden Zahlen direkt. Mit COUNTA erhalten wir eine Anzahl nicht leerer Zellen in "Dingen":
=COUNTA(things)
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"):
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
