Частина 1. Трохи теорії

ВПР (VLOOKUP)

На перший погляд функція виглядає складною, але це не так – потрібно запам'ятати лише три прості правила:

1. ВПР шукає значення зверху вниз;
2. ВПР шукає в першому (крайньому лівому) стовпці діапазону;
3. Якщо у Вас дані не відсортовані за алфавітом (зростанням) і якщо ви не впевнені в результатах пошуку – не використовуйте приблизний збіг.

Схематично роботу функції можна зобразити так:

Розглянемо аргументи функції

Шукане значення - значення, яке нам потрібно знайти в першому стовпці діапазону.

Таблиця – діапазон, у якому виконується пошук даних. Це може бути таблиця з текстом, числами, логічними значеннями, посиланням або ім'ям діапазону. Не забувайте фіксувати діапазон абсолютними посиланнями (за необхідності), інакше він буде зсуватися разом із формулою.

Номер_стовпця – номер стовпця, з якого нам потрібно підтягнути значення (потрібне значення — завжди перший стовпець).

Інтервальний_перегляд - аргумент, який визначає, чи потрібно нам шукати точний збіг (ЛОЖЬ) або приблизне (ИСТИНА). Як говорилося вище – при приблизному збігу дані повинні йти за алфавітом (зростанням).

Частина 2. Поради із практики

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

Автоматична зміна номера стовпця

Якщо таблиця має багато стовпців, а витягнути інформацію нам потрібно відразу по кількох, то міняти номер стовпця вручну досить незручно (у моєму особистому досвіді був випадок, коли потрібно було витягнути дані з 248 стовпця, а за ним із 63-го). У таких випадках ВВР необхідно доповнити функцією ПОШУКПОЗ.

Формула може мати приблизно таку конструкцію:

=ВПР(G3;$B$3:$D$38;ПОИСКПОЗ($H$2;$B$2:$D$2;0);ЛОЖЬ)

Таким чином, при зміні назви стовпця підтягуватимуться дані за новою умовою.

Зворотний режим

Дуже часто виникають ситуації, коли структура файлу така, що значення не в першому стовпці. Якщо ж у цій ситуації Ви хочете скористатися функцією ВПР, то її необхідно трохи доопрацювати, скориставшись функцією ВЫБОР (використовує номер_індексу, щоб вибрати та повернути значення зі списку аргументів-значень).

У цьому випадку формула матиме приблизно такий вигляд:

=ВПР(E4;ВЫБОР({1;2};$C$4:$C$39;$B$4:$B$39);2;ЛОЖЬ)

Таким чином, ми «обдурили» функцію ВПР, вказавши, що першим стовпцем у нашому масиві є стовпець «C», другим – «B».

Тільки експериментуючи з формулами Excel, Ви можете відкрити все нові і нові грані цього потужного інструменту! Не бійтеся робити нові кроки – і у Вас все вийде!

Ще більше — на нашому курсі

Автор: Михайло Беленчук, тренер DATA bi

Дізнайтеся більше про повну програму "EXCEL: бізнес-аналіз і прогнозування"