Kommentare - 0
Kommentare - 0
Logo DeclutterData - Icon

DeclutterData

Datenoptimierung & Controlling

^

Lesefortschritt:

Start 9 Bedingte Formatierung 9 7 Wege für eine Liste mit eindeutigen Werten

7 Wege für eine Liste mit eindeutigen Werten

Phillip Seefeld
Mann entfernt Dateien und entsorgt sie in Mülleimer
Namen, Datums­lis­ten, Arti­kel­num­mern, … Sich wie­der­ho­len­de Ein­trä­ge sind All­tag in Excel. Doch oft­mals benö­ti­gen wir eine Lis­te mit ein­deu­ti­gen Ein­trä­gen, um bspw. eine Auf­lis­tung unse­rer Mit­ar­bei­ter oder die Anzahl unse­rer Pro­duk­te zu erhalten.

Für sol­che eine Lis­te müs­sen sich wie­der­ho­len­de Wer­te (Dupli­ka­te) ent­fernt wer­den. Dafür gibt es ver­schie­de­ne manu­el­le und auto­ma­ti­sier­te Metho­den, die ich dir in die­sem Bei­trag vorstelle!

Duplikate manuell entfernen 💪🏻

Benutzeroberfläche (User Interface)

Kli­cke in die Tabel­le, anschlie­ßend in der Regis­ter­kar­te Daten im Bereich Daten­tools auf Dupli­ka­te ent­fer­nen.

Excel | Datentools | Duplikate entfernen

Menü­punkt Dupli­ka­te entfernen

Es öff­net sich ein Fens­ter mit den Ein­stel­lun­gen zum Dupli­ka­te ent­fer­nen. Wäh­le die Spal­ten aus, die auf Dupli­ka­te geprüft wer­den sollen.

ACHTUNG

Beim Ent­fer­nen von Dupli­ka­ten ist Vor­sicht gebo­ten!
Bei­spiel: Du möch­test eine Lis­te mit ein­deu­ti­gen Kun­den­num­mern aus einer Lis­te von Trans­ak­tio­nen erhal­ten. Hier­bei wür­de die Funk­ti­on den ers­ten Ein­trag jeder Kun­den­num­mer bei­be­hal­ten.
-> Alle ande­ren Trans­ak­ti­ons­zei­len wer­den gelöscht!

Excel | Intelligente Tabelle | Duplikate entfernen

Gewünsch­te Spal­ten mar­kie­ren und Excel erle­digt den Rest!

INFO

Mar­kierst du meh­re­re Spal­ten, so wer­den die Wer­te in Kom­bi­na­ti­on gesucht.

Bei­spiel:
Du mar­kierst die Stand­ort­ID Kun­denID. Es wird also abge­gli­chen, ob die­se Kom­bi­na­ti­on (z. B. L001 & KD00001, L001 & KD00002, …) mehr­mals auf­tritt.

Möch­test du meh­re­re Spal­ten ein­zeln prü­fen, so mar­kierst du nur eine Spal­te und wie­der­holst die Funk­ti­on mit den ande­ren Spal­ten. Die­se Quick-and-Dirty Lösung ist bei der ein­ma­li­gen Auf­be­rei­tung von Daten Gold wert! 🥇

Bedingte Formatierung

Mar­kie­re die zu prü­fen­de Spal­te und gehe in die Regis­ter­kar­te Start im Bereich For­mat­vor­la­gen auf die Funk­ti­on Beding­te For­ma­tie­rung. Anschlie­ßend gehst du in das Unter­me­nü Regeln zum Her­vor­her­ben von Zel­len -> Dop­pel­te Wer­te.

Excel | Menü für bedingte Formatierung zum Hervorheben von doppelten Werten

Opti­on Dop­pel­te Wer­te zur Her­vor­he­bung von Duplikaten

Es erscheint ein Fens­ter, in wel­chem du die For­ma­tie­rung der Her­vor­he­bung anpas­sen kannst.
Einstellungsfenster für bedingte Formatierung von doppelten Werten

