Formula generică
=SUMPRODUCT(1/COUNTIF(data,data))
rezumat
Pentru a număra numărul de valori unice dintr-un interval de celule, puteți utiliza o formulă bazată pe funcțiile COUNTIF și SUMPRODUCT. În exemplul de prezentare, formula din F6 este:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Explicaţie
Funcționând din interior spre exterior, COUNTIF este configurat pentru valori din intervalul B5: B14, folosind toate aceste valori ca criterii:
COUNTIF(B5:B14,B5:B14)
Deoarece oferim 10 valori pentru criterii, obținem înapoi o matrice cu 10 rezultate de acest fel:
(3;3;3;2;2;3;3;3;2;2)
Fiecare număr reprezintă un număr - „Jim” apare de 3 ori, „Sue” apare de 2 ori și așa mai departe.
Această matrice este configurată ca divizor cu 1 ca numărător. După divizare, obținem o altă matrice:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Orice valori care apar doar o singură dată în interval vor apărea ca 1s, dar valorile care apar de mai multe ori vor apărea ca valori fracționare care corespund multiplului. (adică o valoare care apare de 4 ori în date va genera 4 valori = 0,25).
În cele din urmă, funcția SUMPRODUCT însumează toate valorile din matrice și returnează rezultatul.
Manipularea celulelor goale
O modalitate de a gestiona celulele goale sau goale este de a ajusta formula după cum urmează:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Concatenând un șir gol ("") la date, împiedicăm ca zero-urile să se termine în matricea creată de COUNTIF atunci când există celule goale în date. Acest lucru este important, deoarece un zero din divizor va determina formula să arunce o eroare # DIV / 0. Funcționează deoarece utilizarea unui șir gol ("") pentru criterii va conta celulele goale.
Cu toate acestea, deși această versiune a formulei nu va arunca o eroare # DIV / 0 atunci când este cu celule goale, va include celule goale în numărare. Dacă doriți să excludeți celule goale din număr, utilizați:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Acest lucru are ca efect anularea numărului de celule goale, făcând numeratorul zero pentru numărările asociate.
Performanță lentă?
Aceasta este o formulă răcoroasă și elegantă, dar se calculează mult mai lent decât formulele care utilizează FREQUENCY pentru a număra valori unice. Pentru seturi de date mai mari, poate doriți să treceți la o formulă bazată pe funcția FREQUENCY. Iată o formulă pentru valorile numerice și una pentru valorile textului.
