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

Сталося? Тепер просто скопіюйте формулу з G2 до G3: G8.

Звіт про продаж готовий.

Крім того, щоб зрозуміти, що таке відповідність, спробуйте змінити назву продукту на A5 або E2. Наприклад, додайте прогалину в кінці. Зовні нічого не змінилося, але ви відразу отримаєте помилку #N/A. Тобто елемент не знайдено. При цьому таких випадкових помилок легко уникнути, про які ми поговоримо окремо.

Особливу увагу приділимо четвертому параметру. Ми вказали нуль (можна написати FALSE), що означає "точний пошук". Але що, якщо ви забудете вказати його та отримаєте номер стовпця, з якого вилучаються запитані дані?

Зробимо ще крок за кроком, що буде в цьому випадку.

1. Беремо значення з E2.
2. Почнемо шукати його в крайньому лівому стовпці діапазону $A$2: $B$7, тобто у стовпці A. Оскільки збігів у A2 не знайдено, давайте подивимося далі: що нижче.
3. Там знаходимо товар "Слива". Передбачається, що наш список відсортовано за абеткою. Адже це якраз головна умова знаходження зразкового збігу.
4. Оскільки у відсортованому списку «сливи» поступаються «бананам», функція вирішує, що далі шукати слово, що починається з «B», немає сенсу. Процес можна зупинити. І зупинимося на літері "А". Тобто найближча вартість є.
5. Оскільки пошук завершено, перейдіть від A2 до другого стовпця, тобто B. Введіть дані з B2 в G2 як результат обчислень.

На жаль, банани були в нашому прайс-листі нижче, але вони просто не пройшли. І тепер у списку покупок вказано неправильну ціну.

У цьому посібнику ми щойно розглянули основи. І як його реально використати?

Як працює функція ВПР в Excel: кілька прикладів для чайників.

Припустимо, вам потрібно вибрати дані конкретної особи зі списку співробітників. Подивимося, які тонкощі.

По-перше, потрібно одразу визначитися — потрібний точний чи приблизний пошук. Адже вони мають різні вимоги до підготовки вихідних даних.

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

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

Зауважте, що четвертий параметр — 1.

Деякі результати визначені правильно, але здебільшого це помилки. Функція продовжує сканувати дані в стовпці D з назвами товарів, доки не знайде значення, що перевищує значення, зазначене як критерій пошуку. Потім він зупиняється та повертає ціну.

Пошук ціни на єгипетські банани закінчився на першій позиції, оскільки друга містить сливи. І це слово за правилами алфавіту поступається "Бананам Єгипту". Це означає, що вам не слід шукати далі. Отримав 145. І не має значення, що це ціна на абрикоси. Пошук цін на сливи продовжувався доти, доки D15 не знайшов слово з найменшою за абеткою: яблука. Зупинилися та взяли ціну попередньої лінії.

Тепер подивимося, як усе мало статися, якщо все було зроблено правильно. Ми виконуємо лише сортування, як зазначено стрілкою.

Запитайте: "До чого тоді такий неточний погляд, якщо так багато проблем?"

він відмінно підходить для вибору значень із певних діапазонів.

Допустимо, у нас є знижка для покупців залежно від кількості купленого товару. Вам потрібно швидко підрахувати відсоткову ставку за досконале придбання.

Якщо у нас є кількість предметів 11, ми дивимося на стовпець D, доки знайдемо число більше 11.Це 20, і воно знаходиться у четвертому рядку. Давай зупинимося тут. Це означає, що наша знижка знаходиться в 3-му рядку і складає 3%.

Працюючи з діапазонами «від — до» цей прийом цілком підходить.

І ще невелика порада.

Використовуйте цей діапазон.

