Подсчитайте ячейки, которые не содержат ошибок в указанном диапазоне

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

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

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

Как показано на скриншоте ниже, чтобы подсчитать, сколько ячеек не содержат ошибок в диапазоне B3:B9, вы можете сделать следующее.

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

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

Аргументы

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

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

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

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

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

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

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

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

Функция НЕ вместе с функцией ЕОШИБКА может быть вложена внутрь функции СУММ для подсчета количества ячеек, которые не содержат ошибок в указанном диапазоне в Excel.

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

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

Аргументы

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

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

1. Выберите пустую ячейку для вывода результата.

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

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

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

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

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

Примечание: Как функция ЕОШИБКА, так и функция ЕОШ могут использоваться для проверки значений ошибок в диапазоне ячеек. Таким образом, вы можете заменить ЕОШИБКА на ЕОШ в вышеупомянутых формулах, чтобы также подсчитать ячейки без ошибок в указанном диапазоне. Однако следует отметить, что функция ЕОШ игнорирует ошибку #Н/Д и считает её нормальным значением в результате.

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

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