Vlookup Excel: як користуватися (приклади)

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

Необхідність ВВР

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

Синтаксис функції

Vlookup Excel function= ВВР по-російськи. Синтаксис цієї функції такий: ВВР (значение_заданное_для_поиска, таблица_в_которой_осуществляется_поиск, номер_стовпчика,[истина_ложь]). З допомогою функції ВВР здійснюється пошук значень у крайньому лівому стовпці таблиці, при цьому відбувається повернення значень клітинки, що міститься в цьому стовпчику в тому ж рядку.


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

Приклад використання ВВР

Як користуватися Vlookup в Excel, виходячи з наведеного вище синтаксису? Розглянемо приклад, коли у нас в таблиці задані ПІБ, дати прийняття на роботу і відділи, в яких дані працівники функціонують. Припустимо, що нам з однієї таблиці в іншу потрібно підставити значення дати прийняття. У тому випадку, якщо значень трохи, це можна виконати вручну.


Але якщо прізвищ багато, вони розташовані в різних таблицях в хаотичному порядку, число прізвищ неоднакове, та до того ж вони розташовані на різних листах електронної таблиці, то задача значно ускладнюється. Або інший приклад Vlookup в Excel є прайс-лист, в якому вказані ціни конкретних товарів, і є таблиця, в яку потрібно перетягнути ці ціни, вбити число проданих товарів для того, щоб автоматично высчитался товарообіг за день. Функція Vlookup (ВВР) шукає значення, яке використовується для пошуку, у лівому стовпчику заданої таблиці, при цьому пересування здійснюється зверху вниз. Припустимо, що у розглянутому прикладі використання Vlookup в Excel здійснюється пошук товарів, об'єднаних під загальною назвою «Корми для кішок». За замовчуванням колонка таблиці з прайс-листами «Найменування товару» відсортовується за зростанням. Формат даної колонки в двох таблицях повинен бути однаковим. Клікаємо на комірці таблиці розрахунку товарообігу, в якій буде вставлятися ціна з прайс-листа. На панелі інструментів натискаємо на іконку fx і вибираємо функцію ВВР, яку можна знайти пошуком, а також переглядаючи категорію «Посилання та масиви». Після підтвердження вибору функції відкриється діалогове вікно, в якому потрібно задати значення: початкове значення — вказуємо на комірку, у яку буде підставлятися ціна потрібного товару; таблиця — вказуємо діапазон таблиці «Прайс-лист» повністю; номер стовпця вказуємо номер колонки в таблиці «Прайс-лист»; інтервальний перегляд — вказуємо «БРЕХНЯ» (це трошки незрозуміло для звичайного користувача, оскільки «БРЕХНЯ» показує точне значення, а «ІСТИНА» — наближений). Підтверджуємо введені значення, в результаті отримаємо розрахунок. Таким чином, на даному прикладі ми розглянули деякі моменти користування Vlookup в Excel.

Покращуємо введення

Припустимо, що у нас є не дві таблиці, а багато таблиць. В цій ситуації, для того щоб знайти ту таблицю, яку необхідно буде виділити, потрібно затратити певну кількість часу. Щоб полегшити роботу, можна при введенні певних таблиць їх відразу виділяти, а з допомогою контекстного меню давати ім'я діапазону, яке потім використовувати у формулі замість вказівки діапазону таблиці. Таким чином, ми розглянули основні умови, як користуватися Vlookup в Excel.

Помилка #Н/Д (#N/A) ВПР (Vlookup)

Якщо ця функція не знаходить потрібного значення у комірці, в яку повинно було воно записатися, вводиться #Н/Д (або #N/A, якщо англійська версія). Щоб запобігти появі цієї написи, тобто залишити комірку порожньою у разі незнаходження необхідного значення, Excel до версії 2007 року вводимо =ЯКЩО(ЄНР(ВВР(;Лист1!;номер стовпця;значення «Істина» = 1 або «Ні» = 0));"";ВВР(копіюємо те ж, що в попередній дужці))) З 2007 версії цю формулу можна спростити =IFERROR(ВВР(вказуємо те ж саме, що і в попередній формулі);"")

ВВР з кількома умовами

Якщо необхідно здійснювати пошук по декільком вимогам, необхідно створити умови для здійснення пошукової операції, додати колонку, в якій в подальшому будуть об'єднаються колонки з даними, за ним буде проводитися пошук. Це дозволить нам отримати одну необхідну для нас колонку, в якій ми зможемо використовувати формулу так само, як це було описано вище. Наприклад, у нас є постачальники, які здійснюють постачання різних матеріалів. Постачальники відображаються в першій колонці, а товари — у другій. Якщо здійснювати пошук тільки по постачальникам, ми можемо потрапити не на той товар; навіть якщо переставити місцями колонки, користувач потрапить на той товар, але іншого постачальника. Тому здійснюють об'єднання цих двох колонок за допомогою знака &: =B2&C2. В результаті у колонку А буде поміщена об'єднана колонка (якщо цю формулу вводили у A2 а потім простягали), за якою буде здійснюватися пошук аналогічно попередньому.

Як працює Vlookup в Excel при вставці значень без прив'язування до таблиці?

Коли задаються всі формули в книзі, при відкритті її в наступний раз йде за замовчуванням перерахунок цих формул. Не винятком є і використання функції ВПР. Припустимо, що у нас дані у вихідній таблиці зміняться, тоді автоматично зміняться дані в таблиці, в якій застосовувалася функція VLOOKUP, а нам це не потрібно за будь-яких причин. У цьому випадку після застосування функції потрібно скопіювати значення і вставити їх за допомогою спеціальної вставки, вибравши «Вставити» - «Значення» меню «Спеціальна вставка». В результаті таблиця вставится тільки зі значеннями без формул.

Пишемо свій Vlookup

Використовуючи VBA, Vlookup в Excel можна створити самому, точніше, зробити макрос, який буде чим-то схожий на ВВР, але буде призначений для виконання конкретних завдань певного користувача. Так, наприклад, при порівнянні необхідно виконувати подвійну перевірку, оскільки деякі порівняння можуть виявитися більш вірними порівняно з іншими. Якщо ВВР шукає тільки по лівій колонці, то можна створити макрос, за допомогою якого буде здійснюватися пошук по уточнюючої колонці при вдалому завершенні пошуку за базовою. Допускається використовувати VBA і для заміни функції ВПР. Наприклад, є таблиця, в якій перераховано ряд людей і їх зарплата, яка складалася поетапно. Ми хочемо витягти зарплату одного з працівників. Для цього пишемо макрос: Sub VLookupFunctionDemo() Dim d1 As Double d1 = Application.WorksheetFunction.Vlookup("имя_работника_из_первой_колонки", Range("диапазон_таблицы"), номер_колонки) MsgBox Format(d1 "Currency"), , "VBA VLookup Function" End Sub

На закінчення

В даній статті було розглянуто питання: «Як користуватися Vlookup в Excel?» Використання цієї функції може сильно полегшити життя тим користувачам, які шукають дані з різних таблиць, займаються порівнянням різних відомостей. Застосування ВВР корисно для тих користувачів, які додають інформацію з однієї таблиці в іншу з пошуком по заданому критерію. У деяких випадках функції може виявитися недостатньо, тоді користувачі, які розбираються у мові VBA, можуть створювати макроси, які розширюють функціонал Vlookup.