Optio­nen für die Her­vor­he­bung dop­pel­ter Werte

Nun kannst du die Dupli­ka­te fil­tern & ent­fer­nen. Der Vor­teil die­ser Metho­de ist die visu­el­le Dar­stel­lung (hier in rot) und die auto­ma­ti­sche Anwen­dung auf neue Ein­trä­ge. Sie bie­tet sich daher für regel­mä­ßi­ge Sicht-Prüfungen sehr gut an! 👍🏻

Excel | Intelligente Tabelle | Duplikate farblich hervorgehoben

Dupli­ka­te wer­den im gewünsch­ten Design hervorgehoben

Bestimmte Werte entfernen durch Suche

Drü­cke die Tas­ten­kom­bi­na­ti­on STRG + F oder gehe über die Regis­ter­kar­te Start im Bereich Bear­bei­ten in die Funk­ti­on Suchen und Aus­wäh­len -> Suchen.

Excel | Suchen & Auswählen | Suchen

Suchen-Option

Es öff­net sich ein neu­es Fens­ter. Gebe im Feld Suchen nach den gewünsch­ten Wert ein und drü­cke anschlie­ßend auf Alle suchen.
Nun erschei­nen unter­halb alle Zel­len mit dem gesuch­ten Wert.

Drü­cke STRG + A oder Shift + lin­ke Maus­tas­te und wäh­le damit alle Ein­trä­ge aus.

Suchen und Ersetzen | Alle Einträge suchen

Wert ein­ge­ben & Alle suchen drü­cken -
Es wer­den alle Zel­len mit dem gesuch­ten Wert angezeigt

Schlie­ße nun das Fens­ter (Esc), die Zel­len soll­ten wei­ter­hin mar­kiert sein (grau hinterlegt).
Excel | Intelligente Tabelle | Ausgewählte Zellen

Alle Kun­denIDs mit Ken­nung KD00007 wur­den ausgewählt

Nun kannst du die Zel­len nach Belie­ben ändern, bspw. löschen (STRG + Minus-Taste), lee­ren (Entf) oder über­schrei­ben (STRG + Enter).

Die­se Metho­de bie­tet sich für ein­ma­li­ge Fäl­le an, in wel­chen du kei­ne per­ma­nen­te beding­te For­ma­tie­rung möch­test, dir die Dupli­ka­te entfernen-Funktion der Benut­zer­ober­flä­che jedoch nicht die gewünsch­te Lösung bringt.

Das Entfernen von Duplikaten automatisieren

EINDEUTIG-Funktion

Die EINDEUTIG-Funk­ti­on ist eine der neu­en Array-Formeln, die in Excel 2021 ein­ge­führt wur­den. Array bedeu­tet, dass EINDEUTIG mit nur einer For­mel­ein­ga­be die Berech­nung für die gesam­te Matrix (in unse­rem Fall die Spal­te) durch­führt. Wir benö­ti­gen also nur eine For­mel!

EINDEUTIG weist drei Argu­men­te auf, wovon 2 optio­nal sind. Als erfor­der­li­ches Argu­ment benö­ti­gen wir die Matrix, in die­sem Fall die Spal­te Kun­denID.

Excel Bearbeitungsleiste mit EINDEUTIG Formel

Argu­men­te der EINDEUTIG-Funk­ti­on
(optio­na­le Argu­men­te weggelassen)

=EINDEUTIG(verkäufe_eindeutig[KundenID])

Das zwei­te Argu­ment [nach_Spalte] (optio­nal) akzep­tiert WAHR (1) oder FALSCH (0). FALSCH sucht ver­ti­kal (Zei­len), WAHR hori­zon­tal (Spal­ten).

Das drit­te Argu­ment [genau_einmal] (optio­nal) akzep­tiert eben­falls WAHR / FALSCH. FALSCH gibt alle Wer­te ein­ma­lig zurück. WAHR gibt dir nur Wer­te zurück, die exakt ein­mal vor­kom­men.

Lässt du die zwei optio­na­len Argu­men­te weg, so nimmt die For­mel für die optio­na­len Para­me­ter FALSCH an. Per­fekt zum Dupli­ka­te entfernen 🙂

Excel | EINDEUTIG | Array

Aus­ge­ge­be­ne Lis­te (Array) durch EINDEUTIG-For­mel

INFO

EINDEUTIG unter­schei­det nicht zwi­schen Groß- & Klein­schrei­bung! KD00007 ist das glei­che wie kd00007.

Matrix-Formel (Index / Vergleich)

Mit der EINDEUTIG-Funktion kann man sich schnell & ein­fach eine ein­zig­ar­ti­ge Lis­te erzeu­gen. Doch die­se Funk­ti­on ist erst ab Excel 2021 vorhanden.

Ver­wen­dest du eine frü­he­re Excel-Version, so kannst du auf die­se Matrix-Formel zugrei­fen. Die­se musst du so lan­ge kopie­ren, bis du kei­nen Wert (-) mehr erhältst.

Excel | Bearbeitungsleiste | Matrix-Formel

Bezü­ge der Matrix-Funk­ti­on

=WENNNV(INDEX(verkäufe_matrix[KundenID];VERGLEICH(0;ZÄHLENWENN(I$1:$I1;verkäufe_matrix[KundenID]);0));”-”)

CHECK

Drü­cke beim Bestä­ti­gen der For­mel nicht Enter, son­dern STRG + SHIFT + Enter. Die Matrix-Formel erkennst du an den geschweif­ten Klam­mern am Anfang & Ende der Formel.

Zu beach­ten ist beson­ders der Bezug in der ZÄHLENWENN-For­mel (I$1:$I1). Die­ser beginnt eine Zel­le über der eigent­li­chen For­mel und erwei­tert sich durch die Set­zung der $-Zei­chen ent­spre­chend beim nach unten kopieren.
Excel | Matrix-Formel | Array

Aus­ge­ge­be­ne Lis­te (Array) durch Matrix-For­mel

Die­se For­mel habe ich bei Mar­tin Weiß gefun­den, wei­te­re Infos gibt es hier.

PivotTable

Suche in der Regis­ter­kar­te Ein­fü­gen unter Tabel­len den Pivot­Ta­ble But­ton und kli­cke auf das Icon. Wäh­le nun den Daten­be­reich aus.

Excel | Menüpunkt PivotTable

Pivot­Ta­ble ein­fü­gen durch Benutzeroberfläche

Excel | PivotTable | Erstellen mit Spaltenbezug

Daten der Pivot­Ta­ble ohne Bezug auf intel­li­gen­te Tabelle

Alter­na­tiv kannst du in eine intel­li­gen­te Tabel­le kli­cken und in der Regis­ter­kar­te Tabel­len­ent­wurf unter Tools die Funk­ti­on Mit Pivot­Ta­ble zusam­men­fas­sen nut­zen. Die­se wählt auto­ma­tisch die intel­li­gen­te Tabel­le als Bereich aus.

Excel | Intelligente Tabelle | Mit PivotTable zusammenfassen

Pivot­Ta­ble ein­fü­gen mit Bezug auf intel­li­gen­te Tabelle

Excel | PivotTable | Erstellen mit intelligenter Tabelle

Daten der Pivot­Ta­ble mit Bezug auf intel­li­gen­te Tabelle

Zie­he die Spal­te, wel­che die ein­deu­ti­gen Wer­te beinhal­ten soll, in die Zei­len des Pivot­Ta­ble.

PivotTable mit Auswahl einer Spalte als Zeile

Spal­te für ein­deu­ti­ge Lis­te als Zei­len des PivotTable

