Функція ВПР (VLOOKUP) - як працює і чому, приклади, типові помилки та чим замінити

Загальна інформація про ВПР (VLOOKUP)
Функція ВПР – це одна з найбільш популярних функцій посилань та масивів. В англомовному Excel, а також Google Sheets, LibreOffice, OpenOffice, ця функція називається VLOOKUP.

Рівень складності за шкалою BRP ADVICE - 3 із 7 .
ВПР (VLOOKUP) дозволяє знайти в таблиці з даними значення потрібного рядка і стовпця. При цьому потрібну позицію ВПР (VLOOKUP) знайде сам, а ось стовпець вам доведеться вказати самостійно.

Щоб розібратися з ВПР (VLOOKUP), спочатку треба розібратися з тим, як працюють усі функції посилань та масивів.

Як працюють функції посилань та масивів
В Excel, Google Sheets, LibreOffice, OpenOffice та інших табличних документах ви можете посилатися на комірку, щоб отримати її значення або використовувати в розрахунках. Зазвичай у функціях ви вказуєте посилання на комірки виду A1, B17, G34, Z52. Деякі звикли працювати з посиланнями на види R1C1, R17C2, R34C7, R52C26. При цьому ви вказуєте номер рядка та літеру/номер стовпця з початку аркуша. Вказавши комірку, ви даєте програмі точну вказівку, що вам потрібно значення саме цієї комірки, що знаходиться на перетині потрібного рядка та стовпця. Тобто все це виглядає приблизно так:

Функції посилань та масивів працюють трохи інакше. У функціях посилань і масивів ви задаєте нову таблицю на аркуші, яка може бути де завгодно. Назвемо цю нову таблицю – внутрішня таблиця. Вся нумерація рядків та стовпців у цій внутрішній таблиці починається заново. Від 1 до останнього рядка, від 1 і до останнього стовпця.При цьому зовсім не важливо, де починається внутрішня таблиця: на початку листа, в його середині або ближче до кінця. Завжди перший осередок цієї таблиці утворює перетин першого рядка і першого стовпця. Ось як це можна схематично зобразити:

На жаль, жоден табличний редактор не намалює вам такої підказки. Все це і вам, і програмі доведеться пам'ятати.
Як зазначити, де знаходиться внутрішня таблиця? Для цього вам знадобиться вказати її адресу за допомогою стандартних посилань типу A1 або R1C1. На зображенні вище адреса внутрішньої таблиці - це діапазон J17: O25.
Що ж відбувається у цій внутрішній таблиці? Кожен осередок отримує нову адресу, яка складається з номера рядка та номера стовпця. Саме так: спочатку номер рядка, потім номер стовпця.
Що роблять функції посилань і масивів? Їхня кінцева мета - отримати значення за його внутрішньою адресою. При цьому функції посилань і масивів можуть знайти потрібний рядок і потрібний стовпець самі, так і використовувати введені користувачем значення. Для різних завдань використовують різні функції. Але в кінцевому рахунку виходить приблизно так, Excel, Google Sheets, LibreOffice, OpenOffice визначають, що вам потрібний п'ятий рядок і третій стовпець і видають значення з такого осередку:
Як працює ВПР (VLOOKUP)
Найпростіший спосіб розібратися з ВПР (VLOOKUP) – це розглянути його на прикладах. Розглянемо один приклад із точним пошуком, другий приклад - із приблизним пошуком.
Приклад 1 - точний пошук Посилання на прикладний файл наведено в кінці опису цього прикладу. Допустимо, є таблиця по співробітникам вашої організації та їх окладам. У цій таблиці вказано кожен співробітник (його ПІБ) та його оклад. Перший стовпець цієї таблиці – ПІБ співробітника, другий – оклад.Тобто ваша таблиця виглядає так:
ВПР (VLOOKUP) дозволить вам знайти оклад, вказуючи ПІБ співробітника. Звичайно, коли у вас у таблиці 5 рядків і завдання разове, очима ви знайдете потрібне значення дуже швидко. А якщо у вас 200 співробітників? Або 5000? ВПР (VLOOKUP) допоможе спростити вам життя.
Що потрібно зробити, щоб знайти оклад, наприклад, Іванова С.А.?
Треба написати формулу =ВПР("Іванов С.А.";C3:D9;2;БРЕХНЯ) або для англомовного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP("Іванов С.А.";C3:D9;2;FALSE) . До речі, в деяких версіях Excel замість ";" має використовуватися ",".
Після цього програма поверне вам 21 000 відповідей.
Що означають усі аргументи ВПР (VLOOKUP)? 1. Шукане значення. У прикладі це " Іванів С.А. " - це значення, яке ВПР шукатиме у першому стовпці внутрішньої таблиці. Зверніть увагу, це перший стовпець саме внутрішньої таблиці, а чи не перший стовпець листа. І це завжди перший стовпець. ВПР неспроможна шукати у другому, третьому чи будь-якому іншому стовпці таблиці - лише першому стовпці внутрішньої таблиці.
До речі, "Іванов С.А." у нас написано в лапках, тому що будь-який текст усередині формули має бути написаний у лапках. Винятком є ​​лише назви функцій та іменованих діапазонів. В інших випадках завжди ставте текст у лапки.
2. Таблиця. У прикладі це C3:D9. Це координати або адреса тієї самої внутрішньої таблиці. Саме в першому стовпці цієї таблиці Excel намагатиметься знайти потрібне значення (див. пункт вище).

