Das doppelte Lottchen, oder: Dublettensuche mit Excel 7

Doppelte und mehrfache Einträge in Tabellen einfach finden.
 

Das Verwalten von langen Listen und Tabellen ist ja eines der Hauptanwendungsgebiete von Excel. Dabei kommt es immer wieder vor, dass eine Tabelle bewußt oder unbewußt doppelte Einträge enthält.

Insbesondere, wenn man Daten aus mehreren Quellen zusammenfassen möchte, kommt es oftmals zu einer unerwünschten Häufung identischer Datensätze.

Im heutigen Artikel stelle ich euch drei Möglichkeiten vor, in Excel diesen Dubletten (Tripletten…) auf die Schliche zu kommen:

  • Duplikate entfernen
  • Spezialfilter
  • Bedingte Formatierung

Und so geht’s:

1. Duplikate entfernen

Wichtiger Hinweis zu Beginn: Diese Funktion entfernt die Duplikate aus deiner Ursprungstabelle, d.h. sie werden tatsächlich gelöscht! Im Zweifelsfall solltest Du also vorher eine Kopie der Daten anfertigen.

Stelle dann den Cursor irgendwo in die Datentabelle, welche die vermeintlichen Dubletten enthält. In der Registerkarte “Daten” befindet sich der Menüpunkt “Duplikate entfernen”, den du nun anklicken kannst:

Duplikate entfernen

Duplikate entfernen

Im folgenden Fenster lässt sich jetzt auswählen, welche Spalten für die Ermittlung der Duplikate herangezogen werden sollen. In den meisten Fällen wirst du hier sämtliche Spalten markieren. Wenn deine Tabelle auch Spaltenüberschriften enthält, solltest du auch den entsprechenden Haken setzen:

Duplikate entfernen (2)

Duplikate entfernen (2)

Mit einem Klick auf “OK” werden alle Duplikate aus deiner Ursprungstabelle entfernt:

Bereinigte Liste

Bereinigte Liste

2. Spezialfilter

Anders als die Funktion “Duplikate entfernen” werden hier die doppelten Einträge nur gefiltert, also ausgeblendet. Diese Funktion befindet sich ebenfalls im Register “Daten”, Schaltfläche “Erweitert”.

Spezialfilter

Spezialfilter

Wenn der Listenbereich noch nicht korrekt markiert war, kannst du es im nachfolgenden Fenster nachholen. Um echte Dubletten zu finden, brauchst du keinen Kriterienbereich anzugeben, es werden dann sämtliche Spalten berücksichtigt. Nun hast du zwei Möglichkeiten:

Möglichkeit 1: Du willst die Daten direkt innerhalb der vorhandenen Tabelle filtern

An gleicher Stelle filtern

An gleicher Stelle filtern

Der wichtigste Haken ist natürlich die Option “Keine Duplikate”, die du markieren musst.
In der Statusleiste siehst du hinterher, wieviele Datensätze ausgefiltert wurden:

Gefilterte Liste

Gefilterte Liste

Möglichkeit 2: Die gefilterten Daten sollen an eine andere Stelle kopiert werden.

An andere Stelle kopieren

An andere Stelle kopieren

In diesem Fall musst Du noch den gewünschten Zielbereich angeben (es reicht die Angabe der linken oberen Zelle) und natürlich den Haken “Keine Duplikate” nicht vergessen:

Zweite, bereinigte Liste

Zweite, bereinigte Liste

So erhältst Du eine zweite, dublettenfreie Liste, während die Originaltabelle unverändert bleibt.

3. Bedingte Formatierung

Wenn es lediglich darum geht, alle Zellen zu markieren, deren Inhalte mehrfach in meiner Tabelle vorkommen, dann bietet sich die bedingte Formatierung an.

Ich habe dazu eine Beispieltabelle mit zufälligen Zahlen vorbereitet und möchte jede Zahl markieren, die mehrfach vorkommt:

Beispieltabelle

Beispieltabelle

Dazu markiere ich zunächst meine Datentabelle und rufe dann im Register “Start” die Schaltfläche “Bedingte Formatierung” auf. Hier gibt es den Menüpunkt “Regeln zum Hervorheben von Zellen”. Das Untermenü “Doppelte Werte…” bringt mich zum Ziel:

Regeln zum Hervorheben

Regeln zum Hervorheben


Doppelte Werte

Doppelte Werte

Und so sieht die Tabelle dann aus: Alle Zahlen, die doppelt vorkommen, sind farbig hinterlegt:

Ergebnis: Doppelte Werte

Ergebnis: Doppelte Werte

Dies war aber nur der Pflichtlauf, denn jetzt kommt die Kür!

Abhängig davon, wie oft eine Zahl mehrfach vorkommt, soll noch eine andere Farbe für die Markierung verwendet werden. Dazu entfernen wir zuerst die vorhin gesetzte bedingte Formatierung und markieren erneut den Datenbereich. Dann wählen wir wieder die Schaltfläche “Bedingte Formatierung”, jetzt aber den Menüpunkt “Neue Regel…”

