Bedingte Formatierung basierend auf SVERWEIS-Ergebnis – Excel & GS
In diesem Tutorial zeigen wie Ihnen mehrere Beispiele für die Anwendung bedingter Formatierung auf der Grundlage des Ergebnisses einer SVERWEIS-Funktion in Excel und Google Sheets. Wenn Ihre Excel-Version XVERWEIS unterstützt, empfehlen wir, stattdessen XVERWEIS zu verwenden.
Betrachten wir ein Beispiel, in dem wir eine bedingte Formatierung auf der Grundlage des Ergebnisses einer SVERWEIS-Funktion anwenden möchten.
Format basierend auf einem SVERWEIS-Vergleich
Zunächst wenden wir die bedingte Formatierung auf die Tabelle mit den Namen und Studenten-IDs (Spalte E-H) an, indem wir die Punktzahlen der einzelnen Studenten (Spalte B-C) nachschlagen und die Zellen mit roter Farbe ausfüllen, wenn die Punktzahlen unter 72 liegen. Das Ergebnis ist hier zu sehen, aber wir werden die Schritte weiter unten durchgehen.
=SVERWEIS($F3;$B$3:$C$7;2;FALSCH)<72
Schauen wir uns an, wie die obige Formel in der bedingten Formatierung anwendet wird.
Bedingte Formatierung anwenden
- Markieren Sie den Bereich, auf den die bedingte Formatierung angewendet werden soll.
- Gehen Sie in der Menüleiste auf die Hauptregisterkarte Start > Gruppe Formatvorlagen > Bedingte Formatierung > Neue Regel.
- Wählen Sie im Popup-Menü den Typ der Regel: Verwenden Sie eine Formel, um die zu formatierenden Zellen zu bestimmen.
- Geben Sie unsere Formel in die Formelleiste ein.
- Klicken Sie auf Formatieren und legen Sie Ihre Formateinstellungen fest. In unserem Fall werden wir eine ROTE Füllfarbe wählen. Klicken Sie dann auf OK.
- Klicken Sie nun auf OK und die Regel der bedingten Formatierung wird angewendet.
Da wir nun wissen, wie eine bedingte Formatierung anwendet wird, gehen wir die Formel durch.
Es kann hilfreich sein, zunächst die Formel(n) für die bedingte Formatierung in die Zellen einzugeben, um zu testen, ob sie wie erwartet funktionieren. Wir werden dies im Folgenden tun.
Die SVERWEIS-Funktion
Zunächst führen wir unseren SVERWEIS durch.
=SVERWEIS($F3;$B$3:$C$7;2;FALSE)
Wir verwenden $-Zeichen, um Zellenbezüge zu sperren. Dies ist wichtig, um eine für alle Zeilen funktionierende Formel zu erstellen.
Logischer Vergleich
Als nächstes werden die Ergebnisse mit dem festgelegten Kriterium verglichen.
=H3<72
Durch die bedingte Formatierung wird die Zellenfarbe für alle Zeilen, die WAHR enthalten, geändert.
Kombinieren wir alle Formeln miteinander, erhalten wir unsere ursprüngliche SVERWEIS-Formel:
=SVERWEIS($F3;$B$3:$C$7;2;FALSCH)<72Formatieren, wenn SVERWEIS leer ist
Beachten Sie, dass im vorherigen Beispiel ein Student keine Punktzahl hatte. Wir können diese Zellen erkennen und hervorheben, indem wir die ISTLEER-Funktion hinzufügen:
=ISTLEER(SVERWEIS($F3;$B$3:$C$7;2;FALSCH)) 
So wird die Formel ausgewertet:
Formatieren, wenn SVERWEIS in einem Wertebereich liegt
Ein weiteres häufiges Szenario mit SVERWEIS ist die Prüfung, ob der Wert innerhalb eines bestimmten Wertebereichs liegt.
Zu diesem Zweck können wir die UND-Funktion zusammen mit SVERWEIS verwenden:
=UND(SVERWEIS($F3;$B$3:$C$7;2;FALSCH)>=72;SVERWEIS($F3;$B$3:$C$7;2;FALSCH)<=74)
Lassen Sie uns die Formel durchgehen:
Die untere Grenze
Zunächst prüfen wir, ob die Ausgabe von unserem SVERWEIS größer oder gleich einer bestimmten Untergrenze ist (z. B. 72).
=H3>=72
Die obere Grenze
Als Nächstes wird geprüft, ob derselbe Wert kleiner oder gleich der Obergrenze ist (z. B. 74).
=H3<=74
Die UND-Funktion
Schließlich verwenden wir die UND-Funktion, um zu prüfen, ob beide Bedingungen WAHR sind.
=UND(I3;J3)
Die Kombination der obigen Formeln führt zu unserer ursprünglichen Formel:
=UND(SVERWEIS($F3;$B$3:$C$7;2;FALSCH)>=72;SVERWEIS($F3;$B$3:$C$7;2;FALSCH)<=74)Bedingte Formatierung – Mehrere SVERWEIS-Bedingungen
Sie können problemlos mehrere bedingte Formatierungsregeln hinzufügen:
Die Regeln für die bedingte Formatierung werden der Reihe nach (von oben nach unten) angewendet. Wenn Anhalten markiert ist und die dazugehörige Bedingung erfüllt ist., werden keine weiteren Formatierungsregeln getestet oder angewendet.
In unserem Beispiel erfüllt der leere SVERWEIS (z. B. ZEILE 6) zwei Bedingungen, die rote (z. B. <72) und die orangefarbene (z. B. ISTLEER). Da die ISTLEER-Regel zuerst angewendet wird und Anhalten markiert ist, wird ZEILE 6 ORANGE hervorgehoben, da die ISTLEER-Bedingung erfüllt ist und die nachfolgenden Regeln nicht getestet werden.
Bedingte Formatierung basierend auf einem SVERWEIS-Ergebnis in Google Sheets
Alle oben besprochenen Formeln funktionieren in Google Sheets auf die gleiche Weise, außer wenn sich die Nachschlagetabelle in einem anderen Blatt befindet. In diesem Fall müssen wir einen benannten Bereich oder die INDIREKT-Funktion verwenden, um in der bedingten Formatierung auf Bereiche aus anderen Blättern zu verweisen.
=ISTLEER(SVERWEIS($C3;INDIREKT("Punktzahlen!B3:C7");2;FALSCH))Hier sind die Schritte, wie Sie die bedingte Formatierung in Google Sheets anwenden können:
- Markieren Sie den Bereich und gehen Sie dann zur Registerkarte Format > Bedingte Formatierung
- Gehen Sie in der Seitenleiste zu „Regeln für die bedingte Formatierung“ und wählen Sie „Benutzerdefinierte Formel ist“
- Geben Sie unsere Formel in die Formelleiste ein, dann legen Sie die Art der Formatierung fest und klicken Sie auf „Fertig“:
