Die bedingte Formatierung in Excel kann mehr als nur Zahlengrößen unterschiedlich kennzeichnen. Der Zelleninhalt kann nämlich mithilfe von Formeln, welche aus logischen Operatoren bestehen, verglichen werden, um bestimmte Zellen hervorzuheben.
Logischen Operatoren
Logische Operatoren ermöglichen mehrere Bedingungen gleichzeitig aufzustellen. Die wichtigsten werden hier kurz erläutert:
ODER: Prüft, ob mindestens eine der gewählten Bedingungen zutrifft.
UND: Prüft, ob alle gewählten Bedingungen zutreffen.
XODER: Prüft, ob genau eine der gewählten Bedingungen zutrifft.
Excel gibt typischerweise als Ausgabe „Wahr“ bzw. „Falsch“ aus.
Vorgehensweise der Bedingten Formatierung mithilfe von Formeln
Gegeben sind Daten, die formatiert werden sollen.
Um eine bedingte Formatierung mithilfe von Formeln zu erstellen, können folgende Schritte befolgt werden (etwas mehr Grundlagen zur Bedingten Formatierung findest du in dem Artikel: Excel Zellen mit Daten formatieren
- Klicke auf START > Bedingte Formatierung > Neuer Regel
- Wähle den Regeltyp Formel zur Ermittlung der zu formatierenden Zellen verwenden
- Füge in dem Feld unter Werte formatieren, für die diese Formel wahr ist: eine Formel ein
- Klicke auf Formatieren und kreiere ein beliebigen Format
- Klicke auf OK, um die Formatierung zu bestätigen
- Klicke auf OK, um die festgelegte Regel anzuwenden.
Im Fenster Formatieren, was sich im 4. Schritt öffnet, wird das Format gewählt, welches die Zellen annehmen, die die gewählte „Wahrheitsformel“ in Schritt 3 mit „Wahr“ erfüllen. Diese benötigt Excel auch zum Arbeiten, daher wird in der folgenden Tabelle für Erfahrung WAHR verwendet, anstelle von „vorhanden“ oder ähnlichem. Dabei kann der Nutzer auf verschiedene Formatierungselemente zugreifen.
Aufbau
Unter den verschiedenen Aspekten, die eine neue Regel bieten kann bleibt noch die Überlegung wie die Formel auszusehen hat. Hierbei ist zu beachten, dass Excel „WAHR“ oder „FALSCH“ Angaben erwartet. Diese erreicht man oft mit Vergleichen zwischen zwei Zellen. Weiterhin kann Excel auch Abstufungen, aber auch hier werden indirekt „WAHR“ oder „FALSCH“ Angaben erwartet, wobei man Excel die Arbeit überlassen kann, auszurechnen welcher der höchste Wert und welcher der kleinste ist, ebenso die Abstufungen. Möchte man einfach zwei Werte vergleichen, so kann man sobald man eine Formel eingeben soll einfach eine Zelle anklicken, ein Vergleichszeichen (kleiner oder größer) und dann die nächste Zelle anklicken. Dadurch werden diese Zellen verglichen und eine Regel erstellt. Da es verschieden Arten von vergleichen gibt, ist hier viel Spielraum. Es funktioniert aber alles nach dem selben Prinzip.
Anwendung auf mehrere Zeilen
Die Regeltypen kann man im Allgemeinen einfach auf mehrere Zeilen anwenden, besonders einfach ist dies bei den ersten Regeltypen, die beispielsweise Farbskalen obere/untere Werte markieren. Dazu muss man lediglich die zu färbende Tabelle markieren und wie gewohnt die Regel erstellen. Hier ist zu beachten, dass wenn man „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ benutzt und versucht mehrere Werte zu vergleichen, dann nimmt Excel dies als eine ODER-Funktion. Das heißt sobald eine der Ungleichungen erfüllt ist wird das ausgewählte Feld (oder auch die ausgewählten Felder) markiert. Hier muss für jedes Problem eine Lösung angepasst werden, da Excel dies nicht automatisch lösen kann. Für jegliche Übersichtsanpassungen bieten sich aber im Allgemeinen die anderen Regeltypen an. Man muss lediglich aussuchen welcher Typ am besten passt. Nur falls es wirklich erforderlich ist muss man die Regel auf jede Zelle einzeln anwenden.
Aufgabenstellung: Gegeben sind Daten die das Alter, die Note und den Erfahrungsstand von mehreren Bewerbern beinhaltet. Formatiere die Zellen mit fetter, grüner Schrift, die folgende Bedingungen erfüllen: Alter<18, Note<3 oder Erfahrung=“WAHR“. Formatiere die Zellen mit fetter, roter Schrift, die folgende Bedingungen erfüllen: Alter>=18, Note>=3 und Erfahrung=“FALSCH“.
Für diese Aufgabe können wir nicht beides gleichzeitig machen, da wir einmal mindestens einen Wert gut haben wollen und einmal dort markiere, wo alle Werte schlecht sind.
Regel 1: Wiederhole die Schritte 1. und 2. Füge im Feld unter Werte formatieren, für die diese Formel WAHR ist: die Formel =ODER(C4<18;D4<3;E4). Diese Formel testet ob mindestens eine der Bedingungen WAHR ist. Zuerst wird getestet ob C4<18 ist, also ob das Alter von Thomas B. kleiner als 18 ist. Danach wird D4<3 getestet, also ob die Note von Thomas B. kleiner 4 ist. Zuletzt wird getestet ob er Erfahrung hat oder nicht, ist in dieser Zelle WAHR angegeben, so wird Excel WAHR ausgeben, Wahrheitswerte müssen nicht verglichen werden wie das Alter oder die Note, hier geht es nur darum ob in der Zelle WAHR oder FALSCH steht.
Um es sich einfach zu machen, kann man nun die für uns interessante Zelle markieren (also F4) und unten rechts das schwarze Viereck anklicken und runterziehen, so wird die gleiche Formel für die verschiedenen Zeilen fortgesetzt. Da wir nun eine Wahrheitstabelle haben, kann man die Formatierung darauf anwenden, hier machen natürlich nur 2 Farben Sinn, wie vorgegeben rot für FALSCH und grün für WAHR. Dazu einmal exemplarisch an dieser Tabelle: Man erstellt eine neue Regel, hier bietet sich der Regeltyp „Nur Zellen formatieren, die enthalten“. Der Zellwert soll „gleich“ mit dem Wert WAHR sein, dann wird die Zelle grün formatiert. Um die Farbe auszuwählen klicken Sie auf „Formatieren“ dann „Ausfüllen“, hier wählen Sie die Farbe Grün und bestätigen. Nun bestätigen Sie und alle Kandidaten, die WAHR als Wert haben werden grün markiert. Ebenso macht man es für FALSCH mit der Farbe Rot. So sehen danach die Tabelle und die Fenster aus.
Regel 2: Nun markieren wir die Werte rot bei denen alles schlecht ist. Zunächst füllen wir wieder die Tabelle um zu sehen welche Kandidaten tatsächlich keinen Wert gewünscht erfüllen. Dazu verwenden wir die Formel: UND(C4>=18;D4>=3;NICHT(E4)). Diese prüft, ob das Alter größer gleich 18 ist und ob die Note größer gleich (schlechter gleich) 3 ist. Zum Schluss wird geprüft ob in E4 FALSCH oder WAHR angegeben ist, wir möchten die Kandidaten markieren, die keine Vorkenntnisse haben, haben aber bereits zwei wahre Werte und möchten für die UND-Funktion den Wert WAHR haben, falls keine Erfahrung vorhanden ist, also der Erfahrungswert FALSCH ist. Hierfür kann man keine Gleichheitszeichen verwenden, da damit nur Zahlen verglichen werden können. Daher verwenden wir hier die NICHT()-Funktion, diese kehrt den Wert FALSCH zu WAHR um und somit haben wir für einen schlechten Kandidaten tatsächlich drei WAHR-Werte und somit ergibt die UND-Funktion WAHR. Hierzu ein Screenshot, man sieht hier auch die Fenster zum einfärben, wie es oben schon gemacht wurde.
Das Ergebnis ist, dass nur Marc T. alle Kriterien gleichzeitig erfüllt und daher rot markiert wird.
Fazit
Diese Regeln lassen sich sehr vielseitig anwenden. Auch die „neue Regel“ Funktion bietet sehr viel, unter anderem auch die bereits vereinfachten Funktionen die im Thema „Zellen formatieren durch Daten“ vorgestellt wurden. Hier hat man aber noch einige zusätzliche Möglichkeiten. Man kann komplexe Aufteilungen so wesentlich leichter umsetzen und mit logischen Operatoren arbeiten, dies ermöglicht vollkommen eigene Veranschaulichungen. Welche Möglichkeiten Du hast um eine Sinnvolle Visualisierung zu erhalten siehst Du im eben genannten Thema. Dies lässt sich auch hier anwenden, aber wesentlich differenzierter.