Generička formula
SUMPRODUCT(--(A:A=A1))
Sažetak
Predgovor
Ovo je dosadno dugačak uvod, ali kontekst je važan, oprostite!
Ako pokušate izbrojiti vrlo duge brojeve (više od 16 znamenki) u rasponu s COUNTIF, možda ćete vidjeti netočne rezultate zbog greške u načinu na koji određene funkcije obrađuju duge brojeve, čak i kada su ti brojevi pohranjeni kao tekst. Razmotrite donji zaslon. Svi brojevi u stupcu D su netočni - iako je svaki broj u stupcu B jedinstven, broj koji je vratio COUNTIF sugerira da su ti brojevi duplikati.
=COUNTIF(data,B5)
Ovaj je problem povezan s načinom na koji Excel rukuje brojevima. Excel može obraditi samo 15 značajnih znamenki, a ako u Excel unesete broj s više od 15 znamenki, vidjet ćete da se prateće znamenke tiho pretvaraju u nulu. Gore spomenuti problem brojanja proizlazi iz ovog ograničenja.
To ograničenje obično možete izbjeći unošenjem dugih brojeva kao teksta, započinjanjem broja s jednim navodnikom ('999999999999999999) ili formatiranjem ćelija (stanica) kao Tekst prije unosa. Sve dok ne trebate izvoditi matematičke operacije na broju, ovo je dobro rješenje i omogućuje vam unos izuzetno dugih brojeva za stvari poput brojeva kreditnih kartica i serijskih brojeva bez gubljenja bilo kojeg broja.
Međutim, ako pokušate koristiti COUNTIF za brojanje broja s više od 15 znamenki (čak i kad je pohranjen kao tekst), možda ćete vidjeti nepouzdane rezultate. To se događa jer COUNTIF interno pretvara dugu vrijednost natrag u broj u nekom trenutku tijekom obrade, pokrećući gore opisano ograničenje od 15 znamenki. Bez prisutnih svih znamenki, neki brojevi mogu se računati kao duplikati kad se broje s COUNTIF.
Riješenje
Jedno od rješenja je zamjena formule COUNTIF formulom koja koristi SUM ili SUMPRODUCT. U prikazanom primjeru formula u E5 izgleda ovako:
=SUMPRODUCT(--(data=B5))
Formula koristi imenovani raspon "podataka" (B5: B9) i generira točan broj za svaki broj pomoću SUMPRODUCT.
Obrazloženje
Prvo, izraz unutar SUMPRODUCT uspoređuje sve vrijednosti u imenovanom rasponu "podaci" s vrijednošću iz stupca B u trenutnom retku. Rezultat je niz ISTINITIH / NETOČNIH rezultata.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Dalje, dvostruki negativ prisiljava vrijednosti TRUE / FALSE na vrijednosti 1/0.
=SUMPRODUCT((1;0;0;0;0))
Konačno, SUMPRODUCT jednostavno sumira stavke u polju i vraća rezultat.
Varijanta formule niza
Također možete koristiti funkciju SUM umjesto SUMPRODUCT, ali ovo je formula niza i mora se unijeti sa control + shift + enter:
(=SUM(--(B:B=B5)))
Ostale funkcije s ovim problemom
Nisam to osobno provjerio, ali čini se da nekoliko funkcija ima isti problem, uključujući SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF i AVERAGEIFS.
