Excel-Formel: Überprüfen Sie, ob eine Zelle einen von mehreren Werten enthält, schließen Sie jedoch andere Werte aus
Angenommen, es gibt zwei Wertelisten. Sie möchten überprüfen, ob die Zelle B3 einen der Werte im Bereich E3: E5 enthält. Gleichzeitig enthält sie jedoch keine Werte im Bereich F3: F4 (siehe Abbildung unten). Dieses Tutorial enthält eine Formel, mit der Sie diese Aufgabe in Excel schnell erledigen und die Argumente der Formel erläutern können.
Generische Formel:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,text)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,text)))=0) |
Argumente
Text: the text string you want to check. |
Include: the values you want to check if argument text contains. |
Exclude: the values you want to check if argument text does not contain. |
Rückgabewert:
Die Formel gibt 1 oder 0 zurück. Wenn die Zelle einen der Werte enthält, die eingeschlossen werden müssen, und keine Werte enthält, die ausgeschlossen werden müssen, gibt sie 1 oder 0 zurück. Diese Formel 1 und 0 werden wie logische Werte behandelt Richtig und falsch.
Wie diese Formel funktioniert
Angenommen, Sie möchten überprüfen, ob die Zelle B3 einen der Werte im Bereich E3: E5 enthält, aber gleichzeitig Werte im Bereich F3: F4 ausschließen, verwenden Sie bitte die folgende Formel
=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) |
Presse- Enter Schlüssel, um das Prüfergebnis zu erhalten.
Erläuterung
Teil 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) prüft, ob die Zelle Werte in E3: E5 enthält
SUCHE Funktion: Die SEARCH-Funktion gibt die Position des ersten Zeichens der Textzeichenfolge in einer anderen zurück. Wenn die SEARCH-Funktion den übereinstimmenden Text findet, gibt sie die relative Position zurück. Wenn nicht, gibt sie #VALUE zurück! Error. Zum Beispiel hier die Formel SEARCH($E$3:$E$5,B3) durchsucht jeden Wert des Bereichs E3: E5 in Zelle B3 und gibt die Position jeder Textzeichenfolge in Zelle B3 zurück. Es wird ein Array-Ergebnis wie folgt zurückgegeben: {1; 7; 12}.
ISNUMBER-Funktion: Die ISNUMBER-Funktion gibt TRUE zurück, wenn eine Zelle eine Zahl ist. Damit ISNUMBER(SEARCH($E$3:$E$5,B3)) gibt das Array-Ergebnis als {true, true, true} zurück, da die SEARCH-Funktion 3 Zahlen findet.
--ISNUMBER(SEARCH($E$3:$E$5,B3)) konvertiert den TRUE-Wert in 1 und den FALSE-Wert in 0, sodass diese Formel das Array-Ergebnis in ändert {1; 1; 1}.
SUMMENPRODUKT Funktion: wird verwendet, um Bereiche oder Summenarrays zu multiplizieren und die Summe der Produkte zurückzugeben. Das SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) gibt 1 + 1 + 1 = 3 zurück.
Vergleichen Sie zum Schluss die linke Formel SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) und 0, solange das Ergebnis der linken Formel größer als 0 ist, ist das Ergebnis TRUE oder es wird FALSE zurückgegeben. Hier wird TRUE zurückgegeben.
Teil 2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) prüft, ob die Zelle keine Werte in F3: F4 enthält
Die Formel SUCHE ($ F $ 3: $ F $ 4, B3) sucht jeden Wert im Bereich E3: E5 in Zelle B3 und gibt die Position jeder Textzeichenfolge in Zelle B3 zurück. Es wird ein Array-Ergebnis wie folgt zurückgegeben: {#VALUE !; # VALUE!}.
ISNUMBER(SEARCH($F$3:$F$4,B3)) gibt das Array-Ergebnis als zurück {false; false} als die SEARCH-Funktion findet 0 Nummer.
--ISNUMBER(SEARCH($F$3:$F$4,B3)) konvertiert den TRUE-Wert in 1 und den FALSE-Wert in 0, sodass diese Formel das Array-Ergebnis in ändert {0; 0}.
SUMMENPRODUKT Funktion: wird verwendet, um Bereiche oder Summenarrays zu multiplizieren und die Summe der Produkte zurückzugeben. Das SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) gibt 0 + 0 = 0 zurück.
Vergleichen Sie zum Schluss die linke Formel SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) und 0, solange das Ergebnis der linken Formel gleich 0 ist, gibt das Ergebnis TRUE oder FALSE zurück. Hier wird TRUE zurückgegeben.
Teil 3: Mehrere zwei Formeln
=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)
=TRUE*TRUE
=1
Diese Formel 1 und 0 werden wie die logischen Werte TRUE und FALSE behandelt.
Beispieldatei
Klicken Sie hier, um die Beispieldatei herunterzuladen
Relative Formeln
- Überprüfen Sie, ob eine Zelle einen bestimmten Text enthält
Um zu überprüfen, ob eine Zelle einige Texte in Bereich A enthält, jedoch nicht die Texte in Bereich B, können Sie eine Array-Formel verwenden, die die Funktionen COUNT, SEARCH und AND in Excel kombiniert - Überprüfen Sie, ob die Zelle eines von vielen Dingen enthält
Dieses Lernprogramm enthält eine Formel zum Überprüfen, ob eine Zelle einen von mehreren Werten in Excel enthält, und erläutert die Argumente in der Formel und die Funktionsweise der Formel. - Überprüfen Sie, ob die Zelle eines der Dinge enthält
Angenommen, in Excel befindet sich in Spalte E eine Liste mit Werten. Sie möchten überprüfen, ob die Zellen in Spalte B alle Werte in Spalte E enthalten, und TRUE oder FALSE zurückgeben. - Überprüfen Sie, ob die Zelle eine Nummer enthält
Manchmal möchten Sie möglicherweise überprüfen, ob eine Zelle numerische Zeichen enthält. Dieses Tutorial enthält eine Formel, die TRUE zurückgibt, wenn die Zelle eine Nummer enthält, FALSE, wenn die Zelle keine Nummer enthält.
Die besten Tools für die Office-Produktivität
Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...
Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).
- Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
- Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
- Erhöht Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
- Bringt effiziente Tabs in Office (einschließlich Excel), genau wie Chrome, Edge und Firefox.