Bendroji formulė
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Santrauka
Norėdami suskaičiuoti unikalias teksto reikšmes diapazone su kriterijais, galite naudoti masyvo formulę, pagrįstą funkcijomis FREQUENCY ir MATCH. Parodytame pavyzdyje formulė G6 yra:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
kuris grąžina 3, nes prie projekto „Omega“ dirbo trys skirtingi žmonės.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.
Paaiškinimas
Tai yra sudėtinga formulė, pagal kurią FREQUENCY skaičiuojamos skaitinės vertės, gautos naudojant funkciją MATCH. Dirbant iš vidaus, MATCH funkcija naudojama kiekvienai reikšmei, kuri rodoma duomenyse, gauti:
MATCH(B5:B11,B5:B11,0)
„MATCH“ rezultatas yra toks masyvas:
(1;1;3;1;1;6;7)
Kadangi „MATCH“ visada grąžina pirmosios atitikties poziciją, reikšmės, kurios duomenyse rodomos daugiau nei vieną kartą, grąžina tą pačią poziciją. Pavyzdžiui, kadangi „Džimas“ sąraše pasirodo 4 kartus, jis šiame masyve pasirodo 4 kartus kaip skaičius 1.
Už MATCH funkcijos kriterijų taikymui naudojama IF funkcija, kuri šiuo atveju apima testavimą, jei projektas yra „omega“ (iš ląstelės G5):
IF(C5:C11=G5 // filter on "omega"
IF funkcija veikia kaip filtras, leidžianti pereiti MATCH reikšmes tik tuo atveju, jei jos yra susijusios su „omega“. Rezultatas yra toks masyvas:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Filtruotas masyvas pateikiamas tiesiai į funkciją FREQUENCY kaip argumentas „ data_array“ . Tada funkcija ROW naudojama nuosekliam kiekvienos duomenų vertės skaičių sąrašui sukurti:
ROW(B3:B12)-ROW(B3)+1
Tai sukuria tokį masyvą:
(1;2;3;4;5;6;7;8;9;10)
kuris tampa „ Bins_array“ argumentu „FILTER“. Šiuo metu mes turime:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
„FREQUENCY“ pateikia skaičių masyvą, nurodantį kiekvienos duomenų masyvo vertės skaičių, suskirstytą pagal šiukšliadėžę. Kai skaičius jau suskaičiuotas, FREQUENCY grąžins nulį. „FREQUENCY“ rezultatas yra toks masyvas:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Pastaba: FREQUENCY visada pateikia masyvą, kuriame yra dar vienas elementas nei „ bins_array“ .
Šiuo metu formulę galime perrašyti taip:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Mes tikriname, ar reikšmės yra didesnės už nulį, o tai konvertuoja skaičius į TRUE arba FALSE:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Tada mes naudojame dvigubą neigiamą vertę, kad loginės vertės būtų 1 ir 0:
=SUM((1;0;0;0;0;1;1;0))
Galiausiai funkcija SUM grąžina 3 kaip galutinį rezultatą.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti naudojant „Control“ + „Shift“ + „Enter“.
Tuščių langelių diapazone tvarkymas
Jei kuri nors diapazono ląstelė yra tuščia, turėsite pakoreguoti formulę, kad tuščios ląstelės nebūtų perduotos funkcijai MATCH, o tai sukels klaidą. Tai galite padaryti pridėdami kitą įdėtą IF funkciją, kad patikrintumėte, ar nėra tuščių langelių:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Su dviem kriterijais
Jei turite du kriterijus, galite išplėsti formulės logiką pridėdami dar vieną įdėtą IF:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Kur c1 = kriterijai1, c2 = kriterijai2 ir vals = reikšmių diapazonas.
Su logine logika
Naudodami loginę logiką, galite sumažinti įdėtus IF:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Tai leidžia lengviau pridėti ir valdyti papildomus kriterijus.
