ВГОРУ

Поєднання таблиць (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 та нажміть ОК. З'явиться вікно для введення аргументів функції:

Заповніть їх один за одним:

Залишилось натиснути ОК і скопіювати введену функцію по всій колонці.

Недоліки функції 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.

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