Skaičiuokite unikalias reikšmes diapazone naudodami COUNTIF

Norėdami suskaičiuoti unikalių reikšmių skaičių ląstelių diapazone, galite naudoti formulę, pagrįstą COUNTIF ir SUMPRODUCT funkcijas. Rodomame pavyzdyje F6 formulė yra tokia:

= SUMPRODUCT (1/ COUNTIF (data,data))

Dirbant iš vidaus, COUNTIF yra sukonfigūruotas pagal B5: B14 diapazono vertes, naudojant visas šias tas pats vertės kaip kriterijai:

 
= SUMPRODUCT (1/ COUNTIF (B5:B14,B5:B14))

Kadangi kriterijams pateikiame 10 reikšmių, gauname an masyvas su 10 tokių rezultatų:

 
 COUNTIF (B5:B14,B5:B14)

Kiekvienas skaičius reiškia skaičių - „Jim“ pasirodo 3 kartus, „Sue“ - 2 kartus ir pan.

Šis masyvas sukonfigūruotas kaip daliklis, o skaitiklis - 1. Po padalijimo gauname kitą masyvą:

 
{3332233322}

Visos reikšmės, atsirandančios tik vieną kartą diapazone, bus rodomos kaip 1 s, tačiau reikšmės, pasikartojančios kelis kartus, bus rodomos kaip trupmeninės vertės, atitinkančios kartotinį. (t. y. reikšmė, kuri duomenyse pasirodo 4 kartus, sugeneruos 4 reikšmes = 0,25).

Galiausiai funkcija SUMPRODUCT susumuoja visas masyvo reikšmes ir grąžina rezultatą.

„Excel“ pasirinktinio numerio formato kelios sąlygos

Tuščių langelių tvarkymas

Vienas iš būdų tvarkyti tuščius arba tuščius langelius yra koreguoti formulę taip:

 
{0.3333333333333330.3333333333333330.3333333333333330.50.50.3333333333333330.3333333333333330.3333333333333330.50.5}

Iki sujungimas an tuščia eilutė ('') prie duomenų, mes neleidžiame nuliams patekti į COUNTIF sukurtą masyvą, kai duomenyse yra tuščių langelių. Tai svarbu, nes nulis daliklyje sukels #DIV/0 klaidą. Tai veikia, nes kriterijams naudojant tuščią eilutę ('') bus skaičiuojami tušti langeliai.

Tačiau, nors ši formulės versija neišmes #DIV/0 klaidos, kai bus tuščios ląstelės, ji valia į skaičių įtraukite tuščias ląsteles. Jei norite neįtraukti tuščių langelių į skaičių, naudokite:

 
= SUMPRODUCT (1/ COUNTIF (data,data&''))

Dėl to tuščių langelių skaičius atšaukiamas, o susietų skaičių skaitiklis tampa lygus nuliui.

Lėtas našumas?

Tai šauni ir elegantiška formulė, tačiau ji skaičiuojama daug lėčiau nei formulės, kurios naudoja FREQUENCY, kad skaičiuotų unikalias vertes. Jei norite didesnių duomenų rinkinių, galbūt norėsite pereiti prie formulės, pagrįstos FREQUENCY funkcija. Čia yra formulė skaitinės vertės , ir vienas skirtas teksto vertes .