So vergleichen Sie zwei Excel-Tabellen auf Duplikate: 5 schnelle Möglichkeiten

Bei der Arbeit mit großen Datensätzen in Excel kommt es häufig vor, dass mehrere Arbeitsblätter ähnliche Daten enthalten. Der Vergleich dieser Blätter auf doppelte Datensätze kann eine ziemliche Herausforderung sein, insbesondere wenn die Daten über verschiedene Spalten und Zeilen verstreut sind.

Die gebräuchlichsten Methoden zum Auffinden von Duplikaten in zwei Excel-Tabellen sind:

1. VLOOKUP-, COUNTIF- oder EXACT-Funktionen

2. Bedingte Formatierung

3. Power-Abfrage

4. Externe Tools und Add-Ins

5. Visuelle Kontrollen auf Duplikate

In diesem Artikel werden diese fünf Methoden Schritt für Schritt erläutert, um Duplikate in mehreren Arbeitsblättern zu lokalisieren und zu verarbeiten. Am Ende werden Sie sehen, wie einfach es ist, doppelte Werte zu verwalten.

Lass uns anfangen!

Inhaltsverzeichnis

• Beispielarbeitsblätter und Daten
• 1. Verwenden der VLOOKUP-, COUNTIF- oder EXACT-Funktionen zum Suchen von Duplikaten
  • A. Wie verwenden Sie die SVERWEIS-Funktion, um Duplikate in zwei Blättern zu finden?
    • Was ist, wenn Sie verschiedene Arbeitsmappen bearbeiten?
  • B. Wie verwenden Sie die ZÄHLENWENN-Funktion, um Duplikate in Arbeitsblättern zu finden?
  • C. Wie verwenden Sie die EXACT-Funktion, um Duplikate in Arbeitsblättern zu finden?
• 2. So verwenden Sie die bedingte Formatierung für doppelte Zeilen
  • So verwenden Sie den Manager für bedingte Formatierungsregeln
• 3. So verwenden Sie Power Query, um Duplikate in Arbeitsblättern zu finden
• 4. Tools und Add-Ins zum Identifizieren von Duplikaten in Arbeitsblättern
• 5. So überprüfen Sie zwei Blätter visuell auf Duplikate
• 3 Tipps zum Vorbereiten Ihrer Excel-Arbeitsblätter
• Umgang mit Fehlern und Inkonsistenzen
• Abschließende Gedanken

Beispielarbeitsblätter und Daten

Um den Beispielen in diesem Artikel zu folgen, erstellen Sie eine neue Excel-Arbeitsmappe mit diesen Daten in Spalte A des ersten Blatts:

• Apfel

• Orange

• Birne

• Erdbeere

Tragen Sie diese Daten in Spalte A des zweiten Blattes ein:

• Birne

• Erdbeere

• Apfel

• Ananas

Ihre Arbeitsblätter sehen so aus:

Sobald Sie Ihre Arbeitsblätter eingerichtet haben, können wir nun die verschiedenen Möglichkeiten durchgehen, Duplikate in zwei Blättern zu finden, beginnend mit den VLOOKUP-, COUNTIF- und EXACT-Funktionen.

Lass uns gehen!

1. Verwenden der VLOOKUP-, COUNTIF- oder EXACT-Funktionen zum Suchen von Duplikaten

Excel verfügt über drei integrierte Funktionen, die das Auffinden von Duplikaten zum Kinderspiel machen: VLOOKUP , COUNTIF und EXACT .

Diese Funktionen sollen Ihnen dabei helfen, Daten in Ihren Tabellenkalkulationen zu finden, zu zählen und zu vergleichen, was sie zu idealen Werkzeugen zum Auffinden doppelter Einträge macht.

Jede Funktion dient einem einzigartigen Zweck und wenn Sie verstehen, wie Sie sie verwenden, können Sie Ihren Datenanalyseprozess erheblich optimieren.

In den folgenden Abschnitten erfahren Sie, wie Sie die Funktionen VLOOKUP, COUNTIF und EXACT verwenden, um Duplikate in Ihren Excel-Arbeitsblättern effektiv zu identifizieren.

A. Wie verwenden Sie die SVERWEIS-Funktion, um Duplikate in zwei Blättern zu finden?

VLOOKUP steht für Vertical Lookup. Mit dieser Funktion können Sie doppelte Werte zwischen zwei Spalten finden. Dies ist die Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value : Der Wert, nach dem Sie in der ersten Spalte des table_array suchen möchten.

