Как устранить ошибки VLOOKUP в Excel
Боретесь с функцией VLOOKUP в Microsoft Excel? Вот несколько советов по устранению неполадок, которые могут вам помочь.
Есть несколько вещей, которые выявляют пот у пользователей Microsoft Excel больше, чем мысль об ошибке VLOOKUP. Если вы не слишком знакомы с Excel, VLOOKUP является одной из самых сложных или, по крайней мере, одной из наименее понятных доступных функций.
Целью VLOOKUP является поиск и возврат данных из другого столбца в вашей электронной таблице Excel. К сожалению, если вы неправильно укажете формулу VLOOKUP, Excel выдаст вам ошибку. Давайте рассмотрим некоторые распространенные ошибки VLOOKUP и объясним, как их устранять.
Ограничения VLOOKUP
Прежде чем начать использовать VLOOKUP, вы должны знать, что это не всегда лучший вариант для пользователей Excel.
Начнем с того, что его нельзя использовать для поиска данных слева от него. Он также отображает только первое найденное значение, что означает, что VLOOKUP не является опцией для диапазонов данных с дублированными значениями. Ваш столбец поиска также должен быть самым дальним левым столбцом в вашем диапазоне данных.
В приведенном ниже примере самый дальний столбец (колонка А) используется в качестве столбца поиска. В этом диапазоне нет дублированных значений и данных поиска (в данном случае данные из колонка B) находится справа от столбца поиска.
Функции INDEX и MATCH могли бы стать хорошей альтернативой, если возникнет проблема с любой из этих проблем, как и новая функция XLOOKUP в Excel, которая в настоящее время находится в стадии бета-тестирования.
VLOOKUP также требует, чтобы данные были упорядочены в строки, чтобы иметь возможность точного поиска и возврата данных. HLOOKUP будет хорошей альтернативой, если это не так.
Есть некоторые дополнительные ограничения для формул VLOOKUP, которые могут вызвать ошибки, как мы объясним далее.
Ошибки VLOOKUP и # N / A
Одной из самых распространенных ошибок VLOOKUP в Excel является # N / A ошибка. Эта ошибка возникает, когда VLOOKUP не может найти искомое значение.
Начнем с того, что значение поиска может отсутствовать в вашем диапазоне данных, или вы, возможно, использовали неправильное значение. Если вы видите ошибку N / A, дважды проверьте значение в формуле VLOOKUP.
Если значение верное, то вашего поискового значения не существует. Это предполагает, что вы используете VLOOKUP для поиска точных совпадений, с range_lookup аргумент установлен в ЛОЖНЫЙ,
В приведенном выше примере, поиск Студенческий билет с номер 104 (в клетке G4) возвращает Ошибка # N / A потому что минимальный идентификационный номер в диапазоне 105,
Если range_lookup аргумент в конце формулы VLOOKUP отсутствует или установлен в ПРАВДА тогда VLOOKUP вернет ошибку # N / A, если ваш диапазон данных не отсортирован в порядке возрастания.
Он также вернет ошибку # N / A, если ваше значение поиска меньше, чем самое низкое значение в диапазоне.
В приведенном выше примере Студенческий билет значения перепутаны. Несмотря на ценность 105 существующий в ассортименте, VLOOKUP не может выполнить правильный поиск с помощью range_lookup аргумент установлен в ПРАВДА так как колонка А не отсортировано по возрастанию
Другие распространенные причины ошибок # N / A включают использование столбца поиска, который расположен не дальше всего, и использование ссылок на ячейки для значений поиска, которые содержат числа, но отформатированы как текст или содержат лишние символы, такие как пробелы.
Устранение ошибок #VALUE
#ЗНАЧЕНИЕ ошибка обычно является признаком того, что формула, содержащая функцию VLOOKUP, в некотором роде неверна.
В большинстве случаев это обычно происходит из-за ячейки, на которую вы ссылаетесь в качестве значения поиска. максимальный размер значения поиска VLOOKUP 255 символов,
Если вы имеете дело с ячейками, которые содержат более длинные строки символов, VLOOKUP не сможет их обработать.
Единственный обходной путь для этого — заменить формулу VLOOKUP комбинированной формулой INDEX и MATCH. Например, если в одном столбце содержится ячейка со строкой из более чем 255 символов, для поиска этих данных можно использовать вложенную функцию MATCH внутри INDEX.
В приведенном ниже примере ПОКАЗАТЕЛЬ возвращает значение в ячейке B4 используя диапазон в колонка А определить правильный ряд. Это использует вложенный СОВПАДЕНИЕ функция для идентификации строки в колонка А (содержит 300 символов), который соответствует ячейке H4,
В данном случае это клетка A4, с ПОКАЗАТЕЛЬ возврате 108 (значение B4).
Эта ошибка также появится, если вы использовали неправильную ссылку на ячейки в своей формуле, особенно если вы используете диапазон данных из другой книги.
Для правильной работы формулы ссылки на рабочие тетради должны быть заключены в квадратные скобки.
Если вы столкнулись с ошибкой #VALUE, дважды проверьте формулу VLOOKUP, чтобы убедиться, что ссылки на ваши ячейки верны.
#NAME и VLOOKUP
Если ваша ошибка VLOOKUP не является ошибкой #VALUE или ошибкой # N / A, то это, вероятно, #ИМЯ ошибка. Прежде чем паниковать при мысли об этом, будьте уверены — это самая простая ошибка VLOOKUP, которую нужно исправить.
Ошибка #NAME появляется, когда вы ошиблись в функции в Excel, будь то VLOOKUP или другая функция, такая как SUM. Нажмите на ячейку VLOOKUP и проверьте, правильно ли вы написали VLOOKUP.
Если других проблем нет, ваша формула VLOOKUP будет работать после исправления этой ошибки.
Использование других функций Excel
Это смелое утверждение, но такие функции, как VLOOKUP, изменят вашу жизнь. По крайней мере, это изменит вашу рабочую жизнь, сделав Excel более мощным инструментом для анализа данных.
6 причин, почему функция ВПР не работает
Функция VLOOKUP (ВПР) – одна из самых популярных среди функций категории Ссылки и массивы в Excel. А также это одна из самых сложны функций Excel, где страшная ошибка #N/A (#Н/Д) может стать привычной картиной. В этой статье мы рассмотрим 6 наиболее частых причин, почему функция ВПР не работает.
Вам нужно точное совпадение
Последний аргумент функции ВПР, известный как range_lookup (интервальный_просмотр), спрашивает, какое совпадение Вы хотите получить – приблизительное или точное.
В большинстве случаев люди ищут конкретный продукт, заказ, сотрудника или клиента, и потому хотят точное совпадение. Если производится поиск уникального значения, то аргументом range_lookup (интервальный_просмотр) должно быть FALSE (ЛОЖЬ).
Этот аргумент не обязателен, но если его не указать, то будет использовано значение TRUE (ИСТИНА). В таком случае для правильной работы функции необходимо, чтобы данные были отсортированы в порядке возрастания.
На рисунке ниже показана функция ВПР с пропущенным аргументом range_lookup (интервальный_просмотр), которая возвращает ошибочный результат.
Решение
Если Вы ищите уникальное значение, задайте последний аргумент равным FALSE (ЛОЖЬ). Функция ВПР в примере выше должна выглядеть так:
Зафиксируйте ссылки на таблицу
Возможно, Вы захотите использовать сразу несколько функций ВПР, чтобы извлечь большее количество информации. Если Вы собираетесь скопировать функцию ВПР в несколько ячеек, то необходимо зафиксировать часть аргументов.
На рисунке ниже показан пример функции ВПР, введенной некорректно. Для аргументов lookup_value (искомое_значение) и table_array (таблица) введены неправильные диапазоны ячеек.
Решение
Аргумент table_array (таблица) – это таблица, которую ВПР использует для поиска и извлечения информации. Чтобы корректно скопировать функцию ВПР, в аргументе table_array (таблица) должна быть абсолютная ссылка на диапазон ячеек.
Кликните по адресу ссылки внутри формулы и нажмите F4 на клавиатуре, чтобы превратить относительную ссылку в абсолютную. Формула должна быть записана так:
В этом примере ссылки в аргументах lookup_value (искомое_значение) и table_array (таблица) сделаны абсолютными. Иногда достаточно зафиксировать только аргумент table_array (таблица).
Вставлен столбец
Аргумент col_index_num (номер_столбца) используется функцией ВПР, чтобы указать, какую информацию необходимо извлечь из записи.
В связи с тем, что аргумент введен как числовой индекс, он не очень надёжен. Если в таблицу вставить новый столбец, функция ВПР может перестать работать. Рисунок ниже показывает именно такой сценарий.
Столбец Quantity (Количество) был 3-м по счету, но после добавления нового столбца он стал 4-м. Однако функция ВПР автоматически не обновилась.
Решение 1
Одним из решений будет защитить таблицу, чтобы пользователи не могли вставлять новые столбцы. В случае, когда пользователям потребуется такая возможность, решение станет не жизнеспособным.
Решение 2
Другой вариант – вставить функцию MATCH (ПОИСКПОЗ) в аргумент col_index_num (номер_столбца) функции ВПР.
Функция ПОИСКПОЗ может быть использована для того, чтобы найти и возвратить номер требуемого столбца. Это сделает аргумент col_index_num (номер_столбца) динамичным, т.е. можно будет вставлять новые столбцы в таблицу, не влияя на работу функции ВПР.
Формула, показанная ниже, может быть использована в этом примере, чтобы решить проблему, описанную выше.
Таблица стала больше
По мере добавления новых строк в таблицу, функции ВПР может потребоваться корректировка для гарантии, что новые строки таблицы охвачены формулой. На рисунке ниже показана ситуация, когда функция ВПР просматривает не всю таблицу в поисках нужного фрукта.
Решение
Форматируйте диапазон ячеек как таблицу (Excel 2007+) или как именованный диапазон. Такие приёмы дадут гарантию, что ВПР всегда будет обрабатывать всю таблицу.
Чтобы форматировать диапазон как таблицу, выделите диапазон ячеек, который собираетесь использовать для аргумента table_array (таблица). На Ленте меню нажмите Home > Format as Table (Главная > Форматировать как таблицу) и выберите стиль из галереи. Откройте вкладку Table Tools > Design (Работа с таблицами > Конструктор) и в соответствующем поле измените имя таблицы.
В формуле на рисунке ниже использовано имя таблицы FruitList.
ВПР не может смотреть влево
Функция ВПР имеет такое ограничение: она не может извлекать информацию из колонок, находящихся слева от первой. Она ищет искомое значение в крайней левой колонке заданной таблицы и возвращает информацию, находящуюся правее.
Решение
Решение этой проблемы – не использовать ВПР вовсе. Используйте комбинацию функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ), которая стала привычной альтернативой для ВПР. Это намного более гибкое решение
Пример, приведённый ниже, был использован для извлечения информации из колонки слева от той, по которой производится поиск:
Данные в таблице дублируются
Функция ВПР может извлечь только одну запись. Она возвратит первую найденную запись, соответствующую введённому Вами условию поиска.
Если таблица содержит повторяющиеся значения, функция ВПР не справится с такой задачей правильно.
Решение 1
Нужны ли Вам повторяющиеся данные в списке? Если нет – удалите их. Это можно сделать быстро при помощи кнопки Removes Duplicates (Удалить дубликаты) на вкладке Data (Данные).
Решение 2
Решили оставить дубликаты? Хорошо! В таком случае, Вам нужна не функция ВПР. Для таких случаев отлично подойдёт сводная таблица, позволяющая выбрать значение и посмотреть результаты.
Таблица ниже – это список заказов. Допустим, Вы хотите найти все заказы определённого фрукта.
Сводная таблица позволяет выбрать значение из столбца ID в фильтре, которое соответствует определенному фрукту, и получить список всех связанных заказов. В нашем примере выбрано значение ID равное 23 (Бананы).
ВПР без забот
Эта статья показывает решения 6 наиболее распространённых причин сбоя в работе функции ВПР. Вооружившись этой информацией, Вы сможете насладиться более беззаботным будущим в компании замечательных функций Excel.
Почему не работает ВПР в Excel?
Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте).
Давайте постараемся вместе ответим на вопрос: «Почему функция ВПР не работает?»
В первую очередь, сообщение об ошибке вы увидите, если данные, которое вы ищете, действительно отсутствуют в указанном диапазоне поиска. Здесь мы бессильны.
Все остальные случаи связаны с тем, что мы что-то не учли или сделали неверно.
Итак, почему ВПР не находит значение, хотя оно есть?
Неточность при вводе данных.
В этом случае ничего найти не получится, и ВПР возвратит ошибку #Н/Д. Самое простое решение здесь очень часто оказывается самым верным. Быть может, вы просто ошиблись при вводе критерия поиска.
Это вполне может случиться, особенно если вы записываете его прямо в формулу.
Опечатка при вводе функции.
Если вы видите ошибку #ИМЯ?, то это означает, что при записи названия самой функции вы допустили неточность – перепутали или добавили лишнюю букву.
Проверьте синтаксис, и всё будет в порядке.
Неверные ссылки.
Проверьте, верно ли вы указали диапазон поиска, а также координаты значения, которое мы будем искать.
Случается, что в формуле вы указали обычные (относительные) ссылки, забыв заменить их на абсолютные ($), а затем произвели какие-то действия с таблицей. К примеру, добавили столбец. Ваши ссылки в формуле теперь будут указывать на неверные координаты.
Важно! При таком изменении ссылок вы можете и не знать, что найдено не то, что нужно. Ведь какое-то значение все же возвращено, хоть и неверное. Поэтому будьте внимательны в использовании относительных и абсолютных ссылок! Рекомендуем по этой причине стараться использовать «умные» таблицы или именованные диапазоны.
Неверно указан параметр «интервальный просмотр».
Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр = ИСТИНА или вовсе опущен), но на самом деле данные не отсортированы.
Об этой проблеме мы подробно говорили ранее.
Столбец поиска не является первым слева столбцом диапазона поиска.
Часто забывают о том, что ВПР ищет совпадающие данные только в первом слева столбце диапазона, который вы указали.
Если ваши просматриваемые данные находятся не в первом, а во втором или другом столбце, то она не сможет найти ни одного совпадения, и вы вновь увидите сообщение #Н/Д.
Это важное ограничение нельзя забывать.
Несовпадение форматов данных.
Формат ячейки, откуда берется искомое значение наименования (например D3 в нашем случае), и формат ячеек первого столбца (A3:A21) из диапазона поиска отличаются (например, числовой и текстовый). Этот случай особенно часто встречается при использовании вместо текстовых наименований числовых кодов (номера счетов, артикулы, идентификаторы, даты и т.п.). Ведь число, записанное в таблицу Excel, может быть в двух принципиально разных состояниях — как число и как текст. И визуально их отличить практически невозможно.
Преобразуем число в текст.
Как видите, с виду записи одинаковы, однако в D6 артикул сохранен как число, а в A10 – как текст (на таких ячейках обычно видна пометка — зелёный уголок). Текст не может быть равнозначен числу, поэтому получаем #Н/Д. ВПР не находит значение, хотя оно с виду есть.
Как решить эту проблему со случайным несоответствием в формате записи? Можно использовать функцию ТЕКСТ, которая преобразует любой тип данных в текстовый вид.
Как видно, обычную ссылку на D6 мы заменяем конструкцией
Первый аргумент – это адрес, а второй аргумент означает формат, в который мы будем преобразовывать значение (в нашем случае – текстовый).
Можно поступить и проще — «приклеить» к числу пустую строку при помощи оператора склейки &.
Запомните простое правило:
Если какую-либо текстовую операцию мы пытаемся провести над ячейкой, в которой записано число, то Excel обязательно попытается превратить это число в текст.
Если несовпадение форматов встречается редко, то можно просто исправить формат записи данных. Измените формат ячейки на текстовый и пересохраните ее содержание (F2 и затем ENTER). Содержимое будет преобразовано в текст.
Как видите, ошибка исчезла, поскольку теперь текстовое значение сравнивается с таким же текстом.
Преобразуем текст в число.
А если нужно сделать обратное преобразование? Преобразовать число как текст в обычное число, чтобы все данные в таблице были одного типа? Давайте рассмотрим на примере.
Добавьте перед проблемным значением двойной минус. Это означает — дважды умножить на минус 1. А еще со школы мы помним, что минус на минус дает плюс. В результате ничего не изменится. Но есть важный момент.
Если какую-либо математическую операцию мы пытаемся провести над ячейкой, в которой записан текст, то Excel обязательно попытается преобразовать его в число, чтобы выполнить вычисления.
Так же и в нашем случае. Текст преобразуется в число. Итак, наша формула выглядит следующим образом:
Если числовой и текстовый форматы беспорядочно перемешаны?
А теперь — самый сложный случай. И здесь тоже есть простое решение.
Если поиск с преобразованием значения в число не удался, то будем искать его как текст. Объединяем оба варианта при помощи функции ЕСЛИОШИБКА.
В результате получаем немного громоздкий, но зато вполне универсальный вариант, который позволит избежать ошибок в формуле ВПР из-за несовпадения форматов значений.
Лишние пробелы и непечатаемые знаки.
Формула не работает, потому что в артикуле присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.).
В нашем примере на картинке чуть выше формула ВПР возвратила ошибку, так как в D8 при вводе данных случайно были добавлены два пробела после слова. И, поскольку сравниваются символьные значения, поиск, естественно, завершился неудачно.
Такая неточность может встречаться довольно часто, и обнаружить её визуально практически невозможно. К примеру, если наименование состоит из 2 слов, то вполне возможно случайное появление лишнего пробела между этими словами.
Могут быть также случайно добавлены символы, невидимые на экране и на печати (табуляция, перевод строки и т.д.). Визуально определить такие неточности ввода вы не сможете.
В этом случае можно использовать функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для удаления лишних пробелов либо других невидимых символов. Вместо
Функция СЖПРОБЕЛЫ убирает пробелы, а ПЕЧСИМВ удаляет все непечатаемые и невидимые символы.
Эту комбинацию функций рекомендуем применять всегда «на всякий случай», чтобы подстраховать себя от случайных неточностей ввода данных.
Неправильно указан номер столбца.
Иногда функция ВПР не срабатывает, потому что неправильно указан номер столбца, значение из которого вы хотите получить (третий аргумент).
Он не может быть меньше 1 и не может быть больше, чем количество столбцов в указанном для поиска диапазоне. Если он указан неверно, то получим ошибку #ЗНАЧ!
Когда вы видите такое, пересчитайте количество колонок в диапазоне, который вы указали, и убедитесь, что оно не меньше, чем номер, указанный третьим аргументом функции. Быть может, вы указали 4, а колонок всего 3.
И в любом случае – это число должно быть больше 1. Вряд ли кто-то из вас будет руками вводить 0 или -1, но если вы получаете его при помощи каких-то вычислений, вложенных в формулу, то здесь-то и может быть проблема.
Неверная ссылка на данные из другой таблицы
Мы с вами говорили о том, что диапазон поиска может быть расположен в другой таблице или даже в другом файле Excel.
Если вы случайно допустили неточность при указании ссылки на эти данные, то увидите сообщение #ССЫЛКА!.
Это будет означать, что вы ссылаетесь на неверный диапазон, который Excel найти не может.
После изменения таблицы функция ВПР перестала работать
Вы сделали все правильно и до тех пор, пока вы не вставили несколько строк или столбцов в вашу таблицу, все работало верно.
Теперь же появилась ошибка #Н/Д либо результаты стали явно неверны. В чем дело?
А дело всё в том, что вы не использовали абсолютные ссылки, не добавляли к адресам ячеек, строк и столбцов знак $. В результате после добавления (или удаления) строк или столбцов ваши ссылки изменились, и все сломалось.
Кроме того, в третьем аргументе вы указываете порядковый номер столбца, из которого нужно извлечь данные. Но если вы вставили дополнительные колонки, то и количество столбцов, и их порядковые номера изменятся. А у вас в формуле используются «старые» номера.
Постарайтесь отменить сделанные изменения, благо Excel хранит всю историю изменений вашей таблицы.
Важно! Всегда используйте абсолютные ссылки, где это необходимо, чтобы застраховать себя от подобных неожиданных изменений.
Как убрать сообщение #Н/Д в ВПР?
Если функция не находит точного совпадения или искомого объекта просто нет в таблице, то она выдает сообщения, о которых мы говорили выше. С одной стороны, это хорошо, поскольку отрицательный результат- это тоже результат.
Однако, иногда это не очень удобно, т.к. такие сообщения потом порождают новые проблемы в расчете итогов, попадают в распечатку и т.д. Ведь если на эту ячейку ссылаются какие-то другие формулы, то они будут неправильно работать.
Можно легко перехватить ошибки и заменить их на любое другое подходящее нам значение (например, на ноль) с помощью функции ЕСЛИОШИБКА.
Синтаксис её подобен ЕСЛИ. В качестве условия используется выражение, которое потенциально может возвратить сообщение об ошибке.
Задача – заменить его числом или выражением, указанным во втором аргументе. Рассмотрим это на примере.
В нашем случае маракуйя отсутствует в прайсе. Но вместо #Н/Д мы выведем ноль.
Вот как нужно будет преобразовать наши формулы, чтобы все выглядело красиво.
Естественно, вместо нуля можно вписать «не найдено» либо любое другое сообщение, а можно просто поставить пробел. Но в случае, когда на основе этих ячеек затем производятся какие-то вычисления, ноль, возможно, будет предпочтительнее.
Надеемся, теперь вы сможете ответить на вопрос: «Почему не работает функция ВПР в Excel?».
Еще полезные примеры:
Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном…
2 способа извлечь данные из разных таблиц при помощи ВПР. — Задача: Данные, которые нужно найти и извлечь при помощи функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть, одни и те же столбцы, расположенные в одном…
/> Вычисление номера столбца для извлечения данных в ВПР — Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.…
4 способа, как сделать левый ВПР в Excel. — Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом…
Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц – это задача, которую в Excel приходится довольно часто решать. Например, у нас есть старый прайс-лист и его новая версия. Нужно просмотреть, цены на какие товары изменились и…
ВПР с несколькими условиями: 5 примеров. — Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными…
Функция ВПР в Excel: пошаговая инструкция с 5 примерами — ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска…
Формула ВПР в Excel — 22 факта, которые нужно знать. — В процессе работы в Excel часто возникает задача извлечения нужных данных из рабочих таблиц. Для этой цели в Excel предусмотрена формула ВПР (VLOOKUP в английском варианте). И хотя ВПР относительно…