Všeobecný vzorec
=SUMPRODUCT(COUNTIF(data,data)-1)>0
Zhrnutie
Obsahuje rozsah duplicitné hodnoty? Ak chcete otestovať rozsah (alebo zoznam) duplikátov, môžete to urobiť pomocou vzorca, ktorý používa COUNTIF spolu s SUMPRODUCT.
V príklade je zoznam mien v rozsahu B3: B11. Ak chcete vyskúšať tento zoznam a zistiť, či existujú duplicitné názvy, môžete použiť:
=SUMPRODUCT(COUNTIF(B3:B11,B3:B11)-1)>0
Vysvetlenie
Pri postupe zvnútra získa COUNTIF najskôr počet všetkých hodnôt v B3: B11 v rozsahu B3: B11. Pretože pre kritériá dodávame rozsah (pole) buniek, funkcia COUNTIF vráti ako výsledok pole počtov. V zobrazenom príklade toto pole vyzerá takto:
(1; 2; 1; 1; 1; 1; 1; 2; 1)
Nasledujúca 1 sa odpočíta, čím sa získa pole ako je toto:
(0; 1; 0; 0; 0; 0; 0; 1; 0)
Všimnite si, že každá 1 v poli (tj položky, ktoré sa objavia iba raz) bola prevedená na nulu.
Ďalej SUMPRODUCT pridá prvky v tomto poli a vráti výsledok, ktorým je v tomto prípade číslo 2, ktoré sa potom testuje na hodnotu> 0.
Kedykoľvek zoznam obsahuje duplikáty, budú v poli minimálne dve jedničky sčítané pomocou SUMPRODUCT, takže konečný výsledok hodnoty TRUE znamená, že zoznam obsahuje duplikáty.
Zaobchádzanie s prázdnymi bunkami
Prázdne bunky v rozsahu spôsobia, že vyššie uvedený vzorec vygeneruje nesprávne výsledky. Ak chcete odfiltrovať prázdne alebo prázdne bunky, môžete použiť nasledujúcu alternatívu:
=SUMPRODUCT((COUNTIF(list,list)-1)*(list""))>0
Tu používame zoznam logických výrazov na vynútenie všetkých hodnôt spojených s prázdnymi bunkami na nulu.
