Подсчитайте, сколько ячеек содержат ошибки в диапазоне в Excel

Это руководство показывает, как подсчитать количество ячеек, содержащих ошибки (любого рода ошибки, такие как ошибка #Н/Д, ошибка #ЗНАЧ! или ошибка #ДЕЛ/0!) в указанном диапазоне в Excel.

Подсчет ячеек с ошибками с помощью функции СУММПРОИЗВ
Подсчет ячеек с ошибками с помощью функции СУММ

Подсчет ячеек с ошибками с помощью функции СУММПРОИЗВ

Как показано на скриншоте ниже, чтобы подсчитать, сколько ячеек содержат ошибки в диапазоне B3:B9, вы можете применить функцию СУММПРОИЗВ вместе с функцией ЕОШИБКА.

Общая формула

=СУММПРОИЗВ(--ЕОШИБКА(диапазон))

Аргументы

Как использовать эту формулу?

1. Выберите пустую ячейку, например D3 в данном случае.

2. Введите следующую формулу в неё и нажмите клавишу Enter, чтобы получить результат.

=СУММПРОИЗВ(--ЕОШИБКА(B3:B9))

Как работают эти формулы?

=СУММПРОИЗВ(--ЕОШИБКА(B3:B9))

• 1. ЕОШИБКА(B3:B9): Функция ЕОШИБКА возвращает ИСТИНА, когда ячейка в диапазоне B3:B9 содержит ошибку, и ЛОЖЬ, если нет. В итоге она возвращает массив {ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ}, что означает, что только вторая и пятая ячейки содержат значения ошибок в диапазоне.
• 2. --{ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ}: Эти два знака минус преобразуют «ИСТИНА» в 1 и «ЛОЖЬ» в 0. Здесь вы получите новый массив как {0;1;0;0;1;0;0}.
• 3. СУММПРОИЗВ{0;1;0;0;1;0;0}: Функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат как 2.

Подсчет ячеек с ошибками с помощью функции СУММ

Функция СУММ также может помочь подсчитать ячейки с ошибками в диапазоне, вы можете сделать следующее.

Общая формула

{=SUM(--ISERROR(range))}

Аргументы

Примечание: Эта формула должна быть введена как формула массива. После применения формулы, если вокруг формулы появятся фигурные скобки, это означает, что формула массива успешно создана.

Как использовать эту формулу?

1. Выберите пустую ячейку, например D3 в данном случае.

2. Введите следующую формулу в неё, а затем нажмите клавиши Ctrl + Shift + Enter одновременно, чтобы получить результат.

=СУММ(--ЕОШИБКА(B3:B9))

Как работают эти формулы?

=СУММ(--ЕОШИБКА(B3:B9))

• 1. ЕОШИБКА(B3:B9): Функция ЕОШИБКА возвращает ИСТИНА, когда ячейка в диапазоне B3:B9 содержит ошибку, и ЛОЖЬ, если нет. В итоге она возвращает массив {ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ}, что означает, что только вторая и пятая ячейки содержат значения ошибок в диапазоне.
• 2. --{ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ}: Эти два знака минус преобразуют «ИСТИНА» в 1 и «ЛОЖЬ» в 0. Здесь вы получите новый массив как {0;1;0;0;1;0;0}.
• 3. СУММ{0;1;0;0;1;0;0}: Функция СУММ суммирует все числа в массиве и возвращает окончательный результат как 2.

Связанные функции

Связанные формулы