Neue Regeln

Neue Regeln

Als Regeltyp wählen wir “Formel zur Ermittlung der zu formatierenden Zellen verwenden” und geben dann folgende ZÄHLENWENN()-Formel ein:
=ZÄHLENWENN($A$1:$K$24;A1)>1
Als Formatierung habe ich in meinem Beispiel einen grünen Hintergrund gewählt:

Zählenwenn-Regel 1

Zählenwenn-Regel 1

Zu Erläuterung:
In der ZÄHLENWENN()-Funktion gebe ich im ersten Parameter den kompletten absoluten Datenbereich meiner Tabelle an. Der zweite Parameter enthält mein Kriterium. Da beim Aufrufen der Funktion meine aktive Zelle A1 war, gebe ich hier “A1” ein (Wichtig: Keine $-Zeichen verwenden!) und als Bedingung “>1”. Somit wird für jede einzelne Zelle geprüft, ob der Wert dieser Zelle mehr als einmal in der Tabelle vorkommt.

Danach lege ich nach dem gleichen Muster noch eine zweite und eine dritte Regel an:

Weitere Regeln anlegen

Weitere Regeln anlegen

=ZÄHLENWENN($A$1:$K$24;A1)>2 (Formatierung = Gelb)
=ZÄHLENWENN($A$1:$K$24;A1)>3 (Formatierung = Rot)

Regel 3

Regel 3

Insgesamt habe ich somit drei verschiedene Bedingungen definiert:

Drei Regeln definiert

Drei Regeln definiert

Und so sieht mein Ergebnis aus: Zahlen, die zweimal vorkommen sind grün, dreifach vorkommende sind gelb und die Quadletten sind rot markiert!

Endergebnis: Bedingte Formatierung

Endergebnis: Bedingte Formatierung

Du siehst, mit ein bisschen Fantasie macht sogar die Dublettensuche fast noch Spaß!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

7 Gedanken zu “Das doppelte Lottchen, oder: Dublettensuche mit Excel

  • Sabine

    Bei großen Tabellen führen “bedingten Formatierungen” (aus eigener Erfahrung) zu erhöhtem Kaffeekonsum 😉
    Als schnelle Lösung um zu sehen, ob Dubletten vorhanden sind, eignet sich meiner Meinung eine Pivot am besten:
    Die gesuchte Spalte sowohl als Zeilenbeschriftung als auch Werte wählen, ggf. absteigend sortieren und hoffen, dass überall die “1” steht.

    • Martin Weiß Beitragsautor

      Hallo Sabine,

      zwischendurch eine Tasse Kaffee ist ja auch nicht schlecht – immerhin kann man dann mal vom Schreibtisch aufstehen 🙂
      Aber es stimmt schon: Bedingte Formatierungen und andere in großem Stil eingesetzte volatile Funktionen können Excel durchaus ins Schwitzen bringen. Und die von dir vorgeschlagene Pivot-Lösung ist definitiv eine gute und extrem schnelle Variante.

      Schöne Grüße,
      Martin

  • Marc Hommelsheim

    Also hier habe ich bis jetzt die beste Lösung gefunden. Allerdings funktioniert es bei mir immer noch nicht so richtig über mehrere Blätter. sobald ich einen doppelten wert in einem anderen Blatt eingebe, markiert er mir die Felder nicht. soll er aber. 🙂
    Erst wenn ich in demselben Blatt den Wert doppelt eingebe, markiert er es mir auch in allen anderen Blättern. Sehr merkwürdig…
    Kann mir einer Weiterhelfen?! 😀

    BG Marc

    • Martin Weiß Beitragsautor

      Hallo Marc,

      ich nehme mal an, Du hast die ZÄHLENWENN-Funktion in der Formatierungsregel verwendet. Wenn die Werte auf verschiedenen Arbeitsblättern berücksichtigt werden soll, dann musst Du in jedem Blatt eine leicht angepasste Formel verwenden. Beispiel: Eine Liste liegt im Blatt “Tabelle1” und die zweite Liste liegt im Blatt “Tabelle2”.

      Formatierungsregel für Blatt 1:
      =ZÄHLENWENN($A$1:$A$10;A1)+ZÄHLENWENN(Tabelle2!$A$1:$A$10;A1)>1

      Formatierungsregel für Blatt 2:
      =ZÄHLENWENN($A$1:$A$10;A1)+ZÄHLENWENN(Tabelle1!$A$1:$A$10;A1)>1

      Und Wenn es mehr Blätter sind, müssen eben noch weitere ZÄHLENWENN-Funktionen in die Formel rein. Wichtig ist eben, dass immer auch das jeweils andere Blatt im Bezug enthalten ist.

      Schöne Grüße,
      Martin