Üldine valem
=SUMPRODUCT(COUNTIF(data,data)-1)>0
Kokkuvõte
Kas vahemik sisaldab duplikaatväärtusi? Kui soovite duplikaatide vahemikku (või loendit) testida, saate seda teha valemiga, mis kasutab COUNTIF-i koos SUMPRODUCT-iga.
Näites on nimede loend vahemikus B3: B11. Kui soovite seda loendit testida, et näha, kas duplikaatnimesid on, võite kasutada järgmist.
=SUMPRODUCT(COUNTIF(B3:B11,B3:B11)-1)>0
Selgitus
Seestpoolt välja töötades saab COUNTIF kõigepealt väärtuse B3: B11 kõigi väärtuste arvu vahemikus B3: B11. Kuna pakume kriteeriumide jaoks lahtri vahemikku (massiivi), tagastab COUNTIF tulemuseks arvude massiivi. Näidatud näites näeb see massiiv välja selline:
(1; 2; 1; 1; 1; 1; 1; 2; 1)
Järgmine 1 lahutatakse, mis annab sellise massiivi:
(0; 1; 0; 0; 0; 0; 0; 1; 0)
Pange tähele, et massiivi iga 1 (st üks kord ilmuvad üksused) on teisendatud nulliks.
Järgmisena lisab SUMPRODUCT selle massiivi elemendid ja tagastab tulemuse, milleks on antud juhul number 2, mida seejärel testitakse> 0 väärtuse suhtes.
Iga kord, kui loend sisaldab duplikaate, on massiivi SUMPRODUCT summeeritud vähemalt kaks 1-d, seega TRUE lõpptulemus tähendab, et loend sisaldab duplikaate.
Tühjade lahtrite käitlemine
Vahemikus olevad tühjad lahtrid põhjustavad ülaltoodud valemi põhjal valesid tulemusi. Tühjade või tühjade lahtrite filtreerimiseks võite kasutada järgmist alternatiivi:
=SUMPRODUCT((COUNTIF(list,list)-1)*(list""))>0
Siin kasutame loogiliste lausete loendit "", et sundida kõiki tühjade lahtritega seotud väärtusi nulli.
