Generische Formel
=SUMPRODUCT(COUNTIF(data,data)-1)>0
Zusammenfassung
Enthält ein Bereich doppelte Werte? Wenn Sie einen Bereich (oder eine Liste) auf Duplikate testen möchten, können Sie dies mit einer Formel tun, die COUNTIF zusammen mit SUMPRODUCT verwendet.
Im Beispiel gibt es eine Liste von Namen im Bereich B3: B11. Wenn Sie diese Liste testen möchten, um festzustellen, ob doppelte Namen vorhanden sind, können Sie Folgendes verwenden:
=SUMPRODUCT(COUNTIF(B3:B11,B3:B11)-1)>0
Erläuterung
COUNTIF zählt von innen nach außen und zählt zunächst jeden Wert in B3: B11 im Bereich B3: B11. Da wir einen Bereich (Array) von Zellen für die Kriterien bereitstellen, gibt COUNTIF als Ergebnis ein Array von Zählungen zurück. Im gezeigten Beispiel sieht dieses Array folgendermaßen aus:
(1; 2; 1; 1; 1; 1; 1; 2; 1)
Die nächste 1 wird subtrahiert, was ein Array wie das folgende ergibt:
(0; 1; 0; 0; 0; 0; 0; 1; 0)
Beachten Sie, dass jede 1 im Array (dh Elemente, die nur einmal angezeigt werden) in eine Null konvertiert wurde.
Als nächstes fügt SUMPRODUCT die Elemente in diesem Array hinzu und gibt das Ergebnis zurück, in diesem Fall die Nummer 2, die dann auf einen Wert> 0 getestet wird.
Jedes Mal, wenn eine Liste Duplikate enthält, enthält das von SUMPRODUCT summierte Array mindestens zwei Einsen. Ein Endergebnis von TRUE bedeutet also, dass die Liste Duplikate enthält.
Umgang mit leeren Zellen
Leere Zellen im Bereich führen dazu, dass die obige Formel zu falschen Ergebnissen führt. Um leere oder leere Zellen herauszufiltern, können Sie die folgende Alternative verwenden:
=SUMPRODUCT((COUNTIF(list,list)-1)*(list""))>0
Hier verwenden wir die logische Ausdrucksliste "", um alle mit leeren Zellen verknüpften Werte auf Null zu setzen.
