Bendroji formulė
SUMPRODUCT(--(A:A=A1))
Santrauka
Įvadas
Tai erzinamai ilga įžanga, tačiau kontekstas yra svarbus, atsiprašau!
Jei bandysite suskaičiuoti labai ilgus (16 ir daugiau skaitmenų) diapazono skaičius su COUNTIF, galite pamatyti neteisingus rezultatus dėl klaidos, susijusios su tam tikrų funkcijų apdorojimu ilgais skaičiais, net jei šie skaičiai saugomi kaip tekstas. Apsvarstykite toliau pateiktą ekraną. Visi skaičiai D stulpelyje yra neteisingi - nors kiekvienas skaičius B stulpelyje yra unikalus, COUNTIF grąžintas skaičius rodo, kad šie skaičiai yra dublikatai.
=COUNTIF(data,B5)
Ši problema susijusi su tuo, kaip „Excel“ tvarko skaičius. „Excel“ gali apdoroti tik 15 reikšmingų skaitmenų, o jei „Excel“ įvesite skaičių, kuriame yra daugiau nei 15 skaitmenų, pamatysite, kad galiniai skaitmenys tyliai paversti nuliu. Pirmiau minėta skaičiavimo problema kyla iš šios ribos.
Paprastai šios ribos galite išvengti įvesdami ilgus skaičius kaip tekstą, pradėdami skaičių viena kabute ('99999999999999999999) arba prieš įvesdami langelį (langelius) suformatuokite kaip Tekstas. Jei jums nereikia atlikti matematinių operacijų su skaičiumi, tai yra geras sprendimas ir leidžia jums įvesti ypač ilgus skaičius tokiems dalykams kaip kredito kortelių numeriai ir serijos numeriai, neprarandant jokių skaičių.
Tačiau jei bandysite naudoti COUNTIF skaičiuoti daugiau nei 15 skaitmenų skaičiui (net jei jis saugomas kaip tekstas), galite pamatyti nepatikimus rezultatus. Taip atsitinka todėl, kad „COUNTIF“ ilgą reikšmę viduje paverčia skaičiumi tam tikru apdorojimo metu, suaktyvindama aukščiau aprašytą 15 skaitmenų limitą. Jei nėra visų skaitmenų, kai kurie skaičiai gali būti skaičiuojami kaip dublikatai, kai skaičiuojami su COUNTIF.
Sprendimas
Vienas iš sprendimų yra pakeisti formulę COUNTIF formule, kurioje naudojama SUM arba SUMPRODUCT. Parodytame pavyzdyje formulė E5 atrodo taip:
=SUMPRODUCT(--(data=B5))
Formulėje naudojami įvardyti diapazonai „duomenys“ (B5: B9) ir su SUMPRODUCT sukuriamas teisingas kiekvieno skaičiaus skaičius.
Paaiškinimas
Pirma, SUMPRODUCT viduje esanti išraiška lygina visas nurodyto diapazono „data“ reikšmes su dabartinės eilutės B stulpelio verte. Dėl to gaunamas TRUE / FALSE rezultatų masyvas.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Tada dvigubas neigiamas vertė TRUE / FALSE reikšmes verčia į 1/0.
=SUMPRODUCT((1;0;0;0;0))
Galiausiai, SUMPRODUCT tiesiog susumuoja masyvo elementus ir pateikia rezultatą.
Masyvo formulės variantas
Taip pat galite naudoti funkciją SUM vietoj SUMPRODUCT, tačiau tai yra masyvo formulė, kurią reikia įvesti paspaudus klavišą + shift + enter:
(=SUM(--(B:B=B5)))
Kitos šios problemos funkcijos
Aš pats to nepatvirtinau, bet panašu, kad kelios funkcijos turi tą pačią problemą, įskaitant SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF ir AVERAGEIFS.
