Verwenden von Formeln für die bedingte Formatierung in Excel

Durch das Hinzufügen der bedingten Formatierung in Excel können Sie verschiedene Formatierungsoptionen auf eine Zelle oder einen Zellbereich anwenden, die die von Ihnen festgelegten Bedingungen erfüllen. Die Formatierungsoptionen werden nur angewendet, wenn die ausgewählten Zellen diese festgelegten Bedingungen erfüllen.

Zu den Formatierungsoptionen, die angewendet werden können, gehören Änderungen der Schriftart- und Hintergrundfarbe, Schriftstile, Zellrahmen und Hinzufügen von Zahlen zu Daten.

Excel verfügt über eine Reihe von integrierten Optionen für häufig verwendete Bedingungen, z. B. das Ermitteln von Zahlen, die größer oder kleiner als ein bestimmter Wert sind, oder das Ermitteln von Zahlen, die über oder unter dem Durchschnittswert liegen.

Neben diesen voreingestellten Optionen können auch benutzerdefinierte Regeln für die bedingte Formatierung mithilfe von Excel-Formeln erstellt werden, um auf benutzerdefinierte Bedingungen zu testen.

01 von 04

Anwenden mehrerer Regeln in Excel

Es können mehrere Regeln auf dieselben Daten angewendet werden, um unterschiedliche Bedingungen zu testen. Für Budgetdaten können z. B. Bedingungen festgelegt werden, die Formatierungsänderungen anwenden, wenn bestimmte Ebenen (z. B. 50%, 75% und 100% des Gesamtbudgets) ausgegeben werden.

Unter diesen Umständen ermittelt Excel zunächst, ob die verschiedenen Regeln in Konflikt stehen, und falls dies der Fall ist, folgt das Programm einer festgelegten Rangfolge, um zu bestimmen, welche bedingte Formatierungsregel auf die Daten angewendet wird.

Das Finden von Daten, die 25% und 50% übersteigen, steigt

Im folgenden Beispiel werden zwei benutzerdefinierte Regeln für die bedingte Formatierung auf den Bereich von angewendet Zellen B2 zu B5.

• Die erste Regel prüft, ob die Daten in sind Zellen A2 zu A5 ist größer als der entsprechende Wert in B2 zu B5 um mehr als 25%.
• Die zweite Regel prüft, ob die gleichen Daten in A2: A5 überschreitet den entsprechenden Wert in B2: B5 um mehr als 50%.

Wie im obigen Bild zu sehen, ist die Hintergrundfarbe der Zelle oder der Zellen im Bereich, wenn eine der obigen Bedingungen zutrifft B1: B4 wird sich verändern.

• Bei Daten, bei denen der Unterschied mehr als 25% beträgt, ändert sich die Hintergrundfarbe der Zelle in grün.
• Wenn der Unterschied mehr als 50% beträgt, ändert sich die Hintergrundfarbe der Zelle in Rot.

Die Regeln für diese Aufgabe werden mit der bedingten Formatierung eingegeben Neue Formatierungsregel Dialogbox. Beginnen Sie mit der Eingabe der Probendaten in die Zellen A1 bis C5 (siehe Abbildung oben)

In dem dritter Schritt des Tutorials fügt den Zellen Formeln hinzu C2: C4 die den genauen prozentualen Unterschied zwischen den Werten in Zellen anzeigen A2: A5 und B2: B5 um die Genauigkeit der Regeln für die bedingte Formatierung zu überprüfen.

02 von 04

Festlegen von Regeln für die bedingte Formatierung

Wie bereits erwähnt, werden die Regeln für die bedingte Formatierung, die die beiden Bedingungen prüfen, mit der bedingten Formatierung eingegeben Neue Formatierungsregel Dialogbox. Beginnen wir mit der Festlegung unserer benutzerdefinierten bedingten Formatierung:

Bedingte Formatierung für einen Anstieg von mehr als 25 Prozent

