Die bedingte Formatierung ist eine fantastische Möglichkeit, Daten schnell in einer Tabellenkalkulation zu visualisieren. Mit der bedingten Formatierung können Sie beispielsweise Datumsangaben in den nächsten 30 Tagen hervorheben, Dateneingabeprobleme kennzeichnen, Zeilen mit Top-Kunden hervorheben, Duplikate anzeigen und mehr.
Excel wird mit einer großen Anzahl von 'Voreinstellungen' geliefert, die es einfach machen, neue Regeln ohne Formeln zu erstellen. Sie können jedoch auch Regeln mit Ihren eigenen benutzerdefinierten Formeln erstellen. Durch die Verwendung einer eigenen Formel übernehmen Sie die Bedingung, die eine Regel auslöst, und können genau die Logik anwenden, die Sie benötigen. Formeln geben Ihnen maximale Leistung und Flexibilität.
Mit der Voreinstellung „Gleich“ können Sie beispielsweise Zellen mit „Apfel“ ganz einfach hervorheben.
Aber was ist, wenn Sie Zellen hervorheben möchten, die „Apfel“ oder „Kiwi“ oder „Limette“ entsprechen? Sicher, Sie können für jeden Wert eine Regel erstellen, aber das ist eine Menge Ärger. Stattdessen können Sie einfach eine auf einer Formel basierende Regel mit dem ODER-Funktion :
Hier ist das Ergebnis der auf den Bereich B4:F8 in dieser Tabelle angewendeten Regel:
Hier ist die genaue Formel, die verwendet wird:
= OR (B4='apple',B4='kiwi',B4='lime')
Schnellstart
Sie können eine formelbasierte bedingte Formatierungsregel in vier einfachen Schritten erstellen:
1. Wählen Sie die Zellen aus, die Sie formatieren möchten.
2. Erstellen Sie eine Regel für die bedingte Formatierung und wählen Sie die Option Formel
3. Geben Sie eine Formel ein, die TRUE oder FALSE zurückgibt.
4. Legen Sie Formatierungsoptionen fest und speichern Sie die Regel.
Die ISODD-Funktion gibt nur TRUE für ungerade Zahlen zurück und löst die Regel aus:
wie man fehlende Daten in Excel findet
Video: So wenden Sie die bedingte Formatierung mit einer Formel an
Wir bieten auch Videotraining zu diesem Thema .Formellogik
Formeln, die bedingte Formatierung anwenden, müssen TRUE oder FALSE oder numerische Äquivalente zurückgeben. Hier sind einige Beispiele:
Eine logische Funktion ist eine Funktion, die mit Werten arbeitet, die entweder sind
= ISODD (A1) = ISNUMBER (A1) =A1>100 = AND (A1>100,B1<50) = OR (F1='MN',F1='WI')
Die obigen Formeln geben alle TRUE oder FALSE zurück, sodass sie perfekt als Trigger für die bedingte Formatierung funktionieren.
Wenn die bedingte Formatierung auf einen Zellbereich angewendet wird, geben Sie Zellbezüge in Bezug auf die erste Zeile und Spalte in der Auswahl (d. h. die obere linke Zelle) ein. Der Trick, um zu verstehen, wie Formeln für die bedingte Formatierung funktionieren, besteht darin, sich die gleiche Formel vorzustellen, auf die sie angewendet wird jede Zelle in der Auswahl , wobei die Zellbezüge wie gewohnt aktualisiert werden. Stellen Sie sich vor, Sie haben die Formel in die obere linke Zelle der Auswahl eingegeben und dann die Formel über die gesamte Auswahl kopiert. Wenn Sie damit zu kämpfen haben, lesen Sie den Abschnitt über Dummy-Formeln unter.
Formelbeispiele
Im Folgenden finden Sie Beispiele für benutzerdefinierte Formeln, mit denen Sie die bedingte Formatierung anwenden können. Einige dieser Beispiele können mit den integrierten Voreinstellungen von Excel zum Hervorheben von Zellen erstellt werden, aber benutzerdefinierte Formeln können weit über Voreinstellungen hinausgehen, wie Sie unten sehen können.
Siehe auch: Mehr als 30 Formeln für bedingte FormatierungHeben Sie Bestellungen aus Texas hervor
Um Zeilen hervorzuheben, die Bestellungen aus Texas (abgekürzt TX) darstellen, verwenden Sie eine Formel, die den Verweis auf Spalte F sperrt:
=$F5='TX'
Weitere Informationen finden Sie in diesem Artikel: Zeilen mit bedingter Formatierung hervorheben .
Video: So markieren Sie Zeilen mit bedingter Formatierung
Markieren Sie Termine in den nächsten 30 Tagen
Um Daten hervorzuheben, die in den nächsten 30 Tagen auftreten, benötigen wir eine Formel, die (1) sicherstellt, dass Daten in der Zukunft liegen und (2) sicherstellt, dass Daten 30 Tage oder weniger vom heutigen Tag entfernt liegen. Eine Möglichkeit, dies zu tun, besteht darin, die UND-Funktion zusammen mit dem NOW-Funktion so was:
= AND (B4> NOW (),B4<=( NOW ()+30))
Mit dem aktuellen Datum 18. August 2016 hebt die bedingte Formatierung Datumsangaben wie folgt hervor:
Die NOW-Funktion gibt das aktuelle Datum und die aktuelle Uhrzeit zurück. Weitere Informationen zur Funktionsweise dieser Formel finden Sie in diesem Artikel: Markieren Sie die Daten in den nächsten N Tagen .
Spaltenunterschiede hervorheben
Bei zwei Spalten mit ähnlichen Informationen können Sie die bedingte Formatierung verwenden, um feine Unterschiede zu erkennen. Die folgende Formel zum Auslösen der Formatierung lautet:
=$B4$C4 
Fehlende Werte hervorheben
Um Werte in einer Liste hervorzuheben, die in einer anderen fehlen, können Sie eine Formel verwenden, die auf dem ZÄHLENWENN-Funktion :
= COUNTIF (list,B5)=0
Diese Formel überprüft einfach jeden Wert in Liste A gegen Werte im benannten Bereich 'list' (D5:D10). Wenn der Zähler null ist, gibt die Formel TRUE zurück und löst die Regel aus, die Werte in hervorhebt Liste A die fehlen bei Liste B .
Video: So finden Sie fehlende Werte mit COUNTIF
Heben Sie Immobilien mit 3+ Schlafzimmern unter 350.000 US-Dollar hervor
Um Immobilien in dieser Liste zu finden, die mindestens 3 Schlafzimmer haben, aber weniger als 300.000 US-Dollar kosten, können Sie eine Formel verwenden, die auf der UND-Funktion basiert:
= AND ($C5<350000,$D5>=3)
Die Dollarzeichen ($) sperren den Verweis auf die Spalten C und D, und die UND-Funktion wird verwendet, um sicherzustellen, dass beide Bedingungen WAHR sind. In Zeilen, in denen die AND-Funktion TRUE zurückgibt, wird die bedingte Formatierung angewendet:
Top-Werte hervorheben (dynamisches Beispiel)
Obwohl Excel Voreinstellungen für 'Spitzenwerte' hat, zeigt dieses Beispiel, wie man dasselbe mit einer Formel macht und wie Formeln flexibler sein können. Durch die Verwendung einer Formel können wir das Arbeitsblatt interaktiv gestalten – wenn der Wert in F2 aktualisiert wird, reagiert die Regel sofort und hebt neue Werte hervor.
Die für diese Regel verwendete Formel lautet:
wie man Kopf- und Fußzeilen in Excel einfügt
=B4>= LARGE (data,input)
Dabei ist „data“ der benannte Bereich B4:G11 und „input“ der benannte Bereich F2. Diese Seite hat Details und eine vollständige Erklärung .
Gantt-Diagramme
Ob Sie es glauben oder nicht, Sie können sogar Formeln verwenden, um einfache Gantt-Diagramme mit bedingter Formatierung wie dieser zu erstellen:
Dieses Arbeitsblatt verwendet zwei Regeln, eine für die Balken und eine für die Wochenendschattierung:
= AND (D>=$B5,D<=$C5) // bars = WEEKDAY (D,2)>5 // weekends
Dieser Artikel erklärt die Formel für Balken , und Dieser Artikel erklärt die Formel für die Wochenendbeschattung .
Einfaches Suchfeld
Ein cooler Trick, den Sie mit der bedingten Formatierung ausführen können, besteht darin, ein einfaches Suchfeld zu erstellen. In diesem Beispiel hebt eine Regel Zellen in Spalte B hervor, die in Zelle F2 eingegebenen Text enthalten:
Die verwendete Formel lautet:
= ISNUMBER ( SEARCH ($F,B2))
Weitere Details und eine vollständige Erklärung finden Sie unter:
- Artikel: So markieren Sie Zellen, die bestimmten Text enthalten
- Artikel: So markieren Sie Zeilen, die bestimmten Text enthalten
- Video: So erstellen Sie ein Suchfeld, um Daten hervorzuheben
Fehlerbehebung
Wenn Ihre Regeln für die bedingte Formatierung nicht korrekt ausgelöst werden, liegt höchstwahrscheinlich ein Problem mit Ihrer Formel vor. Stellen Sie zunächst sicher, dass Sie die Formel mit einem Gleichheitszeichen (=) begonnen haben. Wenn Sie diesen Schritt vergessen, konvertiert Excel Ihre gesamte Formel stillschweigend in Text und macht sie nutzlos. Um das Problem zu beheben, entfernen Sie einfach die doppelten Anführungszeichen, die Excel auf beiden Seiten hinzugefügt hat, und stellen Sie sicher, dass die Formel mit Gleichen (=) beginnt.
Wenn Ihre Formel richtig eingegeben wurde, aber die Regel nicht auslöst, müssen Sie möglicherweise etwas tiefer graben. Normalerweise, können Sie die Ergebnisse in einer Formel mit der Taste F9 überprüfen oder die Funktion Auswerten verwenden, um eine Formel schrittweise durchzugehen. Leider können Sie diese Tools nicht mit Formeln zur bedingten Formatierung verwenden, aber Sie können eine Technik namens 'Dummy-Formeln' verwenden.
Dummy-Formeln
Dummy-Formeln sind eine Möglichkeit, Ihre bedingten Formatierungsformeln direkt auf dem Arbeitsblatt zu testen, damit Sie sehen können, was sie tatsächlich tun. Dies kann eine große Zeitersparnis sein, wenn Sie Schwierigkeiten haben, Zellreferenzen korrekt zu verwenden.
Kurz gesagt, Sie geben dieselbe Formel in einen Zellbereich ein, der der Form Ihrer Daten entspricht. Auf diese Weise können Sie die von jeder Formel zurückgegebenen Werte anzeigen und die Funktionsweise der formelbasierten bedingten Formatierung visualisieren und verstehen. Für eine ausführliche Erklärung, siehe diesen Artikel .
Video: Testen Sie die bedingte Formatierung mit Dummy-Formeln
Einschränkungen
Bei der formelbasierten bedingten Formatierung gibt es einige Einschränkungen:
- Sie können keine Symbole, Farbskalen oder Datenbalken mit einer benutzerdefinierten Formel anwenden. Sie sind auf die standardmäßige Zellenformatierung beschränkt, einschließlich Zahlenformate, Schriftart, Füllfarbe und Rahmenoptionen.
- Bestimmte Formelkonstrukte wie Vereinigungen, Schnittmengen oder Arraykonstanten können nicht für bedingte Formatierungskriterien verwendet werden.
- Sie können in einer bedingten Formatierungsformel nicht auf andere Arbeitsmappen verweisen.
Sie können manchmal #2 und #3 umgehen. Möglicherweise können Sie die Logik der Formel in eine Zelle im Arbeitsblatt verschieben und dann stattdessen auf diese Zelle in der Formel verweisen. Wenn Sie versuchen, eine Arraykonstante zu verwenden, versuchen Sie stattdessen, einen benannten Bereich zu erstellen.
Weitere Ressourcen zur CF-Formel
- Mehr als 30 Beispiele für bedingte Formatierungsformeln
- Videotraining mit Übungsarbeitsblättern
