So markiert Excel Zellen, deren Wert in einer Referenztabelle enthalten ist

Foto des Autors

Manchmal ist es sinnvoll, die Inhalte eines Arbeitsblattes mit denen einer anderen Tabelle zu vergleichen. Kommt ein Wert in der Referenztabelle vor, soll das optisch angezeigt werden.

So heben Sie Zellen hervor, deren Wert in einer Referenztabelle vorkommt

Stellen Sie sich einen Fischhändler vor, der bei Ware die unter Artenschutz steht besondere Vorkehrungen beim Verkauf treffen muss.

In seiner Excel-Datei hat er im ersten Arbeitsblatt die aktuellen Bestellungen aufgelistet. In einer minimalen Variante hat das diese Form:

excel bedingte formatierung refliste1
Die erste Tabelle enthält eine Liste von Fischbestellungen.

In einem zweiten Arbeitsblatt führt er eine ständig aktualisierte Liste der gefährdeten Fische. Seine „Tabelle2“ sieht also zum Beispiel so aus:

excel bedingte formatierung refliste2
In der zweiten Tabelle befinden sich die Fischarten, die hervorgehoben werden sollen.

Wenn nun bei den Bestellungen der Name eines der geschützten Tiere auftaucht, soll eine Markierung auf das Problem hinweisen.

Werbung

So hilft die Funktion VERGLEICH() beim Nachschlagen

Der Schlüssel zur Lösung der Aufgabe ist die Funktion VERGLEICH(). Sie prüft, ob der Wert des ersten Parameters in einem bestimmten Zellbereich auftaucht, der im zweiten Parameter genannt ist. Falls ja, wird der Index der Zelle im Suchbereich zurückgeliefert. Falls nicht, liefert die Funktion den Fehlerwert #NV zurück.

Für andere Zwecke kann man damit über diesen Index die gefundene Zelle adressieren. Für die Aufgabenstellung genügt es aber, ob es einen Index gibt oder nicht. Wenn ja, kommt der Wert in der Referenztabelle vor.

In der Formel zur Bedingten Formatierung prüfen wir also jede Zelle darauf, ob ihr Wert in der Vergleichstabelle auftaucht.

So bauen Sie die Vergleichstabelle in die Bedingte Formatierung ein

Markieren Sie den Bereich, der die Markierung erhalten soll. Im Beispiel wäre des die Spalte C ab der zweiten Zeile.

Rufen Sie den Menüpunkt Bedingte Formatierung – Neue Regel auf.

Bei Regeltyp wählen Sie den Formel zur Ermittlung der zu formatierenden Zellen verwenden.

Als Formel wählen Sie =VERGLEICH(C2;Tabelle2!A2:A100;0)

Das bedeutet: Die Formatierung wirkt, wenn eine der Zellen im markierten Bereich mit dem Wert der Zellen A2 bis A100 aus dem zweiten Arbeitsblatt übereinstimmt.

Als Formatierung wählen Sie eine auffällige Hervorhebung, wie einen gelben Hintergrund,

Im Beispiel würde sich diese Bedingte Formatierung so auswirken:

Ergebnis der Bedingten Formatierung mit VERGLEICH()
Die Zellen, deren Inhalt VERGLEICH im anderen Tabellenblatt findet, werden hervorgehoben.

Der dritte Parameter von VERGLEICH() wirkt sich auf die Art des Vergleichs aus. Nur mit der Null wird auf echte Übereinstimmung geprüft.

Eine ähnliche Problemstellung finden Sie im Tipp So nutzen Sie in Excel die bedingte Formatierung zusammen mit einer Werteliste. Hier sind allerdings die nachzuschlagenden Daten in die Formel eingebaut. Wenn sich also in den Referenzwerten etwas ändert, muss die Formel für die bedingte Formatierung angepasst werden – bei häufigen Änderungen ist das fehlerträchtig und unbequem.

Werbung
Foto des Autors

Markus Schraudolph

Markus ist IT-Fachjournalist der ersten Stunde. Seine ersten Texte veröffentlichte er 1987 beim legendären Markt&Technik-Verlag. Seine Spezialität sind Datenbanken und Microsoft Excel. Als PHP-Experte programmierte Markus maßgeblich die erste Version des Tippscout und ist anerkannter Experte für PHP, MySQL und Wordpress.