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!
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
Hallo Martin,
vielen Dank für Deine äußerst verständliche Anleitung, damit habe sogar ich es sogar geschafft.
Das Suchfeld funktioniert super! Was mich noch glücklicher machen würde wäre die Möglichkeit die so hervorgehobenen Zeilen dauerhaft abzuspeichern (oder zu kopieren) um die farbige Markierung zu erhalten. Vielen dank für Deine tolle Arbeit! Ich komme wieder.
Alles Gute, Ulli
Hallo Ulli,
freut mich, dass der Artikel weitergeholfen hat. Mit einer dauerhaften Speicherung sieht es bei der bedingten Formatierung aber schlecht aus, denn das widerspricht ja dem dynamischen Gedanken. Du könntest höchstens auf Basis der bedingten Formatierung die hervorgehobenen Zeilen filtern (nach Farbe filtern) und dann sozusagen eine manuelle Einfärbung über die gefilterten Zeilen drüberlegen. Die dann erhalten bleibt, wenn die Suchkriterien sich wieder ändern.
Aber das ist halt ein manueller Schritt.
Schöne Grüße,
Martin
Hallo Martin,
ich verfolge und suche regelmäßig auf deiner sehr aufschlussreichen Seite nach Lösungen.
Heute weiß ich nicht mehr weiter und verzweifle an einem scheinbar einfachen Problem und hoffe auf Unterstützung.
Ich habe eine einfache Tabelle, in Spalte A stehen Gruppennamen unterschiedlicher Zeilenanzahl und in Spalte B gibt es ein Auswahlfeld mit den Möglichkeiten: „ja“; „nein“; „teilweise“ oder auch leer. Die Auswahlmöglichkeiten sollen unterschiedlich farblich hinterlegt werden.
Gruppe 1; leer
Gruppe 1; ja
Gruppe 1; nein
Gruppe 2; ja
Gruppe 3; teilweise
Gruppe 3; nein
Nun möchte ich mit der bedingten Formatierung fortlaufend GRUPPENWEISE auswerten, ob z.B. in der Gruppe 1, egal in welcher Zeile ein „ja“ enthalten ist, alle Zeilen diese Gruppe 1 in der Spalte B insgesamt mit der Hintergrundfarbe für „ja“ markiert wird.
Wenn dafür eine Formel vorhanden ist, kann die Priorität, mit welcher Auswahl die jeweilige gesamte Gruppe markiert wird,
mit der Reihenfolge der Formatierungsregeln erfolgen.
Für jede Hilfe dazu bin ich dankbar.
Herzlichen Gruß André
Hallo André,
das geht mit Hilfe der ZÄHLENWENN-Funktion in einer Formatierungsregel. Angenommen, in A1:A18 steht der Gruppenname und in B1:B18 steht die Auswahlmöglichkeit. Dann wäre die erste Formatierungsregel für den kompletten Tabellenbereich A1:B18 folgende:
=UND($A1=“Gruppe 1″;$B1=“ja“;ZÄHLENWENNS($A$1:$A$18;$A1;$B$1:$B$18;$B1))
Du müsstest dann für jede Kombination aus Gruppe und Auswahlmöglichkeit eine solche Regel erstellen.
Schöne Grüße,
Martin
Hallo Martin,
erst einmal vielen Dank für die schnelle Antwort und deinen interessanten Vorschlag, den ich gleich ausprobiert habe.
Allerdings funktioniert er so leider nicht, denn die „Gruppe 1“ kann keine Konstante sein, da diese variabel ist,
ebenso wie die gesamte Zeilenanzahl je Gruppe und auch die Zeilenanzahl der gesamten Tabelle. (Tab. beginnt bei Zeile 6)
Von einer weiteren komplett gefüllten Spalte der Tabelle habe ich die gesamte Zeilenanzahl mit INDIREKT(„$B$6:$B“&VERGLEICH(„“;$E:$E;-1)) ermittelt.
So könnte die Formel für die Auswahl „ja“ dann aussehen, aber sie funktioniert nicht:
=UND($B6=$B7; $F6=“ja“; ZÄHLENWENNS(INDIREKT(„$B$6:$B“&VERGLEICH(„“;$E:$E;-1));$B6; INDIREKT(„$F$6:$F“&VERGLEICH(„“;$E:$E;-1));$F6))
Ist wohl doch komplizierter als gedacht, denn Excel muss bei der Abarbeitung je Zeile,
bereits das Vorhandensein der Auswahl „ja“ in der aktuell durchlaufenden Gruppe kennen,
um die Zeile dieser Gruppe dann formatieren zu können und das dann fortlaufend mit anderen Gruppen bis zum Tabellenende.
Herzlichen Gruß André
Hallo André,
vielleicht habe ich dich ja auch missverstanden. Aber wenn jede Gruppe in einer eigenen Farbe formatiert werden soll, dann musst du eine Regel pro Gruppe anlegen. Wie sonst sollten unterschiedliche Farben umgesetzt werden?
Wenn es also beispielsweise 5 Gruppen gibt, dann brauchst du 10 fast identische Formatierungsregeln, die sich nur in der Gruppe und dem „ja“ oder „nein“ unterscheiden:
=UND($A1="Gruppe 1";$B1="ja";ZÄHLENWENNS($A$1:$A$18;$A1;$B$1:$B$18;$B1))
=UND($A1="Gruppe 1";$B1="nein";ZÄHLENWENNS($A$1:$A$18;$A1;$B$1:$B$18;$B1))
=UND($A1="Gruppe 2";$B1="ja";ZÄHLENWENNS($A$1:$A$18;$A1;$B$1:$B$18;$B1))
=UND($A1="Gruppe 2";$B1="nein";ZÄHLENWENNS($A$1:$A$18;$A1;$B$1:$B$18;$B1))
usw.
Und was die Anzahl der Zeilen angeht:
Wenn du deine Liste in eine formatierte Tabelle umwandelst, dann hat sich dieses Problem auch erledigt. Denn dann sind die Zeilenbezüge nicht auf Zeilennummern festgelegt, sondern umfassen jeweils soviele Zeilen, wie die formatierte Tabelle eben enthält (Stichwort „Strukturierte Verweise“)
Schöne Grüße,
Martin
Hallo Martin,
ja, mir ist klar, dass ich für jede Farbe eine Regel anlegen muss,
aber es geht NICHT darum jede Gruppe mit einer eigenen Farbe zu formatieren !
Das ist ein Mißverständnis, denn es gibt nur die 3 Auswahlmöglichkeiten
„ja“; „nein“; „teilweise“ mit jeweils einer eigenen Farbe – mehr nicht !
Zudem gibt es hunderte von verschiedenen Gruppen und ich erkenne diese nur durch
den Gruppenwechsel von $A1$A2. (nach deinem Beispiel)
Betrachten wir mal nur die Regel zur Formatierung der Auswahl „ja“ –
wenn in einer Gruppe, egal in welcher Zeile oder auch in mehreren Zeilen der Gruppe,
ein „ja“ ausgewählt wurde, dann soll diese gesamte Gruppe mit der Farbe für „ja“
formatiert werden.
Die folgende Gruppe KÖNNTE wiederum ein „ja“ enthalten und würde dann ebenso
als gesamte Gruppe mit der Farbe für „ja“ formatiert werden.
Es ist aber auch möglich, dass eine der folgenden Gruppen KEIN „ja“ enthält,
dann greifen die anderen beiden Regeln „nein“ oder „teilweise“ in prioritärer Reihenfolge.
Somit muss sich der Gruppenwechsel in der Formel widerspiegeln und in der jeweiligen
Gruppe nach dem Vorkommen von „ja“ gesucht werden.
Wobei das Vorkommen von „ja“ innerhalb oder auch am Ende der Gruppe auftreten kann
und dennoch alle Zeilen der Gruppe vom Anfang bis zum Ende formatiert werden sollen.
Ich hoffe, ich habe es etwas verständlicher aufzeigen können.
Herzlichen Gruß André
Hallo André,
dann mache ich jetzt nochmal einen Versuch. Wenn ich dich also richtig verstanden habe, dann sind die Gruppen also immer durchgängig. Das heißt, nach einem Wechsel einer Gruppe kommt später nicht plötzlich wieder zu vorherige Gruppe.
Die Formatierungsregeln würden dann beispielsweise so aussehen:
=ZÄHLENWENNS($A$1:$A$25;$A1;$B$1:$B$25;“ja“)>0 -> grüne Einfärbung
=ZÄHLENWENNS($A$1:$A$25;$A1;$B$1:$B$25;“teilweise“)>0 -> gelbe Einfärbung
=ZÄHLENWENNS($A$1:$A$25;$A1;$B$1:$B$25;“nein“)>0 -> rote Einfärbung
Die Einfärbung erfolgt immer für die komplette Gruppe und zwar in Abhängigkeit der Reihenfolge der Regeln. In meinem Beispiel würde also für die komplette Gruppe grün eingefärbt, wenn auch nur ein Element dieser Gruppe den Wert „ja“ in Spalte B aufweist.
Gelb käme nur dann zum Tragen, wenn es wenigstens einen Eintrag „teilweise“ gibt und keinen Eintrag „ja“.
Und rot käme wirklich nur dann zur Anwendung, wenn es nur „nein“ innerhalb der Gruppe gibt.
Es ist jedoch nicht möglich, die drei Farben gleichzeitig innerhalb einer Gruppe zum Einsatz zu bringen, denn nach der ersten zutreffenden Regel werden die nachfolgenden Formatierungsregeln nicht mehr angewendet.
Ist es das, was du brauchst?
Schöne Grüße,
Hallo Martin,
ja, das ist richtig, die Gruppen wiederholen sich nicht und du hast den Sachverhalt korrekt erfasst.
Es ist eigentlich ganz einfach und die Logik für mich schlüssig, aber ich verstehe nicht, warum Excel hier auch mit =ZÄHLENWENNS($A$1:$A$25;$A1;$B$1:$B$25;“ja“)>0 NICHT funktioniert – es formatiert nichts !
Scheinbar wird der Gruppenwechsel nicht erkannt ?
Darf ich dir (wie) mal eine Beispiel Excel-Datei zukommen lassen ?
Herzlichen Gruß André
Hallo Andrè,
ich bitte um Verständnis, dass ich hier keinen individuellen Excel-Support geben kann. Du kannst dir an der Anzahl der Kommentar hier sicher vorstellen, wieviele Anfragen ich erhalte 😉
Solltest du aber an einer professionellen (kostenpflichtigen) Unterstützung interessiert sein, kannst Du mir die Datei an info@tabellenexperte.de schicken. Ich werde mir dann gerne alles ansehen und ein Angebot erstellen.
Schöne Grüße,
Martin
Hallo Martin,
ja, das vestehe ich – ich dachte andere Interessierte würden sich hier beteiligen …
Mich hat es aber nicht in Ruhe gelassen und ich habe es weiter probiert und siehe da,
es funktioniert doch, so wie du es vorgeschlagen hast.
Allerdings ist Excel nicht gleich Excel,
denn ich habe es bisher nur mit MSO Home and Student 2021 (64 Bit) zum laufen gebracht.
Unter den aktuellen MS Offices mit denen ich arbeiten muss funktioniert es leider nicht:
– MSO Professional Plus 2016 (32 Bit)
– MS 365 MSO V2109 (32 Bit)
– MS 365 MSO V2304 (64 Bit)
Das nur zur Info und vielleicht gibt es dafür ja doch noch eine Lösung.
Bis dahin aber vielen Dank
Herzlichen Gruß André
Hallo André,
ich sehe keinen Grund, warum die Lösung nicht in allen von dir genannten Excel-Versionen funktionieren sollte. Es ist eine normale ZÄHLENWENNS-Formel und die Funktion gibt es bereits seit Excel 2007. Da muss also meines Erachtens noch irgendwo der Wurm drin sein…
Schöne Grüße,
Martin
Hallo Martin, vielen Dank für deine super „einfachen“ Erklärungen und Anleitungen. Ich habe lange nach so einer Tabelle gesucht und mit meinen Laien Kenntnissen habe ich es geschafft eine Tabelle zum Suchen von KFZ Zeichen zu erstellen. Ich hätte da noch eine Frage zu: Ist es möglich die gefundenen Zeilen an der obersten Stelle in der Tabelle sichtbar zu machen und wenn das Suchfeld geleert wird wieder in den Normalzustand zurück rutscht? Oder die anderen auszublenden?
Vielen Dank Gruß Anja
Hallo Anja,
danke für dein tolles Feedback. Was die automatische Sortierung innerhalb der Tabelle angeht, sieht es allerdings schlecht aus, dazu wäre VBA-Programmierung notwendig. Aber du kannst die Tabelle ja manuelle nach Farben sortieren. Einfach die Autofilter einschalten, dort gibt es dann die Option „Nach Farbe sortieren“.
Geht zwar nicht automatisch, wäre aber eine Möglichkeit.
Wenn du Microsoft 365 im Einsatz hast, käme eventuell die neue FILTER-Funktion in Frage. Damit lassen sich gefilterte Ergebnisse an anderer Stelle ausgeben. Ein Beispiel dazu findest du hier:
Die neue FILTER-Funktion mit Jokersuche
Schöne Grüße,
Martin
Hallo Martin,
danke für diese verständliche Erklärung, ABER gibt es eine Möglichkeit, nicht die ganze Spalte hervorheben zu lassen, sondern nur die Felder, in denen der Suchtext gefunden wurde ?
Würde mich sehr über eine Hilfe freuen.
Schöne Grüße von Susanne
Hallo Susanne,
ja, das geht noch einfacher, denn man muss dann die Zellen in der SUCHEN-Funktion nicht verketten. Die Formel lautet dann:
=UND(NICHT(ISTLEER($C$5));SUCHEN($C$5;A8))
Dabei gehe ich davon aus, dass die aktive Zelle beim Erstellen der Regel in A8 steht.
Schöne Grüße,
Martin
Servus!
Das Thema ist zwar schon ein alter Schuh, vielleicht kann mir trotzdem jemand helfen.
Ich habe ein Telefonverzeichnis erstellt. Im Suchfeld ist es möglich nach Vorname, Nachname oder Nummer zu suchen.
Funktioniert auch wunderbar.
Allerdings ist das Ergebnis immer exakt eine Zeile UNTER dem markierten Bereich.
Beispiel: Ich Suche nach „Mustermann“.
Die markierte Zeile ist „Musterfrau“, direkt darunter steht „Mustermann“. Wie kann ich den Fehler behen?
Danke schonmal!
LG
Hallo Katharina,
die Ursache liegt ziemlich sicher darin, dass du beim Erstellen der Formatierungsregel mit den Zellbezügen verrutscht bist. Man muss hier höllisch aufpassen, wo sich die aktive Zelle zum Zeitpunkt befindet, zu dem man die Regeln anlegt. Denn die dort verwendeten Zellbezüge sind immer relativ zur aktiven Zelle zu sehen.
Also einfach nochmal die Bezüge in der Formatierungsregel prüfen.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für diese Anwendung. Sie hat mir sehr geholfen.
Jedoch habe ich für mich das Problem, wenn ich in meiner Tabelle nach der Zahl 11 suche auch z.B. die Zahlen 110, 115… farblich makiert werden. Oder ich nach GS suche und auch die Zeile mit Gasschrank farblich makiert wird. Müsste ich die Formel anpassen?
Schöne Grüße, Willi
Hallo Willi,
das dürfte eher schwierig werden, für die Zahlensuche wüsste ich auch keine Lösung.
Wenn bei der Textsuche die exakte Schreibweise wichtig ist, dann könntest du statt der SUCHEN-Funktion die FINDEN-Funktion nutzen. Die berücksichtigt Groß- und Kleinschreibung, hat allerdings den Nachteil, dass keine Jokerzeichen (* oder ?) verwendet werden können. Das geht nur mit der SUCHEN-Funktion.
Schöne Grüße,
Martin
Hallo Martin,
ich habe auch selber noch länger nach einer Lösung gesucht. Gefunden habe ich nichts. Deshalb bleibt sie jetzt so. Aber vielen Dank nochmals für Deine Hilfe.
Eine Frage habe ich jedoch. Mir war es immer zu umständlich, nach jeder Eingabe erst die Entertaste zu betätigen um die Zeile farbig zu markieren. Deshalb habe ich mir ein Textfeld Steurelement eingefügt und wie in deinem Beispiel dann mit C5 (LinkedCell) verlinkt. Funktioniert für mich jetzt besser. Jedoch,wenn nichts in dem Textfeld steht, ist nun alles farbig.
Geht das so überhaupt nicht? Oder habe ich nur etwas übersehen?
Viele Grüße, Willi
Hallo Willi,
ich habe das gerade bei mir ausprobiert und bekomme das gleiche Verhalten. Offensichtlich führt die Verlinkung mit der Zelle C5 dazu, dass bei einem leeren Textfeld trotzdem irgendein nicht sichtbarer Wert enthalten ist. Ich habe leider auch keine Einstellung gefunden, die das verhindern würde.
Schöne Grüße,
Martin