Obecný vzorec
SUMPRODUCT(--(A:A=A1))
souhrn
Předmluva
Toto je nepříjemně dlouhý úvod, ale kontext je důležitý, promiňte!
Pokud se pokusíte spočítat velmi dlouhá čísla (16 a více číslic) v rozsahu s COUNTIF, mohou se vám zobrazit nesprávné výsledky kvůli chybě ve způsobu, jakým určité funkce zpracovávají dlouhá čísla, i když jsou tato čísla uložena jako text. Zvažte obrazovku níže. Všechny počty ve sloupci D jsou nesprávné - ačkoli každé číslo ve sloupci B je jedinečné, počet vrácený COUNTIF naznačuje, že tato čísla jsou duplikáty.
=COUNTIF(data,B5)
Tento problém souvisí s tím, jak Excel zpracovává čísla. Excel dokáže zpracovat pouze 15 platných číslic a pokud v aplikaci Excel zadáte číslo s více než 15 číslicemi, uvidíte koncové číslice tiše převedené na nulu. Výše uvedený problém počítání vyplývá z tohoto limitu.
Normálně se tomuto limitu můžete vyhnout zadáním dlouhých čísel jako textu, a to buď zahájením čísla jednoduchou uvozovkou („999999999999999999), nebo naformátováním buněk jako textu před zadáním. Pokud nepotřebujete provádět matematické operace s číslem, je to dobré řešení a umožňuje vám zadávat extra dlouhá čísla pro věci, jako jsou čísla kreditních karet a sériová čísla, aniž byste ztratili čísla.
Pokud se však pokusíte pomocí funkce COUNTIF spočítat číslo s více než 15 číslicemi (i když je uloženo jako text), mohou se vám zobrazit nespolehlivé výsledky. Stává se to proto, že COUNTIF interně převede dlouhou hodnotu zpět na číslo v určitém okamžiku během zpracování, čímž se aktivuje výše popsaný limit 15 číslic. Bez všech číslic mohou být některá čísla počítána jako duplikáty při počítání s COUNTIF.
Řešení
Jedním z řešení je nahradit vzorec COUNTIF vzorcem, který používá SUM nebo SUMPRODUCT. V zobrazeném příkladu vypadá vzorec v E5 takto:
=SUMPRODUCT(--(data=B5))
Vzorec používá pojmenovaný rozsah „data“ (B5: B9) a generuje správný počet pro každé číslo pomocí SUMPRODUCT.
Vysvětlení
Nejprve výraz uvnitř SUMPRODUCT porovná všechny hodnoty v pojmenovaném rozsahu „data“ s hodnotou ze sloupce B v aktuálním řádku. Výsledkem je pole výsledků PRAVDA / NEPRAVDA.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Dále dvojitý zápor vynucuje hodnoty PRAVDA / NEPRAVDA na hodnoty 1/0.
=SUMPRODUCT((1;0;0;0;0))
Nakonec SUMPRODUCT jednoduše sečte položky v poli a vrátí výsledek.
Varianta maticového vzorce
Můžete také použít funkci SUMA namísto SUMPRODUCT, ale toto je maticový vzorec a musí být zadán pomocí control + shift + enter:
(=SUM(--(B:B=B5)))
Další funkce s tímto problémem
Sám jsem to neověřil, ale zdá se, že několik funkcí má stejný problém, včetně SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF a AVERAGEIFS.
