Bedingte Formatierung in Excel – ausführlich mit Beispielen

Wenn Sie Informationen visuell darstellen müssen, ist es sehr praktisch, dies mit bedingter Formatierung zu tun. Der Benutzer, der diesen Dienst nutzt, spart enorm viel Energie und Zeit. Um die notwendigen Informationen zu erhalten, reicht ein kurzer Blick auf die Datei.

So führen Sie eine bedingte Formatierung in Excel durch

Sie finden „Bedingte Formatierung“ auf der allerersten Registerkarte des Menübands, indem Sie zum Abschnitt „Stile“ gehen. 

Als nächstes müssen Sie mit dem Auge das Pfeilsymbol etwas rechts finden und den Cursor darauf bewegen. Anschließend öffnen sich die Einstellungen, in denen Sie alle notwendigen Parameter flexibel konfigurieren können. 

Als nächstes müssen Sie den entsprechenden Operator auswählen, um die gewünschte Variable mit einer Zahl zu vergleichen. Es gibt vier Vergleichsoperatoren – größer als, kleiner als, gleich und zwischen. Sie sind im Regelmenü aufgeführt. 

Als nächstes haben wir eine Reihe von Zahlen im Bereich A1:A11 angegeben.

Lassen Sie uns einen geeigneten Datensatz formatieren. Dann öffnen wir das Einstellungsmenü „Bedingte Formatierung“ und legen die erforderlichen Datenauswahlkriterien fest. Bei uns wählen wir beispielsweise das Kriterium „Mehr“. 

Ein Fenster mit einer Reihe von Optionen wird geöffnet. Im linken Teil des Fensters befindet sich ein Feld, in dem Sie die Nummer 15 angeben müssen. Im rechten Teil wird die Methode zum Hervorheben von Informationen angegeben, sofern sie das zuvor angegebene Kriterium erfüllt. Das Ergebnis wird sofort angezeigt.

Schließen Sie anschließend die Konfiguration mit der Schaltfläche OK ab.

Wozu dient die bedingte Formatierung?

Bis zu einem gewissen Grad können wir sagen, dass es ein Werkzeug ist, nachdem wir gelernt haben, dass die Arbeit mit Excel nie mehr dieselbe sein wird. Dies liegt daran, dass es das Leben viel einfacher macht. Anstatt die Formatierung einer Zelle, die einer bestimmten Bedingung entspricht, jedes Mal manuell einzustellen und auch selbst zu versuchen, die Einhaltung dieses Kriteriums zu überprüfen, müssen Sie die Einstellungen nur einmal vornehmen, und dann erledigt Excel alles von selbst.

Beispielsweise können Sie alle Zellen, die eine Zahl größer als 100 enthalten, rot färben. Oder bestimmen Sie, wie viele Tage noch bis zur nächsten Zahlung verbleiben, und färben Sie dann die Zellen grün, in denen die Frist noch nicht weit genug entfernt ist. 

Nehmen wir ein Beispiel.

Diese Tabelle zeigt den Bestand, der auf Lager ist. Es hat Spalten. als Produkt, durchschnittliche Verkäufe (gemessen in Einheiten pro Woche), Lagerbestand und wie viele Wochen dieses Produkts verbleiben.

Weiterhin ist es die Aufgabe des Einkaufsleiters, diejenigen Positionen zu bestimmen, deren Nachschub notwendig ist. Dazu müssen Sie sich die vierte Spalte von links ansehen, die den Warenbestand pro Woche erfasst.

Angenommen, das Kriterium für die Bestimmung eines Grundes für Panik ist weniger als 3 Wochen Bestandsaufnahme. Dies zeigt an, dass wir eine Bestellung vorbereiten müssen. Beträgt der Warenbestand weniger als zwei Wochen, deutet dies auf eine dringende Bestellung hin. Wenn die Tabelle sehr viele Positionen enthält, ist es ziemlich problematisch, manuell zu überprüfen, wie viele Wochen noch übrig sind. Auch wenn Sie die Suche benutzen. Und jetzt sehen wir uns an, wie die Tabelle aussieht, die knappe Waren rot hervorhebt.

Und in der Tat ist es viel einfacher zu navigieren. 

Dieses Beispiel ist zwar lehrreich, was im Vergleich zum tatsächlichen Bild etwas vereinfacht ist. Und die eingesparten Sekunden und Minuten bei regelmäßiger Nutzung solcher Tische werden zu Stunden. Jetzt reicht es aus, auf die Tabelle zu schauen, um zu verstehen, welche Waren knapp sind, und nicht Stunden damit zu verbringen, jede Zelle zu analysieren (wenn es Tausende solcher Waren gibt).

Wenn es „gelbe“ Waren gibt, müssen Sie mit dem Kauf beginnen. Wenn die entsprechende Position rot ist, müssen Sie dies sofort tun. 

Nach Wert einer anderen Zelle

Schauen wir uns nun das folgende praktische Beispiel an.

