Splošna formula
=SUMPRODUCT(--NOT(ISERROR(range)))
Povzetek
Če želite prešteti število celic, ki ne vsebujejo napak, lahko uporabite funkciji ISERROR in NOT, zavite v funkcijo SUMPRODUCT. V prikazanem primeru je formula v E5:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Pojasnilo
V tem primeru je cilj prešteti število celic v obsegu, ki ne vsebujejo napak.
Če delamo od znotraj navzven, najprej uporabimo funkcijo ISERROR na celotnem območju:
ISERROR(B5:B14) // check all 10 cells
Ker je v območju B5: B14 deset celic, ISERROR vrne matriko z desetimi rezultati, kot je ta:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tu vsaka vrednost TRUE označuje vrednost celice, ki je napaka. Ker je cilj šteti celice, ki ne vsebujejo napak, te rezultate obrnemo s funkcijo NOT:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
ki vrne:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Upoštevajte, da vsaka vrednost TRUE zdaj ustreza celici, ki ne vsebuje napake. Ta matrika je zdaj v pravilni obliki - vrednosti TRUE označujejo celice brez napak, vrednosti FALSE pa celice z napakami.
Ker SUMPRODUCT deluje samo s številskimi podatki, je naslednji korak pretvorba vrednosti TRUE in FALSE v njihove numerične ekvivalente, 1 in 0. To naredimo z dvojnim negativom (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Nastala matrika je videti tako:
(1;0;1;1;1;0;1;1;1;0)
Končno, SUMPRODUCT sešteje elemente v tej matriki in vrne seštevek, ki je v primeru številka 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
Funkcija ISERR
Tako kot funkcija ISERROR, tudi funkcija ISERR vrne TRUE, če je vrednost napaka. Razlika je v tem, da ISERR prezre # N / A napake. Če želite prešteti celice, ki ne vsebujejo napak, in prezreti # N / A napake, lahko ISERROR nadomestite z ISERROR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
Možnost SUM
Za štetje napak lahko uporabite tudi funkcijo SUM. Struktura formule je enaka:
=SUM(--NOT(ISERROR(B5:B14)))
Opomba: to je formula matrike, ki jo je treba vnesti s tipko control + shift + enter, razen v Excelu 365.
