Ein intelligentes Suchfeld sorgt für den Überblick: Gesuchte Einträge in einer Liste automatisch farbig hervorheben

Wer in einer Liste nach bestimmten Einträgen sucht, kann dies über einen Filter erledigen. Der lässt sich über Strg+Umschalt+L schnell ein- und ausschalten. Was aber, wenn in einer umfangreichen Liste nicht klar ist, in welcher Spalte der gesuchte Begriff steht?

Ein Filter hat zudem zwei Nachteile: a) muss er wieder gelöscht werden und b) geht beim Filtern der Zusammenhang zu den anderen Daten verloren, was manchmal hinderlich ist.

Die folgende Lösung schafft hier Abhilfe. In das Suchfeld – hier in Zelle C1 – gebe ich den gewünschten Begriff oder auch nur einen Teil davon ein. Sofort werden alle Datensätze farbig hervorgehoben, in denen der Suchtext in einer der Spalten vorkommt. Bei Bedarf kann ich nur diese Datensätze anzeigen lassen, indem ich im Filter die Option Nach Farbe filtern wähle.

Vorschau auf die fertige Lösung

Bild 1: Im Suchfeld in C1 wird nach der Zeichenkette “mo” gesucht und im Ergebnis dessen werden automatisch alle Datensätze für den Artikel “Monitor” sowie des Bearbeiters MO farbig hervorgehoben. Wird der Suchbegriff auf “mon” erweitert, werden nur noch die Datensätze für den Artikel “Monitor” farbig unterlegt.

So wird die Lösung aufgebaut

Damit ausgehend vom eingegebenen Suchtext die komplette Zeile der zutreffenden Datensätze farbig unterlegt wird, sind zwei Dinge erforderlich:

  • eine bedingte Formatierung, die die farbige Kennzeichnung automatisch zuweist und
  • eine Formel für die bedingte Formatierung, die nicht nur eine, sondern mehrere Spalten gleichzeitig kontrolliert, ob dort der Suchbegriff aus C1 vorkommt.

Schritt 1: Der Zelle C1 einen Bereichsnamen zuweisen

Damit die Formel für die bedingte Formatierungsregel möglichst einfach ist, vergebe ich zunächst für Zelle C1 den Namen Suchfeld.

  • Dazu markiere ich C1 und klicke links von der Bearbeitungsleiste in das Namenfeld.
  • Dort tippe ich das Wort Suchfeld ein und schließe die Eingabe mit Enter ab.
  • In das Suchfeld (C1) gebe ich nun testweise schon mal die Zeichenkette LS ein.

2_Bereichsname

Bild 2: Für Zelle C1 einen Bereichsnamen vergeben

Schritt 2: Die Formatierungsregel für die Liste aufstellen

Die Datensätze, in denen der Suchbegriff LF vorkommt, sollen farbig unterlegt werden.

  • Demzufolge markiere ich erst einmal alle Datensätze, hier also A4 bis F17.
  • Dann wähle ich auf der Registerkarte Start die Befehlsfolge Bedingte Formatierung > Neue Regel > Formel zur Ermittlung der zu formatierenden Zellen verwenden [1].
  • Als Formel gebe ich ein: =SUCHEN(Suchfeld;$B4&$C4&$D4&$E4) [2].
  • Dann klicke ich rechts unten auf die Schaltfläche Formatieren [3] und wähle im folgenden Dialog im Register Ausfüllen [4] zum Hervorheben der Datensätze einen Orangeton aus [5].
  • Nachdem ich alle Dialogfelder mit OK geschlossen habe, sind die Datensätze für den Bearbeiter LF – wie in Bild 1 gezeigt – farbig unterlegt.

Die Formatierungsregel anlegen

Bild 3: Die Formatierungsregel aufstellen, die die Datensätze hervorhebt, in denen in einer der Spalten der Text aus dem Suchfeld vorkommt

Erläuterung der Formel:

  • Als erstes Argument erhält die Funktion SUCHEN den Suchtext. Der steht in der Zelle, die hier im Beispiel den Namen Suchfeld erhalten hat. Alternativ wäre auch der Zellbezug $C$1 möglich.
  • Die Funktion SUCHEN macht zwischen Groß- und Kleinschreibung keinen Unterschied. Es reicht also, wenn ein Suchbegriff in Kleinbuchstaben eingetippt wird.
  • Im zweiten Argument wird der SUCHEN-Funktion mitgeteilt, in welchem Bereich der zu suchende Text steht. Das sind hier im Beispiel die Spalten B, C, D und E. Der Bezug auf diese Spalten wird per $-Zeichen absolut gesetzt. Die Zeilennummer hingegen soll sich von Zeile zu Zeile anpassen. Daraus ergibt sich $B4, $C4, $D4 und $E4.
  • Da die Formel mehrere Spalten gleichzeitig kontrollieren soll, werden die Adressen für diese Spalten mit dem & verbunden.
  • Das dritte Argument der SUCHEN-Funktion wird hier im Beispiel nicht gebraucht.