Angenommen, wir haben eine solche Tabelle und stehen vor der Aufgabe, Zeilen hervorzuheben, die bestimmte Werte enthalten. 

Wenn das Projekt also noch ausgeführt wird (d. h. es ist noch nicht abgeschlossen, daher ist es mit dem Buchstaben „P“ gekennzeichnet), müssen wir den Hintergrund rot machen. Abgeschlossene Projekte sind grün markiert.

Unsere Handlungsabfolge in dieser Situation wird wie folgt sein:

1. Wählen Sie einen Wertebereich aus. 
2. Klicken Sie auf die Schaltfläche „Bedingte Formatierung“ – „Regel erstellen“.
3. Bitte beachten Sie, dass Sie in unserem Fall die Formel in Form einer Regel anwenden müssen. Als nächstes verwenden wir die Funktion IFübereinstimmende Linien hervorheben. 

Füllen Sie als Nächstes die Zeilen wie in diesem Bild gezeigt aus.

Wichtig! Strings müssen absolut referenziert werden. Wenn wir uns auf eine Zelle beziehen, dann ist sie in diesem Fall gemischt (mit Säulenfixierung).

Ebenso wird eine Regel für die bisher nicht abgeschlossenen Arbeitsprojekte erstellt. 

So sehen unsere Kriterien aus.

Und schließlich haben wir eine solche Datenbank als Ergebnis.

Mehrere Bedingungen

Nehmen wir zur Verdeutlichung den Bereich A1:A11 aus der Tabelle, um zu demonstrieren, wie Sie in einem realen Praxisbeispiel mehrere Bedingungen verwenden können, um die Zellformatierung zu bestimmen.

Die Bedingungen selbst lauten wie folgt: Wenn die Zahl in der Zelle den Wert 6 überschreitet, wird sie rot markiert. Wenn es grün ist, dann ist die Farbe grün. Und schließlich werden die größten Zahlen, über 20, gelb hervorgehoben. 

Es gibt mehrere Möglichkeiten, wie die bedingte Formatierung nach mehreren Regeln durchgeführt wird.

1. Methode 1. Wir wählen unseren Bereich aus und wählen anschließend im Einstellungsmenü für die bedingte Formatierung eine solche Zellauswahlregel als „Mehr“ aus. Links steht die Zahl 6, rechts wird die Formatierung eingestellt. In unserem Fall sollte die Füllung rot sein. Danach wird der Zyklus zweimal wiederholt, aber andere Parameter sind bereits eingestellt – mehr als 10 und grüne Farbe bzw. mehr als 20 und gelbe Farbe. Sie werden ein solches Ergebnis erhalten.
   

   

2. Methode 2. Gehen Sie zum Haupteinstellungsmenü des Excel-Tools „Bedingte Formatierung“. Dort finden wir das Menü „Regel erstellen“ und klicken mit der linken Maustaste auf diesen Punkt.
   

   

    Danach wählen Sie als Nächstes den Punkt „Formel verwenden …“ (in der Abbildung rot umrandet) und stellen die erste Bedingung ein. Klicken Sie danach auf OK. Und dann wird der Zyklus für nachfolgende Bedingungen auf die gleiche Weise wie oben beschrieben wiederholt, aber die Formel wird verwendet.

   

Es ist wichtig, dies zu berücksichtigen. In unserem Beispiel stimmen einige Zellen mit mehreren Kriterien gleichzeitig überein. Excel löst diesen Konflikt wie folgt: Die obige Regel wird zuerst angewendet. 

Und hier ist ein Screenshot zum besseren Verständnis.

Wir haben zum Beispiel die Zahl 24. Sie erfüllt alle drei Bedingungen gleichzeitig. In diesem Fall wird es eine Füllung geben, die die erste Bedingung erfüllt, obwohl sie eher unter die dritte fällt. Wenn es also darauf ankommt, dass Zahlen über 20 gelb ausgefüllt werden, muss die dritte Bedingung an erster Stelle stehen. 

Bedingte Datumsformatierung

Sehen wir uns nun an, wie die bedingte Formatierung mit Datumsangaben funktioniert. Wir haben so ein cooles Sortiment.

In diesem Fall müssen Sie eine bedingte Formatierungsregel wie „Datum“ festlegen.

Danach öffnet sich ein Dialogfenster mit einer ganzen Reihe von Bedingungen. Sie können sie in diesem Screenshot im Detail kennenlernen (sie sind auf der linken Seite des Bildschirms in Form einer Liste aufgeführt).

Wir müssen nur das passende auswählen und auf die Schaltfläche „OK“ klicken.

Wir sehen, dass die Daten, die sich auf die letzte Woche zum Zeitpunkt der Ziehung dieser Spanne beziehen, rot hervorgehoben wurden. 

Formeln verwenden

Der Satz von Standardregeln für die bedingte Formatierung ist ziemlich groß. Aber die Situationen sind anders, und die Standardliste reicht möglicherweise nicht aus. In diesem Fall können Sie mithilfe einer Formel eine eigene Formatierungsvorschrift erstellen. 

