Общая информация о ЕСЛИ (IF)
Уровень сложности по шкале BRP ADVICE — 2 из 7. Каждая вложенная ЕСЛИ (IF) увеличивает сложность формулы вдвое.
Чтобы разобраться c функцией ЕСЛИ (IF), сначала надо разобраться с тем, что такое логические функции.
В Excel, Google Sheets, LibreOffice, OpenOffice и других табличных документах работа логических функций основана на существовании логических параметров. Логических параметров два: первый — ИСТИНА (TRUE), второй — ЛОЖЬ (FALSE).
Логические функции позволяют либо построить такое дерево решений, либо задавать вопрос и получать логический параметр. К первым относятся, например, ЕСЛИ (IF), ЕСЛИОШИБКА (IFERROR). Ко вторым — ЕЧИСЛО (ISNUMBER), И (AND), ИЛИ (OR).
Кстати, логические параметры еще называют булевыми в честь английского математика и логика Джорджа Буля.
Итак, функция ЕСЛИ (IF) позволяет построить дерево решений. У этого дерева решений есть один вопрос на входе и два варианта действий. Вопрос обязательно имеет два варианта ответа: да / нет, верно / неверно или в терминах логических параметров ИСТИНА (TRUE) / ЛОЖЬ (FALSE).
Первый аргумент функции ЕСЛИ (IF) — логический вопрос. В Excel он называется «лог_выражение». Excel, Google Sheets, LibreOffice, OpenOffice автоматически находят ответ на этот вопрос, и этот ответ должен принять значение ИСТИНА (TRUE) / ЛОЖЬ (FALSE). Что же может дать такой ответ? Самые простые варианты — это классические равенства и неравенства. Например, выражение 12=12 вернет логический параметр ИСТИНА (TRUE), а неравенство 12>40 вернет логический параметр ЛОЖЬ (FALSE).
Более сложные логические вопросы можно задать с помощью вложенных функций. В результате вычисления таких вложенных функций должен получиться тот самый логический параметр ИСТИНА (TRUE) или ЛОЖЬ (FALSE). К таким функциям относятся, например, ЕЧИСЛО (ISNUMBER), ЕТЕКСТ (ISTEXT), ЕНД (ISNA), И (AND), ИЛИ (OR), в сложных случаях — еще одна ЕСЛИ (IF).
Рассмотрим примеры применения функции ЕСЛИ (IF) с одним или несколькими условиями.
Файл-пример № 1 вы можете скачать по этой ссылке.
В конце каждого месяца формируется таблица, содержащая информацию о продажах каждого менеджера. Эта таблица может выглядеть, например, как на рисунке ниже.
Рисунок. Продажи в разрезе менеджеров по продажам за отчетный месяц
Для того чтобы автоматически заполнять столбец «Выполнение плана» и «Зарплата за месяц, руб.» (столбцы E и F соответственно), можно использовать функцию ЕСЛИ (IF).
Файл-пример № 1 вы можете скачатьпо этой ссылке.
=ЕСЛИ(D4>=1000000;”Молодец!”;”План не выполнен:(“)
=IF(D4>=1000000;”Молодец!”;”План не выполнен:(«) .
После этого ячейку можно будет скопировать вниз до конца столбца, и программа в каждой строке напишет, кто молодец, а кто не выполнил план.
1. Лог_выражение: D4>=1000000. В нашем примере логический вопрос — это сравнение фактического результата и плана продаж. D4 — это ссылка на ячейку с фактическими продажами этого менеджера. Excel, Google Sheets, LibreOffice, OpenOffice подставляют вместо D4 значение из этой ячейки и проверяют, верно ли указанное неравенство. В результате проверки в формуле получается промежуточный результат, он используется для выбора нужной ветки в дереве решений.
Кстати, «Молодец!» у нас написано в кавычках, потому что любой текст внутри формулы должен быть написан в кавычках. Исключением являются только названия функций и именованных диапазонов. В остальных случаях всегда ставьте текст в кавычки.
Здесь мы также указали текст в кавычках, потому что, если не заключать текст внутри формулы в кавычки, возникнет ошибка #ИМЯ? (#NAME?). Исключение — только названия функций и именованных диапазонов.
Во-первых, функция ЕСЛИ (IF) отвечает на логический вопрос (вычисляет первый аргумент). Во-вторых, идет к соответствующей ветке дерева решений. По Александрову П.Ф. получается так:
2. Идем в аргумент Значение_если_истина. Нужно просто подставить текст «Молодец!». Указываем текст в ячейке. Конец расчетов.
Схематически расчеты выглядят, как на рисунке ниже.
Рисунок. Как работает функция ЕСЛИ (IF), когда логическое выражение возвращает ИСТИНА (TRUE)
1. D5>=1000000, следовательно проверяем 848880>=1000000, выражение не верно, значит логический параметр — ЛОЖЬ (FALSE).
Аргумент Значение_если_истина в этом случае функция ЕСЛИ (IF) игнорирует.
А вот этот текст — это ссылка на скачивание примера в Excel 2010-2013. Хотите решить пример онлайн? Оставьте заявку, мы уже работаем над этим.
Пример 1.2 — вычисление разных формул при помощи ЕСЛИ (IF)
В столбце «Зарплата за месяц, руб.» в ячейке F4 используем вот такую формулу:
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
Не забывайте, в некоторых версиях Excel, вместо «;» должна использоваться «,».
Что именно делает функция ЕСЛИ (IF) в этом примере?
1. D4>=1000000, следовательно проверяем 1000329>=1000000, выражение верно, значит логический параметр — это ИСТИНА (TRUE).
Аргумент Значение_если_ложь в этом случае функция ЕСЛИ (IF) игнорирует.
1. D5>=1000000, следовательно проверяем 848880>=1000000, выражение не верно, значит логический параметр — ЛОЖЬ (FALSE).
Аргумент Значение_если_истина в этом случае функция ЕСЛИ (IF) игнорирует.
Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой «Начать заново» на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.
Пример 2 — разные условия в логическом выражении
В прошлом примере и у менеджеров, и у старших менеджеров был одинаковый план продаж на месяц. Усложним задачу: установим повышенный план старшим менеджерам — 1 миллион 200 тысяч в месяц. Отчет тогда будет выглядеть, как на рисунке ниже.
Рисунок. Отчет по результатам работы менеджеров и старших менеджеров
=ЕСЛИ(ЕСЛИ(C4=”Старший менеджер”;D4>=1200000;D4>=1000000);”Молодец!”;”План не выполнен:(“)
=IF(IF(C4=”Старший менеджер”;D4>=1200000;D4>=1000000);”Молодец!”;”План не выполнен:(«) .
Что именно делает функция ЕСЛИ (IF) в этом примере?
1. Функция ЕСЛИ (IF) начинает расчет с логического выражения и видит там вложенную функцию ЕСЛИ (IF). Excel, Google Sheets, LibreOffice, OpenOffice сначала считает вложенную функцию.
3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_ложь и сравнивает фактические продажи с планом менеджера по продажам. 1 000 329 больше 1 000 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ИСТИНА (TRUE).
Формула с несколькими условиями, то есть с вложенными функциями ЕСЛИ (IF), возвращает в ячейку текст «Молодец!».
По Ильину М.А. получается так:
2. Вложенная функция ЕСЛИ (IF) проверяет логическое выражение: должность менеджера старший менеджер или нет. Ильин М.А. — это не старший менеджер. Поэтому логическое выражение во вложенной ЕСЛИ (IF) возвращает ЛОЖЬ (FALSE).
4. Результат вычисления вложенной функции ЕСЛИ (IF) передается в основную функцию. Основная функция ЕСЛИ (IF) видит логический параметр ЛОЖЬ (FALSE) и переходит к своему (а не к вложенному) аргументу Значение_если_ложь. Этот аргумент — просто текст «План не выполнен:(».
Посмотрите построенное дерево решений на схеме ниже.
Рисунок. Дерево решений для функции ЕСЛИ (IF) с несколькими условиями
1. Функция ЕСЛИ (IF) начинает расчет с логического выражения и видит там вложенную функцию ЕСЛИ (IF). Excel, Google Sheets, LibreOffice, OpenOffice сначала считает вложенную функцию.
3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_истина и сравнивает фактические продажи с планом старшего менеджера по продажам. 1 204 346 больше 1 200 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ИСТИНА (TRUE).
Формула с несколькими условиями, то есть с вложенными функциями ЕСЛИ (IF), возвращает в ячейку текст «Молодец!».
По Соколовой Н.И. получается так:
2. Вложенная функция ЕСЛИ (IF) проверяет логическое выражение: должность менеджера старший менеджер или нет. Соколова Н.И. — это старший менеджер. Поэтому логическое выражение во вложенной ЕСЛИ (IF) возвращает ИСТИНА (TRUE).
4. Результат вычисления вложенной функции ЕСЛИ (IF) передается в основную функцию. Основная функция ЕСЛИ (IF) видит логический параметр ЛОЖЬ (FALSE) и переходит к своему (а не к вложенному) аргументу Значение_если_ложь. Этот аргумент — просто текст «План не выполнен:(».
Посмотрите построенное дерево решений на схеме ниже.
Рисунок. Дерево решений для функции ЕСЛИ (IF) с несколькими условиями
В столбце «Зарплата за месяц, руб.» в ячейке F4 используем вот такую формулу:
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
Не забудьте, в некоторых версиях Excel, вместо «;» должна использоваться «,».
Пример 4 — разные условия и в логическом выражении, и в ветках дерева решений
Итак, у нас есть менеджеры, есть старшие менеджеры. У старших менеджеров план выше, чем у обычных менеджеров. Для того чтобы такая модель работала, часто необходимо дополнительное стимулирование для старших менеджеров. Например, премия старшего менеджера повышается до 6%. То есть у нас сразу несколько условий:
2. Если должность старший менеджер, план — 1 миллион 200 тысяч, иначе — 1 миллион.
В итоге получается отчет, как на рисунке ниже.
Рисунок. Отчет по результатам работы менеджеров и старших менеджеров
В ячейке F4 можно написать такую формулу:
ЕСЛИ(C4=”Старший менеджер”;D4>=1200000;D4>=1000000);
20000)
=IF(
20000+D4*IF(C4=”Старший менеджер«;6;5)/100;
Не забывайте, в некоторых версиях Excel, вместо «;» должна использоваться «,».
На рисунке ниже схематически изображено построенное дерево решений.
Рисунок. Пример дерева решений с несколькими условиями и в логическом выражении, и в других аргументах функции ЕСЛИ (IF)
1. Для функции ЕСЛИ (IF) всегда должен быть указан первый аргумент — логическое выражение и второй аргумент — значение если истина. Третий аргумент необязательный. Пользователи часто забывают указать третий аргумент особенно при работе со сложными формулами, из-за этого в некоторых случаях вместо нужного результата в ячейке появляется логический параметр ЛОЖЬ (FALSE).
3. В сложных формулах с ЕСЛИ (IF) очень тяжело отслеживать правильность расчетов: каждая вложенная функция ЕСЛИ (IF) добавляет в ваше дерево решений один вопрос и минимум две ветки. В среднем человек в уме держит до 7 объектов, получается, что при трех вложенных ЕСЛИ (IF) в уме нужно держать 3 вопроса и 6 веток дерева решений. Контролируемость и надежность формулы стремительно снижается.
Совет: работа со сложными формулами
1. Определите конечную цель ваших расчетов: какой результат вы должны получить в итоге.
3. Начинайте создание формулы с этой функции, укажите ее и переходите к работе с аргументами.
5. Если вам необходимо проделать промежуточные вычисления, то определяете конечную цель этих вычислений, функцию и так далее. Обычно, задача промежуточных вычислений — это получить аргумент для основной функции. Помните об этом, так как иногда нужно получить аргумент определенного типа (именно текст, именно число, именно логический параметр или что-то иное).
И помните, если формула слишком сложная, лучше сделать промежуточный расчет в соседней ячейке.
Вместо констант в формуле можно использовать именованные диапазоны.
Функция ЕСЛИ (IF) иногда может быть заменена на функцию ВПР (VLOOKUP), ГПР (HLOOKUP), ПРОСМОТР(LOOKUP), ЕСЛИОШИБКА (IFERROR), СУММЕСЛИ (SUMIF) или СЧЁТЕСЛИ (COUNTIF).
Файл-пример № 1 «Применение функции ЕСЛИ (IF) с одним условием» вы можете скачать по этой ссылке.
Файл-пример № 3 «Применение функции ЕСЛИ (IF) с несколькими условиями в разных аргументах» вы можете скачать по этой ссылке.
Понравилась статья? Узнайте больше раньше других: заходите на нашу страницу в ВКонтакте и подписывайтесь на новости.
Ваш Виктор Рыбцев
