Formula generică
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
rezumat
Pentru a număra valori numerice unice într-un interval, puteți utiliza o formulă bazată pe funcțiile FRECVENȚĂ, SUMĂ și IF. În exemplul prezentat, numărul angajaților apare în intervalul B5: B14. Formula din G6 este:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
care returnează 2, deoarece există 2 coduri unice de angajați în clădirea A.
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter, cu excepția cazului în care utilizați Excel 365.
Explicaţie
Notă: Înainte de Excel 365, Excel nu avea o funcție dedicată de numărare a valorilor unice. Această formulă arată o modalitate unică de a număra valori unice, atâta timp cât acestea sunt numerice. Dacă aveți valori de text sau un amestec de text și numere, va trebui să utilizați o formulă mai complicată.
Funcția Excel FREQUENCY returnează o distribuție a frecvenței, care este un tabel rezumat care conține frecvența valorilor numerice, organizate în „coșuri”. Îl folosim aici ca un mod giratoriu de a număra valori numerice unice. Pentru a aplica criterii, folosim funcția IF.
Lucrând din interior spre exterior, filtrăm mai întâi valorile cu funcția IF:
IF(C5:C14="A",B5:B14) // filter on building A
Rezultatul acestei operații este un tablou de genul acesta:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Observați că toate codurile din clădirea B sunt acum FALSE. Această matrice este livrată direct funcției FREQUENCY ca data_array . Pentru bins_array , furnizăm singuri ID-urile:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Cu această configurație, FREQUENCY returnează matricea de mai jos:
(4;0;0;0;2;0;0;0;0;0;0)
Rezultatul este puțin criptic, dar sensul este 905 apare de patru ori, iar 773 apare de două ori. Valorile FALSE sunt ignorate automat.
FREQUENCY are o caracteristică specială care returnează automat zero pentru orice numere care au apărut deja în matricea de date, motiv pentru care valorile sunt zero după ce a fost întâlnit un număr. Aceasta este caracteristica care permite această abordare să funcționeze.
Apoi, fiecare dintre aceste valori este testată pentru a fi mai mare decât zero:
(4;0;0;0;2;0;0;0;0;0;0)>0
Rezultatul este o matrice ca aceasta:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Fiecare ADEVĂRAT din listă reprezintă un număr unic în listă și trebuie doar să adăugăm valorile ADEVĂRATE cu SUM. Cu toate acestea, SUM nu va adăuga valori logice într-o matrice, deci trebuie mai întâi să constrângem valorile în 1 sau zero. Acest lucru se face cu negativul dublu (-). Rezultă o matrice de doar 1 sau 0:
(1;0;0;0;1;0;0;0;0;0;0)
În cele din urmă, SUM adaugă aceste valori și returnează totalul, care în acest caz este 2.
Criterii multiple
Puteți extinde formula pentru a gestiona mai multe criterii ca aceasta:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
