Üldine valem
SUMPRODUCT(--(A:A=A1))
Kokkuvõte
Eessõna
See on tüütult pikk sissejuhatus, kuid kontekst on oluline, vabandust!
Kui proovite COUNTIF-iga loendada vahemikus väga pikki numbreid (16+ numbrit), võite näha valesid tulemusi vea tõttu, kuidas teatud funktsioonid pikki numbreid käsitlevad, isegi kui need numbrid on tekstina salvestatud. Mõelge allolevale ekraanile. Kõik veerus D olevad arvud on valed - kuigi iga veeru B number on ainulaadne, näitab COUNTIF-i tagastatud arv, et need numbrid on duplikaadid.
=COUNTIF(data,B5)
See probleem on seotud sellega, kuidas Excel numbritega ümber käib. Excel suudab töödelda ainult 15 olulist numbrit ja kui sisestate Excelis rohkem kui 15-kohalise numbri, näete, et lõpunumbrid muudetakse vaikselt nulliks. Eespool nimetatud loendamisprobleem tuleneb sellest piirist.
Tavaliselt saate seda piirangut vältida, sisestades pikkad numbrid tekstina, alustades numbrit ühe jutumärgiga ('999999999999999999) või vormindades enne sisestamist lahtrid lahtriteks Tekst. Niikaua kui teil pole vaja matemaatilisi toiminguid teha numbriga, on see hea lahendus ja see võimaldab teil sisestada eriti pikki numbreid näiteks krediitkaardinumbrite ja seerianumbrite jaoks, kaotamata ühtegi numbrit.
Siiski, kui proovite COUNTIF-i kasutada rohkem kui 15-kohalise numbri lugemiseks (isegi tekstina salvestatuna), võite näha ebausaldusväärseid tulemusi. See juhtub seetõttu, et COUNTIF teisendab pika väärtuse töötlemise käigus mingil hetkel tagasi arvuks, käivitades ülalkirjeldatud 15-kohalise piiri. Kui kõiki numbreid pole, võidakse COUNTIF-iga loendades mõnda numbrit lugeda duplikaatidena.
Lahendus
Üks lahendus on valemi COUNTIF asendamine valemiga, mis kasutab summat või summatoodet. Näidatud näites näeb E5 valem välja järgmine:
=SUMPRODUCT(--(data=B5))
Valem kasutab nimega vahemikku "andmed" (B5: B9) ja genereerib SUMPRODUCT abil igale numbrile õige loenduse.
Selgitus
Kõigepealt võrreldakse SUMPRODUCTi sisestatud väljendit kõikides vahemikus nimega "data" praeguse rea veeru B väärtusega. Selle tulemuseks on massiivi TRUE / FALSE tulemusi.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Järgmisena sunnib topeltnegatiiv TRUE / FALSE väärtused väärtusele 1/0.
=SUMPRODUCT((1;0;0;0;0))
Lõpuks võtab SUMPRODUCT lihtsalt massiivi üksused kokku ja tagastab tulemuse.
Massiivvalemi variant
SUMPRODUCTi asemel võite kasutada ka funktsiooni SUM, kuid see on massiivivalem ja see tuleb sisestada juhtklahviga + shift + enter:
(=SUM(--(B:B=B5)))
Muud selle probleemiga funktsioonid
Ma pole seda ise kinnitanud, kuid näib, et mitmel funktsioonil on sama probleem, sealhulgas SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF ja AVERAGEIFS.
