Obecný vzorec
=SUMPRODUCT(--NOT(ISERROR(range)))
souhrn
Chcete-li spočítat počet buněk, které neobsahují chyby, můžete použít funkce ISERROR a NOT zabalené do funkce SUMPRODUCT. V zobrazeném příkladu je vzorec v E5:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Vysvětlení
V tomto příkladu je cílem spočítat počet buněk v rozsahu, který neobsahuje chyby.
Při práci zevnitř ven nejprve použijeme funkci ISERROR v celém rozsahu:
ISERROR(B5:B14) // check all 10 cells
Protože v rozsahu B5: B14 je deset buněk, vrátí ISERROR pole s deseti výsledky, jako je tento:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Zde každá PRAVÁ hodnota označuje hodnotu buňky, která představuje chybu. Jelikož cílem je počítat buňky, které neobsahují chyby, obrátíme tyto výsledky pomocí funkce NOT:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
který vrací:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Všimněte si, že každá PRAVÁ hodnota nyní odpovídá buňce, která neobsahuje chybu. Toto pole je nyní ve správném formátu - PRAVÉ hodnoty označují buňky bez chyb, FALSE hodnoty označují buňky s chybami.
Protože SUMPRODUCT funguje pouze s číselnými daty, dalším krokem je převod hodnot TRUE a FALSE na jejich číselné ekvivalenty 1 a 0. Děláme to s dvojitým záporem (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Výsledné pole vypadá takto:
(1;0;1;1;1;0;1;1;1;0)
Nakonec SUMPRODUCT sečte položky v tomto poli a vrátí součet, který je v příkladu číslo 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
Funkce ISERR
Stejně jako funkce ISERROR vrátí funkce ISERR hodnotu TRUE, pokud je hodnota chybou. Rozdíl je v tom, že ISERR ignoruje chyby # N / A. Pokud chcete počítat buňky, které neobsahují chyby, a ignorovat chyby # N / A, můžete ISERROR nahradit ISERR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
Možnost SUM
Funkci SUM můžete také použít k počítání chyb. Struktura vzorce je stejná:
=SUM(--NOT(ISERROR(B5:B14)))
Poznámka: Toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter, kromě Excel 365.
