Bendroji formulė
=SUMPRODUCT(1/COUNTIF(data,data))
Santrauka
Norėdami suskaičiuoti unikalių reikšmių skaičių langelių diapazone, galite naudoti formulę, pagrįstą funkcijomis COUNTIF ir SUMPRODUCT. Parodos pavyzdyje F6 formulė yra:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Paaiškinimas
Dirbdamas iš vidaus, COUNTIF sukonfigūruotas pagal B5: B14 diapazono reikšmes, kaip kriterijus naudojant visas šias vertes:
COUNTIF(B5:B14,B5:B14)
Pateikdami 10 kriterijų reikšmių, gausime masyvą su 10 tokių rezultatų:
(3;3;3;2;2;3;3;3;2;2)
Kiekvienas skaičius reiškia skaičių - „Jim“ pasirodo 3 kartus, „Sue“ - 2 kartus ir pan.
Šis masyvas sukonfigūruotas kaip daliklis, kurio skaitiklis yra 1. Po padalijimo gauname kitą masyvą:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Visos vertės, atsirandančios tik vieną kartą diapazone, bus rodomos kaip 1s, tačiau kelis kartus įvykusios reikšmės bus rodomos kaip dalinės vertės, atitinkančios daugiklį. (ty reikšmė, kuri 4 kartus parodoma duomenyse, sukurs 4 reikšmes = 0,25).
Galiausiai funkcija SUMPRODUCT susumuoja visas masyvo reikšmes ir pateikia rezultatą.
Tuščių langelių tvarkymas
Vienas iš būdų tvarkyti tuščias arba tuščias langelius yra koreguoti formulę taip:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Sujungdami tuščią eilutę ("") prie duomenų, mes neleidžiame nuliams patekti į COUNTIF sukurtą masyvą, kai duomenyse yra tuščių langelių. Tai yra svarbu, nes nulyje dalikliu formulė sukels klaidą # DIV / 0. Tai veikia, nes tuščios eilutės ("") naudojimas pagal kriterijus skaičiuos tuščias langelius.
Tačiau nors ši formulės versija neišmes # DIV / 0 klaidos, kai bus tuščių langelių, į skaičių bus įtraukta tuščių langelių. Jei norite neįtraukti tuščių langelių iš skaičiaus, naudokite:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Tai panaikina tuščių langelių skaičių padarius skaitiklį nulį susijusiems skaičiams.
Lėtas pasirodymas?
Tai puiki ir elegantiška formulė, tačiau ji skaičiuojama daug lėčiau nei formulės, kurios naudoja FREQUENCY unikalių verčių skaičiavimui. Jei norite rinktis didesnius duomenų rinkinius, galite pereiti prie formulės, pagrįstos funkcija FREQUENCY. Čia pateikiama skaitinių reikšmių formulė ir viena teksto reikšmių formulė.
