Všeobecný vzorec
SUMPRODUCT(--(A:A=A1))
Zhrnutie
Predslov
Toto je nepríjemne dlhý úvod, ale kontext je dôležitý, prepáčte!
Ak sa pokúsite spočítať veľmi dlhé čísla (16 a viac číslic) v rozsahu s parametrom COUNTIF, môžu sa vám zobraziť nesprávne výsledky v dôsledku chyby v zaobchádzaní s dlhými číslami určitými funkciami, aj keď sú tieto čísla uložené ako text. Zvážte obrazovku uvedenú nižšie. Všetky počty v stĺpci D sú nesprávne - hoci každé číslo v stĺpci B je jedinečné, počet vrátený parametrom COUNTIF naznačuje, že tieto čísla sú duplikáty.
=COUNTIF(data,B5)
Tento problém súvisí s tým, ako program Excel spracováva čísla. Excel dokáže spracovať iba 15 platných číslic a ak v programe Excel zadáte číslo s viac ako 15 číslicami, uvidíte nasledujúce číslice v tichosti prevedené na nulu. Vyššie uvedený problém s počítaním vyplýva z tohto limitu.
Tomuto limitu sa obvykle môžete vyhnúť zadávaním dlhých čísel ako textu, a to buď tak, že začnete číslo jednoduchou úvodzovkou („999999999999999999), alebo naformátujete bunky ako Text pred zadaním. Toto riešenie je dobré, pokiaľ nepotrebujete vykonávať matematické operácie s číslom. Umožňuje vám zadávať mimoriadne dlhé čísla, napríklad čísla kreditných kariet a sériové čísla, bez straty čísel.
Ak sa však pokúsite pomocou funkcie COUNTIF spočítať číslo s viac ako 15 číslicami (aj keď sú uložené ako text), môžu sa vám zobraziť nespoľahlivé výsledky. Stáva sa to preto, lebo COUNTIF interne prevádza dlhú hodnotu späť na číslo v určitom okamihu spracovania, čím sa spustí 15-miestny limit opísaný vyššie. Bez všetkých číslic sa niektoré čísla môžu počítať ako duplikáty, keď sa počítajú s COUNTIF.
Riešenie
Jedným z riešení je nahradiť vzorec COUNTIF vzorcom, ktorý používa SUM alebo SUMPRODUCT. V zobrazenom príklade vyzerá vzorec v E5 takto:
=SUMPRODUCT(--(data=B5))
Vzorec používa pomenovaný rozsah „data“ (B5: B9) a generuje správny počet pre každé číslo pomocou SUMPRODUCT.
Vysvetlenie
Po prvé, výraz vo vnútri SUMPRODUCT porovnáva všetky hodnoty v pomenovanom rozsahu „data“ s hodnotou zo stĺpca B v aktuálnom riadku. Výsledkom je pole výsledkov PRAVDA / NEPRAVDA.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Ďalej dvojitý zápor vynúti hodnoty TRUE / FALSE na hodnoty 1/0.
=SUMPRODUCT((1;0;0;0;0))
Nakoniec program SUMPRODUCT jednoducho sčíta položky v poli a vráti výsledok.
Variant vzorca v poli
Môžete tiež použiť funkciu SUM namiesto SUMPRODUCT, ale toto je vzorec poľa a musí sa zadať pomocou klávesov control + shift + enter:
(=SUM(--(B:B=B5)))
Ďalšie funkcie s týmto problémom
Sám som to neoveril, ale zdá sa, že niekoľko funkcií má rovnaký problém, vrátane SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF a AVERAGEIFS.
