Excel-Formel: Überprüfen, ob eine Zelle einen von mehreren Werten enthält, aber andere Werte ausschließt

Angenommen, es gibt zwei Listen mit Werten, und Sie möchten überprüfen, ob die Zelle B3 einen der Werte im Bereich E3:E5 enthält, aber gleichzeitig keinen der Werte im Bereich F3:F4, wie im folgenden Screenshot gezeigt. Dieses Tutorial bietet eine Formel, um diese Aufgabe in Excel schnell zu erledigen, und erklärt die Argumente der Formel.

Allgemeine Formel:

Argumente

Rückgabewert:

Die Formel gibt 1 oder 0 zurück. Wenn die Zelle einen der Werte enthält, die eingeschlossen werden sollen, und keinen der Werte, die ausgeschlossen werden sollen, gibt sie 1 zurück, andernfalls 0. In dieser Formel werden 1 und 0 wie logische Werte WAHR und FALSCH behandelt.

So funktioniert diese Formel

Angenommen, Sie möchten überprüfen, ob die Zelle B3 einen der Werte im Bereich E3:E5 enthält, aber gleichzeitig die Werte im Bereich F3:F4 ausschließt, verwenden Sie bitte die folgende Formel.

Drücken Sie Eingabetaste , um das Prüfergebnis zu erhalten.

Erklärung

Teil 1: (SUMMENPRODUKT(--ISTZAHL(SUCHEN($E$3:$E$5;B3)))>0) überprüft, ob die Zelle Werte in E3:E5 enthält.

SUCHEN-Funktion: Die SUCHEN-Funktion gibt die Position des ersten Zeichens der Textzeichenfolge innerhalb einer anderen zurück. Wenn die SUCHEN-Funktion den passenden Text findet, gibt sie die relative Position zurück, andernfalls gibt sie einen #WERT!-Fehler zurück. Zum Beispiel sucht die Formel SUCHEN($E$3:$E$5;B3) jeden Wert des Bereichs E3:E5 in Zelle B3 und gibt die Position jeder Textzeichenfolge in der Zelle B3 zurück. Das Ergebnis ist ein Array wie dieses: {1;7;12}.

ISTZAHL-Funktion: Die ISTZAHL-Funktion gibt WAHR zurück, wenn eine Zelle eine Zahl ist. Daher gibt ISTZAHL(SUCHEN($E$3:$E$5;B3)) ein Array-Ergebnis wie {wahr,wahr,wahr} zurück, da die SUCHEN-Funktion 3 Zahlen findet.

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

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

Vergleichen Sie abschließend die linke Formel SUMMENPRODUKT(--ISTZAHL(SUCHEN($E$3:$E$5;B3))) mit 0. Solange das Ergebnis der linken Formel größer als 0 ist, wird das Ergebnis WAHR sein, andernfalls wird es FALSCH zurückgeben. Hier gibt es WAHR zurück.

Teil 2: (SUMMENPRODUKT(--ISTZAHL(SUCHEN($F$3:$F$4;B3)))=0) überprüft, ob die Zelle keine Werte in F3:F4 enthält.

Die Formel SUCHEN($F$3:$F$4;B3) sucht jeden Wert im Bereich E3:E5 in Zelle B3 und gibt die Position jeder Textzeichenfolge in der Zelle B3 zurück. Das Ergebnis ist ein Array wie dieses: {#WERT!;#WERT!}.

ISTZAHL(SUCHEN($F$3:$F$4;B3)) gibt ein Array-Ergebnis wie {falsch;falsch} zurück, da die SUCHEN-Funktion 0 Zahlen findet.

--ISTZAHL(SUCHEN($F$3:$F$4;B3)) konvertiert den WAHR-Wert in 1 und den FALSCH-Wert in 0, sodass diese Formel das Array-Ergebnis in {0;0} ändert.

SUMMENPRODUKT-Funktion: Wird verwendet, um Bereiche zu multiplizieren oder Arrays zusammenzufassen und die Summe der Produkte zurückzugeben. Die SUMMENPRODUKT(--ISTZAHL(SUCHEN($F$3:$F$4;B3))) gibt 0+0=0 zurück.

Vergleichen Sie abschließend die linke Formel SUMMENPRODUKT(--ISTZAHL(SUCHEN($F$3:$F$4;B3))) mit 0. Solange das Ergebnis der linken Formel gleich 0 ist, wird das Ergebnis WAHR zurückgeben, andernfalls wird es FALSCH zurückgeben. Hier gibt es WAHR zurück.

Teil 3: Multiplizieren Sie zwei Formeln

=(SUMMENPRODUKT(--ISTZAHL(SUCHEN($E$3:$E$5;B3)))>0)*(SUMMENPRODUKT(--ISTZAHL(SUCHEN($F$3:$F$4;B3)))=0)

=WAHR*WAHR

=1

In dieser Formel werden 1 und 0 wie logische Werte WAHR und FALSCH behandelt.

Beispieldatei

Klicken Sie hier, um die Beispieldatei herunterzuladen.

Verwandte Formeln

• Überprüfen, ob eine Zelle einen bestimmten Text enthält.
  Um zu überprüfen, ob eine Zelle einige Texte im Bereich A enthält, aber nicht die Texte im Bereich B, können Sie eine Array-Formel verwenden, die die Funktionen ANZAHL, SUCHEN und UND in Excel kombiniert.
• Überprüfen, ob eine Zelle eines von vielen Dingen enthält.
  Dieses Tutorial bietet eine Formel, um zu überprüfen, ob eine Zelle einen von mehreren Werten in Excel enthält, und erklärt die Argumente in der Formel sowie deren Funktionsweise.
• Überprüfen, ob eine Zelle eines von mehreren Dingen enthält.
  Angenommen, in Excel gibt es eine Liste von Werten in Spalte E, und Sie möchten überprüfen, ob die Zellen in Spalte B alle Werte in Spalte E enthalten und WAHR oder FALSCH zurückgeben.
• Überprüfen, ob eine Zelle eine Zahl enthält.
  Manchmal möchten Sie möglicherweise überprüfen, ob eine Zelle numerische Zeichen enthält. Dieses Tutorial bietet eine Formel, die WAHR zurückgibt, wenn die Zelle eine Zahl enthält, und FALSCH, wenn die Zelle keine Zahl enthält.