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

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.

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

  1. Markieren Sie den Bereich, der die Markierung erhalten soll. Im Beispiel wäre des die Spalte C ab der zweiten Zeile.
  2. Rufen Sie den Menüpunkt Bedingte Formatierung - Neue Regel auf.
  3. Bei Regeltyp wählen Sie den Formel zur Ermittlung der zu formatierenden Zellen verwenden.
  4. Als Formel wählen Sie =VERGLEICH(C2;Tabelle2!A2:A100;0)
  5. 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.
  6. Als Formatierung wählen Sie eine auffällige Hervorhebung, wie einen gelben Hintergrund,
  7. 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.


Letzte Aktualisierung am 19.12.2016 / Affiliate Links / Bilder von der Amazon Product Advertising API



2 Gedanken zu “So markiert Excel Zellen, deren Wert in einer Referenztabelle enthalten ist

  1. Hallo Markus Schraudolph,

    vielen Dank für den Excel-Tipp um Daten mit einer Referenztabelle zu vergleichen. Ich hätte dazu noch eine Frage:
    ich habe privat bislang nur Excel 2007 und hier funktionert diese Regel leider nicht. Kann es sein, dass dies in der Excel 2007-Version wirklich nicht möglich war?

    Danke vorab für eine kurze Rückmeldung
    Grüße
    Silke Hailer

Schreibe einen Kommentar