3. Номер стовпця. У прикладі це 2. Це номер стовпця, у якому міститься інформація, яку ви шукаєте (у разі - оклад). Порахувати його потрібно вручну.Починайте підрахунок з першого стовпця внутрішньої таблиці: перший – це ПІБ, другий – оклад. Значить, ставимо цифру 2.
4. Інтервальний перегляд. У нашому прикладі це брехня (FALSE). По суті, це відповідь на питання "Ми ж шукаємо приблизно, вірно?". ІСТИНА (TRUE) означає "так, приблизно", брехня (FALSE) - "ні, ми шукаємо саме це". Тобто ІСТИНА (TRUE) означає, що нам підійде і Іванов С.А., і Іванов С.І., і Іванова О.П., і може бути ще хтось (кого з них вибере Excel, дивіться в Прикладі № 2). Зараз нам потрібен саме Іванов С.А., тому ми поставили БРЕХНУ (FALSE). До речі, деякі використовують значення 1 і 0 замість ІСТИНА (TRUE) і БРЕХНЯ (FALSE) відповідно. Працюватиме, але ваших колег ви можете заплутати. Тож ми радимо завжди писати слово, а не цифру.
Що саме робить ВПР (VLOOKUP) у цьому прикладі? ВПР (VLOOKUP) переглядає кожен осередок першого стовпця зверху вниз. Він дивиться в першому рядку "Газоєв І.В." = "Іванов С.А."? Бачить, що ні. Тоді дивиться другу: "Ромашкіна Б.О."=Іванов С.А."? І так далі, поки не доходить до потрібного рядка. Коли ВПР (VLOOKUP) бачить, що "Іванов С.А."="Іванов С.А. А.", він зупиняється і запам'ятовує номер рядка, в якому це сталося. У нашому прикладі це 4 рядок. Четвертий, тому що підрахунок починається не з початку аркуша, а з початку внутрішньої таблиці. І, нарешті, ВПР (VLOOKUP) повертає значення внутрішньої таблиці, що знаходиться на перетині 4 рядки (те, що він знайшов) та 2 стовпці (те, що ми вказали йому аргументом). А це значення 21 000. Все, завдання вирішено.
А ось цей текст - це посилання на завантаження прикладу в Excel 2010-2013. Бажаєте вирішити приклад онлайн? Залишіть заявку, ми вже працюємо над цим.Як завжди, наші вправи працюють в Excel 2007-2013, а розширений функціонал можна використовувати в Excel 2010 та 2013. З його допомогою можна почати будь-яку вправу з початку лише однією кнопкою "Почати заново" на вкладці BRP ADVICE, що з'являється в Excel при відкритті наших вправ. Тільки не забудьте увімкнути макроси.
Приклад 2 - приблизний пошук Посилання на прикладний файл наведено в кінці опису цього прикладу.
Допустимо, у вас в компанії менеджер з продажу отримує премію в залежності від обсягу продажу. Якщо продажів за місяць більше 100, то премія складає 4% від продажів. Якщо продаж більше 200, то премія - 5%. Більше 300 – 6%. Більше 400 – 7%. І за будь-яких продажів більше 500 - 8%. При продажах менше 100 менеджер премію не отримує. За допомогою ВПР (VLOOKUP) можна швидко дізнатися, яку премію отримає менеджер за його фактичних продажів.
Для цього спочатку нам потрібно буде скласти таблицю з рівнями плану та ставками премії. Така таблиця виглядатиме ось так:
Тепер ми можемо знайти премію Іванова С.А. залежно з його фактичних результатів. Основною відмінністю від попереднього прикладу є те, що фактичні продажі Іванова можуть бути не лише рівно 100, 200, 300, 400 або 500. Вони можуть бути між зазначеними нами значеннями. Наприклад, фактичні продажі Іванова становитимуть 350.
Для того, щоб знайти ставку премії при продажах 350, можна використовувати таку формулу: = ВПР (350; C3: D8; 2; ІСТИНА) або для англомовного Excel, Google Sheets, LibreOffice, OpenOffice:
= VLOOKUP (350; C3: D9; 2; TRUE) . Не забувайте, що в деяких версіях Excel замість ";" має використовуватися ",".
Після цього програма поверне вам 6% відповідь.Все, що вам залишається зробити, це перемножити продажі 350 і ставку премії 6%. Це буде премія Іванова С.А. при продажах рівних 350.

