Generische Formel
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Zusammenfassung
Excel enthält eine integrierte Voreinstellung zum Hervorheben doppelter Werte mit bedingter Formatierung, funktioniert jedoch nur auf Zellebene. Wenn Sie ganze Zeilen markieren möchten, die Duplikate sind, müssen Sie Ihre eigene Formel verwenden, wie unten erläutert.
Wenn Sie doppelte Zeilen in einem unsortierten Datensatz hervorheben und keine Hilfsspalte hinzufügen möchten, können Sie eine Formel verwenden, die die COUNTIFS-Funktion verwendet, um doppelte Werte in jeder Spalte der Daten zu zählen.
Wenn Sie beispielsweise Werte in den Zellen B4: D11 haben und ganze doppelte Zeilen hervorheben möchten, können Sie eine ziemlich hässliche Formel verwenden:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Benannte Bereiche für eine sauberere Syntax
Der Grund, warum die obige Formel so hässlich ist, ist, dass wir jeden Spaltenbereich vollständig sperren und dann eine gemischte Referenz verwenden müssen, um jede Zelle in jeder Spalte zu testen. Wenn Sie benannte Bereiche für jede Spalte in den Daten erstellen: col_a, col_b und col_c, kann die Formel mit einer viel saubereren Syntax geschrieben werden:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Erläuterung
In der Formel zählt COUNTIFS, wie oft jeder Wert in einer Zelle in der übergeordneten Spalte angezeigt wird. Per Definition muss jeder Wert mindestens einmal vorkommen. Wenn also die Anzahl> 1 ist, muss der Wert ein Duplikat sein. Die Referenzen werden sorgfältig gesperrt, sodass die Formel nur dann true zurückgibt, wenn alle drei Zellen in einer Zeile mehr als einmal in ihren jeweiligen Spalten angezeigt werden.
Die Hilfsspaltenoption "betrügt", indem alle Werte in einer Zeile mithilfe der Verkettung in einer Zelle zusammengefasst werden. Dann zählt COUNTIF einfach, wie oft dieser verkettete Wert in Spalte D angezeigt wird.
Hilfsspalte + Verkettung
Wenn es Ihnen nichts ausmacht, Ihren Daten eine Hilfsspalte hinzuzufügen, können Sie die Formel für die bedingte Formatierung erheblich vereinfachen. Verketten Sie in einer Hilfsspalte Werte aus allen Spalten. Fügen Sie beispielsweise in Spalte E eine Formel hinzu, die folgendermaßen aussieht:
=B4&C4&D4
Verwenden Sie dann die folgende Formel in der bedingten Formatierungsregel:
=COUNTIF($E$4:$E$11,$E4)>1
Dies ist eine viel einfachere Regel, und Sie können die Hilfsspalte ausblenden, wenn Sie möchten.
Wenn Sie eine sehr große Anzahl von Spalten haben, können Sie die TEXTJOIN-Funktion (Excel 2016 365) verwenden, um die Verkettung mithilfe eines Bereichs durchzuführen:
=TEXTJOIN(",",TRUE,A1:Z1)
Sie können dann COUNTIF wie oben verwenden.
SUMPRODUCT
Wenn Sie eine Version von Excel vor 2007 verwenden, können Sie SUMPRODUCT wie folgt verwenden:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
