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
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
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
Als Regeltyp wählen wir den letzten Eintrag „Formel zur Ermittlung der zu formatierenden Zellen verwenden“:
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
Leider hat unsere Regel einen Schönheitsfehler: Wenn im Suchfeld nämlich noch nichts eingetragen wurde, werden alle Zeilen farbig markiert:
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
Und schon funktioniert unsere Suchbox wie gewünscht:
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:
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.
Eine schöne Sache, die Du da vorschlägst, Martin. Bedingte Formatierung macht irgendwie Spaß und hat noch Potential.
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
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 😉
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
Ich muss mich entschuldigen, sorry.
Den Grund kann ich leider nicht rekonstruieren beim zweiten Versuch ging es auch mit Zahlen, super , Danke.
Dieter
Hallo Martin,
Danke für die vielen Praxis-Ideen!!!
Schöne Weihnachtsgrüße
sowie ein gesundes & erfolgreiches 2016
wünscht Dir
Hermann
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
Hallo und guten Tag,
ich versuche aus einer bestehenden Tabelle, in einer beliebigen Zeile, eine bestimmte Zelle (in meinem Fall ein Datum) in eine andere Tabelle mit gleichem Inhalt, allerdings auf einem anderen Zeilenplatz so zu laden, das bei neuem Zellinhalt die daten automatisch geladen werden. Leider bin ich mit der Such -Funktion nicht sehr vertraut. Können Sie hier helfen? Grüße,
Hallo Ilko,
ich bin mir nicht sicher, ob ich Sie richtig verstanden habe. Aber für mich hört sich das nach einer normalen Zellverknüpfung an. Sie haben in einer Zelle (beispielsweise A1) in Tabelle1 ein Datum und wollen, dass dieses auch in einer anderen Zelle (beispielsweise C20) in Tabelle2 erscheint?
Dann müssen Sie nur in der Zielzelle C20 einen Bezug zur Quellzelle A1 herstellen:
In Tabelle, C20:
=Tabelle1!A1
Oder habe ich Sie völlig falsch verstanden?
Schöne Grüße,
Martin
Hi Martin,
finde deine Tabelle echt klasse, das einzige was mich nervt ist, wenn ich was suche was ziemlich unten steht, muss man erstmal runterscrollen um das gesuchte ergebnis zu sehen.
Würde mir sowas wie bei Strg F dass der gesuchte, auch gleich angezeit wird (Sprung dahin)
LG Gianni
Hallo Gianni,
ja, das wäre praktisch. Aber dafür ist die bedingte Formatierung einfach nicht gemacht.
Schöne Grüße,
Martin
Filter Funktion,nach Farbe filtern.
Hallo Martin, vielen Dank!
Bei mir klappt es zu 80%.
Wenn ich z.B „Stein“ als Suchbegriff eingebe, markiert er nicht alle Zeile, wo das Wort „Stein“ vorkommt und manchmal markiert er die Zeile, obwohl „Stein“ gar nicht vorkommt.
Woran kann das liegen? Können Sie mir eventuell weiterhelfen?
Schöne Grüße
Daiane
Hallo Daiane,
es gibt eigentlich zwei große Fehlerquellen:
In der Formel sind die $-Zeichen nicht korrekt gesetzt.
Oder beim Erstellen der Formatierungsregel war die aktive Zelle an der falschen Stelle. In meinem Beispiel oben sieht man im dritten Bild, dass die aktive Zelle in A8 steht. Daher wird in meiner Formel auch immer auf die Zeile 8 verwiesen:
=SUCHEN($C$5;$A8&$B8&$C8&$D8&$E8&$F8)
Vielleicht ist Ihnen hier ein Fehler unterlaufen?
Schöne Grüße,
Martin
Hallo,
vielen Dank für diese tolle Anleitung. Sie kam wie gerufen da ich schon am Verzweifeln war!! 🙁
Funktioniert alles Perfekt, bis auf Teil 2 deiner Anleitung:
Leider hat unsere Regel einen Schönheitsfehler: Wenn im Suchfeld nämlich noch nichts eingetragen wurde, werden alle Zeilen farbig markiert:
Wenn ich meine angepasste Formel eingebe =UND(NICHT(ISTLEER($L$1));SUCHEN(($L$1;$B13:$B47))
kommt folgende Fehlermeldung: Sie haben zu wenig Argumente für diese Formel eingegeben
Wo liegt den mein Fehler?
Beste grüße für die ansonsten tolle Arbeit!
Hallo Reiner,
lade dir doch einfach die Beispieldatei herunter und schau dir dort die Formatierungsregel an. Dann sollte es auch klappen.
Schöne Grüße,
Martin
Hallo schönen guten Tag,
vielen lieben Dank, mir hat ihr Eintrag sehr geholfen.
Ich (denke bzw. hoffe) das ich alles richtig gemacht habe, allerdings markiert es bei mir jeweils die Zeile über dem gesuchten. Haben Sie da evtl. ein Idee woran das liegen könnte?
Alles liebe
ronja
Hallo Ronja,
es liegt mit ziemlicher Sicherheit an den Bezügen, die Sie beim Erstellen der Formatierungsregel verwendet haben. Vermutlich lag die zu dem Zeitpunkt aktive Zelle einfach eine Zelle unterhalb des angegebenen Bezugs. Das ist etwas tückisch und wird gerne übersehen. Also einfach nochmal die Formatierungsregel und dort speziell die Zeilenangaben überprüfen.
Schöne Grüße,
Martin
Hallo,
eine Frage: Wenn ich zwar alle Zeilen und Spalten durchsuchen will, aber nur in der Trefferzeile ein bestimmtes Feld anzeigen lassen will, wie muss ich die Formel umstellen?
Danke
Dirk
Hallo Dirk,
du möchtest also nur das Feld hervorheben, dass den Suchbegriff auch tatsächlich enthält? Dann verkürzt sich die Formel ziemlich. Bezogen auf meine Beispiel wird aus
=UND(NICHT(ISTLEER($C$5));SUCHEN($C$5;$A8&$B8&$C8&$D8&$E8&$F8))
dann
=UND(NICHT(ISTLEER($C$5));SUCHEN($C$5;A8))
Wichtig:
Ich gehe in den genannten Formeln davon aus, dass die aktive Zelle jeweils A8 ist.
Schöne Grüße,
Martin
Hallo Martin Weiß
Klasse Anleitung. Kannst Echt gut erklären.
Trotzdem bekomme ich es nicht auf meine Anforderung umgemünzt und weiß auch gar nicht ob es hiermit überhaupt geht.
Ich habe eine Tabelle (Übersicht) und dort in der Spalte A steht ein Wert. Alla A1, A2, A3 etc… Nun möchte ich diese jeweiligen Zellen farbig markieren wenn der Wert in der Tabelle (D0608) in der Spalte D oder E oder F zu finden ist.
Kannst Du mir einen Tipp geben?
Ich habe so viele Varianten deiner Formel probiert das ich Die schon gar nicht mehr aufzählen kann. Aber ich bekomme ich einfach nicht.
Geht das überhaupt so?
Danke im voraus.
Gruß Frank
Hallo Frank,
die Formel dafür ist etwas aufwändiger, da du hier drei Spalten durchsuchen möchtest. Ein Variante wäre das hier:
=ODER(NICHT(ISTFEHLER(VERGLEICH(A1;$D$1:$D$14;0)));NICHT(ISTFEHLER(VERGLEICH(A1;$E$1:$E$14;0)));NICHT(ISTFEHLER(VERGLEICH(A1;$F$1:$F$14;0))))
Den Tabellenbereich musst du natürlich anpassen. Die VERGLEICH-Funktion liefert entweder eine Zahl, falls der gesuchte Wert in der Spalte gefunden wurde, oder einen Fehler. Daher muss das ganz noch mit der ISTFEHLER-Funktion abgefangen werden, ansonsten wird es nicht funktionieren.
Schöne Grüße,
Martin
Hallo Martin,
echt eine super Anleitung und es hat auch gut funktioniert! Ich habe allerdings eine Frage und hoffe Du kannst mir weiterhelfen…
Ich suche in meiner Tabelle nach nummerierten Sicherheitshinweisen (z.B. H224, H301, H319) von chemischen Stoffen. Wenn ich in das Suchfeld „H224, H319“ eingebe, werden mir nur Felder farblich markiert, in denen auch „H224, H319“ hintereinander stehen. Wenn in einer Zelle „H224, H301, H319“ steht, wird mir diese nicht markiert, obwohl sie die gesuchten Parameter auch enthält. Gibt es dafür eine Lösung?
Vielen Dank und liebe Grüße
Carolin
Hallo Carolin,
du kannst in deinem Suchfeld mit dem Stern als Jokerzeichen arbeiten. Also H224*H319 sollte dann alle entsprechenden Felder markieren.
Schöne Grüße,
Martin
Hallo,
lässt sich die Formel auch so erweitern, dass die Spalten die nicht zutreffen, ausgeblendet werden.
Also man sucht etwas, und nur die Spalten auf die die Suche zutrifft werden noch angezeigt, ist dies hiermit auch möglich?
Grüße
Hallo Hendrik,
wenn ich es richtig verstehe, geht es um die Zeilen (nicht Spalten), oder?
Also direkt ausblenden kann man Zeilen über Formatierungsregeln nicht. Aber mit einer zweiten Regel können zumindest die Zelleninhalte unsichtbar gemacht werden, auch wenn die Zeilen als solche sichtbar bleiben:
=UND(NICHT(ISTLEER($C$5));ISTFEHLER(SUCHEN($C$5;$A8&$B8&$C8&$D8&$E8&$F8)))
Und als Format gibt wählt man dann im Register „Zahlenformat“ den Eintrag „Benutzerdefiniert“ und gibt als Typ drei Semikolons an ;;;
Vielleicht hilft das weiter.
Schöne Grüße,
Martin
Seit Langem mal wieder eine richtig nützliche Verformelung. Diese Mühe weiß ich zu schätzen. Danke dafür.
Vielleicht kannst du mir eine kleine Frage beantworten. Nun möchte ich innerhalb deiner Beispieltabelle nicht nur nach einem Begriff suchen (zb. „disney“), sondern nach mehreren Begriffen. Biespiel: „disney“ und „dreamworks“. Was muss ich in das Suchfeld angeben, damit mir alle Filme von Disney und DreamWorks angezeigt werden?
Liebe Grüße aus Wilhelmshaven,
Marek
Hallo Marek,
danke für dein nettes Feedback.
Mit einem Suchfeld bekommst du so etwas nicht hin. Aber du kannst einfach ein zweites (oder drittes) Suchfeld einrichten und musst dann nur eine zweite (dritte) Formatierungsregel anlegen, die sich eben auf das neue Suchfeld bezieht. Ansonsten wäre die Formel identisch.
Schöne Grüße,
Martin
Hey Martin,
vielen Dank für deine Erklärung, die übrigens sehr hilfreich war 😉
Sag mal, besteht auch die Möglichkeit, neben dem Suchfeld die Anzahl der Suchergebnisse zu kreieren? Ich habe in meiner Datenbank öfters mal doppelte Begriffe. Somit würde es noch mehr helfen.
Noch ein Wunschdenken: Besteht die Möglichkeit, dass nur die farbigmarkierten Suchergebnisse angezeigt werden und die anderen in dem Moment ausgeblendet werden? Freue mich auf dein Feedback.
Hi Anja,
ja, das geht. Du legst einfach folgende zweite Regel an:
=ISTFEHLER(NICHT(ISTLEER($C$5));SUCHEN($C$5;$A8&$B8&$C8&$D8&$E8&$F8))
Die ist also praktisch identisch mit der ersten Regel, nur um die umschließende ISTFEHLER-Funktion erweitert.
Und als Format gibst du das benutzerdefinierte Zahlenformat ;;; an (oder alternativ eine weiße Schriftfarbe).
Schöne Grüße,
Martin
Super, vielen vielen Dank. Und besteht auch eine Zahlangabe, zu den gefundenen Suchbegriffen? Würde sich ja gut neben dem Suchfeld machen, dass zum Beispiel 5 Treffer erzielt worden sind. Gibt es sowas auch?
Viele Grüße Anja
Hallo, Supersache!
Aber ich habe das Problem, dass bei mir nicht die Zeile die den Suchbegriff beinhaltet markiert wird, sondern die darüberliegende.
ich weiß, das Problem sitzt am Lap. Gibt’s trotzdem eine Lösung
VG Peter
Hallo Peter,
Erkenntnis ist ja schon der halbe Weg zur Lösung 🙂
Du hast mit hoher Wahrscheinlichkeit nur beim Anlegen der Formatierungsregeln nicht ganz aufgepasst und bist in der falschen Zeile gestanden. Es ist hier immer wichtig, dass man die Zeilennummern in der Formatierungsregel immer in Bezug zur aktiven Zelle setzt. Und hier hat sich vermutlich der Fehler eingeschlichen.
Im Zweifelsfall einfach die Beispieldatei herunterladen.
Schöne Grüße,
Martin
Hallo Martin,
funktioniert diese Suche eigentlich auch, wenn die Suchbox (in Deinem Beispiel die Zelle C5) nicht eine Zelle, sondern eine mit der Funktion FILTER erstelllte Liste wäre?
Hallo Jens,
nein, die von mir vorgestellte Suchbox funktioniert nur mit einem einzelnen Eingabefeld.
Schöne Grüße,
Martin
Hallo Martin,
mir hat deine Bedingte Formatierungs-Formal mal wieder sehr geholfen.
Adressdaten zu splitten ist nicht ganz so leicht, konnte damit besonders die von bis Adressen hervorheben.
Vielen Dank. Ich schließe mich anderen an und finde, dass du so gut erklärst, dass ich das nachvollziehen kann.
Viele Grüße
Roswitha
Hallo Roswitha,
vielen Dank für das Lob, das freut mich sehr!
Schöne Grüße,
Martin
Guten Tag
ich versuche etwas ähnliches zu erreichen, indem ich nach einem fixen String suche, statt nach dem Inhalt eines Suchfeldes. Das sollte ja aber gehen indem ich „$C$5“ ersetze mit „MEIN_SUCHSTRING“. Leider tut es gar nicht und mir gehen langsam die Ideen aus. Auffäölig ist einfach, dass Excel meine Formel immer in Anführungszeichen setzt. In der Beispieldatei ist das nicht der Fall. Woran könnte das denn liegen?
Viele Grüsse
Carolin
Hallo Carolin,
ja, das funktioniert natürlich auch mit einem fixen Suchstring in der Formel. Wichtig ist halt nur, dass der Suchtext an sich in Anführungszeichen gesetzt wird.
Schöne Grüße,
Martin
Hallo Martin,
Soviel Hilfe und dann noch toll und verständlich erklärt.
Meine Frage: Kann man das auch erweitern, das über z.B. 2 Tabellen gesucht wird?
Du würdest mir einen riesigen Gefallen tun, ich komme einfach nicht weiter.
Besten Dank!
Hallo Michael,
die Suchfunktion kannst du in beliebig vielen Arbeitsblättern einrichten. Du musst halt in jedem Blatt die Formatierungsregeln so anpassen, dass auf das Suchfeld im ersten Blatt zugegriffen wird.
Ich bleibe mal bei meinem Beispiel mit der Filmtabelle. Angenommen, du hast mehrere gleich aufgebaute Arbeitsblätter und nur auf dem ersten Blatt steht in Zelle C5 das Suchfeld. Dann änderst du auf den anderen Blättern die Formatierungsregel
von
=UND(NICHT(ISTLEER($C$5));SUCHEN($C$5;$A8&$B8&$C8&$D8&$E8&$F8))
in
=UND(NICHT(ISTLEER(Tabelle1!$C$5));SUCHEN(Tabelle1!$C$5;$A8&$B8&$C8&$D8&$E8&$F8))
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die sehr schnelle Hilfe!
„Der Depp saß mal wieder vor dem PC“!
Ist ja nicht so, dass die Lösung eine Herausforderung darstellt. Aber ich habe wieder um die Ecke gedacht!
Du machst hier einen ganz tollen „Freizeit“-Job!
Ich hoffe viele können das auch schätzen!?!?
Beste Grüße!!!
Michael
Hallo Michael,
sei nicht so hart mit dir 🙂
Manchmal ist es einfach so, dass man den Wald vor lauter Bäumen nicht sieht, geht mir auch so.
Und vielen Dank für das schöne Feedback!
Schöne Grüße,
Martin