Excel-Formel: Prüfen, ob eine Zelle alle von vielen Dingen enthält

Angenommen, in Excel gibt es eine Liste von Werten in Spalte E, und Sie möchten prüfen, ob die Zellen in Spalte B alle Werte aus Spalte E enthalten, und TRUE oder FALSE zurückgeben, wie im folgenden Screenshot gezeigt. In diesem Tutorial wird eine Formel zur Lösung dieser Aufgabe bereitgestellt.

Allgemeine Formel:

Argumente

Rückgabewert:

Diese Formel gibt einen logischen Wert zurück: FALSE - die Zelle enthält nicht alle Dinge; TRUE - die Zelle enthält alle Dinge.

Wie diese Formel funktioniert

Zum Beispiel, in Spalte B gibt es eine Liste von Textstrings, die Sie überprüfen möchten, ob sie alle Werte im Bereich E3:E5 enthalten. Bitte verwenden Sie die folgende Formel.

Drücken Sie Eingabetaste Taste, ziehen Sie den Ausfüllkursor über die Zellen, die Sie überprüfen möchten. FALSE zeigt an, dass die Zelle nicht alle Werte im Bereich E3:E5 enthält, während TRUE anzeigt, dass die entsprechende Zelle alle Werte enthält.

Erklärung

SUCHEN-Funktion: Die SUCHEN-Funktion gibt die Position des ersten Zeichens einer Textzeichenfolge innerhalb einer anderen zurück. Wenn die SUCHEN-Funktion den Text findet, gibt sie die Position zurück, andernfalls gibt sie einen #WERT!-Fehler zurück. Zum Beispiel gibt hier die Formel SUCHEN($E$3:$E$5;B4) jeden Wert des Bereichs E3:E5 in Zelle B4 wieder und gibt die Position jeder Textzeichenfolge in Zelle B4 zurück. Es wird ein Array-Ergebnis wie folgt zurückgegeben: {1;7;12}

ISTZAHL-Funktion: Die ISTZAHL-Funktion überprüft, ob ein Wert eine Zahl ist, und gibt WAHR oder FALSCH zurück. Hier ISTZAHL(SUCHEN($E$3:$E$5;B4)) wird ein Array-Ergebnis zurückgegeben als {true;true;true} da die SUCHEN-Funktion 3 Zahlen findet.

--ISTZAHL(SUCHEN($E$3:$E$5;B4)) wandelt den WAHR-Wert in 1 um und den FALSCH-Wert in 0, sodass diese Formel das Array-Ergebnis ändert in {1;1;1}.

SUMMENPRODUKT-Funktion: Wird verwendet, um Bereiche zu multiplizieren oder Arrays zusammenzufassen und die Summe der Produkte zurückzugeben. Das SUMMENPRODUKT(--ISTZAHL(SUCHEN($E$3:$E$5;B4))) gibt 1+1+1=3 zurück.

ANZAHL2-Funktion: Gibt die Anzahl der nicht leeren Zellen zurück. ANZAHL2($E$3:$E$5) gibt 3 zurück, also ist das Ergebnis von SUMMENPRODUKT(--ISTZAHL(SUCHEN($E$3:$E$5;B4))) gleich 3, die gesamte Formel gibt WAHR zurück, andernfalls FALSCH.

Anmerkungen:

Die Formel =SUMMENPRODUKT(--ISTZAHL(SUCHEN(Dinge;Text)))=ANZAHL2(Dinge) ist nicht exakt prüfend. Siehe Screenshot:

Beispieldatei

Klicken Sie hier, um die Beispieldatei herunterzuladen

Relative Formeln

• Zellen zählen, die gleich sind
  Mit der ZÄHLENWENN-Funktion können Sie Zellen zählen, die gleich oder nicht gleich einem bestimmten Wert sind.
• Zellen zählen, die gleich x oder y sind
  Manchmal möchten Sie möglicherweise die Anzahl der Zellen zählen, die eines von zwei Kriterien erfüllen. In diesem Fall können Sie die ZÄHLENWENN-Funktion verwenden.
• Zellen zählen, die gleich x und y sind
  Dieser Artikel stellt die Formel vor, um Zellen zu zählen, die gleichzeitig zwei Kriterien entsprechen.
• Zellen zählen, die nicht gleich sind
  Dieser Artikel behandelt, wie Sie die ZÄHLENWENN-Funktion verwenden, um die Anzahl der Zellen zu zählen, die nicht gleich einem bestimmten Wert sind.