Вставить значение если оно отвечает условию. Как в excel при помощи вложенных функций если() рассчитать бонус с продаж. Логическая операция ИЛИ()

СИНТАКСИС:
=ЕСЛИ(лог_выражение ;значение_если_ИСТИНА ;значение_если_ЛОЖЬ )

АРГУМЕНТЫ:
1. лог_выражение - любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.
2. значение_если_ИСТИНА - значение, которое возвращается если лог_выражение = ИСТИНА. Оно может быть числом, текстом, формулой или ссылкой на ячейку ().
3. значение_если_ЛОЖЬ - значение, которое возвращается если лог_выражение = ЛОЖЬ. Оно может быть числом, текстом, формулой или ссылкой на ячейку (в формулах массива допустимо использовать так же ссылки на диапазон ячеек ).

КОММЕНТАРИИ:
Если аргумент лог_выражение = ИСТИНА, но аргумент значение_если_ИСТИНА
Если аргумент лог_выражение = ЛОЖЬ, но аргумент значение_если_ЛОЖЬ не указан, то возвращается значение 0.

ОГРАНИЧЕНИЯ:
Допускается до 7 вложений функции ЕСЛИ друг в друга в качестве любого из аргументов в Excel версий до 2003 включительно, до 64 вложений в Excel версии 2007 и до 128 вложений в Excel версии 2010 .

ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ:

ФОРМУЛЫ МАССИВА:
Функция работает в формулах массива

ЭТОГО НЕТ В ОФИЦИАЛЬНОЙ СПРАВКЕ EXCEL:
Аргумент лог_выражение может и не быть выражением. Это может быть просто текст, число или ссылка на ячейку содержащую их.
Если аргумент лог_выражение является текстом, то Вы получите значение ошибки #ЗНАЧ , если числом, то при значении 0 функция ЕСЛИ вернёт ЛОЖЬ, при любом другом значении - ИСТИНА.
Если не указывать аргументы значение_если_ИСТИНА или значение_если_ЛОЖЬ, то при выполнении условий будут возвращаться текстовые значения ИСТИНА или ЛОЖЬ.

В КАКОЙ ВЕРСИИ EXCEL РАБОТАЕТ:
В любой версии.

Здравствуйте, друзья! Часто ли вам приходится делать выбор? Например, захотели купить новый телефон, а полной суммы денег у вас нет. Взять кредит или копить? Так сегодня разберем как делать выбор в электронных таблицах Excel. Это позволяет сделать условная функция ЕСЛИ().

Условная функция ЕСЛИ()

В этой функции проверяется истинность одного или нескольких логических выражений и выбирается дальнейшее действие. В логических выражениях используют знаки операций отношения между сравниваемыми величинами.

Операции отношения в логических выражениях

Простое условие

Что же делает функция ЕСЛИ()? Посмотрите на схему. Здесь приведен простой пример работы функции при определении знака числа а .

Блок-схема "Простое условие". Определение отрицательных и неотрицательных чисел

Условие а>=0 определяет два возможных варианта: неотрицательное число (ноль или положительное) и отрицательное. Ниже схемы приведена запись формулы в Excel. После условия через точку с запятой перечисляются варианты действий. В случае истинности условия, в ячейке отобразится текст "неотрицательное", иначе - "отрицательное". То есть запись, соответствующая ветви схемы «Да», а следом – «Нет».

Текстовые данные в формуле заключаются в кавычки, а формулы и числа записывают без них.

Если результатом должны быть данные, полученные в результате вычислений, то смотрим следующий пример. Выполним увеличение неотрицательного числа на 10, а отрицательное оставим без изменений.

Блок-схема "Простое условие". Расчет данных

На схеме видно, что при выполнении условия число увеличивается на десять, и в формуле Excel записывается расчетное выражение А1+10 (выделено зеленым цветом). В противном случае число не меняется, и здесь расчетное выражение состоит только из обозначения самого числа А1 (выделено красным цветом).

Это была краткая вводная часть для начинающих, которые только начали постигать азы Excel. А теперь давайте рассмотрим более серьезный пример с использованием условной функции.

Задание:
Процентная ставка прогрессивного налога зависит от дохода. Если доход предприятия больше определенной суммы, то ставка налога выше. Используя функцию ЕСЛИ, рассчитайте сумму налога.

Решение:

Решение данной задачи видно на рисунке ниже. Но внесем все-таки ясность в эту иллюстрацию. Основные исходные данные для решения этой задачи находятся в столбцах А и В. В ячейке А5 указано пограничное значение дохода при котором изменяется ставка налогообложения. Соответствующие ставки указаны в ячейках В5 и В6. Доход фирм указан в диапазоне ячеек В9:В14. Формула расчета налога записывается в ячейку С9: =ЕСЛИ(B9>A$5;B9*B$6;B9*B$5) . Эту формулу нужно скопировать в нижние ячейки (выделено желтым цветом).

