Функція ВПР в Excel для чайників і не тільки

Функція ВПР Excel дозволяє дані з однієї таблиці переставити у відповідні осередки другий. Її англійське найменування – VLOOKUP.

Дуже зручна і часто використовується. Т.к. Зіставити вручну діапазони з десятками тисяч найменувань проблематично.

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

Припустимо, на склад підприємства з виробництва тари та упаковки надійшли матеріали у певній кількості.

Вартість матеріалів – у прайс-листі. Це окрема таблиця.

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

Наведемо першу таблицю у потрібний нам вид. Додамо стовпці «Ціна» та «Вартість/Сума». Встановимо грошовий формат для нових осередків.
Виділяємо перший осередок у стовпці «Ціна». У прикладі – D2. Викликаємо "Майстер функцій" за допомогою кнопки "fx" (на початку рядка формул) або натиснувши комбінацію гарячих клавіш SHIFT + F3. У категорії «Посилання та масиви» знаходимо функцію ВПР та тиснемо ОК. Цю функцію можна викликати перейшовши по закладці «Формули» і вибрати зі списку «Посилання та масиви».
Відкриється вікно із аргументами функції. У полі «Шукане значення» - діапазон даних першого стовпця з таблиці з кількістю матеріалів, що надійшли. Це значення, які Excel повинен знайти у другій таблиці.
Наступний аргумент - "Таблиця". Це наш прайс-лист. Ставимо курсор у полі аргументу. Переходимо на аркуш із цінами. Виділяємо діапазон із найменуванням матеріалів та цінами. Вказуємо, які значення функція має зіставити.
Щоб Excel посилався безпосередньо на ці дані, потрібно зафіксувати посилання.Виділяємо значення поля «Таблиця» та натискаємо F4. З'являється $ значок.
У полі аргументу "Номер стовпця" ставимо цифру "2". Тут є дані, які потрібно «підтягнути» в першу таблицю. «Інтервальний перегляд» - БРЕХНЯ. Т.к. нам потрібні точні, а чи не приблизні значення.

Натискаємо ОК. А потім розмножуємо функцію по всьому стовпцю: чіпляємо мишею правий нижній кут і тягнемо вниз. Отримуємо необхідний результат.

Тепер знайти вартість матеріалів не складе труднощів: кількість * ціну.

Функція ВВР пов'язала дві таблиці. Якщо зміниться прайс, то і зміниться вартість матеріалів, що надійшли на склад (сьогодні надійшли). Щоб уникнути цього, скористайтеся «Спеціальною вставкою».

Виділяємо стовпець із вставленими цінами.
Права кнопка миші - "Копіювати".
Не знімаючи виділення, правою кнопкою миші є «Спеціальна вставка».
Поставити галочку навпроти «Значення». ОК.

Формула в осередках зникне. Залишаться лише значення.

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

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

У старому прайсі робимо стовпець "Нова ціна".
Виділяємо першу комірку та вибираємо функцію ВПР. Задаємо аргументи (див. вище). Для прикладу: . Це означає, що потрібно взяти найменування матеріалу з діапазону А2:А15, подивитися його в «Новому прайсі» в стовпці А. Потім взяти дані з другого стовпця нового прайсу (нову ціну) і підставити їх у комірку С2.

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

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

Досі ми пропонували для аналізу лише одну умову – найменування матеріалу.Насправді ж нерідко потрібно порівняти кілька діапазонів із даними і вибрати значення по 2, 3-му тощо. критеріям.

Таблиця для прикладу:

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

Справа ускладнюється тим, що від одного постачальника надходить кілька найменувань.

Додаємо до таблиці крайній лівий стовпець (важливо!), об'єднавши «Постачальників» та «Матеріали».
Так само об'єднуємо шукані критерії запиту:
Тепер ставимо курсор у місці і задаємо аргументи для функции: . Excel знаходить потрібну ціну.

Розглянемо формулу детально:

Функція ВПР і список, що випадає

Припустимо, якісь дані у нас зроблені у вигляді списку, що розкривається. У прикладі – «Матеріали». Необхідно налаштувати функцію те щоб при виборі найменування з'являлася ціна.

Спочатку зробимо список, що розкривається:

Ставимо курсор у осередок Е8, де і буде цей перелік.
Заходимо на вкладку "Дані". Меню "Перевірка даних".
Вибираємо тип даних - "Список". Джерело – діапазон із найменуваннями матеріалів.
Коли натиснемо ОК - сформується список, що випадає.

Тепер потрібно зробити так, щоб при виборі певного матеріалу у графі ціна з'являлася відповідна цифра. Ставимо курсор в осередок Е9 (де має з'являтися ціна).

Відкриваємо «Майстер функцій» та вибираємо ВПР.
Перший аргумент - «Шукане значення» - осередок з списком, що випадає. Таблиця – діапазон із назвами матеріалів та цінами. Стовпець, відповідно, 2. Функція набула наступного вигляду: .
Натискаємо ВВЕДЕННЯ і насолоджуємося результатом.

Змінюємо матеріал – змінюється ціна:

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

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

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

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

ВПР (потрібне значення; діапазон пошуку; номер стовпця з вхідним значенням; 0 (БРЕХНЯ) або 1 (ІСТИНА)).

БРЕХНЯ – точне значення, ІСТИНА – приблизне значення.

Найпростіше завдання функції ВПР. Наприклад, ми маємо список лікарських препаратів. Наше перше завдання – знайти вартість препарату Хепілор.

У комірці С12 починаємо писати функцію:

