Excel Doppelte Werte In Spalte Finden
Das Auffinden von doppelten Einträgen in langen Tabellen gehört zu den häufigen Aufgaben des geplagten Anwenders: Auftragsnummern, Kontonummern, Kundennummern und so weiter, die Liste ließe sich beliebig erweitern.
Zum Glück stellt Excel einige praktische Hilfsmittel zur Verfügung, um solche Dubletten in einzelnen Spalten schnell zu identifizieren. Die Betonung liegt hier auf „einzelnen" Spalten. Was ist jedoch, wenn mich nur solche doppelten Einträge interessieren, dice über mehrere Spalten hinweg identisch sind. Also zum Beispiel Adressen?
Auch dafür gibt es eine Lösung.
Dem Problem mit doppelt vorkommenden Tabelleneinträgen habe ich mich auch schon in einem anderen Artikel gewidmet, den du gerne nochmal hier nachlesen kannst. Dort werden auch ein paar andere Techniken beschrieben.
Der heutige Beitrag befasst sich speziell mit den Möglichkeiten der bedingten Formatierung.
Dubletten in einzelnen Spalten
Doppelte Auftragsnummern etc. lassen sich am schnellsten über eine bedingte Formatierung erkennen. Sollte dir dieses extrem einfache Hilfsmittel bisher noch nicht bekannt gewesen sein, hier nochmal die Vorgehensweise am Beispiel von IP-Adressen. Nehmen wir an, ich bin Systemadministrator und habe eine Liste von IP-Adressen:
Liste mit IP-Adressen
Nun möchte ich ohne großen Aufwand die doppelten Werte farblich hervorheben. Genau dafür gibt es eine vordefinierte Regel in den bedingten Formatierungen: Erst die Liste markieren (1), danach das Menü „Start | Bedingte Formatierung | Regeln zum Hervorheben von Zellen | Doppelte Werte…" öffnen (ii).
Vordefinierte Formatierungsregel für doppelte Werte
Die vorgeschlagenen Werte im nächsten Fenster kann ich unverändert übernehmen, da hier bereits die Option „Doppelte Werte" voreingestellt ist:
Duplikate werden farbig markiert
Nachdem ich das Fenster mit OK geschlossen habe, werden mir sofort alle Duplikate farbig angezeigt. Voraussetzung für diese Art der bedingten Formatierung ist, dass immer nur die jeweilige einzelne Zelle für den Abgleich herangezogen werden soll.
Dubletten mit mehreren Spalten
In einem anderen Szenario funktioniert diese vordefinierte Regel nicht, nämlich dann, wenn mehrere Zellen berücksichtigt werden müssen. Also beispielsweise Adressen:
Beispiel: Adressenlste
Eine Dublette ist hier nur dann gegeben, wenn die Werte über alle Spalten hinweg identisch sind. Bevor wir hier eine entsprechende bedingte Formatierung einrichten können, sollten wir uns überlegen, wie dieses Problem generell zu lösen ist.
Im Klartext formuliert lautet die Regel in etwa so:
Du bist eine Dublette, wenn die jeweilige Kombination aus Vorname UND Nachname UND Straße UND Postleitzahl UND Ort mehr als einmal in der Liste vorkommt. Wir zählen also und arbeiten dabei mit mehreren Bedingungen.
Zu diesem Zweck gibt es in Excel dice Funktion ZÄHLENWENNSouth:
=ZÄHLENWENNS(Bereich 1; Kriterium 1; Bereich ii; Kriterium 2; Bereich 3; Kriterium 3; ....)
Dice Bereiche sind dabei dice verschiedenen Spalten, das Kriterium ist der Eintrag in der jeweiligen aktuellen Zeile. Bezogen auf meine Beispielliste sieht das likewise and then aus:
=ZÄHLENWENNS($A$2:$A$thirty;$A2;$B$two:$B$30;$B2;$C$2:$C$thirty;$C2;$D$2:$D$30;$D2;$E$2:$E$30;$E2)
Sieht wie ein ziemliches Monster aus, ist aber eigentlich leicht zu durchschauen:
Dice ZÄHLENWENNS-Funktion
Ganz wichtig:
Da wir die Formel anschließend einfach nach unten kopieren wollen, müssen die Bereiche zwingend mit absoluten Bezügen angegeben werden. Besides $A$2:$A$thirty, $B$2:$B$30 und so weiter.
Dice Kriterien hingegen dürfen nur mit gemischten Bezügen angegeben werden. Dice Spalte ist mit dem Dollarzeichen fixiert, dice Zeilennummer jedoch nicht. Also $A2, $B2 und and then weiter. Ansonsten stimmen die Bezüge nicht mehr, wenn dice Formel kopiert wird.
Als Ergebnis seht die 1, wenn diese Adresse in der ganzen Liste nur ein einziges Mal vorkommt. Ansonsten zeigt dice Zahl eben an, ob sie zweimal, dreimal oder noch öfter auftaucht:
Wie oft kommt die Adresse vor?
(Die farbigen Markierungen habe ich noch manuell gesetzt)
Für unsere Dublettenprüfung interessiert uns jetzt weniger dice exakte Zahl 1, 2 usw., es reicht eigentlich aus zu wissen, ob das Ergebnis größer als one ist. Daher hängen wir an die Formel noch eine kleine Prüfung an und erhalten als Ergebnis nur noch WAHR oder FALSCH:
Erweiterung der Formel
Und mit dieser Formel haben wir alles, was wir für eine eigene bedingte Formatierungsregel benötigen.
Wir markieren also zunächst wieder dice Adressenliste ohne dice Überschriften (1), in meinem Beispiel den Bereich A2:E30 und legen dann eine neue Formatierungsregel an (2):
Eigene Formatierungsregel anlegen
Im nächsten Fenster markieren wir oben den Eintrag „Formel zur Ermittlung der zu formatierenden Zellen verwenden" (3) und kopieren dann dice fertige ZÄHLENWENN-Funktion in das Formelfeld (4):
=ZÄHLENWENNS($A$two:$A$xxx;$A2;$B$2:$B$30;$B2;$C$2:$C$30;$C2;$D$2:$D$30;$D2;$E$2:$Eastward$30;$E2)>1
Die ZÄHLENWENNS-Funktion kommt zum Einsatz
Wichtig ist nur, dass die Zeilennummern in der Formel auch mit der ersten markierten Zeile übereinstimmen. Und wie man im Hintergrund schon sieht, werden jetzt wirklich nur die vollständigen Duplikate hervorgehoben.
Wie man an diesem Beispiel sieht, macht es durchaus Sinn, sich bei eigenen Formatierungsregeln dice benötigten Formeln erst einmal in der Tabelle zusammenzubasteln. Dann sieht homo auch schnell, ob sie grundsätzlich funktioniert oder nicht.
Falls du beim Nachbauen trotzdem Probleme hattest, kannst du dir meine Beispieldatei hier herunterladen.
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!
P.Southward. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Calculator.
Source: https://www.tabellenexperte.de/duplikate-in-excel-ueber-mehrere-spalten-finden/
Posted by: higdonlifid2001.blogspot.com
0 Response to "Excel Doppelte Werte In Spalte Finden"
Post a Comment