Щоб спростити використання формул, можна створити іменований діапазон, а потім посилатися на нього. У разі назвемо його «EmployeeData» (пам'ятайте, що тут не можна використовувати прогалини).

В осередку B2 ми введемо необхідне прізвище, а в осередку C2: F2 запишемо формули:

= ВПР ($ B $ 2; EmployeeData; 2; БРЕХНЯ)

= ВПР ($ B $ 2; EmployeeData; 3; Брехня)

= ВПР ($ B $ 2; EmployeeData; 4; БРЕХНЯ)

= ВПР ($ B $ 2; EmployeeData; 5; БРЕХНЯ)

Як бачите, вони відрізняються лише номером стовпця, з якого витягуватиметься необхідна інформація. 0 можна використати замість FALSE.

Які тут переваги?

• Хіба вас не зачаровують літери, цифри та знаки долара у звичайних адресах?
• Формула іменованого діапазону здається набагато більш доброзичливою, наочною та зрозумілою. Замість нудних безособових координат ви бачите ідентифікатори, які викликають у вас якісь асоціації. Погодьтеся, «ціна» або «ціна» — це безперечно інформація про ціну.
• Якщо вам потрібно змінити координати діапазону пошуку, який ви використовували у великій кількості формул, вам потрібно виправити кожну формулу або використовувати функцію «Знайти та замінити»? Погодьтеся, це дуже довго, ретельно, можливі помилки.

Використовуючи іменований діапазон, просто натисніть

Меню – Формула – Менеджер імен.

Потім знайдіть потрібний діапазон у списку діапазонів і налаштуйте його. Зміни будуть автоматично застосовані до всіх формул.

При використанні звичайних адрес ми завжди повинні думати про те, яку адресацію застосувати: відносну або абсолютну. Ця проблема не виникає під час використання іменованих діапазонів.

Використання символів підстановки та інші тонкощі критерію пошуку.

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

Регістр символів не впливає на результат. Можна вводити все великими літерами - нічого не зміниться. Див. Приклад нижче.

Якщо у списку є люди з однаковим прізвищем, буде знайдено лише перший. Як ми вже казали, щойно щось підходяще знайдено, процес зупиняється.

ви можете використовувати знаки підстановки * і ?. Нагадаю, що знак питання позначає будь-який символ, а зірочка позначає будь-яку кількість символів (включаючи нуль). Ми згадали про них на початку.

це хороша ідея, якщо вам відома лише частина значення аргументу.

Але будьте обережні: буде знайдено лише перший збіг, як показано на скріншоті. Це дуже важливе обмеження, яке потрібно враховувати.

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

Формула в осередку F2 виглядає так:

= ВПР («*» & D2 & «*»; $ A $ 2: $ B $ 7; 2; 0)

Тут ми використовуємо оператора конкатенації рядків &.

Конструкція "*" & D2 & "*" означає, що зірочки * додаються до вмісту осередку D2 з обох боків. Тобто, шукаємо будь-яке входження цього слова — до і після нього можуть бути інші слова та символи. Як, наприклад, трапилося із продуктом «Персики». У нашому випадку перший параметр матиме вигляд «персики».При пошуку такого дизайну прийнятним варіантом буде "Консервовані персики (Туреччина)".

Використання кількох умов.

Ще один простий приклад для чайників: як використовувати кілька умов при виборі бажаного значення?

Припустимо, у нас є список імен та прізвищ. Нам потрібно знайти відповідну людину і подивитися розмір її доходу.

У F2 ми використовуємо таку формулу:

= ВПР (D2 & «» & E2; $ A $ 2: $ B $ 21; 2; 0)

Розберемо покроково, як працює ВПР у разі.

Спочатку формуємо умову. Для цього за допомогою оператора & ми «вставляємо» ім'я та прізвище та вставляємо між ними пробіл.

Не забудьте укласти пробіл у лапки, інакше Excel не сприйме його як текст.

Потім у таблиці доходів знайдіть комірку з ім'ям та прізвищем, розділеними пробілом.

Отже, все відбувається за вже відпрацьованою схемою.

Ви можете спробувати перестрахуватися, якщо між іменем та прізвищем буде введено більше прогалин. Замініть пропуск у формулі на знак підстановки «*».

Примітно так – D2 & «*» & E2

Але при цьому врахуйте, що збіг імені та прізвища вже не буде зовсім точним. Ми розглянули аналогічний приклад трохи вище.

Окремо розглянемо складніші та точніші способи роботи з безліччю умов. Див. Посилання наприкінці.

"Розумна" таблиця.

І ще одна рекомендація – використовуйте розумний стіл.

Може бути дуже зручно спочатку перетворити довідкову таблицю (прайс-лист) на смарт за допомогою команди Home — Format as table (в англомовній версії Excel), потім вказати ім'я створеної таблиці у другому аргументі. До речі, воно буде присвоєно йому автоматично.

У цьому випадку розмір списку товарів із цінами нас більше не турбуватиме. Коли ви додаєте нові продукти до прайс-листу або видаляєте їх, розмір розумної таблиці змінюється сам.

Спеціальні інструменти для ВПР в Excel.

ВПР, безсумнівно, одна з найпотужніших і найкорисніших функцій Excel, але вона також одна з найбільш заплутаних. Щоб спростити вашу роботу, ви можете використовувати надбудову Ultimate Suite для Excel з майстром ВПР, який може значно заощадити час на пошук потрібних даних.

Майстер ВПР – простий спосіб писати складні формули

Інтерактивний майстер ВВР проведе вас через параметри конфігурації пошуку, необхідні для створення ідеальної формули для ваших критеріїв. Залежно від структури даних він буде використовувати стандартну функцію ВПР або формулу ІНДЕКС + ПОШУК, якщо йому потрібно отримати значення зліва від стовпчика підстановки.

Ось що вам потрібно зробити, щоб отримати формулу вирішення вашої проблеми:

1. Запустіть майстер, натиснувши кнопку Vlookup Wizard на стрічці Ablebits Data.

1. Виберіть вашу таблицю (вашу таблицю та таблицю пошуку).
2. Вкажіть такі стовпці (у багатьох випадках вони вибираються автоматично):
   • Ключовий стовпець: розташований в основній таблиці, він містить значення для пошуку.
   • Колонка пошуку — у якій шукатимемо.
   • Стовпець повернення: ми отримаємо значення.
3. Натисніть кнопку Вставити.

Подивимося все у дії.

Стандартний ВВР.

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

Як і в попередніх прикладах, наше завдання — знайти ціну на кожен товар, витягаючи її з прайс-листа.

Необов'язково писати руками нічого.

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

"Лівий" ВПР.

Коли стовпець результатів (Ціна) знаходиться ліворуч від області пошуку (Ціна), майстер автоматично вставляє формулу ІНДЕКС + ПОШУК:

Додатковий бонус! Завдяки експертному використанню посилань на осередки отримані формули ВПР можна скопіювати або перемістити в будь-який стовпець без необхідності оновлювати посилання.

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

Перенесення даних таблиці через функцію ВВР

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

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

Синтаксис ВВР

ВПР розшифровується як вертикальний перегляд. Тобто команда переносить дані з одного стовпця до іншого.Для роботи з рядками є горизонтальний перегляд – ГПР.

Аргументи функції такі:

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

Як переміщати дані за допомогою ВВР?

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

У комірку D3 потрібно підтягнути ціну гречки із правої таблиці. Пишемо =ВПР і заповнюємо аргументи.

Шуканим значенням буде гречка із осередку B3. Важливо проставити саме номер комірки, а не слово «гречка», щоб потім можна було простягнути формулу вниз і автоматично отримати інші значення.

Таблиця - виділяємо прайс без шапки. Тобто. лише самі назви товарів та його ціни. Цей масив ми зафіксуємо кнопкою F4, щоб він не змінювався при протягуванні формули.

Номер стовпця – у разі це цифра 2, оскільки необхідні нам дані (ціна) стоять у другому стовпці виділеної таблиці (прайса).

Інтервальний перегляд - ставимо 0, т.к. нам потрібні точні значення, а чи не приблизні.

Бачимо, що з правої таблиці до лівої підтягнулася ціна гречки.

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

Ось так, за допомогою елементарних дій можна підставляти значення з однієї таблиці до іншої. Важливо пам'ятати, що функція Вертикального перегляду працює тільки якщо таблиця, з якої підтягуються дані, знаходиться праворуч. В іншому випадку, потрібно перемістити її або скористатися командою ІНДЕКС та ПОШУКПОЗ. Оволодівши цими двома функціями ви зможете набагато більше реалізувати складніших рішень, ніж ті можливості, які надає функція ВПР чи ДПР.

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

Функція ВПР (VLOOKUP) в Excel для чайників

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

Функція ВПР в Екселі: покрокова інструкція

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

Якщо перелік товарів в обох таблицях збігається, то знаючи магічне поєднання Ctrl+C і Ctrl+Vдані про ціни можна легко підставити до даних про кількість.Однак, черговість позицій в обох таблицях не збігається. Тупо скопіювати ціни та підставити до кількості не вдасться.

Тому ми не можемо прописати формулу множення і протягнути вниз на всі позиції.

Що робити? Треба якось ціни з другої таблиці підставити до відповідної кількості у першій, тобто. ціну товару до кількості товару А, ціну Б до кількості Б і т.д.

Функція ВПР Ексель легко впорається із завданням.

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

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

Клікаємо за написом «ВВР». Відкриється наступне діалогове вікно.

Тепер потрібно заповнити пропоновані поля. У першому віконці «Шукане_значення» потрібно вказати критерій для комірки, в яку ми вписуємо формулу. У нашому випадку це осередок із найменуванням товару «А».

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

Наступне поле «Номер_стовпця» - Це число, на яке стовпець з даними (цінами) від стоїть від стовпця з критерієм (найменуванням товару) включно. Тобто відлік іде, починаючи із самого стовпця з критерієм. Якщо у нас у другій таблиці обидва стовпці знаходяться поруч, то потрібно вказати число 2 (перший – критерій, другий – ціни). Часто буває, що дані відстоять від критерію на 10 чи 20 стовпців. Це не важливо, Excel все порахує.

Останнє поле «Інтервальний_перегляд», де вказується тип пошуку: точне (0) або приблизне (1) збіг критерію. Поки ставимо 0 (або БРЕХНЯ). Другий варіант розглянуто нижче.

Натискаємо ОК. Якщо все правильно і значення критерію є в обох таблицях, то на місці щойно введеної формули з'явиться певне значення. Залишається лише протягнути (або просто скопіювати) формулу вниз до останнього рядка таблиці.

Тепер легко розрахувати вартість простим множенням на ціну.

Формулу ВПР можна прописати вручну, набираючи аргументи по порядку, і розділяючи крапкою з комою (див. нижче).

Особливості використання формули ВПР в Excel

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

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

2. Номер стовпця, що вказується у третьому полі «Номер_стовпця» при використанні Майстра функцій повинен відраховуватися, починаючи з самого критерію.

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

4. Останній параметр формули, який 0 (нуль), потрібно ставити обов'язково. Інакше формула може криво працювати.

5. Після використання ВПР саму формулу краще відразу видалити, залишивши лише отримані значення. Робиться це дуже просто. Виділяємо діапазон з отриманими значеннями, натискаємо «копіювати» і на це місце за допомогою спеціальної вставки вставляємо значення. Якщо таблиці перебувають у різних книгах Excel, дуже зручно розірвати зовнішні зв'язки (залишивши замість них лише значення) за допомогою спеціальної команди, яка перебуває на шляху Дані → Змінити зв'язки.

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

Це дозволить видалити одразу всі зовнішні посилання.

Приклади функції ВПР в Excel

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

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

Друга таблиця менша за першу, тобто. деякі коди у ній відсутні. Для відсутніх позицій ВВР видає помилку #Н/Д.

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

Конструкція з функцією ЛИСПОМИЛКА

Разом з функцією ВПР часто використовують функцію ПОЛИШНЯ, яка «заглушує» помилки #Н/Д і замість них повертає деяке значення. Зазвичай це 0 чи порожньо.

Очевидно, помилок більше немає, а замість них порожні осередки.

Різні формати критерію у таблицях

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

Перший випадок, коли критерії першої таблиці збережені як числа, а критерії другої таблиці – як текст.

У комірках з числами, збереженими як текст, у лівому верхньому куті утворюється зелений трикутник. Можна виділити всі такі числа і в списку, що розкривається, вибрати Перетворити на число.

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

Автоматично змінити формат критерію на другий таблиці не можна, т.к. Посилання веде на цілий діапазон. Доведеться втручатися у посилання критерій у першій таблиці.Для цього потрібно дописати функцію ТЕКСТ, яка змінить числовий формат на текстовий. Синтаксис функції ТЕКСТ передбачає обов'язкову вказівку формату. Достатньо задати формат #. Нижче зображення з готовою формулою.

Дві помилки, як і раніше, пов'язані з тим, що ці товари відсутні в другій таблиці. Щоб їх заглушити, можна знову скористатися функцією ПОЛИШНЯ.

Друга ситуація, у тому, що «текстом» є критерій з першої таблиці. Формати знову не збігаються.

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

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

Відсутні критерії, як і раніше, викликають помилку #Н/Д.У такому разі всю формулу можна ще раз «обернути» в ЕСЛИПОМИЛКА.

Функція СЖПРОБЕЛИ для чищення текстового критерію

Як критерій рекомендується брати унікальний код, у якому друкарські помилки, характерні для тексту, малоймовірні. Але іноді все-таки коду немає і критерієм виступає текст (назви організацій, прізвища людей тощо). І тут можливі випадкові помилки у написанні. Одна з найпоширеніших помилок – зайві прогалини. Проблема вирішується просто за допомогою функції СЖПРОБІЛИ для всіх критеріїв. Зробити це можна всередині формули ВПР, а можна і попередньо пройтися за всіма критеріями обох таблицях. Кому як зручніше.

Підрахунок номера стовпця у великій таблиці

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

Здорово заощаджує час.

Інтервальний перегляд функції ВПР

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

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

Простіше зрозуміти на прикладі. За результатами виконання плану продажу кожному торговому агенту слід видати заслужену премію (у відсотках від окладу). Якщо план виконано менш ніж на 100%, премія не належить, якщо план виконаний від 100% до 110% (110% не входить) – премія 20%, від 110% до 120% (120% не входить) – 40%, 120% та більше – премія 60%. Дані перебувають у такому вигляді.

Потрібно підставити премію виходячи з виконання планів продажів. Для вирішення задачі в першому осередку пропишемо таку формулу:

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

Джекі Чан здійснив план на 124%. Значить ВПР як критерій шукає у другій таблиці найближче менше значення. Це 120%. Потім відраховує 2 стовпці та повертає премію 60%. Брюс Лі план не виконав, тому його найближчий менший критерій – 0%.

Пропоную подивитися відеоурок про роботу ВВР з курсу «Основні функції Excel».