Wie prüft man, ob eine Zelle einen von mehreren bestimmten Werten in Excel enthält?
In vielen geschäftlichen, analytischen oder datenüberprüfenden Szenarien verfügen Sie möglicherweise über eine Liste von Textzeichenfolgen in Spalte A und möchten prüfen, ob jede Zelle einen der im Bereich D2:D7 angegebenen Werte enthält. Beispielsweise ist es bei Umfragedaten, Protokollen oder Produktlisten entscheidend, festzustellen, ob Einträge relevante Stichwörter, Produktcodes oder unzulässige Begriffe enthalten. Wenn eine Zelle eines der Elemente aus Ihrer vorgegebenen Liste enthält, gibt Excel WAHR zurück; andernfalls FALSCH, wie im folgenden Screenshot veranschaulicht. Dieser Artikel stellt praktische Methoden vor, um zu prü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.
Prüfen, ob eine Zelle einen von mehreren Werten aus einer Liste mithilfe von Formeln enthält
Um zu prüfen, ob eine Zelle einen der Textwerte aus einem anderen Bereich enthält, verwenden Sie eine Matrixformel – sie arbeitet auch bei großen Datensätzen effizient. Diese Methode ist besonders nützlich, wenn Sie die Ergebnisse als logische Werte ()WAHR/FALSCH) für weitere Formeln, Filter oder logische Tests benötigen. Die 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 Originaldaten) und ziehen Sie den Ausfüllknauf nach unten, um sie bei Bedarf auf weitere Zellen anzuwenden. Die Formel gibt WAHR zurück, wenn die Zelle einen der Textwerte aus Ihrem begrenzten Bereich enthält; andernfalls FALSCH. Siehe Screenshot:
Tipps und Hinweise:
- Diese Formel führt eine Prüfung ohne Berücksichtigung der Groß-/Kleinschreibung durch. Wenn die Groß-/Kleinschreibung relevant ist, empfiehlt sich der Einsatz einer Hilfsspalte oder die fortgeschrittene Kombination von Formeln.
- Um stattdessen ein Ergebnis „Ja“ oder „Nein“ anstelle von WAHR/FALSCH zu erhalten, verwenden Sie die folgende angepasste Formel:
- D2:D7 ist Ihr Wertebereich (die „Liste“); A2 ist die zu prüfende Zelle.
- Achten Sie auf leere Zellen oder nicht-textbasierte Daten, denn die SUCHEN-Funktion benötigt eine gültige Texteingabe – andernfalls können leere Werte zu unerwarteten Ergebnissen („WAHR“) führen.
Die Übereinstimmungen anzeigen, wenn eine Zelle einen von mehreren Werten aus einer Liste mithilfe von Formeln enthält
Manchmal ist es aussagekräftiger, direkt anzuzeigen, welche Werte aus der Liste tatsächlich in jeder Zelle vorkommen, statt nur ein WAHR/FALSCH-Ergebnis zu liefern. Beispielsweise möchten Sie beim Durchsuchen von Produktbeschreibungen oder Kommentaren nach bestimmten Stichwörtern alle gefundenen Treffer für eine weitergehende Analyse oder Berichterstattung erhalten. Mit der folgenden Formel lassen sich alle übereinstimmenden Werte – durch Kommas getrennt – wie hier dargestellt anzeigen:
Geben Sie diese Formel in eine leere Zelle ein (z. B. B2), und sie listet alle Werte aus D2:D7, die in A2 gefunden wurden, durch Kommas getrennt auf:
Hinweis: Dabei ist D2:D7 die Sammlung der zu suchenden Werte und A2 die zu durchsuchende Zelle.
Drücken Sie nach der Eingabe der Formel Strg + Umschalt + Enter. Anschließend ziehen Sie einfach den Ausfüllknauf nach unten, um die Formel wie im Ergebnisscreenshot gezeigt auf weitere Zeilen anzuwenden:
- Die TEXTVERKETTEN-Funktion ist nur in Excel 2019 und Office 365 verfügbar. Verwenden Sie in früheren Excel-Versionen die folgende Matrixformel: Geben Sie sie in eine leere Zelle ein und drücken Sie anschließend Strg + Umschalt + Enter.
Ziehen Sie die Formel nach rechts, um so viele Spalten wie möglich zu erfassen und alle potenziellen Übereinstimmungen abzudecken – anschließend ziehen Sie sie für jede Zeile nach unten. Enthält eine Zeile weniger Übereinstimmungen, bleiben die zusätzlichen Spalten leer. Dieses Format eignet sich besonders gut, wenn Sie die Übereinstimmungen in separaten Spalten aufgelistet benötigen:
Sollten Fehler auftreten, überprüfen Sie die Bereiche erneut, stellen Sie sicher, dass der D2:D7-Bereich korrekt ist, und vergewissern Sie sich, dass Sie das richtige Trennzeichen für Ihre Region (Komma oder Semikolon) verwenden.
Die Übereinstimmungen hervorheben, wenn eine Zelle einen von mehreren Werten aus einer Liste mit einer praktischen Funktion enthält
Wenn Sie Stichwörter oder Ausdrücke, die mit einem Wert aus Ihrer Liste übereinstimmen, innerhalb jeder Zelle visuell hervorheben möchten, können Sie so wichtige Daten für Überprüfung oder Maßnahmen sofort erkennen. Die Schlüsselwortmarkierung-Funktion in Kutools für Excel wurde genau für dieses Szenario entwickelt und ermöglicht es Ihnen, jede Instanz Ihrer angegebenen Begriffe im Datenbereich blitzschnell hervorzuheben – ganz ohne Formeln oder VBA-Code. Gerade bei großen Tabellen oder komplexen Datensätzen ist diese Funktion besonders wertvoll, wenn eine manuelle Prüfung schlichtweg unpraktisch wäre.
Befolgen Sie nach der Installation von Kutools für Exceldiese Schritte:
1. Wechseln Sie zu Kutools > Text > Schlüsselwortmarkierung, um das Dialogfeld wie gezeigt zu öffnen:
2. Führen Sie im Schlüsselwortmarkierung-Dialogfeld Folgendes aus:
- Wählen Sie Ihren Ziel-Datenbereich aus dem Bereich-Feld aus.
- Wählen Sie die Stichwörter aus, oder geben Sie sie manuell in das Stichwort-Feld ein (durch Kommas getrennt).
- Geben Sie eine Hervorhebungsfarbe mithilfe der Schlüsselwort-Schriftfarbe-Option an.
3. Klicken Sie auf OK. Alle übereinstimmenden Wörter innerhalb des ausgewählten Bereichs werden mit Ihrer gewählten Schriftfarbe hervorgehoben:
- Diese Funktion passt die Anzeigeformatierung der übereinstimmenden Stichwörter direkt an und macht die Überprüfung oder Weitergabe der Ergebnisse für Teams, die Formelausgaben möglicherweise nicht verstehen, intuitiver.
- Sie ist besonders effektiv, wenn die Stichwortliste oder der Textbereich sehr umfangreich ist oder Sie mehrere Kriterien gleichzeitig hervorheben müssen.
- Überprüfen Sie Ihre Auswahl des Bereichs und Ihre Stichworteingaben stets sorgfältig, bevor Sie bestätigen, um unbeabsichtigte Hervorhebungen zu vermeiden.
Prüfen Sie mithilfe der bedingten Formatierung, ob eine Zelle einen von mehreren Werten enthält.
Die integrierte Excel-Funktion Bedingte Formatierung verwenden ist ein weiterer effektiver Ansatz, um Zellen hervorzuheben, die einen beliebigen Wert aus Ihrer angegebenen Liste enthalten. Diese Lösung eignet sich besonders gut, um relevante Zeilen auf einen Blick visuell zu identifizieren – ideal etwa bei der Datenprüfung, Fehleranalyse oder Compliance-Aufgaben. Im Gegensatz zur Schlüsselwortmarkierung-Funktion von Kutools benötigt dieser Ansatz keine Add-Ins und ist daher in jeder Standard-Excel-Installation sofort verfügbar.
So verwenden Sie Bedingte Formatierung verwenden mit einer Formel:
- Wählen Sie die Zellen in Ihrem Datenbereich (z. B.)A2:A20) aus, die Sie überwachen möchten.
- Wechseln Sie zur Start-Registerkarte, und klicken Sie auf Bedingte Formatierung verwenden > Neue Regel.
- Wählen Sie im Dialogfeld „Neue Formatierungsregel“ die Option Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen.
- Geben Sie diese Formel ein, wobei D2:D7 Ihre Werte enthält und A2 die erste Datenzelle ist:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0- Klicken Sie auf Format, legen Sie die gewünschte Formatierung (z. B. Füllfarbe) fest und klicken Sie auf OK.
Alle Zellen, die ein Element aus der Liste D2:D7 enthalten, werden nun automatisch hervorgehoben.
- Diese Methode ist dynamisch: Sobald Sie den Bereich D2:D7 aktualisieren, passt sich die Formatierung automatisch an.
- Die bedingte Formatierung ist schreibgeschützt: Sie markiert Zellen visuell, liefert jedoch keine Ergebnisse in einer separaten Spalte oder für weitere Berechnungen.
- Die formelbasierte bedingte Formatierung ist leistungsstark, kann bei sehr großen Datensätzen jedoch aufgrund wiederholter Berechnungen zu Leistungseinbußen führen.
- Beachten Sie, dass SUCHEN nicht zwischen Groß- und Kleinschreibung unterscheidet. Um eine groß-/kleinschreibungsabhängige Suche zu ermöglichen, sind fortgeschrittenere Techniken oder Hilfsspalten erforderlich.
Weitere verwandte Artikel:
- Vergleichen von zwei oder mehr Textzeichenfolgen in Excel
- Wenn Sie zwei oder mehr Textzeichenfolgen in einem Arbeitsblatt entweder unter Berücksichtigung oder ohne Beachtung der Groß-/Kleinschreibung vergleichen möchten – wie im folgenden Screenshot gezeigt –, erfahren Sie in diesem Artikel, wie Sie diese Aufgabe mithilfe nützlicher Formeln in Excel lösen können.
- Wenn Zelle Text enthält, dann in Excel anzeigen
- Wenn Sie eine Liste von Textzeichenfolgen in Spalte A und eine Zeile mit Stichwörtern haben, müssen Sie nun prüfen, ob die Stichwörter in der Textzeichenfolge vorkommen. Wenn die Stichwörter in der Zelle erscheinen, wird das Stichwort angezeigt; andernfalls wird eine leere Zelle angezeigt, wie im folgenden Screenshot dargestellt.
- Anzahl der Stichwörter zählen, die eine Zelle basierend auf einer Liste enthält
- Möchten Sie die Anzahl der Stichwörter zählen, die in einer Zelle auf Grundlage einer Liste von Zellen vorkommen, hilft Ihnen die Kombination der Funktionen SUMMENPRODUKT, ISTZAHL und SUCHEN, dieses Problem in Excel elegant zu lösen.
- Suchen und Ersetzen mehrere Werte in Excel
- Normalerweise hilft Ihnen die Suchen-und-Ersetzen-Funktion dabei, einen bestimmten Text zu finden und durch einen anderen zu ersetzen. Manchmal müssen Sie jedoch mehrere Werte gleichzeitig suchen und ersetzen – beispielsweise alle „Excel“-Texte durch „Excel2019“, „Outlook“ durch „Outlook2019“ usw., wie im folgenden Screenshot gezeigt. In diesem Artikel stelle ich eine Formel vor, mit der Sie diese Aufgabe in Excel effizient erledigen können.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins