Wie kann man in Excel überprüfen, ob eine Zelle einen von mehreren Werten enthält?
In vielen Geschäftsszenarien, bei der Analyse oder der Datenprüfung haben Sie möglicherweise eine Liste von Textzeichenfolgen in Spalte A und möchten prüfen, ob jede Zelle einen der angegebenen Werte enthält, wie z. B. diejenigen im Bereich D2:D7. Zum Beispiel ist es bei Umfragedaten, Protokollen oder Produktlisten entscheidend zu identifizieren, ob Einträge relevante Schlüsselwörter, Produktcodes oder verbotene Begriffe enthalten. Wenn eine Zelle eines der Elemente aus Ihrer angegebenen Liste enthält, können Sie Excel dazu bringen, „Ja“ zurückzugeben; andernfalls „Nein“, wie im folgenden Screenshot dargestellt. Dieser Artikel erklärt praktische Methoden, um zu überprüfen, ob eine Zelle einen von mehreren Werten aus einem anderen Bereich enthält, und bietet verschiedene Ansätze für unterschiedliche Excel-Versionen und Benutzeranforderungen.
Überprüfen Sie mit Formeln, ob eine Zelle einen von mehreren Werten aus einer Liste enthält.
Um festzustellen, ob eine Zelle einen der Textwerte aus einem anderen Bereich enthält, können Sie eine Array-Formel verwenden, die selbst bei großen Datensätzen effizient funktioniert. Diese Methode ist besonders nützlich, wenn Sie Ergebnisse als logische Werte (Ja/Nein) für weitere Formeln, Filterungen oder logische Tests benötigen. Diese Technik funktioniert in den meisten modernen Excel-Versionen.
Geben Sie die folgende Formel in eine leere Zelle ein (z. B. in Zelle B2, neben Ihren ursprünglichen Daten), und ziehen Sie dann den Ausfüllkästchen nach unten, um sie auf andere Zellen anzuwenden. Wenn die Zelle einen der Textwerte im angegebenen Bereich enthält, gibt sie Ja zurück; andernfalls gibt sie Nein zurück. Siehe Screenshot:
Tipps und Hinweise:
- Diese Formel führt eine Überprüfung durch, die nicht zwischen Groß- und Kleinschreibung unterscheidet. Wenn Sie eine Unterscheidung nach Groß-/Kleinschreibung benötigen, sollten Sie eine Hilfsspalte verwenden oder Formeln auf fortgeschrittene Weise kombinieren.
- Um stattdessen ein „Ja“ oder „Nein“-Ergebnis anstelle von Ja/Nein zu erhalten, verwenden Sie die folgende angepasste Formel:
- D2:D7 ist Ihr Wertebereich (die „Liste“); A2 ist die zu testende Zelle.
- Seien Sie vorsichtig mit leeren Zellen oder Nicht-Text-Daten, da die SUCHEN-Funktion eine gültige Texteingabe erfordert, und leere Werte können zu unerwarteten Ergebnissen führen („Ja“).
Zeigen Sie die Übereinstimmungen an, wenn eine Zelle einen von mehreren Werten aus einer Liste mit Formeln enthält.
Manchmal kann es informativer sein, anzuzeigen, welche der Werte aus der Liste tatsächlich in jeder Zelle erscheinen, anstatt einfach ein Ja/Nein-Ergebnis zu zeigen. Zum Beispiel möchten Sie beim Durchsuchen von Produktbeschreibungen oder Kommentaren nach bestimmten Schlüsselwörtern alle gefundenen Werte für weitere Analysen oder Berichte zurückgeben. Sie können die folgende Formel verwenden, um alle übereinstimmenden Werte durch Kommas getrennt anzuzeigen, wie hier gezeigt:
Geben Sie diese Formel in eine leere Zelle ein (z. B. B2), und sie listet alle Werte aus D2:D7 auf, die innerhalb von A2 gefunden wurden, durch Kommas getrennt:
Hinweis: Hier ist D2:D7 die Sammlung von Werten, die gesucht werden sollen, und A2 ist die Zelle, in der gesucht wird.
Nachdem Sie die Formel eingegeben haben, drücken Sie Strg + Umschalt + Enter. Dann können Sie das Ausfüllkästchen nach unten ziehen, um die Formel auf andere Zeilen anzuwenden, wie im Ergebnis-Screenshot gezeigt:
- Die TEXTVERBINDEN-Funktion ist nur in Excel 2019 und Office 365 verfügbar. In früheren Excel-Versionen verwenden Sie die folgende Array-Formel, indem Sie sie in eine leere Zelle eingeben und dann Strg + Umschalt + Enter drücken:
Ziehen Sie die Formel nach rechts, um so viele Spalten wie möglich abzudecken, um alle möglichen Übereinstimmungen zu erfassen, und ziehen Sie sie dann für jede Zeile nach unten. Zusätzliche Spalten bleiben dort leer, wo es weniger Übereinstimmungen gibt. Dieses Format ist besonders nützlich, wenn Sie die Übereinstimmungen in separaten Spalten aufgelistet haben möchten:
Wenn Sie Fehler auftreten, überprüfen Sie die Bereiche noch einmal, stellen Sie sicher, dass der Bereich D2:D7 korrekt ist, und bestätigen Sie, dass Sie das richtige Trennzeichen für Ihre Region verwenden (Komma oder Semikolon).
Heben Sie die Übereinstimmungen hervor, wenn eine Zelle einen von mehreren Werten aus einer Liste mit einer praktischen Funktion enthält.
Wenn Sie Schlüsselwörter oder Phrasen visuell hervorheben müssen, die einem Wert aus Ihrer Liste in jeder Zelle entsprechen, kann dies dabei helfen, wichtige Daten zur Überprüfung oder Aktion hervorzuheben. Die Funktion „Schlüsselwörter markieren“ in Kutools für Excel ist für dieses Szenario konzipiert und ermöglicht es Ihnen, schnell alle Instanzen Ihrer angegebenen Wörter im Datenbereich hervorzuheben, ohne Formeln oder VBA-Code schreiben zu müssen. In großen Tabellenblättern oder komplexen Datensätzen ist diese Funktion besonders hilfreich, wenn eine manuelle Prüfung nicht praktikabel ist.
Nach der Installation von Kutools für Excel gehen Sie wie folgt vor:
1. Gehen Sie zu Kutools > Text > Schlüsselwort markieren, um das Dialogfeld zu öffnen, wie gezeigt:
2. Führen Sie im Dialogfeld „Schlüsselwort markieren“ Folgendes aus:
- Wählen Sie Ihren Ziel-Datenbereich im Bereich aus.
- Wählen Sie oder geben Sie manuell die Schlüsselwörter im Feld Schlüsselwort ein (durch Kommas getrennt).
- Legen Sie eine Hervorhebungsfarbe mit der Option Schlüsselwort Schriftfarbe fest.
3. Klicken Sie auf OK. Alle übereinstimmenden Wörter im ausgewählten Bereich werden mit Ihrer gewählten Schriftfarbe hervorgehoben:
- Diese Funktion ändert direkt die Anzeigeformatierung der übereinstimmenden Schlüsselwörter, was die Überprüfung oder das Teilen von Ergebnissen intuitiver für Teams macht, die Formelausgaben möglicherweise nicht verstehen.
- Sie ist besonders effektiv, wenn die Schlüsselwortliste oder der Textrahmen sehr groß ist oder Sie mehrere Kriterien gleichzeitig hervorheben müssen.
- Überprüfen Sie immer Ihren ausgewählten Bereich und die Schlüsseleinträge, bevor Sie bestätigen, um unbeabsichtigte Hervorhebungen zu vermeiden.
Überprüfen Sie mit bedingter Formatierung, ob eine Zelle einen von mehreren Werten enthält.
Excels integrierte bedingte Formatierung ist ein weiterer effektiver Ansatz, um Zellen hervorzuheben, die einen beliebigen Wert aus Ihrer angegebenen Liste enthalten. Diese Lösung ist nützlich, um relevante Zeilen auf einen Blick visuell zu identifizieren, insbesondere bei der Datenprüfung, der Fehlererkennung oder Compliance-Aufgaben. Im Gegensatz zur Funktion „Schlüsselwörter markieren“ von Kutools erfordert dieser Ansatz keine Add-Ins, was ihn in Standard-Excel-Installationen zugänglich macht.
So verwenden Sie die bedingte Formatierung mit einer Formel:
- Wählen Sie die Zellen in Ihrem Datenbereich aus (z. B. A2:A20), die Sie überwachen möchten.
- Gehen Sie zur Registerkarte Start, klicken Sie auf Bedingte Formatierung > Neue Regel.
- Wählen Sie im Dialogfeld Neue Formatierungsregel die Option Formel verwenden, um festzulegen, welche Zellen formatiert werden sollen.
- Geben Sie diese Formel ein, wobei davon ausgegangen wird, dass D2:D7 Ihre Werte enthält und A2 die erste Datenzelle ist:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0- Klicken Sie auf Formatieren, legen Sie Ihre gewünschte Formatierung fest (z. B. Füllfarbe) und drücken Sie OK.
Alle Zellen, die ein Element aus der Liste D2:D7 enthalten, werden nun automatisch hervorgehoben.
- Diese Methode ist dynamisch: Wenn Sie den Bereich D2:D7 aktualisieren, passt sich die Formatierung entsprechend an.
- Die bedingte Formatierung ist nur zur Ansicht: Sie markiert Zellen visuell, liefert aber keine Ergebnisse in einer separaten Spalte oder für weitere Berechnungen.
- Formelbasierte bedingte Formatierung ist leistungsstark, aber bei sehr großen Datensätzen kann die Leistung aufgrund wiederholter Berechnungen langsamer werden.
- Beachten Sie, dass SUCHEN nicht zwischen Groß- und Kleinschreibung unterscheidet. Um diesen Ansatz groß-/kleinschreibungssensitiv zu machen, sind fortgeschrittene Techniken oder Hilfsspalten erforderlich.
Weitere Artikel:
- Vergleichen Sie zwei oder mehr Textzeichenfolgen in Excel.
- Wenn Sie zwei oder mehr Textzeichenfolgen in einem Arbeitsblatt vergleichen möchten, sei es mit oder ohne Beachtung der Groß-/Kleinschreibung, wie im folgenden Screenshot gezeigt, werde ich in diesem Artikel einige nützliche Formeln vorstellen, um diese Aufgabe in Excel zu bewältigen.
- Wenn eine Zelle Text enthält, dann anzeigen in Excel.
- Wenn Sie eine Liste von Textzeichenfolgen in Spalte A und eine Reihe von Schlüsselwörtern haben und prüfen möchten, ob die Schlüsselwörter in der Textzeichenfolge erscheinen, wird das Schlüsselwort angezeigt, falls es in der Zelle erscheint; andernfalls bleibt die Zelle leer, wie im folgenden Screenshot gezeigt.
- Anzahl der Schlüsselwörter, die eine Zelle basierend auf einer Liste enthält.
- Wenn Sie die Anzahl der Schlüsselwörter zählen möchten, die in einer Zelle basierend auf einer Liste von Zellen erscheinen, kann die Kombination der Funktionen SUMMENPRODUKT, ISTZAHL und SUCHEN helfen, dieses Problem in Excel zu lösen.
- Suchen und Ersetzen mehrerer Werte in Excel.
- Normally, the Find and Replace feature can help you to find a specific text and replace it with another one, but, sometimes, you may need to find and replace multiple values simultaneously. For example, to replace all “Excel” text to “Excel2019”, “Outlook” to “Outlook2019” and so on as below screenshot shown. This article, I will introduce a formula for solving this task in Excel.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins