Suchfunktion mit bedingter Formatierung 59

Artikelbild 139
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!

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Martin Weiß Antworten abbrechen

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

59 Gedanken zu “Suchfunktion mit bedingter Formatierung

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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 😉

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        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

  • Avatar-Foto
    Hermann Hirschberger

    Hallo Martin,

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

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

    wünscht Dir

    Hermann

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Ilko Loedige

    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,

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Gianni

    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

  • Avatar-Foto
    Daiane

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Reiner

    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!

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Ronja

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Dirk Krüger

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Frank Book

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Carolin

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Hendrik

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Marek

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Anja

    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.

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Anja

        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

  • Avatar-Foto
    Peter

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Jens K.

    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?

    • Avatar-Foto
      Martin Weiß

      Hallo Jens,

      nein, die von mir vorgestellte Suchbox funktioniert nur mit einem einzelnen Eingabefeld.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Roswitha

    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

  • Avatar-Foto
    Carolin

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Michael Feick

    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!

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Michael Feick

        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

        • Avatar-Foto
          Martin Weiß

          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

  • Avatar-Foto
    Ulli Kersten

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    André

    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é

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        André

        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é

        • Avatar-Foto
          Martin Weiß

          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

          • Avatar-Foto
            André

            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é

          • Avatar-Foto
            Martin Weiß

            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,

          • Avatar-Foto
            André

            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é

          • Avatar-Foto
            Martin Weiß

            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

          • Avatar-Foto
            André

            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é

          • Avatar-Foto
            Martin Weiß

            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

  • Avatar-Foto
    Anja

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Susanne Voges

    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

    • Avatar-Foto
      Martin Weiß

      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