Hallo Paula, kannst du Listen in Excel vergleichen?
oder
Was ist der beste Weg, um zwei Datensätze in Excel zu vergleichen?
Sehr oft gibt es in Excel die Anforderung, zwei Listen oder zwei Datensätze zu vergleichen, um fehlende oder übereinstimmende Elemente zu finden. Da es sich um Excel handelt, gibt es immer mehr als eine Möglichkeit, Dinge zu tun, einschließlich des Vergleichs von Daten. Von Formeln und bedingter Formatierung bis hin zu Power Query. In diesem Artikel werden wir uns eine Reihe von Möglichkeiten ansehen, zwei Listen in Excel zu vergleichen, und wir werden uns auch den Vergleich ganzer Zeilen eines Datensatzes ansehen.
Vielleicht ist Ihre bevorzugte Methode hier nicht aufgeführt. Wenn nicht, hinterlassen Sie doch einen Kommentar und teilen Sie uns mit, wie Sie zwei Listen oder Datensätze in Excel vergleichen möchten. Ich freue mich darauf, Ihre Kommentare zu lesen.
Wir möchten Liste 1 mit Liste 2 vergleichen.
Inhalt
Schnelle bedingte Formatierung, um zwei Datenspalten zu vergleichen
Aufhebung der bedingten Formatierung
Vergleich von Daten in Excel mit der MATCH-Funktion
Vergleich von 2 Listen in Excel 365 mit MATCH oder XMATCH als Dynamische Array-Funktion
MATCH und dynamische Arrays zum Vergleichen von 2 Listen
XMATCH Excel 365 zum Vergleichen von zwei Listen
Tabellen – Vergleichen von Listen in Excel, bei denen sich die Bereichsgrößen ändern können
Unterschiede in Listen mit benutzerdefinierter bedingter Formatierung hervorheben
Kopieren Formel in die benutzerdefinierte bedingte Formatierung
Andere Formeln zum Vergleichen von zwei Listen in Excel
VLOOKUP zum Vergleichen von zwei Listen in Excel
XLOOKUP zum Vergleichen von zwei Listen in Excel
COUNTIF zum Vergleichen von zwei Listen in Excel
Wie man 2 Datensätze in Excel vergleicht
Vergleichen von Listen oder Datensätzen mit Power Query
Schnelle bedingte Formatierung, um zwei Datenspalten zu vergleichen
Mit der bedingten Formatierung können Sie eine Zelle oder einen Bereich anhand vordefinierter Kriterien hervorheben. Der schnellste und einfachste Weg, diese beiden Spalten schnell visuell zu vergleichen, ist die Verwendung der vordefinierten Regel zum Hervorheben doppelter Werte.
Starten Sie mit der Auswahl der beiden Datenspalten.
Wählen Sie auf der Registerkarte Start die Dropdown-Liste Bedingte Formatierung. Wählen Sie dann Regeln zum Hervorheben von Zellen. Wählen Sie als Nächstes die Option Werte duplizieren.
Ein Einstellungsfeld für doppelte Werte wird geöffnet, in dem Sie die Formatierung festlegen und zwischen doppelten oder eindeutigen Werten wählen können.
Durch die Auswahl von Duplikat werden alle wiederkehrenden Einträge auf die ausgewählte Formatierung gesetzt. Jetzt können Sie schnell die Einträge in Liste 1 sehen, die in Liste 2 enthalten sind, da dies die formatierten Einträge sind. Sie können auch schnell die Einträge in Liste 2 sehen, die nicht in Liste 1 enthalten sind, da auf diese keine Formatierung angewendet wurde.
Sie können jedoch auch die eindeutigen Einträge formatieren. Dies ist möglich, indem Sie im Feld „Doppelte Werte“ die Option „Eindeutig“ auswählen.
In diesem Fall haben wir zwei verschiedene bedingte Formate angewendet. Die rote Farbe kennzeichnet die Duplikate und die grüne Farbe die eindeutigen Elemente.
Bitte beachten Sie, dass ich nicht nur die Zellen mit Daten genommen habe, sondern alle Spalten A bis C. Spalte B enthält keine Daten und kann daher die Ergebnisse nicht beeinflussen. Diese Zellen enthalten jedoch die angewandte bedingte Formatierung, so dass es besser wäre, nur die benötigten Zellen auszuwählen.
Löschen der bedingten Formatierung
Um die gesamte bedingte Formatierung zu löschen, wählen Sie zunächst die Zelle oder den Bereich aus. Wählen Sie dann das Dropdown-Menü für die bedingte Formatierung im Menüband Start. Wählen Sie dann Regeln löschen. Wählen Sie schließlich Regeln aus ausgewählten Zellen löschen.
Wenn Sie mehr als eine bedingte Formatierung gleichzeitig angewendet haben und nur eine davon entfernen möchten, wählen Sie Regeln verwalten aus dem Dropdown-Menü für bedingte Formatierungen. Wählen Sie die Regel aus, die Sie löschen möchten, und wählen Sie dann Regel löschen.
Durch Drücken von OK wird die Regel aus dem Regel-Manager entfernt, und die Zellen enthalten nicht mehr die Formatierung.
So, das ist die einfachste Möglichkeit, zwei Listen in Excel zu vergleichen. Es geht schnell, ist einfach und effektiv. Sie können auch bedingte Formatierungen auf der Grundlage von Formeln anwenden, die wir uns später in diesem Artikel ansehen werden.
Vergleichen Sie Daten in Excel mit der MATCH-Funktion
Es gibt viele Nachschlageformeln, die Sie verwenden können, um zwei Bereiche oder Listen in Excel zu vergleichen. Die erste, die wir uns ansehen werden, ist die MATCH-Funktion.
Die MATCH-Funktion gibt die relative Position in einer Liste zurück. Die Syntax für MATCH lautet
=MATCH (Nachschlagewert, Nachschlagefeld, Übereinstimmungstyp)
Wobei Nachschlagewert der Wert ist, für den Sie eine Übereinstimmung finden möchten. Lookup array ist die Liste, in der Sie nach einer Übereinstimmung suchen. Und Match type erlaubt Ihnen, zwischen einer exakten und einer ungefähren Übereinstimmung zu wählen.
Wir wollen eine Match-Formel schreiben, um zu sehen, ob die Elemente in Liste 2 in Liste 1 sind.
In Zelle E3 können wir die Formel
=MATCH(C2, $A$2:$A$21,0)
Indem wir diese Formel ausfüllen, wird für die Werte, bei denen Excel eine Übereinstimmung findet, die Position dieser Übereinstimmung zurückgegeben. Wenn es keine Übereinstimmung gibt, ist der Rückgabewert ein #N/A.
Sehr oft ist die relative Position oder das #N/A für uns nicht von Wert und wir müssen diese Werte in wahr oder falsch umwandeln. Zu diesem Zweck können wir unsere Match-Formel mit einer logischen Funktion leicht erweitern. Da Match eine Zahl zurückgibt, können wir die Funktion ISNUMBER verwenden
=ISNUMBER(MATCH(C2, $A$2:$A$21,0))
Vergleichen Sie 2 Listen in Excel 365 mit MATCH oder XMATCH als dynamische Array-Funktion
Wenn Sie Excel 365 verwenden, haben Sie weitere Alternativen, wenn Sie MATCH zum Vergleichen von Listen oder Daten verwenden. Da Excel 365 in Arrays denkt, können wir jetzt ein Array als Nachschlagewert von MATCH übergeben und unsere Ergebnisse werden für uns verschüttet. Damit entfällt die Notwendigkeit, die Formel nach unten zu kopieren, und mit nur einer Formel wird Ihre Tabelle weniger fehleranfällig und kompakter.
MATCH und dynamische Arrays zum Vergleich von 2 Listen
Wenn Sie noch nicht mit dynamischen Arrays vertraut sind, empfehle ich Ihnen, diesen Artikel zu lesen: Excel Dynamic Arrays – A new way to model your Excel Spreadsheets (Dynamische Arrays in Excel – Eine neue Art, Ihre Excel-Tabellen zu modellieren), um ein besseres Verständnis für die Funktionsweise und die Überlaufbereiche zu bekommen.
Die einzige Änderung an der Abgleichsformel besteht darin, dass wir statt der Zelle C2 als Nachschlagewert den Bereich C2:12 auswählen
=ISNUMMER(MATCH (C2:C12,$A$2:$A$21,0))
Wie Sie sehen können, werden die Ergebnisse durch die eine Formel in die Spalte E verschüttet.
XMATCH Excel 365 zum Vergleich zweier Listen
Excel 365 führt auch die neue Funktion XMATCH ein. Genau wie die MATCH-Funktion gibt XMATCH eine relative Position in einer Liste zurück. Sie kennen jetzt XLOOKUP, das die alte Funktion VLOOKUP ersetzt, und wissen, dass XLOOKUP mit zusätzlichen Funktionen ausgestattet ist. Diese kommen in Form von neuen Bedingungen in der Formelsyntax wie Suchmodus und Übereinstimmungstypen. Nun, XMATCH hat auch diese zusätzliche Leistung gegenüber seinem Vorgänger MATCH.
Die Syntax für XMATCH lautet
XMATCH (Lookup Value, Lookup Array, ,)
Wobei
Lookup Value der Wert ist, den Sie suchen, um die relative Position zu finden
Lookup Array ist die Zeile oder Spalte, die den Lookup Value enthält
Der Übereinstimmungsmodus ist optional. Anders als bei der alten MATCH-Funktion ist die Voreinstellung eine exakte Übereinstimmung. Sie können auch wählen zwischen
- Exakte Übereinstimmung oder nächstkleinere
- Exakte Übereinstimmung oder nächstgrößere
- Wildcard-Übereinstimmung
Der Suchmodus ist ebenfalls optional. Die Standardeinstellung (und einzige Option in der alten MATCH-Funktion) ist die Suche von oben nach unten. Sie können auch die Suche vom letzten zum ersten und die binäre Suche auswählen. Wenn Sie mit binären Suchen arbeiten. Die Option der Platzhalterübereinstimmung funktioniert nicht.
Mit XMATCH können wir entweder dynamische Arrays oder Zellbezüge verwenden, um die Formel zu erstellen, genau wie wir es bei MATCH gesehen haben. Für dieses Beispiel werden wir dynamische Arrays verwenden. Die Formel ist der von MATCH sehr ähnlich, mit der Ausnahme, dass wir nicht 0 für eine exakte Übereinstimmung auswählen müssen, da dies in XMATCH die Standardeinstellung ist. Diesmal wollen wir die Dinge etwas anders angehen und nach Elementen in Liste 2 und nicht in Liste 1 suchen.
In diesem Fall können wir die Formel
=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))
verwenden, wobei „not“ aus „true“ ein „false“ und aus „false“ ein „tru“ macht.
Tabellen – Vergleichen von Listen in Excel, bei denen sich die Bereichsgrößen ändern können
In jeder der bisher betrachteten Formeln haben wir in unseren Abgleichsfunktionen einen Bereich von Zellen ausgewählt, der nicht dynamisch ist. Das heißt, wenn wir einer der Listen neue Daten hinzufügen, müssen wir unsere Formel manuell aktualisieren, um die neuen Daten einzubeziehen.
Um die Listen in Tabellen zu konvertieren, markieren Sie eine der Listen und drücken Sie STRG. Dies ist das Tastaturkürzel für die Umwandlung in eine Tabelle. Wenn Sie die Kopfzeile im Zellenbereich ausgewählt haben, stellen Sie sicher, dass Sie das Kästchen ankreuzen, um zu bestätigen, dass Ihre Tabelle Kopfzeilen hat.
Tabellen verwenden von Natur aus strukturierte Bezeichnungen. Wenn Sie eine Formel schreiben und eine Spalte aus einer Tabelle auswählen, werden daher keine Zellbezüge angezeigt, sondern der Spaltenname.
Betrachten wir unsere vorherige Formel, die XMATCH verwendet, um Elemente in Liste 2 zu finden, die nicht in Liste 1 enthalten sind, können wir diese Funktion jetzt unter Verwendung unserer Tabellenreferenzen neu schreiben
=NOT (ISNUMBER(XMATCH(list2,list1)))
Nun, da wir Tabellen verwendet haben, wird, wenn wir eine neue Zeile zu einer der Tabellen hinzufügen, unser Überlaufbereich ebenfalls erweitert, um die neuen Daten aufzunehmen.
Unterschiede in Listen mit benutzerdefinierter bedingter Formatierung hervorheben
Zuvor haben wir uns in diesem Artikel eine sehr schnelle Möglichkeit angesehen, diese beiden Listen mithilfe einer vordefinierten Regel für Duplikate zu vergleichen. Wir können jedoch auch die benutzerdefinierte bedingte Formatierung verwenden. Wenn Sie mit der benutzerdefinierten bedingten Formatierung nicht vertraut sind, sollten Sie sich diesen Artikel ansehen: Excel Dynamische bedingte Formatierung Tricks:
Wir müssen uns hier zwei verschiedene Ansätze ansehen, um die Unterschiede hervorzuheben. Wenn wir keine Tabellen verwenden und eine Wahr/Falsch-Formel erstellt haben, um die Unterschiede zu ermitteln, können wir eine Kopie der Formel nehmen und diese zu unserer benutzerdefinierten Formatierung hinzufügen. Bei Tabellen müssen wir jedoch die Verwendung von Zellverweisen erzwingen.
Kopieren Sie die Formel in die benutzerdefinierte bedingte Formatierung
Starten Sie mit einer Kopie der Formel. Da wir die Formel in der Tabellenkalkulation getestet haben, können wir sehen, dass sie funktioniert, bevor wir sie in der bedingten Formatierung verwenden. Dies ist die beste Vorgehensweise, da es bei relativen und absoluten Zellbezügen oft schwierig sein kann, die Formel korrekt einzugeben.
Markieren Sie die Zellen, auf die Sie die benutzerdefinierte Formatierung anwenden möchten. Wählen Sie dann im Menüband „Start“ die Dropdown-Liste „Bedingte Formatierung“ und wählen Sie „Neue Regel“
Das Feld „Neue Formatierungsregel“ wird geöffnet, und wählen Sie „Formel verwenden“, um die zu formatierenden Zellen zu bestimmen. Fügen Sie dann die Formel ein und legen Sie den Formatierungstyp fest
Damit werden alle Zellen in beiden Listen in dem von Ihnen gewählten Format formatiert.
Denken Sie daran, dass wir einen Bereich von Zellen ausgewählt haben, um diese bedingte Formatierung anzuwenden, und dass sie nicht dynamisch ist. Würden wir Tabellen verwenden, würde sich dies aktualisieren, ohne dass wir etwas ändern müssten!
Andere Formeln zum Vergleich zweier Listen in Excel
Es gibt viele Formeln, die Sie zum Vergleich zweier Listen in Excel verwenden können. Wir haben uns bereits MATCH und XMATCH angesehen, aber jetzt werden wir uns noch ein paar weitere ansehen. Jede der Lookup-Funktionen wird zusammen mit einigen anderen funktionieren!
VLOOKUP zum Vergleich zweier Listen in Excel
Wenn Sie mit VLOOKUP nicht vertraut sind, können Sie hier darüber lesen. Einfach gesagt gibt VLOOKUP einen entsprechenden Wert aus einer Zelle zurück, wenn es keinen entsprechenden Wert gibt, wird ein #N/A-Fehler zurückgegeben. In unserem Beispiel arbeiten wir mit Text. Wir können also ein VLOOKUP durchführen und testen, ob es Text zurückgibt. Wenn wir mit Zahlen arbeiten würden, könnten wir ISTEXT durch ISNUMBER ersetzen.
Wir könnten die Funktion =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
Oder wenn wir dynamische Arrays in Excel 365 verwenden würden, könnten wir die Funktion
=ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP, um zwei Listen in Excel zu vergleichen
XLOOKUP wurde in Excel 365 eingeführt und Sie können hier mehr darüber erfahren. Ähnlich wie VLOOKUP gibt XLOOKUP einen entsprechenden Wert aus einer Zelle zurück, und Sie können ein Ergebnis definieren, wenn der Wert nicht gefunden wird. Bei Verwendung von dynamischen Arrays wäre die Funktion
=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
COUNTIF, um zwei Listen in Excel zu vergleichen
Die Funktion COUNTIF zählt, wie oft ein Wert oder Text innerhalb eines Bereichs enthalten ist. Wenn der Wert nicht gefunden wird, wird 0 zurückgegeben. Wir können dies mit einer WENN-Anweisung kombinieren, um unsere wahren und falschen Werte zurückzugeben.
=IF(COUNTIF (A2:A21,C2:C12)<>0, „True“, „False“)
Wie man 2 Datensätze in Excel vergleicht
Der Vergleich zweier Listen ist einfach genug, und wir haben uns jetzt mehrere Möglichkeiten angesehen, dies zu tun. Aber der Vergleich zweier Datensätze kann etwas schwieriger sein.
Betrachten wir ein Beispiel. Wir haben zwei Datentabellen, die jeweils die gleichen Spaltenüberschriften enthalten. Ein Blick auf das Bild zeigt, dass der Abgleich dieser beiden Tabellen die Betrachtung von mehr als einer Spalte erfordert.
Wenn Sie mehr als eine Spalte betrachten müssen, besteht die Lösung darin, eine zusammengesetzte Spalte zu erstellen, die die Daten in einer Spalte kombiniert. Auf diese Weise wird für jede Zeile eine eindeutige Spalte erstellt, die dann als übereinstimmende Spalte verwendet werden kann
In diesem Beispiel könnten wir Name und DoB kombinieren, um jeder Tabelle einen eindeutigen Bezeichner zu geben
Es gibt viele Möglichkeiten, den Inhalt einer Zelle zu verbinden, in diesem Fall werden wir eine einfache Verkettung vornehmen. Da wir Tabellen verwenden, wird die Formel das Tabellenbenennungsformat übernehmen.
= &“-„&
Wiederholen Sie die Schritte für die zweite Tabelle.
Jetzt können wir jedes der oben genannten Beispiele verwenden, um diese beiden neuen Datenspalten abzugleichen. Wenn sie übereinstimmen, wissen wir, dass sie mit der gesamten Zeile übereinstimmen.
Vergleichen Sie Listen oder Datensätze mit Power Query
Sie können auch Listen und Datensätze mit Excels Power Query vergleichen. Durch Verbinden mit den Tabellen und anschließendes Zusammenführen der Tabellen unter Verwendung verschiedener Join-Typen können wir beide Listen vergleichen.
In diesem Video lernen Sie, wie Sie zwei verschiedene Datensätze mit Excels Power Query vergleichen oder abgleichen können
Zu diesem Video und zur Übung gibt es einen Datensatz, den Sie aus diesem Artikel entnehmen können.