Verwendung der bedingten Formatierung in Excel

Durch Hinzufügen einer bedingten Formatierung in Excel können Sie einer Zelle oder einem Zellbereich verschiedene Formatierungsoptionen zuweisen, die bestimmte von Ihnen festgelegte Bedingungen erfüllen. Das Festlegen solcher Bedingungen kann Ihnen dabei 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, Zellränder und das Hinzufügen von Zahlenformatierungen zu Daten.

Excel verfügt über integrierte Optionen für häufig verwendete Bedingungen, z. B. das Auffinden von Zahlen, die größer oder kleiner als ein bestimmter Wert sind, oder das Auffinden von Zahlen, die über oder unter dem Durchschnittswert liegen. Zusätzlich zu diesen voreingestellten Optionen können Sie mithilfe von Excel-Formeln benutzerdefinierte bedingte Formatierungsregeln erstellen.

Diese Anweisungen gelten für Excel 2019, 2016, 2013, 2010 und Excel für Office 365.

Anwenden mehrerer Bedingungen in Excel

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 Ausgabenebenen erreicht werden, z. B. 50%, 75% und 100% des Gesamtbudgets.

Unter diesen Umständen ermittelt Excel zunächst, ob die verschiedenen Regeln in Konflikt stehen. In diesem Fall 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, nimmt zu

Im folgenden Beispiel werden zwei benutzerdefinierte bedingte Formatierungsregeln auf den Bereich der Zellen B2 bis B5 angewendet.

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

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

  • Bei Daten mit einem Unterschied von mehr als 25% ändert sich die Hintergrundfarbe der Zelle in Grün.
  • Wenn der Unterschied größer als 50% ist, ändert sich die Zellenhintergrundfarbe in Rot.

Die Regeln für diese Aufgabe werden im Dialogfeld Neue Formatierungsregel eingegeben. Beginnen Sie mit der Eingabe der Beispieldaten in die Zellen A1 bis C5, wie im obigen Bild dargestellt.

Im Finale Im 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 und B2: B5 anzeigen . Auf diese Weise können wir die Genauigkeit der bedingten Formatierungsregeln überprüfen.

Festlegen bedingter Formatierungsregeln

Zunächst wenden wir die bedingte Formatierung an, um eine signifikante Steigerung von 25 Prozent oder mehr zu ermitteln.

Die Funktion sieht folgendermaßen aus:

= (A2-B2) / A2> 25%
  1. Markieren Sie die Zellen B2 bis B5 im Arbeitsblatt.

  2. Klicken Sie auf die Registerkarte Home des Menübands .

  3. Klicken Sie im Menüband auf das Symbol Bedingte Formatierung , um das Dropdown- Menü zu öffnen.

  4. Wählen Sie Neue Regel , um das Dialogfeld Neue Formatierungsregel zu öffnen.

  5. Klicken Sie unter Regeltyp auswählen auf die letzte Option: Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen.

  6. Geben Sie die oben angegebene Formel in das Feld unter den Formatwerten ein, in denen diese Formel zutrifft:

  7. Klicken Sie auf die Schaltfläche Format , um das Dialogfeld zu öffnen. Klicken Sie auf die Registerkarte Füllen und wählen Sie eine Farbe aus.

  8. Klicken Sie auf OK , um die Dialogfelder zu schließen und zum Arbeitsblatt zurückzukehren.

  9. Die Hintergrundfarbe der Zellen B3 und B5 sollte sich zu der Farbe ändern, die Sie auswählten.

Jetzt wenden wir die bedingte Formatierung an, um eine Steigerung von mindestens 50 Prozent zu erzielen. Die Formel sieht folgendermaßen aus:

  1. Wiederholen Sie die ersten fünf Schritte oben.

  2. Geben Sie die oben angegebene Formel in das Feld unter Format ein, wobei diese Formel zutrifft:

  3. Klicken Sie auf die Schaltfläche Format , um das Dialogfeld zu öffnen. Klicken Sie auf die Registerkarte Füllen und wählen Sie eine andere Farbe als in den vorherigen Schritten.

  4. Klicken Sie auf OK , um die Dialogfelder zu schließen und zum Arbeitsblatt zurückzukehren.

