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

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

Як правильно користуватися функцією ВПР в Екселі

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

Окремо ми маємо розроблений прайс-лист із зазначенням вартості товарів. Це буде для нашої роботи окрема таблична частина.

Хочеш збудувати кар'єру мрії?
Підпишись та отримуй добірку найкращих вакансій на ринку та корисні матеріали від провідних HR

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

Алгоритм дій

1. Для цих цілей необхідний параметр першої таблиці привести у відповідний нам вигляд. Для зручності розрахунку додаємо стовпці та присвоюємо їм відповідну літерну функцію: «Ціна», а також «Вартість/Сума» та вказуємо грошовий еквівалент нових осередків.

2.Далі, звичайною дією виділяємо осередок «Ціна» і У нашому конкретному прикладі цей осередок буде під номером D2. Після цього ми викликаємо команду «Майстер функцій», натиснувши на «fx», тобто на ті кнопки, які розташовані на початку рядка. Можна також використовувати стандартну команду, одночасно натиснувши на кнопки SHIFT+F3. Після цього вирушаємо до категорії «Посилання та масиви», і знаходимо нам необхідну опцію ВПР. Далі натискаємо кнопку підтвердження ОК. Можна також скористатися цією функцією, натиснувши на кнопки, використовуючи перехід з масиву дерева закладок «Формули». Тут нам потрібно буде знову знайти категорію «Посилання та масиви» і виконати вказаний механізм вище.

3. Після цього перед нами відкриється вікно з призначеними аргументами шуканої функції. Звертаємо увагу на поле «Шукані значення», і виберемо дані з раніше сформованого першого стовпця з табличною функцією матеріалу, що надійшов на наш склад. Для нас це буде той блок даних, який необхідний для розрахунку Excel, тобто для пошуку в другій таблиці.

4. Далі переходимо до розрахунку даних аргументу - тобто блок "Таблиця". Це наш розроблений прайс-лист на початковому етапі роботи. Встановлюємо курсор у полі аргументу. Після цього здійснюємо режим переходу на поле аркуша із цінами. Знову виділяємо діапазон із назвами матеріалів та цінами. Вказуємо таблиці, які функції можна порівняти.

5. Для того щоб наш Excel зміг посилатися безпосередньо на ці параметри, рекомендується зафіксувати зазначене посилання. Для цього знову виділяємо функцію осередку «Таблиця» та натискаючи клавішу F4. У нас з'явиться значок у вигляді $.

6. Далі ми знову звертаємося до функції поля аргументу під назвою «Номер стовпця», і надає цифрове значення «2».Тут будуть ті метадані, які нам потрібно буде потім «підтягнути» в першу таблицю. Зверніть увагу, що поява функції «Інтервальні значення» - це буде для нас брехня. Тобто враховуємо, що нам потрібні ТОЧНІ параметри, а не приблизні.

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

З цього випливає, що тепер знайти нам необхідну вартість товару не складе труднощів, тобто «кількість» * «ціна».
Звідси видно, що функція ВПР Excel пов'язала нам метадані двох різнотипних осередків. Але, якщо зміниться база даних прайс-листа, то відповідно будуть змінені в ціновому еквіваленті матеріали та товари, що надійшли на склад, тобто надійшли за фактом, або «на сьогодні». Щоб уникнути цього прикрого факту, потрібно буде виконати наступний порядок таблиці.

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

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

