Der Abgleich von Listen oder Tabellen ist ein durchaus häufiger Anwendungsfall in Excel. Es geht unter anderem darum, Gemeinsamkeiten oder Unterschiede zwischen zwei Tabellen herauszufinden. Wie so oft bietet Excel verschiedene Lösungsmöglichkeiten, beispielsweise mit Hilfe von Power Query.
Im heutigen Artikel werde ich jedoch auf eine dynamische Array-Funktion zurückgreifen, die in Microsoft 365 und Excel 2021 enthalten ist.
Und so geht’s:
Beispieldatei herunterladen
Die Aufgabenstellung
Für das heutige Beispiel habe ich zwei fiktive Artikellisten erstellt, die gegeneinander abgeglichen werden sollen. Es handelt sich dabei um formatierte („intelligente“) Tabellen mit den Namen tblArtikelAlt und tblArtikelNeu:
Zwei Artikeltabellen sollen miteinander verglichen werden
Die Aufgabe ist es nun, sowohl die Artikel zu identifizieren, die in beiden Tabellen enthalten sind, als auch diejenigen Artikel, die nur in jeweils einer Tabelle vorkommen. Zu Fuß ist das aus offensichtlichen Gründen natürlich etwas mühsam. Aber zum Glück ist das auch gar nicht nötig.
Die Lösung
Wer die aktuelle Excel-Version 2021 oder Microsoft 365 im Einsatz hat, kann auf die geniale FILTER-Funktion zurückgreifen. In Kombination mit der altbekannten ZÄHLENWENN-Funktion liefert sie die Lösung für unser Problem.
Schritt 1: Finde die Artikel, die in beiden Tabellen vorkommen
Die FILTER-Funktion filtert eine Liste oder einen Bereich nach einem oder mehreren Kriterien und gibt die gefilterte Liste an einer anderen Stelle dynamisch zurück. Doch was ist in unserem Fall das Filterkriterium?
Es muss für jeden Artikel aus tblArtikelAlt geprüft werden, ob er auch in tblArtikelNeu vorkommt. Das lässt sich mit der ZÄHLENWENN-Funktion bewerkstelligen, die ich in Spalte G eingetragen habe. In der Vergangenheit (also vor Excel 2021) konnte man in ZÄHLENWENN nur eine einzige Zelle als Suchkriterium angeben und musste die Formel dann soweit wie eben nötig nach unten kopieren:
=ZÄHLENWENN(tblArtikelNeu[Artikel];tblArtikelAlt[@Artikel])
ZÄHLENWENN liefert 1 oder 0
Geprüft wird dabei immer der Artikel aus der jeweiligen Zeile, daher [@Artikel]
Es wird also für jeden Artikel aus tblArtikelAlt der Wert 1 geliefert, wenn dieser Artikel auch in tblArtikelNeu enthalten ist. Falls nicht, kommt der Wert 0.
Seit Excel 2021 (und natürlich in Microsoft 365) lässt sich stattdessen gleich die komplette Spalte prüfen und die Formel läuft automatisch in alle benötigten Zeilen über:
=ZÄHLENWENN(tblArtikelNeu[Artikel];tblArtikelAlt[Artikel])
Die dynamische Variante in Microsoft 365
Und diese Funktion lässt sich dann als Kriterium in der FILTER-Funktion verwenden und man erhält das gewünschte Ergebnis:
=FILTER(tblArtikelAlt;ZÄHLENWENN(tblArtikelNeu[Artikel];tblArtikelAlt[Artikel]))
FILTER kombiniert mit ZÄHLENWENN liefert die Lösung
Die FILTER-Funktion filtert also alle Einträge, die den Wert 1 haben (oder WAHR)
Schritt 2: Finde die Artikel, die nur in der ersten Tabelle vorkommen
Um nun die Artikel zu erhalten, die nur in der tblArtikelAlt enthalten sind, ist nur eine kleine Anpassung in der Formel notwendig. Es wird nur noch geprüft, ob das Ergebnis der ZÄHLENWENN-Funktion den Wert 0 liefert:
=FILTER(tblArtikelAlt;ZÄHLENWENN(tblArtikelNeu[Artikel];tblArtikelAlt[Artikel])=0)
Die zusätzliche Prüfung auf den Wert 0
Die Prüfung auf den Wert 0 ergibt ein logisches WAHR, wenn der Artikel eben nicht in tblArtikelNeu gefunden wurde beziehungsweise ein logisches FALSCH, wenn er gefunden wurde. Und FILTER berücksichtigt eben alle WAHR-Werte (oder 1, wie im ersten Beispiel).
Schritt 3: Finde die Artikel, die nur in der zweiten Tabelle vorkommen
Hierfür muss nur die Reihenfolge der Tabellen in ZÄHLENWENN vertauscht werden, um das richtige Ergebnis zu erhalten:
=FILTER(tblArtikelNeu;ZÄHLENWENN(tblArtikelAlt[Artikel];tblArtikelNeu[Artikel])=0)
Die Tabellen müssen nur vertauscht werden
Das Schöne an diesen Formeln ist, dass sie wirklich dynamisch sind. Sobald sich eine der beiden Tabellen verändert, länger oder kürzer wird, ändern sich auch die gefilterten Listen automatisch.
Für alle Anwender von Excel 2019 oder älter, die sich jetzt darüber ärgern, dass ihnen die FILTER-Funktion nicht zur Verfügung steht:
Im Artikel Unikate gesucht: Listenabgleich in Excel habe ich ein ähnliches Szenario mit der SUMMENPRODUKT-Funktion beschrieben. Natürlich geht das nicht so elegant und komfortabel wie hier, aber es wäre eine zumindest eine Alternative.
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.
Toller Artikel, sehr hilfreich
Dankeschön, gern geschehen.
Für diese Fragestellung nutze ich gerne PowerQuery und die unterschiedlichen Join Arten (vielleicht ja mal ein Artikel Wert, da du doch auch ein PQ-Fan bist)
Damit bekommt man es auch mit den älteren Excel Versionen ziemlich gut hin. Nachteil ist, dass man zum aktualisieren einmal klicken muss.
Hallo Daniel,
klar, Power Query bietet hier mit den verschiedenen Join-Arten sehr praktische Optionen. Vielleicht schreibe ich wirklich mal einen Artikel dazu.
Schöne Grüße,
Martin