Hervorhebung im Kassenbuch
Mit bedingter Formatierung Suchergebnisse anzeigen
Kategorie: Excel-Tipp | Artikel vom 01.11.2013 | Autor: Martin
Kramer
Ich selbst verwende Excel für viele Dinge und habe es dabei oft
mit langen Listen zu tun. Um in diesen Listen bestimmte Einträge
schnell wieder zu finden, benutze ich die
bedingte Formatierung.
Ein Suchbegriff in der Zelle E2 (siehe Abbildung) sorgt dafür,
dass alle Einträge, in denen dieser Begriff vorkommt, farbig
hervorgehoben werden.
Im Beispiel geht es um ein (stark vereinfachtes) Kassenbuch.
So geht's ...
Im Menüband START gibt es den Befehl Bedingte
Formatierung.
Neben mehreren vordefinierten Formatierungsregeln kannst
Du weiter unten über den Befehl Neue Regel ... selbst eine
Regel definieren.
Klick also auf diesen Befehl (Neue Regel...) und
entscheide Dich dann dafür, eine
Formel zur Ermittlung der zu formatierenden Zellen zu
erstellen.
Ganz Wichtig !
Vorher richtig markieren
Die Markierungsrichtung ist
ausschlaggebend dafür, ob Ihre Formel funktioniert oder nicht.
Nun zurück zur Neuen Regel - Sie klicken sich also
zunächst durch die folgenden Befehle:
- Bedingte Formatierung
- Neue Regel
- Formel zur Ermittlung der zu formatierenden Zellen
Zum besseren Verständnis
was man wissen muss
Als Excel-User ist man es gewohnt, eine Formel immer in eine
Zelle einzugeben. In vielen Fällen wird anschließend diese einmal
eingegebene Formel in weitere Zellen kopiert (runtergezogen).
Theoretisch passiert das hier auch. Die Formel, die in die
abgebildete Dialogbox eingetragen wird bezieht sich zunächst auf die
Zelle B7 (die aktive weiße Zelle im markierten Bereich).
Das heißt, die Eingabe der Formel geschieht aus Sicht der Zelle
B7. Excel überträgt diese Formel aber automatisch in den gesamten
markierten Bereich und passt die Zellbezüge dabei entsprechend an.
So, als würden wir diese Formel eingeben und anschließend runter-
und rüberziehen (kopieren).
Schritt-für-Schritt-Herleitung der Formel
Überlegungen zum Aufbau der Formel
Nach dem Suchbegriff "papier" (dem Inhalt aus Zelle E2) soll in
der Spalte C und in der Spalte D gesucht werden.
Der entsprechende Teil der Formel sieht folgendermaßen aus:
Gesucht wird der Inhalt von E2 in den zusammengefassten Inhalten
von C7 und D7 - Soweit klar?
Wenn diese Suche erfolgreich ausfüllt, ist das Ergebnis die
Position, an der der Suchbegriff gefunden wurde. In dem Wort
"Druckerpapier" wäre das Beispielsweise die 8, da dort das "papier"
beginnt.
Das heißt, wenn das Ergebnis dieses Formelteils größer als Null
ist, dann wurde der Suchbegriff gefunden. Die fast fertige Formel
sieht dann so aus:
übrigens sind die Formeln für bedingte Formatierungen verwandt
mit der WENN-Funktion oder besser gesagt mit der Bedingung in der
WENN-Funktion.
Wenn das Ergebnis der SUCHE von E2 in den Zellen C7 und D7 größer
Null ist, dann formatiere.
Mit dieser Formel würde die bedingte Formatierung schon fast
funktionieren. Nun muss man aber bedenken, dass Excel (automatisch
im Hintergrund) diese Formel in den gesamten markierten Bereich
überträgt und die Zellbezüge automatisch anpasst.
Damit Excel dies nicht willkürlich tut, müssen bestimmte
Zellbezüge "festgesetzt", also zu absoluten oder teilabsoluten
Bezügen gemacht werden.
Die Zelle E2 wird komplett festgesetzt, da der Suchbegriff dort
steht. Bei C7 und D7 wird nur die Spalte festgesetzt (nicht die
Zeilennummer), da in jeder neuen Zeile neue Einträge stehen, muss
Excel diese Bezüge entsprechend anpassen können.
Noch ein Problem
ohne Suchbegriff wird alles hervorgehoben
Jetzt wäre die Formel eigentlich fertig, wenn nicht folgendes
Problem auftreten würde:
Wenn kein Suchbegriff eingegeben wird, also die Zelle E2 leer
bleibt, werden alle Zeilen des Kassenbuches farbig hervogehoben. Das
ist natürlich blöd.
Die Formel muss daher noch prüfen, ob in E2 überhaupt etwas
eingetragen wurde. Das heißt, es müssen zwei Bedingungen
erfüllt sein bevor Excel formatiert. Es muss also außerdem noch
geprüft werden, ob E2 überhaupt einen Inhalt hat.
Die endgültige und funktionierende Formel sieht dann so aus:
Zuletzt wird auf die Schaltfläche [Formatieren]
geklickt, um das gewünschte Format einzustellen.
Hinweis: Die Formel wird natürlich
ohne Leerstellen eingegeben. Ich habe in meinen Abbildungen
Leerstellen verwendet, um die Formeln übersichtlicher darzustellen.