Schritt 3: Formel anpassen und einen Mangel beheben

Auf den ersten Blick funktioniert diese Lösung. Lösche ich jedoch den Inhalt von C1 mit Entf, gibt es ein Problem. Nun werden alle Zeilen der Liste farbig unterlegt.

Demzufolge erweitere ich die Formel für die bedingte Formatierungsregel noch um die Prüfung, ob in Zelle C1 ein Text steht. Dies erledigt die Funktion ISTTEXT. ISTTEXT und die schon vorhandene SUCHEN-Funktion schließe ich in einer UND-Funktion ein.

  • Zum Korrigieren der Formel für die Formatierungsregel klicke ich in die Liste und wähle auf der Registerkarte Start die Befehlsfolge Bedingte Formatierung > Regeln verwalten.
  • Per Doppelklick auf die Regel öffne ich das Dialogfeld zum Bearbeiten der Regel.
  • Im Eingabefeld ergänze ich die ursprüngliche SUCHEN-Funktion wie folgt:
    =UND(ISTTEXT($C$1);SUCHEN(Suchfeld;$B4&$C4&$D4&$E4)).

Die Formel erweitern

Bild 4: Die ursprüngliche Formel wird an den gelb markierten Stellen ergänzt

So lässt sich die Lösung weiter nutzen

Bei einer großen Liste wäre es zu umständlich, erst lange nach unten zu scrollen, um alle farbig hervorgehobenen Datensätze zu sehen. Hier ist ein Filter hilfreich.

  • Sind noch keine Spaltenfilter eingerichtet, klicke ich in die Liste und schalte die Filter-Dropdowns mit Strg+Umschalt+L ein.
  • Nun wähle ich in der Spalte, nach der filtern ist, per Klick auf das Dropdown [1] die Option Nach Farbe filtern [2] und die entsprechende Farbe [3].

Den Farbfilter einsetzen

Bild 5: Über den Farbfilter schnell nur die gekennzeichneten Datensätze anzeigen

Dieser Beitrag wurde unter Excel abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

7 Antworten auf Ein intelligentes Suchfeld sorgt für den Überblick: Gesuchte Einträge in einer Liste automatisch farbig hervorheben

  1. Marcel sagt:

    Hallo,

    eine wirklich sehr elegante und schöne Lösung. Ich möchte diese llerdings noch weiter nutzen, als nur mit dem Farbfilter.

    Ich habe nun eine zusätzliche Zelle, in der die Anzahl der Treffer nummerisch dargestellt werden sollen. D.h. wenn ich nach Er suche und in meiner Liste 5 Zeilen blau markiert werden, möchte ich in diesem Feld gerne die Zahl 5 stehen haben.

    Zudem möchte ich die Filterfunktion gerne vereinfachen. D.h. ich habe eine Zelle die als Button fungieren soll und über diesen „Zellen-Button“ soll das automatische Filtern passieren. Ist dies so möglich?

    Vielen Dank.

  2. Dominique sagt:

    Top! Das erleichtert mir die Arbeit sehr:)

  3. Jens sagt:

    Hallo zusammen. Das Markieren der Datensätze durch automatisches Ausfüllen der Zellen klappt prima! Aber wie kann ich jetzt noch die Anzahl der durch den Filter farblich markierten Zeilen auslesen, bzw. automatisch anzeigen lassen?
    Danke für die Hilfe

  4. Suchfrage sagt:

    Es klappt jetzt- habe den Fehler gefunden, allerdings bleibt die gefilterte Auswahl nun immer erhalten, auch wenn ich neu „suche“ und andere Treffer markiert werden sollten. Gibt es hierfür eine Lösung?

  5. Suchfrage sagt:

    Eigentlich eine tolle Idee- leider bekomme ich es nicht zum laufen.

    Habe das projekt „nachgebaut“ mit nur 4 Einträgen.

    Bei mir werden leider nicht die richtigen Felder farbig hervorgehoben, sondern wenn ich nach „MO“ suche alle Zeilen außer denen in denen MO steht. Sehr seltsam. Die Formel ist exakt kopiert.. woran kann das liegen?

  6. Diana sagt:

    Danke! Nach so etwas hab ich schon lange gesucht. Allerdings hat es die Formel in sich. Da muss frau erst mal drauf kommen. Das mit dem Filtern nach Farbe ist echt praktisch. Ich denke, mein Chef wird staunen, wenn ich ihm das vorführe 🙂

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.