Що означають усі аргументи ВПР (VLOOKUP)? 1. Шукане значення. У прикладі це 350 - це значення, яке ВПР шукатиме у першому стовпці внутрішньої таблиці. Зверніть увагу, це перший стовпець саме внутрішньої таблиці, а чи не перший стовпець листа. І це завжди перший стовпець. ВПР неспроможна шукати у другому, третьому чи будь-якому іншому стовпці таблиці - лише першому стовпці внутрішньої таблиці.

До речі, цього разу 350 у нас написано без лапок, бо 350 – це число, а в лапки ми беремо лише текст.
2. Таблиця. У прикладі це C3:D8. Це координати або адреса тієї самої внутрішньої таблиці. Саме в першому стовпці цієї таблиці Excel намагатиметься знайти потрібне значення (див. пункт вище).

3. Номер стовпця. У прикладі це 2. Це номер стовпця, у якому міститься інформація, яку ви шукаєте (у разі - ставка премії). Порахувати його потрібно вручну. Починайте підрахунок із першого стовпця внутрішньої таблиці: перший - це "При продажах більше", другий - "ставка премії". Значить, ставимо цифру 2.
4. Інтервальний перегляд. На цей раз нам потрібна ІСТИНА (TRUE). По суті, це відповідь на питання "Ми ж шукаємо приблизно, вірно?". ІСТИНА (TRUE) означає "так, приблизно", брехня (FALSE) - "ні, ми шукаємо саме це". Ми використовуємо ІСТИНА (TRUE), тому що нам потрібно знайти не тільки значення 100, 200 та інші прямо зазначені в таблиці, але і все, що знаходиться між ними. Тобто ІСТИНА (TRUE) означає, що нам підійде і 300, і 350, і 380 і так далі, і може бути ще щось (яке з них вибере Excel, дивіться нижче).
До речі, деякі використовують значення 1 і 0 замість ІСТИНА (TRUE) і БРЕХНЯ (FALSE) відповідно. Працюватиме, але ваших колег ви можете заплутати. Тож ми радимо завжди писати слово, а не цифру.
Що саме робить ВПР (VLOOKUP) у цьому прикладі? Як і минулого разу ВПР (VLOOKUP) послідовно переглядає всі осередки першого стовпця нашої таблиці. Але цього разу він не шукає точної відповідності, а виконує такі перевірки: 100, вказане у таблиці, менше 350? Так, відповідає ВПР (VLOOKUP), тоді запам'ятовуємо рядок 1 і дивимося наступне значення. 200 менше 350? Так, тоді забуваємо рядок 1, запам'ятовуємо 2 і дивимося таке. 300 менше 350? Так, тоді забуваємо рядок 2, запам'ятовуємо 3 і дивимося таке. 400 менше 350? Ні! Тоді повертаємось до рядка 3. І, нарешті, ВПР (VLOOKUP) повертає значення, вказане на перетині 3 рядки (те, що він знайшов) та 2 стовпці (те, що ми вказали у функції).
Приблизно так, можна описати логіку обчислень ВПР (VLOOKUP) під час роботи з приблизним пошуком. Зверніть увагу, що ВПР (VLOOKUP) не переглядає всю таблицю, а дивиться до тих пір, поки значення не виявляються більше шуканого. Іншими словами, ВПР (VLOOKUP) шукає найближче найменше. Але для того, щоб все працювало правильно, вам потрібно відсортувати таблицю зростання значень у першому стовпці.

