În acest tutorial, veți învăța cum să numărați valori unice în Excel folosind formule (funcții COUNTIF și SUMPRODUCT).
Cum se numără valorile unice în Excel
Să presupunem că avem un set de date așa cum se arată mai jos:
În scopul acestui tutorial, voi numi intervalul A2: A10 drept NUME. Înainte vom folosi acest interval numit în formule.
A se vedea, de asemenea: Cum se creează game numite în Excel.
În acest set de date, există o repetare în intervalul NAMES. Pentru a obține numărul de nume unice din acest set de date (A2: A10), putem folosi o combinație de funcții COUNTIF și SUMPRODUCT așa cum se arată mai jos:
= SUMPRODUCT (1 / COUNTIF (NUME, NUME))
Cum funcționează această formulă?
Să descompunem această formulă pentru a înțelege mai bine:
- COUNTIF (NUME, NUME)
- Această parte a formulei returnează o matrice. În exemplul de mai sus, ar fi {2; 2; 3; 1; 3; 1; 2; 3; 2}. Numerele de aici indică de câte ori apare o valoare în intervalul dat de celule.
De exemplu, numele este Bob, care apare de două ori în listă, prin urmare ar întoarce numărul 2 pentru Bob. În mod similar, Steve apare de trei ori și, prin urmare, 3 este returnat pentru Steve.
- Această parte a formulei returnează o matrice. În exemplul de mai sus, ar fi {2; 2; 3; 1; 3; 1; 2; 3; 2}. Numerele de aici indică de câte ori apare o valoare în intervalul dat de celule.
- 1 / COUNTIF (NUME, NUME)
- Această parte a formulei ar returna o matrice - {0,5; 0,5; 0,3333333333333333; 1; 0,3333333333333333; 1; 0,5; 0,3333333333333333; 0,5}
Deoarece am împărțit 1 la matrice, aceasta returnează această matrice.
De exemplu, primul element al matricei returnate mai sus a fost 2. Când 1 este împărțit la 2, acesta returnează .5.
- Această parte a formulei ar returna o matrice - {0,5; 0,5; 0,3333333333333333; 1; 0,3333333333333333; 1; 0,5; 0,3333333333333333; 0,5}
- SUMPRODUCT (1 / COUNTIF (NUME, NUME))
- SUMPRODUCT adaugă pur și simplu toate aceste numere. Rețineți că dacă Bob apare de două ori în listă, matricea de mai sus returnează 0,5 oriunde a apărut numele Bob în listă. În mod similar, deoarece Steve apare de trei ori în listă, matricea returnează .3333333 ori de câte ori apare numele Steve. Când adăugăm numerele pentru fiecare nume, acesta va reveni întotdeauna 1. Și dacă adăugăm toate numerele, acesta va returna numărul total de nume unice din listă.
Această formulă funcționează bine până când nu aveți celule goale în interval. Dar dacă aveți celule goale, acesta va întoarce un # DIV / 0! eroare.
Cum se manipulează celulele BLANK?
Să înțelegem mai întâi de ce returnează o eroare atunci când există o celulă goală în interval. Să presupunem că avem setul de date așa cum se arată mai jos (celula A3 fiind goală):
Acum, dacă folosim aceeași formulă pe care am folosit-o mai sus, partea COUNTIF a formulei returnează o matrice {2; 0; 3; 1; 3; 1; 2; 3; 1}. Deoarece nu există text în celula A3, numărul său este returnat ca 0.
Și din moment ce împărțim 1 la această întreagă matrice, acesta returnează un # DIV / 0! eroare.
Pentru a gestiona această eroare de divizare în cazul celulelor goale, utilizați formula de mai jos:
= SUMPRODUCT ((1 / COUNTIF (NUME, NUME & ””)))
O modificare pe care am adus-o acestei formule este partea de criterii a funcției COUNTIF. Am folosit NAMES & ”” în loc de NAMES. Procedând astfel, formula va returna numărul de celule goale (mai devreme a returnat 0 acolo unde era o celulă goală).
NOTĂ: Această formulă va număra celulele goale ca valoare unică și o va returna în rezultat.
În exemplul de mai sus, rezultatul ar trebui să fie 5, dar returnează 6 deoarece celula necompletată este numărată ca una dintre valorile unice.
Iată formula care are grijă de celulele goale și nu o ia în calcul în rezultatul final:
= SUMPRODUCT ((NAMES ””) / COUNTIF (NAMES, NAMES & ””))
În această formulă, în loc de 1 ca numărător, am folosit NUME ””. Aceasta returnează o serie de ADEVĂRATE și FALSE. Revine FALS ori de câte ori există o celulă necompletată. Deoarece ADEVĂRAT echivalează cu 1 și FALS echivalează cu 0 în calcule, celulele goale nu sunt numărate, deoarece numeratorul este 0 (FALS).
Acum că avem pregătit scheletul de bază al formulei, putem merge mai departe și putem număra diferite tipuri de date.
Cum se numără valori unice în Excel care sunt text
Vom folosi același concept discutat mai sus pentru a crea formula care va număra doar valorile textului care sunt unice.
Iată formula care va conta valorile unice ale textului în Excel:
= SUMPRODUCT ((ISTEXT (NAMES) / COUNTIF (NAMES, NAMES & ””)))
Tot ce am făcut este să folosim formula ISTEXT (NUME) ca numărător. Revine TRUE atunci când celula conține text și FALSE dacă nu. Nu va număra celulele goale, dar va număra celulele care au un șir gol („”).
Cum se numără valorile unice în Excel care sunt numerice
Iată formula care va număra valori numerice unice în Excel
= SUMPRODUCT ((ISNUMBER (NAMES)) / COUNTIF (NAMES, NAMES & ””))
Aici, folosim ISNUMBER (NAMES) ca numărător. Revine TRUE atunci când celula conține tip de date numerice și FALSE dacă nu. Nu contează celulele goale.
