Üldine valem
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Kokkuvõte
Ainulaadsete tekstiväärtuste loendamiseks vahemikus võite kasutada valemit, mis kasutab mitut funktsiooni: FREQUENCY, MATCH, ROW ja SUMPRODUCT. Näidatud näites on valem F5-s järgmine:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
mis tagastab 4, kuna B5-s on 4 kordumatut nime: B14.
Märkus. Teine võimalus unikaalsete väärtuste loendamiseks on funktsiooni COUNTIF kasutamine. See on palju lihtsam valem, kuid suurte andmekogumite korral võib see töötada aeglaselt. Exceli 365 abil saate kasutada lihtsamat ja kiiremat valemit, mis põhineb UNIKAALSEL.
Selgitus
See valem on keerulisem kui sarnane valem, mis kasutab kordumatute arvväärtuste loendamiseks funktsiooni FREQUENCY, kuna FREQUENCY ei tööta mittearvuliste väärtustega. Selle tulemusel muudab suur osa valemist mittearvulised andmed lihtsalt arvandmeteks, millega FREQUENCY saab hakkama.
Seest väljapoole töötades kasutatakse funktsiooni MATCH, et saada iga üksus, mis kuvatakse andmetes:
MATCH(B5:B14,B5:B14,0)
MATCHi tulemus on selline massiiv:
(1;1;1;4;4;6;6;6;9;9)
Kuna MATCH tagastab alati esimese vaste positsiooni, tagastavad andmetes rohkem kui üks kord kuvatud väärtused sama positsiooni. Näiteks, kuna "Jim" ilmub loendis 3 korda, ilmub ta selles massiivis kolm korda numbrina 1.
See massiiv sisestatakse argumendina data_array sagedusele FREQUENCY . Bins_array argument on ehitatud see osa valemiga:
ROW(B5:B14)-ROW(B5)+1)
mis loob andmetes iga väärtuse järjestikuse loendi:
(1;2;3;4;5;6;7;8;9;10)
Sel hetkel on FREQUENCY konfigureeritud järgmiselt:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY tagastab arvude massiivi, mis näitab andmemassiivi iga numbri arvu, korraldatuna prügikasti järgi. Kui number on juba loendatud, tagastab FREQUENCY nulli. See on selle valemi toimimise põhijoon. FREQUENCY tulemuseks on selline massiiv:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Märkus. FREQUENCY tagastab alati massiivi, millel on üks üksus rohkem kui bin-array .
Nüüd saame valemi ümber kirjutada järgmiselt:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Järgmisena kontrollime väärtusi, mis on suuremad kui null (> 0), mis teisendab arvud tõeks või vääraks, seejärel kasutame topeltnegatiivset (-), et teisendada TÕENE ja VALE väärtus 1-ks ja 0-ks. Nüüd on meil:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Lõpuks liidab SUMPRODUCT lihtsalt numbrid kokku ja tagastab summa, mis on antud juhul 4.
Tühjade lahtrite käitlemine
Vahemikus olevad tühjad lahtrid tagastavad valemi # N / A tõrke. Tühjade lahtrite käsitsemiseks võite kasutada keerukamat massiivalemit, mis tühjade väärtuste filtreerimiseks kasutab funktsiooni IF.
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Märkus: IF lisamine muudab selle massiivivalemiks, mis nõuab kontrolli-shift-sisestamist.
Lisateavet leiate sellelt lehelt.
Muud võimalused kordumatute väärtuste loendamiseks
Kui teil on Excel 365, saate unikaalsete väärtuste palju lihtsama valemiga lugemiseks kasutada funktsiooni UNIQUE.
Pöördtabel on ka suurepärane viis ainulaadsete väärtuste loendamiseks.
