Bendroji formulė
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Santrauka
Norėdami suskaičiuoti unikalias skaitines reikšmes diapazone, galite naudoti formulę, pagrįstą funkcijomis FREQUENCY, SUM ir IF. Parodytame pavyzdyje darbuotojų skaičius rodomas diapazone B5: B14. G6 formulė yra:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
kuri pateikia 2, nes A pastate yra 2 unikalūs darbuotojų ID.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control + Shift + Enter“, nebent naudojate „Excel 365“.
Paaiškinimas
Pastaba: Iki „Excel 365“ „Excel“ neturėjo specialios funkcijos unikalių reikšmių skaičiavimui. Ši formulė rodo vieną būdą suskaičiuoti unikalias reikšmes, jei jos yra skaitinės. Jei turite teksto reikšmes arba teksto ir skaičių derinį, turėsite naudoti sudėtingesnę formulę.
„Excel FREQUENCY“ funkcija pateikia dažnio pasiskirstymą, tai yra suvestinė lentelė, kurioje pateikiamas skaitinių reikšmių dažnis, išdėstytas „šiukšliadėžėse“. Čia mes naudojame kaip žiedinę sankryžą, norėdami suskaičiuoti unikalias skaitines vertes. Norėdami taikyti kriterijus, mes naudojame IF funkciją.
Dirbdami iš vidaus į išorę, mes pirmiausia filtruojame reikšmes su IF funkcija:
IF(C5:C14="A",B5:B14) // filter on building A
Šios operacijos rezultatas yra toks masyvas:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Atkreipkite dėmesį, kad visi B pastato ID dabar yra NETIESA. Šis masyvas pristatomas tiesiai į FREQUENCY funkciją kaip duomenų_diskai . „ Bins_array“ pateikiame pačius ID:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Naudojant šią konfigūraciją, FREQUENCY grąžina toliau pateiktą masyvą:
(4;0;0;0;2;0;0;0;0;0;0)
Rezultatas yra šiek tiek paslaptingas, tačiau reikšmė yra 905 rodoma keturis kartus, o 773 - du kartus. FALSE reikšmės automatiškai nepaisomos.
FREQUENCY turi specialią funkciją, kuri automatiškai grąžina nulį visiems duomenims, kurie jau atsirado duomenų masyve, todėl, kai tik susiduriama su skaičiumi, reikšmės yra lygios nuliui. Tai funkcija, leidžianti dirbti tokiu požiūriu.
Tada kiekviena iš šių verčių yra tikrinama kaip didesnė už nulį:
(4;0;0;0;2;0;0;0;0;0;0)>0
Rezultatas yra toks masyvas:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Kiekviena sąraše esanti TRUE reiškia unikalų skaičių sąraše, ir mes tiesiog turime pridėti TRUE reikšmes su SUM. Tačiau SUM nesumažins loginių reikšmių masyve, todėl pirmiausia turime priversti reikšmes į 1 arba nulį. Tai daroma su dvigubai neigiama (-). Rezultatas yra tik 1 arba 0 masyvas:
(1;0;0;0;1;0;0;0;0;0;0)
Galiausiai SUM susumuoja šias reikšmes ir pateikia bendrą sumą, kuri šiuo atveju yra 2.
Keli kriterijai
Galite išplėsti formulę, kad ji atitiktų kelis tokius kriterijus:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
