Formula generică
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
rezumat
Pentru a număra valori de text unice într-un interval cu criterii, puteți utiliza o formulă matrice bazată pe funcțiile FRECVENȚĂ și MATCH. În exemplul prezentat, formula din G6 este:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
care returnează 3, deoarece trei persoane diferite au lucrat la proiectul Omega.
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Explicaţie
Aceasta este o formulă complexă care folosește FRECVENȚA pentru a număra valorile numerice care sunt derivate cu funcția MATCH. Funcționând din interior spre exterior, funcția MATCH este utilizată pentru a obține poziția fiecărei valori care apare în date:
MATCH(B5:B11,B5:B11,0)
Rezultatul de la MATCH este o matrice ca aceasta:
(1;1;3;1;1;6;7)
Deoarece MATCH returnează întotdeauna poziția primei potriviri, valorile care apar de mai multe ori în date returnează aceeași poziție. De exemplu, deoarece „Jim” apare de 4 ori în listă, apare în această matrice de 4 ori ca numărul 1.
În afara funcției MATCH, funcția IF este utilizată pentru a aplica criterii, care în acest caz implică testarea dacă proiectul este „omega” (din celula G5):
IF(C5:C11=G5 // filter on "omega"
Funcția IF acționează ca un filtru, permițând valorilor din MATCH să treacă numai dacă sunt asociate cu „omega”. Rezultatul este o matrice ca aceasta:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Matricea filtrată este livrată direct la funcția FREQUENCY ca argument data_array . Apoi, funcția ROW este utilizată pentru a construi o listă secvențială de numere pentru fiecare valoare din date:
ROW(B3:B12)-ROW(B3)+1
Aceasta creează o matrice ca aceasta:
(1;2;3;4;5;6;7;8;9;10)
care devine argumentul bins_array în FILTRU. În acest moment, avem:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY returnează o matrice de numere care indică un număr pentru fiecare valoare din matricea de date, organizată după bin. Când un număr a fost deja numărat, FREQUENCY va reveni la zero. Rezultatul din FREQUENCY este o matrice ca aceasta:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Notă: FREQUENCY returnează întotdeauna o matrice cu încă un articol decât bins_array .
În acest moment, putem rescrie formula astfel:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Verificăm valori mai mari decât zero, care transformă numerele în ADEVĂRAT sau FALS:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Apoi, folosim un negativ dublu pentru a constrânge valorile logice la 1s și 0s:
=SUM((1;0;0;0;0;1;1;0))
În cele din urmă, funcția SUM returnează 3 ca rezultat final.
Notă: aceasta este o formulă matrice și trebuie introdusă folosind Control + Shift + Enter.
Manevrarea celulelor goale din interval
Dacă celulele din interval sunt goale, va trebui să ajustați formula pentru a preveni trecerea celulelor goale în funcția MATCH, ceea ce va arunca o eroare. Puteți face acest lucru adăugând o altă funcție IF imbricată pentru a verifica celulele goale:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Cu două criterii
Dacă aveți două criterii, puteți extinde logica formulei adăugând un alt IF imbricat:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Unde c1 = criterii1, c2 = criterii2 și vals = intervalul de valori.
Cu logică booleană
Cu logica booleană, puteți reduce IF-urile imbricate:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Acest lucru facilitează adăugarea și gestionarea unor criterii suplimentare.
