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