Знайти значення в таблиці допоможе функція VLOOKUP в Excel приклади якої описані нижче в статті.
Під час роботи з програмою у користувачів часто з’являється необхідність швидкого пошуку інформації в одній таблиці і перенесення її на інший об’єкт листа.
Розуміння принципу роботи ВВР суттєво спростить вашу роботу в Excel і допоможе швидше виконувати завдання.
Зміст:
VLOOKUP (Vertical Lookup) – це ще одна назва функції, яке можна зустріти в англомовній версії табличного процесора.
Сама абревіатура ВВР означає «вертикальний перегляд».
Аналіз даних та їх пошук в таблиці здійснюється за допомогою поступового перебору елементів від рядка до рядку, в кожній колонці.
Також, в Excel є протилежна функція під назвою HLOOKUP або ДПР – горизонтальний перегляд.
Єдина відмінність роботи опцій полягає в тому, що ДПР виробляє пошук в таблиці по перебору стовпців, а не строчок.
Найчастіше користувачі віддають перевагу саме функції ВВР, адже більшість таблиць мають більше рядків, ніж стовпців.
Зміст
Як виглядає синтаксис ВВР?
Синтаксис функції в Excel – це набір параметрів, за допомогою яких її можна викликати і поставити. Запис аналогічна з методом запису математичних функцій.
Подивитися правильний вид опції можна, відкривши табличний процесор:
- Використовуйте вже створений документ, або відкрийте новий порожній лист;
- Натисніть на клавішу «Формули», як показано на малюнку нижче;
- У рядку пошуку введіть «ВВР» або «VLOOKUP» в залежності від мови програми;
- Налаштуйте категорію «Повний перелік»;
- Натисніть на «Знайти».
Рис.2 – пошук формул в Ексель
В результаті пошуку формули ви побачите її знаходження в переліку. Натиснувши на елемент, внизу екрана відобразиться його формула.
За дужками вказується ім’я функції, а в дужках – її параметри. Всередині формули кожен окремий параметр прописується в кутових скобах.
Загальний вигляд опису для ВВР виглядає так:
Рис.3 – перелік параметрів
Розглянемо детальніше кожне із значень, яке описується у дужках:
- — перший елемент. Замість нього вам потрібно прописати саме те значення, яке ви хочете знайти в таблиці. Також, можна вписувати адресу комірки в таблиці;
- — тут потрібно надрукувати номер стовпчика, у рамках якого буде здійснюватися перебір даних.
- — тут юзер визначає кількість комірок, ставлячи їх розмірність у вигляді двовимірного масиву даних. Перший стовпчик – це елемент «ЩО»;
- — цей елемент функції ВВР відповідає за сортування першого стовпця за зростанням (перший стовпчик для «ДЕ»). В результаті успішної сортування, значення стає істинним (одиниця). Якщо виникають які-небудь неточності або помилки під час введення параметрів – з’являється помилкове значення сортування (нуль). Варто зауважити, що під час завдання ВВР можна пропустити, і тоді його значення за замовчуванням приймається як істина.
Читайте також:
Зведені таблиці Excel — Приклади створення
Практичні поради — Як об’єднати комірки в Excel
Як закріпити рядок в Excel — Докладні інструкції
Як працює ВВР. Корисний приклад
Щоб краще зрозуміти принцип роботи VLOOKUP, перейдемо до розгляду конкретних прикладів. Візьмемо найпростішу таблицю з двома колонками. Нехай вона позначає код і найменування товару.
Після заповнення таблиці кликнемо на порожню комірку і випишемо в неї формулу і результат ВВР. Клацніть на вкладку «Формули» і виберіть VLOOKUP.
Потім введіть всі необхідні параметри в вікно, яке зображено на малюнку 3. Підтвердіть дію. У комірці з’явиться результат виконання команди.
Рис.4 – приклад пошуку в простій таблиці
На малюнку вище в кольорових комірках значення для товару. Якщо ви не ввели значення для сортування, то функція автоматично сприймає це як одиницю.
Далі програма «думає», що елементи першого стовпчика вашої таблиці йдуть за зростанням зверху-вниз.
Завдяки цьому, процедура пошуку буде зупинена тільки коли буде досягнута рядок зі значенням, номер якого вже перевищує шуканий об’єкт.
Розглянемо ще один приклад використання функції, яка часто зустрічається під час реальної роботи з прайсами та листами найменувань товару.
У випадку, коли користувач пропечатує, що останній елемент в дужках дорівнює нулю, Ексель працює наступним чином: опція перевіряє самий перший стовпець в заданому діапазоні масиву.
Пошук буде зупинений автоматично, як тільки знайдеться збіг параметра «ЩО» і імені товару.
Якщо в таблиці немає введеного вами ідентифікатора для імені продукції, в результаті виконання пошуку VLOOKUP буде отримано значення «Н/Д», що означає відсутність елемента для заданого номера.
Рис.5 – другий приклад для ВВР
Коли використовувати ВВР?
Вище описані два варіанти застосування VLOOKUP.
Перша варіація VLOOKUP підійде для наступних випадків:
- Коли необхідно розділити значення об’єкта табличного процесора за його діапазонів;
- Для тих таблиць, в яких параметр ДЕ може містити кілька ідентичних значень. У такому разі формула поверне лише те, яке знаходиться на останній сходинці щодо масиву;
- Коли потрібно шукати значення, більше того, що може міститися в першому стовпчику. Так ви знайдете останній рядок таблиці практичним миттєво.
Перший варіант правопису VLOOKUP не може знайти елемент, якщо не було знайдено менше значення шуканого або дорівнює йому. У комірці для результату повернеться тільки «Н/Д».
Другий варіант для ВПР (з зазначенням «0» для сортування) застосовується для великих таблиць, в яких зустрічаються однакові назви для кількох комірок.
VLOOKUP дозволить легко оперувати даними, адже повертає першу знайдену рядок.
У реальному житті опція використовується, коли потрібно здійснити пошук по заданому діапазону – він не обов’язково повинен відповідати всій величині таблиці.
В об’єктах листа, в яких зустрічаються різні види значень, ВВР допомагає знайти текстові рядки.
Рис.6 – приклад пошуку текстового значення
ВВР буває корисна, коли потрібно видалити багато зайвих пробілів. Функція швидко знаходить всі назви з пробілами, і ви зможете швидко видалити їх. Приклад:
Рис.7 – ВВР видалення пробілів
Вам це може бути цікаво:
Ексель (Excel) для чайників: робота з таблицями, графіками, сортуванням даних і математичними розрахунками
Формули EXCEL з прикладами — Інструкція по застосуванню
Чим відкрити xml файл: 5 кращих програм
Швидкодія VLOOKUP
Більшість користувачів віддавати перевагу не вписувати параметр під час роботи з функцією. Звичайно ж, нуль вписати простіше, але ігнорування оператора уповільнює пошук.
При роботі з великими масивами даних Ексель може працювати дуже повільно. На старих пристроях табличний процесор іноді навіть зависає через занадто повільного пошуку з ВВР.
Якщо на одному аркуші вашого документа представлено відразу кілька тисяч формул, краще подбати про сортування першого стовпця.
Це дозволяє збільшити загальну продуктивність пошуку на цілих 400%-500%.
Така різниця в швидкості виконання різних видів VLOOKUP полягає в тому, що будь комп’ютерній програмі набагато простіше працювати з вже відсортованими даними, здійснюючи бінарний пошук.
У першому виді функції застосовується бінарний пошук, а у другому – ні, адже оптимізація цього способу задання функції все ще відсутня.
Тематичні відеоролики:
Видеопример використання функції ВПР в MS Excel
Розглядається конкретний приклад застосування функції ВПР для порівняння великої кількості даних з різних файлів Excel.
Як використовувати функцію ВПР (VLOOKUP) в Excel
Покрокова демонстрація застосування функції ВПР (VLOOKUP) для зв’язування двох таблиць, тобто підстановки даних з однієї таблиці в іншу
