Suchfunktion mit bedingter Formatierung 7

Mit Hilfe der bedingten Formatierung lässt sich eine praktische Suchfunktion erstellen.
 

Eine meiner absoluten Lieblingsfunktionen in Excel ist mittlerweile die bedingte Formatierung.

Mit ihrer Hilfe lassen sich unglaublich viele Dinge bewerkstelligen und Excel-Tabellen damit sehr anwenderfreundlich gestalten, indem Zellen abhängig von bestimmten Ereignissen z.B. optisch hervorgehoben werden.

Eine praktische Anwendung, die du vielleicht noch nicht kennst, zeige ich dir heute:

Wir basteln uns mit der bedingten Formatierung nämlich eine Suchbox, die unsere Tabelle nach beliebigen Zeichen und Wörtern durchsucht und alle Treffer farbig markiert.

Und so geht’s:

Die Beispieltabelle

Für meine Beispieltabelle habe ich aus Wikipedia eine Übersicht erfolgreicher Kinofilme kopiert:

Die Beispieldatei

Die Beispieldatei

Bei Bedarf kann die Tabelle hier heruntergeladen werden.

Wie man im Screenshot sieht, habe ich oberhalb der Tabelle die Zelle C5 farbig hinterlegt. Dies soll meine Suchbox sein. Und wenn ich hier ein beliebiges Wort oder Teile davon eingebe, werden alle gefundenen Zeilen hervorgehoben:

Die Suchbox in Aktion

Die Suchbox in Aktion

Dabei spielt es keine Rolle, in welcher Spalte sich der gesuchte Text befindet. Ebenso unerheblich ist Groß- und Kleinschreibung.

Wie funktioniert das?

Die bedingte Formatierung

Markieren wir zunächst unsere Filmtabelle und erstellen eine neue Formatierungsregel:

Bedingte Formatierung

Bedingte Formatierung

Als Regeltyp wählen wir den letzten Eintrag “Formel zur Ermittlung der zu formatierenden Zellen verwenden”:

Neue Formatierungsregel anlegen

Neue Formatierungsregel anlegen

In das Formelfeld geben wir nun diese Funktion ein:
=SUCHEN($C$5;$A8&$B8&$C8&$D8&$E8&$F8)

Die SUCHEN-Funktion benötigt zumindest zwei Parameter:
1. Den Suchtext, der in unserem Beispiel in Zelle C5 stehen soll. Wichtig ist, dass wir mit absoluten Bezügen arbeiten, daher $C$5.
2. Den zu durchsuchenden Text. Da wir immer alle Spalten einer Zeile durchsuchen und im Falle eines Treffers die komplette Zeile hervorheben wollen, verknüpfen wir alle Spalten mit dem &-Zeichen.
Hier müssen wir mit gemischten Bezügen arbeiten, da nur die Spalten fix sind. Die Zeilennummern müssen sich natürlich anpassen. Daher also $A8, $B8 usw.
(Beginnt deine Tabelle in einer anderen Zeile, müssen diese Nummern natürlich angepasst werden)

Nun wählen wir nur noch die gewünschte Formatierung aus und schließen das Regelfenster:

Variante 1: Die SUCHEN-Funktion

Variante 1: Die SUCHEN-Funktion

Leider hat unsere Regel einen Schönheitsfehler: Wenn im Suchfeld nämlich noch nichts eingetragen wurde, werden alle Zeilen farbig markiert:

Ein Schönheitsfehler

Ein Schönheitsfehler

Das ist aber kein Problem, wir müssen unsere Formel lediglich noch etwas erweitern. Denn genau genommen müssen ja zwei Bedingungen zutreffen, damit eine Zeile farbig markiert werden soll:
1. Das Suchfeld darf nicht leer sein
2. Der Suchtext muss auch gefunden werden

Und genau das erreichen wir mit folgender Formel:
=UND(NICHT(ISTLEER($C$5));SUCHEN($C$5;$A8&$B8&$C8&$D8&$E8&$F8))

Die ISTLEER-Funktion gibt WAHR zurück, wenn die Zelle C5 leer ist. Wir wollen aber, dass sie nicht leer ist und müssen daher das Ergebnis mit der NICHT-Funktion umkehren:

Die verbesserte Variante

Die verbesserte Variante

Und schon funktioniert unsere Suchbox wie gewünscht:

Die Suchbox wieder in Aktion

Die Suchbox wieder in Aktion

Mit ein wenig Fantasie werden auf diese Weise sicherlich noch ausgefeiltere Suchtechniken möglich. Jetzt bist du an der Reihe!

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Kommentar erstellen

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

7 Gedanken zu “Suchfunktion mit bedingter Formatierung

    • Martin Weiß Beitragsautor

      Hallo Gerhard,

      danke für Deinen Kommentar. Ja, mit der bedingten Formatierung lässt sich wirklich allerhand bewerkstelligen und sie sorgt bei vielen Anwendern für echte Aha-Erlebnisse.

      Schöne Grüße,
      Martin

  • Dieter Friedrich

    Hallo Martin,
    diese Anwendung ist wirklich sehr hilfreich. Die Aufgabe ist aber noch nicht vollständig gelöst!

    Was ist mit Ziffern, wenn ich mich z.B. nur an einen Teil der Postleitzahl erinner
    oder an einen Teil der Hausnummer oder der Kontonummer ??

    Als kleine Anregung

    Dieter
    P.s. Danke für Deine wöchentlichen Überraschungen 😉

    • Martin Weiß Beitragsautor

      Hallo Dieter,

      die Anwendung funktioniert grundsätzlich auch mit Ziffern. Darüber hinaus lässt sich auch mit den Jokerzeichen * und ? arbeiten. In meinem Beispiel wird lediglich immer über alle Spalten gesucht, so dass eine Einschränkung nur z.B. auf Postleitzahlen nicht möglich ist. Dazu müssten für jede Spalte eigene Suchfelder definiert werden.

      Was genau funktioniert denn bei dir nicht?

      Schöne Grüße,
      Martin

      • Dieter Friedrich

        Ich muss mich entschuldigen, sorry.

        Den Grund kann ich leider nicht rekonstruieren beim zweiten Versuch ging es auch mit Zahlen, super , Danke.
        Dieter

  • Hermann Hirschberger

    Hallo Martin,

    Danke für die vielen Praxis-Ideen!!!

    Schöne Weihnachtsgrüße
    sowie ein gesundes & erfolgreiches 2016

    wünscht Dir

    Hermann

    • Martin Weiß Beitragsautor

      Hallo Hermann,

      vielen Dank für die lieben Grüße. Auch Dir wünsche ich ein gutes und gesundes Jahr 2016 und hoffentlich weiterhin viel Spaß hier auf dem Blog!

      Liebe Grüße,
      Martin