зміст
Нещодавно ми присвятили статтю одній з найкорисніших функцій Excel під назвою VPR і показали, як це можна використовувати для вилучення необхідної інформації з бази даних у комірку робочого листа. Ми також згадали, що є два випадки використання функції VPR і лише один із них має справу із запитами до бази даних. У цій статті ви дізнаєтесь про інший менш відомий спосіб використання функції VPR в Excel.
Якщо ви ще цього не зробили, то обов'язково прочитайте останню статтю про функцію VPR, тому що вся інформація нижче передбачає, що ви вже знайомі з принципами, описаними в першій статті.
При роботі з базами даних функції VPR передається унікальний ідентифікатор, який використовується для ідентифікації інформації, яку ми хочемо знайти (наприклад, код продукту або ідентифікаційний номер клієнта). Цей унікальний код повинен бути присутнім у базі даних, інакше VPR повідомить про помилку. У цій статті ми розглянемо цей спосіб використання функції VPRколи ідентифікатор взагалі не існує в базі даних. Як би функція VPR перемикається в приблизний режим і вибирає, які дані надавати нам, коли ми хочемо щось знайти. За певних обставин це саме те, що потрібно.
Приклад із життя. Ставимо завдання
Давайте проілюструємо цю статтю прикладом із реального життя – розрахунок комісійних на основі широкого діапазону показників продажів. Ми почнемо з дуже простого варіанту, а потім будемо поступово його ускладнювати, поки єдиним раціональним рішенням проблеми не стане використання функції VPR. Початковий сценарій для нашої фіктивної задачі виглядає наступним чином: якщо продавець заробляє понад 30000 30 доларів на рік, то його комісія становить 20%. В іншому випадку комісія становить лише XNUMX%. Оформимо це у вигляді таблиці:
Продавець вводить дані про свої продажі в клітинку B1, а формула в клітинці B2 визначає правильну ставку комісії, яку може очікувати продавець. У свою чергу, отримана ставка використовується в клітинці B3 для розрахунку загальної комісії, яку повинен отримати продавець (просто перемножуючи клітинки B1 і B2).
Найцікавіша частина таблиці міститься в клітинці B2 – це формула для визначення ставки комісії. Ця формула містить функцію Excel під назвою IF (ЯКЩО). Для тих читачів, які не знайомі з цією функцією, я поясню, як вона працює:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
стан є аргументом функції, який приймає значення будь-якого ІСТИННИЙ КОД (ІСТИНА), або ПОМИЛКОВИЙ (ПОМИЛКОВИЙ). У наведеному вище прикладі вираз B1
Чи правда, що B1 менше, ніж B5?
Або можна сказати інакше:
Чи правда, що загальна сума продажів за рік менша за граничне значення?
Якщо відповісти на це питання ТАК (TRUE), тоді функція повертається значення, якщо істинно (значення, якщо TRUE). У нашому випадку це буде значення клітинки B6, тобто ставка комісії, коли загальний обсяг продажів нижчий за порогове значення. Якщо ми відповімо на запитання НЕМАЄ (FALSE), потім повертається значення, якщо false (значення, якщо FALSE). У нашому випадку це значення комірки B7, тобто ставка комісії, коли загальний обсяг продажів перевищує порогове значення.
Як бачите, якщо ми візьмемо загальний обсяг продажів у 20000 2 доларів США, ми отримаємо комісійну ставку 20% у клітинці B40000. Якщо ми введемо значення $30, то ставка комісії зміниться на XNUMX%:
Ось як працює наша таблиця.
Ускладнюємо завдання
Давайте трохи ускладнимо ситуацію. Встановимо інший поріг: якщо продавець заробляє більше $40000, то комісія зростає до 40%:
Начебто все просто і зрозуміло, але наша формула в комірці В2 помітно ускладнюється. Якщо ви уважно подивитеся на формулу, то побачите, що третій аргумент функції IF (IF) перетворився на ще одну повноцінну функцію IF (ЯКЩО). Така конструкція називається вкладенням функцій одна в одну. Excel із задоволенням допускає ці конструкції, і вони навіть працюють, але їх набагато важче прочитати та зрозуміти.
Ми не будемо вникати в технічні подробиці – чому і як це працює, а також не будемо вдаватися в нюанси написання вкладених функцій. Адже це стаття присвячена функції VPR, не повний посібник із Excel.
У будь-якому випадку формула стає складнішою! Що, якщо ми запровадимо інший варіант комісійної ставки в розмірі 50% для тих продавців, які мають продажі понад 50000 60000 доларів США. І якщо хтось продав понад 60 XNUMX доларів, чи заплатить він XNUMX% комісії?
Тепер формула в комірці B2, навіть якщо вона була написана без помилок, стала зовсім нечитабельною. Я думаю, що мало хто хоче використовувати формули з 4 рівнями вкладеності в своїх проектах. Має бути легший спосіб?!
І такий спосіб є! Нам допоможе функція VPR.
Для вирішення проблеми застосовуємо функцію VLOOKUP
Давайте трохи змінимо дизайн нашого столу. Ми збережемо ті самі поля та дані, але впорядкуємо їх по-новому, більш компактно:
Знайдіть хвилинку і переконайтеся, що новий стіл Таблиця тарифів містить ті самі дані, що й попередня таблиця порогових значень.
Основна ідея полягає у використанні функції VPR визначити бажану тарифну ставку за табл Таблиця тарифів в залежності від обсягу продажу. Зверніть увагу, що продавець може продати товар на суму, яка не дорівнює одному з п’яти порогів у таблиці. Наприклад, він міг би продати за $34988, але такої суми немає. Давайте подивимося, як працює функція VPR може впоратися з такою ситуацією.
Вставлення функції VLOOKUP
Виберіть клітинку B2 (куди ми хочемо вставити нашу формулу) і знайдіть ВЛООКУП (VLOOKUP) у бібліотеці функцій Excel: Формули (формули) > Функція бібліотеки (Бібліотека функцій) > Пошук та довідкові матеріали (Посилання та масиви).
З'явиться діалогове вікно Аргументи функції (Аргументи функції). Заповнюємо значення аргументів по черзі, починаючи з Пошук_значення (Пошукове_значення). У цьому прикладі це загальна сума продажів із клітинки B1. Помістіть курсор у поле Пошук_значення (Пошукове_значення) і виберіть клітинку B1.
Далі потрібно вказати функції VPRде шукати дані. У нашому прикладі це таблиця Таблиця тарифів. Помістіть курсор у поле Масив_таблиці (Таблиця) і виберіть всю таблицю Таблиця тарифівкрім заголовків.
Далі нам потрібно вказати, з якого стовпця витягувати дані за допомогою нашої формули. Нас цікавить розмір комісії, який знаходиться у другому стовпчику таблиці. Тому для аргументу Номер_індексу стовпця (Номер_стовпця) введіть значення 2.
І, нарешті, наведемо останній аргумент – Пошук_діапазону (Інтервальний_пошук).
Важливо: саме використання цього аргументу робить різницю між двома способами застосування функції VPR. При роботі з базами даних аргумент Пошук_діапазону (діапазон_пошуку) завжди має мати значення ПОМИЛКОВИЙ (FALSE), щоб знайти точну відповідність. У нашому використанні функції VPR, потрібно залишити це поле порожнім або ввести значення ІСТИННИЙ КОД (ПРАВДА). Вкрай важливо правильно вибрати цей варіант.
Щоб було зрозуміліше, представимо ІСТИННИЙ КОД (ІСТИНА) у полі Пошук_діапазону (Інтервальний_пошук). Хоча, якщо залишити поле порожнім, це не буде помилкою, оскільки ІСТИННИЙ КОД це значення за замовчуванням:
Ми заповнили всі параметри. Тепер тиснемо OK, і Excel створює для нас формулу з функцією VPR.
Якщо ми поекспериментуємо з декількома різними значеннями загальної суми продажів, то переконаємося, що формула працює правильно.
Висновок
Коли функція VPR працює з базами даних, аргумент Пошук_діапазону (діапазон_пошуку) потрібно прийняти ПОМИЛКОВИЙ (ПОМИЛКОВИЙ). І значення, введене як Пошук_значення (Lookup_value) має існувати в базі даних. Іншими словами, він шукає точну відповідність.
У прикладі, який ми розглянули в цій статті, немає необхідності отримувати точну відповідність. Це той випадок, коли функція VPR необхідно перейти в наближений режим, щоб повернути бажаний результат.
Наприклад: Ми хочемо визначити, яку ставку використовувати для розрахунку комісії для продавця з обсягом продажів $34988. функція VPR повертає нам значення 30%, що є абсолютно правильним. Але чому формула вибрала рядок, який містить саме 30%, а не 20% або 40%? Що мається на увазі під орієнтовним пошуком? Давайте розберемося.
Коли аргумент Пошук_діапазону (interval_lookup) має значення ІСТИННИЙ КОД (ІСТИНА) або опущена функція VPR повторює перший стовпець і вибирає найбільше значення, яке не перевищує значення пошуку.
Важливий момент: Щоб ця схема працювала, перший стовпець таблиці має бути відсортований у порядку зростання.