Splošna formula
SUMPRODUCT(--(A:A=A1))
Povzetek
Predgovor
To je nadležno dolg uvod, vendar je kontekst pomemben, žal!
Če poskušate v obsegu s štetjem šteti zelo dolge številke (16+ števk), boste morda opazili napačne rezultate zaradi napake v načinu, kako nekatere funkcije obdelujejo dolge številke, tudi če so te številke shranjene kot besedilo. Razmislite o spodnjem zaslonu. Vsa štetja v stolpcu D so napačna - čeprav je vsako število v stolpcu B unikatno, štetje, ki ga vrne COUNTIF, pomeni, da so te številke podvojene.
=COUNTIF(data,B5)
Ta težava je povezana s tem, kako Excel obdeluje številke. Excel lahko obravnava samo 15 pomembnih števk in če v Excel vnesete številko z več kot 15 števkami, boste zaostale številke tiho pretvorili v nič. Zgornja težava pri štetju izhaja iz te omejitve.
Običajno se tej omejitvi lahko izognete tako, da vnesete dolge številke kot besedilo, tako da začnete številko z enim narekovajem ('999999999999999999) ali tako, da celice pred vnosom oblikujete kot Besedilo. Dokler vam ni treba izvajati matematičnih operacij na številki, je to dobra rešitev in vam omogoča, da vnesete zelo dolge številke za stvari, kot so številke kreditnih kartic in serijske številke, ne da bi izgubili številke.
Če pa s funkcijo COUNTIF poskušate šteti številko z več kot 15 števkami (tudi če je shranjena kot besedilo), boste morda videli nezanesljive rezultate. To se zgodi, ker COUNTIF v določenem trenutku med obdelavo dolgo vrednost pretvori nazaj v število in sproži zgoraj opisano 15-mestno omejitev. Brez prisotnosti vseh številk lahko nekatere številke štejemo kot dvojnike, če jih štejemo z COUNTIF.
Rešitev
Ena od rešitev je zamenjava formule COUNTIF s formulo, ki uporablja SUM ali SUMPRODUCT. V prikazanem primeru je formula v E5 videti tako:
=SUMPRODUCT(--(data=B5))
Formula uporablja imenovani obseg "podatki" (B5: B9) in s SUMPRODUCT ustvari pravilno število za vsako številko.
Pojasnilo
Najprej izraz znotraj SUMPRODUCT primerja vse vrednosti v imenovanem obsegu "podatki" z vrednostjo iz stolpca B v trenutni vrstici. Rezultat je niz TRUE / FALSE rezultatov.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Nato dvojni negativ prisili vrednosti TRUE / FALSE na vrednosti 1/0.
=SUMPRODUCT((1;0;0;0;0))
Končno, SUMPRODUCT preprosto sešteje elemente v matriki in vrne rezultat.
Različica formule matrike
Namesto SUMPRODUCT lahko uporabite tudi funkcijo SUM, vendar je to matrična formula in jo je treba vnesti s tipko control + shift + enter:
(=SUM(--(B:B=B5)))
Druge funkcije s to težavo
Tega še nisem preveril, vendar se zdi, da ima več funkcij enako težavo, vključno s SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF in AVERAGEIFS.
