Сочетание таблиц (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) если:

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

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

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

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