Namen, Datumslisten, Artikelnummern, … Sich wiederholende Einträge sind Alltag in Excel. Doch oftmals benötigen wir eine Liste mit eindeutigen Einträgen, um bspw. eine Auflistung unserer Mitarbeiter oder die Anzahl unserer Produkte zu erhalten.
Für solche eine Liste müssen sich wiederholende Werte (Duplikate) entfernt werden. Dafür gibt es verschiedene manuelle und automatisierte Methoden, die ich dir in diesem Beitrag vorstelle!
Duplikate manuell entfernen 💪🏻
Benutzeroberfläche (User Interface)
Klicke in die Tabelle, anschließend in der Registerkarte Daten im Bereich Datentools auf Duplikate entfernen.
Menüpunkt Duplikate entfernen
ACHTUNG
Beim Entfernen von Duplikaten ist Vorsicht geboten!
Beispiel: Du möchtest eine Liste mit eindeutigen Kundennummern aus einer Liste von Transaktionen erhalten. Hierbei würde die Funktion den ersten Eintrag jeder Kundennummer beibehalten.
-> Alle anderen Transaktionszeilen werden gelöscht!
Gewünschte Spalten markieren und Excel erledigt den Rest!
INFO
Beispiel:
Du markierst die StandortID & KundenID. Es wird also abgeglichen, ob diese Kombination (z. B. L001 & KD00001, L001 & KD00002, …) mehrmals auftritt.
Möchtest du mehrere Spalten einzeln prüfen, so markierst du nur eine Spalte und wiederholst die Funktion mit den anderen Spalten. Diese Quick-and-Dirty Lösung ist bei der einmaligen Aufbereitung von Daten Gold wert! 🥇
Bedingte Formatierung
Markiere die zu prüfende Spalte und gehe in die Registerkarte Start im Bereich Formatvorlagen auf die Funktion Bedingte Formatierung. Anschließend gehst du in das Untermenü Regeln zum Hervorherben von Zellen -> Doppelte Werte.
Option Doppelte Werte zur Hervorhebung von Duplikaten
Optionen für die Hervorhebung doppelter Werte
Nun kannst du die Duplikate filtern & entfernen. Der Vorteil dieser Methode ist die visuelle Darstellung (hier in rot) und die automatische Anwendung auf neue Einträge. Sie bietet sich daher für regelmäßige Sicht-Prüfungen sehr gut an! 👍🏻
Duplikate werden im gewünschten Design hervorgehoben
Bestimmte Werte entfernen durch Suche
Drücke die Tastenkombination STRG + F oder gehe über die Registerkarte Start im Bereich Bearbeiten in die Funktion Suchen und Auswählen -> Suchen.
Suchen-Option
Es öffnet sich ein neues Fenster. Gebe im Feld Suchen nach den gewünschten Wert ein und drücke anschließend auf Alle suchen.
Nun erscheinen unterhalb alle Zellen mit dem gesuchten Wert.
Drücke STRG + A oder Shift + linke Maustaste und wähle damit alle Einträge aus.
Wert eingeben & Alle suchen drücken –
Es werden alle Zellen mit dem gesuchten Wert angezeigt
Alle KundenIDs mit Kennung KD00007 wurden ausgewählt
Diese Methode bietet sich für einmalige Fälle an, in welchen du keine permanente bedingte Formatierung möchtest, dir die Duplikate entfernen-Funktion der Benutzeroberfläche jedoch nicht die gewünschte Lösung bringt.
Das Entfernen von Duplikaten automatisieren ♻
EINDEUTIG-Funktion
Die EINDEUTIG-Funktion ist eine der neuen Array-Formeln, die in Excel 2021 eingeführt wurden. Array bedeutet, dass EINDEUTIG mit nur einer Formeleingabe die Berechnung für die gesamte Matrix (in unserem Fall die Spalte) durchführt. Wir benötigen also nur eine Formel!
EINDEUTIG weist drei Argumente auf, wovon 2 optional sind. Als erforderliches Argument benötigen wir die Matrix, in diesem Fall die Spalte KundenID.
Argumente der EINDEUTIG-Funktion
(optionale Argumente weggelassen)
Das zweite Argument [nach_Spalte] (optional) akzeptiert WAHR (1) oder FALSCH (0). FALSCH sucht vertikal (Zeilen), WAHR horizontal (Spalten).
Das dritte Argument [genau_einmal] (optional) akzeptiert ebenfalls WAHR / FALSCH. FALSCH gibt alle Werte einmalig zurück. WAHR gibt dir nur Werte zurück, die exakt einmal vorkommen.
Lässt du die zwei optionalen Argumente weg, so nimmt die Formel für die optionalen Parameter FALSCH an. Perfekt zum Duplikate entfernen 🙂
Ausgegebene Liste (Array) durch EINDEUTIG-Formel
INFO
EINDEUTIG unterscheidet nicht zwischen Groß- & Kleinschreibung! KD00007 ist das gleiche wie kd00007.
Matrix-Formel (Index / Vergleich)
Mit der EINDEUTIG-Funktion kann man sich schnell & einfach eine einzigartige Liste erzeugen. Doch diese Funktion ist erst ab Excel 2021 vorhanden.
Verwendest du eine frühere Excel-Version, so kannst du auf diese Matrix-Formel zugreifen. Diese musst du so lange kopieren, bis du keinen Wert (-) mehr erhältst.
Bezüge der Matrix-Funktion
CHECK
Drücke beim Bestätigen der Formel nicht Enter, sondern STRG + SHIFT + Enter. Die Matrix-Formel erkennst du an den geschweiften Klammern am Anfang & Ende der Formel.
Ausgegebene Liste (Array) durch Matrix-Formel
Diese Formel habe ich bei Martin Weiß gefunden, weitere Infos gibt es hier.
PivotTable
Suche in der Registerkarte Einfügen unter Tabellen den PivotTable Button und klicke auf das Icon. Wähle nun den Datenbereich aus.
PivotTable einfügen durch Benutzeroberfläche
Daten der PivotTable ohne Bezug auf intelligente Tabelle
Alternativ kannst du in eine intelligente Tabelle klicken und in der Registerkarte Tabellenentwurf unter Tools die Funktion Mit PivotTable zusammenfassen nutzen. Diese wählt automatisch die intelligente Tabelle als Bereich aus.
PivotTable einfügen mit Bezug auf intelligente Tabelle
Daten der PivotTable mit Bezug auf intelligente Tabelle
Ziehe die Spalte, welche die eindeutigen Werte beinhalten soll, in die Zeilen des PivotTable.
Spalte für eindeutige Liste als Zeilen des PivotTable
Power Query
Mit Power Query kannst du dir ebenso eine eindeutige Liste ausgeben lassen, auch im Tabellenformat. Lade hierfür die Daten in Power Query.
Geladene Tabelle in Power Query
Anschließend wählst du die gewünschte Spalte aus, mit Rechtsklick auf andere Spalten entfernen bleibt die gewünschte Spalte übrig.
Relevante Spalte durch Entfernen aller anderen Spalten auswählen
Klicke erneut auf die Spalte und wähle den Befehl Duplikate entfernen, dadurch hast du eine eindeutige Liste erzeugt!
Abschließend kannst du die Werte zur besseren Übersichtlichkeit sortieren.
Eindeutige Liste auf- oder absteigend sortieren
Nun kannst du die Abfrage schließen & in ein Blatt laden.
Laden der Abfrage in Arbeitsmappe
Wähle aus, in welcher Form du die Liste haben möchtest. Entweder als intelligente Tabelle oder als PivotTable. Meine Empfehlung wäre eine Tabelle, für ein PivotTable gibt es eine einfachere Lösung.
Optionen zum Importieren der Daten in die Arbeitsmappe
Power Query bietet den Vorteil, dass die Daten wieder als intelligente Tabelle geladen werden können, statt als PivotTable oder Formel. Zudem können weitaus komplexere Sachverhalte hiermit gelöst werden.
Query Abfrage als intelligente Tabelle
Fazit 💡
In diesem Beitrag habe ich dir einige Methoden gezeigt, um eine Auflistung eindeutiger Werte zu erhalten.
Jede dieser Methoden hat ihre eigenen Vorteile hinsichtlich Anwendungsfreundlichkeit oder Flexibilität und jede hat ihre eigenen Anwendungsfälle.
Die manuellen Vorgehensweisen bieten sich für schnelle & einmalige Lösungen an, PivotTable & Power Query für automatisierte & häufige Vorgänge.
Da sollte auch für dich die ein oder andere dabei sein. 😉
Viel Spaß beim Ausprobieren!
0 Kommentare