Suchen und Hervorheben von Daten in Excel (mit bedingter Formatierung)

Video ansehen - Suchen und Hervorheben von Daten mit bedingter Formatierung

Wenn Sie mit großen Datensätzen arbeiten, kann es erforderlich sein, eine Suchfunktion zu erstellen, mit der Sie Zellen/Zeilen für den gesuchten Begriff schnell hervorheben können.

Obwohl dies in Excel nicht direkt möglich ist, können Sie Suchfunktionen mithilfe der bedingten Formatierung erstellen.

Angenommen, Sie haben ein Dataset wie unten (im Bild) gezeigt. Es enthält Spalten für Produktname, Vertriebsmitarbeiter und Land.

Jetzt können Sie die bedingte Formatierung verwenden, um nach einem Schlüsselwort zu suchen (indem Sie es in Zelle C2 eingeben) und alle Zellen hervorheben, die dieses Schlüsselwort enthalten.

Etwas wie unten gezeigt (wo ich den Artikelnamen in Zelle B2 eingebe und die Eingabetaste drücke, wird die gesamte Zeile hervorgehoben):

In diesem Tutorial zeige ich Ihnen, wie Sie diese Such- und Hervorhebungsfunktion in Excel erstellen.

Später im Tutorial werden wir etwas fortgeschrittener gehen und sehen, wie man es dynamisch macht (so dass es während der Eingabe in das Suchfeld hervorgehoben wird).

Klicken Sie hier, um die Beispieldatei herunterzuladen und folge mit.

Suchen und markieren Sie übereinstimmende Zellen

In diesem Abschnitt. Ich zeige Ihnen, wie Sie nur die übereinstimmenden Zellen in einem Dataset suchen und hervorheben.

Etwas wie unten gezeigt:

Hier sind die Schritte, um alle Zellen mit dem übereinstimmenden Text zu suchen und hervorzuheben:

1. Wählen Sie das Dataset aus, auf das Sie die bedingte Formatierung anwenden möchten (in diesem Beispiel A4:F19).
2. Klicken Sie auf die Registerkarte Startseite.
3. Klicken Sie in der Gruppe Stile auf Bedingte Formatierung.
4. Klicken Sie in den Dropdown-Optionen auf Neue Regel.
   
5. Klicken Sie im Dialogfeld "Neue Formatierungsregel" auf die Option "Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen".
6. Geben Sie die folgende Formel ein: =A4=$B$1
7. Klicken Sie auf die Schaltfläche „Formatieren…“.
8. Geben Sie die Formatierung an (um Zellen hervorzuheben, die dem gesuchten Schlüsselwort entsprechen).
9. OK klicken.

Geben Sie nun etwas in Zelle B1 ein und drücken Sie die Eingabetaste. Es hebt die übereinstimmenden Zellen im Datensatz hervor, die das Schlüsselwort in B1 enthalten.

Wie funktioniert das?

Bedingte Formatierung wird angewendet, wenn die darin angegebene Formel TRUE zurückgibt.

Im obigen Beispiel überprüfen wir jede Zelle mit der Formel =A4=$B$1

Die bedingte Formatierung überprüft jede Zelle und überprüft, ob der Inhalt der Zelle mit dem in Zelle B1 übereinstimmt. Wenn es gleich ist, gibt die Formel TRUE zurück und die Zelle wird hervorgehoben. Wenn dies nicht der Fall ist, gibt die Formel FALSE zurück und nichts passiert.

Klicken Sie hier, um die Beispieldatei herunterzuladen und folge mit.

Suchen und markieren Sie Zeilen mit übereinstimmenden Daten

Wenn Sie die gesamte Zeile und nicht nur die übereinstimmenden Zellen hervorheben möchten, können Sie dies tun, indem Sie die Formel ein wenig optimieren.

Unten sehen Sie ein Beispiel, in dem die gesamte Zeile hervorgehoben wird, wenn der Produkttyp mit dem in Zelle B1 übereinstimmt.

Hier sind die Schritte zum Suchen und Markieren der gesamten Zeile:

1. Wählen Sie das Dataset aus, auf das Sie die bedingte Formatierung anwenden möchten (in diesem Beispiel A4:F19).
2. Klicken Sie auf die Registerkarte Startseite.
3. Klicken Sie in der Gruppe Stile auf Bedingte Formatierung.
4. Klicken Sie in den Dropdown-Optionen auf Neue Regel.
   
5. Klicken Sie im Dialogfeld "Neue Formatierungsregel" auf die Option "Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen".
6. Geben Sie die folgende Formel ein: =$B4=$B$1
7. Klicken Sie auf die Schaltfläche „Formatieren…“.
8. Geben Sie die Formatierung an (um Zellen hervorzuheben, die dem gesuchten Schlüsselwort entsprechen).
9. OK klicken.

