Функция VLOOKUP (ВПР)

Как использовать формулу Vlookup? Почему не работает Vlookup? Ошибки #REF, #Value, #N/A (#ССЫЛКА, #ЗНАЧ, №Н/Д). Для того, чтобы правильно использовать Vlookup нужно идти самым лёгким путём: пошагово выбрать 4 пункта формулы Vlookup и нажать ОК. Эта формула сложная, и с первого раза освоить её никому не удаётся. В каждой задаче свои условия и формула может не сработать по той или иной причине. Как пользоваться формулой Vlookup и как справляться с ошибками и написано в этой статье.

Формула Vlookup — это формула для поиска нужного числа (или значения/текста) из одной таблицы для другой таблицы.

Чтобы использовать формулу Vlookup нужно обязательно иметь следующее:

1) 2 таблицы (минимум) с 1ой одинаковой колонкой (т.е. с колонкой с одними и теми же данными). Если в 1ой таблице (Прайс-лист) цены и названия продуктов, то в 2ой таблице (Ассортимент) идентификаторы продуктов должны иметь колонку с теми же названиями, что и в 1ой таблице.

2) Таблицы должны постоянно быть расположены в одном и том же месте (желательно в общей Excel книге, т.е. в едином файле). Если у вас 1 таблица на рабочем столе, а 2ая на флешке, то, изъяв флешку из компьютера, формула перестанет работать.

3) Знание некоторых нюансов (описаны в случаях с ошибками ниже, например, неверный формат)

Как Использовать Формулу Vlookup

Для начала необходимо для примера найти 2 таблицы. Вот они:

Пример 2ух Таблиц для использования формулы Vlookup

В Таблице № 1 мы видим что колонка «Название Товара» пустая. Самый лёгкий способ, чтобы её заполнить, это скопировать-вставить имена товаров с Таблицы № 2 в эту колонку. Но что если бы эта таблица была длиннее, чем 4 строки? Да и к тому же номера и названия товаров в правой таблице в другом порядке. Для этого и нужна формула VLookup.

ВАЖНО: Если бы вы захотели добавить колонку «Название Товара» в Таблицу № 1 Слева от колонки «# товара», то формула VLookup бы не сработала. Почему? См. п. 1 «Ошибок VLookup и их Причин» (ниже).

Добавляем формулу Vlookup в Таблицу

Стаём в ячейку B3, вводим =Vlookup( и нажимаем значок функции fx (см. картинку ниже). После нажатия видим появившееся окно с четырьмя полями (для ввода аргументов). В каждое поле вводим соответствующее ему значение.

  • Lookup_value (Искомое значение) = ячейка данных, которые есть как в Таблице № 1 так и в Таблице № 2.
  • Table_array (Таблица) = Таблица 2, из которой нам нужно взять информацию. А именно, колонку «Название Товара».
  • Col_index_num (Номер столбца) = № столбца из Таблицы № 2, считается по очередности слева направо, в данном случае 2.
  • Range_lookup (Интервальный просмотр) = Если здесь ставите 0 (или FALSE или ЛОЖЬ), то получите точные данные. Если здесь ставите 1 (или TRUE или ИСТИНА), получите приблизительные данные.

Вводим формулу Vlookup в Excel
Итак, мы добавляем следующие данные в следующие поля:

  • Lookup_value (Искомое значение) = A3 (т.е. число 110 из 1ой колонки «# товара»)
  • Table_array (Таблица) = $F$2:$G$6 (просто выбрали Таблицу 2 и зафиксировали её значками доллара)
  • Col_index_num (Номер столбца) = 2 (№ столбца «Название товара» из Таблицы № 2)
  • Range_lookup (Интервальный просмотр) = 0 (чтобы получить точные данные).

Ошибки Vlookup и их Причины

Виды ошибок формулы VLookup

  1. Колонка искомого значения (1го аргумента Lookup_value в формуле) находится справа от вводимой формулы.
  2. Формула не находит значения, т.к. съехала рамка выбранной таблицы (выбранный диапазон не зафиксирован долларовыми знаками). (ошибка #N/A или #Н/Д)
  3. Формула выбирает данные не из той колонки, хотя выбрано всё верно.
  4. Сравнивая числа [01], [ 1] (пробел слева и единица) и [1] формула считает их разными и не может найти сопоставимые им значения. (ошибка #VALUE или #ЗНАЧ)
  5. После перемещения файла с Таблицой № 2, формула больше не видит Таблицу № 2 и не может ничего найти. (ошибка #REF или #ССЫЛКА)
  6. Для чисел 110 и 111 формула находят одни и те же данные, т.к. считает их похожими.
  7. Только в Excel 2003: Всё сделано правильно, но формула не работает.

Советы и Способы решения проблем с формулой VLookup (соответственно номерам ошибок)

  1. Выбор таблицы (table_array) должен начинаться с колонки, в которой данные из обоих Таблиц (в данном случае это # товара).
  2. Всегда ставьте долларовые значки на диапазон Таблицы, делая ссылку абсолютной.
  3. Номер колонки должен соблюдаться даже тогда, когда какие-то колонки спрятаны (hidden).
  4. Формат значений должен быть одинаковым в обоих таблицах:
    • формат должен быть везде числовой (если видите зелёный треугольник в левом верхнем углу, значит формат числа выставлен текстовым — нажмите на него — и формат станет числовым).
    • соблюдение нулей (например в случае с числами 01, 02 это не то же самое что 1, 2).
    • количество пробелов должно быть одинаковым.
  5. Таблицы должны всегда оставаться в одном и том же месте.
  6. В последнем аргументе всегда надо ставить 0 (или FALSE), чтобы искать точные данные.
  7. Только в Excel 2003: Если Ваши таблицы находятся в разных файлах, необходимо убедиться, что вы сохранили эти файлы хотя бы 1 раз и хотя бы 1 раз закрывали эти файлы. В противном случае формула не будет работать.
[embeddoc url=»http://www.tehnoslon.com.ua/Excel/vlookup-1.xlsx» viewer=»microsoft»] Скачать пример:
Скачать

Если вас интересует ещё одна интересная формула Excel, то вас может заинтересовать следующая статья: Функция СЧEТЕСЛИ в Excel

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.