Formula generică
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
rezumat
Pentru a număra valori unice cu una sau mai multe condiții, puteți utiliza o formulă bazată pe UNIQUE și FILTER. În exemplul prezentat, formula din H7 este:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
care returnează 3, deoarece există trei nume unice în B6: B15 asociate cu proiectul Omega.
Notă: această formulă necesită formule din matrice dinamică, disponibile numai în Excel 365. Cu o versiune mai veche de Excel, puteți utiliza formule alternative mai complexe.
Explicaţie
La bază, această formulă utilizează funcția UNIQUE pentru a extrage valori unice, iar funcția FILTER aplică criterii.
Funcționând din interior spre exterior, funcția FILTER este utilizată pentru a aplica criterii și pentru a extrage numai nume care sunt asociate cu proiectul „Omega”:
FILTER(B6:B15,C6:C15=H6) // Omega names only
Rezultatul de la FILTER este o matrice ca aceasta:
("Jim";"Jim";"Carl";"Sue";"Carl")
Apoi, funcția UNIQUE este utilizată pentru a elimina duplicatele:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
ceea ce are ca rezultat o nouă matrice ca aceasta:
("Jim";"Carl";"Sue") // after UNIQUE
În acest moment, avem o listă unică de nume asociate cu Omega și trebuie doar să le numărăm. Din motivele explicate mai jos, facem acest lucru cu funcția LEN și funcția SUM. Pentru a clarifica lucrurile, mai întâi vom rescrie formula pentru a include lista unică:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Funcția LEN obține lungimea fiecărui element din listă și returnează o serie de lungimi:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Apoi, verificăm dacă lungimile sunt mai mari decât zero:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Și utilizați un negativ dublu pentru a constrânge valorile TRUE și FALSE la 1s și 0s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
În cele din urmă, adăugăm rezultatele cu funcția SUM:
=SUM((1;1;1)) // returns 3
Această matrice este livrată direct la funcția COUNTA, care returnează un număr final:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Rețineți că, deoarece verificăm lungimea fiecărui articol returnat de UNIQUE, celulele goale sau goale care îndeplinesc criteriile sunt ignorate. Această formulă este dinamică și se va recalcula imediat dacă se modifică datele sursă.
Numărați unic cu mai multe criterii
Pentru a număra valori unice bazate pe mai multe criterii, puteți extinde logica „include” din FILTRU. De exemplu, pentru a număra nume unice pentru proiectul Omega numai în iunie, utilizați:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Acesta este un exemplu de utilizare a logicii booleene pentru a aplica mai multe condiții. Abordarea este explicată mai detaliat aici.
Pentru mai multe detalii, consultați acest videoclip de instruire: Cum se filtrează cu mai multe criterii.
COUNTA
Este posibil să scrieți o formulă mai simplă care răspunde la funcția COUNTA. Cu toate acestea, un avertisment important este acela că COUNTA va returna 1 atunci când nu există valori potrivite. Acest lucru se datorează faptului că funcția FILTER returnează o eroare atunci când nu există date care corespund criteriilor, iar această eroare ajunge să fie numărată de funcția COUNTA. Formula de bază COUNTA arată astfel:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Din nou, această formulă va returna 1 atunci când nu există date potrivite. De asemenea, va include celule goale care îndeplinesc criteriile. Formula bazată pe LEN și SUM este o opțiune mai bună.
Fără matrice dinamice
Dacă utilizați o versiune mai veche de Excel fără suport din matrice dinamică, puteți utiliza o formulă mai complexă. Pentru o discuție mai generală a alternativelor din matrice dinamică, consultați: Alternative la Dynamic Array Formulas.
