Excel - это просто!

A+ A A-

Поисковые формулы (vlookup - ВПР, hlookup - ГПР)

Пожалуй, одна из самых полезных и ходовых формул в работе - vlookup (ВПР). Она же вызывает самое большое количество вопросов и сложностей. Данная статья описывает, как можно пользоваться этой формулой (а также ее аналогом для горизонтального поиска - hlookup - ГПР), какие чаще всего возникают проблемы, и как с ними бороться.

Зачем она нужна? Формула vlookup ищет некоторое значение в таблице. Если находит, то выводит из той же строчки значение, которое находится в нужной нам колонке. Если не находит, то выдает ошибку #N/A (#Н/Д)

Пример использования: у нас есть список номенклатурных номеров товаров, к ним необходимо подтянуть названия из таблицы с номенклатурными номерами;

Синтаксис: =VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)

Lookup_value - искомое значение - то, что мы ищем, в примере - конкретный номенклатурный номер.

Table_array - таблица, в которой мы будет искать совпадение. Что важно: первая колонка таблицы - это колонка, в которой мы будем искать lookup_value. Таблица может состоять из нескольких колонок, которые должны включать в себя ту, из которой мы будем брать значение. То есть для примера диапазон должен состоять из нескольких колонок, первой из которых будет номенклатурный номер, а последней - название товара.

Col_index_num - номер колонки в таблице Table_array. Первой колонкой будет колонка, в которой производится поиск. Второй - следующая, справа от нее. И так далее.

Range_lookup - искать точное совпадение или нет? Поставить FALSE - ЛОЖЬ - 0 для поиска точного совпадение или TRUE - ИСТИНА - 1 для неточного совпадения. На практике обычно используется точный поиск - не могу представить, для чего может потребоваться приблизительный поиск.

Предлагаю разобрать эту формулу на примере.

Дано:

Две таблицы. В первой есть код, к нему необходимо подтянуть наименование из второй таблицы. Вторая таблица содержит две колонки - код и наименование.

Решение:

Во второй колонке (где мы хотим получить наименование) прописываем следующую формулу:

=vlookup(A6;D:E;2;0)

(1) Что ищем - A6 - то есть код "1005".

(2) Где ищем (первая колонка должна быть с кодами, последняя - с искомым значением или после него) - D:E - то есть колонки от D до E - то есть всю таблицу, начиная с кодов и по наименование.

(3) Из какой колонки берем значение - 2. Считаем колонки: 1 - код, 2 - наименование. Посколько нам нужно наименование, то ставим значение 2.

(4) искать точное совпадение или нет? Да - ставим 0 (с таким же успехом можно было поставить FALSE или ЛОЖЬ, если вам не повезло работать в русском экселе).

Как работает:

Программа перебирает значение в колонке D и проверяет, совпадает ли оно с искомым значением "1005".

D1 - "Таблица с данными" не равно "1005" - пропускаем.

D2 - "" не равно "1005" - пропускаем.

D3 - "Код" не равно "1005" - пропускаем.

D4 - "1001" не равно "1005" - пропускаем.

D5 - "1002" не равно "1005" - пропускаем.

D6 - "1003" не равно "1005" - пропускаем.

D7 - "1004" не равно "1005" - пропускаем.

D8 - "1005" равно "1005" - отсчитываем вторую колонку (первая - D, вторая - E). Из той же строчки, где нашли значение "1005" из второй колонки (E) берем значение. Это "персик". Дальше не ищем.

То есть в ячейке B6 получится значение "персик".

Есть несколько важных вещей, на которые стоит обратить внимание, если вы собираетесь эффективно пользоваться формулой.

(1) Формула действительно перебирает все значения в колонке D. Если массив большой (а зачастую это может быть до миллиона значений), это может занимать какое-то время. Поэтому при использовании формулы стоит задумываться об оптимизации производительности. Об этом в одной из следующих статей.

(2) Формула выдает результат, соответствующий первому совпадению. Если один и тот же код встречается несколько раз и разными наименованиями, то формула во всех случаях будет выдавать значение, соответствующее первому совпадению.

(3) Искомое значение ОБЯЗАТЕЛЬНО должно быть в ПЕРВОЙ колонке таблицы. Если изначальная таблица, в которой вы собираетесь искать значение имеет колонку с результатом левее, чем та, по которой вы собираетесь искать, то одну из колонок придется переставить.

(4) Искомое значение должно быть в одном формате в таблице, где мы указываем, что искать, и в таблице, где мы ищем. Это самая частая проблема, с которой сталкиваются пользователи формулы vlookup. Предположим, что код 1005 в первой таблице представлен в формате числа, а во второй - в текстовом формате (это может случиться, например, при выгрузке номенклатуры из 1С или SAP). Формула не будет находить значение и будет выдавать ошибку #N/A, хотя поиск (CTRL + F) будет находить это значение. Для того чтобы решить эту проблему можно сделать одну из двух вещей: привести их к одному формату с помощью формул VALUE или TEXT (об этом в другой статье), или изменить формат. Обратите внимание, что если число записано как текст, то недостаточно просто изменить формат ячейки на числовой. Потребуется также зайти в каждую ячейку со значением и нажать Enter (применить изменение). Это не вариант, если у вас номенклатура, которая состоит за нескольких десятков тысяч наименований. Но как всегда есть обходной путь. В этом случае это функционал Найти и заменить - Find and replace (Ctrl + H). Обычно номенклатурный номер начинается с одной и той же цифры (в моем примере - с 1). Выделяем колонку, в которой необходимо изменить формат, и заменяем эту цифру на нее же (1 на 1). Формат изменится.

 

Hlookup - формула абсолютно аналогичная vlookup, только данные во второй таблице должны быть представлены не в колонках, а в строках и, соответственно, третий параметр - не номер колонки, а номер строки, из которой необходимо брать значение.

Эти и многие другие формулы описаны в книге Формулы в Microsoft Excel 2010. Книга - наш лучший помощник в обучении - почему бы не иметь хорошую книгу дома или на работе.

Последние новости

Как быстро удалить тысячи строк из громадной таблицы по филь…

Пошаговая инструкция по удалению множества строк, которые перемешаны в огромном массиве данных. Так, чтобы компьютер не ...

О нас

Напишите мне edward@youcanexcel.ru

О проекте

Копирование материалов сайта разрешено только с согласия владельца с размещением ссылки на источник.

Веб-сайт оптимизирован под разрешение не менее 1024x768.

Сайт отражается некорректно? Должно быть вы пользуетесь Internet explorer. Установите себе хороший браузер!