Функция ЕСЛИ в Excel — примеры использования
Функция ЕСЛИ в Excel — одна из самых простых функций. Именно поэтому он является одним из основных и в то же время очень полезным.
Сейчас мы увидим примеры того, как можно использовать функцию ЕСЛИ в Excel, а также какие задачи мы можем с ее помощью решить.
Что делает функция ЕСЛИ?
Он позволяет создать дерево решений, в котором при выполнении определенного условия происходит определенное действие. И если это условие не выполняется, то выполняется другое действие.
При этом аргументом функции должен быть вопрос, на который возможны 2 варианта ответа: «да» и «нет», «верно» или «ложно».
Вот как будет выглядеть это дерево решений.
Итак, функция ЕСЛИ позволяет задать вопрос и указать 2 варианта расчетов в зависимости от полученного ответа. Это три аргумента функции.
Синтаксис функции ЕСЛИ
Вот синтаксис этой функции и ее аргументы:
=ЕСЛИ(логическое выражение, значение, если да, значение, если нет»)
Логическое выражение: условие (обязательное), возвращающее значение «истина» или «ложь» («да» или «нет»);
Значение «да» — это (обязательное) действие, которое следует предпринять, если ответ «да;
Значение «нет» — это (обязательное) действие, которое следует предпринять, если ответ отрицательный;
Давайте вместе рассмотрим эти аргументы подробнее.
Первый аргумент — логичный вопрос. И этот ответ может быть только «да» или «нет», «верно» или «ложно».
Как задать правильный вопрос? Для этого можно составить логическое выражение, используя знаки «=», «>», «=», «». Давайте вместе попробуем задать этот вопрос.
Простейший пример применения.
Допустим, вы работаете в компании, которая продает шоколад в нескольких регионах и работает со многими покупателями.
Нам нужно различать продажи, которые произошли в нашем регионе, и те, которые произошли за рубежом. Для этого нужно добавить в таблицу для каждой продажи еще один атрибут: страну, в которой она произошла. Мы хотим, чтобы этот атрибут создавался автоматически для каждого ввода (т.е строки).
В этом нам поможет функция ЕСЛИ. Добавим в таблицу данных столбец «Страна». Регион «Запад» — это местные продажи («Местные»), а остальные регионы — зарубежные продажи («Экспорт»).
Как правильно записать?
Поместите курсор в ячейку G2 и введите знак «=». Для Excel это означает, что теперь будет введена формула. Поэтому, как только буква «е» будет нажата дальше, нам будет предложено выбрать функцию, которая начинается на эту букву. Выберите «ДА”.
Кроме того, все наши действия также будут сопровождаться подсказками.
В качестве первого аргумента пишем: С2=»Запад». Как и в других функциях Excel, адрес ячейки нельзя ввести вручную, достаточно кликнуть по нему мышкой. Затем ставим «;» и укажите второй аргумент.
Второй аргумент — это значение, которое примет ячейка G2, если будет выполнено записанное нами условие. Это будет слово «Местный”.
После этого снова через запятую укажите значение третьего аргумента. Ячейка G2 примет это значение, если не выполнено условие: «Экспорт». Не забудьте закончить ввод формулы, закрыв скобки и нажав «Enter”.
Наша функция выглядит так:
=ЕСЛИ(C2=»Запад»,»Местный»,»Экспорт»)
Наша ячейка G2 настроена как «Местные жители».
Теперь нашу функцию можно скопировать во все остальные ячейки столбца G.
А если один из параметров не заполнен?
Если вас не интересует, что произойдет, например, если не будет выполнено интересующее вас условие, то вы не можете ввести второй аргумент. Например, мы предлагаем скидку 10% при заказе более 100 товаров. Мы не указываем никаких аргументов для случая, когда условие не выполняется.
=ЕСЛИ(Е2>100,F2*0,1)
Каков будет результат?
Насколько это красиво и удобно, судить вам. Я думаю, что лучше использовать оба аргумента.
А если второе условие не выполняется, а делать ничего не нужно, вставьте в ячейку пустое значение.
=ЕСЛИ(Е2>100,F2*0,1;»»)
Однако такую конструкцию можно использовать, если значение «Истина» или «Ложь» будет использоваться другими функциями Excel в качестве логических значений.
Также обратите внимание, что логические значения, полученные в ячейке, всегда центрированы. Это видно на скриншоте выше.
Кроме того, если вам действительно нужно просто проверить какое-то условие и получить «Истина» или «Ложь» («Да» или «Нет»), вы можете использовать следующую конструкцию –
=ЕСЛИ(Е2>100,ИСТИНА,ЛОЖЬ)
Обратите внимание, что кавычки здесь не обязательны. Если вы заключите аргументы в кавычки, функция ЕСЛИ вернет текстовые значения, а не логические значения.
Давайте посмотрим, как еще можно использовать функцию ЕСЛИ.
Использование функции ЕСЛИ с числами.
Как и в случае с текстом, в аргументах функций можно использовать числа.
Однако для нас важно то, что функция ЕСЛИ позволяет не только заполнять ячейки определенными числовыми значениями в зависимости от условия, но и производить некоторые вычисления.
Например, мы предлагаем нашим клиентам скидку в зависимости от суммы покупки. Если количество больше 100, то вы получаете скидку 10%.
Назовем столбец H «Скидка» и в ячейку H2 введем функцию ЕСЛИ, вторым аргументом которой будет формула расчета скидки.
=ЕСЛИ(Е2>100,F2*0,1,0)
Функция ЕСЛИ: примеры с несколькими условиями.
Итак, мы выяснили, как работает эта одна из самых часто используемых функций. Обычная формула ЕСЛИ, проверяющая одно условие, очень проста и удобна в написании.
Но что, если ваши данные требуют более сложных логических проверок с несколькими условиями? В этом случае в одну формулу можно включить несколько функций ЕСЛИ, и это будет называться вложенными условиями, своего рода «ЕСЛИ в ЕСЛИ». Самым большим преимуществом этого подхода является то, что он позволяет вам тестировать более одного условия и возвращать разные значения на основе результатов этих тестов, и все это с помощью одной формулы.
Вот типичный пример «ЕСЛИ в СИ». Предположим, у вас есть список учащихся в столбце A электронной таблицы Excel и их результаты тестов в столбце B. Вы хотите ранжировать баллы в соответствии со следующими критериями:
- «Отлично»: более 249 баллов
- «Хорошо»: от 249 до 200 включительно
- «Удовлетворительно»: от 199 до 150 включительно
- «Плохо» — до 150.
Теперь давайте напишем вложенную функцию ЕСЛИ на основе вышеуказанных критериев. Рекомендуется начинать с самого важного условия и максимально упростить его функции. Наша вложенная формула ЕСЛИ в Excel выглядит так:
=IF(B2>249, «Отлично», SI(B2>=200, «Хорошо», SI(B2>150, «Удовлетворительно», «Плохо»)))
Многие считают, что вложенные условия слишком сложны. Попробуйте посмотреть на это под другим углом:
=ДА(B2>249; «Отлично»;
ЕСЛИ(B2>=200; «Хорошо»;
ЕСЛИ(В2>150; «Удовлетворительно», «Плохо»)))
Что формула на самом деле говорит Excel, так это выполнить логический тест для первого условия и, если оно истинно, вернуть значение, указанное в аргументе ИСТИНА. Если условие первой проверки не выполняется, проверяют второе выражение и так далее.
ДА (проверьте, если B2 >= 249, если TRUE вернуть «отлично», иначе
ЕСЛИ (проверьте, если B2 >= 200, если TRUE, верните «ok», иначе
ЕСЛИ (проверить, если B2 > 150, если ИСТИНА — вернуть «Успешно», если ЛОЖЬ —
вернуть «Плохо»)))
Вложенные условия с математическими выражениями.
Вот еще одна типичная проблема: цена единицы товара меняется в зависимости от его количества. Ваша цель — написать формулу, вычисляющую цену любого количества товаров, введенных в определенную ячейку. Другими словами, ваша формула должна проверять различные условия и выполнять различные вычисления в зависимости от диапазона сумм, в который попадает указанная сумма.
Эту задачу также можно решить с помощью некоторых вложенных функций ЕСЛИ. Логика такая же, как и в предыдущем примере, с той лишь разницей, что указанное количество умножается на значение, возвращаемое вложенными условиями (то есть на соответствующую цену за единицу).
Предполагая, что количество записано в 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 позволяет использовать более сложные конструкции. Можно также использовать несколько условий. В то же время мы также воспользуемся тем, что функции могут быть «встроены» друг в друга.
Чтобы объединить несколько условий в одно, мы используем логические функции ИЛИ и И. Рассмотрим простые примеры.
Пример 1
Функция ИЛИ возвращает ИСТИНА, если хотя бы одно из нескольких перечисленных в ней условий истинно.
=ЕСЛИ(ИЛИ(C2=»Восток»,C2=»Юг»),»Экспорт»,»Местный»)
Вставляем функцию ИЛИ в качестве условия в функцию ЕСЛИ. В нашем случае, если регион покупателя Восточный или Южный, то посылка считается экспортной.
Пример 2.
Мы используем более сложные условия внутри функции ЕСЛИ.
Если регион продажи Запад или Юг, а количество более 100 штук, предоставляется скидка 10%.
=ЕСЛИ(И(ИЛИ(C2=»Запад»,C2=»Юг»);E2>100);F2*0.1;0)
Функция И возвращает ИСТИНА, если все перечисленные в ней условия истинны. Внутри функции AND мы ставим два условия:
- Регион — Запад или Юг
- Число больше 100.
Первую из них реализуем так же, как и в первом примере: ИЛИ(C2=»Запад», C2=»Юг»)
Второе — тут все очень просто: E2>100
В строках 2, 3 и 5 выполняются оба условия. Эти покупатели получат скидку.
Строка 4 не выполнялась. А в строке 6,7,8 выполняется только первый, но номер слишком мал. Следовательно, скидка будет равна нулю.
Пример 3.
Конечно, эти несколько условий могут быть более сложными. Ведь логические функции можно «обратить» друг в друга.
Например, в дополнение к вышеуказанному условию скидка действует только на темный шоколад.
Все наше ранее написанное условие становится, в свою очередь, первым аргументом новой функции И:
- Регион — Запад или Юг и число больше 100 (рассматривается в примере 2)
- В названии шоколада появляется слово «черный».
В итоге получаем формулу ЕСЛИ с несколькими условиями:
=ЕСЛИ(И(ЧИСЛО(НАЙТИ(«Черный»,D2)),
И(ИЛИ(C2=»Запад»;C2=»Юг»));E2>100);F2*0.1;0)
Функция НАЙТИ ищет точное совпадение. Если нам не важен регистр символов в тексте, вместо НАЙТИ можно использовать аналогичную функцию ТОЧНО.
=ЕСЛИ(И(ЧИСЛО(ТОЧНОЕ(«черный»,D2));
И(ИЛИ(C2=»Запад»;C2=»Юг»));E2>100);F2*0.1;0)
В результате количество вложенных условий в Excel может быть очень большим. Важно лишь строго соблюдать логическую последовательность его выполнения.
Производим вычисления по условию.
Чтобы выполнить действие только тогда, когда ячейка не пуста (содержит какое-то значение), можно использовать формулу на основе функции ЕСЛИ.
В следующем примере столбец F содержит даты окончания покупок шоколада.
Поскольку даты для Excel — это числа, наша задача — проверить наличие числа в ячейке.
Формула в ячейке F3:
=ЕСЛИ(СЧЕТ(D3:D9)=7,СУММ(C3:C9);»»)
Как работает эта формула?
Функция COUNTA подсчитывает количество значений (текстовых, числовых и логических) в диапазоне ячеек Excel. Если мы знаем количество значений в диапазоне, мы можем легко задать условие. Если количество значений равно количеству ячеек, то пустых ячеек нет и можно производить расчет. Если равенства нет, то есть хотя бы одна пустая ячейка и вычисление невозможно.
Согласитесь, что этот способ определения наличия пустых ячеек нельзя назвать удобным. Ведь количество строк в таблице может измениться, и формулу придется изменить: вместо цифры 7 поставить другую цифру.
Давайте рассмотрим другие варианты. Ячейка F6 содержит большую формулу -3
= ЕСЛИ (ИЛИ (ПУСТО (D3), ПУСТО (D4), ПУСТО (D5), ПУСТО (D6);
BBПУСТО(D7);BBПУСТО(D8);BBПУСТО(D9));»»;СУММ(C3:C9))
Функция ЕПУСТО проверяет, относится ли оно к пустой ячейке. Если это так, он возвращает ИСТИНА.
Функция ИЛИ (англ вариант — ИЛИ) позволяет нам объединять условия и указывать, что нам достаточно, чтобы хотя бы одна функция ПУСТО определяла пустую ячейку. В этом случае мы не производим никаких вычислений и функция ЕСЛИ возвращает пустую строку. В противном случае производим расчеты.
Все довольно просто, но перечислять кучу ссылок на ячейки не очень удобно. Также здесь, как и в предыдущем случае, формула не масштабируема: при изменении таблицы ее приходится корректировать. Это не очень удобно, и вы можете забыть это сделать.
Теперь рассмотрим более универсальные решения.
=ЕСЛИ(СЧИТАТЬПУСТОТЫ(D3:D9),»»;СУММ(C3:C9))
В качестве условия в функции ЕСЛИ мы используем СЧИТАТЬПУСТОТЫ. Возвращает количество пустых ячеек, но Excel интерпретирует любое число больше 0 как ИСТИНА.
И, наконец, еще одна формула Excel, которая позволит вам произвести расчет, только если есть непустые ячейки.
=ЕСЛИ(ЧИСЛО(D3:D9),СУММ(C3:C9);»»)
Функция ЕЧИСЛО (или ЕЧИСЛО) возвращает ИСТИНА, если она ссылается на число. Естественно, при ссылке на пустую ячейку возвращается FALSE.
А теперь давайте посмотрим, как это работает. Дополните таблицу недостающим значением.
Как видите, все наши формулы рассчитаны и возвращают одни и те же значения.
А теперь посмотрим, как проверить, что ячейки не пустые, если в них могут быть не только числа, но и текст.
Итак, у нас есть знакомая формула
=ЕСЛИ(СЧЕТ(D3:D9)=7,СУММ(C3:C9);»»)
Для функции COUNTA не имеет значения, используется ли число или текст в ячейке Excel.
=ЕСЛИ(СЧИТАТЬПУСТОТЫ(D3:D9),»»;СУММ(C3:C9))
То же самое можно сказать и о функции СЧИТАТЬПУСТОТЫ.
А вот и третий вариант: для проверки условия с помощью функции ISNUMBER добавьте проверку ETEXT (ISTEXT в английской версии). Объединяем их функцией ИЛИ.
=ЕСЛИ(ИЛИ(ETEXT(D3:D9),ЧИСЛО(D3:D9)),СУММ(C3:C9);»»)
А теперь вставляем недостающее значение в ячейку D5 и проверяем, все ли работает.
Итак, мы увидели, что кажущаяся простой функция ЕСЛИ на самом деле дает нам множество возможностей для операций с данными.
Надеемся, что этот материал был полезен. А вот еще несколько примеров работы с условиями и функцией ЕСЛИ в Excel.