ВПР у Excel функція, яка виступає за «вертикальний пошук» буде шукати значення в першому стовпчику діапазону, і повертає значення в будь-якому іншому стовпчику в тому ж рядку.
Якщо ви не можете визначити, яка комірка містить певні дані, VLOOKUP - дуже ефективний спосіб знайти ці дані. Це особливо корисно в гігантських електронних таблицях, де важко знайти інформацію.
Інструкції в цій статті належать до Excel для Office 365, Excel 2019, 2016, 2013, 2010, Excel для Mac і Excel Online.
Як працює функція VLOOKUP
VLOOKUP зазвичай повертає одне поле даних як вивід.
Як це працює:
- Ви надаєте назву або значення lookup_value, яке повідомляє VLOOKUP, в якому рядку таблиці даних шукати потрібні дані.
- Номер стовпчика вказується як аргумент col_index_num, який повідомляє VLOOKUP, в якому стовпчику містяться шукані дані.
- Функція шукає значення lookup_value у першому стовпчику таблиці даних.
- Потім VLOOKUP знаходить і повертає інформацію з номера стовпчика, визначеного вами в col_index_num, з того ж рядка, що і значення пошуку.
VLOOKUP Функціональні аргументи і синтаксис
Синтаксис функції VLOOKUP:
= ВПР (шукане _ значення, таблиця _ масив, номер _ стовпчик, інтервальний _ перегляд)
Функція VLOOKUP може бути заплутаною, оскільки містить чотири аргументи, але її легко використовувати.
Ось чотири аргументи для функції VLOOKUP:
lookup_value (обов'язково): значення для пошуку в першому стовпчику масиву таблиці.
table_array (обов'язково) - це таблиця даних (діапазон комірок), яку VLOOKUP шукає, щоб знайти потрібну вам інформацію.
- Таблиця _ array має містити принаймні два стовпчики даних
- Перший стовпчик повинен містити lookup_value
col_index_num (обов'язково) - це номер стовпчика значення, яке ви хочете знайти.
- Нумерація починається зі стовпчика 1
- Якщо ви посилаєтеся на число, що перевищує кількість стовпчиків у масиві таблиці, функція поверне # REF! помилка
range_lookup (необов'язково) - вказує, чи потрапляє значення пошуку в діапазон, що міститься в масиві таблиці. Аргумент range_lookup має значення «ІСТИНА» або «БРЕХНЯ». Використовуйте TRUE для приблизного збігу і FALSE для точного збігу. Якщо опущено, значення TRUE типове.
Якщо аргумент range_lookup дорівнює TRUE, то:
- Lookup_value - це значення, яке ви хочете перевірити, чи потрапляє воно в діапазон, визначений table_array.
- Аргументtable_array містить всі діапазони і стовпчики, що містять значення діапазону (наприклад, високий, середній або низький).
- Аргумент col_index_num є результуючим значенням діапазону.
Як працює аргумент Range_Lookup
Використання необов'язкового аргументу range_lookup складно зрозуміти багатьом, тому варто поглянути на швидкий приклад.
Приклад на зображенні вище використовує функцію VLOOKUP, щоб знайти ставку дисконтування залежно від кількості придбаних товарів.
У прикладі показано, що знижка на купівлю 19 товарів становить 2%, оскільки 19 знаходиться між 11 і 21 у стовпчику «Кількість» довідкової таблиці.
У результаті VLOOKUP повертає значення з другого стовпчика таблиці пошуку, оскільки у цьому рядку міститься мінімум цього діапазону. Інший спосіб налаштувати таблицю пошуку діапазону - це створити другий стовпчик для максимуму, і цей діапазон буде мати мінімум 11 і максимум 20. Але результат працює так само.
У прикладі використовується наступна формула, що містить функцію VLOOKUP, щоб знайти знижку на кількість придбаних товарів.
= ВПР (С2, $ C $5: $ D $ 8,2, TRUE),
- C2: це значення пошуку, яке може знаходитися в будь-якій комірці електронної таблиці.
- $ C $ 5: $ D $ 8: це фіксована таблиця, що містить всі діапазони, які ви хочете використовувати.
- 2: Це стовпчик у таблиці пошуку діапазону, який ви хочете повернути функції LOOKUP.
- TRUE: включає функцію range_lookup цієї функції.
Після того як ви натиснули Enter і результат повернувся до першої комірки, ви можете автоматично заповнити весь стовпчик, щоб переглянути результати діапазону для інших комірок у стовпчику пошуку.
Аргумент range_lookup - це переконливий спосіб сортування стовпчика змішаних чисел за різними категоріями.
Помилки VLOOKUP: # N/A і # REF
Функція VLOOKUP може повертати такі помилки:
# N/A є помилкою «значення недоступне» і виникає за таких умов:
- Значення _ upue пошуку не знайдено в першому стовпчику аргументу table_array
- Таблиця _ масив аргумент є неточним. Наприклад, аргумент може містити порожні стовпчики в лівій частині діапазону
- Діапазон _ перегляду аргумент встановлено у FALSE, а точну відповідність для Lookup_Value аргументу не можна знайти в першому стовпчику table_array
- Інтервальний _ перегляд аргумент встановлений в TRUE, а всі значення в першому стовпчику table_array більше, ніж lookup_value
#REF! («посилання поза діапазоном») помилка виникає, якщо col_index_num більше, ніж число стовпчиків у table_array.
