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