Excel-Formel: Überprüfen Sie, ob die Zelle viele 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 (siehe Abbildung unten). Hier in diesem Tutorial finden Sie eine Formel zur Lösung dieser Aufgabe.
Generische Formel:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) |
Argumente
Things: the list of values that you want to use to check if argument text contains. |
Text: the cell or text string you want to check if containing argument things. |
Rückgabewert:
Diese Formel gibt den logischen Wert FALSE zurück: Die Zelle enthält nicht alle Dinge. WAHR: Die Zelle enthält alle Dinge.
Wie diese Formel funktioniert
In Spalte B finden Sie beispielsweise eine Liste von Textzeichenfolgen, die Sie überprüfen möchten, ob sie alle Werte im Bereich E3: E5 enthalten. Verwenden Sie dazu die folgende Formel.
=SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))=COUNTA($E$3:$E$5) |
Presse- Enter Ziehen Sie mit der Taste den Füllgriff ü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 TURE angibt, dass die relative Zelle alle Werte enthält.
Erläuterung
SEARCH-Funktion: Die SEARCH-Funktion gibt die Position des ersten Zeichens der Textzeichenfolge in einem anderen zurück. Wenn die SEARCH-Funktion den Text findet, gibt sie die Position zurück. Wenn nicht, gibt sie #VALUE zurück! Error. Zum Beispiel hier die Formel SEARCH($E$3:$E$5,B4) durchsucht jeden Wert des Bereichs E3: E5 in Zelle B4 und gibt die Position jeder Textzeichenfolge in Zelle B4 zurück. Es wird ein Array-Ergebnis wie folgt zurückgegeben: {1;7;12}
ISNUMBER-Funktion: Die ISNUMBER-Funktion prüft, ob ein Wert eine Zahl ist, und gibt TRUE o False zurück. Hier ISNUMBER(SEARCH($E$3:$E$5,B4)) gibt das Array-Ergebnis als zurück {true;true;true} da die SEARCH-Funktion 3 Zahlen findet.
--ISNUMBER(SEARCH($E$3:$E$5,B4)) konvertiert den TRUE-Wert in 1 und den FALSE-Wert in 0, sodass diese Formel das Array-Ergebnis in ändert {1;1;1}.
SUMPRODUCT-Funktion: Wird verwendet, um Bereiche oder Summenarrays miteinander zu multiplizieren und die Summe der Produkte zurückzugeben. Das SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) gibt 1 + 1 + 1 = 3 zurück.
COUNTA-Funktion: Gibt die Anzahl der nicht leeren Zellen zurück. COUNTA($E$3:$E$5) gibt 3 zurück, also das Ergebnis von SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) gleich 3 ist, gibt die gesamte Formel TRUE oder FALSE zurück.
Bemerkungen:
Die Formel =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) prüft nicht genau. Siehe Screenshot:
Beispieldatei
Klicken Sie hier, um die Beispieldatei herunterzuladen
Relative Formeln
- Zähle die Zellen gleich
Mit der COUNTIF-Funktion können Sie Zellen zählen, die einem bestimmten Wert entsprechen oder nicht. - Zählen Sie Zellen, die x oder y entsprechen
In einigen Fällen 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 COUNTIF-Funktion verwenden. - Zähle Zellen gleich x und y
In diesem Artikel wird die Formel zum Zählen von Zellen vorgestellt, die inzwischen zwei Kriterien entsprechen. - Zähle Zellen ungleich
In diesem Artikel wird erläutert, wie Sie mit der COUNTIF-Funktion die Anzahl der Zellen zählen, die einem bestimmten Wert nicht entsprechen
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.