• table_array : Der Zellbereich, der die Daten enthält, in denen Sie suchen möchten.

• col_index_num : Die Spaltennummer im table_array, aus der Sie den Wert zurückgeben möchten.

• range_lookup : Optional. Es ist entweder WAHR (ungefähre Übereinstimmung) oder FALSCH (genaue Übereinstimmung). Der Standardwert ist TRUE.

Um die Funktion über zwei Arbeitsblätter in einer Excel-Datei hinweg zu verwenden, müssen Sie wissen, wie Sie in einer Formel auf ein separates Blatt verweisen. Geben Sie dazu den Blattnamen gefolgt vom Ausrufezeichen (!) und Ihrer Zelle oder Ihrem Zellbereich ein.

Dies verweist beispielsweise auf die Zellen A2 bis A5 in Blatt 2 derselben Arbeitsmappe: Blatt 2!$A$2:$A$5.

Gehen Sie folgendermaßen vor, um die SVERWEIS-Funktion in Ihrer Beispieltabelle zu verwenden:

1. Wählen Sie Zelle B2 aus, um das erste Vergleichsergebnis anzuzeigen.

2. Geben Sie diese Formel ein: =VLOOKUP(A2,Sheet2!$A$2:$A$5, 1, FALSE).

3. Drücken Sie die Eingabetaste, um das Vergleichsergebnis anzuzeigen.

4. Füllen Sie die Formel aus, um die Werte für die restlichen Zeilen im ersten Blatt zu vergleichen.

Die Ergebnisse sehen wie im Bild unten aus:

Sie können die Anzeige aufräumen, indem Sie statt einer Fehlermeldung eine benutzerfreundliche Meldung anzeigen, wenn kein Duplikat gefunden wird.

Diese Formel zeigt beispielsweise „Ja“ oder „Nein“ für gefundene bzw. nicht gefundene Werte an:

=IF(ISNA(SVERWEIS(A2, Blatt 2!$A$2:$A$5, 1, FALSE)), „Nein“, „Ja“)

Weitere Beispiele für diese Funktion finden Sie in unserem Artikel über die Verwendung von VLOOKUP zum Vergleichen zweier Spalten .

Was ist, wenn Sie verschiedene Arbeitsmappen bearbeiten?

Wenn sich Ihre Arbeitsblätter in separaten Arbeitsmappen befinden, ist die Funktionsverwendung bei zwei Excel-Dateien dieselbe. Allerdings ist die Bezugnahme auf das zweite Arbeitsblatt etwas komplexer.

Du brauchst:

• Schließen Sie den Namen der Excel-Arbeitsmappe in Klammern ein

• Geben Sie anschließend den Namen des Arbeitsblatts ein

• Setzen Sie die Arbeitsmappe und das Arbeitsblatt in Anführungszeichen.

Wenn sich die Zellen beispielsweise in einem Blatt mit dem Namen Sheet2 in einer Arbeitsmappe mit dem Namen „WB 2.xlsx“ befinden, sieht das Format wie folgt aus:

'[WB 2.xlsx]Sheet2'!$A$2:$A$5

Schließen Sie die zweite Arbeitsmappe, bevor Sie die Formel eingeben. Andernfalls erhalten Sie eine Fehlermeldung.

B. Wie verwenden Sie die ZÄHLENWENN-Funktion, um Duplikate in Arbeitsblättern zu finden?

Die ZÄHLENWENN-Funktion in Excel wird verwendet, um die Anzahl der Zellen innerhalb eines angegebenen Bereichs zu zählen, die ein bestimmtes Kriterium erfüllen.

Um mehrere Blätter zu vergleichen, können Sie die Anzahl der Zellen im zweiten Arbeitsblatt zählen, die mit einer Zelle im ersten Arbeitsblatt übereinstimmen.

Dies ist die Syntax der Funktion:

=COUNTIF(Bereich, Kriterien)

• Bereich : der Zellbereich, den Sie basierend auf den angegebenen Kriterien zählen möchten.

• Kriterien : Die Bedingung, die erfüllt sein muss, damit eine Zelle gezählt wird.

Um die Funktion mit den Beispieldaten zu verwenden, gehen Sie folgendermaßen vor:

1. Wählen Sie Zelle B2 aus, um das erste Vergleichsergebnis anzuzeigen.

2. Geben Sie diese Formel ein: =COUNTIF(Sheet2!$A$2:$A$5, A2)

3. Drücken Sie die Eingabetaste, um das Vergleichsergebnis anzuzeigen.

