Formuła ogólna
=SUMPRODUCT(COUNTIF(data,data)-1)>0
Podsumowanie
Czy zakres zawiera zduplikowane wartości? Jeśli chcesz przetestować zakres (lub listę) pod kątem duplikatów, możesz to zrobić za pomocą formuły, która używa funkcji LICZ.JEŻELI razem z SUMPRODUCT.
W tym przykładzie jest lista nazwisk z zakresu B3: B11. Jeśli chcesz przetestować tę listę, aby sprawdzić, czy istnieją zduplikowane nazwy, możesz użyć:
=SUMPRODUCT(COUNTIF(B3:B11,B3:B11)-1)>0
Wyjaśnienie
Działając od wewnątrz, LICZ.JEŻELI najpierw pobiera liczbę wszystkich wartości w B3: B11 z zakresu B3: B11. Ponieważ dostarczamy zakres (tablicę) komórek dla kryteriów, LICZ.JEŻELI zwraca jako wynik tablicę zliczeń. W pokazanym przykładzie tablica wygląda następująco:
(1; 2; 1; 1; 1; 1; 1; 2; 1)
Następna 1 jest odejmowana, co daje następującą tablicę:
(0; 1; 0; 0; 0; 0; 0; 1; 0)
Zauważ, że każda 1 w tablicy (tj. Elementy, które pojawiają się tylko raz) została przekonwertowana na zero.
Następnie SUMPRODUCT dodaje elementy w tej tablicy i zwraca wynik, którym w tym przypadku jest liczba 2, która jest następnie sprawdzana pod kątem wartości> 0.
Za każdym razem, gdy lista zawiera duplikaty, w tablicy zsumowanej przez SUMPRODUCT będą co najmniej dwie jedynki, więc końcowy wynik PRAWDA oznacza, że lista zawiera duplikaty.
Obsługa pustych komórek
Puste komórki w zakresie spowodują, że powyższa formuła wygeneruje nieprawidłowe wyniki. Aby odfiltrować puste lub puste komórki, możesz użyć następującej alternatywy:
=SUMPRODUCT((COUNTIF(list,list)-1)*(list""))>0
Tutaj używamy listy "" wyrażeń logicznych, aby wymusić na zera wszystkie wartości skojarzone z pustymi komórkami.