А ось цей текст - це посилання на завантаження прикладу в Excel 2010-2013. Бажаєте вирішити приклад онлайн? Залишіть заявку, ми вже працюємо над цим. Як завжди, наші вправи працюють у Excel 2007-2013, а розширений функціонал можна використовувати у Excel 2010 та 2013.З його допомогою можна розпочати будь-яку вправу з початку лише однією кнопкою "Почати заново" на вкладці BRP ADVICE, що з'являється в Excel при відкритті наших вправ. Тільки не забудьте увімкнути макроси.
Типові помилки Які помилки ми найчастіше зустрічаємо під час роботи з ВПР (VLOOKUP)? 1. Це помилки, пов'язані з неправильною роботою із 4 аргументом, з інтервальним переглядом. Часто плутаються в різниці між ІСТИНА (TRUE) та БРЕХНЯ (FALSE). Запам'ятайте таке запитання: "Ми шукаємо приблизно таке саме значення, вірно?", нехай це буде вам підказкою. Часто ще випадково не вказують на четвертий аргумент взагалі. І це може спричинити зовсім різні наслідки. Перший випадок, коли ви працюєте з ВПР (VLOOKUP), ніби в ньому всього 3 аргументи. Тобто у формулі ви ставите лише дві точки з комою. Наприклад, формула має такий вигляд: =ВПР("Іванов С.А.";C3:D9;2)
або для англомовного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP("Іванов С.А.";C3:D9;2) .

У цьому випадку Excel вирішує, що треба використовувати значення 4 аргументу за замовчуванням і підставляє як ІСТИНА (TRUE). Тоді ви можете знайти не Іванова С.А., а когось чиє прізвище буде приблизно схоже на його (дивись як працює ВПР (VLOOKUP) із приблизним пошуком). Другий випадок, коли ви ставите третю крапку з комою, але не вказуєте сам аргумент ІСТИНА (TRUE) або БРЕХНЯ (FALSE). Наприклад, формула виглядає так: = ВПР (350; C3: D8; 2;)
або для англомовного Excel, Google Sheets, LibreOffice, OpenOffice:
= VLOOKUP (350; C3: D9; 2;) . І тут ВПР (VLOOKUP) вирішить, що 4 аргумент дорівнює нулю. А нуль ВПР (VLOOKUP) перетворить на брехню (FALSE) і шукатиме саме 350 у вашій таблиці, а не найближче менше.

Наша порада, щоб не допускати таких помилок, завжди вказуйте останній, четвертий аргумент функції ВПР (VLOOKUP). І пишіть його словом, не замінюйте його на цифри, то вашу формулу буде легше прочитати і зрозуміти, що ж вона робить.
2. Інша часта помилка - це спроба застосовувати ВПР (VLOOKUP), щоб знайти другий чи пізніше збіг у внутрішній таблиці. Наприклад, є таблиця для ведення складського обліку, в якій відображаються матеріали, що надходять, і ціни на них. Виглядатиме вона приблизно так: За допомогою ВПР (VLOOKUP) іноді намагаються повернути ціну в останньому надходженні. Наприклад, скільки коштували цвяхи в останній закупівлі. Але ВПР (VLOOKUP) поверне вам результат лише першого збігу. Він не покаже вам дані про другий, третій або пізніший рядок, тільки перший збіг. Тому використовувати ВПР (VLOOKUP) у такому завданні та у завданнях складського обліку потрібно дуже акуратно. Так, у поєднанні з ще кількома функціями та проміжними обчисленнями, ви зможете отримати потрібний результат, але ми називаємо такі підходи "танці з бубном".
3. Іноді неправильно вказують номер шпальти (третій аргумент ВПР (VLOOKUP)). Номер стовпця не може бути меншим за 1 і не може бути більшим, ніж стовпців у внутрішній таблиці. Якщо номер стовпця не вказано, то ВПР (VLOOKUP) повертає помилку #ПОСИЛКА! (#REF!). Коли ви бачите таку помилку, перерахуйте кількість стовпців у таблиці, яку ви вказали у функції, і переконайтеся, що це значення не менше ніж номер стовпця, вказаний третім аргументом функції ВПР (VLOOKUP).
Що відбувається, коли ВПР (VLOOKUP) не знаходить значення?
У випадках, коли Excel, Google Sheets, LibreOffice, OpenOffice не може знайти точний збіг при 4 аргументі брехня (FALSE), ВПР (VLOOKUP) повертає помилку #Н/Д (#N/A). Таку ж помилку ВПР (VLOOKUP) поверне, якщо ви використовуєте приблизний пошук і таблиця починається зі значення, яке більше шуканого.

Як прибрати помилку #Н/Д (#N/A)
По-перше, перевірте адресу внутрішньої таблиці. Справді шукане значення перебуває у першому стовпчику внутрішньої таблиці. По-друге, перевірте, чи правильно ви вказали тип інтервального перегляду: ІСТИНА (TRUE) або БРЕХНЯ (FALSE). По-третє, якщо ви все зробили правильно, але у внутрішній таблиці немає потрібного значення, доповніть формулу функцією ОСЛИПОМИЛКА (IFERROR). Наприклад, так: =ЯКЛИПОМИЛКА(ВПР("Іванова А.О.";C3:D9;2);"немає такого співробітника")
або для англомовного Excel, Google Sheets, LibreOffice, OpenOffice:
=IFERROR(VLOOKUP("Іванова А.О.";C3:D9;2);"немає такого співробітника") .
У цьому випадку Excel, Google Sheets, LibreOffice, OpenOffice замість #Н/Д (#N/A) будуть писати, що такого співробітника немає. Це допоможе зробити ваші розрахунки більш інформативними та надійними.
Чим доповнити та замінити ВПР (VLOOKUP)?
Доповнити ВПР (VLOOKUP) можна функцією ПОЛІПОМИЛКА (IFERROR) і функцією ПОШУКПОЗ (MATCH). В особливо складних випадках можливе використання у комбінації з функцією ЗМІЩ (OFFSET). Основні варіанти заміни функції ВПР (VLOOKUP): функція ПЕРЕГЛЯД (LOOKUP), комбінація функцій ІНДЕКС (INDEX) і ПОШУКПОЗ (MATCH), а також у деяких випадках - функція ГПР (HLOOKUP), функція СУМІСЛІ (SUMIF).При використанні функції ВПР (VLOOKUP) велику автоматизацію та надійність вашим файлам може додати перевірка даних і списки, що випадають, а також умовне форматування.
Швидкі посилання на файли-приклади: Приклад 1 – застосування ВПР з точним пошуком
Приклад 2 - застосування ВПР із приблизним пошуком

Залишились питання? Пишіть нам у форму зворотного зв'язку і записуйтесь на інтенсивність Excel або курс функцій Excel.

Сподобалася стаття? Дізнайтесь більше раніше за інших: заходьте на нашу сторінку у ВКонтакті та підписуйтесь на новини.

Бажаємо вам успішної роботи!
Ваш Віктор Рибцев та команда Навчального центру BRP ADVICE.

Функції ВПР та ГПР в Excel з прикладами їх використання

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

ГПР відповідно для горизонтального. Оскільки в таблицях рідко рядків більше, ніж стовпців, цю функцію викликають нечасто.

Синтаксис функцій ВПР та ГПР

Функції мають 4 аргументи:

1. ЩО шукаємо – шуканий параметр (цифри та/або текст) або посилання на комірку з потрібним значенням;
2. ДЕ шукаємо – масив даних, де здійснюватиметься пошук (для ВПР – пошук значення здійснюється у ПЕРШОМУ стовпці таблиці; для ГПР – у ПЕРШОМУ рядку);
3. НОМЕР стовпця/рядки – звідки саме повертається відповідне значення (1 – з першого стовпця або першого рядка, 2 – з другого тощо);
4. ІНТЕРВАЛЬНИЙ ПЕРЕГЛЯД – точне або приблизне значення має знайти функція (БРЕХНЯ/0 – точне; ІСТИНА/1/не вказано – приблизне).

! Якщо значення в діапазоні відсортовані у порядку, що зростає (або за алфавітом), ми вказуємо ІСТИНА/1. В іншому випадку - Брехня/0.

Як користуватись функцією ВПР в Excel: приклади

Для навчальних цілей візьмемо таблицю з даними:

1. Функція ВПР завжди шукає дані у крайньому лівому стовпці таблиці зі значеннями.
2. Регістр не враховується: малі й великі літери для Excel однакові.
3. Якщо шукане менше, ніж мінімальне значення масиві, програма видасть помилку #Н/Д.
4. Якщо встановити номер стовпця 0, функція покаже #ЗНАЧ. Якщо третій аргумент більший за кількість стовпців у таблиці – #ПОСИЛКА.
5. Щоб при копіюванні зберігався правильний масив, використовуємо абсолютні посилання (клавіша F4).

Як користуватись функцією ГПР в Excel: приклади

Для навчальних цілей візьмемо таку табличку:

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

Символи підстановки у функціях ВПР та ГПР

Трапляється, що користувач не пам'ятає точної назви. Задаючи потрібне значення, він може застосувати символи підстановки:

• "?" - замінює будь-який символ у текстовій чи цифровій інформації;
• * для заміни будь-якої послідовності символів.

1. Знайдемо текст, який починається чи закінчується певним набором символів. Припустимо, нам потрібно знайти назву компанії. Ми забули його, але пам'ятаємо, що починається з Kol. Із завданням впорається така формула: .
2. Нам потрібно знайти назву компанії, яке закінчується на "uda". Допоможе така формула: .
3. Знайдемо компанію, назва якої починається на "Ce" та закінчується на - "sef". Формула ВПР виглядатиме так: .

Коли проблеми з пам'яттю усунуті, можна працювати з даними, використовуючи ті самі функції.

Як порівняти аркуші за допомогою ВПР та ДПР

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

Як порівняти аркуші за допомогою ВПР у Excel?

Вирішимо проблему 1: порівняємо найменування товарів у січні та лютому. Так як у лютому їх більше, вводитимемо формулу на листі «Лютий».

Вирішимо проблему 2: порівняємо продажі за позиціями у січні та лютому. Використовуємо таку формулу:

Як порівняти аркуші за допомогою ГПР у Excel?

Для демонстрації дії функції ГПР візьмемо дві «горизонтальні» таблиці, що розташовані на різних аркушах.

Завдання – порівняти продажі за позиціями за січень та лютий.

Створюємо новий аркуш «Порівняння». Це не є обов'язковою умовою. Порівнювати дані та відображати різницю можна на будь-якому аркуші («Січень» або «Лютий»).

Проаналізуємо частини формули:

. Шукане значення – перший осередок у таблиці для порівняння. Аналізований діапазон – таблиця із продажами за лютий. Функція ДПР "бере" дані з 2 рядка в "точному" відтворенні.

. Все те саме. Окрім діапазону.Тут береться таблиця із продажами за січень.

Коли ми вводимо формулу, Excel підказує, який аргумент потрібно ввести.

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади

Функція ВПР в Excel

У табличному редакторі Microsoft Excel безліч різних формул та функцій. Вони дозволяють заощадити час і уникнути помилок – достатньо правильно написати формулу та підставити потрібні значення.

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

Необхідно до кожного товару з таблиці зліва додати ціну з прайсу праворуч.

Як створити функцію ВПР в Excel

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

Записуємо формулу в стовпчик ціни (С2). Це можна зробити двома способами:

1. Виділити комірку та вписати функцію.
2. Виділити комірку → натиснути на Fx (Shift + F3) → вибрати категорію «Посилання та масиви» → вибрати функцію ВПР → натиснути «ОК».

Після цього відкривається вікно, де можна заповнити осередки аргументів формули.

Синтаксис функції ВПР має такий вигляд:

=ВПР(потрібне значення;таблиця;номер стовпця;інтервальний перегляд)

У нашому випадку вийде така формула:

Аргументи функції ВПР

Зараз розберемося, що й куди писати.

Зі знаком рівності «=» та назвою «ВПР» все зрозуміло. Поговоримо про аргументи. Вони записуються в дужках через крапку з комою або заповнюються в комірки у вікні функції. Формула ВПР має 4 аргументи: потрібне значення, таблиця, номер стовпця та інтервальний перегляд.

Шукане значення – це назва осередку, з якого ми «підтягуватимемо» дані. Формула ВВР шукає повний або частковий збіг в іншій таблиці, з якої бере інформацію.

У нашому випадку вибираємо комірку «A2», у ній знаходиться найменування товару. ВПР візьме цю назву і шукатиме аналогічний осередок у другій таблиці з прайсом.

Таблиця – це діапазон осередків, з яких ми «підтягуватимемо» дані для шуканого значення. У цьому вся аргументі використовуємо абсолютні посилання. Це означає, що у формулі таблиця буде виглядати як $G$2:$H$11 замість G2:H11. Знаки "$" можна поставити вручну, а можна виділити "G2: H11" усередині формули та натиснути F4. Якщо цього не зробити, таблиця не зафіксується у формулі та зміниться під час копіювання.

