Функція ВПР і її використання

1. Синтаксис функції ВПР
2.Швидке порівняння двох таблиць за допомогою ВПР
3. Функція ВПР з декількома умовами
4. ВПР і випадаючий список

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

Синтаксис функції ВПР: 

=ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])

• искомое_значение – значення, яке має бути знайденим в першому стовпці массиву (значення, посилання або рядок тексту)
• таблица – таблиця з текстом, числами або логічними значеннями, в якій здійснюється пошук даних.
• номер_столбца – номер стовпця в таблиці, із якого потрібно витягнути значення.
• [интервальный_просмотр] – логічне значення, яке оприділяє точно (ЛОЖЬ) чи приблизно (ИСТИНА або відсутність значення) повинен виконуватись пошук в першому стовпці таблиці.

Варто відмітити, що ИСТИНА, або 1, або відсутність значення дуже корисною є, якщо, наприклад, у другій таблиці у значення із додатковими пробілами.

Наприклад в нас є прайс. Наше завдання – заповнити ціну у стовпці F.

Для розв’язання такої задачі ми використаємо функцію ВПР, яка легко і ефективно визначить ціну.

Розв’язок буде: =впр(D3;A:B;2;ЛОЖЬ)

В нашому прикладі – нам потрібно знайти ціну для абрикоса (в першому рядку) з таблиці A:B. Ціна в таблиці, тобто те значення, яке нам потрібно перетягнути, знаходиться в другому стовпчику. І так, як в нас назва товарів у двох табличках співпадають, то ми використовуємо точний пошук (ЛОЖЬ або 0).
Прописавши ми формулу для першого значення копіюємо його на цілий стовпець.

Швидке порівняння двох таблиць за допомогою ВПР

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

В старому прайсі добавляємо стовпець «Нова ціна». І прописуємо формулу за допомогою ВПР, перетягаючи нову ціну  відповідному значенні:

=ВПР(A3;$E$3:$F$15;2;0)

Копіюємо формулу на цілий стовпець і можемо приступити до порівнювання зміни цін. Тут можна добавити стовпець дельту ціну. Але це вже як кому зручно, адже головне завдання ми уже зробили за допомогою ВПР.

Функція ВПР з декількома умовами

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

Приклад: у нас є таблиця-прайс 1, де ціна присвоюється артикулу товару і є таблиця розшифрування артикулів 2. Нам потрібно заповнити ціну для конкретної назви товару у таблиці 3.

Тоді формула буде мати наступний вигляд: =ВПР(ВПР(G3;$D$3:$E$15;2;0);$A$3:$B$15;2;0)

Друга функція ВПР, яка використовується всередині, вичислює значення для першої функції ВПР.

ВПР і випадаючий список

Наприклад, перед нами поставили задачу з випадаючим списком товарів, для яких потрібно вичислити ціну.

Для початку створимо випадаючий список із таблиці, для якої ми в попередньому завданні вичисляли ціну. Для цього в клітинку, в якій буде назва товару ставимо курсор, заходимо у вкладку «Данные» - Меню «Проверка данных» - «Список». У комірці «Источник» віділяємо список товарів із тмблиці =$G$3:$G$15

В нас з’являється випадаючий список із назвами товарів:

Розраховуємо ціну: =ВПР(J4;G3:H15;2;0)

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

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