Excel bedingte Formatierung - Beispiele - itService Thomas Käflein

=$E$4=$B4

• Definieren Sie Ihre Formatierungswünsche und bestätigen mit OK.

Der Bezug auf Zelle E4 ist als Absolutbezug definiert, der Bezug auf den Vergleichswert in B4 als gemischter Bezug, bei dem die Spalte fixiert ist, die Zeile jedoch nicht. Dadurch ist gewährleistet, dass der Vergleich nur mit den Werten aus Spalte B erfolgt, die Formatierung aufgrund der anfänglichen Markierung jedoch auf alle Zellen innerhalb der Markierung wirkt.

Ergebnis

Beispiel auf Tabellenblatt BF-1

Gleicher Inhalt in allen Zellen

Ziel

Ein Zellbereich soll formatiert werden, wenn alle Zellen im Vergleichsbereich denselben Wert enthalten.

Umsetzung

Mit Hilfe der Funktion IDENTISCH werden die einzelnen Werte auf Gleichheit überprüft. Da aber nur dann formatiert werden soll, wenn ALLE gleich sind, mussals äußere Funktion UND ergänzt werden.

Beachten Sie, dass die nachstehende Formel NICHT als Matrixformel einzugeben ist.

=UND(IDENTISCH(A4;$A$4:$A$8))

=REST(A4;2)=0

=ISTGERADE(D4)

=REST(A4;2)=1
=ISTUNGERADE(D4)

Ergebnis

Beispiel auf Tabellenblatt BF-3

Jede n-te Zeile einfärben

Ziel

In einem Bereich soll jede Zeile eingefärbt werden, deren Zeilennummer durch die in einer Zelle (hier D4) angegebene Zahl ohne Rest teilbar ist. Beachten Sie die verwendeten Adressierungsarten (gemischt und absolut).

Im Prinzip entspricht dies der Formatierungsmöglichkeit in einem Als Tabelle formatierten Datenbereich, jedoch mit der Möglichkeit, nicht starr jede zweite Zeile zu formatieren.

Umsetzung

=REST(ZEILE($A4);$D$4)=0

Ergebnis

Beispiel auf Tabellenblatt BF-4

Überfällige Rechnungen hervorheben

Ziel

In einer Tabelle liegen Rechnungsdaten vor. Die Daten sollen mit dem jeweils aktuellen Datum verglichen werden, wobei bereits überfällige Daten hervorgehoben werden.

Das aktuelle Datum (hier der 30.10.20) sowie das Zahlungsziel sind in Zellen vorhanden.

Umsetzung

• Formatieren Sie alle Datumszellen in grüner Füllfarbe.
  
• Markieren Sie den Bereich un fügen bedingte Formatierungen als Formelregeln ein:
  
• Mehr als 10 Tage überschritten (orange)
  

=$C$11-10>A4+$C$4

=$C$11>A4+$C$4

Beispiel auf Tabellenblatt BF-5

Wochenenden und Feiertage hervorheben

Ziel

In einem Kalenderblatt sind die Wochenenden in Grau, Feiertage in Blau formatiert werden. Feiertagsformatierungen haben dabei Vorrang. D. h., fällt ein Feiertag auf ein Wochenende, soll die Farbe des Feiertags und nicht die des Wochenendes angewendet werden. Beachten Sie daher die Reihenfolge der bedingten Formatierungen (Feiertage oben, Wochenenden darunter).

Umsetzung

• Feiertag (die Feiertagsliste steht im Bereich E4:F16)
  

=NICHT(ISTNV(SVERWEIS(A4;$E$4:$E$16;1;FALSCH)))

=WOCHENTAG(A4;2)>5

Ergebnis

Beispiel auf Tabellenblatt BF-6

Bedingte Formatierung dynamisch ein- und ausschalten

Ziel

Bisher hatten wir die Bedingte Formatierung so angewandt, dass sie immer wirkte. Will der Benutzer diese aber bei Bedarf ausschalten, ohne sie zu löschen, müssen wir eine weitere Bedingung einfügen.

Basierend auf dem vorherigen Beispiel verwenden wir Zelle C4 als Schalter. Steht in dieser Zelle das Wort ein, ist die Bedingte Formatierung aktiv. Steht etwas anderes in der Zelle ist sie deaktiviert.

Umsetzung

=NICHT($C$4="ein")

• Aktivieren Sie das Kontrollkästchen Anhalten. Verändern Sie kein Format.

Wichtig ist außerdem, diese Schreibweise in der Formel mit NICHT zu verwenden und nicht etwa $C$4<>"ein", da letztere nicht das korrekte Ergebnis liefert.

Ergebnis

Beispiel auf Tabellenblatt BF-7

Im linken Bild ist die eingeschaltete Version zu sehen, rechts die ausgeschaltete.

Jede 2-te Zeile einfärben - gefiltert

Ziel

Weiter oben hatten wir bereits eine bedingte Formatierung erstellt, mit deren Hilfe jede n-te Zeile eingefärbt werden kann. Sie funktioniert wunderbar ... außer wen n wir einen Filter anwenden und dadurch Zeilen ausblenden. In diesem Beispiel soll jede 2-te Zeile formatiert werden, auch bei vorgenommener Filterung.

Umsetzung

Für die Umsetzung verwenden wir die Funktion TEILEREGBNIS mit Parameter 3 (entspricht ANZAHL2), da diese Funktion nur die sichtbaren Zellen zählt.

=REST(TEILERGEBNIS(3;$A$3:$A3);2)<>0

Der angegebene Bereich beginnt immer in Zelle A3 und wird mit jeder Zelle nach unten erweitert. Beachten Sie daher die gemischte Bezugsart bei den Parametern.

Ergebnis

Beispiel auf Tabellenblatt BF-8

Wie man an den blauen Zeilennummern und dem Filtersymbol in der Überschrift im Bild erkennen kann, wurde die Liste gefiltert, aber die wechselnde Formatierung wirkt.

Wechsel Kunden-Nr anzeigen

Ziel

In einer Liste befinden sich eindeutige Kunden-Nummern, nach denen die Liste sortiert ist. Für einen Kunden können eine oder mehrere Zeilen vorhanden sein.Nach jedem Kundenwechsel soll die Farbe der Zeile wechseln.

Umsetzung

Zur Umsetzung bieten sich mehrere Versionen an, von denen ich hier eine vorstelle

Verwendet werden die geschachtelten Funktionen N (wandelt nicht numerischen Wert in eine Zahl um), SUMMENPRODUKT (ermittelt die Summe der Produkte) und REST (gibt Rest einer Division zurück) .
Beachten Sie auch hier wieder die gemischte Bezugsart (absolut/gemischt).

=REST(SUMMENPRODUKT(N($A$3:$A3<>$A$4:$A4));2)

Ergebnis

Beispiel auf Tabellenblatt BF-9

n größte hervorheben

Ziel

In einer Tabelle liegen Zahlen in mehreren Spalten vor. Die n größten dieser Zahlen je Spalte sollen farblich hervorgehoben werden.

Umsetzung

• Nach dem Markieren der ersten Zahlenspalte (hier B4:B9) geben Sie in das Formeleingabefeld einer neuen Regel folgendes ein (in A11 steht die Vorgabe, wieviele größte angezeigt werden sollen):
  

=B4>=KGRÖSSTE(B$4:B$9;$A$11)

• Anschließend kann die Formatierung auf die anderen Spalten kopiert werden.
  

Ergebnis

Beispiel auf Tabellenblatt BF-10