Unikate gesucht: Listenabgleich in Excel 3

Mit der SUMMENPRODUKT-Funktion lassen sich in Excel Unikate in zwei unterschiedlichen Listen finden.
 

Die Verarbeitung von Listen gehört bekanntermaßen zu den Standardanwendungen von Excel. Wie man Dubletten in solchen Listen findet, habe ich im Artikel „Das doppelte Lottchen“ ja schon gezeigt.

Doch was macht man, wenn man stattdessen Unikate in zwei unterschiedlichen Listen identifizieren möchte? Sozusagen das schwarze Schaf in der weißen Herde? Den Osterhasen unter den Nikoläusen?

Eine geschickte Kombination der SUMMENPRODUKT-Funktion mit der bedingten Formatierung führt hier zum Ziel.

Und so geht’s:

Die Listen

In meinem Beispiel möchte ich zwei Städtelisten miteinander vergleichen. Das Ziel ist es, alle Städte farblich hervorzuheben, die nur in einer der beiden Listen enthalten sind. Zur einfacheren Handhabung habe ich die beiden Listen mit einem Namen versehen: städte1 und städte2:

Die beiden Städtelisten

Die beiden Städtelisten

SUMMENPRODUKT mal anders

Die SUMMENPRODUKT-Funktion dient eigentlich dazu, zwei Listen miteinander zu multiplizieren und die Ergebnisse danach zu addieren:
=SUMMENPRODUKT(liste1; liste2; liste3 ...)
Dies setzt natürlich voraus, dass die Listen numerische Werte enthalten, mit denen man auch rechnen kann.

Im Artikel „Neo lässt grüßen“ haben wir gesehen, dass man mit dieser Funktion auch Bedingungen prüfen kann, deren Ergebnis dann ein Wahrheitswert (WAHR bzw. FALSCH) ist. Mit diesem Wissen lassen sich dann auch ganz andere Dinge bewerkstelligen, die die Microsoft-Entwickler ursprünglich wahrscheinlich nicht im Kopf hatten.

Im folgenden Bild habe ich vor jede einzelne Stadt eine spezielle Summenprodukt-Funktion gestellt, die entweder 1 oder 0 ergibt:

Abgleich per SUMMENPRODUKT-Funktion

Abgleich per SUMMENPRODUKT-Funktion

Sehen wir uns die erste Formel in Zelle A5 näher an:
=SUMMENPRODUKT(--(B5=städte2))
Wir prüfen also, ob der Wert in Zelle B5 in der Liste „städte2“ enthalten ist. Aber was hat es mit den doppelten Minuszeichen vor der zweiten Klammer auf sich?

Wie oben erwähnt, benötigt SUMMENPRODUKT numerische Werte, um rechnen zu können. Die Prüfung, ob eine Stadt in der anderen Liste enthalten ist, ergibt aber nur einen Wahrheitswert WAHR oder FALSCH. Um Excel dazu zu zwingen, diesen Wahrheitswert in die numerischen Werte 1 oder 0 umzuwandeln, wird das doppelte Minuszeichen verwendet. Alternativ hätte man auch mit 1 multiplizieren oder 0 addieren können:
=SUMMENPRODUKT(1*(B5=städte2))
=SUMMENPRODUKT(0+(B5=städte2))
führen zum gleichen Ergebnis.

Somit erhalten wir also 1, wenn die Stadt in der zweiten Liste ebenfalls vorhanden ist und 0, wenn sie dort nicht enthalten ist.

Und wie bekomme ich jetzt die eingangs erwähnte farbliche Markierung dieser Städte hin?

Bedingte Formatierung

Hier haben wir einen klassischen Einsatz für die bedingte Formatierung. Markieren wir also die erste Städteliste, rufen das Menü „Bedingte Formatierung“ auf und wählen die Option „Regeln verwalten…“

Bedingte Formatierung

Bedingte Formatierung

Dann fügen wir eine neue Regel ein, in der wir die zuvor getestete SUMMENPRODUKT-Funktion in leicht erweiterter Form eintragen:

Neue Regel hinzufügen

Neue Regel hinzufügen

Zur Erinnerung: Die Funktion ergibt den Wert 0, wenn die Stadt nicht in der zweiten Liste enthalten ist. In diesem Fall wollen wir die Zelle mit der Stadt farbig einfärben. Und genau deshalb hängen wir am Ende der Funktion noch die Prüfung „=0“ an. Nun legen wir noch die gewünschte Formatierung fest, z.B. eine grüne Hintergrundfarbe und fertig ist die erste Liste. Danach markieren wird die zweite Städteliste und fügen auch hier eine neue Regel für die bedingte Formatierung ein:

Regel für die zweite Liste

Regel für die zweite Liste

Fertig! Und so sieht das Ergebnis aus:

Fertige Listen

Fertige Listen

Jede Stadt, die in der jeweils anderen Liste nicht enthalten ist, hat eine farbige Markierung. Wie man sieht, steckt in der SUMMENPRODUKT-Funktion ziemliches Potential. Und geschickt kombiniert mit anderen Excel-Funktionalitäten, wie z.B. der bedingten Formatierung, lassen sich damit verblüffende Ergebnisse erzielen.

Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

3 Gedanken zu “Unikate gesucht: Listenabgleich in Excel

  • Avatar-Foto
    Zangger Melania

    Wie kann ich eine Adressliste über mehrere Spalten nach Duplikaten absuchen?
    Leider kenne ich nur eine Formel über eine Spalte : =WENN(H3=““;““;(WENN(H3=H2;“!!!“;““)))
    Ich möchte diese jedoch über mehrere Spalten, leider bekomme ich immer einen Wertfehler.
    Danke für die Hilfe

    • Avatar-Foto
      Martin Weiß

      Hallo Melania,

      eine sehr schnelle Möglichkeit wäre der Spezialfilter: Registerkarte „Daten“, Befehlsgruppe „Sortieren und Filtern“, „Erweitert“.
      Als Listenbereich markierst Du die ganze Tabelle, und als Kriterienbereich alle relevanten Spaltenköpfe. Dann setzt Du unten das Häkchen „Keine Duplikate“.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Stefanie

    Hallo!
    Gibt es eine einfache Möglichkeit ganze Tabellen miteinander zu vergleichen und Unterschiede zu finden wie neue Zeilen, geänderte Zeilen? Ich habe es über bedingte Formatierung versucht und die Tabellen jeweils in unterschiedlichen Tabs gespeichert „=A1Tab1!A1“ aber diese Möglichkeit gibt es in Excel 2010 anscheinend noch nicht.
    Danke!