Arbeiten mehrere Personen an einer Liste oder werden Daten aus Fremdsystemen importiert, erhöht sich das Risiko, dass Datensätze mehrfach vorkommen. Excel bietet zwar die Befehlsfolge Daten – Duplikate entfernen, aber was dabei passiert, lässt sich nur zum Teil kontrollieren. Daher ziehen es viele Anwender vor, zunächst alle mehrfach vorkommenden Datensätze kenntlich zu machen. Dafür gibt es in Excel eine vorgefertigte Regel (Start – Bedingte Formatierung – Regeln zum Hervorheben von Zellen – Doppelte Werte). Doch sie hat einen Nachteil: Sie untersucht stets nur EINE Spalte nach identischen Daten.
Muss jedoch – wie im Beispiel in Bild 1 – überprüft werden, ob GLEICHZEITIG in MEHREREN Spalten identische Daten stehen, ist ein anderer Lösungsweg erforderlich.
Hier führt eine Bedingte Formatierung in Verbindung mit ZÄHLENWENNS zum Ziel.
Bild 1: Links die Ausgangsliste, rechts sind die Datensätze gekennzeichnet, die in den drei Spalten „Systemnr.“, „Von“ sowie „Fehlercode“ mehr als einmal vorkommen
Die spezielle Formatierungsregel anlegen
Die Regel wird in drei Schritten aufgestellt:
1) Markieren der betreffenden Daten.
2) Eingeben der Formel, die auf ZÄHLENWENNS beruht.
3) Wählen der Kennzeichnungsfarbe.
Bild 2: In der Liste den Bereich von A3 bis E11 markieren
- Zunächst markiere ich den zu überwachenden Datenbereich – hier also A3:E11.
- Dann wähle ich über die Registerkarte Start die Befehlsfolge Bedingte Formatierung – Neue Regel und klicke im folgenden Dialogfeld – wie in Bild 3 zu sehen – die unterste Regel an.
Bild 3: Im Dialogfeld zum Aufstellen der Regel den untersten Regeltyp wählen
Nun gebe ich in das Eingabefeld die in Bild 4 gezeigte Formel ein, die zunächst mit Hilfe von ZÄHLENWENNS prüft, wie oft ein Wert in den Spalten B, D und E vorkommt. Mit der anschließenden Anweisung >1 werden alle Ergebnisse der ZÄHLENWENNS-Funktion herausgefiltert, wo Daten in allen drei untersuchten Spalten mehr als einmal vorkommen. Für diese Daten trifft der Wert WAHR zu und genau auf diese Datensätze wird eine farbige Kennzeichnung angewendet.
Bild 4: Die Formel in der Vergrößerung: Wichtig sind die gemischten Bezüge für die Suchkriterien
Wichtig beim Anlegen der Formel: Korrekte Wahl der Art der Zellbezüge
- Die zu durchsuchenden Bereiche in Spalte B, D und E werden nach dem Markieren jeweils mit einmal F4 auf absolut gesetzt: also $B$3:$B$11, $D$3:$D$11 und $E$3:$E$11.
- Der zu prüfende Wert (das Suchkriterium) wird ebenfalls für die Spalte auf absolut gesetzt ($B, $D, $E), muss aber bei der Zeile relativ bleiben, die Zeilennummer muss sich also anpassen. Dazu betätige ich nach Anklicken des Suchkriteriums (B3, D3, E3) jeweils dreimal die F4-Taste und erhalte so $B3, $D3 sowie $E3.
- Anschließend klicke ich rechts unten auf die Schaltfläche Formatieren und wähle über die Registerkarte Ausfüllen eine gut sichtbare Füllfarbe zum Signalisieren der Datensätze, die in den Spalten B, D und E identische Daten haben.
- Mit OK schließe ich alle Dialogfelder.
Ausblick
Mit der hier gezeigten Lösung lassen sich IM NACHHINEIN mehrfach vorkommende Daten in mehreren Spalten nachweisen.
Wie sich VON VORNHEREIN vermeiden lässt, dass gleiche Datensätze mehrfach eingegeben werden, zeige ich in diesem Beitrag.
Hallo,
ich habe eine Frage dazu bevor ich Ihre Lösung angehen werde. Ich habe versucht 3x Felder zu verketten, darauf dann die bedingte Formatierung / Doppelte Werte laufen lassen. Warum funktioniert diese nicht?
Danke und VG
R.Lehmann
Auch diese Variante funktioniert … erstelle eine neue Spalte, die die drei relevanten Felder verkettet (beispielsweise =A1&B1&C1) und wende auf diese Spalte Start > Bedingte Formatierung > Doppelte Werte an. Allerdings ist darüber nur die Markierung in der neu berechneten Spalte möglich, nicht aber eine komplette Zeilenmarkierung.
Besten Dank dieser Beitrag hat mir weitergeholfen. Doch das Problem ist sie zu finden.
MFG Klaus