4. Füllen Sie die Formel aus, um die Werte für die restlichen Zeilen im ersten Blatt zu vergleichen.

Die Funktion findet für einige Zellen eine Übereinstimmung und für andere keine. Die Vergleichszelle zeigt die Anzahl an. Hier ist das Ergebnis aus den Beispieldaten:

Die ZÄHLENWENN-Funktion kann für andere nützliche Aufgaben wie das Zählen nicht leerer Zellen in Excel verwendet werden .

C. Wie verwenden Sie die EXACT-Funktion, um Duplikate in Arbeitsblättern zu finden?

Die EXACT-Funktion in Excel kann auch verwendet werden, um in zwei verschiedenen Excel-Arbeitsblättern nach Duplikaten innerhalb derselben Zellen zu suchen. Die Syntax lautet:

=GENAU(text1, text2)

• text1 ist die erste Textzeichenfolge, die Sie vergleichen möchten.

• text2 ist die zweite Textzeichenfolge, die Sie vergleichen möchten.

Folge diesen Schritten:

1. Wählen Sie Zelle B2 aus.

2. Geben Sie die Formel =EXACT(A2, Sheet2!A2) ein.

3. Drücken Sie die Eingabetaste, um das Vergleichsergebnis anzuzeigen. Die Formel gibt TRUE zurück, wenn beide Werte identisch sind, andernfalls FALSE.

4. Füllen Sie die Formel aus, um die Werte für die restlichen Zeilen im ersten Blatt zu vergleichen.

Note that this method doesn’t search for duplicates across a cell range. Instead, you are only looking for matches based on the same cell in a different sheet.

This can be useful with ordered data where you only expect a few exceptions.

The VLOOKUP, COUNTIF, and EXACT functions are useful for finding duplicates, but Excel is a versatile program, and there are other options. In the next section, we look at how you can use conditional formatting to identify duplicates in two sheets.

2. How to Use Conditional Formatting for Duplicate Rows

In this section, you’ll learn how to use conditional formatting to find and highlight duplicate rows in two Excel worksheets.

To create a conditional formatting rule, follow these steps:

1. Select the range of cells containing the data (A2:A5 in this case).

2. Click on the “Home” tab in the Excel ribbon.

3. Klicken Sie in der Gruppe „Stile“ auf „Bedingte Formatierung“.

4. Wählen Sie „Neue Regel“ aus dem Dropdown-Menü.

Die nächste Aufgabe besteht darin, eine Formel für die Verwendung Ihrer Regel bereitzustellen. Folge diesen Schritten:

1. Wählen Sie im Dialogfeld „Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen“.

2. Geben Sie die folgende Formel ein: =COUNTIF(Sheet2!$A$2:$A$5, A2) > 0

Abschließend wenden Sie die gewünschte Formatierung für doppelte Zellen an.

1. Klicken Sie auf die Schaltfläche „Formatieren“, um das Dialogfeld „Zellen formatieren“ zu öffnen.

2. Wählen Sie ein Format, z. B. füllen Sie Duplikate mit einer gelben Hintergrundfarbe.

3. OK klicken.

Ihre doppelten Daten werden jetzt gelb hervorgehoben.

So verwenden Sie den Manager für bedingte Formatierungsregeln

Nachdem Sie die bedingte Formatierungsregel erstellt haben, können Sie sie mit dem Manager für bedingte Formatierungsregeln verwalten.

So greifen Sie auf den Manager zu:

1. Gehen Sie zur Registerkarte „Startseite“.

2. Klicken Sie auf Bedingte Formatierung.

3. Wählen Sie „Regeln verwalten“.

Sie sehen eine Liste aller bedingten Formatierungsregeln, die auf das ausgewählte Blatt angewendet werden. Sie können Regeln bearbeiten, löschen oder ihre Reihenfolge ändern, indem Sie die Regel auswählen und auf die entsprechenden Schaltflächen klicken.

Um dieselbe Regel auf das andere Blatt anzuwenden, gehen Sie folgendermaßen vor:

1. Wählen Sie im zweiten Blatt den Bereich aus, den Sie vergleichen möchten.

2. Gehen Sie zum Manager für bedingte Formatierungsregeln.

3. Wählen Sie die Regel aus, klicken Sie auf Regel duplizieren und dann auf Regel bearbeiten.

4. Ersetzen Sie „Blatt2“ durch den Namen des ersten zu vergleichenden Blattes.

Nachdem Sie nun die bedingte Formatierungsregel auf beide Blätter angewendet haben, werden Duplikate entsprechend der von Ihnen gewählten Formatierung hervorgehoben.

