Загальна формула
=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.
