Všeobecný vzorec
=SUMPRODUCT(--NOT(ISERROR(range)))
Zhrnutie
Ak chcete spočítať počet buniek, ktoré neobsahujú chyby, môžete použiť funkcie ISERROR a NOT zabalené do funkcie SUMPRODUCT. V zobrazenom príklade je vzorec v E5:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Vysvetlenie
V tomto príklade je cieľom spočítať počet buniek v rozsahu, ktorý neobsahuje chyby.
Pri práci zvnútra smerom von najskôr použijeme funkciu ISERROR v celom rozsahu:
ISERROR(B5:B14) // check all 10 cells
Pretože v rozsahu B5: B14 je desať buniek, vráti ISERROR pole s desiatimi výsledkami, ako je tento:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tu každá PRAVÁ hodnota označuje hodnotu bunky, ktorá predstavuje chybu. Pretože cieľom je spočítať bunky, ktoré neobsahujú chyby, obrátime tieto výsledky pomocou funkcie NOT:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
ktorý vracia:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Všimnite si, že každá PRAVÁ hodnota teraz zodpovedá bunke, ktorá neobsahuje chybu. Toto pole je teraz v správnom formáte - TRUE hodnoty označujú bunky bez chýb, FALSE hodnoty označujú bunky s chybami.
Pretože SUMPRODUCT funguje iba s číselnými údajmi, ďalším krokom je prevod hodnôt TRUE a FALSE na ich číselné ekvivalenty 1 a 0. Robíme to s dvojitým záporom (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Výsledné pole vyzerá takto:
(1;0;1;1;1;0;1;1;1;0)
Nakoniec SUMPRODUCT sčíta položky v tomto poli a vráti súčet, ktorý je v príklade číslo 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
Funkcia ISERR
Rovnako ako funkcia ISERROR, aj funkcia ISERR vráti TRUE, ak je hodnota chybou. Rozdiel je v tom, že ISERR ignoruje chyby # N / A. Ak chcete počítať bunky, ktoré neobsahujú chyby, a ignorovať chyby # N / A, môžete ISERROR nahradiť ISERR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
Možnosť SUM
Na počítanie chýb môžete použiť aj funkciu SUM. Štruktúra vzorca je rovnaká:
=SUM(--NOT(ISERROR(B5:B14)))
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter, s výnimkou Excel 365.
