Сложные функции в Excel

Общая информация о ЕСЛИ (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) с несколькими условиями

3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_истина и сравнивает фактические продажи с планом старшего менеджера по продажам. 1 204 346 больше 1 200 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ИСТИНА (TRUE).

По Соколовой Н.И. получается так:

2. Вложенная функция ЕСЛИ (IF) проверяет логическое выражение: должность менеджера старший менеджер или нет. Соколова Н.И. — это старший менеджер. Поэтому логическое выражение во вложенной ЕСЛИ (IF) возвращает ИСТИНА (TRUE).

Посмотрите построенное дерево решений на схеме ниже.

Рисунок. Дерево решений для функции ЕСЛИ (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) с несколькими условиями в разных аргументах» вы можете скачать по этой ссылке.

Понравилась статья? Узнайте больше раньше других: заходите на нашу страницу в ВКонтакте и подписывайтесь на новости.

Ваш Виктор Рыбцев