= (A2-B2) / A2> 25%

1. Markieren Zellen B2 zu B5 im Arbeitsblatt.
2. Klicke auf das Registerkarte Start des Band.
3. Klicke auf das Bedingte Formatierung Symbol in der Band um die Dropdown-Liste zu öffnen.
4. Wählen Neue Regel die öffnen Neue Formatierungsregel Dialogbox.
5. Von dem Stil: Wählen Sie das Feld aus Klassisch Möglichkeit.
6. Klicken Sie im zweiten Dropdown auf die letzte OptionVerwenden Sie eine Formel, um die zu formatierenden Zellen zu bestimmen.
7. Schreib die Formel oben in den vorgesehenen Raum vorgesehen.
8. Klicke auf das Formatieren mit: Dropdown-Menü und wählen Sie dann Hellrot Füllen Sie mit dunkelrotem Text Möglichkeit.
9. Klicken OK um die Dialogfelder zu schließen und zum Arbeitsblatt zurückzukehren.
10. Zu diesem Zeitpunkt ist die Hintergrundfarbe von Zellen B3 und B5 sollte grün sein.

Bedingte Formatierung zum Erhöhen von mehr als 50 Prozent

= (A2-B2) / A2> 50%

Markieren Zellen B2 zu B5 im Arbeitsblatt.

1. Klicke auf das Registerkarte Start des Band.
2. Klicke auf das Bedingte Formatierung Symbol in der Band um die Dropdown-Liste zu öffnen.
3. Wählen Neue Regel die öffnen Neue Formatierungsregel Dialogbox.
4. Von dem Stil: Wählen Sie das Feld aus Klassisch Möglichkeit.
5. Klicken Sie im zweiten Dropdown auf die letzte OptionVerwenden Sie eine Formel, um die zu formatierenden Zellen zu bestimmen.
6. Schreib die Formel oben in den vorgesehenen Raum vorgesehen.
7. Klicke auf das Formatieren mit: Dropdown-Menü und wählen Sie dann Hellgrün Fülle mit dunkelgrünem Text Möglichkeit.
8. Klicken OK um die Dialogfelder zu schließen und zum Arbeitsblatt zurückzukehren.

Die Hintergrundfarbe von Zelle B3 sollte immer noch grün sein, um anzuzeigen, dass der prozentuale Unterschied zwischen den Zahlen in Zellen A3 und B3 ist größer als 25 Prozent, aber kleiner oder gleich 50 Prozent. Die Hintergrundfarbe von Zelle B5 sollte rot werden, um anzuzeigen, dass der prozentuale Unterschied zwischen den Zahlen in Zellen A5 und B5 ist größer als 50 Prozent.

03 von 04

Bedingte Formatierungsregeln prüfen

Um zu überprüfen, ob die eingegebenen Regeln für die bedingte Formatierung korrekt sind, können Sie Formeln in Zellen eingeben C2: C5 das berechnet die genaue prozentuale Differenz zwischen den Zahlen in den Bereichen A2: A5 und B2: B5.

= (A2-B2) / A2

1. Klicke auf Zelle C2 um es zur aktiven Zelle zu machen.
2. Geben Sie die obige Formel ein und drücken Sie die Eingeben Taste auf der Tastatur.
3. Die Antwort 10% sollte in erscheinen Zelle C2zeigt an, dass die Nummer in Zelle A2 ist 10% größer als die Zahl in Zelle B2.
4. Möglicherweise müssen Sie die Formatierung ändern Zelle C2 um die Antwort in Prozent anzuzeigen.
5. Verwenden Sie die Griff füllen die Formel aus kopieren Zelle C2 zu Zellen C3 zu C5.
6. Die Antworten für Zellen C3 zu C5 sollte 30%, 25% und 60% sein.

