Üldine valem
=SUMPRODUCT(--NOT(ISERROR(range)))
Kokkuvõte
Vigadeta lahtrite arvu lugemiseks võite kasutada funktsioone SUMPRODUCT pakituna funktsioone ISERROR ja NOT. Näidatud näites on valem E5-s järgmine:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Selgitus
Selles näites on eesmärk loendada lahtrite arv vahemikus, mis ei sisalda vigu.
Seestpoolt välja töötades kasutame kõigepealt funktsiooni ISERROR kogu vahemikus:
ISERROR(B5:B14) // check all 10 cells
Kuna vahemikus B5: B14 on kümme lahtrit, tagastab ISERROR massiivi kümne sellise tulemusega:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Siin näitab iga TRUE väärtus lahtri väärtust, mis on viga. Kuna eesmärk on loendada lahtrid, mis ei sisalda vigu, tühistame need tulemused funktsiooniga EI:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
mis tagastab:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Pange tähele, et iga TRUE väärtus vastab nüüd lahtrile, mis ei sisalda viga. See massiiv on nüüd õiges vormingus - TRUE väärtused tähistavad vigadeta lahtreid, FALSE väärtused näitavad vigadega lahtreid.
Kuna SUMPRODUCT töötab ainult arvandmetega, on järgmine samm TRUE ja FALSE väärtuste teisendamine nende arvekvivalentideks 1 ja 0. Teeme seda topeltnegatiiviga (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Saadud massiiv näeb välja selline:
(1;0;1;1;1;0;1;1;1;0)
Lõpuks summeerib SUMPRODUCT selle massiivi üksused ja tagastab summa, mis näites on number 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
Funktsioon ISERR
Sarnaselt funktsiooniga ISERROR tagastab funktsioon ISERR väärtuse TÕENE, kui väärtus on viga. Erinevus seisneb selles, et ISERR ignoreerib # puuduvat viga. Kui soovite loendada lahtrid, mis ei sisalda vigu, ja ignoreerida # puuduvat tõrget, võite asendada ISERRORiga ISERROR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
SUM variant
Vigade lugemiseks saate kasutada ka funktsiooni SUM. Valemi struktuur on sama:
=SUM(--NOT(ISERROR(B5:B14)))
Märkus: see on massiivivalem ja see tuleb sisestada juhtklahviga + shift + enter, välja arvatud Excelis 365.
