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
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
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
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
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
Fertig! Und so sieht das Ergebnis aus:
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.
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.
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
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
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!