Давним-давно я працював у віце-президента з продажів у компанії. Я завжди моделював якусь нову бонусну програму або план комісій. Я вже досить звик вводити плани в будь-які умови. Той, що показаний у цій підказці, досить ручний.
Звичайним підходом є початок побудови вкладеної формули IF. Ви завжди починаєте з верхнього або нижнього кінця діапазону. “Якщо продажі перевищують 500 тис. Доларів, то знижка становить 20%; інакше, … " Третім аргументом функції IF є цілком нова функція IF, яка перевіряє другий рівень: "Якщо обсяг продажів перевищує 250 тис. Доларів, тоді знижка становить 15%; в іншому випадку, …"
Ці формули стають довшими і довшими, оскільки рівнів більше. Найскладнішою частиною такої формули є запам'ятовування кількості заключних дужок у кінці формули.
Якщо ви використовуєте Excel 2003, ваша формула вже наближається до межі. З цією версією ви не можете вкласти більше 7 функцій IF. Це був потворний день, коли повноваження, які повинні були змінити план комісії, і вам потрібен був спосіб додати восьму функцію IF. Сьогодні ви можете вкласти 64 функції IF. Ви ніколи не повинні гніздитися так багато, але приємно знати, що немає проблем вкласти 8 або 9.
Замість того, щоб використовувати вкладену функцію IF, спробуйте скористатися функцією VLOOKUP. Коли четвертий аргумент VLOOKUP змінюється з False на True, функція більше не шукає точного збігу. Ну, спочатку VLOOKUP намагається знайти точну відповідність. Але якщо точного збігу не знайдено, Excel осідає в рядку трохи менше того, що ви шукаєте.
Розглянемо таблицю нижче. У клітинці C13 Excel шукатиме в таблиці відповідності на суму 28 355 доларів. Коли не вдається знайти 28355, Excel поверне знижку, пов’язану із значенням, яке є просто меншим - у цьому випадку знижка 1% для рівня 10 тис. Доларів.
Коли ви перетворюєте правила в таблицю в E13: F18, вам потрібно починати з найменшого рівня і переходити до найвищого рівня. Хоча це не було зазначено в правилах, якщо хтось продає менше 10 000 доларів, знижка становитиме 0%. Вам потрібно додати це як перший рядок у таблиці.
Обережно
Коли ви використовуєте «Справжню» версію VLOOKUP, вашу таблицю потрібно сортувати за зростанням. Багато людей вважають, що всі таблиці пошуку потрібно сортувати. Але таблицю потрібно сортувати лише за приблизним збігом.
Що робити, якщо ваш менеджер хоче повністю автономну формулу і не хоче бачити таблицю бонусів праворуч? Побудувавши формулу, ви можете вбудувати таблицю прямо у формулу. Переведіть формулу в режим редагування, двічі клацнувши клітинку або виділивши комірку та натиснувши F2. За допомогою курсору виберіть весь другий аргумент: $ E $ 13: $ F $ 18.
Натисніть клавішу F9. Excel вбудовує таблицю пошуку як константу масиву. У константі масиву крапка з комою позначає новий рядок, а кома - новий стовпець. Див. Розуміння констант масиву.
Натисніть клавішу Enter. Скопіюйте формулу в інші комірки.
Тепер ви можете видалити таблицю. Остаточна формула наведена нижче.
Дякую Майку Гірвіну за те, що він навчив мене відповідних дужок. Техніка VLOOKUP була запропонована Денні Маком, Боряною Петровою, Андреасом Техосом та @mvmcos.
