Google Sheets-Kochbuch: Bedingte Formatierungen

Beispiel für Formel in bedingter Formatierung

Ich liebe bedingte Formatierungen! Die Magie, wie sich z.B. Hintergrundfarben abhängig vom Inhalt von Tabellenzellen ändern - großartig! Das Menü finde ich recht selbsterklärend, daher soll es in diesem Artikel vor allem um benutzerdefinierte Formeln gehen.

Die Logik dahinter ist simpel: ist die benutzerdefinierte Formel wahr, wird die Formatierung angewendet. Ich rate dazu, mit IF-Formeln zu arbeiten, um nicht "zufällig" richtige Ergebnisse zu erhalten (sonst kann die Fehlersuche länger dauern). Als wahr werden allerdings auch Zahlen angesehen (außer der 0).

Die Formel wird immer für die aktuelle Zelle ausgewertet. Das ist am Anfang (und später) manchmal überraschend, auch wenn sich die Formel immer auf die erste Zelle des Bereiches bezieht, muss man bei relativen Zellbezügen immer überlegen, was ist die aktuelle Zelle und wie ändern sich möglicherweise jetzt die Zellbezüge.

Die benutzerdefinierte Formel beginnt - wie alle Formeln - mit einem Gleichheitszeichen.

• =IF(A1=$B$1;TRUE;FALSE)
  • Die aktive Zelle ist A1.
  • Wenn der Inhalt der Zelle A1 gleich dem Inhalt der Zelle $B$1 ist, wird die Formatierung angewendet (TRUE), sonst nicht (FALSE).
  • Die bedingte Formatierung wird auf den Bereich A1:A15 angewendet. Was passiert in der Zelle A2?
    • Die benutzerdefinierte Formel bleibt für alle Zellen des Bereiches die Gleiche.
    • Sie wird aber so angewendet, als wäre die Formel "herunterkopiert" worden, relative Zellbezüge ändern sich relativ. Es wird also abgefragt, ob A2 gleich $B$1 ist.
    • Wäre statt dem absoluten Zellbezug $B$1 ein relativer Zellbezug verwendet worden (also B1), würden die Spalten paarweise verglichen. Dann wäre die Bedingung A2=B2?

Wir können auch mehrere Spalten nebeneinander einfärben, dazu machen wir uns wieder absolute Zellbezüge nutzbar. So formatiert man allgemein Zellen basierend auf Werten in anderen Spalten.

• Als Bereich wird A1:B15 eingestellt.
• =IF($A1=$B$1;TRUE;FALSE)
  • Es wird also jetzt immer der Wert in Spalte A überprüft, die Formatierung aber auf den gesamten Bereich angewendet, also auch auf die Spalte B.

Benannte Bereiche und Verweise auf andere Tabellenblätter werden als String übergeben, und müssen mit INDIRECT derefenziert werden.

• =COUNTIF(INDIRECT("Suchbereich");$A1)>1
• =A1=INDIRECT("'Anderes Tabellenblatt'!B1")

Suchergebnisse hervorheben

Im einfachsten Fall finden wir das Suchergebnis oder die Suchergebnisse mit Match. Dann lautet die benutzerdefinierte Formel:

• =MATCH($B$1;A1;0)
  • Hier verwenden wir MATCH nur bei genauen Treffern.
  • MATCH liefert eine Zahl zurück (hier immer die 1), die gilt auch als TRUE.
• =A1=$B$1
  • Es geht noch einfacher. Hier wird verglichen, ob der Inhalt der Zelle A1 mit dem Inhalt der Zelle $B$1 übereinstimmt.

Alle Zeilen bis zu einem Suchergebnis hervorheben

Wenn die vorliegende Liste sortiert ist, und wir MATCH verwenden können, ist das sehr einfach. Es ist ein bisschen "fake", da wir ja nicht die ersten Ergebnisse einfärben, sondern die ersten Ergebnisse kleiner sind als der Suchwert.

• =MATCH($B$1;A1;1)
  • Bei einer aufsteigend sortierten Liste wird die Formatierung überall dort angewandt, wo der Wert kleiner als der Suchwert ist.
• =MATCH($B$1;A1;-1)
  • Und das gleiche für absteigend sortierte Listen.

Wenn wir bis zu einem bestimmten Suchwert formatieren wollen, können wir die Zeilennummer des Suchergebnisses verwenden. Dabei muss der Offset - also die Anfangszeile berücksichtigt werden.

• =if(ROW(Erste-Zelle-des-Bereiches)<ROW($Erste-Zelle-des-Bereiches)+MATCH(Suchwert;$Bereich;0);TRUE;FALSE)
  • Ist die aktuelle Zeile kleiner als der Offset + die Position des Suchergebnisses, ist die Bedingung wahr.
  • Die Zellbezüge müssen natürlich als richtige Zellbezüge angegeben werden.
  • Diese Methode hat einen Nachteil - die Position des Suchergebnisses muss für jede Zeile neu berechnet werden, obwohl sie sich nicht ändert. Es wäre aus Performance-Gründen sicher wesentlich besser, wenn die Position nur einmal berechnet werden müsste. Wenn Ihre Tabelle aber nicht zu groß ist (kleiner 1000 Reihen sollte kein Problem sein), und/oder Sie kein Performance-Problem haben, können Sie das natürlich ignorieren.

