Wie wendet man bedingte Formatierung basierend auf VLOOKUP in Excel an?

Angenommen, Sie führen eine Liste mit Schülernoten für das aktuelle und ein vorangegangenes Semester und möchten schnell erkennen, welche Schüler sich verbessert haben. In diesem Szenario vergleichen Sie die aktuelle Note jedes Schülers mit seinem Ergebnis aus dem vorherigen Semester und heben visuell jene hervor, die besser abgeschnitten haben.

Um einen hervorgehobenen Zeilenbereich im Arbeitsblatt Score hervorzuheben, wenn die aktuelle Note eines Schülers höher ist als seine Note im letzten Semester, können Sie VLOOKUP innerhalb einer Regel für bedingte Formatierung verwenden. Dieser Ansatz eignet sich hervorragend zur Überwachung akademischer Leistungen, zum Verfolgen von Projektmeilensteinen oder für jede wiederholte Datenanalyse, bei der ein Vergleich erforderlich ist.

1. Wählen Sie im Arbeitsblatt Score den Bereich der Schülernoten aus, den Sie bewerten möchten (ohne Überschriften; wählen Sie in diesem Beispiel B3:C26 aus). Wechseln Sie zur Registerkarte Start, klicken Sie auf Bedingte Formatierung verwenden und wählen Sie Neue Regel.

2. Führen Sie im Dialogfeld Neue Formatierungsregel Folgendes aus:

1. Wählen Sie „Eine Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen“.
2. Geben Sie die folgende Formel in das Feld Formatwerte, bei denen diese Formel wahr istein:

   =VLOOKUP($B3,'Score of Last Semester'!$B$2:$C$26,2,FALSE) < Score!$C3

3. Klicken Sie auf die Schaltfläche Formatieren, um Ihre bevorzugte Formatierung auszuwählen.

Hinweis:In dieser Formel

• $B3 bezieht sich auf den Namen des ersten Schülers im Arbeitsblatt Score. Wenn Sie die bedingte Formatierung auf mehrere Zeilen anwenden, passt Excel diesen Bezug automatisch für jede Zeile an.
• ‚Score of Last Semester'!$B$2:$C$26 definiert den Suchbereich für die Noten des vorherigen Semesters. Passen Sie den Bereich bei Bedarf an, falls Ihre Liste länger ist oder in anderen Zeilen beginnt bzw. endet.
• 2 gibt an, dass sich die aus dem Suchbereich abzurufenden Werte in dessen zweiter Spalte befinden.
• Score!$C3 verweist auf die aktuelle Note des Schülers im zu formatierenden Arbeitsblatt.

3. Navigieren Sie im Dialogfeld Zellenformat festlegen zur Registerkarte Füllung, wählen Sie eine Hervorhebungsfarbe aus und bestätigen Sie mit OK > OK, um die Dialogfelder zu schließen und die Einstellungen anzuwenden.

Sobald die Regel angewendet wurde, hebt die Formatierung automatisch jede Schülerzeile hervor, deren aktuelle Note höher ist als die des vorherigen Datensatzes – so sind positive Entwicklungen auf einen Blick erkennbar.

Falls einige Zeilen nicht wie erwartet hervorgehoben werden, prüfen Sie, ob beide Arbeitsblätter exakt dieselben Namen verwenden – ohne zusätzliche Leerzeichen oder inkonsistente Schreibweisen – und ob Ihre Bezugsbereiche sämtliche erforderlichen Daten enthalten. Da VLOOKUP bei fehlender Übereinstimmung einen Fehler zurückgibt, ist ein konsistentes Datenlayout für präzise Ergebnisse unerlässlich.

Möglicherweise möchten Sie visuell erkennen, ob Elemente einer Liste in einem anderen Arbeitsblatt vorhanden sind – beispielsweise Teilnehmer einer Gewinnerliste markieren, die auch in einer Hauptliste enthalten sind. Bedingte Formatierung verwenden mit VLOOKUP ermöglicht eine Echtzeit-Hervorhebung übereinstimmender oder nicht übereinstimmender Elemente, was in vielen Veranstaltungs- oder Teilnehmerverwaltungsszenarien wertvoll ist.

Hinweis: Ziehen Sie die vertikale Bildlaufleiste, um das obige Bild anzuzeigen.

Für diesen Anwendungsfall haben Sie möglicherweise eine Gewinnerliste auf Sheet1 und eine vollständige Schülerliste auf Sheet2. Um Namen in der Gewinnerliste hervorzuheben, die auch in der Hauptliste enthalten sind, gehen Sie wie folgt vor:

1. Markieren Sie die Namen in der Gewinnerliste (ohne Überschriften) und wechseln Sie dann zu Start > Bedingte Formatierung verwenden > Neue Regel.

2. Führen Sie im Dialogfeld „Neue Formatierungsregel“ Folgendes aus:

1. Wählen Sie unter Regeltyp auswählen die Option Eine Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen.
2. Geben Sie die folgende Formel in das Feld Formatwerte, bei denen diese Formel wahr istein:

   =NOT(ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE)))

3. Klicken Sie auf die Schaltfläche Formatieren, um den Hervorhebungsstil festzulegen.

Hinweis:

• $C3 bezieht sich auf den Vornamen in der Gewinnerliste – stellen Sie sicher, dass Ihre Auswahl dem tatsächlichen Layout Ihrer Daten entspricht.
• Sheet2!$B$2:$C$24 ist die Suchtabelle mit der vollständigen Schülerliste. Passen Sie den Bereich bei Bedarf an Ihr Arbeitsblatt an.
• 1 gibt an, dass VLOOKUP in der ersten Spalte des begrenzten Bereichs sucht.

Falls Sie Namen Bereich hervorheben aus der Gewinnerliste hervorheben möchten, die nichtin der Schülerliste enthalten sind, verwenden Sie die entgegengesetzte Formel:

=ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE))

3. Navigieren Sie im Dialogfeld Zellenformat festlegen zur Registerkarte Füllung, wählen Sie Ihre bevorzugte Hintergrundfarbe aus und klicken Sie auf OK, um den Vorgang abzuschließen.

Nach Anwendung der Regel werden automatisch alle Namen hervorgehoben, die Ihren Kriterien für Übereinstimmung oder Nichtübereinstimmung entsprechen. Dadurch werden Fehlerprüfungen, Teilnehmervalidierungen oder Listenabgleiche deutlich effizienter.

Tipp: Falls Sie unerwartete Ergebnisse erhalten, überprüfen Sie Ihre Suchbereiche auf zusätzliche Leerzeichen oder inkonsistente Werte, die die exakte Übereinstimmungslogik von VLOOKUP beeinträchtigen könnten. Erwägen Sie bei Bedarf die Verwendung der GLÄTTEN-Funktion (TRIM) in Hilfsspalten, um Ihre Daten zu standardisieren.

Wenn Sie Kutools für Excel installiert haben, profitieren Sie von mehr Flexibilität und Komfort beim Vergleichen von Listen und bei der Anwendung bedingter Formatierung. Mit der Funktion Gleiche/Unterschiedliche Zellen auswählen können Sie übereinstimmende oder abweichende Werte mit nur wenigen Klicks hervorheben – und so das Risiko von Fehlern minimieren, wie sie bei manueller Formeleingabe häufig auftreten.

Kutools für Excel umfasst mehr als 300 praktische Tools für Excel und bietet robuste Unterstützung bei Datenvergleichs- und Formatierungsaufgaben – besonders vorteilhaft für alle, die regelmäßig umfangreiche Listen oder vielfältige Datensätze verarbeiten. Testen Sie den vollen Funktionsumfang jetzt 60 Tage lang kostenlos,ohne Kreditkarte.

1. Klicken Sie auf Kutools > Auswählen > Gleiche/Unterschiedliche Zellen auswählen, um das Tool zu starten.

2. Konfigurieren Sie im Dialogfeld „Gleiche/Unterschiedliche Zellen auswählen“ Ihre Einstellungen wie folgt:

1. Wählen Sie im Feld Werte suchen in die Namensspalte der Gewinnerliste aus.
2. Wählen Sie im Feld Entsprechend die Namensspalte der Schülerliste aus.
3. Falls Ihre Daten Überschriften enthalten, können Sie bei Bedarf die Option Titel einschließen aktivieren.
4. Wählen Sie unter Basierend auf die Option Jede Zeile für einen zeilenweisen Vergleich aus.
5. Wählen Sie im Abschnitt Suchen entweder Gleiche Werte oder Unterschiedliche Werte aus, je nachdem, ob Übereinstimmungen oder Abweichungen hervorgehoben werden sollen.
6. Aktivieren Sie Hintergrundfarbe füllen, und wählen Sie eine gewünschte Farbe zur Hervorhebung aus.
7. Wenn Sie die gesamte Zeile hervorheben möchten, wählen Sie Gesamte Zeile auswählen.

3. Klicken Sie auf OK, um den Vorgang auszuführen. Das Tool markiert und wählt sofort die Zeilen mit übereinstimmenden (oder unterschiedlichen) Werten aus. Zusätzlich zeigt ein Dialogfeld die Anzahl der ausgewählten Zeilen an, sodass Sie die Vergleichsergebnisse auf einen Blick bewerten können.

Diese Methode ist besonders vorteilhaft bei langen Listen, da sie den manuellen Aufwand zum Erstellen und Fehlersuchen von Formeln entfällt. Zudem reduzieren das visuelle Feedback und das zusammenfassende Dialogfeld Raten und potenzielle Fehler.