Всем привет. Эта статья – одна из важнейших, что есть на этом сайте. Уже многие годы я учу вас пользоваться функциями поиска: ВПР, ПОИСКПОЗ и др. Эти функции разыскивают нужное значение в таблице и возвращают что-то в зависимости от результата поиска. Это важнейшие инструменты для любого пользователя Excel, вам нужно иметь полное представление о том, как они работают.

Часто функции поиска возвращают ошибку #Н/Д. Значит, программа не смогла найти искомое значение. Это неприятно, ведь все последующие расчёты становятся невозможными. Многие пугаются такой ошибки, т.к. мало о ней знают. А напрасно.

Это очень полезная ошибка, она возникает, когда в расчётах, или в данных что-то не так, и функция не может найти искомое значение. Чтобы разобраться с этим, задайте себе вопрос: возможно ли, что нужного значения нет в таблице? В зависимости от ответа, имеем два сценария.

Отсутствие значения в исходной таблице маловероятно или исключено

Если значение должно быть, а его нет, значит нужно проверить:

  1. Правильно ли записана формула, не пропущены аргументы;
  2. Правильно ли вы используете функцию. Почитайте о том, как она работает и сверьте с тем, как её применяете. Список полезных статей о функциях поиска я прикреплю в конце этого материала;
  3. Оцените таблицу с исходными данными, при возможности, визуально оцените её полноту, сверьте с первоисточником, если такой есть. Например, сравните количество строк;
  4. Проверьте правильность записи искомого значения. Часто в таких данных появляются лишние пробелы, непечатаемые символы. Их сложно заметить сразу, но они могут быть причиной ошибки #Н/Д;
  5. Аналогично, проверьте правильность записи искомых значений в таблице с данными. Здесь есть высокая вероятность непечатаемых символов, которые могут появиться при выгрузке таблиц из базы данных;
  6. Диапазон для поиска, заданный в функции, не охватывает всю таблицу, некоторые строки или столбцы «выпали» из неё. Такое может быть результатом механической ошибки, либо неправильного использования относительных ссылок. Вообще, относительные и абсолютные ссылки очень полезны, но часто приводят к ошибкам у новичков. Разберитесь с ними, если хотите хорошо владеть Эксель, это простая и важная тема. Вот вам статья в помощь;
  7. Искомое значение и проверяемые в таблице – одного и того же типа. Часто, при сохранении таблиц из базы данных, числа сохраняются, как текст. Сопоставление данных разных типов приводит к появлению #Н/Д;
  8. Вы используете правильный режим поиска в функциях? Необдуманный выбор режимов может привести к ошибке, или вернуть неверные результаты. В последнем случае, вы даже не заметите, что что-то пошло не так. Больше о режимах поиска – в статьях из списка ниже;
  9. Обдумайте прочие возможные причины. При возможности, обратитесь за помощью к более опытным пользователям.

Отсутствие значения в исходной таблице возможно

Если вы допускаете, что искомых данных может и не быть, попробуйте использовать следующие методы. Однако, делайте это осторожно, когда полностью уверены в своих действиях. Вы делаете это на свой страх и риск, ведь сейчас я научу вас игнорировать ошибки!

Обход #Н/Д с помощью функции ЕСЛИОШИБКА

Эта функция универсальна и позволяет вам определить, что вывести, если значение не найдено:

=ЕСЛИОШИБКА(значение; вывести_если_ошибка):

  • Значение – результат или функция поиска
  • Вывести если ошибка – что вернуть вместо сообщения об ошибке

На примере ниже мы ищем человека в списке и выводим его возраст. Если имя не найдено – будет ошибка #Н/Д:

Можно использовать ЕСЛИОШИБКА, чтобы вывести «НЕ НАЙДЕНО» вместо ошибки:

Выглядит лучше, чем в первом случае, не находите?

Перехват функцией ЕСНД

Предыдущий способ хорош, но имеет недостаток: он обрабатывает ошибки всех типов. Значит, мы можем случайно скрыть проблемы, которые должны были обработать вручную, разобраться. Получаем непредсказуемый результат. Потому, лучше  использовать функцию ЕСНД. Она работает так же, как и предыдущая, но «отлавливает» лишь одну ошибку — #Н/Д:

Не выводить ничего в случае ошибки

В предыдущих двух способах мы вывели информативное сообщение, когда получили ошибку. Альтернативой этому может быть пустая ячейка вместо ошибки. Это выглядит лучше, но здесь вы не знаете наверняка, пустота – из-за ошибки, или формула поиска вернула её. Это неопределенность, но часто ею можно пренебречь. Вот два варианта, как выводить пустое значение:

  1. Вместо строки «НЕ НАЙДЕНО», поставьте пустые кавычки. Отлично, когда результатом поиска должна быть строка. Вы строку и вернёте, только пустую;

  2. Вместо «НЕ НАЙДЕНО» укажите ноль и отключите показ нулей в ячейке. Я делаю так, если результат поиска участвует в последующих вычислениях. Мы ожидаем в этой ячейке число, его и возвращаем:

Напоследок: если вам зачем-то понадобится принудительно вернуть #Н/Д, используйте функцию НД(). Не знаю, зачем это может быть нужным, я так ни разу и не воспользовался ею. Может у Вас есть идеи, для чего может пригодиться такая функция? Пишите в комментариях! Если что-то из этого материала у Вас вызвало затруднения – пишите, обязательно отвечу!

Вам так же может быть интересно