VLOOKUP в Excel, частина 2 Використання VLOOKUP без бази даних
У недавній статті ми ввели функцію Excel, яку називаємо VLOOKUP і пояснили, як він може бути використаний для отримання інформації з бази даних у клітинку місцевого робочого аркуша. У цій статті ми згадали, що для VLOOKUP було два способи використання, і тільки один з них стосувався запитів до баз даних. У цій статті, другій і заключній в серії VLOOKUP, ми розглядаємо це інше, менш відоме використання для функції VLOOKUP.
Якщо ви ще цього не зробили, будь ласка, прочитайте першу статтю VLOOKUP - ця стаття буде вважати, що багато концепцій, пояснених у цій статті, вже відомі читачеві.
При роботі з базами даних VLOOKUP передається "унікальний ідентифікатор", який служить для визначення того, який запис даних ми хочемо знайти в базі даних (наприклад, код продукту або ідентифікатор клієнта). Цей унікальний ідентифікатор обов'язково існують в базі даних, інакше VLOOKUP повертає нам помилку. У цій статті ми розглянемо спосіб використання VLOOKUP, де ідентифікатору взагалі не потрібно існувати в базі даних. Це майже так, ніби VLOOKUP може прийняти підхід “досить близько”, щоб повернути дані, які ми шукаємо. За певних обставин це точно що нам потрібно.
Ми проілюструємо цю статтю з прикладом реального світу - розрахунку комісій, які генеруються на основі цифр продажів. Почнемо з дуже простого сценарію, а потім поступово зробимо його більш складним, доки єдиним раціональним рішенням проблеми буде використання VLOOKUP. Початковий сценарій в нашій фіктивній компанії працює таким чином: якщо продавець створює більше $ 30 000 на продаж у даному році, комісія, яку вони отримують на цих продажах, становить 30%. Інакше їх комісія становить лише 20%. Поки що це досить простий лист:
Щоб скористатися цією таблицею, продавець вводить свої показники продажів у комірку B1, а формула в комірці B2 обчислює правильну ставку комісії, яку вони мають право отримувати, яка використовується в комірці B3 для обчислення загальної комісії, яку повинен сплатити продавець ( це просто множення B1 і B2).
Клітка B2 містить єдину цікаву частину цього аркуша - формулу для визначення того, яка ставка комісії використовувати: одна нижче поріг $ 30,000, або один вище поріг. Ця формула використовує функцію Excel IF. Для тих читачів, які не знайомі з IF, він працює так:
IF (умова, значення if true, значення false)
Де стан є виразом, який оцінюється або вірно або помилковий. У наведеному вище прикладі стан є виразом B1
Як ви можете бачити, використання загального обсягу продажів у розмірі $ 20,000 дає нам 20% комісійної ставки в комірці B2. Якщо ввести значення $ 40,000, ми отримаємо іншу ставку комісії:
Тому наша таблиця працює.
Давайте зробимо його більш складним. Введемо другий поріг: якщо продавець заробляє більше $ 40,000, то їх комісія зростає до 40%:
Досить легко зрозуміти в реальному світі, але в комірці B2 наша формула стає все більш складною. Якщо ви уважно подивитеся на формулу, ви побачите, що третій параметр вихідної функції IF ( значення, якщо false) тепер цілком функціонує ПФ самостійно. Це називається a вкладені функції (функція в межах функції). Це цілком правильно в Excel (він навіть працює!), Але важче читати і розуміти.
Ми не збираємося входити в гайки і болти того, як і чому це працює, і не будемо розглядати нюанси вкладених функцій. Це посібник з VLOOKUP, а не в Excel взагалі.
Так чи інакше, все гірше! Коли ми вирішимо, що якщо вони заробляють більше $ 50,000, то вони мають право на 50% комісії, а якщо вони заробляють більше $ 60,000, то вони мають право на комісію в розмірі 60%?
Тепер формула в комірці B2, хоча і правильна, стала практично нечитабельною. Ніхто не повинен писати формули, де функції вкладені чотирма рівнями глибоко! Звичайно, має бути простіший спосіб?
Безумовно, є. VLOOKUP на допомогу!
Давайте трохи переробили робочий аркуш. Ми збережемо всі ті ж цифри, але організуємо їх по-новому, більше табличний шлях:
Знайдіть хвилинку і перевірте для себе, що новий Таблиця тарифів працює точно так само, як і ряд порогів вище.
Концептуально, те, що ми збираємося зробити, це використовувати VLOOKUP для пошуку загального обсягу продажів продавця (з B1) у таблиці ставок і повернення нам відповідної ставки комісії. Зверніть увагу, що продавець, можливо, дійсно створив продажі ні одне з п'яти значень у таблиці ставок ($ 0, $ 30,000, $ 40,000, $ 50,000 або $ 60,000). Можливо, вони створили $ 34,988. Важливо відмітити, що $ 34,988 ні відображаються в таблиці ставок. Давайте подивимося, чи зможе VLOOKUP вирішити нашу проблему ...
Ми вибираємо клітинку B2 (розташування, яку ми хочемо поставити формулою), а потім вставимо функцію VLOOKUP з Формули вкладка:
The Функція Аргументи з'являється вікно для VLOOKUP. Ми заповнюємо аргументи (параметри) один за одним, починаючи з Lookup_value, що є, в даному випадку, загальним обсягом продажів з осередку B1. Ми розміщуємо курсор у вікні Lookup_value і натисніть один раз на клітинку B1:
Далі нам потрібно вказати для VLOOKUP яку таблицю шукати ці дані. У цьому прикладі це, звичайно, таблиця тарифів. Ми розміщуємо курсор у вікні Таблиця Потім виділіть весь таблицю тарифів - виключаючи заголовки:
Далі ми повинні вказати, який стовпець таблиці містить інформацію, яку ми хочемо, щоб наша формула повернулася до нас. У цьому випадку ми хочемо, щоб ставка комісії була знайдена у другому стовпці таблиці, тому ми вводимо a 2 в Col_index_num поле:
Нарешті ми вводимо значення в Range_lookup поля.
Важливо: саме використання цього поля відрізняє два способи використання VLOOKUP. Для використання VLOOKUP з базою даних цей остаточний параметр, Range_lookup, завжди має бути встановлено на ПОМИЛКОВИЙ, але за допомогою цього іншого використання VLOOKUP ми повинні або залишити його порожнім, або ввести значення ІСТИНА. При використанні VLOOKUP необхідно зробити правильний вибір для цього остаточного параметра.
Щоб бути явними, ми введемо значення вірно в Range_lookup поля. Також було б добре залишити його порожнім, оскільки це значення за замовчуванням:
Ми виконали всі параметри. Тепер ми натискаємо кнопку в порядку і Excel створює для нас формулу VLOOKUP:
Якщо ми експериментуємо з кількома різними загальними обсягами продажів, ми можемо переконатися, що формула працює.
Висновок
У «базі даних» версії VLOOKUP, де Range_lookup параметр ПОМИЛКОВИЙ, значення, передане в першому параметрі (Lookup_value) обов'язково бути в базі даних. Іншими словами, ми шукаємо точну відповідність.
Але в цьому іншому використанні VLOOKUP ми не обов'язково шукаємо точного відповідності. У цьому випадку «досить близько». Але що ми маємо на увазі під "досить близько"? Наведемо приклад: при пошуку ставки комісії на загальний обсяг продажів $ 34,988, наша формула VLOOKUP поверне нам значення 30%, що є правильною відповіддю. Чому він вибрав рядок у таблиці, що містить 30%? Що, власне, означає “досить близько” у цьому випадку? Давайте будемо точними:
Коли Range_lookup встановлено на ІСТИНА (або пропущено), VLOOKUP буде виглядати у стовпці 1 і збігатися найвище значення, яке не перевищує Lookup_value параметр.
Важливо також відзначити, що ця система працює, таблицю слід сортувати у порядку зростання у стовпці 1!
Якщо ви хочете попрактикуватися з VLOOKUP, зразок файлу, проілюстрований у цій статті, можна завантажити звідси.