У нашому випадку це таблиця з прайсом. Формула шукатиме в ній збіг із осередком, який вказали у першому аргументі формули – A2 (Кава). Натискаємо F4 і робимо посилання абсолютним.

Номер стовпця - Це стовпець таблиці, з якої потрібно взяти дані. Саме з нього ми «підтягуватимемо» результат.

1. Формула сканує таблицю по вертикалі.
2. Знаходить у лівому стовпці збіг з шуканим значенням.
3. Дивиться в стовпець навпаки, черговість якого ми вказуємо у цьому аргументі.
4. Передає дані у комірку з формулою.

У нашому випадку – це стовпець із ціною продуктів у прайсі.Формула шукає потрібне значення комірки A2 (Кава) у першому стовпці прайсу і «підтягує» дані з другого стовпця (бо ми вказали цифру 2) в комірку з формулою.

Інтервальний перегляд – це параметр, який може набувати 2 значень: «істина» або «брехня». Істина позначається у формулі цифрою 1 і означає приблизний збіг з потрібним значенням. Брехня позначається цифрою 0 і має на увазі точний збіг. Приблизний пошук та критерій «істина» зазвичай використовують при роботі з числами, а точний і «брехня» – у роботі з найменуваннями.

У разі шукане значення – це текстове найменування. Тому використовуємо точний пошук - ставимо цифру 0 і закриваємо дужку.

Автозаповнення

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

Щоб функція ВПР правильно спрацювала під час автозаповнення, потрібне значення має бути відносним посиланням, а таблиця – абсолютною.

• У нашому випадку потрібне значення – A2. Це відносне посилання на комірку, тому що в ній немає знаків "$". Завдяки цьому посилання на потрібне значення змінюється щодо кожного рядка, коли відбувається автозаповнення до інших осередків: A2 → A3 → … → A11. Це зручно, коли потрібно повторити формулу на кілька рядків, адже її не доводиться писати наново.
• Таблиця зафіксована абсолютним посиланням «$G$2:$H$11». Це означає, що посилання на осередки не зміняться під час автозаповнення. Таким чином, розрахунок щоразу буде коректним і спиратиметься на таблицю.

ВПР та приблизний інтервальний перегляд

У попередньому прикладі ми «підтягували» значення таблиці, використовуючи точний інтервальний перегляд. Він підходить для роботи із найменуваннями.Тепер розберемо ситуацію, коли знадобиться приблизний інтервальний перегляд.

Завдання. До магазину привезли товар. Необхідно надати кожному товару розмір партії, спираючись на його кількість.

Товари такі ж, як і в першому прикладі, але завдання змінилося: потрібно прив'язати формулу не до найменування, а до кількості

Рішення. Заповнюємо формулу ВПР у осередку «Партія», як було показано у попередньому прикладі.

Різниця в тому, що тепер потрібне значення – число, а інтервальний перегляд – істина, що означає приблизний пошук. Виходить такий результат:

Що сталося? Аргумент «інтервальний перегляд» має значення 1. Це означає, що формула ВПР шукає у таблиці найближче менше шуканого значення.

У нашому випадку кількість товару «Кава» – 380. ВПР бере це число у вигляді шуканого значення, після чого шукає найближче менше у сусідній таблиці – число 300. Наприкінці функція «підтягує» дані зі стовпця навпроти («Велика»). Якщо кількість товару "Кава" = 340 - це "Велика партія". Важливо, щоб крайній лівий стовпець таблиці, яка вказана у формулі, була відсортована за зростанням. Інакше ВПР не спрацює.

Підсумки

• Функція ВПР означає вертикальний перегляд. Вона переглядає крайній лівий стовпець таблиці зверху донизу.
• Синтаксис функції: =ВПР(потрібне значення;таблиця;номер стовпця;інтервальний перегляд).
• Функцію можна вписати вручну або у спеціальному вікні (Shift+F3).
• Шукане значення – відносне посилання, а таблиця – абсолютна.
• Інтервальний перегляд може шукати точний або приблизний збіг з потрібним значенням.
• Приблизний пошук та критерій «істина» зазвичай використовують при роботі з числами, а точний і «брехня» – у роботі з найменуваннями.
• Порядок роботи з функцією підходить для Google-таблиць.