Функция ЕНД как скрыть ошибку #Н/Д для ВПР и ПРОСМОТР в Excel

Функция ЕНД в Excel используется для проверки ячеек или выражений, передаваемых в качестве аргумента, и возвращает логическое значение ИСТИНА. Например, если ячейка содержит код ошибки #Н/Д или результатом вычисления выражения, переданного в качестве аргумента, является код ошибки #Н/Д. В противном случае результатом выполнения данной функции является логическое ЛОЖЬ.

Суммирование количества ошибок в ячейках Excel

Примеры использования функции ЕНД в Excel. Данная функция принадлежит к категории «Проверка свойств и значений» – функций Excel (нелогические функции для проверки условий). Она удобна при проведении сложных расчетов с ветвлением логики. Например, при отсутствии ошибки будет выполнено действие_1, в противном случае – действие_2.

Пример 1. В таблице содержатся данные о товарах и их количестве. Данные были получены из СУБД, если количество единиц товаров равно нулю, в таблице Excel данная информация отобразилась в виде кода ошибки #Н/Д. Определить число наименований товаров, которых нет в наличии.

Вид таблицы данных:

Пример 1.

Для расчета используем следующую запись (формула массива CTRL+SHIFT+Enter):

=СУММ(--ЕНД(B3:B13))

Функция ЕНД принимает сразу диапазон ячеек B3:B13 в качестве аргумента, поскольку используется формула массива. Двойное отрицание «--» необходимо для явного преобразования логических значений к числовым данным (ИСТИНА – 1, ЛОЖЬ – 0). Функция СУММ суммирует элементы полученного массива из нулей и единиц. В результате получаем:

Функция ЕНД.

В результате мы получили число равно количеству ошибок #Н/Д в столбце B.



Как получить первое значение ячейки вместо ошибки Н/Д в Excel

Пример 2. В таблице содержится диапазон ячеек со случайными числами, отсортированными в порядке возрастания. Найти ближайшее число из данного диапазона заданному с помощью функции ПРОСМОТР. Известно, если искомое число меньше первого значения в диапазоне, будет выведен код ошибки #Н/Д. Обработать данную ситуацию так, чтобы вместо кода ошибки выводился первый элемент массива.

Вид таблицы данных:

Пример 2.

Для поиска числа 1 используем следующую запись:

Функция ЕНД анализирует результат выполнения функции ПРОСМОТР. Если в качестве первого аргумента ПРОСМОТР передано числовое значение, которое меньше значения первого элемента просматриваемого диапазона, будет сгенерирован код ошибки #Н/Д и будет выполнено выражение, переданное в качестве аргумента значение_если_истина функции ЕСЛИ. В противном случае (искомое число находится в диапазоне массива или превышает значение его последнего элемента), выполнится выражение, переданное в качестве аргумента значение_если_ложь.

Результат вычислений:

результат выполнения функции ПРОСМОТР.

Как видно, ошибка #Н/Д не выводится, а вместо нее первое значение ячейки просматриваемого столбца.

Описание синтаксиса и параметров функции ЕНД в Excel

Функция ЕНД имеет следующую синтаксическую запись:

=ЕНД(значение)

Единственным и обязательным для заполнения аргументом рассматриваемой функции является значение. Он принимает ссылки на ячейки, текстовые, числовые, логические данные, а также имена.

Примечания:

  1. Преобразование типов данных для значений, переданных в качестве аргумента функции ЕНД, не выполняется. Например, число «99» указанное в кавычках, будет рассматриваться как текстовые данные. Если в качестве аргумента была передана строка «#Н/Д», ЕНД вернет значение ЛОЖЬ. Если ячейка содержит код ошибки #Н/Д, функция ЕНД, получившая ссылку на данную ячейку в качестве аргумента, вернет – ИСТИНА.
  2. Данная функция обычно используется в комбинации с ЕСЛИ и другими функциями для проверки выражения для своевременной проверки результатов вычислений и перехвата возможной ошибки.
  3. Код ошибки #Н/Д генерируют функции в случаях, когда в формулах используются недопустимые значения. Распространенные примеры:
  • при использовании функций для поиска данных (ПОИСКОП, ВПР и других), если в качестве аргумента «искомое_значение» было введено несуществующее;
  • при использовании формул массивов, если длина массива результатов превышает длину исходных массивов;
  • если при использовании функции не были указаны один либо нескольких аргументов, обязательных для заполнения.

en ru