Bendroji formulė
=SUMPRODUCT(--ISERROR(range))
Santrauka
Norėdami suskaičiuoti langelius, kuriuose yra klaidų, galite naudoti funkciją ISERROR, apvyniotą funkcija SUMPRODUCT. Parodytame pavyzdyje E5 langelyje yra ši formulė:
=SUMPRODUCT(--ISERROR(B5:B9))
Paaiškinimas
Funkcija SUMPRODUCT priima vieną ar daugiau masyvų, daugina masyvus kartu ir grąžina „produktų sumą“ kaip galutinį rezultatą. Jei pateikiamas tik vienas masyvas, SUMPRODUCT tiesiog grąžina masyvo elementų sumą.
Pateiktame pavyzdyje tikslas yra suskaičiuoti klaidas tam tikrame diapazone. E5 formulė yra:
=SUMPRODUCT(--ISERROR(B5:B9))
Dirbant iš vidaus, funkcija ISERROR grąžina TRUE, kai langelyje yra klaida, o FALSE, jei ne. Kadangi B5: B9 diapazone yra penkios ląstelės, ISERROR įvertina kiekvieną langelį ir pateikia tokius masyvo rezultatus penkis:
(TRUE;FALSE;TRUE;FALSE;TRUE)
Norėdami priversti TRUE / FALSE reikšmes į 1 ir 0, naudojame dvigubą neigiamą reikšmę (-). Gautas masyvas atrodo taip:
(1;0;1;0;1)
Galiausiai SUMPRODUCT susumuoja šio masyvo elementus ir pateikia bendrą sumą, kuri šiuo atveju yra 3.
ISERR parinktis
Funkcija ISERROR suskaičiuoja visas klaidas. Jei dėl kokių nors priežasčių norite skaičiuoti visas klaidas, išskyrus # N / A, vietoj to galite naudoti funkciją ISERR:
=SUMPRODUCT(--ISERR(B5:B9)) // returns 2
Kadangi viena iš pavyzdyje parodytų klaidų yra # N / A, ISERR parinktis grąžina 2, o ne 3.
Masyvo formulė su SUM
Klaidoms skaičiuoti taip pat galite naudoti funkciją SUM, tačiau turite įvesti kaip masyvo formulę. Įvedus formulę atrodys taip:
(=SUM(--ISERROR(range)))
Garbanotieji skliaustai automatiškai pridedami „Excel“ ir nurodo masyvo formulę.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su klavišais + Shift + Enter, išskyrus „Excel 365“.