Stellen Sie sicher, dass Sie den Bereich und die Zellbezüge in den Formeln nach Bedarf anpassen, um alle Daten abzudecken, die Sie vergleichen möchten.

Bedingte Formatierung mag etwas primitiv erscheinen. Wenn Sie eine genauere Kontrolle wünschen, ist Power Query möglicherweise die Antwort! Im nächsten Abschnitt erfahren Sie, wie Sie mit Power Query Duplikate finden können.

3. So verwenden Sie Power Query, um Duplikate in Arbeitsblättern zu finden

Power Query ist ein Datentransformations- und Datenaufbereitungstool in Microsoft Excel. Die Identifizierung derselben Werte ist nur eine der vielen Analyseaufgaben, die Sie mit dem Tool durchführen können.

Dazu sollten Sie zunächst die Daten der beiden Arbeitsblätter in separate Tabellen importieren. Befolgen Sie diese Schritte in jedem Blatt:

1. Klicken Sie mit der rechten Maustaste auf den Zellbereich.

2. Wählen Sie „Daten aus Tabelle/Bereich abrufen“.

3. Ändern Sie den Tabellennamen in einen geeigneten Namen.

Nach dem Import beider Blätter besteht die erste Aufgabe darin, die Daten zusammenzuführen:

1. Gehen Sie zur Registerkarte Daten.

2. Klicken Sie auf „Daten abrufen“.

3. Wählen Sie „Abfragen kombinieren“.

4. Wählen Sie „Zusammenführen“ und wählen Sie die beiden Tabellen aus.

5. Klicken Sie auf die beiden Schlüsselspalten.

6. Wählen Sie „Inner“ als „Join-Art“ und klicken Sie auf „OK“.

Der Power Query-Editor wird mit den kombinierten Daten aus beiden Tabellen in Ihrer Excel-Tabelle geöffnet. Sie sehen zwei Spalten, eine aus jeder Tabelle. Da Sie nur an den doppelten Werten interessiert sind, können Sie die zweite Spalte entfernen.

Sie können im Power Query-Editor auf „Schließen und Laden“ klicken, um die Duplikate in ein neues Arbeitsblatt zu laden.

Um weitere Aspekte dieser leistungsstarken Funktion zu erkunden, folgen Sie den Beispielen in unserem Artikel zur Verwendung von Power Query in Excel .

Excel verfügt auch über Tools und Add-Ins von Drittanbietern, die die Möglichkeit bieten, Duplikate nahtlos zu finden. Schauen wir uns daher im nächsten Abschnitt einige dieser Tools an.

4. Tools und Add-Ins zum Identifizieren von Duplikaten in Arbeitsblättern

Externe Tools und Add-Ins können erweiterte Funktionen bieten, die in nativen Excel-Funktionen möglicherweise nicht verfügbar sind. Diese Tools können den Prozess des Vergleichs von Blättern auf Duplikate weiter rationalisieren.

Spreadsheet Compare ist ein Microsoft-Tool, mit dem Sie zwei Arbeitsmappen nebeneinander vergleichen, Unterschiede hervorheben und Duplikate leicht identifizieren können. Sie können es von der Microsoft-Website herunterladen .

Es gibt mehrere Add-Ins, die Sie installieren können, um die Suche nach Duplikaten zu automatisieren. Ein Beispiel ist „ Duplicate Remover “. So installieren Sie ein Add-In:

1. Gehen Sie zur Registerkarte Einfügen.

2. Klicken Sie auf „Add-In abrufen“.

3. Suchen Sie nach „Duplikat“.

4. Klicken Sie beim Tool Ihrer Wahl auf „Hinzufügen“.

5. So überprüfen Sie zwei Blätter visuell auf Duplikate

Wenn alles andere fehlschlägt, nutzen Sie Ihre Augen! Mit dem Dialogfeld „Fenster anordnen“ in Excel können Sie mehrere Arbeitsblätter oder Arbeitsmappen nebeneinander anzeigen.

Obwohl Duplikate nicht direkt gefunden werden, kann es Ihnen dabei helfen, Daten in Arbeitsblättern oder Arbeitsmappen visuell zu vergleichen, um Duplikate zu erkennen. Folge diesen Schritten:

1. Klicken Sie im Excel-Menüband auf die Registerkarte „Ansicht“.

2. Klicken Sie in der Gruppe „Fenster“ auf „Alle anordnen“.

