Direkt zum Inhalt

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.
doc prüfen, ob eines der Dinge enthalten ist, aber 1 ausschließen

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.
doc prüfen, ob eines der Dinge enthalten ist, aber 2 ausschließen

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.
doc prüfen, ob eines der Dinge enthalten ist, aber 3 ausschließen

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.
doc prüfen, ob eines der Dinge enthalten ist, aber 4 ausschließen

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

doc BeispielKlicken Sie hier, um die Beispieldatei herunterzuladen


Relative Formeln


Die besten Tools für die Office-Produktivität

Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben

🤖 Kutools KI-Assistent: Revolutionieren Sie die Datenanalyse basierend auf: Intelligente Ausführung   |  Code generieren  |  Erstellen Sie benutzerdefinierte Formeln  |  Analysieren Sie Daten und erstellen Sie Diagramme  |  Rufen Sie Kutools-Funktionen auf...
Beliebte Funktionen: Suchen, markieren oder identifizieren Sie Duplikate  |  Leere Zeilen löschen  |  Kombinieren Sie Spalten oder Zellen, ohne Daten zu verlieren  |  Runde ohne Formel ...
Super VLookup: Mehrere Kriterien  |  Mehrfachwert  |  Über mehrere Blätter hinweg  |  Unscharfe Suche...
Adv. Dropdown-Liste: Einfache Dropdown-Liste  |  Abhängige Dropdown-Liste  |  Mehrfachauswahl Dropdown-Liste...
Spaltenmanager: Fügen Sie eine bestimmte Anzahl von Spalten hinzu  |  Spalten verschieben  |  Schalten Sie den Sichtbarkeitsstatus ausgeblendeter Spalten um  Spalten vergleichen mit Wählen Sie Gleiche und Unterschiedliche Zellen ...
Ausgewählte Funktionen: Rasterfokus  |  Designansicht  |  Große Formelleiste  |  Arbeitsmappen- und Blattmanager | Ressourcen (Autotext)  |  Datumsauswahl  |  Arbeitsblätter kombinieren  |  Zellen verschlüsseln/entschlüsseln  |  Senden Sie E-Mails nach Liste  |  Superfilter  |  Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Toolsets12 Text Tools (Text hinzufügen, Zeichen entfernen ...)  |  50+ Chart Typen (Gantt-Diagramm ...)  |  40+ Praktisch Formeln (Berechnen Sie das Alter basierend auf dem Geburtstag ...)  |  19 Einfügen Tools (QR-Code einfügen, Bild aus Pfad einfügen ...)  |  12 Umwandlung (Conversion) Tools (Zahlen zu Wörtern, Currency Conversion ...)  |  7 Zusammenführen & Teilen Tools (Erweiterte Zeilen kombinieren, Teilen Sie Excel-Zellen ...)  |  ... und mehr

Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...

Beschreibung


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.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
<p>avec les fonctions en français ça donne : SOMMEPROD(--ESTNUM(CHERCHE(Liste;B2)))</p>
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations