Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий — statanaliz

Функция ЕСЛИ в Excel

Функция имеет следующий синтаксис.

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

log_expr — это состояние для проверки. Например А2<100. Если значение в ячейке A2 действительно меньше 100, в памяти Excel создается ответ ИСТИНА, и функция возвращает все, что введено в следующем поле. Если это не так, в памяти генерируется ответ FALSE и возвращается значение из последнего поля.

value_if_true — это значение или формула, возвращаемая при возникновении события, указанного в первом параметре.

значение_если_ложь — это альтернативное значение или формула, которая возвращается, если условие не выполняется. Это поле является необязательным. В этом случае при возникновении альтернативного события функция вернет FALSE.

Очень простой пример. Вы должны проверить, превышает ли продажа отдельных продуктов 30 единиц или нет. Если они превышают, формула должна вернуть «ОК», иначе — «Удалить». Расчет с результатом показан ниже.

Продажа первого предмета составляет 75 норвежских крон, т е выполняется условие, что она больше 30. Поэтому функция возвращает то, что введено в соседнее поле — «ОК». Продажи второго товара меньше 30, поэтому условие (>30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом весь смысл функции ЕСЛИ. Если мы продлим вычисление вниз, мы получим результат для каждого продукта.

Однако это была демонстрация. Чаще формула ЕСЛИ в Excel используется для более сложных элементов управления. Допустим, есть средненедельные продажи товаров и их текущие остатки. Покупатель должен сделать прогноз по остатку через 2 недели. Для этого вам нужно вычесть из текущего запаса удвоенный средний недельный объем продаж.

Пока все логично, но минусы смущают. Есть ли отрицательные остатки? Конечно, нет. Акции не могут быть ниже нуля. Чтобы прогноз был верным, необходимо заменить отрицательные значения нулями. Здесь на помощь приходит формула ЕСЛИ. Он проверит полученное значение по прогнозу, и если оно окажется меньше нуля, принудительно ответит 0, в противном случае вернет результат расчета, т.е положительное число. В общем та же логика, только вместо значений используем формулу как условие.

Отрицательных значений в прогнозе по акциям больше нет, что в целом очень хорошо.

Формулы Excel ЕСЛИ также активно используются в матричных формулах. Здесь мы не будем заходить слишком далеко. Для интересующихся рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значения по условию. Правда, математика в той статье уже не актуальна, т.к. В Excel 2016 представлены функции МИНЕСЛИ и МАКСЕСЛИ. А вот познакомиться, например, очень полезно — пригодится в другой ситуации.

Синтаксис функции ЕСЛИ с одним условием

Синтаксис оператора в Excel — это структура функции, данные, необходимые для ее работы.

=ЕСЛИ (логическое_выражение, значение_если_истина, значение_если_ложь)

Разберем синтаксис функции:

Boolean_expression — ЧТО проверяет оператор (текстовые или числовые данные ячейки).

Value_if_true — ЧТО будет отображаться в ячейке, когда текст или число удовлетворяют заданному условию (истина).

Значение if_false — ЧТО будет отображаться в колонке, когда текст или число НЕ удовлетворяют заданному условию (false).

Пример:

Оператор проверяет ячейку A1 и сравнивает ее с 20. Это «логическое_выражение». При содержимом столбца больше 20 выводится истинная надпись «больше 20». Нет — «меньше или равно 20».

Обратите внимание на следующее! Слова в формуле должны быть заключены в кавычки. Чтобы Excel понимал, что нужно отображать текстовые значения.

Еще один пример. Чтобы быть допущенными к экзамену, студенты в группе должны пройти тест. Результаты сведем в таблицу со столбцами: список студентов, кредиты, экзамен.

Примечание. Оператор IF должен проверять не числовой тип данных, а текст. Поэтому в формуле мы написали В2 = «кредит». Берем цитаты, чтобы программа правильно распознала текст.

Читайте также: Математические функции в Excel: подробное описание

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных связей не 2 (проверено и необязательно), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь вам придется вкладывать ее друг в друга, указывая поочередно все условия. Рассмотрим следующий пример.

Несколько менеджеров по продажам должны заработать премию в зависимости от выполнения плана продаж. Система мотивации выглядит следующим образом. При выполнении плана менее чем на 90 % премия не начисляется, от 90 % до 95 % — премия 10 %, от 95 % до 100 % — премия 20 %, а при перевыполнении плана — 30 %. Как видите, есть 4 варианта. Чтобы указать их в одной формуле, требуется следующая логическая структура. При выполнении первого условия возникает первый вариант, в противном случае при выполнении второго условия возникает второй вариант, иначе если . и т д. Количество условий может быть достаточно большим. В конце формулы указывается последний вариант, для которого не выполняется ни одно из вышеперечисленных условий (как третье поле в обычной формуле ЕСЛИ). В результате формула имеет следующий вид.

Комбинация функций ЕСЛИ работает таким образом, что при выполнении каких-либо заданных условий следующие уже не проверяются. Поэтому важно перечислить их в правильном порядке. Если бы мы начали проверку с B2<1, то условия B2<0,9 и B2<0,95 просто не были бы замечены Excel, потому что они находятся в интервале B2<1, который будет проверяться первым (если значение меньше 0,9, это, конечно, также меньше, чем 1). И тогда у нас было бы только два возможных варианта: меньше 1 и вариант, т.е. 1 или больше.

При написании формулы легко запутаться, поэтому рекомендуется взглянуть на всплывающую подсказку.

В конце необходимо закрыть все скобки, иначе Excel выдаст ошибку

Одновременное выполнение двух условий

Также в Excel есть возможность отображать данные об одновременном выполнении двух условий. При этом значение будет считаться ложным, если хотя бы одно из условий не выполнено. Для этой задачи используется оператор «И».

Возьмем для примера нашу таблицу. Теперь скидка 30% действует только в том случае, если это женская обувь и предназначена для бега. Если эти условия соблюдены, значение ячейки будет равно 30% при этом, иначе будет 0.

Для этого воспользуемся следующей формулой:

=ЕСЛИ(И(B2=»женщина»;C2=»бегун»);30%;0)

Нажмите клавишу Enter, чтобы отобразить результат в ячейке.

Как и в примерах выше, распространяем формулу на остальные строки.

Расширение функционала с помощью операторов «И» и «ИЛИ»

При проверке нескольких истинных условий используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТО значение в ИНАЧЕ значение с.

Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, результат будет истинным. Суть в следующем: ЕСЛИ a = 1 OR a = 2 THEN значение в ELSE значение c.

Функции И и ИЛИ могут проверять до 30 условий.

Функция Excel ЕСЛИМН

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

В MS Excel 2016 появилась функция IIFS, для которой и была написана вся эта статья. Это тот же ПЧ, только заточенный специально под поиск многих условий. Теперь вам не нужно сто раз писать ЕСЛИ и считать открытые скобки. Достаточно перечислить условия и закрыть скобку в конце.

Работает следующим образом. Возьмем приведенный выше пример и воспользуемся новой формулой Excel IIFS.

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

Стоит принять во внимание следующее. Мы по-прежнему перечисляем условия в правильном порядке, чтобы не было ненужного перекрытия диапазонов. Последнее альтернативное условие, в отличие от обычного ЕСЛИ, также необходимо указать. IF указывает только альтернативное значение, которое возникает, если ни одно из перечисленных условий не выполняется. Здесь также необходимо указать само условие, которое в нашем случае будет B2>=1. Однако этого можно избежать, написав ИСТИНА в поле условия, что указывает на то, что если ранее перечисленные условия не выполняются, происходит ИСТИНА и возвращается последнее альтернативное значение.

Теперь вы знаете, как использовать функцию ЕСЛИ в Excel, а также ее более современную версию для набора коэффициентов ЕСЛИ.

Как правильно записать условие «если – то» в Excel?

Поместите курсор в ячейку G2 и введите символ «=». Для Excel это означает, что формула введена. Поэтому, как только буква «е» будет нажата дальше, нам будет предложено выбрать функцию, которая начинается на эту букву. Выберите «ЕСЛИ”.

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

В качестве первого аргумента ЕСЛИ пишем: С2=»Запад». Как и в других функциях Excel, вы не можете ввести адрес ячейки вручную, а просто кликните по нему мышкой. Затем ставим «;» и укажите второй аргумент.

Второй аргумент ЕСЛИ — это значение, которое примет ячейка G2, если будет выполнено записанное нами условие. Оно становится словом «Местный”.

После этого снова через запятую укажите значение третьего аргумента. Ячейка G2 примет это значение, если не будет выполнено условие: «Экспорт». Не забудьте закончить ввод формулы, закрыв скобки и нажав «Enter”.

Наша формула выглядит так:

=ЕСЛИ(C2=»Запад»,»Местный»,»Экспорт»)

В английской версии формула ЕСЛИ будет выглядеть так:

=ЕСЛИ(C2=»Запад»,»Местный»,»Экспорт»)

То есть, если значение в ячейке C2 равно «Запад», Excel вернет слово «Местный» в ячейке с формулой. А если условие не выполнено, то — «Экспорт».

Наша ячейка G2 настроена на «Местные жители».

Теперь эту формулу можно скопировать во все остальные ячейки столбца G.

А если один из параметров не заполнен?

Если вас не интересует, что произойдет, например, если не выполняется интересующее вас условие, то вы не можете передать второй аргумент. Например, мы даем скидку 10% при заказе более 100 позиций. Мы не указываем третий аргумент в формуле ЕСЛИ для случая, когда условие не выполняется.

=ЕСЛИ(Е2>100;F2*0,1)

Каков будет результат?

Насколько это красиво и удобно, судить вам. Я думаю, что лучше использовать оба аргумента в функции ЕСЛИ.

А если второе условие не выполняется, но ничего делать не нужно, просто поставьте в ячейку пустое значение.

=ЕСЛИ(Е2>100,F2*0,1;»»)

Однако эту конструкцию можно использовать, если значение True или False будет использоваться другими функциями Excel в качестве логических значений.

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

Кроме того, если вам действительно нужно просто проверить условие и получить «Истина» или «Ложь» («Да» или «Нет»), вы можете использовать следующую конструкцию –

=ЕСЛИ(Е2>100,ИСТИНА,ЛОЖЬ)

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

Давайте посмотрим, как еще можно использовать функцию ЕСЛИ.

Использование функции ЕСЛИ с числами.

Как и в случае с текстом, в аргументах функций можно использовать числа.

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

Например, мы даем нашему клиенту скидку в зависимости от цены покупки. Если сумма больше 100, ему предоставляется скидка 10%.

Назовем столбец H «Скидка», а в ячейку H2 введем функцию ЕСЛИ, вторым аргументом которой будет формула расчета скидки.

=ЕСЛИ(Е2>100;F2*0,1;0)

Вложенные условия с математическими выражениями.

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

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

Предполагая, что количество записано в ячейке B8, формула будет выглядеть так:

=B8*IF(B8>=101, 12, ЕСЛИ(B8>=50, 14, ЕСЛИ(B8>=20, 16, ЕСЛИ(B8>=11, 18, ЕСЛИ(B8>=1, 22; «»)))))

И вот результат:

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

Например, вместо «жесткого кодирования» цен в самой формуле вы можете ссылаться на ячейки, где они указаны (ячейки с B2 по B6). Это позволит вам редактировать исходные данные, не обновляя саму формулу:

=B8*IF(B8>=101, B6, IF(B8>=50, B5, IF(B8>=20, B4, IF(B8>=11, B3, IF(B8>=1, B2; «»)))))

Как сравнить данные в двух таблицах

Пользователям часто требуется сравнить две таблицы в Excel на наличие совпадений. Примеры из «жизни»: сравнение цен на товары в разных отгрузках, сравнение балансов (бухгалтерских отчетов) за несколько месяцев, успеваемости школьников (студентов) в разных классах, в разных кварталах и т д

Чтобы сравнить 2 таблицы в Excel, вы можете использовать оператор СЧЁТЕСЛИ. Рассмотрим порядок использования функции.

Для примера возьмем две таблицы с техническими характеристиками разных кухонных комбайнов. Мы задумали подчеркнуть различия цветом. Условное форматирование решает эту проблему в Excel.

Исходные данные (таблицы, с которыми мы будем работать):

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

В строке формул пишем: = СЧЁТЕСЛИ (диапазон для сравнения; первая ячейка в первой таблице) = 0. Диапазон для сравнения — вторая таблица.

Чтобы ввести диапазон в формулу, просто выберите первую ячейку и последнюю. «= 0» означает команду на поиск точных (а не приблизительных) значений.

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

Выберите вторую таблицу. Условное форматирование — создать правило — применить формулу. Мы используем тот же оператор (COUNTIF).

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

Как задать функции ЕСЛИ через Построитель формул

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

Давайте посмотрим, как это работает. Предположим, что снова, как и в первом примере, мы должны сделать скидку на всю женскую обувь на 25%.

1. Ставим курсор на нужную ячейку, переходим в категорию «Формулы», а затем нажимаем «Вставить функцию”.
2. В открывшемся списке построителя формул выберите «ЕСЛИ» и нажмите «Вставить функцию”.
3. Откроется окно настроек функции.
   В поле «логическое выражение» напишите условие, при котором должна выполняться проверка. В нашем случае это «B2=«женщина”.
   В поле «Истина» напишите значение, которое будет отображаться в ячейке при выполнении условия.
   В поле «False» — значение, если условие не выполняется.
4. После заполнения всех полей нажмите «Готово» для получения результата.