Durch das Hinzufügen einer bedingten Formatierung in Excel können Sie verschiedene Formatierungsoptionen auf eine Zelle oder einen Zellbereich anwenden, die bestimmte von Ihnen festgelegte Bedingungen erfüllen. Das Festlegen solcher Bedingungen kann helfen, Ihre Tabelle zu organisieren und das Scannen zu vereinfachen. Zu den Formatierungsoptionen, die Sie verwenden können, gehören Änderungen der Schrift- und Hintergrundfarbe, Schriftstile, Zellenränder und das Hinzufügen von Zahlenformatierungen zu Daten.
Excel verfügt über integrierte Optionen für häufig verwendete Bedingungen, z. B. das Finden von Zahlen, die größer oder kleiner als ein bestimmter Wert sind, oder das Finden von Zahlen, die über oder unter dem Durchschnittswert liegen. Zusätzlich zu diesen voreingestellten Optionen können Sie mithilfe von Excel-Formeln auch benutzerdefinierte Regeln für die bedingte Formatierung erstellen.
Diese Anleitung gilt für Excel 2019, 2016, 2013, 2010 und Excel für Microsoft 365.
Mehrere Bedingungen in Excel anwenden
Sie können mehr als eine Regel auf dieselben Daten anwenden, um sie auf unterschiedliche Bedingungen zu testen. Beispielsweise können für Budgetdaten Bedingungen festgelegt sein, die Formatierungsänderungen anwenden, wenn bestimmte Ausgabenniveaus erreicht werden, z. B. 50 %, 75 % und 100 % des Gesamtbudgets.
Unter solchen Umständen bestimmt Excel zuerst, ob die verschiedenen Regeln in Konflikt stehen, und wenn ja, folgt das Programm einer festgelegten Rangfolge, um zu bestimmen, welche bedingte Formatierungsregel auf die Daten angewendet werden soll.
Das Finden von Daten, die 25 % und 50 % überschreiten, erhöht sich
Im folgenden Beispiel werden zwei benutzerdefinierte bedingte Formatierungsregeln auf den Bereich von Zellen B2 bis B5. angewendet
- Die erste Regel prüft, ob die Daten in Zellen A2:A5 größer sind als der entsprechende Wert in B2:B5 mehr als 25 %.
- Die zweite Regel prüft, ob dieselben Daten in A2:A5 den entsprechenden Wert in B2:B5 um mehr als überschreiten 50 %.
Wie im obigen Bild zu sehen ist, ändert sich die Hintergrundfarbe der Zelle oder der Zellen im Bereich B1:B4, wenn eine der obigen Bedingungen zutrifft.
- Bei Daten, bei denen der Unterschied mehr als 25 % beträgt, ändert sich die Hintergrundfarbe der Zelle in Grün.
- Wenn die Differenz größer als 50 % ist, ändert sich die Hintergrundfarbe der Zelle auf Rot.
Die für diese Aufgabe verwendeten Regeln werden im Dialogfeld Neue Formatierungsregel eingegeben. Beginnen Sie mit der Eingabe der Beispieldaten in die Zellen A1 bis C5 wie im obigen Bild zu sehen.
Im letzten Teil des Tutorials werden wir den Zellen C2:C4 Formeln hinzufügen, die den genauen prozentualen Unterschied zwischen den Werten in den Zellen A2:A5 zeigen und B2:B5; Auf diese Weise können wir die Genauigkeit der Regeln für die bedingte Formatierung überprüfen.
Regeln für die bedingte Formatierung festlegen
Zunächst wenden wir die bedingte Formatierung an, um eine signifikante Steigerung von 25 Prozent oder mehr zu finden.
Die Funktion sieht so aus:
=(A2-B2)/A2>25%
- Markieren Sie Zellen B2 bis B5 im Arbeitsblatt.
- Klicken Sie auf die Registerkarte Startseite des Menübands.
-
Klicken Sie auf das Symbol Bedingte Formatierung im Menüband, um das Dropdown-Menü zu öffnen.
- Wählen Sie Neue Regel, um das Dialogfeld Neue Formatierungsregel zu öffnen.
- Klicken Sie unter Regeltyp auswählen auf die letzte Option: Mit einer Formel bestimmen, welche Zellen formatiert werden sollen.
- Geben Sie die oben notierte Formel in das Feld darunter ein Formatieren Sie Werte, bei denen diese Formel zutrifft:
- Klicken Sie auf die Sch altfläche Format, um das Dialogfeld zu öffnen. Klicken Sie auf den Tab ausfüllen und wählen Sie eine Farbe aus.
- Klicken Sie auf OK, um die Dialogfelder zu schließen und zum Arbeitsblatt zurückzukehren.
- Die Hintergrundfarbe der Zellen B3 und B5 sollte sich in die von Ihnen ausgewählte Farbe ändern.
Nun wenden wir die bedingte Formatierung an, um eine Steigerung von 50 Prozent oder mehr zu finden. Die Formel sieht dann so aus:
- Wiederholen Sie die ersten fünf Schritte oben.
- Geben Sie die Formel oben in das Feld darunter ein Formatieren Sie Werte, bei denen diese Formel wahr ist:
- Klicken Sie auf die Sch altfläche Format, um das Dialogfeld zu öffnen. Klicken Sie auf die Registerkarte ausfüllen und wählen Sie eine andere Farbe als in den vorherigen Schritten.
- Klicken Sie auf OK, um die Dialogfelder zu schließen und zum Arbeitsblatt zurückzukehren.
Die Hintergrundfarbe von Zelle B3 sollte gleich bleiben, um anzuzeigen, dass der prozentuale Unterschied zwischen den Zahlen in Zellen A3 undist B3 ist größer als 25 Prozent, aber kleiner oder gleich 50 Prozent. Die Hintergrundfarbe von Zelle B5 sollte sich in die neue Farbe ändern, die Sie ausgewählt haben, was anzeigt, dass der prozentuale Unterschied zwischen den Zahlen in Zellen A5 und ist B5 ist größer als 50 Prozent.
Bedingte Formatierungsregeln prüfen
Um zu überprüfen, ob die eingegebenen bedingten Formatierungsregeln korrekt sind, können wir Formeln in die Zellen C2:C5 eingeben, die den genauen prozentualen Unterschied zwischen den Zahlen in den Bereichenberechnen A2:A5 und B2:B5.
Die Formel in Zelle C2 sieht so aus:
=(A2-B2)/A2
- Klicken Sie auf Zelle C2, um sie zur aktiven Zelle zu machen.
- Geben Sie die obige Formel ein und drücken Sie die Taste Enter auf der Tastatur.
- Die Antwort 10 % sollte in Zelle C2 erscheinen, was darauf hinweist, dass die Zahl in Zelle A2 10 % größer ist als die Zahl in Zelle B2.
- Möglicherweise muss die Formatierung in Zelle C2 geändert werden, um die Antwort in Prozent anzuzeigen.
- Verwenden Sie das Ausfüllkästchen, um die Formel von Zelle C2 nach Zelle C3 zu kopieren C5.
- Die Antworten für die Zellen C3 bis C5 sollten 30 %, 25 % und 60 % lauten.
Die Antworten in diesen Zellen zeigen, dass die Regeln für die bedingte Formatierung korrekt sind, da der Unterschied zwischen Zellen A3 und B3 größer als 25 ist Prozent, und der Unterschied zwischen Zellen A5 und B5 ist größer als 50 Prozent.
Zelle B4 hat die Farbe nicht geändert, weil der Unterschied zwischen Zelle A4 und B4 gleich ist 25 Prozent, und unsere bedingte Formatierungsregel gab an, dass ein Prozentsatz von mehr als 25 Prozent erforderlich war, damit sich die Hintergrundfarbe ändert.
Rangordnung für bedingte Formatierung
Wenn Sie mehrere Regeln auf denselben Datenbereich anwenden, bestimmt Excel zuerst, ob die Regeln in Konflikt stehen. Widersprüchliche Regeln sind solche, bei denen die Formatierungsoptionen nicht beide auf dieselben Daten angewendet werden können.
In unserem Beispiel kollidieren die Regeln, da beide dieselbe Formatierungsoption verwenden - Ändern der Hintergrundzellenfarbe.
In der Situation, in der die zweite Regel wahr ist (der Wertunterschied zwischen zwei Zellen beträgt mehr als 50 Prozent), dann ist auch die erste Regel (der Wertunterschied beträgt mehr als 25 Prozent) wahr.
Da eine Zelle nicht gleichzeitig zwei verschiedene Hintergrundfarben haben kann, muss Excel wissen, welche bedingte Formatierungsregel angewendet werden soll.
Die Rangfolge von Excel besagt, dass die Regel, die in der Liste im Dialogfeld "Regeln für bedingte Formatierung" weiter oben steht, zuerst angewendet wird.
Wie in der obigen Abbildung gezeigt, steht die zweite in diesem Tutorial verwendete Regel in der Liste weiter oben und hat daher Vorrang vor der ersten Regel. Als Ergebnis ist die Hintergrundfarbe von Zelle B5 grün.
Standardmäßig werden neue Regeln an den Anfang der Liste gesetzt; Um die Reihenfolge zu ändern, verwenden Sie die Pfeilsch altflächen Nach oben und Nach unten im Dialogfeld.
Anwendung widersprüchlicher Regeln
Wenn zwei oder mehr Regeln zur bedingten Formatierung nicht in Konflikt stehen, werden beide angewendet, wenn die Bedingung, die jede Regel testet, wahr wird.
Wenn die erste bedingte Formatierungsregel in unserem Beispiel den Zellbereich B2:B5 mit einem orangefarbenen Rand anstelle einer orangefarbenen Hintergrundfarbe formatiert hätte, würden die beiden bedingten Formatierungsregeln dies nicht tun Konflikt, da beide Formate angewendet werden können, ohne sich gegenseitig zu stören.
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.
