Bedingte Formatierung beim Vergleich von Listen in Excel

Möglicherweise werden Sie aufgefordert, zwei Listen zu vergleichen und die Werte auszuwählen, die in einer Liste enthalten sind, aber nicht in der anderen, oder Sie müssen möglicherweise zwei Listen vergleichen und nur die Werte auswählen, die in beiden Listen vorhanden sind. Die bedingte Formatierung ist eine ideale Methode, um beide Aufgaben zu erledigen.

Heben Sie Werte hervor, die in Liste1 vorhanden sind, aber nicht in Liste2

Die Abbildung veranschaulicht eine Übung zur bedingten Formatierung, die Kunden aus 2013 und 2014 vergleicht und diejenigen Kunden im Jahr 2014 hervorhebt, die Neukunden sind (sie waren 2013 keine Kunden).

Gehen Sie folgendermaßen vor, um diese grundlegende Formatierungsregel zu erstellen:

Wählen Sie die Datenzellen in Ihrem Zielbereich aus (Zellen E4:E28 in diesem Beispiel), klicken Sie auf die Registerkarte Start des Excel-Menübands und wählen Sie dann Bedingte Formatierung→Neue Regel aus.

Dadurch wird das Dialogfeld Neue Formatierungsregel geöffnet.

Klicken Sie im Listenfeld oben im Dialogfeld auf die Option Mit einer Formel bestimmen, welche Zellen formatiert werden sollen.

Diese Auswahl wertet Werte basierend auf einer von Ihnen angegebenen Formel aus. Wenn ein bestimmter Wert als TRUE ausgewertet wird, wird die bedingte Formatierung auf diese Zelle angewendet.

Geben Sie in das Formeleingabefeld die in diesem Schritt angezeigte Formel ein.

Beachten Sie, dass Sie die Funktion ZÄHLENWENN verwenden, um auszuwerten, ob der Wert in der Zielzelle (E4) in Ihrem Vergleichsbereich ($B$4:$B$21) gefunden wird. Wird der Wert nicht gefunden, gibt die ZÄHLENWENN-Funktion eine 0 zurück und löst damit die bedingte Formatierung aus. Wie bei Standardformeln müssen Sie sicherstellen, dass Sie absolute Referenzen verwenden, damit jeder Wert in Ihrem Bereich mit der entsprechenden Vergleichszelle verglichen wird.

=ZÄHLENWENN($B$4:$B$21,E4)=0

Beachten Sie, dass Sie in der Formel die absoluten Referenzdollarsymbole ($) für die Zielzelle (E4) ausschließen. Wenn Sie auf Zelle E4 klicken, anstatt den Zellbezug einzugeben, macht Excel Ihren Zellbezug automatisch absolut. Es ist wichtig, dass Sie die absoluten Referenzdollarsymbole nicht in Ihre Zielzelle aufnehmen, da Sie Excel benötigen, um diese Formatierungsregel basierend auf dem eigenen Wert jeder Zelle anzuwenden.

Klicken Sie auf die Schaltfläche Formatieren.

Dadurch wird das Dialogfeld Zellen formatieren geöffnet, in dem Sie alle Optionen zum Formatieren von Schriftart, Rahmen und Füllung für Ihre Zielzelle haben. Nachdem Sie Ihre Formatierungsoptionen ausgewählt haben, klicken Sie auf die Schaltfläche OK, um Ihre Änderungen zu bestätigen und zum Dialogfeld Neue Formatierungsregel zurückzukehren.

Klicken Sie im Dialogfeld Neue Formatierungsregel auf die Schaltfläche OK, um Ihre Formatierungsregel zu bestätigen.

Wenn Sie Ihre bedingte Formatierungsregel bearbeiten müssen, platzieren Sie Ihren Cursor einfach in einer der Datenzellen innerhalb Ihres formatierten Bereichs und gehen Sie dann zur Registerkarte Start und wählen Sie Bedingte Formatierung→Regeln verwalten. Dadurch wird das Dialogfeld Manager für bedingte Formatierungsregeln geöffnet. Klicken Sie auf die Regel, die Sie bearbeiten möchten, und klicken Sie dann auf die Schaltfläche Regel bearbeiten.

