ВПР (VLOOKUP), пожалуй, одна из самых ходовых функций в MicrosoftExcel. Эта статья предназначена для тех, кто знает, как ей пользоваться. Если это не про вас, читайте описание функции здесь. Но сегодня речь пойдет о другом – у всех она рано или поздно начинает возвращать ошибку #Н/Д (#N/A). Почему она возникает, и как с ней бороться.
Есть три основные причины, почему появляется эта ошибка.
Первый и самых ходовой вариант – вы ищете значение, которого нет в диапазоне. Решение проблемы предельно простое:
(1) добавить нужную строку в диапазон, в котором происходит поиск,
(2) изменить последний параметр на ИСТИНУ (или 1), если вас устраивает неточный поиск – что редко бывает правдой, но как вариант, я указать был обязан, или
(3) если вас устраивает отсутствие значения, но не устраивает ошибка, встройте функцию ВПР (VLOOKUP) в функцию ЕСЛИОШИБКА (IFERROR), подробнее про нее здесь.
Простая проверка, копируете искомое значение, выделяете диапазон, где производится поиск, нажимаете Найти (Ctrl+ F), вставляете искомое значение и ищете. Если программа не находит результат, проблема описана выше. Если находит, все становится немного интереснее – читайте дальше.
Excel очень трепетно относится к тому, как хранятся данные в ячейке. Если число хранится в формате текста, программа сообщит вам об этом двумя способами: нарисует зеленый треугольник в левом верхнем углу ячейки и, если выравнивание не установлено, поместит ваше горе-число в левую часть ячейки (обычно числа выровнены по правому краю). Кроме того число 100, хранящееся как число, и число 100, хранящееся как текст, — это два разных значения. Поэтому функция ВПР (VLOOKUP) не будет находит число 100 в диапазоне, где есть текст 100. Все варианты решения проблемы сводятся к тому, что надо привести все значения к одному формату (либо числовому, либо текстовому). Как это сделать:
(1) перевести текст в число можно по одной ячейке (скажем, она одна не в том формате), изменив формат, а потом войдя в ее редактирование (F2) и нажав Enter,
(2) другой вариант сделать то же самое для большего диапазона – воспользоваться функционалом Найти и заменить. Обычно число представлено в виде текста, если таблица была выгружена из внешней программы, скажем SAPили 1С. Обычно поиск производится по номенклатурным номерам. Если так, то они часто начинаются с одной и той же цифры, с единицы, двойки и т.п. Если так, то вам повезло. Выделяйте диапазон, меняйте формат на числовой и жмите Найти и заменить (Ctrl + H). Вбивайте найти 1, заменить на 1 (если все номера начинаются с 1), заменить все. Через некоторое время (если таблица большая) все значения будут приведены к одному формату.
(3) с помощью функции ЗНАЧЕН (VALUE) или ТЕКСТ (TEXT), причем их можно встраивать непосредственно в функцию ВПР (VLOOKUP), чтобы не создавать отдельную колонку.
В очень редких случаях бывает, что и это не помогло. Тогда возможен еще один вариант, из-за которого функция может возвращать именно эту ошибку. Этот вариант прост до безумия, тем не менее именно он может поставить в ступор. Именно с таким вариантом я столкнулся сегодня, что и навело меня на мысль написать эту статью.
Симптомы: поиск находит одно или несколько (этот вариант даже интереснее) совпадений, форматы одинаковые (в моем случае поиск производился по маске WBSэлемента – не число, выглядит приблизительно так X.XX.XXX.XX.XXX.XXXXX). При поиске найденному варианту соответствовало нужное мне значение, но формула упорно выдавала ошибку «нет данных».
Позвольте напомнить вам, как работает функция ВПР (VLOOKUP) – она ищет значение в первой колонке указанного диапазона, при первом совпадении отсчитывает нужное количество колонок и берет из нужной колонки значение. Ключевая часть этой теории – «первое совпадение». В моем случае нужный мне элемент затрат попадался несколько раз. Во всех случаях, кроме первого, ему соответствовало правильное искомое значение. А в первом – ошибка #Н/Д (#N/A). Ее функция и подтянула.
Вариант решения – убрать строку с ошибкой или переставить ее в конец диапазона.
Для вашего удобства я суммировал эти три ошибки в одну таблицу, на которую вы можете посмотреть как на картинку
или скачать по этой ссылке
Если вы хотите не просто найти первое совпадение, но и просуммировать или посчитать все совпадения, вам помогут функции СУММЕСЛИМН (SUMIFS) и СЧЕТЕСЛИМН (COUNTIFS). Хотите еще лучше разбираться в функциях Excel – купите себе эту книгу.