В расчетной формуле адреса ячеек записаны в виде A$5, B$6, B$5. Знак доллара делает фиксированной часть адреса, перед которой он установлен, при копировании формулы. Здесь установлен запрет на изменение номера строки в адресе ячейки.

Составное условие

Составное условие состоит из простых, связанных логическими операциями И() и ИЛИ().

И() - логическая операция, требующая одновременного выполнения всех условий, связанных ею.
ИЛИ() - логическая операция, требующая выполнения любого из перечисленных условий, связанных ею.

Логическая операция И()

Например: Рассмотрим электронную таблицу "Ведомость сдачи вступительных экзаменов". Для зачисления абитуриента в ВУЗ, ему необходимо преодолеть проходной балл, и по математике отметка должна быть выше 70 баллов.
Посмотрите внимательно на рисунок ниже.

В этом примере функция ЕСЛИ() использует составное условие, связанное логической операцией И() . Обратите внимание: абитуриент Петров не зачислен, хотя сумма его баллов равна проходному.

Почему так произошло? Посмотрим внимательно на условие в нашей формуле =ЕСЛИ(И(E6>=D2;B6>70);"зачислен";"не зачислен") . Логическая операция И() требует выполнения всех условий, но у нас выполняется только одно. Второе условие B6>70 не выполнено, поэтому составное условие принимает значение «ложь». И на экран выводится сообщение «не зачислен» (вспоминаем схему – ветвь «нет»).

Задание:
В торговой фирме перед Новым Годом устроена праздничная распродажа. Рассчитать сумму продаж с учетом скидки, назначаемой в период распродажи.

2. Определите скидку (в процентах), используя функцию ЕСЛИ(). Если дата продажи попадает в период праздничной распродажи, то назначается скидка, иначе скидка равняется нулю. При задании условий используйте логическую функцию И().

3. Определите сумму продажи с учетом скидки. Сумма продажи с учетом скидки = Итого* (1- Скидка%).

Решение:

• В ячейке Е7: =B7*C7
• В ячейке F7: =ЕСЛИ(И(D7>=D$4;D7<=E$4);B$4;0)
• В ячейке G7: =E7*(1-F7)

и скопировать по соответствующим столбцам до 16 строки включительно.

Логическая операция ИЛИ()

Посмотрим пример с логической операцией ИЛИ() в той же таблице. Только немного изменим условие задачи. Для зачисления абитуриента достаточно иметь по математике более 60 баллов или общую сумму баллов не меньше проходного. Рисунок ниже.

Абитуриент Сидоров зачислен, хотя не набрал проходной балл. Вот формула =ЕСЛИ(ИЛИ(B7>60;E7>D2;);"зачислен";"не зачислен"). Здесь использована операция ИЛИ() , поэтому достаточно выполнение хотя бы одного условия. Что и произошло, первое условие B7>60 истинно. Оно привело к выводу сообщения о зачислении абитуриента.

Задание:
В торговой фирме установлены дни распродаж - последние числа месяца. Рассчитать сумму продаж с учетом скидки, назначаемой в дни распродажи.

1. Рассчитайте общую стоимость продаж. Итого = Стоимость* Количество.

2. Определите скидку (в процентах), используя функцию ЕСЛИ(). Если дата продажи совпадает с датами распродаж, то назначается скидка, иначе скидка равняется нулю. При задании условий используйте логическую функцию ИЛИ().

3. Определите сумму продажи с учетом скидки. Сумма продажи с учетом скидки = Итого* (1- Скидка%)

Решение:

Для проведения расчетов необходимо вписать следующие формулы:

• В ячейке Е7: =B7*C7
• В ячейке F7: =ЕСЛИ(ИЛИ(D7=D$4;D7=E$4;D7=F$4);B$4;0)
• В ячейке G7: =E7*(1-F7)

и скопировать по соответствующим столбцам до 15 строки включительно.

Вложенные условия

Если для решения задачи недостаточно использования составных условий, то тогда применяют более сложную конструкцию вложенных условий. Смотрите схему.

В данном примере вторая функция ЕСЛИ() является вложенной и записывается на месте действия, которое вызывается при не выполнении условия первой функции. В Excel последних версий допускается делать до 64 вложений.

Задание:
С целью уменьшения текучести кадров администрация решила выплачивать надбавку за непрерывный стаж работы на своем предприятии. Рассчитайте надбавку за непрерывный стаж работы в соответствии с таблицей Надбавка.

1. Определите стаж работы сотрудников. Стаж = 2018- Год приема на работу.

2. Используя вложенные функции Если, рассчитайте надбавку для сотрудников. Надбавка (руб.) = Надбавка(%)* Оклад

Решение:

Для проведения расчетов необходимо вписать следующие формулы:

• В ячейке D9: =2018-B9
• В ячейке E9: =ЕСЛИ(D9>=B$6;C9*C$6;ЕСЛИ(D9>=B$5;C9*C$5;0))

и скопировать по соответствующим столбцам до 19 строки включительно.

