01Welche Funktion nutzen?
Zu diesem Zweck gibt es in Excel die Funktion ZÄHLENWENNS. Deren allgemeine Syntax lautet: =ZÄHLENWENNS(Bereich 1; Kriterium 1; Bereich 2; Kriterium 2; ....).
- Microsoft Excel 365 (Version 1905) für Windows
Als Beispiel für die spaltenübergreifende Dublettensuche dient eine kleine Tabelle inklusive Spaltenüberschrift in der ersten Zeile. - Microsoft Excel 365 (Version 1905) für Windows
Insgesamt tauchen zwei Adressen doppelt auf. - Microsoft Excel 365 (Version 1905) für Windows
Das Grundgerüst für die Suche bildet die Funktion „ZÄHLENWENNS“ mit Bereichen und Kriterien als Argumente. - Microsoft Excel 365 (Version 1905) für Windows
Bezogen auf das Adressbeispiel ergibt sich die folgende Formel. Beachten Sie die Fixierung einiger Angaben per Dollar-Zeichen, um beim Kopieren die richtigen Bezüge zu erhalten. - Microsoft Excel 365 (Version 1905) für Windows
Wenn man die Formel um eine Überprüfung auf größer als 1 ergänzt, erhält man als Ergebnis „Wahr“ (Dublette) oder „Falsch“ (Keine Dublette). - Microsoft Excel 365 (Version 1905) für Windows
Nach unten kopiert, zeigt die Funktion jetzt direkt, ob und wo Duplikate vorkommen. - Microsoft Excel 365 (Version 1905) für Windows
Mithilfe der entwickelten Formel lässt sich eine Formatierungsregel anlegen. Dazu klicken Sie auf „Bedingte Formatierung“. - Microsoft Excel 365 (Version 1905) für Windows
Aus dem Pulldown-Menü wählen Sie die Option „Neue Regel“. - Microsoft Excel 365 (Version 1905) für Windows
Als Regeltyp selektieren Sie den rot hervorgehobenen Eintrag. In das Feld darunter kopieren Sie Ihre Formel. Mit dem Formatieren-Button können Sie eine farbige Markierung zuweisen, die in der Vorschau angezeigt wird. - Microsoft Excel 365 (Version 1905) für Windows
Nach einem Klick auf OK erscheinen alle identischen Adressen in der Tabelle farbig hervorgehoben, sodass sie sofort ins Auge fallen.
02Die Formel bestimmen
Als Beispiel wählen wir eine Adressliste, die von Spalte A bis Spalte E und von Zeile 2 bis Zeile 9 geht. In der ersten Zeile sind die Spaltenüberschriften untergebracht. Die Bereiche entsprechen hierbei den verschiedenen Spalten, das Kriterium dem Eintrag in der jeweiligen aktuellen Zeile. Somit ergibt sich folgende Formel:
=ZÄHLENWENNS($A$2:$A$9;$A2;$B$2:$B$9;$B2;$C$2:$C$9;$C2;$D$2:$D$9;$D2;$E$2:$E$9;$E2)
03Die richtigen Bezüge
Diese Formel fügen wir in F2 ein und kopieren sie dann nach unten. Damit beim Kopieren die Bezüge stimmen, müssen Sie die Bereiche mit absoluten Bezügen angeben, zu erkennen am Dollar-Zeichen. Die Kriterien dürfen Sie wiederum nur mit gemischten Bezügen angeben, sodass die Spalte einen absoluten Bezug hat, die Zeilennummer jedoch nicht.
04Was die Formel liefert
Die Zählenwenns-Funktion liefert eine Zahl zurück, die anzeigt, wie oft eine bestimmte Adresse in der Tabelle vorkommt. Entscheidend für eine Dublette ist aber lediglich, ob das Ergebnis größer als 1 ist. Eine Prüfung auf >1 komplettiert daher unsere Formel und liefert das Ergebnis Wahr (Dublette) oder Falsch (Keine Dublette).
05Die Formatierungsregel anlegen
Damit ist alles vorhanden, um eine eigene bedingte Formatierungsregel anzulegen. Dazu markieren Sie die Tabelle ohne die Spaltenüberschriften und erstellen über Bedingte Formatierung / Neue Regel eine neue Formatierungsregel.
06Das Ergebnis
Im folgenden Dialog markieren Sie unter Regeltyp den Eintrag Formel zur Ermittlung der zu formatierenden Zellen verwenden. Anschließend kopieren Sie Ihre Zählenwenns-Funktion in das Feld Werte formatieren, für die diese Formel wahr ist und bestätigen mit dem OK-Button. In der Adressliste werden jetzt die Dubletten entsprechend Ihrer festgelegten Formatierung hervorgehoben, etwa durch eine farbliche Markierung. (jd)