Heben Sie Werte hervor, die in List1 und List2 vorhanden sind

Die Abbildung veranschaulicht eine Übung zur bedingten Formatierung, die Kunden aus den Jahren 2013 und 2014 vergleicht und die Kunden aus dem Jahr 2014 hervorhebt, die in beiden Listen aufgeführt sind.

Gehen Sie folgendermaßen vor, um diese grundlegende Formatierungsregel zu erstellen:

Wählen Sie die Datenzellen in Ihrem Zielbereich aus (Zellen E4:E28 in diesem Beispiel), klicken Sie auf die Registerkarte Start des Excel-Menübands und wählen Sie Bedingte Formatierung→Neue Regel aus.

Dadurch wird das Dialogfeld Neue Formatierungsregel geöffnet.

Klicken Sie im Listenfeld oben im Dialogfeld auf die Option Mit einer Formel bestimmen, welche Zellen formatiert werden sollen.

Diese Auswahl wertet Werte basierend auf einer von Ihnen angegebenen Formel aus. Wenn ein bestimmter Wert als TRUE ausgewertet wird, wird die bedingte Formatierung auf diese Zelle angewendet.

Geben Sie in das Formeleingabefeld die in diesem Schritt angezeigte Formel ein.

Beachten Sie, dass Sie die Funktion ZÄHLENWENN verwenden, um auszuwerten, ob der Wert in der Zielzelle (E4) in Ihrem Vergleichsbereich ($B$4:$B$21) gefunden wird. Wird der Wert gefunden, gibt die Funktion ZÄHLENWENN eine Zahl größer 0 zurück und löst damit die bedingte Formatierung aus. Wie bei Standardformeln müssen Sie sicherstellen, dass Sie absolute Referenzen verwenden, damit jeder Wert in Ihrem Bereich mit der entsprechenden Vergleichszelle verglichen wird.

=ZÄHLENWENN($B$4:$B$21,E4)>0

Beachten Sie, dass Sie in der Formel die absoluten Referenzdollarsymbole ($) für die Zielzelle (E4) ausschließen. Wenn Sie auf Zelle E4 klicken, anstatt den Zellbezug einzugeben, macht Excel Ihren Zellbezug automatisch absolut. Es ist wichtig, dass Sie die absoluten Referenzdollarsymbole nicht in Ihre Zielzelle aufnehmen, da Sie Excel benötigen, um diese Formatierungsregel basierend auf dem eigenen Wert jeder Zelle anzuwenden.

Klicken Sie auf die Schaltfläche Formatieren.

Dadurch wird das Dialogfeld Zellen formatieren geöffnet, in dem Sie alle Optionen zum Formatieren von Schriftart, Rahmen und Füllung für Ihre Zielzelle haben. Nachdem Sie Ihre Formatierungsoptionen ausgewählt haben, klicken Sie auf die Schaltfläche OK, um Ihre Änderungen zu bestätigen und zum Dialogfeld Regel für neue Formatierung zurückzukehren.

Klicken Sie im Dialogfeld Neue Formatierungsregel auf die Schaltfläche OK, um Ihre Formatierungsregel zu bestätigen.

Wenn Sie Ihre bedingte Formatierungsregel bearbeiten müssen, platzieren Sie Ihren Cursor einfach in einer der Datenzellen innerhalb Ihres formatierten Bereichs und gehen Sie dann zur Registerkarte Start und wählen Sie Bedingte Formatierung→Regeln verwalten. Dadurch wird das Dialogfeld Manager für bedingte Formatierungsregeln geöffnet. Klicken Sie auf die Regel, die Sie bearbeiten möchten, und klicken Sie dann auf die Schaltfläche Regel bearbeiten.