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