Как использовать формулу 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 таблицы. Вот они:
В Таблице № 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 или ИСТИНА), получите приблизительные данные.
Итак, мы добавляем следующие данные в следующие поля:
- Lookup_value (Искомое значение) = A3 (т.е. число 110 из 1ой колонки «# товара»)
- Table_array (Таблица) = $F$2:$G$6 (просто выбрали Таблицу 2 и зафиксировали её значками доллара)
- Col_index_num (Номер столбца) = 2 (№ столбца «Название товара» из Таблицы № 2)
- Range_lookup (Интервальный просмотр) = 0 (чтобы получить точные данные).
Ошибки Vlookup и их Причины
Виды ошибок формулы VLookup
- Колонка искомого значения (1го аргумента Lookup_value в формуле) находится справа от вводимой формулы.
- Формула не находит значения, т.к. съехала рамка выбранной таблицы (выбранный диапазон не зафиксирован долларовыми знаками). (ошибка #N/A или #Н/Д)
- Формула выбирает данные не из той колонки, хотя выбрано всё верно.
- Сравнивая числа [01], [ 1] (пробел слева и единица) и [1] формула считает их разными и не может найти сопоставимые им значения. (ошибка #VALUE или #ЗНАЧ)
- После перемещения файла с Таблицой № 2, формула больше не видит Таблицу № 2 и не может ничего найти. (ошибка #REF или #ССЫЛКА)
- Для чисел 110 и 111 формула находят одни и те же данные, т.к. считает их похожими.
- Только в Excel 2003: Всё сделано правильно, но формула не работает.
Советы и Способы решения проблем с формулой VLookup (соответственно номерам ошибок)
- Выбор таблицы (table_array) должен начинаться с колонки, в которой данные из обоих Таблиц (в данном случае это # товара).
- Всегда ставьте долларовые значки на диапазон Таблицы, делая ссылку абсолютной.
- Номер колонки должен соблюдаться даже тогда, когда какие-то колонки спрятаны (hidden).
- Формат значений должен быть одинаковым в обоих таблицах:
- формат должен быть везде числовой (если видите зелёный треугольник в левом верхнем углу, значит формат числа выставлен текстовым — нажмите на него — и формат станет числовым).
- соблюдение нулей (например в случае с числами 01, 02 это не то же самое что 1, 2).
- количество пробелов должно быть одинаковым.
- Таблицы должны всегда оставаться в одном и том же месте.
- В последнем аргументе всегда надо ставить 0 (или FALSE), чтобы искать точные данные.
- Только в Excel 2003: Если Ваши таблицы находятся в разных файлах, необходимо убедиться, что вы сохранили эти файлы хотя бы 1 раз и хотя бы 1 раз закрывали эти файлы. В противном случае формула не будет работать.
Скачать