Doppelte Einräge in Excel filtern und mit Formel finden
3 min

Mit Excel den Duplikaten auf der Spur

Microsoft Excel kennt viele Möglichkeiten, um doppelten Einträgen auf die Spur zu kommen. Diese Tipps zeigen Ihnen, wie Sie Duplikate mit Filtern, Formatieren und Formeln erkennen – auch über mehrere Spalten hinweg.

Doppelte Werte in einer Excel-Tabelle können auf vielfältige Weise entstehen und sind nicht immer Zeichen eines Fehlers. Aber sie können das Bild verfälschen. Nehmen wir an, Sie betreiben ein Malergeschäft und haben aus Ihrer Bestellsoftware die Bestellungen in eine Excel-Tabelle exportiert (das Beispiel ist der Übersichtlichkeit halber kurz gehalten).

Excel: Duplikate filtern und mit Formel finden
Die Beispieltabelle für diesen Tipp.

Diese Daten können Sie nun filtern, formatieren und mittels Formeln bearbeiten, um Aussagen über Ihre Kunden und Bestellungen zu gewinnen.

Wie Sie Duplikate löschen, können Sie in diesem Excel-Tipp nachlesen.

Bedingte Formatierung: gute Kunden hervorheben

Welcher Kunde hat mehr als eine Bestellung aufgegeben? Diese Frage können Sie «optisch» beantworten, indem Sie mit der bedingten Formatierung (mehr dazu finden Sie in diesem Tipp) mehrfach vorkommende Werte in der Spalte «Firma» hervorheben.

Excel Duplikate mit bedingter Formatierung anzeigen
Mit der bedingten Formatierung in Excel lassen sich Duplikate farbig hervorheben.

Markieren Sie die Einträge, und wählen Sie aus dem Menüband «Start > Bedingte Formatierung». Unter «Regeln zum Hervorheben von Zellen» finden Sie den Eintrag «Doppelte Werte». Wählen Sie diesen aus und passen Sie allenfalls die Farbe an. Sie werden schnell feststellen, dass die «WM AG» Ihr bester Kunde ist.

Duplikate ausfiltern

Doch – wie viele Kunden haben Sie? Diese Frage können Sie mittels Filter beantworten, indem Sie Duplikate ausblenden und die Zahl der übriggebliebenen Zeilen zählen. Doch der Reihe nach:

  1. Markieren Sie die Spalte «Firma» samt Überschrift, und wählen Sie aus dem Menüband «Daten» beim Abschnitt «Sortieren und Filtern» den Eintrag «Erweitert». Im Dialogfeld des Spezialfilters wählen Sie «an eine andere Stelle kopieren» und eine Zelle, die als linke obere Ecke des Bereichs dient. Zudem aktivieren Sie die Option «Keine Duplikate» und klicken auf Ok. Nun wird die Tabelle kopiert, aber ohne doppelte Einträge.
  2. Klicken Sie in ein leeres Feld unterhalb der Spalte «Firma» und fügen Sie folgende Formel ein: =ZEILEN(A11:A15) – den Bereich A11:A15 passen Sie so an, dass er sämtliche Einträge in der Firmenspalte umfasst. Als Ergebnis erhalten Sie die Anzahl Zeilen und damit die Zahl der Kunden.
Excel erweiterter Filter: Duplikate ausschliessen
Eintrage innerhalb der Tabelle kopieren und dabei Duplikate ausschliessen mit dem erweiterten Filter von Excel.

Mit einer Formel Duplikate über mehrere Spalten hinweg erkennen

Die bis jetzt beschriebenen Ansätze haben alle den Nachteil, dass Sie nur Einträge in einer einzigen Spalte berücksichtigen. Was nun, wenn Sie doppelte Bestellungen erkennen möchten, in der Beispieltabelle also die Einträge in den ersten vier Spalten identisch sein müssen?

Excel doppelte Einträge mit Formel finden
Excel findet Duplikate auch mithilfe einer Formel.

In diesem Fall ziehen Sie zuerst die Werte der ersten vier Spalten in einer Hilfsspalte zusammen und prüfen danach diese Einträge auf Duplikate. Das geht am besten mit einer Formel:

  1. Ziehen Sie die Einträge der ersten vier Spalten zeilenweise zusammen. In der Beispieltabelle dient dazu diese Excel-Formel in Spalte H: =A2&B2&C2&D2. Als Ergebnis erhalten Sie etwas wie «WM AGWalterMüllerFarbe rot» – nicht sehr leserlich, aber für die Erkennung von Duplikaten ausreichend.
  2. Übertragen Sie die Formel in die restlichen Zeilen der Hilfsspalte, indem Sie die Zelle an der rechten unteren Ecke «packen» und nach unten ziehen. Der Mauszeiger verändert sich dabei zu einem Kreuz.
  3. In einer weiteren Hilfsspalte tragen Sie folgende Formel ein: =WENN(ZÄHLENWENN(H:H;H2)>1;»Duplikat»;»»). Die Excel-Funktion ZÄHLENWENN() zählt, wie häufig der Eintrag in der Zelle H2 in der Spalte H (Bereich H:H) vorkommt. Ist das mehr als ein Mal der Fall, schreibt die WENN-Funktion den Text «Duplikat» in die Zelle. Ist der Eintrag einmalig, bleibt die Zelle leer.
  4. Mit dem Filter aus dem Menüband «Start > Sortieren und Filtern» können Sie nun doppelte oder mehrfache Einträge ausfiltern.

Sie sehen, mit Excel doppelte Einträge zu berechnen und zu filtern, ist – einfach!

 

Die Beispieltabelle zu diesem Excel-Tipp können Sie hier herunterladen.

Schreiben Sie einen Kommentar

Ihre E-Mail-Adresse wird nicht veröffentlicht.

5 Kommentare zu “Mit Excel den Duplikaten auf der Spur

  1. diese formel lässt sich leider bei mir nicht einfach runterziehen, mache ich dies, dann wird entweder immer die gleiche Formel eingesetzt oder nur die Endzahl der Formel um eins erhöht!

    1. Guten Tag Herr Hoffmann
      Auf welche Formel beziehen Sie sich? Das Herunterziehen müsste eigentlich in den beschriebenen Fällen funktionieren.
      Freundliche Grüsse aus der Redaktion
      Andreas Heer

  2. Zählenwenn in der beschriebenen Form um Dublikate zu erkennen, ist fehleranfällig, da nicht berücksichtigt wird in welcher Zelle die Werte standen.
    Ein Eintrag mit Spalte A = 101 und Spalte B = 1 würde so als Dublikat von einer zweiten Zeile mit Spalte A = 10 und Spalte B = 11 erkannt.
    In diesem Beispiel sollten die Zellen mit einerm Trennzeichen verknüpft werden.Anstatt A2 & B2 in der Hilfstabelle also zum Beispiel A2 & «|» & B2.

    1. Guten Tag Herr Müller

      Danke für diesen Input, Sie haben natürlich völlig recht. So weit hatte ich gar nicht gedacht 😉
      Freundliche Grüsse aus der Redaktion
      Andreas Heer

Jetzt lesen