У наборі функцій Excel, в категорії «Посилання та масиви» є функція VLOOKUP. Вона дозволяє шукати дані в одній таблиці, і знайшовши їх, повернути значення сусіднього правого стовпця (номер вказується користувачем) в іншу таблицю. Шукані дані повинні бути в обох таблицях – у таблиці, з якої беремо дані та в таблиці, де їх отримуємо.
Vlookup підтримує приблизне та точне зіставлення, а також знаки підстановки (* та ?). Розглянемо прийоми її застосування у формулах Excel.
VLOOKUP Excel: покрокова інструкція
Розглянемо простий приклад, як працює функція VLOOKUP Excel.
Задача. Автоматично підставити ціни з прайс-листа, в таблицю із замовленнями на купівлю деяких із цих товарів. Вручну шукати товар та вписувати в замовлення його ціну займе багато часу, тому з цим завданням добре впорається функція VLOOKUP.
Для виклику функції за допомогою Майстра, потрібно встановити курсор у клітинку, де буде прописано формулу та натиснути кнопку f(x) на самому початку рядка формул. З’явиться діалогове вікно Майстра, де потрібно знайти та вибрати функцію VLOOKUP. Також можна знайти потрібну функцію в категорії «Підстановка та посилання».
Після виклику функції з’явиться вікно введення її аргументів. Заповнюємо їх по черзі:
- Значення_підстановки (Lookup Value) – це назва товару, яку функція має знайти у крайньому лівому стовпці таблиці з прайс-листом. У нашому випадку – слово «Абрикос» із клітинки D2.
- Масив_таблиці (Table Array) – таблиця, із якої беруться шукані значення. У нашому випадку це діапазон клітинок таблиці з прайс-листом – A2:B9. За допомогою кнопки F4 цей діапазон потрібно закріпити (встановити знак $), щоб він не зміщувався при копіюванні формули вниз.
- Номер_стовпця (Column index number) – порядковий номер (не літера) стовпця в прайс-листі з якого будемо брати значення ціни. Перший стовпець прайс-листа з назвами має номер 1, отже, нам потрібна ціна зі стовпця з номером 2.
- Точність_пошуку (Range Lookup) – у це поле можна вводити лише два значення: точний (0) або приблизний (1) збіг критерію.
Останній параметр формули, йдеться мова про 0 (нуль), потрібно ставити обов’язково. Інакше формула може неправильно працювати.
Після заповнення всіх полів необхідно натиснути кнопку OK. Якщо все правильно і значення критерію є в обох таблицях, то на місці щойно введеної формули з’явиться певне значення. Залишається лише протягнути (або просто скопіювати) формулу вниз до останнього рядка таблиці. Тепер легко розрахувати вартість простим множенням обсягу на ціну.
Для простоти подальшого використання функції можна створити іменований діапазон і посилатися на нього. Для цього виділіть усі клітинки таблиці прайс-листа крім заголовків (A2:B9), виберіть у меню «Вставлення – Формули – Визначити ім’я» або Ctrl+F3. Введіть будь-яке ім’я (без пробілів), наприклад, «Прайс». Тепер можна буде використовувати це ім’я для посилання на прайс-лист.
Особливості використання формули VLOOKUP в Excel
Функція VLOOKUP в Excel має особливості, про які слід знати.
Першу особливість можна вважати загальноприйнятою для всіх функцій Excel – це використання відносної та абсолютної адресації посилань. Якщо казати конкретно за VLOOKUP, то в неї критерій (перше поле) повинен мати відносне посилання (без знаків $), оскільки в кожної клітинки свій власний критерій. А ось поле «Масив_таблиці» повинно мати абсолютне посилання, інакше при копіюванні формули діапазон «поїде» вниз і багато значень буде втрачено, тому що їх буде ніде шукати.
Якщо в одній з клітинок наприкінці випадково поставити пробіл, то з’явиться помилка #Н/Д, тому дуже важливо дотримуватися точного збігу даних у таблицях.
Номер стовпця, що вказується в третьому полі «Номер_стовпця» при використанні Майстра функцій, повинен рахуватись починаючи з самого критерію.
Функція VLOOKUP з діапазону з шуканими даними видає перше зверху значення. Це означає, що, якщо у другій таблиці, звідки ми намагаємося «підтягнути» деякі дані, є декілька клітинок з однаковим критерієм, то в рамках виділеного діапазону VLOOKUP захопить перше зверху значення. Про це слід пам’ятати.
Наприклад, якщо ми хочемо до ціни товару підтягнути кількість з іншої таблиці, а там цей товар зустрічається кілька разів (у кількох рядках), то до ціни підтягнеться перша зверху кількість.
Використання приблизного пошуку VLOOKUP
Застосування неточного перегляду (останній аргумент функції VLOOKUP) може бути корисним при вибірці значень з певних числових інтервалів. Наприклад, ми маємо встановлені норми знижок залежно від кількості товарів. При покупці товарів від 0 до 5 – знижка 0%, від 5 до 10 – знижка 2% тощо. (Див. скриншот нижче).
Задача – швидко розрахувати, скільки відсотків необхідно нарахувати на завершену покупку. Для цього можна використовувати функцію VLOOKUP, в якій для аргументу «Точність_пошуку» встановити значення 1, тобто приблизне. Формулу VLOOKUP також можна прописати вручну, набираючи аргументи по черзі, і розділяючи крапкою з комою. У результаті отримаємо такий результат.
Як бачимо, якщо у нас кількість товару 11 одиниць, то ми переглядаємо стовпець D доти, доки не зустрінемо число, більше 11. Це 20 і знаходиться воно в 4-му рядку. Зупиняємось тут. Отже, наша знижка розташована в 3-му рядку і дорівнює 3%. Працюючи з інтервалами виду «від – до» така методика цілком може стати в нагоді.
Функція VLOOKUP із кількома умовами
Ще один простий приклад – як використовувати при виборі потрібного значення кілька умов. Припустимо, у нас є список імен та прізвищ. Нам потрібно знайти потрібну людину за ім’ям та прізвищем і вивести суму її доходу. У клітинці F2 використовуємо таку формулу:
=VLOOKUP(D2&" "&E2;$A$2:$B$17;2;0)
Розберемо покроково, як в цьому випадку працює VLOOKUP. На початку формується умова. Для цього за допомогою оператора & «склеюємо» разом ім’я та прізвище, а між ними вставляємо пробіл. Не забуваємо при цьому пробіл укласти в лапки, інакше Excel не сприйме його як текст.
Потім у таблиці з доходами шукаємо клітинку з ім’ям та прізвищем, розділеними пробілом. Далі все відбувається за вже відпрацьованою схемою.
Функція VLOOKUP та розкривний список
Розглянемо приклад, в якому необхідно налаштувати функцію таким чином, щоб при виборі найменувань товару з розкривного списку, з’являлася його ціна. Для початку створимо розкривний список з таблиці.
Щоб створити розкривний список Excel виконайте кроки:
- Встановити курсор в клітинку, де буде список, наприклад, Е8.
- Переходимо на вкладку «Дані» – «Перевірка даних».
- Вибираємо тип даних – «Список», а в полі «Джерело» вказуємо діапазон із найменуваннями матеріалів. У прикладі $A$2:$A$15.
- Натискаємо кнопку OK.
В результаті з’явиться список із найменуваннями матеріалів.
Тепер потрібно зробити так, щоб при виборі певного матеріалу у графі ціна з’являлася відповідна цифра. Ставимо курсор в клітинку Е3 (де має з’являтися ціна) викликаємо функцію та заповнюємо аргументи. Після того, як аргументи заповнені, натискаємо OK і перевіряємо результат.
Тепер для конкретного товару з’являється ціна. При виборі іншого товару з розкривного списку, ціна буде змінюватися автоматично.
Якщо вам вдалося освоїти на практиці функцію VLOOKUP Excel, то поділіться своїм досвідом у коментарях, які будуть важливими як для нас, так і для інших відвідувачів ресурсу.