ВПР - це функція Excel для пошуку та вилучення даних із певного стовпця в таблиці. Вона підтримує приблизне та точне зіставлення, а також знаки підстановки (* і ?). Значення пошуку повинні відображатися в першому стовпці таблиці, а стовпці пошуку знаходяться праворуч.

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

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

  Як зробити ВВР в Excel: зрозуміла покрокова інструкція.

  Спочатку на простому прикладі розберемо, як працює функція ВПР в Excel. Припустимо, ми маємо дві таблиці. Перша – це прайс-лист із найменуваннями та цінами. Друга – це замовлення на купівлю деяких із цих товарів. Шукати в прайс-листі потрібний товар і руками вписувати на замовлення його ціну - дуже стомлююче заняття. Адже прайс із цінами може налічувати сотні рядків. Нам потрібно зробити все автоматично.

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

  Наш прайс-лист розташований у стовпцях А та В. Список покупок – в E-H. Припустимо, перша позиція у списку покупок – банани. Нам потрібно в стовпці A, де вказані всі найменування, знайти цей товар, потім його ціну помістити в комірку G2.

  Для цього в G2 запишемо таку формулу:

  А тепер докладно розберемо, як зробити ВПР.

  1. Ми беремо значення з E2.
  2. Шукаємо точний збіг (оскільки четвертим параметром вказано 0) у діапазоні $A$2:$B$7 у першій його колонці (крайній лівій). Зверніть увагу, що краще відразу використовувати абсолютні посилання на прайс-лист, щоб при копіюванні цієї формули посилання не «сковзнула».
  3. Якщо товар буде знайдено, потрібно перейти в другий стовпець діапазону (на це вказує третій параметр = 2).
  4. Взяти з нього ціну і вставити її в наш осередок G2.

  Вийшло? Тепер просто скопіюйте формулу з G2 до G3:G8.

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

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

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

  Давайте ще раз крок за кроком розберемо, що в цьому випадку відбуватиметься.

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

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

  За допомогою цієї інструкції ми розглянули лише основи. А як реально цим можна скористатися?

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

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

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

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

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

  Зауважте, що четвертий параметр дорівнює 1.

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

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

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

  Ви запитаєте: "А навіщо тоді цей неточний перегляд, якщо з ним стільки проблем?"

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

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

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

  Працюючи з інтервалами виду «від – до» така методика цілком придатна.

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

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

  Для спрощення роботи з формулами можна створити іменований діапазон і надалі посилатися на нього. У нашому випадку назвемо його "Дані Співробітників" (пам'ятайте, що прогалини тут неприпустимі).

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

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

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

  1. У вас не рябить в очах від букв, цифр та знаків долара у звичайних адресах діапазонів?

  Формула з іменованим діапазоном виглядає набагато більш дружньо, наочно та зрозуміло. Замість нудних та безликих координат ви бачите ідентифікатори, які народжують у вас деякі асоціації. Погодьтеся, "price" або "ціна" - це напевно інформація про ціни.

  1. Якщо з якихось причин вам необхідно буде змінити координати пошуку, який ви використовували у великій кількості формул – вам потрібно коригувати кожну формулу або користуватися функцією “Знайти та замінити”? Погодьтеся, це дуже довго, трудомістко, можливі помилки.

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

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

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

  1. Використовуючи звичайні адреси, ми повинні думати, яку адресацію застосувати – відносну чи абсолютну. З використанням іменованих діапазонів цієї проблеми немає.

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

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

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

  Це доцільно робити, якщо ми знаємо лише частину значення аргументу.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  Можна спробувати підстрахуватися на той випадок, якщо між ім'ям та прізвищем введено кілька прогалин. Знак пропуску у формулі замінюємо на знак підстановки "*".

  Помітно так - D2&"*"&E2

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

  Більш складні та точні способи роботи з кількома умовами ми розглянемо окремо: ВВР з кількома умовами: 5 прикладів. Дивіться також посилання наприкінці статті.

  ВПР у таблиці Excel

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

  Буває дуже зручно спочатку перетворити пошукову таблицю (прайс-лист) на «розумну» за допомогою команди Головна – Форматувати як таблицю (Home – Format as Table в англійській версії Excel), а потім вказати у другому аргументі використовувати ім'я створеної таблиці. До речі, воно буде присвоєно автоматично.

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

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

  Безсумнівно, ВПР - одна з найпотужніших і найкорисніших функцій Excel, але вона також одна з найбільш заплутаних. Для роботи з нею простіше можна використовувати надбудову Ultimate Suite for Excel з інструментом «Майстер ВПР», що дозволяє значно заощадити час на пошук потрібних даних.

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

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

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

  1. Запустіть майстер кнопкою Vlookup Wizard на стрічці Ablebits Data.
  1. Виберіть свою основну таблицю (Your table) та таблицю пошуку (Lookup table).
  2. Вкажіть такі стовпці (у багатьох випадках вони вибираються автоматично):
     • Ключовий стовпець (Key column) – знаходиться у вашій основній таблиці, містить значення для пошуку.
     • Стовпець пошуку (Lookup column) — в який будемо шукати.
     • Стовпець, що повертається (Return column) - з нього отримуватимемо значення.
  3. Натисніть кнопку Вставити (Insert).

  Давайте подивимося все у дії.

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

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

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

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

  Після натискання кнопки Insert праворуч від колонки з найменуваннями товарів буде вставлено додаткову, яка буде озаглавлена ​​так само, як і стовпець результату. Сюди буде записано всі знайдені значення ціни, причому у вигляді формули. За потреби ви зможете її підправити чи використовувати інших таблицях.

  Лівий ВВР

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. B3 – критерій, яким проводимо пошук даних.
  2. F3:G14 – діапазон, за яким наша функція здійснюватиме пошук збігу критерію та даних за рядком.
  3. Цифра «2» - номер стовпця з необхідною інформацією за критерієм.
  4. Цифра "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%. А співробітник Наталія здійснила продажі на 21% вище за норму і була премійована на 20%, що ми й бачимо, якщо порівняти самостійно дані з двох таблиць.

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

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