Сочетание таблиц (VLOOKUP)

Что такое VLOOKUP

Встроенная функция VLOOKUP – одна из наиболее мощных функций в Excel. Она принадлежит к тройке самых популярных в Excel – после SUM и AVERAGE. Ее задача – найти нужное значение в таблице данных и вывести его в заданную ячейку.

Синтаксис функции включает в себя поисковое значение (то, что вы ищете), таблицу с одной или более колонками (где искать), номер индекса столбца (из которого столбца возвращать данные) и тип поиска (дополнительный аргумент, позволяющий выбрать приблизительный или точный результат).

Функция VLOOKUP обычно используется для финансового анализа, управления данными и работы с базами данных. Используя эту функцию, пользователи могут быстро и легко найти информацию в большом наборе данных, что может сэкономить время и повысить точность.

Функция VLOOKUP ищет значение по вертикали, то есть среди строк (на это указывает первая буква V – Vertical). В Excel, также, похожа функция HLOOKUP, которая имеет аналогичный функционал, однако делает горизонтальный (H - Horizontal) поиск по столбцам.

Пример задачи

Допустим мы имеем две даблицы с данными - таблицу кредитов и таблицу обеспечения :

Нам необходимо автоматически подставить обеспечение в таблицу кредитов, ориентируясь по номеру сделки для последующего формирования отчетности.

Решение

В Excel в стандартном наборе функций в категории Lookup and reference есть функция VLOOKUP . Эта функция ищет указанное значение (в нашем примере номер сделки) в крайней левой колонке указанной таблицы (таблицы обеспечения) двигаясь с горы вниз и, находя их, отображает значение соседней ячейки (тип обеспечения по кредиту). Схематически работа функции выглядит так:

Итак, используем функцию VLOOKUP . Выберите ячейку, где она будет введена ( E2 ) и откройте мастера формул (меню Insert - Function ). В категории (Lookup and Reference) найдите функцию VLOOKUP и нажмите ОК . Появится окно для ввода аргументов функции:

Заполните их один за другим:

• Lookup Value - номер сделки, которую функция будет искать в крайней левой колонке таблицы обеспечения. В нашем случае – номер сделки "KF568" из ячейки С2 .
• Table Array - таблица, из которой мы будем получать искомые значения, наша таблица обеспечена. Для ссылки мы используем диапазон $G$2:$H$11 (диапазон фиксируем знаком доллара ($) , для того чтобы он нам не смещался вниз при копировании формулы).
• Column index number - порядковый номер (не буква!) колонки в таблице обеспечения, из которой мы будем брать название обеспечения. Первая колонка таблицы обеспечения имеет порядковый номер 1, следовательно, нам следует взять тип обеспечения из второй колонки (номер 2).
• Range Lookup - в это поле мы можем ввести только два значения: TRUE или FALSE:
  
  1) Если мы введем значение 0 или FALSE , то это на самом деле означает, что разрешенный поиск только с точным совпадением , то есть если функция не найдет в таблице обеспечения номер сделки (если он отсутствует), тогда отобразится ошибка #N/A (нет данных).
  
  2) Если мы введем значение 1 или TRUE , это будет означать, что разрешен поиск не только точного, но и приблизительного совпадения . В большинстве случаев такая приблизительная подстановка может вызвать ложный результат, когда VLOOKUP будет подставлять значение других сделок. Итак, примерный поиск вам следует использовать только для цифровых значений (предварительно отсортированных по возрастанию).

Осталось нажать ОК и скопировать вводимую функцию по всей колонке.

Недостатки функции VLOOKUP

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

Второй недостаток VLOOKUP состоит в том, что функция перестает работать, если убрать или добавить столбец в таблицу поиска. Вставленный или удаленный элемент изменит результат формулы, поскольку синтаксис функции требует указывать весь диапазон и конкретный номер столбца, из которого необходимо удалить данные.

Кроме того, функция VLOOKUP имеет ограничение по длине искомого значения в 255 символов, иначе будет возвращена ошибка #VALUE!

Проблемы при работе с функцией

Функция VLOOKUP возвращает ошибку (#N/A) если:

1. Разрешен точный поиск (аргумент) Range Lookup=0) и искомого значения нет в таблице (Table) .
2. Разрешен приблизительный поиск (Range Lookup=1), но в таблице (Table) , в которой мы ищем значение, названия не рассортированы по росту.
3. Формат ячейки, из которой мы берем номер сделки (например, С2 в нашем случае) и формат ячейки первой колонки (G2:G11) таблицы разные (например, цифровой и текстовый). Эта ситуация особенно типична, когда используются цифровые коды вместо текстовых названий (номера счетов, идентификационные коды, даты и т.п.). В этом случае вы можете использовать функции VALUE да TEXT для преобразования форматов данных Это выглядит так:
   =VLOOKUP(TEXT(C2);$G$2:$H$11;0) .
4. Функция не находит значения потому, что в коде есть пробелы и невидимые непечатаемые символы (переносы ленты и т.п.). В этом случае вы можете использовать текстовую функцию (TRIM) да (CLEAN) для их удаления:
   =VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0) .

Подавление ошибок

Для подавления сообщения об ошибке (#N/A) , когда функция не может найти точное совпадение, вы можете использовать функцию IFERROR . Данная функция проверяет, ошибка ли (#N/A) является результатом работы VLOOKUP , и если да, то выводит пустую ленту ( "" ) или ноль, если нет - результат работы VLOOKUP .

Статьи по теме:

• Улучшенная функция VLOOKUP (VLOOKUP2)
• VLOOKUP для сочетания по двум условиям (VLOOKUP3)
• Быстрое объединение больших таблиц (VLOOKUP2D)