Поєднання таблиць (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.

Статті по темі: