Функція VLOOKUP (ВПР)

Назва в Excel
VLOOKUP
У російській версії
ВПР
Версії Excel
2003200720102013365
Категорія
Посилання та масиви

Функція VLOOKUP (у російській версії Excel — ВПР) є однією з найчастіше вживаних і універсальних при роботі з даними в Excel. Вона має недоліки, але її обов’язково слід опанувати для розуміння механізму роботи з даними в Excel. Увага!!! У серпні 2019 Microsoft представила заміну для VLOOKUP — функцію XLOOKUP, яка краща в усьому, але вимагає підтримки динамічних масивів

Назви функцій подано англійською, як в українському Excel. Скриншоти й книги-приклади — з російської версії.

Опис функції VLOOKUP

У російській версії Excel ця функція називається ВПР. VLOOKUP виконує пошук вказаного значення в певному діапазоні (або таблиці з кількох стовпців) і повертає значення, яке знаходиться в тому ж рядку, що й шукане, з цього ж діапазону. Наглядно алгоритм дій виглядає так:

Простий приклад роботи функції VLOOKUP
Простий приклад роботи функції VLOOKUP
  • Функція посилається на ідентифікатор «40», тобто значення, яке потрібно знайти в першому стовпці діапазону A3:D9, і повертає значення з 4-го стовпця того самого рядка, де знаходиться ідентифікатор 40 — це значення зарплати 87000
  • =VLOOKUP(G2;A3:D9;4;0)
  • У другому випадку функція посилається на ідентифікатор «12», тобто значення, яке потрібно знайти в першому стовпці діапазону A3:D9, і повертає значення з 3-го стовпця того самого рядка, де знаходиться ідентифікатор 12 — це прізвище Лінкольн
  • =VLOOKUP(G6;A3:D9;3;0)

В обох випадках як четвертий параметр стояв «0» — цей параметр називається «інтервальний_пошук». Його можна записувати як «0» або «1», а також як FALSE або TRUE. Детальніше про цей аргумент нижче в розділі «Синтаксис»

На перший погляд може здатися трохи заплутано, але насправді використовувати цю опцію доволі просто.

Буква «V» у назві функції VLOOKUP означає «vertical» (вертикально). VLOOKUP застосовують замість функції HLOOKUP, якщо порівнювані значення розташовані в стовпці ліворуч від шуканих даних.

Ось інший приклад, що наочно демонструє принцип роботи VLOOKUP:

Принцип роботи функції VLOOKUP з категорії «Посилання й масиви»
Принцип роботи функції VLOOKUP з категорії «Посилання й масиви»

Синтаксис

=VLOOKUP(шукане_значення; таблиця; номер_стовпця; [інтервальний_пошук])

Аргументи

шукане_значення

Обов'язковий. Значення, яке має бути знайдене в першому стовпці таблиці або діапазону. Аргумент шукане_значення може бути значенням або посиланням. Якщо шукане_значення менше, ніж найменше значення в першому стовпці аргументу таблиця, функція VLOOKUP повертає помилку #N/A.

таблиця

Обов'язковий. Діапазон клітинок, що містить дані. Можна використовувати посилання на діапазон (наприклад, A2:D8) або ім'я діапазону. Значення в першому стовпці аргументу таблиця — це значення, у яких виконується пошук аргументу шукане_значення. Ці значення можуть бути текстовими, числовими або логічними. Текстові значення в нижньому й верхньому регістрах вважаються еквівалентними.

номер_стовпця

Обов'язковий. Номер стовпця в аргументі таблиця, з якого повертається знайдене значення. Якщо номер_стовпця дорівнює 1, повертається значення з першого стовпця аргументу таблиця; якщо номер_стовпця дорівнює 2 — значення з другого стовпця аргументу таблиця і т. д.

Якщо значення аргументу номер_стовпця:

  • менше 1, функція VLOOKUP повертає помилку #VALUE!;
  • більше, ніж число стовпців в аргументі таблиця, функція VLOOKUP повертає помилку #REF!.

інтервальний_пошук

Необов'язковий. Логічне значення, що визначає, яке співпадіння має знайти VLOOKUP — точне чи приблизне.

Якщо аргумент інтервальний_пошук має значення TRUE або опущений, повертається точне або приблизне співпадіння. Якщо точне співпадіння не знайдено, повертається найбільше значення, яке менше за шукане_значення.

Увага! Якщо аргумент інтервальний_пошук має значення TRUE або опущений, значення в першому стовпці аргументу таблиця мають бути впорядковані за зростанням, інакше VLOOKUP може повернути неправильний результат.