Die obigen Schritte würden im Dataset nach dem angegebenen Element suchen, und wenn das übereinstimmende Element gefunden wird, wird die gesamte Zeile hervorgehoben.

Beachten Sie, dass dadurch nur die Artikelspalte überprüft wird. Wenn Sie hier einen Vertriebsmitarbeiternamen eingeben, funktioniert es nicht. Wenn Sie möchten, dass es für den Namen des Vertriebsmitarbeiters funktioniert, müssen Sie die Formel in ändern =$C4=$B$1

Hinweis: Der Grund dafür, dass die gesamte Zeile und nicht nur die übereinstimmende Zelle hervorgehoben wird, ist, dass wir vor der Spaltenreferenz ($B4) ein $-Zeichen verwendet haben. Wenn nun die bedingte Formatierung Zellen in einer Zeile analysiert, prüft sie, ob der Wert in Spalte B dieser Zeile gleich dem Wert in Zelle B1 ist. Selbst wenn A4 oder B4 oder C4 usw. analysiert werden, wird nur der B4-Wert überprüft (da wir Spalte B mit dem Dollarzeichen gesperrt haben).

Mehr über absolute, relative und gemischte Bezüge erfahren Sie hier.

Zeilen suchen und hervorheben (basierend auf teilweiser Übereinstimmung)

In einigen Fällen möchten Sie möglicherweise Zeilen basierend auf einer teilweisen Übereinstimmung hervorheben.

Wenn Sie beispielsweise Elemente wie White Board, Green Board und Gray Board haben und alle diese anhand des Wortes Board hervorheben möchten, können Sie dies mit der SUCHEN-Funktion tun.

Etwas wie unten gezeigt:

Hier sind die Schritte, um dies zu tun:

1. Wählen Sie das Dataset aus, auf das Sie die bedingte Formatierung anwenden möchten (in diesem Beispiel A4:F19).
2. Klicken Sie auf die Registerkarte Startseite.
3. Klicken Sie in der Gruppe Stile auf Bedingte Formatierung.
4. Klicken Sie in den Dropdown-Optionen auf Neue Regel.
   
5. Klicken Sie im Dialogfeld "Neue Formatierungsregel" auf die Option "Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen".
6. Geben Sie die folgende Formel ein: =UND($B$1””,ISNUMBER(SUCHE($B$1,$B4)))
7. Klicken Sie auf die Schaltfläche „Formatieren…“.
8. Geben Sie die Formatierung an (um Zellen hervorzuheben, die dem gesuchten Schlüsselwort entsprechen).
9. OK klicken.

Wie funktioniert das?

• Die SEARCH-Funktion sucht in allen Zellen einer Reihe nach der Suchzeichenfolge/dem Suchbegriff. Es gibt einen Fehler zurück, wenn das Suchschlüsselwort nicht gefunden wird, und gibt eine Zahl zurück, wenn es eine Übereinstimmung findet.
• Die Funktion ISNUMBER wandelt den Fehler in FALSE und die numerischen Werte in TRUE um.
• UND-Funktion prüft auf eine zusätzliche Bedingung - dass Zelle C2 nicht leer sein darf.

Wenn Sie nun also ein Schlüsselwort in Zelle B1 eingeben und die Eingabetaste drücken, werden alle Zeilen hervorgehoben, die die Zellen enthalten, die dieses Schlüsselwort enthalten.

Bonus-Tipp: Wenn Sie bei der Suche die Groß-/Kleinschreibung beachten möchten, verwenden Sie die FIND-Funktion anstelle von SEARCH.

Klicken Sie hier, um die Beispieldatei herunterzuladen und folge mit.

Dynamische Such- und Hervorhebungsfunktion (Hervorhebungen während der Eingabe)

Mit den oben beschriebenen Tricks zur bedingten Formatierung können Sie auch noch einen Schritt weiter gehen und sie dynamisch gestalten.

Sie können beispielsweise eine Suchleiste erstellen, in der die übereinstimmenden Daten hervorgehoben werden, während Sie in die Suchleiste eingeben.

Etwas wie unten gezeigt:

Dies kann mithilfe von ActiveX-Steuerelementen erfolgen und kann eine gute Funktion beim Erstellen von Berichten oder Dashboards sein.

Unten ist ein Video, in dem ich zeige, wie man das erstellt:

Fanden Sie dieses Tutorial nützlich? Teilen Sie mir Ihre Meinung im Kommentarbereich mit.

Die folgenden Excel-Tutorials könnten Ihnen auch gefallen:

• Dynamischer Excel-Filter - Extrahiert Daten während der Eingabe.
• Erstellen Sie eine Dropdown-Liste mit Suchvorschlägen.
• Erstellen einer Heatmap in Excel.
• Markieren Sie Zeilen basierend auf einem Zellenwert in Excel.
• Markieren Sie die aktive Zeile und Spalte in einem Datenbereich in Excel.
• So markieren Sie leere Zellen in Excel.