VLOOKUP, або ВПР – це одна з найпопулярніших функцій в Excel. Зазвичай її застосовують для пошуку точних відповідностей, наприклад, ідентифікаторів товарів чи клієнтів. У цій статті ми розглянемо, як використовувати VLOOKUP для пошуку в діапазоні значень.
Застосування VLOOKUP для визначення оцінок за результатами іспиту
Розглянемо ситуацію, коли є список результатів іспитів, і потрібно присвоїти кожному результату відповідну літерну оцінку. В нашій таблиці стовпець A містить фактичні бали, а стовпець B призначений для відображення обчислених літерних оцінок. Праворуч ми створили допоміжну таблицю (стовпці D і E), де вказано мінімальну кількість балів, необхідну для отримання кожної літерної оцінки.
Використовуючи VLOOKUP, ми можемо застосувати значення діапазону в стовпці D для присвоєння літерних оцінок зі стовпця E всім фактичним результатам іспиту.
Синтаксис формули VLOOKUP
Перш ніж перейти до практичного застосування формули, давайте згадаємо її синтаксис:
=VLOOKUP(значення_пошуку; масив_таблиці; номер_стовпця; діапазонний_пошук)
У цій формулі:
значення_пошуку: це значення, яке ми шукаємо. В нашому випадку це бали у стовпці A, починаючи з комірки A2.
масив_таблиці: це таблиця пошуку. Для нас це таблиця, що містить бали та відповідні оцінки (діапазон D2:E7).
номер_стовпця: це порядковий номер стовпця, де містяться результати. У нашому прикладі це стовпець B, але оскільки функція VLOOKUP вимагає числа, це стовпець 2.
діапазонний_пошук: це логічне значення (істина або хибність). Чи виконуємо ми пошук діапазону? Для нас відповідь “так” (або TRUE у термінах VLOOKUP).
Отже, формула для нашого прикладу виглядає так:
=VLOOKUP(A2;$D$2:$E$7;2;TRUE)
Масив таблиці зафіксовано для того, щоб він не змінювався під час копіювання формули у стовпчик B.
Важливий нюанс
При використанні VLOOKUP для пошуку в діапазонах, необхідно, щоб перший стовпець масиву таблиці (стовпець D у нашому прикладі) був відсортований у порядку зростання. Формула використовує цей порядок для правильного визначення діапазону.
На зображенні нижче показано, що відбудеться, якщо масив таблиці відсортувати за літерними оцінками, а не за балами.
Важливо розуміти, що порядок має значення лише для пошуку в діапазоні. Якщо в кінці функції VLOOKUP вказати значення FALSE, порядок не відіграє такої ролі.
Приклад 2: Надання знижки залежно від суми витрат клієнта
У цьому прикладі маємо дані про продажі. Ми хочемо надати знижку на суму продажу, при цьому розмір знижки залежить від витраченої суми.
У довідковій таблиці (стовпці D і E) наведено розміри знижок для різних діапазонів витрат.
Наведена нижче формула VLOOKUP допоможе отримати правильну знижку з таблиці.
=VLOOKUP(A2;$D$2:$E$7;2;TRUE)
Цей приклад цікавий тим, що ми можемо використовувати цю формулу безпосередньо для обчислення кінцевої ціни зі знижкою.
Багато користувачів Excel створюють складні формули для такої умовної логіки, але VLOOKUP надає простий спосіб досягти того ж результату.
Нижче показано, як до формули додано VLOOKUP для віднімання знижки від суми продажу у стовпці A.
=A2-A2*VLOOKUP(A2;$D$2:$E$7;2;TRUE)
VLOOKUP корисна не лише для пошуку конкретних значень, таких як ідентифікатори співробітників чи продуктів. Її можливості набагато ширші, і пошук у діапазоні значень є яскравим тому прикладом. Також її можна використовувати як альтернативу складним формулам.