Nun hast du eine Auf­lis­tung aller ein­deu­ti­gen Ein­trä­ge der Spal­te und somit alle Dupli­ka­te ent­fernt! 🙂 Ein net­ter Neben­ef­fekt beim Pivot­Ta­ble: Wenn mög­lich sind die Wer­te gleich sortiert!

Power Query

Mit Power Query kannst du dir eben­so eine ein­deu­ti­ge Lis­te aus­ge­ben las­sen, auch im Tabel­len­for­mat. Lade hier­für die Daten in Power Query.

Power Query | Abfrage von Tabelle

Gela­de­ne Tabel­le in Power Query

Anschlie­ßend wählst du die gewünsch­te Spal­te aus, mit Rechts­klick auf ande­re Spal­ten ent­fer­nen bleibt die gewünsch­te Spal­te übrig.

Excel | Power Query | Spalte beibehalten

Rele­van­te Spal­te durch Ent­fer­nen aller ande­ren Spal­ten auswählen

Kli­cke erneut auf die Spal­te und wäh­le den Befehl Dupli­ka­te ent­fer­nen, dadurch hast du eine ein­deu­ti­ge Lis­te erzeugt!

Excel | Power Query | Duplikate entfernen
Befehl zum Ent­fer­nen von Dupli­ka­ten in Power Query

Abschlie­ßend kannst du die Wer­te zur bes­se­ren Über­sicht­lich­keit sor­tie­ren.

Excel | Power Query | Aufsteigend sortieren

Ein­deu­ti­ge Lis­te auf- oder abstei­gend sortieren

Nun kannst du die Abfra­ge schlie­ßen & in ein Blatt laden.

Power Query | Schließen & Laden

Laden der Abfra­ge in Arbeitsmappe

Wäh­le aus, in wel­cher Form du die Lis­te haben möch­test. Ent­we­der als intel­li­gen­te Tabel­le oder als Pivot­Ta­ble. Mei­ne Emp­feh­lung wäre eine Tabel­le, für ein Pivot­Ta­ble gibt es eine ein­fa­che­re Lösung.

Excel | Power Query | Daten importieren

Optio­nen zum Impor­tie­ren der Daten in die Arbeitsmappe

Power Query bie­tet den Vor­teil, dass die Daten wie­der als intel­li­gen­te Tabel­le gela­den wer­den kön­nen, statt als Pivot­Ta­ble oder For­mel. Zudem kön­nen weit­aus kom­ple­xe­re Sach­ver­hal­te hier­mit gelöst werden.

Excel | Intelligente Tabelle mit eindeutigen Kundennummern

Query Abfra­ge als intel­li­gen­te Tabelle

Fazit 💡

In die­sem Bei­trag habe ich dir eini­ge Metho­den gezeigt, um eine Auf­lis­tung ein­deu­ti­ger Wer­te zu erhalten.

Jede die­ser Metho­den hat ihre eige­nen Vor­tei­le hin­sicht­lich Anwen­dungs­freund­lich­keit oder Fle­xi­bi­li­tät und jede hat ihre eige­nen Anwen­dungs­fäl­le.

Die manu­el­len Vor­ge­hens­wei­sen bie­ten sich für schnel­le & ein­ma­li­ge Lösun­gen an, Pivot­Ta­ble & Power Query für auto­ma­ti­sier­te & häu­fi­ge Vorgänge.

Da soll­te auch für dich die ein oder ande­re dabei sein. 😉
Viel Spaß beim Ausprobieren!

Phillip Seefeld

Ich möchte dir helfen deine Daten & Zahlen auf das nächste Level zu bringen! Hier im Blog schreibe ich über alles Mögliche zu Excel & Power BI. Mehr Austausch? Tritt DeclutterData auf Reddit bei!

0 Kommentare

Einen Kommentar abschicken

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

Ich helfe dir!

Schwierigkeiten mit Excel & Power BI?

Das könnte dir auch gefallen 🙂

Share This

Bitte weitersagen 🙂

Beitrag teilen und Wissen verbreiten. Danke 💚