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

Давайте начнем с простейшей возможности использования формулы. Синтаксис: ЕСЛИ (лог_выражение; значение_если истина) , где лог_выражение — логическое выражение, то есть выражение возвращающее значение ИСТИНА или ЛОЖЬ (или эквивалентные численные значения: 0, если ЛОЖЬ, и любое положительное значение — ИСТИНА, и где значение_если истина — значение, возвращаемое функцией, в случае если лог_выражение имеет значение ИСТИНА.

Например, разберем следующую формулу: =ЕСЛИ(A1>1000;"много") . Логическое выражение А1>100 0 используется для проверки. Допустим, вы добавите данную формулу в ячейку В1. Если логическое выражение является истинным (оно является таковым, когда число в A1 больше 1000), функция возвращает значение «много», и именно это вы увидите в ячейке В1 (в случае если А1 меньше 1000, вы увидите значение ЛОЖЬ).

Другое простое применение функции - проверка для вывода предупреждений. Например, если у вас имеется книга с результатами увеличения продаж различных товаров в процентах. Будет полезно отметить определенным образом товары с уменьшившимися продажами. Базовая формула для такой отметки представляет собой следующее: =ЕСЛИ(ячейка

Здесь ячейка - адрес ячейки для проверки, а отметка - определенного рода текст для вывода на экран для указания на отрицательное значение. Вот пример: =ЕСЛИ(В1

Немного более улучшенная версия отметки, в которой она будет зависеть от величины отрицательного значения, выглядит так: =ПОВТОР("

Обработка ложного результата

Как вы можете видеть на рис. 4.15, если в функции ЕСЛИ() логическое выражение принимает отрицательное значение, функция возвращает ЛОЖЬ в качестве результата. Это не является критическим недостатком, однако делает рабочие листы как бы «тяжелее», чем если бы в результате возвращалась, например, пустая строка.

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

Например, рассмотрим следующую формулу: =ЕСЛИ(A1>1000;"много";"мало") . Если на этот раз ячейка A1 содержит число, меньшее или равное 1000, формула выведет строку «мало». Для примера с отметкой отрицательных объемов продаж (см. выше рис. 4.15) необходимо использовать следующую формулу: =ЕСЛИ(B1

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

Предотвращение деления на ноль

Как мы уже указывали ранее, функция возвращает ошибку #ДЕЛ/0! , если формула пытается поделить какое-то значение на 0. Для устранения даже возможности совершения такой ошибки вы можете воспользоваться функцией ЕСЛИ() для проверки деления.

Например, простейшее вычисление процента валовой прибыли по формуле (Доходы - Расходы)/Доходы. Для уверенности в том, что значение Доходы не равно нулю, используйте следующую формулу (предполагается, что вместо слов Доходы и Расходы будут подставлены необходимые значения): =ЕСЛИ(Доходы 0; (Доходы - Расходы)/Доходы; "Доходы равны 0!") . Если логическое выражение Доходы 0 верно, значит, произойдет вычисление по формуле. В противном случае функция вернет предупреждающее сообщение вместо ошибки.

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

Распространенный вопрос по Excel «Как записывать несколько условий в одной формуле?». Особенно часто применяется два и более условий при использовании функции ЕСЛИ. Сделать несколько условий в формуле ЕСЛИ довольно просто, главное знать основные принципы. Их и обсуждаем ниже.

На мой взгляд, показательно рассмотреть пример с решением системы условий. Такие задания часто задают в институтах, на парах по Excel.

Например, есть вот такая, довольно нагроможденная формула:

Разберем на примере, как перенести ее в Excel

Понятно, что эта формула будет состоять из 3 частей, как минимум:

SIN(B1)^2 =COS(B1) =EXP(1/B1)

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

Ее состав следующий:

ЕСЛИ(Условие;если условие = ДА (ИСТИНА);если условие = НЕТ (ЛОЖЬ))

Т.е. если мы запишем простую формулу, что мы получим в итоге в ячейке B2?

Верно — отобразиться 100. Если же в А1 будет стоять любое другое значение кроме 1, то в B2 отобразится бы 0.

Вернемся к нашей системе условий. Теперь нам надо понимать как записать сразу два условия до первой точки с запятой. У нас в B1 пусто, а значит = 0, и только при выполнении обоих условий А1=1 и B1=0 (знак *) значение формулы будет равно 100.

Особо разберем * между скобками

Оператор И он же * означает, что должно выполняться оба условия одновременно, А1=1 и B1=0.

Если между скобками поставить + (или), то достаточно будет одного из условий. Например только если А1=1, то уже будет отображаться 100.

Мы готовы к написанию формулы, будем это делать по частям

Запишем первое условие

ЕСЛИ((B1>-2)*(B1<9);SIN(B1)^2);

Если условие выполняется, то выполняется первая формула с синусом
Если нет, второе условие

ЕСЛИ((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1)

Во всех же остальных случаях будет выполнятся формула =EXP(1/B1)
Итого получается:

ЕСЛИ((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1);EXP(1/B1)))

Запись нескольких формул в одной

Если в ячейки B1 будет текст, то формула выдаст ошибку. Поэтому я часто применяю формулу .

Представим что вся наша формула из предыдущего пункта это один условный аргумент А

Тогда =ЕСЛИОШИБКА(А;»»)

Или для нашего примера

ЕСЛИОШИБКА(ЕСЛИ((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1);EXP(1/B1)));"")

Пример можно скачать

" была рассмотрена работа со встроенной функцией ЕСЛИ(). Все бы хорошо, но возникают такие ситуации, и довольно часто, когда вложенностью условия в условие не обойтись. Например, элементарно необходимо проверить следующее: 1 больше или равно x и x меньше или равно 5 . Реализовать такое условие несколькими ЕСЛИ довольно громоздко и проблематично, а в некоторых ситуациях - вообще невозможно. Для расширения функционала ЕСЛИ и облегчения составления формул с условием, в Excel имеются еще пара полезных функции - И() и ИЛИ().

Функция И()

Функция И() Условие 1 И Условие 2 . При этом все условия должны быть истинными. Результатом работы данной функции является ИСТИНА или ЛОЖЬ (TRUE / FALSE). Пример: ЕСЛИ а = b И а=с ТОГДА значение 1 ИНАЧЕ значение 2.
Как видно из примера, значение 1 будет только в том случае, если все условия верны.

Функция ИЛИ()

Функция ИЛИ() используется тогда, когда необходимо проверить несколько условий следующим образом - Условие 1 ИЛИ Условие 2 . Результат функции будет истинным, если хотя бы одно из условий истинно. Пример: ЕСЛИ а = b ИЛИ а=с ТОГДА значение 1 ИНАЧЕ значение 2.

Синтаксис функций И() и ИЛИ() одинаков: Функция(Условие 1; Условие 2; Условие 3 и до 30-ти условий). Результат ИСТИНА или ЛОЖЬ.

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

Все вышесказанное сложно к пониманию и относится к разделу Мат. логики и Дискретной математики. Попробую это все изложить на понятном языке. Разберем несколько примеров. Скажу сразу, все примеры будут с использованием функции ЕСЛИ.

Пример 1.
Столбец А, начиная с первой строки, содержит 56, 55, 88, 6, 74. Столбец В - 52, 55, 88, 4, 25. Столбец С - 53, 55, 88, 6, 25. С помощью функций ЕСЛИ и И необходимо определить строки, значения которых равны следующим образом А=В и В=С.

Переходим в ячейку D1 и с помощью мастера функций вводим следующее

Формула будет выглядеть так: “ =ЕСЛИ(И(A1=B1;B1=C1);"Все значения равны";"Значения не равны") “

Результат работы:

Пример 2.

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

Мастер функций выглядит так:

Формула: “ =ЕСЛИ(ИЛИ(A1=B1;B1=C1;A1=C1);"Есть равные значения";"Нет равных значений")”

Результат:

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

И напоследок рассмотрим еще один пример с функцией И и ИЛИ из реальной жизни.

Пример 3.

В таблицу занесены процент выполнения заданий, необходимо проценты перевести в пятибалльную систему следующим образом:

1 – от 0% до 10%
2- от 11% до 30%
3- от 31% до 40%
4- от 41% до 75%
5-от 76% до 100%

Таблица следующего вида.

Для того чтобы понять как пользоваться этой программой, необходимо рассмотреть формулы EXCEL с примерами.

Если поставить курсор мышки на любую ячейку и нажать на пункт «выбрать функцию», то появляется мастер функций.

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

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

В случае если функция неизвестна пользователю, то он может установить категорию «полный алфавитный перечень».

Например, дана задача, найти функцию СУММЕСЛИМН. Для этого нужно зайти в категорию математических функций и там найти нужную.

Функция ВПР

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

После чего осуществляется возврат итогового значения из ячейки, которая располагается на пересечении выбранной строчки и столбца.

Вычисление ВПР можно проследить на примере, в котором приведен список из фамилий . Задача – по предложенному номеру найти фамилию.

Применение функции ВПР

Формула показывает, что первым аргументом функции является ячейка С1.

Второй аргумент А1:В10 – это диапазон, в котором осуществляется поиск.

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

Вычисление заданной фамилии с помощью функции ВПР

Кроме того, выполнить поиск фамилии можно даже в том случае, если некоторые порядковые номера пропущены.

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

Поиск фамилии с пропущенными номерами

Объясняется такое явление тем, что функция ВПР обладает четвертым аргументом, с помощью которого можно задать интервальный просмотр.

Он имеет только два значения – «ложь» или «истина». Если аргумент не задается, то он устанавливается по умолчанию в позиции «истина».

Округление чисел с помощью функций

Функции программы позволяют произвести точное округление любого дробного числа в большую или меньшую сторону.

А полученное значение можно использовать при расчетах в других формулах.

Округление числа осуществляется с помощью формулы «ОКРУГЛВВЕРХ». Для этого нужно заполнить ячейку.

Первый аргумент – 76,375, а второй – 0.

Округление числа с помощью формулы

В данном случае округление числа произошло в большую сторону. Чтобы округлить значение в меньшую сторону, следует выбрать функцию «ОКРУГЛВНИЗ».

Округление происходит до целого числа. В нашем случае до 77 или 76.

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

Наиболее активно программу используют проектировщики, предприниматели, а также студенты.

Вся правда о формулах программы Microsoft Excel 2007

Формулы EXCEL с примерами - Инструкция по применению

Общая информация о ЕСЛИ (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 .