Excel-Formel: Prüfen, ob eine Zelle alle Elemente einer Liste enthält

Angenommen, in Excel befindet sich eine Liste von Werten in Spalte E. Sie möchten prüfen, ob die Zellen in Spalte B sämtliche Werte aus Spalte E enthalten, und entsprechend WAHR oder FALSCH zurückgeben – wie im folgenden Screenshot veranschaulicht. In diesem Tutorial erhalten Sie eine Formel, mit der Sie diese Aufgabe mühelos erledigen können.

Allgemeine Formel:

Argumente

Rückgabewert:

Diese Formel gibt einen logischen Wert zurück: WAHR, wenn die Zelle alle Elemente enthält, andernfalls FALSCH.

Funktionsweise dieser Formel

Beispielsweise enthält Spalte B eine Liste von Textzeichenfolgen, bei denen Sie prüfen möchten, ob sie alle Werte aus dem Bereich E3:E5 enthalten. Verwenden Sie hierfür die folgende Formel.

Drücken Sie die Eingabetaste und ziehen Sie den Ausfüllknauf über die Zellen, die Sie prüfen möchten. „FALSCH“ bedeutet, dass die Zelle nicht alle Werte aus dem Bereich E3:E5 enthält, während „WAHR“ anzeigt, dass die entsprechende Zelle sämtliche Werte enthält.

Erläuterung

SUCHEN-Funktion: Die SUCHEN-Funktion gibt die Position des ersten Zeichens einer Textzeichenfolge innerhalb einer anderen zurück. Wird der gesuchte Text gefunden, liefert die Funktion dessen Position; andernfalls tritt der Fehlerwert #WERT! auf. So sucht beispielsweise die Formel SEARCH($E$3:$E$5,B4) jeden Wert aus dem Bereich E3:E5 in Zelle B4 und gibt die jeweilige Position der gefundenen Textzeichenfolgen in Zelle B4 zurück. Das Ergebnis ist ein Array wie folgt: {1;7;12}

ISTZAHL-Funktion: Die ISTZAHL-Funktion prüft, ob ein Wert eine Zahl ist, und gibt WAHR oder FALSCH zurück. In diesem Fall liefert ISNUMBER(SEARCH($E$3:$E$5,B4)) ein Array-Ergebnis wie {true;true;true}, da die SUCHEN-Funktion drei Zahlen findet.

--ISNUMBER(SEARCH($E$3:$E$5,B4)) wandelt den WAHR-Wert in 1 und den FALSCH-Wert in 0 um. Dadurch verwandelt diese Formel das Array-Ergebnis in {1;1;1}.

SUMMENPRODUKT-Funktion: Multipliziert Bereiche oder Arrays miteinander und gibt die Summe der Produkte zurück. SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) ergibt 1+1+1=3.

ANZAHL2-Funktion: Gibt die Anzahl der nicht leeren Zellen zurück. COUNTA($E$3:$E$5) liefert 3, sodass das Ergebnis von SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) ebenfalls 3 ist. Die gesamte Formel gibt daher WAHR oder FALSCH zurück.

Hinweise:

Die Formel =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) führt keine exakte Prüfung durch. Siehe Screenshot:

Beispieldatei

Klicken Sie, um die Beispieldatei herunterzuladen

Verwandte Formeln

• Zellen zählen, die gleich sind
  Mit der ZÄHLENWENN-Funktion zählen Sie ganz einfach Zellen, die einem bestimmten Wert entsprechen – oder ihn eben nicht enthalten.
• Zellen zählen, die gleich x oder y sind
  Manchmal möchten Sie die Anzahl der Zellen zählen, die eines von zwei Kriterien erfüllen – dafür eignet sich die ZÄHLENWENN-Funktion perfekt.
• Zellen zählen, die gleichzeitig x und y entsprechen
  In diesem Artikel stellen wir die Formel vor, mit der Sie Zellen zählen können, die beide Kriterien gleichzeitig erfüllen.
• Zellen zählen, die ungleich einem bestimmten Wert sind
  Dieser Artikel zeigt Ihnen, wie Sie mit der ZÄHLENWENN-Funktion die Anzahl der Zellen ermitteln, die nicht einem bestimmten Wert entsprechen.