Функція ВПР в Excel приклади використання + поради

Знайти значення в таблиці допоможе функція ВПР в Excel приклади якої описані нижче в статті.

Під час роботи з програмою у користувачів часто виникає необхідність швидкого пошуку інформації в одній таблиці і перенесення її в інший об’єкт листа.

Розуміння принципу роботи ВВР істотно спростить вашу роботу в Excel і допоможе швидше виконувати завдання.

VLOOKUP (Vertical Lookup) – це ще одна назва функції, яке можна зустріти в англомовній версії табличного процесора.

Сама абревіатура ВВР означає «вертикальний перегляд».

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

Також, в Excel є протилежна функція під назвою HLOOKUP або ГПР – горизонтальний перегляд.

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

Найчастіше користувачі віддають перевагу саме функції ВПР, адже більшість таблиць мають більше рядків, ніж стовпців.

Як виглядає синтаксис ТВП?

Синтаксис функції в Excel – це набір параметрів, за допомогою яких її можна викликати і задати. Запис аналогічна з методом запису математичних функцій.

Подивитися правильний вид опції можна, відкривши табличний процесор:

  • Використовуйте вже створений документ, або відкрийте новий порожній лист;
  • Натисніть на клавішу «Формули», як показано на малюнку нижче;
  • У рядку пошуку надрукуйте «ВПР» або «VLOOKUP» в залежності від мови програми;
  • Налаштуйте категорію «Повний перелік»;
  • Натисніть на «Знайти».

Рис.2 – пошук формул в Ексель

В результаті пошуку формули ви побачите її знаходження в переліку. Натиснувши на елемент, внизу екрану відобразиться його формула.

За дужками вказується назва функції, а всередині дужок – її параметри. Усередині формули кожен окремий параметр прописується в кутових скобах.

Загальний вигляд опису для ВПР виглядає так:

Рис.3 – перелік параметрів

Розглянемо детальніше кожне з значень, яке описується в дужках:

  • – перший елемент. Замість нього вам потрібно прописати саме те значення, яке ви хочете знайти в таблиці. Також, можна вписувати адресу осередки в таблиці;
  • – тут потрібно надрукувати номер стовпчика, в рамках якого буде здійснюватися перебір даних.
  • – тут юзер визначає кількість осередків, задаючи їх розмірність в вигляді двовимірного масиву даних. Перший стовпчик – це елемент «ЩО»;
  • – цей елемент функції ВПР відповідає за сортування першого стовпчика по зростанню (перший стовпчик для «ДЕ»). В результаті успішної сортування, значення стає справжнім (одиниця). Якщо виникають будь-які неточності або помилки під час введення параметрів – з’являється помилкове значення сортування (нуль). Варто зауважити, що під час завдання ВВР можна пропустити, і тоді його значення за замовчуванням приймається як істина.

Як працює ВВР. корисний приклад

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

Після заповнення таблиці кликнемо на вільну позицію і випишемо в неї формулу і результат ВВР. Натисніть на вкладку «Формули» і виберіть VLOOKUP.

Потім введіть всі необхідні параметри в вікно, яке зображено на малюнку 3. Підтвердіть дію. В осередку відобразиться результат виконання команди.

Рис.4 – приклад пошуку в простій таблиці

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

Далі програма «думає», що елементи першого стовпчика вашої таблиці йдуть по зростанню зверху-вниз.

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

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

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

Пошук буде зупинено автоматично, як тільки знайдеться збіг параметра «ЩО» і імені товару.

Якщо в таблиці немає введеного вами ідентифікатора для імені продукції, в результаті виконання пошуку VLOOKUP буде отримано значення «Н / Д», що означає відсутність елемента для заданого номера.

Рис.5 – другий приклад для ВПР

Коли використовувати ВПР?

Вище описані два варіанти застосування VLOOKUP.

перша варіація VLOOKUP підійде для таких випадків:

  • Коли необхідно розділити значення об’єкта табличного процесора по його діапазонами;
  • Для тих таблиць, в яких параметр ДЕ може містити кілька ідентичних значень. В такому випадку, формула поверне тільки те, яке знаходиться в останньому рядку щодо масиву;
  • Коли потрібно шукати значення, які більше того, що може міститися в першому стовпчику. Так ви знайдете останній рядок таблиці практичним миттєво.

Перший варіант правопису VLOOKUP не може знайти елемент, якщо не було знайдено значення менше шуканого або рівне йому. В осередку для результату повернеться тільки «Н / Д».

Другий варіант для ВПР (Із зазначенням «0» для сортування) застосовується для великих таблиць, в яких зустрічаються однакові назви для кількох осередків.

VLOOKUP дозволить легко оперувати даними, адже повертає першу знайдену рядок.

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

В об’єктах листа, в яких зустрічаються різні види значень, ВВР допомагає знайти текстові рядки.

Рис.6 – приклад пошуку текстового значення

ВПР буває корисна, коли потрібно видалити багато зайвих прогалин. Функція швидко знаходить все найменування з пробілами, і ви зможете швидко видалити їх. приклад:

Рис.7 – ВПР при видаленні прогалин

Вам це може бути цікаво:

Ексель (Excel) для чайників: робота з таблицями, графіками, сортуванням даних і математичними розрахунками

Формули EXCEL з прикладами – Інструкція по застосуванню

Чим відкрити xml файл: 5 кращих програм

швидкодія VLOOKUP

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

При роботі з великими масивами даних Ексель може працювати дуже повільно. На старих пристроях табличний процесор іноді навіть зависає через занадто повільного пошуку з ВПР.

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

Це дозволяє збільшити загальну продуктивність пошуку на цілих 400% -500%.

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

У першому виді функції застосовується бінарний пошук, а в другому – немає, адже оптимізація цього способу завдання функції все ще відсутня.

Тематичні відеоролики:

Відеопрімер використання функції ВПР в MS Excel

Розглядається конкретний приклад застосування функції ВПР для порівняння великої кількості даних з різних файлів Excel.

Ссылка на основную публикацию