Bedingte Formatierung auf Basis einer Formel – Excel & G-Sheets
In diesem Tutorial zeigen wir Ihnen, wie Sie bedingte Formatierungen auf Basis einer Formel in Excel und Google Sheets anwenden können.
Die bedingte Formatierung in Excel verfügt über zahlreiche voreingestellte Regeln, mit denen Sie Zellen schnell nach ihrem Inhalt formatieren können. Diese voreingestellten Regeln haben jedoch ihre Grenzen und hier kommt die Verwendung einer Formel zur Bestimmung des Formats einer Zelle ins Spiel. Dadurch erhalten Sie eine viel größere Flexibilität bei der bedingten Formatierung.
Formelregeln für die bedingte Formatierung
Gleich
- Markieren Sie die Zellen, für die Sie die bedingte Formatierung festlegen möchten, dann wählen Sie dann in der Menüleiste Start > Bedingte Formatierung > Neue Regel.
- Wählen Sie Formel zur Ermittlung der zu formatierenden Zellen verwenden und geben Sie die folgende Formel ein:
=$C4=“West“
Sie müssen Ihre Formel immer mit einem Gleichheitszeichen beginnen.
Die Formel testet, ob das Ergebnis WAHR oder FALSCH ist, ähnlich wie die herkömmliche WENN-Anweisung in Excel, aber im Fall der bedingten Formatierung müssen Sie die Bedingungen WAHR und FALSCH nicht hinzufügen. Wenn die Formel den Wert WAHR liefert, wird die Formatierung, die Sie im Abschnitt Formatieren festgelegt haben, auf die betroffenen Zellen angewendet.
Sie müssen in dieser Formel einen gemischten Verweis verwenden ($C4), um die Spalte zu sperren (absolut zu machen), aber die Zeile relativ zu machen. Dadurch kann die Formatierung die gesamte Zeile formatieren und nicht nur eine einzelne Zelle, die die Kriterien erfüllt.
Wenn die Regel für alle Zellen im Bereich ausgewertet wird, ändert sich die Zeile, aber nicht die Spalte. Dies führt dazu, dass die Regel die Werte in den anderen Spalten ignoriert und sich nur auf die Werte in Spalte C konzentriert. Wenn Spalte C dieser Zeile das Wort „West“ enthält, ist das Ergebnis der Formel WAHR, und die Formatierung wird auf die gesamte Zeile angewendet.
- Klicken Sie auf Formatieren… und wählen Sie die gewünschte Formatierung aus.
- Klicken Sie auf OK und dann erneut auf OK, um das Ergebnis anzuzeigen.
Die Zeilen, in denen Spalte C „West“ entspricht, werden hervorgehoben, andere Zeilen dagegen nicht. Im obigen Beispiel werden nur zwei Zeilen hervorgehoben.
Nicht gleich
Wenn Sie eine entgegengesetzte Regel erstellen möchten, können Sie den Operator <>(nicht gleich) anstelle von = verwenden.
=$C4<>“West“
Das Ergebnis ist das Gegenteil: Die meisten Zeilen sind jetzt hervorgehoben!
Größer als
Sie können auch mithilfe einer Formel auf „größer als“ testen. Sie können zum Beispiel eine Formel verwenden, um zu prüfen, ob die Werte in Spalte D größer als 23.000 $ sind.
=$D4>23000
Diese Regel hebt nur Zeilen hervor, in denen der Wert in Spalte D größer als 23.000 $ ist.
Größer als oder gleich
In ähnlicher Weise können Sie die Zeilen, in denen der Wert in Spalte D größer oder gleich 23.000 $ ist, mit der folgenden Formel hervorheben:
=$D4>=23000
Das Ergebnis für diese Formel wäre etwas anders: Die Zeilen, in denen der Wert von Rot genau 23.000 $ beträgt, werden ebenfalls hervorgehoben (hier Zeile 5).
Kleiner als
Durch Tauschen des >-Zeichens mit einem <-Zeichen wird nach Werten unter 23.000 $ gesucht.
=$D4<23000
Jetzt werden nur Zeilen, in denen Rot weniger als 23.000 $ beträgt, hervorgehoben.
Kleiner als oder gleich
Um den Test so zu ändern, dass er den Zielwert (z. B. 23.000 $) einschließt, verwenden Sie <=.
=$D4<=23000
Nun werden alle Zeilen, in denen Spalte D gleich 23.000 $ ist, ebenfalls hervorgehoben.
Regeln mit ODER/UND erstellen
Die ODER-Formel
Sie können eine kompliziertere Regel erstellen, indem Sie die Excel-Funktion ODER einbeziehen, um die Formel zu erweitern und nach zwei oder mehr Werten zu suchen.
- Geben Sie diese Formel ein:
=ODER($C4=“West“;$C4=“Ost“)
- Klicken Sie auf die Schaltfläche Formatieren..., wählen Sie die gewünschte Formatierung aus und klicken Sie dann auf OK, um zu Excel zurückzukehren.
Dadurch werden alle Zeilen, in denen die Region West oder Ost angegeben ist, hervorgehoben.
Die UND-Formel
In ähnlicher Weise können Sie eine Regel mit UND erstellen. Dies würde bedeuten, dass die Werte in zwei oder mehr Spalten überprüft werden.
Die obige Formel prüft, ob der Text in Spalte C Nord entspricht und der Wert in der entsprechenden Zeile in Spalte D größer als 23.000 $ ist.
Diese bedingte Formatierungsregel hebt nur die Zeilen hervor, in denen beide Bedingungen erfüllt sind.
Die WENN-Formel
Sie müssen der Formel nicht unbedingt eine WENN-Anweisung hinzufügen, wenn Sie sie in einer bedingten Formatierungsregel erstellen, da die bedingte Formatierung die Regel immer dann anwendet, wenn die Formel, die Sie erstellt haben, den Wert WAHR liefert.
So zum Beispiel diese Formel:
=WENN($C4=“West“;WAHR;FALSCH)
und diese Formel
$C4=“West“
wenden beide die gleiche bedingte Formatierung auf Ihr Arbeitsblatt an.
Integrierte Excel-Funktionen
Viele integrierte Excel-Funktionen können für die bedingte Formatierung verwendet werden, z. B. ISTLEER, ISTFEHLER, ISTGERADE, ISTZAHL usw.
Die ISTLEER-Funktion
Mit der ISTLEER-Funktion können Sie eine Regel erstellen, um Zeilen mit leeren Zellen im Arbeitsblatt zu finden.
- Markieren Sie den Bereich und wählen Sie dann in der Menüleiste Start > Bedingte Formatierung > Neue Regel.
- Geben Sie diese Formel ein:
=ISTLEER($E3)
- Klicken Sie auf die Schaltfläche Formatieren..., wählen Sie die gewünschte Formatierung aus und klicken Sie dann auf OK, um zu Excel zurückzukehren.
ISTLEER mit ODER
Sie können dies erweitern, um leere oder fehlerhafte Zellen darin einzuschließen.
- Geben Sie die folgende Formel ein
=ODER(ISTLEER($E3);ISTFEHLER($E3))
- Klicken Sie auf die Schaltfläche Formatieren..., wählen Sie die gewünschte Formatierung aus und klicken Sie dann auf OK, um zu Excel zurückzukehren.
Andere Funktionen in der bedingten Formatierung
Es gibt eine Reihe weiterer Funktionen, die bei der Erstellung von formelbasierten Regeln zur bedingten Formatierung verwendet werden können.
- ZÄHLENWENNS – zählt die Zellen, die eine bestimmte Bedingung erfüllen (nützlich zum Hervorheben von Duplikaten)
- KGRÖSSTE – findet den k-ten größten Wert
- MAX – findet die größte Zahl
- MIN – findet die kleinste Zahl
- MOD – gibt den Rest nach der Division zurück (nützlich, um jede zweite Zeile hervorzuheben)
- NICHT – ändert WAHR in FALSCH und umgekehrt
- JETZT – gibt das aktuelle Datum und die aktuelle Uhrzeit zurück (nützlich für die bedingte Formatierung von Daten und Zeiten)
- ZEILE – gibt die Anzahl der Zeilen in einem Array zurück
- SUCHEN – sucht nach einer Textzeichenkette innerhalb einer anderen Zeichenkette (nützlich, um einen bestimmten Text zu finden)
- HEUTE – gibt das aktuelle Datum zurück (nützlich für die Anwendung bedingter Formatierung auf Datumsangaben)
- SVERWEIS – gibt das Ergebnis einer vertikalen Suche zurück
- WOCHENTAG – gibt den Wochentag zurück
- XVERWEIS – eine neue, leistungsfähigere Nachschlagefunktion
Mit der Funktion MAX können Sie beispielsweise die Zeile, die den höchsten Wert in einer bestimmten Spalte enthält, hervorheben.
=$D4=MAX($D:$D)
Jetzt ist nur noch die Zeile mit dem Höchstwert in Spalte D hervorgehoben.
Wenn Sie bei der Erstellung einer Regel der bedingten Formatierung Schwierigkeiten haben, eine Formel zum Funktionieren zu bringen, ist es eine gute Idee, die Formel zunächst in Ihrem Excel-Blatt zu testen. Sobald Sie sie dort richtig haben, können Sie sie in Ihre Regel einbauen.
Bedingte Formatierung auf Basis einer Formel in Google Sheets
Die bedingte Formatierung funktioniert in Google Sheets fast genauso wie in Excel.
- Markieren Sie die Zellen, für die Sie die bedingte Formatierung festlegen möchten und wählen Sie dann im Menü Format > Bedingte Formatierung.
- Vergewissern Sie sich, dass die Angabe unter Auf Bereich anwenden korrekt ist und wählen Sie dann Benutzerdefinierte Formel ist aus dem Dropdown-Feld Formatierungsregeln aus. Geben Sie die Formel ein.
- Wählen Sie dann die gewünschte Art der Formatierung aus und klicken Sie auf “ Fertig“.
Sie können in Google Sheets auch Größer als, Größer als oder gleich, Kleiner als und Kleiner als oder gleich verwenden.
UND und ODER funktionieren ebenfalls. Dies gilt auch für die anderen oben aufgeführten Excel-Funktionen (mit Ausnahme von XVERWEIS, die in Google Sheets nicht verfügbar ist).