B12 – оскільки нам потрібний Хепілор, вибираємо комірку з попередньо написаною назвою шуканих ліків.
Далі вибираємо діапазон даних B3: D10, де функція здійснюватиме пошук потрібного нам значення. Крайній лівий стовпець діапазону повинен містити в собі критерій, по якому проводиться пошук значення.
Наступний крок – вказати номер стовпця в масиві B3: D10, з якого буде прочитана інформація на одному рядку з Хепілором. Стовпці нумеруються зліва направо в самому діапазоні, у нашому прикладі перший стовпець - В, але не А, оскільки А лежить поза межами діапазону.

Пошук по стовпцю «Виробник» працюватиме так само, потрібно просто вказати послідовність стовпця, де знаходиться потрібна нам інформація – замінюємо цифру «3» у формулі (осередок С27) на цифру «2»:

Є певна особливість, пов'язана із стовпцями.Іноді в Excel-файлі деякі таблиці об'єднують у таблицях. На малюнку нижче у формулі на місці порядкового номера стовпця у нас написано цифру «3», але результат – назва виробника, а не ціна, як у першому прикладі:

Відбулося зрушення нумерації стовпців саме через наявність об'єднання осередків у стовпці «Лікарський засіб»: ми об'єднували стовпці «H» і «I», візуально стовпець «Лікарський засіб» - це перший стовпець, а «Виробник» - другий, АЛЕ формула нумерує їх наступним чином:

H – перший;
I – другий;
J – третій;
K – четвертий.

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

Використання функції ВПР для роботи з кількома таблицями та іншими функціями

У наступному прикладі розглянемо, як ще ми можемо використовувати функцію для пошуку та одержання інформації за критеріями та комбінування функції з функцією ОСЛИПОМИЛКА. Наприклад, ми маємо два звіти – звіт про кількість товару та звіт про ціну за одиницю товару, які нам необхідні для підрахунку вартості. Знову ж таки, з невеликою кількістю даних це цілком можна зробити вручну, але коли ми маємо великий обсяг, впоратися з цим швидше і ефективніше нам допоможе функція ВПР. У осередку D3 починаємо писати функцію:

B3 – критерій, яким проводимо пошук даних.
F3:G14 – діапазон, за яким наша функція здійснюватиме пошук збігу критерію та даних за рядком.
Цифра «2» - номер стовпця з необхідною інформацією за критерієм.
Цифра "0" (або можна використовувати слово "БРЕХНЯ") - для точності результатів.

Таким чином, коли ми задаємо формулі шуканий критерій, вона починає пошук збігів з верхнього осередку першого стовпця (крок 1 на зображенні). Потім функція читає всі критерії зверху вниз, поки не знайде точне збіг (крок 2). Коли ВПР дійде до Хепілора, вона відрахує потрібну кількість стовпців вправо (крок 3) і дасть нам значення для критерію – ціну 86,90 (крок 4):

Але зараз у нас є дані лише за першим критерієм. Для того щоб заповнити третій стовпець першої таблиці D до кінця, потрібно просто скопіювати функцію до останнього критерію. Однак, на цьому етапі для коректної роботи діапазон, де відбувається пошук, потрібно закріпити, інакше масив даних з'їде вниз і в нас нічого не вийде. Для цього використовуємо абсолютні посилання для діапазону в комірці D3 - виділяємо курсором діапазон F3: G14 і натискаємо клавішу F4, після чого копіювання формули до кінця таблиці:

У результаті ми отримуємо необхідний результат:

Однак наш приклад базувався на повній відповідності критеріїв з обох таблиць – однакова кількість товарів, однакові найменування. Але що, якщо, наприклад, прибрати останні чотири товари зі звіту щодо цін за упаковку? Тоді у нас буде помилка #Н/Д у першій таблиці в тих позиціях, які знаходяться на одному рядку з критерієм:

Якщо вас не влаштовує такий вміст осередків, можна замінити значення помилки. Для цього комбінуємо функцію ВПР з функцією ОСЛИПОМИЛКА. Синтаксис функції ОСЛИПОМИЛКА (значення, значення_якщо_помилка), таким чином значенням у нас буде наша використана функція ВПР, а значенням якщо помилка – те, що ми хочемо бачити замість #Н/Д, наприклад, прочерк, але обов'язково взятий у лапки:

В результаті ми отримаємо красиво оформлену таблицю з належним виглядом:

Використання приблизного значення

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

Як бачимо, розмір премії залежить від того діапазону системи преміювання, куди потрапив показник виконання продажів конкретного співробітника. Ми бачимо, що якщо план виконаний менш ніж на 100% - премія не присвоюється, а якщо на 107% (вище 100%, але менше 110%), тоді співробітник отримує премію розміром 10%. Описані показники премії нам потрібно вписати за допомогою функції ВПР у стовпець «Премія» першої таблиці, лише цього разу критерій перебуватиме у певному діапазоні.

Для коректної роботи необхідно переконатися, що межі діапазонів у другій таблиці крайнього лівого стовпця розміщені по зростанню зверху донизу (крок 1). Формула бере обраний нами критерій та здійснює пошук у першому стовпці другої таблиці (крок 2), переглядаючи всі значення зверху донизу (крок 3). Як тільки функція знаходить перше значення, яке перевищує критерій з першої таблиці, робить крок назад (крок 4) і зчитує значення, яке відповідає знайденому критерію (крок 5). Іншими словами, при неточному пошуку функція ВПР шукає менше значення для шуканого критерію:

Таким чином, наша функція виглядатиме так:

І результат використання функції ВПР з приблизним пошуком має такий результат:

Наприклад, співробітник Ольга має премію розміром 0%, оскільки вона виконала 76% продажів, тобто перевиконала план на 0%. з двох таблиць.

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

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

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

Загальна інформація про ВПР (VLOOKUP)
Функція ВПР - це одна з найбільш популярних функцій посилань і масивів.

Рівень складності за шкалою BRP ADVICE - 3 із 7 .
ВПР (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.

Яке завдання вирішує функція впр