Обща формула
=SUMPRODUCT(--NOT(ISERROR(range)))
Обобщение
За да преброите броя на клетките, които не съдържат грешки, можете да използвате функциите ISERROR и NOT, увити във функцията SUMPRODUCT. В показания пример формулата в E5 е:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Обяснение
В този пример целта е да се преброи броят на клетките в диапазон, които не съдържат грешки.
Работейки отвътре навън, първо използваме функцията ISERROR за целия диапазон:
ISERROR(B5:B14) // check all 10 cells
Тъй като има десет клетки в диапазона B5: B14, ISERROR връща масив с десет резултата като този:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Тук всяка стойност TRUE показва стойност на клетка, която е грешка. Тъй като целта е да се броят клетки, които не съдържат грешки, обръщаме тези резултати с функцията NOT:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
което връща:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Забележете, че всяка TRUE стойност сега съответства на клетка, която не съдържа грешка. Този масив вече е в правилния формат - TRUE стойностите показват клетки без грешки, FALSE стойностите показват клетки с грешки.
Тъй като SUMPRODUCT работи само с числови данни, следващата стъпка е да преобразуваме стойностите TRUE и FALSE в техните числови еквиваленти, 1 и 0. Правим това с двойно отрицателно (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Полученият масив изглежда така:
(1;0;1;1;1;0;1;1;1;0)
И накрая, SUMPRODUCT сумира елементите в този масив и връща сумата, която в примера е числото 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
Функция ISERR
Подобно на функцията ISERROR, функцията ISERR връща TRUE, когато дадена стойност е грешка. Разликата е, че ISERR игнорира # N / A грешки. Ако искате да преброите клетки, които не съдържат грешки, и да игнорирате # N / A грешки, можете да замените ISERR с ISERROR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
SUM опция
Можете също да използвате функцията SUM за преброяване на грешки. Структурата на формулата е една и съща:
=SUM(--NOT(ISERROR(B5:B14)))
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter, освен в Excel 365.
