Ошибки #ЗНАЧ и #Н/Д в функции ВПР() Excel и как сними бороться.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечания:

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

• при использовании функций для поиска данных (ПОИСКОП, ВПР и других), если в качестве аргумента «искомое_значение» было введено несуществующее;
• при использовании формул массивов, если длина массива результатов превышает длину исходных массивов;
• если при использовании функции не были указаны один либо нескольких аргументов, обязательных для заполнения.

В данной статье расскажу о двух ошибках которые может выдать функция ВПР() :

Перечисленные выше ошибки наиболее часто встречаться при использовании функции ВПР() и очень часто вызывают трудности с устранением  у начинающих пользователей Excel .

Когда возникает ошибка #Н/Д и как от нее избавиться при использовании ВПР().

Сообщение об ошибке Н/Д можно расшифровать как аббревиатуру (НД) – нет данных, то есть функции ВПР() нечего отобразить, и она как бы сообщает: «нет данных для отображения».

Почему возникает ошибка Н/Д (НД)?

1. Ошибка может возникать потому, что в Вашем списке (диапазоне) для сравнения нет искомого функцией ВПР() значения.
2. Ошибка может возникать потому, что в Вашем списке (диапазоне) для сравнения значения ячеек имеют ошибки. Иногда ошибки нельзя увидеть «не вооружённым глазом», например, если в ячейке добавлен лишний пробел или едва заметная точка. ВПР() воспринимает значение ячейки без пробела и с пробелом как совершенно разные данные и выдает ошибку «Н/Д».
3. Ошибка может возникать потому, что в искомой ячейке уже стоит значение «Н/Д», то есть ВПР() подтягивает эту ошибку из другой ячейки (искомой).

Как исправить ошибки Н/Д?

1. Первый способ – применить обработку ошибок – функцию ЕСЛИОШИБКА(ВПР(*;*;*;0);”Здесь была ошибка”). Эта функция заменяет сообщение об ошибке на любое значение, которое Вы укажете.
2. Способ №2 – удалить все пробелы и, по возможности, знаки препинания из ячеек. Для этого нужно нажатием клавиш ctrl+H вызвать окно замены значений, потом в поле «Найти» ввести пробел или знак препинания, а в поле «Заменить на:» не вводить ничего и нажить кнопку «Заменить все».
3. Способ №3 – поставить в функции ВПР() допуск ошибки. Как нам извесчтно 4 –й аргумент функции это число ошибок которые может допускать в сравниваемой строке функция ВПР(). То есть, если поставить число «1», то допускается 1 ошибка при сравнении [ВПР(*;*;*;1)]. В таком случае строка без пробела и с одним пробелом будут считаться идентичными. Но в таком способе есть подвох — очень высока вероятность неверных результатов, например, слово «полка» и «палка» имеют отличие всего в один знак и будут восприняты функцией, как одно и то же.

Когда возникает ошибка #ЗНАЧ и как от нее избавиться при использовании ВПР().

Ошибка #ЗНАЧ может выводиться функцией ВПР(), если введенные значения аргументов функции  некорректны и функция не может их обработать.

Казалось бы какие значения могут быть некорректными, если ВПР() необходимо просто сравнить одно значение с другим и присвоить ячейке данные из совпавших ячеек, но эта ошибка возникает.

Появляется ошибка #ЗНАЧ в функции ВПР() тогда, когда длина строки сравниваемой функцией слишком большая и не может быть обработана.  Например, в Excel 2010 максимальная длина строки обрабатываемой функцией всего 255 символов, и если Вы будете сравнивать строки длиной 256 и более символов, то получите ошибку #ЗНАЧ.

Исправить ошибку #ЗНАЧ в таком случае можно уменьшив длины сравниваемых строк.

Еще ошибка #ЗНАЧ может возникнуть если Вы пропустили(не указали) один из аргументов в функции.