Klicken Sie dazu im Menü der bedingten Formatierung auf die Schaltfläche „Regel erstellen“ und wählen Sie dann das im Screenshot angezeigte Element aus.

Zeilen (nach Zellenwert)

Angenommen, wir müssen die Zeile hervorheben, die eine Zelle mit einem bestimmten Wert enthält. In diesem Fall müssen wir dieselbe Abfolge von Aktionen wie oben ausführen, jedoch für Zeichenfolgen. Das heißt, Sie können auswählen, für welchen Bereich die bedingte Formatierung gelten soll. 

So erstellen Sie eine Regel

Um eine Regel zu erstellen, müssen Sie die entsprechende Option im Abschnitt „Bedingte Formatierung“ verwenden. Schauen wir uns eine bestimmte Abfolge von Aktionen an. 

Zuerst müssen Sie das Element „Bedingte Formatierung“ in der Multifunktionsleiste auf der Registerkarte „Startseite“ finden. Erkennbar ist es an den charakteristischen mehrfarbigen Zellen unterschiedlicher Größe mit einem daneben stehenden Bild mit einem durchgestrichenen Gleichheitszeichen. 

Es gibt eine Schaltfläche "Regel erstellen". Sie können auch auf die Schaltfläche „Regeln verwalten“ klicken, woraufhin sich ein Dialogfeld öffnet, über das Sie auch eine Regel erstellen können, indem Sie auf die entsprechende Schaltfläche in der oberen linken Ecke klicken. 

In diesem Fenster können Sie auch die bereits angewendeten Regeln sehen.

Zellauswahlregeln

Okay, wir reden so viel darüber, wie man Regeln erstellt, aber was ist das? Die Zellauswahlregel bezieht sich auf die Merkmale, die vom Programm berücksichtigt werden, um zu entscheiden, wie die passenden Zellen formatiert werden. Beispielsweise können Bedingungen größer als eine bestimmte Zahl, kleiner als eine bestimmte Zahl, eine Formel usw. sein. Es gibt eine ganze Reihe von ihnen. Sie können sich mit ihnen vertraut machen und ihre Anwendung „in der Sandbox“ üben. 

Formatieren aller Zellen basierend auf ihren Werten

Eine Person kann den Bereich unabhängig festlegen: eine ganze Zeile, das gesamte Dokument oder eine einzelne Zelle. Das Programm kann sich nur auf den Wert einer Zelle konzentrieren. Dies erhöht die Flexibilität des Prozesses. 

Formatieren Sie nur eindeutige oder doppelte Zellen

Es gibt eine große Anzahl von Regeln, die direkt in das Programm eingebunden sind. Sie können sich mit ihnen im Menü „Bedingte Formatierung“ vertraut machen. Insbesondere damit die Formatierung ausschließlich auf doppelte Werte oder nur auf eindeutige angewendet wird, gibt es eine spezielle Option – „Doppelte Werte“.

Wenn Sie diesen Punkt auswählen, öffnet sich ein Fenster mit Einstellungen, in dem Sie auch die gegenteilige Option auswählen können – wählen Sie nur eindeutige Werte aus. 

Formatieren Sie Werte im Bereich unter und über dem Durchschnitt

Um unter- oder überdurchschnittliche Werte zu formatieren, gibt es im selben Menü auch eine spezielle Option. Sie müssen jedoch ein anderes Untermenü auswählen – „Regeln für die Auswahl des ersten und letzten Werts“. 

Nur die ersten und letzten Werte formatieren

Im selben Untermenü ist es möglich, nur den ersten und letzten Wert mit einer speziellen Farbe, Schriftart und anderen Formatierungsmethoden hervorzuheben. Gemäß Standard besteht die Möglichkeit, die ersten und letzten zehn Elemente sowie 10 % der Gesamtzahl der in diesem Bereich enthaltenen Zellen auszuwählen. Der Benutzer kann jedoch selbst bestimmen, wie viele Zellen er auswählen muss.

Nur Zellen mit bestimmtem Inhalt formatieren

Um nur Zellen mit bestimmtem Inhalt zu formatieren, müssen Sie die Formatierungsregel Gleich oder Text enthält auswählen. Der Unterschied zwischen ihnen besteht darin, dass die Zeichenfolge im ersten Fall vollständig und im zweiten nur teilweise dem Kriterium entsprechen muss. 

Wie Sie sehen können, ist die bedingte Formatierung eine multifunktionale Funktion des Excel-Programms, die demjenigen, der sie beherrscht, eine Vielzahl von Vorteilen bietet. Wir wissen, dass all dies auf den ersten Blick kompliziert erscheinen mag. Tatsächlich aber zieht es die Hände an, sobald es aufgrund seines Inhalts oder nach einem anderen Kriterium einen bestimmten Text rot hervorzuheben (oder anderweitig zu formatieren) scheint. Diese Funktion ist in der Grundausstattung an Excel-Kenntnissen enthalten, ohne sie ist auch ein Laien-Arbeiten mit Tabellenkalkulationen und Datenbanken unmöglich.