Formula generică
SUMPRODUCT(--(A:A=A1))
rezumat
Prefaţă
Aceasta este o introducere deranjantă de lungă, dar contextul este important, îmi pare rău!
Dacă încercați să numărați numere foarte lungi (peste 16 cifre) într-un interval cu COUNTIF, este posibil să vedeți rezultate incorecte, datorită unei erori în modul în care anumite funcții gestionează numerele lungi, chiar și atunci când aceste numere sunt stocate ca text. Luați în considerare ecranul de mai jos. Toate numărările din coloana D sunt incorecte - deși fiecare număr din coloana B este unic, numărul returnat de COUNTIF sugerează că aceste numere sunt duplicate.
=COUNTIF(data,B5)
Această problemă este legată de modul în care Excel gestionează numerele. Excel poate gestiona doar 15 cifre semnificative și, dacă introduceți un număr cu mai mult de 15 cifre în Excel, veți vedea cifrele finale convertite în tăcere la zero. Problema de numărare menționată mai sus rezultă din această limită.
În mod normal, puteți evita această limită introducând numerele lungi ca text, fie începând numărul cu un singur ghilimel ('99999999999999999999), fie formatând celula (celulele) ca text înainte de a introduce. Atâta timp cât nu este nevoie să efectuați operații matematice pe un număr, aceasta este o soluție bună și vă permite să introduceți numere foarte lungi pentru lucruri precum numerele cardurilor de credit și numerele de serie, fără a pierde niciun număr.
Cu toate acestea, dacă încercați să utilizați COUNTIF pentru a număra un număr cu mai mult de 15 cifre (chiar și atunci când este stocat ca text) este posibil să vedeți rezultate nesigure. Acest lucru se întâmplă deoarece COUNTIF convertește intern valoarea lungă înapoi la un număr la un moment dat în timpul procesării, declanșând limita de 15 cifre descrisă mai sus. Fără toate cifrele prezente, unele numere pot fi numărate ca duplicate atunci când sunt numărate cu COUNTIF.
Soluţie
O soluție este înlocuirea formulei COUNTIF cu o formulă care utilizează SUM sau SUMPRODUCT. În exemplul prezentat, formula din E5 arată astfel:
=SUMPRODUCT(--(data=B5))
Formula folosește intervalul numit „date” (B5: B9) și generează numărul corect pentru fiecare număr cu SUMPRODUCT.
Explicaţie
În primul rând, expresia din SUMPRODUCT compară toate valorile din intervalul numit „date” cu valoarea din coloana B din rândul curent. Acest lucru are ca rezultat o serie de rezultate ADEVĂRATE / FALSE.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Apoi, dublul negativ constrânge valorile TRUE / FALSE la valorile 1/0.
=SUMPRODUCT((1;0;0;0;0))
În cele din urmă, SUMPRODUCT sumează pur și simplu articolele din matrice și returnează rezultatul.
Varianta formulă matrice
De asemenea, puteți utiliza funcția SUM în loc de SUMPRODUCT, dar aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter:
(=SUM(--(B:B=B5)))
Alte funcții cu această problemă
Nu am verificat eu însămi acest lucru, dar se pare că mai multe funcții au aceeași problemă, inclusiv SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF și AVERAGEIFS.
