Подсчёт ячеек, не содержащих ошибки, в Ограниченный диапазон

С помощью комбинации функций NOT и ISERROR внутри функций SUMPRODUCT или SUM можно подсчитать количество ячеек без ошибок в заданном диапазоне Excel. В этом руководстве пошагово описано, как легко справиться с этой задачей.

Подсчёт ячеек без ошибок с помощью функции SUMPRODUCT
Подсчёт ячеек без ошибок с помощью функции SUM

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

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

Универсальная формула

=SUMPRODUCT(--NOT(ISERROR(range)))

Аргументы

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

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

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

=SUMPRODUCT(--NOT(ISERROR(B3:B9)))

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

=SUMPRODUCT(--NOT(ISERROR(B3:B9)))

• 1) ISERROR(B3:B9): функция ISERROR возвращает значение ИСТИНА, если ячейка в диапазоне B3:B9 содержит ошибку, и ЛОЖЬ — в противном случае. В результате получается массив {ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ}, что указывает на наличие ошибок только во второй и пятой ячейках диапазона.
• 2)NOT{FALSE;TURE;FALSE;FALSE;TURE;FALSE;FALSE}: функция NOT инвертирует значения ИСТИНА и ЛОЖЬ в массиве, возвращая новый массив {ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА}. Каждое значение ИСТИНА в этом новом массиве указывает на ячейку, не содержащую ошибки.
• 3)--{TURE;FALSE;TURE;TURE;FALSE;TURE;TURE}: эти два знака «минус» преобразуют «ИСТИНА» в 1, а «ЛОЖЬ» — в 0, и вы получите новый массив {1;0;1;1;0;1;1}.
• 4) SUMPRODUCT{1;0;1;1;0;1;1}: функция SUMPRODUCT суммирует все числа в массиве и возвращает итоговый результат — 5.

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

С помощью комбинации функций NOT и ISERROR внутри функции SUM можно подсчитать количество ячеек без ошибок в заданном диапазоне Excel.

Универсальная формула

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

Аргументы

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

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

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

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

=SUM(--NOT(ISERROR(B3:B9)))

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

=SUM(--NOT(ISERROR(B3:B9)))

• 1)ISERROR(B3:B9): функция ISERROR возвращает значение ИСТИНА, если ячейка в диапазоне B3:B9 содержит ошибку, и ЛОЖЬ — в противном случае. В итоге она возвращает массив {ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ}, что означает наличие ошибок только во второй и пятой ячейках диапазона.
• 2)NOT{FALSE;TURE;FALSE;FALSE;TURE;FALSE;FALSE}: функция NOT инвертирует значения ИСТИНА и ЛОЖЬ в массиве и возвращает новый массив {ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА}. Таким образом, каждое значение ИСТИНА в этом новом массиве указывает на ячейку, не содержащую ошибки.
• 3)--{TURE;FALSE;TURE;TURE;FALSE;TURE;TURE}: эти два знака «минус» преобразуют «ИСТИНА» в 1, а «ЛОЖЬ» — в 0. В результате вы получите новый массив {1;0;1;1;0;1;1}
• 4) SUM{1;0;1;1;0;1;1}: функция SUM складывает все числа в массиве и возвращает итоговый результат — 5.

Примечание: Чтобы проверить наличие ошибок в диапазоне ячеек, можно использовать как функцию ISERROR, так и функцию ISERR. Поэтому в приведённых выше формулах вы можете заменить ISERROR на ISERR для подсчёта ячеек без ошибок в ограниченном диапазоне. Однако имейте в виду: функция ISERR игнорирует ошибку #Н/Д и воспринимает её как обычное значение.

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

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