Üldine valem
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Kokkuvõte
Ainulaadsete arvväärtuste loendamiseks vahemikus saate kasutada funktsioone FREQUENCY, SUM ja IF põhinevat valemit. Näidatud näites kuvatakse töötajate arv vahemikus B5: B14. G6 valem on:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
mis tagastab 2, kuna A-hoones on 2 unikaalset töötajate ID-d.
Märkus: see on massiivivalem ja see tuleb sisestada juhtklahviga + Tõstuklahv + Sisesta, välja arvatud juhul, kui kasutate Excel 365.
Selgitus
Märkus. Enne Excel 365 ei olnud Excelis unikaalsete väärtuste loendamiseks spetsiaalset funktsiooni. See valem näitab ainulaadsete väärtuste loendamise viisi, kui need on arvulised. Kui teil on tekstiväärtusi või segu tekstist ja numbritest, peate kasutama keerulisemat valemit.
Funktsioon Exceli sagedus tagastab sagedusjaotuse, mis on kokkuvõtlik tabel, mis sisaldab arvväärtuste sagedust, korraldatuna "prügikastides". Kasutame seda siin ringristmikuna unikaalsete arvväärtuste loendamiseks. Kriteeriumide rakendamiseks kasutame funktsiooni IF.
Seestpoolt välja töötades filtreerime kõigepealt väärtused funktsiooniga IF:
IF(C5:C14="A",B5:B14) // filter on building A
Selle toimingu tulemuseks on selline massiiv:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Pange tähele, et kõik hoones B olevad ID-d on nüüd FALSE. See massiiv edastatakse otse funktsioonile FREQUENCY kui andmemõõt . Sest bins_array , pakume IDS ise:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Selle konfiguratsiooni korral tagastab FREQUENCY allpool oleva massiivi:
(4;0;0;0;2;0;0;0;0;0;0)
Tulemus on natuke krüptiline, kuid tähendus on 905 neli korda ja 773 kaks korda. Väärtusi FALSE ignoreeritakse automaatselt.
FREQUENCY-l on erifunktsioon, mis tagastab kõigi andmemassiivis juba ilmunud numbrite korral automaatselt nulli, mistõttu on väärtused nulli sattunud, kui number on juba tekkinud. See on funktsioon, mis võimaldab seda lähenemist töötada.
Järgmisena testitakse, et kõik need väärtused on suuremad kui null:
(4;0;0;0;2;0;0;0;0;0;0)>0
Tulemuseks on selline massiiv:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Iga loendis olev TÕENE tähistab loendis ainulaadset numbrit ja peame lihtsalt TÕENE väärtused summaliselt kokku lisama. Kuid SUM ei liida massiivi loogilisi väärtusi, seega peame kõigepealt sundima väärtused 1 või nulliks. Seda tehakse topeltnegatiiviga (-). Tulemuseks on ainult ühe või 0 massiiv:
(1;0;0;0;1;0;0;0;0;0;0)
Lõpuks liidab SUM need väärtused kokku ja tagastab summa, mis antud juhul on 2.
Mitu kriteeriumi
Valemit saab laiendada mitme sellise kriteeriumi käsitlemiseks:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