Die Antworten in diesen Zellen zeigen, dass die erstellten Regeln für die bedingte Formatierung seit dem Unterschied korrekt sind Zellen A3 und B3 ist größer als 25 Prozent und der Unterschied zwischen Zellen A5 und B5 ist größer als 50 Prozent.

Zelle B4 Farbe nicht geändert, weil der Unterschied zwischen Zellen A4 und B4 entspricht 25 Prozent, und unsere Bedingung für die bedingte Formatierung gab an, dass ein Prozentsatz von mehr als 25 Prozent erforderlich war, damit die Hintergrundfarbe grün wird.

04 von 04

Rangfolge der bedingten Formatierung

Wenn mehrere Regeln auf denselben Datenbereich angewendet werden, ermittelt Excel zunächst, ob die Regeln in Konflikt stehen. Konfliktregeln sind Regeln, bei denen die gewählten Formatierungsoptionen für jede Regel nicht auf dieselben Daten angewendet werden können.

In dem in diesem Tutorial verwendeten Beispiel stehen die Regeln in Konflikt, da beide Regeln dieselbe Formatierungsoption verwenden, nämlich die Farbe der Hintergrundzelle.

In der Situation, in der die zweite Regel wahr ist (der Wertunterschied ist größer als 50 Prozent zwischen zwei Zellen), gilt auch die erste Regel (der Wertunterschied ist größer als 25 Prozent).

Vorrang von Excel

Da eine Zelle nicht gleichzeitig einen roten und einen grünen Hintergrund haben kann, muss Excel wissen, welche Regel für die bedingte Formatierung gelten soll.

Welche Regel angewendet wird, wird durch die Prioritätsreihenfolge von Excel festgelegt, die besagt, dass die Regel, die in der Liste im Dialogfeld "Bedingte Formatierungsregeln" enthalten ist, Vorrang hat.

Wie in der Abbildung oben gezeigt, ist die zweite Regel, die in diesem Lernprogramm verwendet wird, höher in der Liste und hat daher Vorrang vor der ersten Regel. Als Ergebnis wird die Hintergrundfarbe der Zelle B5 in Rot geändert.

Standardmäßig werden neue Regeln am Anfang der Liste hinzugefügt und haben daher eine höhere Priorität. Um die Rangfolge zu ändern, verwenden Sie die Auf und ab Pfeilschaltflächen im Dialogfeld wie in der Abbildung oben angegeben.

Nicht widersprüchliche Regeln anwenden

Wenn zwei oder mehr bedingte Formatierungsregeln nicht miteinander in Konflikt stehen, werden beide angewendet, wenn die Bedingung, die jede Regel prüft, wahr wird.

Wenn die erste bedingte Formatierungsregel in unserem Beispiel den Zellbereich formatiert B2: B5 Mit einem grünen Rahmen anstelle einer grünen Hintergrundfarbe würden die beiden Regeln für die bedingte Formatierung nicht in Konflikt stehen, da beide Formate angewendet werden können, ohne das andere zu beeinträchtigen.

Als Ergebnis, Zelle B5 hätte sowohl einen grünen Rand als auch eine rote Hintergrundfarbe, da der Unterschied zwischen den Zahlen in Zellen A5 und B5 ist größer als 25 und 50 Prozent.

Bedingte Formatierung vs. Normale Formatierung

Bei Konflikten zwischen bedingten Formatierungsregeln und manuell angewendeten Formatierungsoptionen hat die bedingte Formatierungsregel immer Vorrang und wird anstelle von manuell hinzugefügten Formatierungsoptionen angewendet.

Wenn ursprünglich eine gelbe Hintergrundfarbe angewendet wurde Zellen B2 zu B5 In diesem Beispiel wurden nur die Regeln für die bedingte Formatierung hinzugefügt Zellen B2 und B4 würde gelb bleiben Weil die eingegebenen Regeln für die bedingte Formatierung für Zellen gelten B3 und B5, würden ihre Hintergrundfarben von gelb zu grün bzw. rot wechseln.