3. Wählen Sie eine Anordnungsoption, z. B. „Vertikal“ oder „Horizontal“.

Dadurch werden beide Blätter entweder nebeneinander oder übereinander angezeigt. Jetzt können Sie die Daten in jedem Blatt manuell vergleichen, um Duplikate zu identifizieren.

Sie müssen durch die Daten scrollen und jeden Wert visuell prüfen, um Übereinstimmungen zu finden.

Beachten Sie, dass diese Methode für große Datensätze nicht effizient ist, da sie einen manuellen Vergleich erfordert. Mit den anderen Methoden in diesem Artikel können Sie Duplikate in größeren Datensätzen effektiver finden.

Und das ist die letzte unserer gängigen Methoden zum Auffinden doppelter Werte in Excel-Tabellen! Im nächsten Abschnitt geben wir Ihnen einige Tipps zur Vorbereitung Ihrer Arbeitsblätter.

3 Tipps zum Vorbereiten Ihrer Excel-Arbeitsblätter

Bevor Sie mit dem Vergleich mehrerer Blätter beginnen, stellen Sie sicher, dass die Spalten und Zeilen Ihrer Datensätze richtig ausgerichtet sind.

Stellen Sie sicher, dass beide Excel-Tabellen die gleiche Struktur und die gleichen Kopfzeilennamen haben. Bei Bedarf können Sie die Spalten in beiden Blättern neu anordnen, damit sie zueinander passen.

Hier sind drei Vorschläge, um genaue Vergleiche zu gewährleisten:

1. Ordnen Sie Ihre Daten in beiden Blättern in der gleichen Reihenfolge an. Dies erleichtert die effektive Arbeit von Excel-Funktionen.

2. Normalisieren Sie Ihre Daten, indem Sie einheitliche Formatierungen, Groß- und Kleinschreibung und Datentypen verwenden. Dadurch werden nicht übereinstimmende Einträge aufgrund geringfügiger Unterschiede vermieden.

3. Entfernen Sie unnötige leere Zeilen oder Spalten, da diese den Vergleichsprozess beeinträchtigen können.

Sie können beurteilen, wie viele Duplikate in einem Datensatz vorhanden sind, indem Sie die eindeutigen Werte zählen. In diesem Video werden verschiedene Methoden erläutert:

Umgang mit Fehlern und Inkonsistenzen

Inkonsistenzen in Ihren Daten können den Vergleichsprozess beeinträchtigen. Hier sind vier Tipps zur Lösung von Inkonsistenzen:

1. Suchen Sie nach Diskrepanzen bei den Datentypen, z. B. der Vermischung von Text und numerischen Werten in derselben Spalte.

2. Stellen Sie sicher, dass für Datumsangaben, Zahlen und andere Datentypen eine einheitliche Formatierung verwendet wird.

3. Überprüfen Sie Ihre Daten auf fehlende oder fehlerhafte Eingaben und aktualisieren Sie diese gegebenenfalls.

4. Standardisieren Sie Abkürzungen oder inkonsistente Namenskonventionen in Ihren Datensätzen.

Abschließende Gedanken

Das Auffinden von Duplikaten in zwei Excel-Arbeitsblättern ist eine wesentliche Aufgabe für die Datenverwaltung und -analyse und stellt die Datenintegrität und -genauigkeit sicher. Excel bietet mehrere Techniken zur Identifizierung von Duplikaten, jede mit ihren eigenen Vorteilen und Einschränkungen.

Die Wahl der Methode hängt von den Bedürfnissen des Benutzers, der Größe und Komplexität des Datensatzes und dem gewünschten Ergebnis ab. Für kleinere Datensätze und einfache Vergleiche kann die Verwendung von SVERWEIS , ZÄHLENWENN oder bedingter Formatierung ausreichend sein.

Für größere Datensätze oder komplexere Datentransformationen ist Power Query ein leistungsstarkes und flexibles Tool, das eine Vielzahl von Datenvorbereitungsaufgaben bewältigen kann, einschließlich der Suche nach Duplikaten.

Abschließend lässt sich sagen, dass der Vergleich von Excel-Tabellen auf Duplikate eine äußerst praktische Fähigkeit ist, die Sie in Ihrem Werkzeugkasten haben sollten. Mit den Tricks in diesem Artikel können Sie diese lästigen Duplikate erkennen und Ihre Daten blitzsauber halten.

Vertrauen Sie uns, wenn Sie darin besser werden, werden Sie Ihre Datenaufgaben im Handumdrehen erledigen und alle um Sie herum beeindrucken!