У вас есть формула Excel, которая содержит несколько функций ЕСЛИ? Когда вы дойдете до сути со слишком большим количеством вложенных операторов IF, вам нужно посмотреть, станет ли все это проще с простой функцией VLOOKUP. Я видел, как формулы из 1000 символов были упрощены до формулы VLOOKUP из 30 символов. Его легко поддерживать, когда вам нужно будет добавить новые значения позже.
Давным-давно я работал вице-президентом по продажам на работе. Я всегда моделировал новую бонусную программу или комиссионный план. Я привык заказывать планы со всевозможными условиями. Тот, что ниже, довольно ручной.
Обычный подход - начать построение вложенной формулы ЕСЛИ. Вы всегда начинаете либо с верхнего, либо с нижнего предела диапазона. «Если продажи превышают 500 тысяч долларов, то скидка составляет 20%; в противном случае… ." Третий аргумент функции ЕСЛИ - это совершенно новая функция ЕСЛИ, которая проверяет второй уровень: если продажи превышают 250 тыс. Долларов, то скидка составляет 15%; в противном случае,… "
Эти формулы становятся длиннее и длиннее по мере увеличения количества уровней. Самая сложная часть такой формулы - это запомнить, сколько закрывающих скобок нужно поставить в конце формулы.
Мини-бонусный совет
Соответствие круглых скобок
Excel циклически перебирает различные цвета для каждого нового уровня скобок. Хотя Excel повторно использует цвета, он использует черный только для открывающей круглой скобки и для соответствующей закрывающей скобки. Завершая формулу ниже, просто продолжайте вводить закрывающие скобки, пока не наберете черную скобку.
Вернуться к подсказке вложенных формул
Если вы используете Excel 2003, ваша формула уже приближается к пределу. Тогда вы не могли вложить более 7 функций ЕСЛИ. Это был ужасный день, когда власть имущие изменили план комиссионных, и вам понадобился способ добавить восьмую функцию IF. Сегодня вы можете вложить 64 функции ЕСЛИ. Вы никогда не должны этого делать, но приятно знать, что с вложением 8 или 9 проблем нет.
Вместо того, чтобы использовать вложенную функцию ЕСЛИ, попробуйте использовать необычное использование функции ВПР. Когда четвертый аргумент VLOOKUP изменяется с False на True, функция больше не ищет точное совпадение.
Ну, сначала VLOOKUP пытается найти точное совпадение. Но если точное совпадение не найдено, Excel помещается в строку чуть меньше той, которую вы ищете.
Рассмотрим таблицу ниже. В ячейке C13 Excel будет искать совпадение на сумму 28 355 долларов в таблице. Если не удается найти 28355, Excel вернет скидку, связанную с меньшим значением. В этом случае скидка 1% для уровня 10 тысяч долларов.
Когда вы конвертируете правила в таблицу в E13: F18, вам нужно начинать с наименьшего уровня и переходить к высшему уровню. Хотя в правилах это не оговорено, если у кого-то продажи ниже 10 000 долларов, скидка составит 0%. Вам нужно добавить это как первую строку в таблице.
Осторожно
Когда вы используете «Истинную» версию ВПР, ваша таблица должна быть отсортирована по возрастанию. Многие люди считают, что все таблицы поиска нужно отсортировать. Но сортировать таблицу нужно только в случае приблизительного совпадения.
Что, если ваш менеджер хочет полностью автономную формулу и не хочет видеть таблицу бонусов справа? После построения формулы вы можете встроить таблицу прямо в формулу.
Переведите формулу в режим редактирования, дважды щелкнув ячейку или выбрав ячейку и нажав F2.
Используя курсор, выберите весь второй аргумент: $ E $ 13: $ F $ 18.
Нажмите клавишу F9. Excel встроит таблицу поиска как константу массива. В константе массива точка с запятой указывает на новую строку, а запятая указывает на новый столбец.
Нажмите Ввод. Скопируйте формулу в другие ячейки.
Теперь вы можете удалить таблицу. Окончательная формула показана ниже.
Спасибо Майку Гирвину за то, что научил меня подходящим скобкам. Технику ВПР предложили Дэнни Мак, Бориана Петрова, Андреас Техос и @mvmcos.
Смотреть видео
- В многоуровневой программе комиссионных, бонусов или скидок вам часто приходится вкладывать свои функции IF
- Предел Excel 2003 составлял 7 вложенных операторов IF.
- Теперь вы можете вложить 32, но я не думаю, что вам когда-нибудь понадобится 32
- Когда вы когда-нибудь использовали бы версию ВПР с приблизительным соответствием? Это время.
- Переведите дисконтную программу в справочную таблицу
- ВПР в большинстве случаев не дает ответа.
- Установка True в конце скажет ВПР найти значение чуть меньше.
- Это единственный раз, когда нужно отсортировать таблицу ВПР.
- Не хотите, чтобы таблица ВПР была отложена в сторону? Вставьте его в формулу.
- F2 для редактирования формулы. Выберите таблицу поиска. Нажмите F9. Войти.
Стенограмма видео
Изучите Excel по подкасту, выпуск 2030 - Вложенный IF заменен на ВПР!
Я буду подкастировать всю эту книгу, нажмите «i» в правом верхнем углу, чтобы перейти к списку воспроизведения!
Привет, добро пожаловать обратно в сеть, я Билл Джелен. Хорошо, это действительно обычное дело для программы комиссионных, дисконтной программы или бонусной программы, где у нас есть уровни, разные уровни, верно? Если вы больше 10000 долларов, вы получите скидку 1%, 50000 долларов - 5%. Вы должны быть осторожны при построении этого вложенного оператора IF: вы начинаете его с верхнего конца, если вы ищете больше, или с нижнего конца, если вы ищете меньше. Итак, B10> 50000, 20%, иначе другое IF, B10> 250000, 25% и так далее и так далее. Это просто длинная и сложная формула, и если ваша таблица в Excel 2003 была больше, вы не могли вложить более 7 операторов IF, мы почти приблизились к пределу. Вы можете перейти на 32 сейчас, я не думаю, что вам когда-либо стоит переходить на 32, и даже если вы кричите: «Эй, погоди,как насчет новой функции, которая только что появилась в Excel 2016, выпуске от февраля 2016 года, с функцией IFS? » Да, конечно, здесь меньше скобок, и 87 символов вместо 97, это все равно беспорядок, давайте избавимся от этого и сделаем это с помощью ВПР!
Хорошо, вот шаги, вы принимаете эти правила и переворачиваете их с ног на голову. Первое, что мы должны сказать, если у вас было 0 долларов продаж, вы получаете скидку 0%, если у вас было 10000 долларов в продажах, вы получаете скидку 1%, если у вас 50000 долларов в продажах, вы получаете скидку 5%. . 100000 долларов, скидка 10%, 250000 долларов, скидка 15%, и, наконец, все, что превышает 500000 долларов, получает скидку 20%, хорошо. Сейчас много раз, каждый раз, когда я говорю о ВПР, я говорю, что каждая ВПР, которую вы когда-либо создавали, будет заканчиваться ЛОЖНОЙ, и люди будут говорить: «Ну, подождите секунду, а какая ИСТИННАЯ версия там вообще?» Это тот самый случай, когда мы ищем диапазон, поэтому мы ищем это значение, обычная ВПР, конечно,, 2, FALSE не найдет 550000, вернет # N / A!Итак, в этом очень частном случае мы собираемся изменить это ЛОЖЬ на ИСТИНУ, и это скажет Excel: «Иди поищи 550000, если ты не можешь его найти, дайте нам значение, которое просто меньше». Так что это дает нам 20%, это то, что он делает, хорошо? И это единственный раз, когда ваша таблица VLOOKUP должна быть отсортирована, все остальные разы с FALSE, это может быть как угодно. И да, вы можете оставить, ИСТИНА выключенным, но я всегда помещаю его туда, чтобы напомнить себе, что это одна из тех странных невероятно опасных ВПР, и я не хочу копировать эту формулу где-нибудь еще. Хорошо, мы скопируем и вставим специальные формулы, хорошо.хорошо? И это единственный раз, когда ваша таблица VLOOKUP должна быть отсортирована, все остальные разы с FALSE, это может быть как угодно. И да, вы можете оставить, ИСТИНА выключенным, но я всегда помещаю его туда, чтобы напомнить себе, что это одна из тех странных невероятно опасных ВПР, и я не хочу копировать эту формулу где-нибудь еще. Хорошо, мы скопируем и вставим специальные формулы, хорошо.хорошо? И это единственный раз, когда ваша таблица VLOOKUP должна быть отсортирована, все остальные разы с FALSE, это может быть как угодно. И да, вы можете оставить, ИСТИНА выключенным, но я всегда помещаю его туда, чтобы напомнить себе, что это одна из тех странных невероятно опасных ВПР, и я не хочу копировать эту формулу где-нибудь еще. Хорошо, мы скопируем и вставим специальные формулы, хорошо.
Следующая жалоба: ваш менеджер не хочет видеть справочную таблицу, он хочет ее «Просто избавьтесь от этой справочной таблицы». Хорошо, мы можем сделать это, встроив таблицу поиска, проверьте это, отличный трюк, который я получил от Майка Гирвина из ExcelIsFun. F2, чтобы перевести формулу в режим редактирования, а затем очень осторожно выбрать только диапазон для таблицы поиска. Нажмите F9, и он берет эту таблицу и помещает ее в номенклатуру массива, а затем я просто нажимаю Enter вот так. Скопируйте это, вставьте специальные формулы, а затем я могу избавиться от таблицы поиска, которая просто продолжает работать вниз по строке. Это огромная проблема, если вам нужно вернуться и отредактировать это, вы должны действительно взглянуть на это с большой ясностью. Запятая означает переход к следующему столбцу, точка с запятой означает переход к следующей строке, хорошо,но теоретически вы можете вернуться туда и изменить эту формулу, если изменится одно из номеров цепочки.
Хорошо, поэтому использование ВПР вместо вложенного оператора IF - это совет № 32 на нашем пути к 40 «40 величайших советов по Excel за все время», вы можете получить всю эту книгу. Нажмите на букву «i» в правом верхнем углу, 10 долларов за электронную книгу, 25 долларов за печатную книгу, хорошо. Итак, сегодня мы пытаемся решить многоуровневую комиссию, бонус или программу скидок. Вложенные ЕСЛИ действительно распространены, будьте осторожны, 7 - это все, что у вас может быть в Excel 2003, сегодня у вас может быть 32, но никогда не должно быть 32. Итак, когда использовать приблизительную версию MATCH для ВПР, вот и все. Возьмите эту программу скидок, переверните ее вверх дном, превратите в поисковую таблицу, начиная с наименьшего числа и заканчивая наибольшим числом. ВПР, конечно, не найдет ответа, но, изменив ВПР на ИСТИНА в конце, он скажет ему найти значение чуть меньше,это единственный раз, когда таблица должна быть отсортирована. Если вы не хотите видеть эту таблицу там, вы можете встроить ее в формулу, используя трюк Майка Гирвина, F2, чтобы отредактировать формулу, выбрать таблицу поиска, нажать F9, а затем Enter.
Хорошо, эй, я хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз на другом сетевом трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2030.xlsx