Якщо аргумент інтервальний_пошук має значення FALSE, VLOOKUP шукає лише точне співпадіння. Якщо в першому стовпці аргументу таблиця є кілька значень, що відповідають шукане_значення, використовується перше знайдене значення. Якщо точне співпадіння не знайдено, повертається помилка #N/A.

Зауваження

  • При пошуку в великому масиві, особливо якщо на аркуші Excel багато формул, у користувача можуть виникати проблеми з продуктивністю. Перше, що спадає на думку — це оновити комп'ютер. Так, VLOOKUP вважається досить повільною, проте мало хто знає, що її швидкість можна збільшити в десять разів, якщо встановити аргумент рівним TRUE (або 1). Тут є нюанс у вигляді обов'язкового сортування, тому таке прискорення працюватиме не завжди. До речі, швидкість зростає через сортування, у цьому випадку немає потреби порівнювати кожне значення з стовпця.
  • Функція VLOOKUP завжди виконує пошук у самому першому (лівому) стовпці діапазону. Обмеження можна обійти, де це можливо, звузивши аргумент таблиця, щоб шуканий стовпець став першим
    Пошук VLOOKUP завжди в самому лівому стовпці
    Пошук VLOOKUP завжди в самому лівому стовпці
  • При пошуку текстових значень у першому стовпці аргументу таблиця переконайтеся, що дані в цьому стовпці не містять початкових або кінцевих пробілів, неправильних прямих ( ‘ або » ) чи парних ( ‘ або “ ) лапок або непечатних символів. У таких випадках VLOOKUP може повернути неправильне або неочікуване значення
    Повернення помилки функцією VLOOKUP
    Повернення помилки функцією VLOOKUP
  • При пошуку числових значень або дат переконайтеся, що дані в першому стовпці аргументу таблиця не є текстовими. В іншому випадку VLOOKUP може повернути неправильне або неочікуване значення
  • Якщо значення аргументу «інтервальний_пошук» — FALSE, а аргумент шукане_значення є текстом, то в аргументі шукане_значення дозволяється використання підстановочних знаків: знак питання (?) і зірочка (*). Знак питання відповідає будь-якому одиночному символу; зірочка — будь-якій послідовності символів. Якщо потрібно знайти сам знак питання або зірочку, поставте перед ними тильду (~)
    Використання підстановок при пошуку текстових значень
    Використання підстановок при пошуку текстових значень

Нюанси використання VLOOKUP

Коли користувач працює з VLOOKUP, можуть виникати різні ситуації, коли щось іде не так або потрібно обійти певні обмеження. Тут розглядаються такі нюанси використання й рішення потенційних проблем.

Пошук у першому стовпці

Як уже зазначалося в зауваженнях, одним із мінусів є те, що пошук виконується тільки в найлівішому стовпці.

Може статися, що стовпець із потрібними значеннями розташований лівіше від стовпця, у якому має відбуватися пошук.

Якщо потрібно шукати лівіше від шуканого стовпця, слід використовувати зв'язку INDEX + MATCH. Розуміння роботи цієї зв'язки дещо складніше, ніж VLOOKUP, але, опанувавши її, використовувати INDEX + MATCH буде нескладно.

Пошук до першого співпадіння

Особливістю роботи VLOOKUP є пошук лише до першого співпадіння. Якщо перший стовпець діапазону не є унікальним, буде знайдено лише перше входження. Іноді це саме те, що потрібно, іноді — ні, тому варто мати це на увазі.

Демонстрація пошуку VLOOKUP до першого співпадіння
Демонстрація пошуку VLOOKUP до першого співпадіння

Це обмеження можна обійти — рішення непросте, але детально описане в курсі Excel.

Незалежність від регістру

Для Excel і VLOOKUP не важливо, у якому регістрі (великі чи малі літери) записане шукане значення і як воно записане в діапазоні.

Незалежність від регістру
Незалежність від регістру

Зайві пробіли

У примітках ми показали, як непомітний пробіл може спричинити помилку в роботі цієї функції. Якщо є ризик, що клітинки засмічуються надмірною кількістю пробілів, слід очистити клітинки за допомогою функції TRIM.

Різниця в форматі даних

Якщо один аргумент VLOOKUP посилається на клітинку, що містить число у текстовому форматі, а початковий стовпець масиву має числовий формат (або навпаки), пошук не дасть результату.

Усе можна виправити приведенням аргументів до однакового формату. Наприклад:

=VLOOKUP(--G2; A3:D9; 4; 0)

— якщо в D5 букви, а в таблиці — цифри;

=VLOOKUP(G2 & ""); A3:D9; 4; 0)

— або навпаки.

Узгодження формату прямо в формулі
Узгодження формату прямо в формулі

Існує кілька варіантів, як перетворити текст у число:

  • Піднести до степеня: G2^1;
  • Подвійне заперечення: --G2;
  • Додати нуль: G2+0;
  • Помножити на одиницю: G2*1.

Припинення появи помилки #N/A

Якщо є ймовірність виникнення помилки під час обчислення формули, можна скористатися функцією IFERROR, яка поверне задане значення у випадку помилки.

Не зафіксовано масив

У наведених вище прикладах ми не використовували фіксацію масиву, бо завжди шукали одне значення, тобто не користувалися автозаповненням.

Однак у більшості випадків VLOOKUP ви будете використовувати саме з автозаповненням. У такому випадку найкраще застосувати абсолютне посилання для фіксації діапазону пошуку.

Масив можна оформити як розумну таблицю. Команда «Форматувати як таблицю» на вкладці «Основне», або гаряче поєднання Ctrl+T. У такому випадку посилання на такий діапазон автоматично стануть абсолютними. Але при роботі з такими таблицями в Excel є свої нюанси, і не завжди це підходить.

Фіксація діапазону в VLOOKUP
Фіксація діапазону в VLOOKUP

Відносний пошук

При використанні VLOOKUP користувачі, як правило, завжди ставлять аргумент інтервальний_пошук рівним «0» (FALSE), що означає виконати точний пошук і що значення в першому стовпці не відсортовані. Проте якщо пошук здійснюється за числовими значеннями, в деяких випадках гарним рішенням буде відсортувати значення та виконати неточний пошук. А в деяких випадках неточний пошук взагалі буде єдиним рішенням. Розглянемо детальніше.

Розглянемо докладно приклади з неточним співпадінням, і тут не зайвим буде нагадати, що якщо точне співпадіння не знайдено, повертається найбільше значення, що менше за шукане_значення.

Пошук оцінки

Сьогодні оцінку часто можна зустріти в двох форматах: у балах і у буквено-символьному позначенні, наприклад:

  • 0 — 60: F
  • 60 — 70: D
  • 70 — 80: C
  • 80 — 90: B
  • 90 — 100: A

Ось як виглядає формула, якщо для її побудови використовувати логічну функцію IF (так-так, з використанням SWITCH або IFS рішення теж буде кращим, але припустимо, у вас не найновіший Excel, і тут VLOOKUP виграє навіть у нових логічних функцій):

Вишукане рішення з VLOOKUP для пошуку оцінки
Вишукане рішення з VLOOKUP для пошуку оцінки

подивіться на рисунку на формулу з IF і порівняйте з VLOOKUP з неточним співпадінням — остання виглядає значно краще:

=VLOOKUP(E1;A2:B6;2;1)

Максимальне значення

Для пошуку максимального значення є більш підходяща функція MAX, однак вона просто знайде максимальне число, а от за допомогою VLOOKUP ми можемо знайти інше значення в таблиці, що стоїть в одному рядку з максимальним. Наприклад, у нас є стовпець із зарплатою співробітників і потрібно знайти прізвище співробітника з максимальною зарплатою.

Оскільки, якщо VLOOKUP не знаходить точного співпадіння, функція поверне максимальне значення, що менше за шукане_значення, то нам достатньо ввести дуже велике число, яке гарантовано більше зарплати будь-якого співробітника. Головне не забути відсортувати дані за зарплатою.

VLOOKUP для пошуку максимального значення
VLOOKUP для пошуку максимального значення

В'язкість рідини

У прикладах ви знайдете класичний приклад використання VLOOKUP для визначення в'язкості рідини або температури за щільністю. Чому класичний? Тому що в таких випадках, як правило, точні значення щільності не вказують — через округлення до тисячних отримане значення може не збігатися з табличним, доводиться брати максимальне значення, що менше за шукане_значення.

Приклад

Великий приклад з відео

Приклад про в'язкість рідини

Відео роботи функції

Повна інструкція VLOOKUP

Полная инструкция ВПР · YouTube

Ще про VLOOKUP

Еще про ВПР · YouTube

Порівняння двох файлів

Сравнение двух файлов · YouTube

Розв'язання задач #ExcelWorldChamp

Решение задач #ExcelWorldChamp · YouTube

Додаткові матеріали

Як порівняти два файли MS Excel
#Excelwordlchamp 1-1 Розв'язуємо задачі першого туру