Formula generică
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
rezumat
Pentru a număra valorile unice ale textului într-un interval, puteți utiliza o formulă care utilizează mai multe funcții: FRECVENȚĂ, MATCH, ROW și SUMPRODUCT. În exemplul prezentat, formula din F5 este:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
care returnează 4, deoarece există 4 nume unice în B5: B14.
Notă: Un alt mod de a număra valori unice este utilizarea funcției COUNTIF. Aceasta este o formulă mult mai simplă, dar poate rula încet pe seturi de date mari. Cu Excel 365, puteți utiliza o formulă mai simplă și mai rapidă bazată pe UNIQUE.
Explicaţie
Această formulă este mai complicată decât o formulă similară care folosește FREQUENCY pentru a număra valori numerice unice, deoarece FREQUENCY nu funcționează cu valori non-numerice. Ca rezultat, o mare parte a formulei transformă pur și simplu datele non-numerice în date numerice pe care FRECVENȚA le poate gestiona.
Funcționând din interior spre exterior, funcția MATCH este utilizată pentru a obține poziția fiecărui element care apare în date:
MATCH(B5:B14,B5:B14,0)
Rezultatul de la MATCH este o matrice ca aceasta:
(1;1;1;4;4;6;6;6;9;9)
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 3 ori în listă, apare în această matrice de 3 ori ca numărul 1.
Această matrice este introdusă în FREQUENCY ca argument data_array . Bins_array Argumentul este construit din această parte a formulei:
ROW(B5:B14)-ROW(B5)+1)
care construiește o listă secvențială de numere pentru fiecare valoare din date:
(1;2;3;4;5;6;7;8;9;10)
În acest moment, FREQUENCY este configurat astfel:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY returnează o matrice de numere care indică un număr pentru fiecare număr din matricea de date, organizat după bin. Când un număr a fost deja numărat, FREQUENCY va reveni la zero. Aceasta este o caracteristică cheie în funcționarea acestei formule. Rezultatul din FREQUENCY este o matrice ca aceasta:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Notă: FREQUENCY returnează întotdeauna o matrice cu încă un articol decât bins_array .
Acum putem rescrie formula astfel:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Apoi, verificăm valori mai mari decât zero (> 0), care convertește numerele în ADEVĂRAT sau FALS, apoi folosim un negativ dublu (-) pentru a converti valorile ADEVĂRATE și FALSE la 1s și 0s. Acum avem:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
În cele din urmă, SUMPRODUCT pur și simplu adaugă numerele și returnează totalul, care în acest caz este 4.
Manipularea celulelor goale
Celulele goale din interval vor determina formula să returneze o eroare # N / A. Pentru a gestiona celulele goale, puteți utiliza o formulă de matrice mai complicată care utilizează funcția IF pentru a filtra valorile goale:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Notă: adăugarea IF face acest lucru într-o formulă matrice care necesită control-shift-enter.
Pentru mai multe informații, consultați această pagină.
Alte modalități de numărare a valorilor unice
Dacă aveți Excel 365, puteți utiliza funcția UNIQUE pentru a număra valori unice cu o formulă mult mai simplă.
Un tabel pivot este, de asemenea, un mod excelent de a număra valori unice.
