Üldine valem
=SUMPRODUCT(1/COUNTIF(data,data))
Kokkuvõte
Lahtrivahemikus olevate unikaalsete väärtuste arvu lugemiseks võite kasutada funktsioone COUNTIF ja SUMPRODUCT põhinevat valemit. Näites on F6 valem:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Selgitus
Seestpoolt edasi töötades on COUNTIF konfigureeritud väärtuseks vahemikus B5: B14, kasutades kriteeriumidena kõiki neid samu väärtusi:
COUNTIF(B5:B14,B5:B14)
Kuna pakume kriteeriumidele 10 väärtust, saame tagasi 10 sellise tulemusega massiivi:
(3;3;3;2;2;3;3;3;2;2)
Iga number tähistab loendust - "Jim" ilmub 3 korda, "Sue" ilmub 2 korda jne.
See massiiv on konfigureeritud jagurina, mille lugeja on 1. Pärast jagamist saame veel ühe massiivi:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Kõik väärtused, mis esinevad vahemikus vaid ühe korra, kuvatakse kui 1s, kuid mitu korda esinevad väärtused kuvatakse murdarvudena. (st väärtus, mis kuvatakse andmetes 4 korda, genereerib 4 väärtust = 0,25).
Lõpuks summeerib funktsioon SUMPRODUCT kõik massiivi väärtused ja tagastab tulemuse.
Tühjade lahtrite käitlemine
Üks võimalus tühjade või tühjade lahtrite käsitsemiseks on valemi kohandamine järgmiselt:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Liites andmetele tühja stringi ("") väldime nullide sattumist COUNTIFi loodud massiivi, kui andmetes on tühjad lahtrid. See on oluline, sest null jaguris põhjustab valemi # DIV / 0 vea. See töötab, sest kriteeriumide jaoks tühja stringi ("") kasutamine loeb tühjad lahtrid.
Kuigi see valemi versioon ei viska tühjade lahtritega viga # DIV / 0, lisab see loendisse tühjad lahtrid. Kui soovite tühjad lahtrid loendist välja jätta, kasutage järgmist:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
See tühistab tühjade lahtrite arvu, muutes lugeja seotud loenduste jaoks nulliks.
Aeglane jõudlus?
See on lahe ja elegantne valem, kuid see arvutab palju aeglasemalt kui valemid, mis unikaalsete väärtuste loendamiseks kasutavad Sagedust. Suuremate andmekogumite puhul võiksite minna valemile, mis põhineb funktsioonil FREQUENCY. Siin on arvväärtuste valem ja üks tekstiväärtuste jaoks.
