Bendroji formulė
=SUMPRODUCT(--NOT(ISERROR(range)))
Santrauka
Norėdami suskaičiuoti langelių, kuriuose nėra klaidų, skaičių, galite naudoti funkcijas ISERROR ir NOT, įvyniotus į funkciją SUMPRODUCT. Parodytame pavyzdyje formulė E5 yra:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Paaiškinimas
Šiame pavyzdyje tikslas yra suskaičiuoti langelių skaičių diapazone, kuriame nėra klaidų.
Dirbdami iš vidaus, iš pradžių mes naudojame ISERROR funkciją visame diapazone:
ISERROR(B5:B14) // check all 10 cells
Kadangi B5: B14 diapazone yra dešimt langelių, ISERROR pateikia masyvą su dešimčia tokių rezultatų:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Kiekviena TRUE reikšmė nurodo langelio vertę, kuri yra klaida. Kadangi tikslas yra suskaičiuoti langelius, kuriuose nėra klaidų, šiuos rezultatus pakeičiame funkcija NOT:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
kuris grąžina:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Atkreipkite dėmesį, kad kiekviena TRUE reikšmė dabar atitinka langelį, kuriame nėra klaidos. Šis masyvas dabar yra teisingo formato - TRUE reikšmės nurodo langelius be klaidų, FALSE reikšmės rodo langelius su klaidomis.
Kadangi „SUMPRODUCT“ veikia tik su skaitmeniniais duomenimis, kitas žingsnis yra konvertuoti TRUE ir FALSE reikšmes į jų skaitmeninius ekvivalentus 1 ir 0. Tai darome dvigubai neigiamai (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Gautas masyvas atrodo taip:
(1;0;1;1;1;0;1;1;1;0)
Galiausiai „SUMPRODUCT“ susumuoja šio masyvo elementus ir pateikia bendrą sumą, kuri pavyzdyje yra skaičius 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
ISERR funkcija
Kaip ir funkcija ISERROR, taip ir funkcija ISERR grąžina TRUE, kai reikšmė yra klaida. Skirtumas tas, kad ISERR nepaiso # klaidų, kurių nėra. Jei norite suskaičiuoti langelius, kuriuose nėra klaidų, ir ignoruoti klaidas Nr. Nėra, galite pakeisti ISERROR ISERR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
SUM variantas
Klaidoms skaičiuoti taip pat galite naudoti funkciją SUM. Formulės struktūra yra ta pati:
=SUM(--NOT(ISERROR(B5:B14)))
Pastaba: tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control + Shift + Enter“, išskyrus „Excel 365“.