Die Hintergrundfarbe der Zelle B3 sollte gleich bleiben, um anzuzeigen, dass der prozentuale Unterschied zwischen den Zahlen in den Zellen A3 und B3 größer als 25 Prozent, aber kleiner oder gleich 50 Prozent ist. Die Hintergrundfarbe von Zelle B5 sollte sich zu der neuen Farbe ändern, die Sie auswählten, um anzuzeigen, dass der prozentuale Unterschied zwischen den Zahlen in Zellen A5 und B5 größer als 50 Prozent ist.

Überprüfen der bedingten Formatierungsregeln

Um zu überprüfen, ob die eingegebenen bedingten Formatierungsregeln korrekt sind, können Sie Formeln in die Zellen C2: C5 eingeben, mit denen die genaue prozentuale Differenz zwischen den Zahlen in den Bereichen A2: A5 und B2: B5 berechnet wird.

Die Formel in Zelle C2 sieht folgendermaßen aus:

= (A2-B2) / A2
  1. Klicken Sie auf Zelle C2 , um sie zur aktiven Zelle zu machen.

  2. Geben Sie die obige Formel ein und drücken Sie die Eingabetaste auf der Tastatur.

  3. Die Antwort 10% sollte in Zelle C2 angezeigt werden , was angibt, dass die Zahl in Zelle A2 10% größer ist als die Zahl in Zelle B2 .

  4. Möglicherweise muss die Formatierung in Zelle C2 geändert werden, damit die Antwort in Prozent angezeigt wird.

  5. Verwenden Sie das Ausfüllkästchen, um die Formel von Zelle C2 in die Zellen C3 bis C5 zu kopieren.

  6. Die Antworten für die Zellen C3 bis C5 sollten 30%, 25% und 60% betragen.

Die Antworten in diesen Zellen zeigen, dass die bedingten Formatierungsregeln genau sind, da der Unterschied zwischen den Zellen A3 und B3 größer als 25 Prozent und der Unterschied zwischen den Zellen A5 und B5 größer als 50 Prozent ist.

Zelle B4 hat die Farbe nicht geändert, da der Unterschied zwischen den Zellen A4 und B4 25 Prozent beträgt, und unsere bedingte Formatierungsregel bestimmte, dass ein Prozentsatz von mehr als 25 Prozent erforderlich ist, damit sich die Hintergrundfarbe ändert.

Rangfolge für die bedingte Formatierung

Wenn Sie mehrere Regeln auf denselben Datenbereich anwenden, ermittelt Excel zunächst, ob ein Regelkonflikt vorliegt. Bei widersprüchlichen Regeln können die Formatierungsoptionen nicht auf dieselben Daten angewendet werden.

In unserem Beispiel besteht ein Regelkonflikt, da beide dieselbe Formatierungsoption verwenden - Ändern der Hintergrundzellenfarbe.

In der Situation, in der die zweite Regel zutrifft (der Wertunterschied zwischen zwei Zellen beträgt mehr als 50 Prozent), gilt auch die erste Regel (der Wertunterschied beträgt mehr als 25 Prozent).

Da eine Zelle nicht gleichzeitig zwei verschiedene Farbhintergründe haben kann, muss Excel wissen, welche bedingte Formatierungsregel angewendet werden soll.

In der Rangfolge von Excel wird angegeben, dass die Regel, die in der Liste im Dialogfeld Manager für Regeln für bedingte Formatierung höher ist, zuerst angewendet wird.

Wie in der obigen Abbildung gezeigt, ist die zweite in diesem Lernprogramm verwendete Regel in der Liste höher und hat daher Vorrang vor der ersten Regel. Folglich ist die Hintergrundfarbe der Zelle B5 grün.

Standardmäßig werden neue Regeln an den Anfang der Liste gestellt. Verwenden Sie zum Ändern der Reihenfolge die Aufwärts- und Abwärtspfeiltasten im Dialogfeld.

Anwenden widerspruchsfreier Regeln

Wenn zwei oder mehr bedingte Formatierungsregeln nicht in Konflikt stehen, werden beide angewendet, wenn die Bedingung, die jede Regel testet, erfüllt ist.

Wenn die erste bedingte Formatierungsregel in unserem Beispiel den Bereich der Zellen B2: B5 mit einem orangefarbenen Rand anstelle einer orangefarbenen Hintergrundfarbe formatiert hätte, würden die beiden bedingten Formatierungsregeln keinen Konflikt verursachen, da beide Formate angewendet werden können, ohne den anderen zu beeinträchtigen.

Bedingte Formatierung im Vergleich zur regulären 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.