Bendroji formulė
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Santrauka
Norėdami suskaičiuoti unikalias teksto reikšmes diapazone, galite naudoti formulę, kurioje naudojamos kelios funkcijos: FREQUENCY, MATCH, ROW ir SUMPRODUCT. Parodytame pavyzdyje formulė F5 yra:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
kuris grąžina 4, nes B5 yra 4 unikalūs pavadinimai: B14.
Pastaba: Kitas būdas suskaičiuoti unikalias reikšmes yra naudoti funkciją COUNTIF. Tai yra daug paprastesnė formulė, tačiau ji gali veikti lėtai dideliuose duomenų rinkiniuose. Naudodami „Excel 365“ galite naudoti paprastesnę ir greitesnę formulę, pagrįstą UNIKALIA.
Paaiškinimas
Ši formulė yra sudėtingesnė nei panaši formulė, kuri naudoja FREQUENCY unikalių skaitinių reikšmių skaičiavimui, nes FREQUENCY neveikia ne skaitmeninėmis reikšmėmis. Todėl didelė formulės dalis ne skaitmeninius duomenis paprasčiausiai paverčia skaitmeniniais duomenimis, kuriuos FREQUENCY gali valdyti.
Dirbant iš vidaus, MATCH funkcija naudojama norint gauti kiekvieno elemento, kuris rodomas duomenyse, padėtį:
MATCH(B5:B14,B5:B14,0)
„MATCH“ rezultatas yra toks masyvas:
(1;1;1;4;4;6;6;6;9;9)
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 3 kartus, jis šiame masyve rodomas 3 kartus kaip skaičius 1.
Šis masyvas įtraukiamas į FREQUENCY kaip argumentą „ data_array“ . Bins_array argumentas yra pagaminta iš šios formulės dalis:
ROW(B5:B14)-ROW(B5)+1)
kuris sudaro nuoseklų skaičių sąrašą kiekvienai duomenų reikšmei:
(1;2;3;4;5;6;7;8;9;10)
Šiuo metu FREQUENCY sukonfigūruojamas taip:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY pateikia skaičių masyvą, nurodantį kiekvieno skaičiaus skaičių duomenų masyve, suskirstytą pagal šiukšliadėžę. Kai skaičius jau suskaičiuotas, FREQUENCY grąžins nulį. Tai yra pagrindinis šios formulės veikimo bruožas. „FREQUENCY“ rezultatas yra toks masyvas:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Pastaba: FREQUENCY visada pateikia masyvą, kuriame yra dar vienas elementas nei „ bins_array“ .
Dabar galime perrašyti tokią formulę:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Tada mes patikriname, ar nėra didesnių nei nulis (> 0) verčių, kurios paverčia skaičius į TRUE arba FALSE, tada dvigubai neigiamai (-) paverčiame TRUE ir FALSE reikšmes į 1s ir 0s. Dabar mes turime:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Galiausiai, SUMPRODUCT paprasčiausiai susumuoja skaičius ir grąžina sumą, kuri šiuo atveju yra 4.
Tuščių langelių tvarkymas
Ištuštinus diapazono langelius, formulė pateiks klaidą # N / A. Norėdami tvarkyti tuščius langelius, galite naudoti sudėtingesnę masyvo formulę, kuri naudoja IF funkciją, kad filtruotų tuščias reikšmes:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Pastaba: pridėjus IF, tai padaroma į masyvo formulę, kuriai reikia „control-shift-enter“.
Daugiau informacijos rasite šiame puslapyje.
Kiti unikalių verčių skaičiavimo būdai
Jei turite „Excel 365“, galite naudoti funkciją UNIKALA, kad suskaičiuotumėte unikalias reikšmes naudodami daug paprastesnę formulę.
„Pivot“ lentelė taip pat yra puikus būdas suskaičiuoti unikalias vertes.
