Пожалуй, одна из самых полезных и ходовых формул в работе — 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. Книга — наш лучший помощник в обучении — почему бы не иметь хорошую книгу дома или на работе.