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