Ein konkretes Beispiel sieht so aus:

• =if(ROW(D5)<ROW($D$5)+MATCH($E$5;$D$5:$D19;0);TRUE;FALSE)
  • Der Suchbereich ist im Beispiel $D$5:$D$19. Dieser Bereich muss absolut angegeben werden.
  • Der Suchwert steht in der Zelle $E$5. Auch dieser Bezug muss absolut angegeben werden.
  • Den Offset liefert uns ROW($D$5), das ist die erste Zeile des Bereiches. Auch diese muss absolut angegeben werden.
  • D5 ist die jeweils aktuelle Zelle, die bedingte Formatierung verändert den Zellbezug.

n Zeilen über/unter einem Suchergebnis hervorheben

• =IF(OR(ROW($Erste-Zelle-des-Bereiches)+MATCH(Suchwert;$Bereich;0)-N>ROW(Erste-Zelle-des-Bereiches);ROW(Erste-Zelle-des-Bereiches)>=ROW($Erste-Zelle-des-Bereiches)+MATCH(Suchwert;$Bereich;0));FALSE;TRUE)
  • Das ist natürlich nicht schön, jetzt müssen wir sogar zweimal nach dem Suchwert suchen. Eine Hilfszelle ist zu empfehlen.
  • Ist der Suchwert minus eine Zahl N größer als die aktuelle Zeile? Oder ist die aktuelle Zeile größer gleich dem Suchergebnis plus dem Offset? => Dann ist das Ergebnis Falsch, es soll nicht formatiert werden.
  • Ein konkretes Beispiel sieht so aus:
  • =if(OR(ROW($A$2)+MATCH($B$1;$A$2:$A;0)-5>ROW(A2);ROW(A2)>=ROW($A$2)+MATCH($B$1;$A$2:$A;0));FALSE;TRUE)
    • Wir suchen in der Spalte A von Zelle zwei bis zum Ende ($A$2:$A) nach dem Suchwert in $B$1.
    • Es sollen 5 Zeilen formatiert werden.

• =if(OR(ROW(A1)<MATCH($B$1;$A$1:$A;0);MATCH($B$1;$A$1:$A;0)+5<ROW(A1));FALSE;TRUE)
  • Es werden 5 Reihen unter dem Suchergebnis formatiert.
  • Hier läuft der Suchbereich über die gesamte Spalte A.

Fehlerwerte hervorheben

• =IFERROR(zelle;TRUE)
  • Die benutzerdefinierte Formel ist wahr, wenn ein Fehlerwert zurückgegeben wird.

Duplikate in einer Liste oder einem Array hervorheben

• =COUNTIF(A:A;A1)>1
  • Hier suchen wir nach doppelten Einträgen in Spalte A. Genauer: wir suchen nach mehr als einem Eintrag.
  • Der Bereich, auf den die Formatierung angewendet werden soll, ist ebenfalls die Spalte A. Da wir die ganze Spalte durchsuchen, benötigen wir keine absoluten Zellbezüge. Soll nur ein Teil der Spalte durchsucht werden, würden wir absolute Zellbezüge angeben müssen.
  • Für jede einzelne Zeile wird überprüft, ob der Wert in Spalte A mehr als einmal vorkommt.
  • Das Kriterium für COUNTIF ist hier A1 (also der Inhalt der Zelle A1), hier muss die erste Zelle des Bereiches eingetragen werden.
  • Es lassen sich auch andere Bedingungen einstellen: mehr als 3, zwischen 4 und 8, usw. ...

• =COUNTIF($F$9:$I$11;$F9)>1
  • Das geht auch mit einem Array, hier dem Bereich F9:I11.
  • Wir müssen mit absoluten Zellbezügen arbeiten, da sich der Bereich in dem gesucht wird, nicht verschieben soll.
  • Das Kriterium für COUNTIF ist die erste Zelle des Bereiches.

Doppelte Zeilen hervorheben

Zeilenvergleich mit COUNTIFS

• =COUNTIFS($A:$A;$A1;$B:$B;$B1;$C:$C;$C1)>1
  • Hier suchen wir für die drei Spalten A, B und C alle Zeilen, in denen alle Einträge gleich sind.
  • Der zu formatierende Bereich ist A:C.
  • Die drei Bereiche müssen wieder absolut adressiert werden.
  • Die Spalte mit dem Kriterium wird fixiert, die Zeile soll sich ändern. Daher $A1, $B1, $C1.

Zwei Tabellen vergleichen

• =COUNTIFS(INDIRECT("Sheet2!$A$2:$A");$A2;INDIRECT("Sheet2!$B$2:$B");$B2)
  • Nehmen wir an, wir haben in der einen Tabelle (Sheet1) eine Liste mit Namen und Vornamen in Spalte A und B. Die erste Zeile ist eine Kopfzeile.
  • In der zweiten Tabelle (Sheet2) haben wir ebenfalls eine Liste mit Namen und Vornamen. Alle Namen die auf Sheet2 sind, sollen in der Liste von Sheet1 hervorgehoben werden.
  • Wir wenden die bedingte Formatierung auf alle Zeilen an, in denen der Wert in Spalte A übereinstimmt und der Wert in Spalte B.

Die Beispiele auf dieser Seite wurden getestet: 03/2022