В этой статье я постарался максимально подробно и доходчиво объяснить применение условной функции в Excel. Конечно же, в Excel есть и другие функции с условием, но о них поговорим в следующей статье. Друзья, если вам была интересна и полезна данная информация, не забывайте делиться ею в социальных сетях. А также буду рад прочитать ваши комментарии.

PS: Удивительные факты

Дорогой читатель! Вы посмотрели статью до конца.
Получили вы ответ на свой вопрос? Напишите в комментариях пару слов.
Если ответа не нашли, укажите что искали .

Общая информация о ЕСЛИ (IF)

Функция ЕСЛИ - это одна из самых популярных в Excel функций. В англоязычном Excel, а также в Google Sheets, LibreOffice, OpenOffice, эта функция называется IF. ЕСЛИ (IF) относится к логическим функциям.

Уровень сложности по шкале BRP ADVICE - 2 из 7 . Каждая вложенная ЕСЛИ (IF) увеличивает сложность формулы вдвое.

ЕСЛИ (IF) позволяет построить дерево решений, то есть при выполнении условия выполнять одно действие, а при невыполнении - другое. При этом условие должно быть вопросом, имеющим варианты ответа «да / нет» или «верно / неверно» (в терминах Excel, Google Sheets, LibreOffice, OpenOffice это «ИСТИНА / ЛОЖЬ» («TRUE / FALSE»).

Чтобы разобраться c функцией ЕСЛИ (IF), сначала надо разобраться с тем, что такое логические функции.

Что такое логические функции

В Excel, Google Sheets, LibreOffice, OpenOffice и других табличных документах работа логических функций основана на существовании логических параметров. Логических параметров два: первый - ИСТИНА (TRUE), второй - ЛОЖЬ (FALSE).

На основе использования этих логических параметров можно построить дерево решений. В простейшем варианте этого дерева будет задан вопрос, ответом на который может быть ИСТИНА (TRUE) или ЛОЖЬ (FALSE), и дано указание, что делать в каждом из этих двух случаев. Схематически такое дерево решений изображено на рисунке ниже.

Рисунок. Простейшее дерево решений

Логические функции позволяют либо построить такое дерево решений, либо задавать вопрос и получать логический параметр. К первым относятся, например, ЕСЛИ (IF), ЕСЛИОШИБКА (IFERROR). Ко вторым - ЕЧИСЛО (ISNUMBER), И (AND), ИЛИ (OR).

Excel, Google Sheets, LibreOffice, OpenOffice и большинство других программных продуктов позволяет использовать логические параметры ИСТИНА (TRUE) и ЛОЖЬ (FALSE) при выполнении математических операций. Чаще всего, ИСТИНА (TRUE) принимает значение 1, ЛОЖЬ (FALSE) принимает значение 0. Хотя иногда ИСТИНА (TRUE) и ЛОЖЬ (FALSE) принимают другие значения, например, при программировании в VBA ИСТИНА (TRUE) - это -1, а не 1.

Кстати, логические параметры еще называют булевыми в честь английского математика и логика Джорджа Буля.

Функция ЕСЛИ (IF)

Итак, функция ЕСЛИ (IF) позволяет построить дерево решений. У этого дерева решений есть один вопрос на входе и два варианта действий. Вопрос обязательно имеет два варианта ответа: да / нет, верно / неверно или в терминах логических параметров ИСТИНА (TRUE) / ЛОЖЬ (FALSE).

Вопрос и два варианта действий - это и есть три аргумента функции ЕСЛИ (IF).

Первый аргумент функции ЕСЛИ (IF) - логический вопрос. В Excel он называется «лог_выражение». Excel, Google Sheets, LibreOffice, OpenOffice автоматически находят ответ на этот вопрос, и этот ответ должен принять значение ИСТИНА (TRUE) / ЛОЖЬ (FALSE). Что же может дать такой ответ? Самые простые варианты - это классические равенства и неравенства. Например, выражение 12=12 вернет логический параметр ИСТИНА (TRUE), а неравенство 12>40 вернет логический параметр ЛОЖЬ (FALSE).

В логическом вопросе можно использовать равенства (левая и правая часть сравниваются при помощи знака «=»), неравенства (больше - «>», меньше - «<», больше или равно - «>=», меньше или равно «<=»), а также просто не равно - «<>».

Более сложные логические вопросы можно задать с помощью вложенных функций. В результате вычисления таких вложенных функций должен получиться тот самый логический параметр ИСТИНА (TRUE) или ЛОЖЬ (FALSE). К таким функциям относятся, например, ЕЧИСЛО (ISNUMBER), ЕТЕКСТ (ISTEXT), ЕНД (ISNA), И (AND), ИЛИ (OR), в сложных случаях - еще одна ЕСЛИ (IF).

Второй и третий аргумент - это функция ЕСЛИ (IF) должна сделать, когда ответ на вопрос ИСТИНА (TRUE), а когда ЛОЖЬ (FALSE). Функция ЕСЛИ (IF) вычисляет либо только второй аргумент (если ИСТИНА (TRUE)), либо только третий аргумент (если ЛОЖЬ (FALSE)).

Рассмотрим примеры применения функции ЕСЛИ (IF) с одним или несколькими условиями.

Применение ЕСЛИ (IF) с одним условием

Файл-пример №1 вы можете скачать .

Предположим, в компании установлен план по продажам: каждый менеджер должен продать не менее чем на 1 миллион рублей в месяц. Оклад менеджера по продажам составляет 20 тысяч рублей. При выполнении плана менеджер получает оклад и премию 5% от фактического объема продаж. При невыполнении плана продаж - только оклад.

В конце каждого месяца формируется таблица, содержащая информацию о продажах каждого менеджера. Эта таблица может выглядеть, например, как на рисунке ниже.

Рисунок. Продажи в разрезе менеджеров по продажам за отчетный месяц

При помощи функции ЕСЛИ (IF) эту таблицу можно быстро превратить из простого набора данных о продажах за месяц в отчет, который будет показывать, кто план выполнил, кто нет, и какая будет зарплата у каждого из менеджеров. Такой отчет может выглядеть как на рисунке ниже.

Рисунок. Отчет по результатам работы менеджеров по продажам

Для того чтобы автоматически заполнять столбец «Выполнение плана» и «Зарплата за месяц, руб.» (столбцы E и F соответственно), можно использовать функцию ЕСЛИ (IF).

Пример 1.1 - подстановка текста при помощи ЕСЛИ (IF)

.

В столбце «Выполнение плана» в ячейке E4 используем вот такую формулу:

ЕСЛИ(D4>=1000000;"Молодец!";"План не выполнен:(")

IF(D4>=1000000;"Молодец!";"План не выполнен:(") .

Кстати, в некоторых версиях Excel, вместо ";" должна использоваться ",".

После этого ячейку можно будет скопировать вниз до конца столбца, и программа в каждой строке напишет, кто молодец, а кто не выполнил план.

Что значат все аргументы ЕСЛИ (IF)?

1. Лог_выражение: D4>=1000000. В нашем примере логический вопрос - это сравнение фактического результата и плана продаж. D4 - это ссылка на ячейку с фактическими продажами этого менеджера. Excel, Google Sheets, LibreOffice, OpenOffice подставляют вместо D4 значение из этой ячейки и проверяют, верно ли указанное неравенство. В результате проверки в формуле получается промежуточный результат, он используется для выбора нужной ветки в дереве решений.

2. Значение_если_истина. На наших схемах это левая ветка дерева решений. В текущем примере значение аргумента - "Молодец!". Этот аргумент показывает, что должна сделать функция ЕСЛИ (IF), когда в результате вычисления первого аргумента получается ИСТИНА (TRUE). В текущем примере необходимо просто написать текст «Молодец!».

Кстати, "Молодец!" у нас написано в кавычках, потому что любой текст внутри формулы должен быть написан в кавычках. Исключением являются только названия функций и именованных диапазонов. В остальных случаях всегда ставьте текст в кавычки.

3. Значение_если_ложь. На наших схемах это правая ветка дерева решений. В текущем примере значение аргумента - "План не выполнен:(". Этот аргумент показывает, что должна сделать функция ЕСЛИ (IF), когда в результате вычисления первого аргумента получается ЛОЖЬ (FALSE). В текущем примере необходимо просто написать текст «План не выполнен:(».

Здесь мы также указали текст в кавычках, потому что, если не заключать текст внутри формулы в кавычки, возникнет ошибка #ИМЯ? (#NAME?). Исключение - только названия функций и именованных диапазонов.

Во-первых, функция ЕСЛИ (IF) отвечает на логический вопрос (вычисляет первый аргумент). Во-вторых, идет к соответствующей ветке дерева решений. По Александрову П.Ф. получается так:

1. D4>=1000000, следовательно проверяем 1000329>

2. Идем в аргумент Значение_если_истина. Нужно просто подставить текст «Молодец!». Указываем текст в ячейке. Конец расчетов.

Рисунок. Как работает функция ЕСЛИ (IF), когда логическое выражение возвращает ИСТИНА (TRUE)

1. D5>=1000000, следовательно проверяем 848880>

2. Идем в аргумент Значение_если_ложь. Нужно просто подставить текст «План не выполнен:(». Указываем текст в ячейке. Конец расчетов.

Схематически расчеты выглядят, как на рисунке ниже.

Рисунок. Как работает функция ЕСЛИ (IF), когда логическое выражение возвращает ЛОЖЬ (FALSE)

Пример 1.2 - вычисление разных формул при помощи ЕСЛИ (IF)

Файл-пример №1 вы можете скачать .

ЕСЛИ(D4>=1000000;20000+D4*5/100;20000)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

IF(D4>=1000000;20000+D4*5/100;20000) .

После этого ячейку можно будет скопировать вниз до конца столбца, и программа в каждой строке напишет зарплату каждого из менеджеров.

Что именно делает функция ЕСЛИ (IF) в этом примере?

Функция ЕСЛИ (IF) отвечает на логический вопрос (вычисляет первый аргумент) и переходит к соответствующей ветке дерева решений. По Александрову П.Ф. получается так:

1. D4>=1000000, следовательно проверяем 1000329>=1000000, выражение верно, значит логический параметр - это ИСТИНА (TRUE).

2. Идем в аргумент Значение_если_истина. Нужно вычислить 20000+D4*5/100 (то есть оклад 20 тысяч и та самая премия 5% от продаж). Получаем 70016, указываем это значение в ячейке. Конец расчетов.

Аргумент Значение_если_ложь в этом случае функция ЕСЛИ (IF) игнорирует.

По Ильину М.А. получается так:

1. D5>=1000000, следовательно проверяем 848880>=1000000, выражение не верно, значит логический параметр - ЛОЖЬ (FALSE).

2. Идем в аргумент Значение_если_ложь. Нужно просто поставить 20000. Указываем число в ячейке. Конец расчетов.

Аргумент Значение_если_истина в этом случае функция ЕСЛИ (IF) игнорирует.

Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой "Начать заново" на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.

Применение ЕСЛИ (IF) с несколькими условиями

Пример 2 - разные условия в логическом выражении

Файл-пример №2 вы можете скачать .

В прошлом примере и у менеджеров, и у старших менеджеров был одинаковый план продаж на месяц. Усложним задачу: установим повышенный план старшим менеджерам - 1 миллион 200 тысяч в месяц. Отчет тогда будет выглядеть, как на рисунке ниже.

В этом случае в столбце «Выполнение плана» в ячейке E4 используем вот такую формулу:

ЕСЛИ(ЕСЛИ(C4="Старший менеджер";D4>=1200000;D4>=1000000);"Молодец!";"План не выполнен:(")

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

IF(IF(C4="Старший менеджер";D4>=1200000;D4>=1000000);"Молодец!";"План не выполнен:(") .

Что именно делает функция ЕСЛИ (IF) в этом примере?

По Александрову П.Ф. получается так:

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

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

По Ильину М.А. получается так:

1. Функция ЕСЛИ (IF) начинает расчет с логического выражения и видит там вложенную функцию ЕСЛИ (IF). Excel, Google Sheets, LibreOffice, OpenOffice сначала считает вложенную функцию.

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

3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_ложь и сравнивает фактические продажи с планом менеджера по продажам. 848 880 меньше 1 000 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ЛОЖЬ (FALSE).

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

Рисунок. Дерево решений для функции ЕСЛИ (IF) с несколькими условиями

По Незенецеву А.А. получается так:

1. Функция ЕСЛИ (IF) начинает расчет с логического выражения и видит там вложенную функцию ЕСЛИ (IF). Excel, Google Sheets, LibreOffice, OpenOffice сначала считает вложенную функцию.

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

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

4. Результат вычисления вложенной функции ЕСЛИ (IF) передается в основную функцию. Основная функция ЕСЛИ (IF) видит логический параметр ИСТИНА (TRUE) и переходит к своему (а не к вложенному) аргументу Значение_если_истина. Этот аргумент - просто текст «Молодец!».

Формула с несколькими условиями, то есть с вложенными функциями ЕСЛИ (IF), возвращает в ячейку текст «Молодец!».

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

Рисунок. Дерево решений для функции ЕСЛИ (IF) с несколькими условиями

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

1. Функция ЕСЛИ (IF) начинает расчет с логического выражения и видит там вложенную функцию ЕСЛИ (IF). Excel, Google Sheets, LibreOffice, OpenOffice сначала считает вложенную функцию.

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

3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_истина и сравнивает фактические продажи с планом старшего менеджера по продажам. 1 046 625 меньше 1 200 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ЛОЖЬ (FALSE).

4. Результат вычисления вложенной функции ЕСЛИ (IF) передается в основную функцию. Основная функция ЕСЛИ (IF) видит логический параметр ЛОЖЬ (FALSE) и переходит к своему (а не к вложенному) аргументу Значение_если_ложь. Этот аргумент - просто текст «План не выполнен:(».

Формула с несколькими условиями, то есть с вложенными функциями ЕСЛИ (IF), возвращает в ячейку текст «План не выполнен:(».

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

Рисунок. Дерево решений для функции ЕСЛИ (IF) с несколькими условиями

Формула для расчета заработной платы в примере 3

В столбце «Зарплата за месяц, руб.» в ячейке F4 используем вот такую формулу:

ЕСЛИ(ЕСЛИ(C4="Старший менеджер";D4>=1200000;D4>=1000000);20000+D4*5/100;20000)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

IF(IF(C4="Старший менеджер";D4>=1200000;D4>=1000000);20000+D4*5/100;20000) .

Не забудьте, в некоторых версиях Excel, вместо ";" должна использоваться ",".

В этом случае функция ЕСЛИ (IF) работает точно так же, как и в ячейке E4.

Пример 4 - разные условия и в логическом выражении, и в ветках дерева решений

Файл-пример №3 вы можете скачать .

Итак, у нас есть менеджеры, есть старшие менеджеры. У старших менеджеров план выше, чем у обычных менеджеров. Для того чтобы такая модель работала, часто необходимо дополнительное стимулирование для старших менеджеров. Например, премия старшего менеджера повышается до 6%. То есть у нас сразу несколько условий:

1. Премия выплачивается только если выполнен план.

2. Если должность старший менеджер, план - 1 миллион 200 тысяч, иначе - 1 миллион.

3. Если должность старший менеджер, премия - 6%, иначе - 5%.

В итоге получается отчет, как на рисунке ниже.

Рисунок. Отчет по результатам работы менеджеров и старших менеджеров

Как решить такую задачу при помощи функции ЕСЛИ (IF)?

В ячейке F4 можно написать такую формулу:

ЕСЛИ(C4="Старший менеджер";D4>=1200000;D4>=1000000);

20000+D4*ЕСЛИ(C4="Старший менеджер";6;5)/100;

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

IF(C4="Старший менеджер";D4>=1200000;D4>=1000000);

20000+D4*IF(C4="Старший менеджер";6;5)/100;

Не забывайте, в некоторых версиях Excel, вместо ";" должна использоваться ",".

На рисунке ниже схематически изображено построенное дерево решений.

Рисунок. Пример дерева решений с несколькими условиями и в логическом выражении, и в других аргументах функции ЕСЛИ (IF)

Частые ошибки при работе с функцией ЕСЛИ (IF)

1. Для функции ЕСЛИ (IF) всегда должен быть указан первый аргумент - логическое выражение и второй аргумент - значение если истина. Третий аргумент необязательный. Пользователи часто забывают указать третий аргумент особенно при работе со сложными формулами, из-за этого в некоторых случаях вместо нужного результата в ячейке появляется логический параметр ЛОЖЬ (FALSE).

2. Сложность формулы очень быстро растет при использовании вложенных ЕСЛИ (IF). Из-за этого очень часто пользователи забывают закрыть скобки вложенных вычислений, не ставят разделитель аргументов («;» или «,»). В зависимости от ошибки формулу в ячейку либо не удается записать, либо она считается неправильно.

3. В сложных формулах с ЕСЛИ (IF) очень тяжело отслеживать правильность расчетов: каждая вложенная функция ЕСЛИ (IF) добавляет в ваше дерево решений один вопрос и минимум две ветки. В среднем человек в уме держит до 7 объектов, получается, что при трех вложенных ЕСЛИ (IF) в уме нужно держать 3 вопроса и 6 веток дерева решений. Контролируемость и надежность формулы стремительно снижается.

Как избежать этих ошибок при работе с функцией ЕСЛИ (IF)? Минимизируйте использование ЕСЛИ (IF) с другими функциями и особенно с вложенными ЕСЛИ (IF). Лучше делайте промежуточные расчеты в соседних ячейках.

Совет: работа со сложными формулами

Часто нам приходится работать со сложными формулами, формулами в которых в одну функцию вложены другие. Как не ошибиться при создании такой формулы? Действуете по следующем алгоритму:

1. Определите конечную цель ваших расчетов: какой результат вы должны получить в итоге.

2. Определите функцию, которая позволяет это сделать.

3. Начинайте создание формулы с этой функции, укажите ее и переходите к работе с аргументами.

5. Если вам необходимо проделать промежуточные вычисления, то определяете конечную цель этих вычислений, функцию и так далее. Обычно, задача промежуточных вычислений - это получить аргумент для основной функции. Помните об этом, так как иногда нужно получить аргумент определенного типа (именно текст, именно число, именно логический параметр или что-то иное).

6. Всегда следите за скобками: как только вы закончили описание функции, закрывайте скобки.

И помните, если формула слишком сложная, лучше сделать промежуточный расчет в соседней ячейке.

Чем дополнить и заменить функцию ЕСЛИ (IF)

Вместо констант в формуле можно использовать именованные диапазоны.

Решение задачи с несколькими условиями можно значительно упростить с помощью использования вложенных функций И (AND), ИЛИ (OR).

Функция ЕСЛИ (IF) иногда может быть заменена на функцию ВПР (VLOOKUP) , ГПР (HLOOKUP), ПРОСМОТР(LOOKUP), ЕСЛИОШИБКА (IFERROR), СУММЕСЛИ (SUMIF) или СЧЁТЕСЛИ (COUNTIF).

Файл-пример №1 "Применение функции ЕСЛИ (IF) с одним условием" вы можете скачать .

Файл-пример №2 "Применение функции ЕСЛИ (IF) с несколькими условиями" .

Остались вопросы? Пишите нам в форму обратной связи и записывайтесь на интенсив по Excel или курс по функциям Excel .

Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.

ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй - если сравнение ложно.

Операторы ЕСЛИ чрезвычайно надежны и являются неотъемлемой частью многих моделей электронных таблиц. Но они же часто становятся причиной многих проблем с электронными таблицами. В идеале оператор ЕСЛИ должен применяться для минимума условий (например, "Женский"/"Мужской", "Да"/"Нет"/"Возможно"), но иногда сценарии настолько сложны, что для их оценки требуется использовать вместе больше 3 вложенных* функций ЕСЛИ.

* "Вложенность" означает объединение нескольких функций в одной формуле.

Функция ЕСЛИ, одна из логических функций , служит для возвращения разных значений в зависимости от того, соблюдается ли условие.

Синтаксис

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

Например:

ЕСЛИ(A2>B2;"Превышение бюджета";"ОК")

ЕСЛИ(A2=B2;B4-A4;"")

Примечания

Excel позволяет использовать до 64 вложенных функций ЕСЛИ, но это вовсе не означает, что так и надо делать. Почему?

Нужно очень крепко подумать, чтобы выстроить последовательность из множества операторов ЕСЛИ и обеспечить их правильную отработку по каждому условию на протяжении всей цепочки. Если при вложении вы допустите в формуле малейшую неточность, она может сработать в 75 % случаев, но вернуть непредвиденные результаты в остальных 25 %. К сожалению, шансов отыскать эти 25 % немного.

Работа с множественными операторами ЕСЛИ может оказаться чрезвычайно трудоемкой, особенно если вы вернетесь к ним через какое-то время и попробуете разобраться, что пытались сделать вы или, и того хуже, кто-то другой.

Если вы видите, что ваш оператор ЕСЛИ все разрастается, устремляясь в бесконечность, значит вам пора отложить мышь и пересмотреть свою стратегию.

Давайте посмотрим, как правильно создавать операторы с несколькими вложенными функциями ЕСЛИ и как понять, когда пора переходить к другим средствам из арсенала Excel.

Примеры

Ниже приведен пример довольно типичного вложенного оператора ЕСЛИ, предназначенного для преобразования тестовых баллов учащихся в их буквенный эквивалент.

ЕСЛИ(D2>89;"A";ЕСЛИ(D2>79;"B";ЕСЛИ(D2>69;"C";ЕСЛИ(D2>59;"D";"F"))))

Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:

Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.

Если тестовых баллов больше 79, учащийся получает оценку B.

Если тестовых баллов больше 69, учащийся получает оценку C.

Если тестовых баллов больше 59, учащийся получает оценку D.

В противном случае учащийся получает оценку F.

Этот частный пример относительно безопасен, поскольку взаимосвязь между тестовыми баллами и буквенными оценками вряд ли будет меняться, так что дополнительных изменений не потребуется. Но что если вам потребуется разделить оценки на A+, A и A– (и т. д.)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот так будет выглядеть ваша формула:

ЕСЛИ(B2>97;"A+";ЕСЛИ(B2>93;"A";ЕСЛИ(B2>89;"A-";ЕСЛИ(B2>87;"B+";ЕСЛИ(B2>83;"B";ЕСЛИ(B2>79;"B-"; ЕСЛИ(B2>77;"C+";ЕСЛИ(B2>73;"C";ЕСЛИ(B2>69;"C-";ЕСЛИ(B2>57;"D+";ЕСЛИ(B2>53;"D";ЕСЛИ(B2>49;"D-";"F"))))))))))))

Она по-прежнему точна и будет правильно работать, но вы потратите много времени, чтобы написать ее, а потом протестировать. Еще одна очевидная проблема состоит в том, что вам придется вручную вводить баллы и эквивалентные буквенные оценки. Каковы шансы, что вы не ошибетесь? А теперь представьте, как вы пытаетесь сделать это 64 раза для более сложных условий! Конечно, это возможно. Но неужели вам хочется потратить столько сил без всякой уверенности в отсутствии ошибок, которые потом будет трудно обнаружить?

Совет: Для каждой функции в Excel обязательно указываются открывающая и закрывающая скобки (). При редактировании Excel попытается помочь вам понять, что куда идет, окрашивая разными цветами части формулы. Например, во время редактирования показанной выше формулы при перемещении курсора за каждую закрывающую скобку ")" тем же цветом будет окрашиваться соответствующая открывающая скобка. Это особенно удобно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли в них парных скобок.

Дополнительные примеры

Ниже приведен распространенный пример расчета комиссионных за продажу в зависимости от уровней дохода.

ЕСЛИ(C9>15000;20%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>5000;10%;0)))))

Эта формула означает: ЕСЛИ(ячейка C9 больше 15 000, то вернуть 20 %, ЕСЛИ(ячейка C9 больше 12 500, то вернуть 17,5 % и т. д...

На первый взгляд все очень похоже на предыдущий пример с оценками, однако на примере этой формулы хорошо видно, насколько сложно бывает работать с большими операторами ЕСЛИ. Что вы будете делать, если ваша организация решит добавить новые уровни компенсаций или изменить имеющиеся суммы или проценты? У вас появится очень много работы!

Совет: Чтобы сложные формулы было проще читать, вы можете вставить разрывы строк в строке формул. Просто нажмите клавиши ALT+ВВОД перед текстом, который хотите перенести на другую строку.

Перед вами пример сценария для расчета комиссионных с неправильной логикой:

Видите, что происходит? Посмотрите порядок сравнения доходов в предыдущем примере. А как все идет в этом? Именно! Сравнение идет снизу вверх (от 5 000 до 15 000 ₽), а не наоборот. Ну и что в этом такого? Это важно, потому что формула не может пройти первую оценку для любого значения, превышающего 5 000 ₽. Скажем, ваш доход составил 12 500 ₽ - оператор ЕСЛИ вернет 10 %, потому что это больше 5 000 ₽, и на этом остановится. Это может быть очень проблематично, поскольку ошибки такого типа часто остаются незамеченными, пока не оказывают негативного влияния. Так что же вам делать теперь, когда вы знаете, какие трудности могут ожидать вас при использовании вложенных операторов ЕСЛИ? В большинстве случаев вместо сложной формулы с функциями ЕСЛИ можно использовать функцию ВПР. При использовании функции ВПР вам для начала нужно создать ссылочную таблицу:

ВПР(C2;C5:D17;2;ИСТИНА)

В этой формуле предлагается найти значение ячейки C2 в диапазоне C5:C17. Если значение найдено, возвращается соответствующее значение из той же строки в столбце D.

ВПР(B9;B2:C6;2;ИСТИНА)

Эта формула ищет значение ячейки B9 в диапазоне B2:B22. Если значение найдено, возвращается соответствующее значение из той же строки в столбце C.

Примечание: В обеих функциях ВПР в конце формулы используется аргумент ИСТИНА, который означает, что мы хотим найти близкое совпадение. Иначе говоря, будут сопоставляться точные значения в таблице подстановки, а также все значения, попадающие между ними. В этом случае таблицы подстановки нужно сортировать по возрастанию, от меньшего к большему.

Функция ВПР подробно рассматривается , но очевидно, что она значительно проще, чем сложный 12-уровневый вложенный оператор ЕСЛИ. Есть и другие, менее очевидные, преимущества:

Таблицы ссылок функции ВПР открыты и их легко увидеть.

Значения в таблицах просто обновлять, и вам не потребуется трогать формулу, если условия изменятся.

Если вы не хотите, чтобы люди видели вашу таблицу ссылок или вмешивались в нее, просто поместите ее на другой лист.

Вы знали?

Теперь есть функция УСЛОВИЯ , которая может заменить несколько вложенных операторов ЕСЛИ. Так, в нашем первом примере оценок с 4 вложенными функциями ЕСЛИ:

ЕСЛИ(D2>89;"A";ЕСЛИ(D2>79;"B";ЕСЛИ(D2>69;"C";ЕСЛИ(D2>59;"D";"F"))))

можно сделать все гораздо проще с помощью одной функции ЕСЛИМН:

ЕСЛИМН(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"D";ИСТИНА;"F")

Функция ЕСЛИМН - просто находка! Благодаря ей вам больше не нужно переживать обо всех этих операторах ЕСЛИ и скобках.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе

Сегодня мы рассмотрим функцию ЕСЛИ .

Функция ЕСЛИ часто используется в Excel для решения многих задач. Знать ее очень полезно. В данной статье мы попробуем рассказать про ее работу на простых примерах, достаточно один раз разобраться с конструкцией функцией ЕСЛИ и вы сможете применять ее и в самых сложных вариантах.

Функция ЕСЛИ проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.

Синтаксис функции ЕСЛИ очень простой:

ЕСЛИ(лог_выражение ; [значение_если_истина ]; [значение_если_ложь ])

лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Что это значит? Выражение при вычислении дает значение ИСТИНА если это выражение верно.

В этой части необходимо проверить на соответствие выражения.

Например:

ЕСЛИ(А1=10 ; [значение_если_истина]; [значение_если_ложь]) - если А1 равно 10, то выражение А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ

Другой пример

ЕСЛИ(А1>30 ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ

Еще пример

ЕСЛИ(С1=”Да” ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да” вернет ЛОЖЬ

ЕСЛИ(лог_выражение ; [значение_если_истина ]; [значение_если_ложь ])

значение_если_истина , значение_если_ложь – как видно из их названия, это то что необходимо сделать в зависимости от того, что вернул лог выражения : ИСТИНА и ЛОЖЬ

Пример использования функции ЕСЛИ в Excel

Рассмотрим использование функции ЕСЛИ на практическом примере. У нас есть таблица заказов, которую мы использовали при рассмотрении работы . Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами. Это можно сделать различными способами, но мы с вами будет использовать функцию ЕСЛИ, чтобы показать ее работу на примере. (см.рисунок)

Для решения поставленной задачи напишем формулу с использованием функции ЕСЛИ

ЕСЛИ(A3="Ведро";D3;"-")

Как вы смогли заметить аргументы функции ЕСЛИ разделены точкой с запятой.

Итак, первый аргумент (лог выражения ) A3="Ведро" проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина ), в нашем случае это D3 (т.е стоимость заказа), если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь ), в нашем случае это «-» (т.е будет написано тире).

Таким образом, в ячейки E3 появится значение D3, т.е число 240.