Inhalte
Das Lesen dieses Artikels dauert etwa 10 Minuten. In den nächsten 5 Minuten können Sie ganz einfach zwei Spalten in Excel vergleichen und herausfinden, ob sich Duplikate darin befinden, diese löschen oder farblich hervorheben. Es ist also soweit!
Excel ist eine sehr mächtige und wirklich coole Anwendung zum Erstellen und Verarbeiten großer Datenmengen. Wenn Sie mehrere Arbeitsmappen mit Daten (oder nur eine riesige Tabelle) haben, möchten Sie wahrscheinlich 2 Spalten vergleichen, doppelte Werte finden und dann etwas damit machen, z. B. den Inhalt löschen, hervorheben oder löschen . Spalten können sich in derselben Tabelle befinden, benachbart oder nicht benachbart sein, können sich auf 2 verschiedenen Blättern oder sogar in verschiedenen Büchern befinden.
Stellen Sie sich vor, wir haben 2 Spalten mit den Namen von Personen – 5 Namen pro Spalte A und 3 Namen in einer Spalte B. Sie müssen die Namen in diesen beiden Spalten vergleichen und Duplikate finden. Wie Sie verstehen, handelt es sich um fiktive Daten, die lediglich als Beispiel dienen. In echten Tabellen haben wir es mit Tausenden oder sogar Zehntausenden von Datensätzen zu tun.
Option A: Beide Spalten befinden sich auf demselben Blatt. Zum Beispiel eine Spalte A und Spalte B.
Option B: Die Spalten befinden sich auf verschiedenen Blättern. Zum Beispiel eine Spalte A auf dem Blatt Sheet2 und Spalte A auf dem Blatt Sheet3.
Excel 2013, 2010 und 2007 haben ein eingebautes Tool Duplikate entfernen (Duplikate entfernen), aber es ist in dieser Situation machtlos, da es keine Daten in 2 Spalten vergleichen kann. Außerdem kann es nur Duplikate entfernen. Weitere Optionen wie Hervorheben oder Farbwechsel gibt es nicht. Und Punkt!
Als Nächstes zeige ich Ihnen die Möglichkeiten, zwei Spalten in Excel zu vergleichen, wodurch Sie doppelte Datensätze finden und entfernen können.
Vergleichen Sie 2 Spalten in Excel und finden Sie doppelte Einträge mithilfe von Formeln
Option A: Beide Spalten befinden sich auf demselben Blatt
- In die erste leere Zelle (in unserem Beispiel ist das die Zelle C1) schreiben wir folgende Formel:
=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")
In unserer Formel A1 Dies ist die erste Zelle der ersten Spalte, die wir vergleichen werden. $B$1 и $B$10000 das sind die Adressen der ersten und letzten Zelle der zweiten Spalte, mit denen wir den Vergleich durchführen. Beachten Sie die absoluten Bezüge – Spaltenbuchstaben und Zeilennummern wird ein Dollarzeichen ($) vorangestellt. Ich verwende absolute Bezüge, damit die Zelladressen beim Kopieren von Formeln gleich bleiben.
Wenn Sie Duplikate in einer Spalte finden möchten B, ändern Sie die Referenzen so, dass die Formel wie folgt aussieht:
=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")Stattdessen "Nur" und "Duplikat» Sie können Ihre eigenen Etiketten schreiben, zum Beispiel «Nicht gefunden" und "Gefunden“, oder lass nur “Duplikat' und geben Sie anstelle des zweiten Werts ein Leerzeichen ein. Im letzteren Fall bleiben die Zellen, für die keine Duplikate gefunden werden, leer, und ich glaube, dass diese Darstellung der Daten für die weitere Analyse am bequemsten ist.
- Kopieren wir nun unsere Formel in alle Zellen der Spalte C, bis hinunter zur untersten Zeile, die die Daten in der Spalte enthält A. Bewegen Sie dazu den Mauszeiger in die untere rechte Ecke der Zelle C1, nimmt der Zeiger die Form eines schwarzen Fadenkreuzes an, wie im Bild unten gezeigt:Klicken und halten Sie die linke Maustaste gedrückt und ziehen Sie den Rand des Rahmens nach unten, um alle Zellen hervorzuheben, in die Sie die Formel einfügen möchten. Wenn alle erforderlichen Zellen ausgewählt sind, lassen Sie die Maustaste los:
Klicken und halten Sie die linke Maustaste gedrückt und ziehen Sie den Rand des Rahmens nach unten, um alle Zellen hervorzuheben, in die Sie die Formel einfügen möchten. Wenn alle erforderlichen Zellen ausgewählt sind, lassen Sie die Maustaste los:
TIPP: In großen Tabellen geht das Kopieren der Formel schneller, wenn Sie Tastaturkürzel verwenden. Markieren Sie eine Zelle C1 und drücken Sie Ctrl + C (um die Formel in die Zwischenablage zu kopieren), dann klicken Sie auf Strg + Umschalt + Ende (um alle nicht leeren Zellen in Spalte C auszuwählen) und drücken Sie schließlich Strg + V (um die Formel in alle ausgewählten Zellen einzufügen).
- Toll, jetzt werden alle doppelten Werte als „Duplikat„:
Option B: Zwei Spalten befinden sich auf verschiedenen Blättern (in verschiedenen Arbeitsmappen)
- In der ersten Zelle der ersten leeren Spalte auf dem Arbeitsblatt Sheet2 (in unserem Fall ist es Spalte B) geben Sie die folgende Formel ein:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")Hier Sheet3 ist der Name des Blattes, auf dem sich die 2. Spalte befindet, und $ A $ 1: $ A $ 10000 sind Zelladressen von der ersten bis zur letzten in dieser zweiten Spalte.
- Kopieren Sie die Formel in alle Zellen einer Spalte B (wie Variante A).
- Wir erhalten dieses Ergebnis:
Verarbeitung gefundener Duplikate
Toll, wir haben Einträge in der ersten Spalte gefunden, die auch in der zweiten Spalte vorhanden sind. Jetzt müssen wir etwas mit ihnen machen. Das manuelle Durchsuchen aller doppelten Datensätze in einer Tabelle ist ziemlich ineffizient und nimmt zu viel Zeit in Anspruch. Es gibt bessere Wege.
Nur doppelte Zeilen in Spalte A anzeigen
Wenn Ihre Spalten keine Überschriften haben, müssen Sie sie hinzufügen. Platzieren Sie dazu den Cursor auf der Zahl, die die erste Zeile darstellt, und sie verwandelt sich in einen schwarzen Pfeil, wie in der folgenden Abbildung gezeigt:
Klicken Sie mit der rechten Maustaste und wählen Sie aus dem Kontextmenü Einfügen (Einfügung):
Geben Sie den Spalten Namen, zum Beispiel „Name und Vorname" und "Duplikat?» Öffnen Sie dann den Reiter Datum (Daten) und drücken Sie Filter (Filter):
Klicken Sie danach auf den kleinen grauen Pfeil neben „Duplikat?« um das Filtermenü zu öffnen; deaktivieren Sie alle Elemente in dieser Liste außer Duplikat, und drücke OK.
Das ist alles, jetzt sehen Sie nur noch diese Elemente der Spalte А, die in der Spalte dupliziert werden В. In unserer Trainingstabelle gibt es nur zwei solcher Zellen, aber wie Sie verstehen, wird es in der Praxis viel mehr davon geben.
Alle Zeilen einer Spalte wieder anzeigen А, klicken Sie auf das Filtersymbol in der Spalte В, das jetzt wie ein Trichter mit einem kleinen Pfeil aussieht, und wählen Sie aus Wählen Sie alle (Wählen Sie Alle). Oder Sie können dasselbe über die Multifunktionsleiste tun, indem Sie auf klicken Datum (Daten) > Auswählen und filtern (Sortieren & Filtern) > Auswahl aufheben (Klar) wie im Screenshot unten gezeigt:
Ändern Sie die Farbe oder markieren Sie gefundene Duplikate
Wenn die Hinweise „Duplikat“ reicht für Ihre Zwecke nicht aus und Sie möchten doppelte Zellen mit einer anderen Schriftfarbe, Füllfarbe oder einer anderen Methode markieren …
Filtern Sie in diesem Fall die Duplikate wie oben gezeigt, wählen Sie alle gefilterten Zellen aus und klicken Sie auf Strg + 1um den Dialog zu öffnen Format Cells (Zellformat). Als Beispiel ändern wir die Füllfarbe von Zellen in Zeilen mit Duplikaten in helles Gelb. Natürlich können Sie die Füllfarbe mit dem Werkzeug ändern Füllen (Füllfarbe) Registerkarte Startseite (Home) aber Dialogfeld Vorteil Format Cells (Zellformat), indem Sie alle Formatierungsoptionen gleichzeitig konfigurieren können.
Jetzt werden Sie definitiv keine Zellen mit Duplikaten verpassen:
Doppelte Werte aus der ersten Spalte entfernen
Filtern Sie die Tabelle so, dass nur Zellen mit doppelten Werten angezeigt werden, und wählen Sie diese Zellen aus.
Wenn sich die 2 Spalten, die Sie vergleichen, auf verschiedenen Blättern befinden, d. h. in verschiedenen Tabellen, klicken Sie mit der rechten Maustaste auf den ausgewählten Bereich, und wählen Sie aus Zeile löschen (Zeile entfernen):
Öffentlichkeitsarbeit/Presse OKwenn Excel Sie auffordert, zu bestätigen, dass Sie wirklich die gesamte Blattzeile löschen möchten, und dann den Filter zu löschen. Wie Sie sehen, bleiben nur Zeilen mit eindeutigen Werten übrig:
Wenn sich 2 Spalten auf demselben Blatt befinden, nahe beieinander (benachbart) oder nicht nahe beieinander (nicht benachbart), dann wird das Entfernen von Duplikaten etwas komplizierter. Wir können nicht die gesamte Zeile mit doppelten Werten entfernen, da dadurch auch die Zellen aus der zweiten Spalte entfernt werden. Also nur eindeutige Einträge in einer Spalte zu belassen А, mach das:
- Filtern Sie die Tabelle, um nur doppelte Werte anzuzeigen, und wählen Sie diese Zellen aus. Klicken Sie mit der rechten Maustaste darauf und wählen Sie aus dem Kontextmenü Klarer Inhalt (Inhalt löschen).
- Reinigen Sie den Filter.
- Wählen Sie alle Zellen in einer Spalte aus А, ausgehend von der Zelle A1 bis ganz nach unten mit den Daten.
- Klicken Sie auf die Datum (Daten) und drücken Sie Sortieren von A bis Z (Sortieren von A bis Z). Wählen Sie im sich öffnenden Dialogfeld aus Fahren Sie mit der aktuellen Auswahl fort (Innerhalb der angegebenen Auswahl sortieren) und auf die Schaltfläche klicken Schwarz (Sortierung):
- Lösche die Spalte mit der Formel, du brauchst sie nicht mehr, ab jetzt hast du nur noch eindeutige Werte.
- Das ist es, jetzt die Spalte А enthält nur eindeutige Daten, die nicht in der Spalte enthalten sind В:
Wie Sie sehen können, ist das Entfernen von Duplikaten aus zwei Spalten in Excel mithilfe von Formeln nicht so schwierig.