Что делает функция ЕСЛИ?
Он позволяет вам создать дерево решений, в котором при выполнении определенного условия происходит определенное действие. И если это условие не выполняется, предпринимается другое действие.
В этом случае аргументом функции должен быть вопрос, на который есть 2 возможных ответа: «да» и «нет», «истина» или «ложь».
Вот как может выглядеть это дерево решений.
Затем функция ЕСЛИ позволяет задать вопрос и указать 2 варианта расчетов в зависимости от ответа. Это три аргумента функции.
Синтаксис функции ЕСЛИ
Вот как выглядит синтаксис этой функции и ее аргументов:
= ЕСЛИ (логическое выражение, значение, если да, значение, если нет»)
Логическое выражение — это (обязательное) условие, которое возвращает истину или ложь (да или нет»);
Значение, если «да» — действие (обязательное), которое выполняется в случае положительного ответа;
Значение, если «нет» — действие (обязательное), которое выполняется в случае отрицательного ответа;
Давайте вместе рассмотрим эти темы подробнее.
Первый аргумент — логичный вопрос. И этот ответ может быть только «да» или «нет», «правда» или «ложь».
Как правильно задать вопрос? Для этого можно составить логическое выражение, используя знаки «=», «>», « =», «». Попробуем вместе задать этот вопрос.
Простейший пример применения.
Допустим, вы работаете в компании, которая продает шоколад в разных регионах и работает с большим количеством клиентов.
Отдельно стоит выделить продажи, которые происходили в нашем регионе и за рубежом. Для этого вам нужно добавить в таблицу для каждой продажи еще одну характеристику — страну, в которой она произошла. Мы хотим, чтобы этот тег автоматически генерировался для каждой записи (то есть строки).
В этом нам поможет функция SE. Добавим в таблицу данных столбец «Страна». Западный регион — это местные продажи («Местные»), а остальные регионы — зарубежные продажи («Экспорт»).
Как правильно записать?
Установите курсор в ячейку G2 и введите знак «=». Для Excel это означает, что формула будет вставлена. Поэтому, как только буква «е» будет нажата дальше, мы получим предложение выбрать функцию, которая начинается с этой буквы. Выбираем «СЭ”.
Кроме того, все наши действия также будут сопровождаться предложениями.
Пишем в качестве первого аргумента: C2 = «Запад». Как и в других функциях Excel, здесь нет необходимости вручную вводить адрес ячейки, достаточно просто щелкнуть по ней мышью. Поэтому мы ставим «» и указываем второй аргумент.
Второй аргумент — это значение, которое примет ячейка G2, если условие, которое мы отметили, выполнено. Это будет слово «Местный”.
Далее мы снова указываем значение третьего аргумента через запятую. Это значение будет принято ячейкой G2, если условие не выполнено: «Экспорт». Не забудьте завершить ввод формулы, закрыв круглые скобки и нажав «Enter”.
Наша функция выглядит так:
= IF (C2 = «Запад», «Местный», «Экспорт»)
Наша ячейка G2 настроена на Local».
Теперь нашу функцию можно скопировать во все остальные ячейки в столбце G.
А если один из параметров не заполнен?
Если вас не интересует, что произойдет, например, если интересующее вас условие не выполняется, вы можете опустить второй аргумент. Например, мы предоставляем скидку 10% при заказе более 100 наименований. Мы не указываем никаких аргументов в случае невыполнения условия.
= ЕСЛИ (MI2> 100; F2 * 0,1)
Что будет в результате?
Насколько красиво и удобно — судить вам. Я думаю, что в любом случае лучше использовать оба аргумента.
А если второе условие не выполняется, но ничего делать не нужно, введите в ячейку пустое значение.
= ЕСЛИ (MI2> 100; F2 * 0,1,»»)
Однако эту конструкцию можно использовать в случае, если значение «Истина» или «Ложь» используется другими функциями Excel в качестве логических значений.
Также обратите внимание, что результирующие логические значения в ячейке всегда центрируются. Это видно на скриншоте выше.
Кроме того, если вам действительно нужно проверить некоторые условия и получить «Истина» или «Ложь» («Да» или «Нет»), вы можете использовать следующую конструкцию –
= ЕСЛИ (MI2> 100; ИСТИНА; ЛОЖЬ)
Учтите, что здесь кавычки использовать не нужно. Если вы заключите аргументы в кавычки, функция ЕСЛИ вернет текстовые значения, а не логические значения.
Посмотрим, как еще можно использовать функцию ЕСЛИ.
Использование функции ЕСЛИ с числами.
Как и в случае с текстом, вы можете использовать числа в аргументах функций.
Однако для нас важно, что функция ЕСЛИ позволяет не только заполнять ячейки определенными числовыми значениями в зависимости от выполнения условия, но и производить некоторые вычисления.
Например, мы предоставляем нашему покупателю скидку в зависимости от суммы покупки. Если сумма больше 100 — скидка 10%.
Мы называем столбец H «Скидка», а в ячейку H2 вводим функцию ЕСЛИ, вторым аргументом которой будет формула для расчета скидки.
= ЕСЛИ (E2> 100; F2 * 0,1,0)
Функция ЕСЛИ: примеры с несколькими условиями.
Итак, мы разобрались, как работает эта одна из наиболее часто используемых функций. Распространенную формулу ЕСЛИ, которая проверяет условие, очень просто и легко написать.
Но что, если ваши данные требуют более сложных логических проверок с большим количеством условий? В этом случае вы можете включить в формулу несколько функций ЕСЛИ, и это будет называться вложенными условиями, своего рода «ЕСЛИ в SE». Самым большим преимуществом этого подхода является то, что он позволяет вам тестировать более одного условия и возвращать разные значения на основе результатов этих тестов, все из одной формулы.
Вот типичный пример от IF до IF. Предположим, у вас есть список студентов в столбце A таблицы Excel и их оценки за тест в столбце B. Вы хотите ранжировать их оценки на основе следующих условий:
- «Отлично»: более 249 баллов
- «Хорошо»: с 249 до 200 включительно
- «Удовлетворительно»: от 199 до 150 включительно
- «Плохо»: до 150.
Теперь давайте напишем вложенную функцию ЕСЛИ на основе вышеуказанных критериев. Рекомендуется начать с самого важного условия и максимально упростить функции. Наша вложенная формула SE в Excel выглядит так:
= ЕСЛИ (B2> 249; «Отлично»; ЕСЛИ (B2> = 200; «Хорошо»; ЕСЛИ (B2> 150; «Удовлетворительно»; «Плохо»)))
Многие думают, что вложенные условия слишком сложны. Попробуйте посмотреть на это под другим углом:
= ЕСЛИ (B2> 249; «Отлично»;
ЕСЛИ (B2> = 200; «Хорошо»;
ЕСЛИ (B2> 150; «Удовлетворительно»; «Плохо»)))
Фактически, формула сообщает Excel, что нужно выполнить логический тест для первого условия, и, если оно истинно, возвращает значение, указанное в аргументе ИСТИНА. Если условие первой проверки не выполняется, проверьте второе выражение и так далее.
ЕСЛИ (проверяет, является ли B2> = 249, если ИСТИНА — возвращает «отлично», или
ЕСЛИ (проверяет, если B2> = 200, если ИСТИНА — возвращает «хорошо», или
ЕСЛИ (проверяет, если B2> 150, если ИСТИНА — возвращает «Удовлетворительно», если ЛОЖЬ —
вернуть «Плохо»)))
Вложенные условия с математическими выражениями.
Вот еще одна типичная проблема: цена за единицу продукта меняется в зависимости от количества. Ваша цель — написать формулу, которая вычисляет цену для любого количества предметов, введенных в определенную ячейку. Другими словами, ваша формула должна проверять разные условия и выполнять разные вычисления в зависимости от диапазона суммы, к которой относится указанное количество товаров.
Эту задачу также можно выполнить с помощью нескольких вложенных функций ЕСЛИ. Логика такая же, как в предыдущем примере, с той лишь разницей, что вы умножаете указанное количество на значение, возвращаемое вложенными условиями (то есть на соответствующую цену за единицу).
Предполагая, что количество находится в B8, формула будет выглядеть так:
= L8 * SE (La8> = 101; 12; SE (La8> = 50; 14; SE (La8> = 20; 16; SE (La8> = 11; 18; SE (La8> = 1; 22; «»)))))
И вот результат:
Как вы понимаете, этот пример демонстрирует только общий подход, и вы можете легко настроить эту вложенную функцию на основе конкретного действия.
Например, вместо того, чтобы «кодировать» цены в самой формуле, вы можете ссылаться на ячейки, в которых они появляются (ячейки с B2 по B6). Это позволит вам изменить исходные данные без обновления самой формулы:
= L8 * SE (L8> = 101; L6; SE (L8> = 50; L5; SE (L8> = 20; L4; SE (L8> = 11; L3; SE (L8> = 1; L2; «»)))))
Объединяем несколько условий.
Для описания условия в функции ЕСЛИ Excel позволяет использовать более сложные конструкции. Также можно использовать несколько условий. В этом случае мы также воспользуемся тем, что функции могут быть «вложены» друг в друга.
Чтобы объединить несколько условий в одно, мы используем логические функции ИЛИ и И. Рассмотрим простые примеры.
Пример 1
Функция ИЛИ возвращает ИСТИНА, если хотя бы одно из нескольких перечисленных в ней условий истинно.
= SE (OR (C2 = «Восток», C2 = «Юг»), «Экспорт», «Местный»)
Мы вставляем функцию ИЛИ как условие в функцию ЕСЛИ. В нашем случае, если регион покупателя — Восток или Юг, отгрузка считается экспортной.
Пример 2.
Мы используем несколько более сложные условия в функции ЕСЛИ.
Если регион продаж — Запад или Юг и количество больше 100, предоставляется скидка 10%.
= SE (И (ИЛИ (C2 = Запад, C2 = Юг), E2> 100), F2 * 0,1,0)
Функция И возвращает ИСТИНА, если все перечисленные в ней условия выполнены. Внутри функции И мы устанавливаем два условия:
- Регион — Запад или Юг
- Число больше 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:
= ЕСЛИ (СЧЁТ (RE3: RE9) = 7; СУММ (DO3: DO9),»»)
Как работает эта формула?
Функция COUNT (английская версия — COUNTA) подсчитывает количество значений (текстовых, числовых и логических) в диапазоне ячеек Excel. Если мы знаем количество значений в диапазоне, мы можем легко создать условие. Если количество значений равно количеству ячеек, пустых ячеек нет, и расчет может быть выполнен. Если нет равенства, есть хотя бы одна пустая ячейка, и вы не можете вычислить.
Согласен, такой способ определения наличия пустых ячеек нельзя назвать удобным. Ведь количество строк в таблице может измениться, и формулу нужно будет изменить — вместо числа 7 введите другое число.
Давайте посмотрим и на другие варианты. Ячейка F6 содержит большую формулу -3
= SE (ИЛИ (ISBLANK (D3), ISBILANK (D4), ISBILANK (D5), ISBILANK (D6),
ISBLANK (D7), ISBLANK (D8), ISBLANK (D9)), «», СУММ (C3: C9))
Функция ISBLANK проверяет, ссылается ли он на пустую ячейку. Если да, возвращается ИСТИНА.
Функция OR (англ. Версия — OR) позволяет комбинировать условия и указывать, что нам достаточно, чтобы хотя бы одна функция ISBLANK нашла пустую ячейку. В этом случае мы не выполняем никаких вычислений, а функция ЕСЛИ возвращает пустую строку. Если нет, то займемся расчетами.
Все довольно просто, но перечислять серию ссылок на ячейки не очень удобно. Также здесь, как и в предыдущем случае, формула не масштабируется: при изменении таблицы ее нужно корректировать. Это не очень удобно и можно забыть это сделать.
Рассмотрим теперь более универсальные решения.
= ЕСЛИ (СЧИТАТЬ ПУСТОЙ (D3: D9); «»; СУММ (C3: C9))
В качестве условия в функции ЕСЛИ мы используем СЧИТАТЬПУСТОТЫ (английская версия — СЧИТАТЬПУСТОТЫ). Возвращает количество пустых ячеек, но любое число больше 0 интерпретируется Excel как ИСТИНА.
И, наконец, еще одна формула Excel, которая позволит вам вычислить только наличие непустых ячеек.
= ЕСЛИ (ЕЧИСЛО (D3: D9); СУММ (C3: C9),»»)
Функция ЕЧИСЛО (или ЕЧИСЛО) возвращает ИСТИНА при ссылке на число. Конечно, при ссылке на пустую ячейку он вернет FALSE.
Теперь посмотрим, как это работает. Заполняем таблицу недостающим значением.
Как видите, все наши формулы были рассчитаны и вернули одинаковые значения.
Теперь давайте посмотрим, как проверить, что ячейки не пустые, если в них можно писать не только числа, но и текст.
Итак, перед нами уже знакомая формула
= ЕСЛИ (СЧЁТ (RE3: RE9) = 7; СУММ (DO3: DO9),»»)
Для функции COUNT не имеет значения, используется ли число или текст в ячейке Excel.
= ЕСЛИ (СЧИТАТЬ ПУСТОЙ (D3: D9); «»; СУММ (C3: C9))
То же самое можно сказать и о функции СЧИТАТЬПУСТОТЫ.
И третий вариант: добавим элемент управления ETEXT (ISTEXT в английской версии) в элемент управления условием с помощью функции ISNUMBER. Объединяем их с функцией ИЛИ.
= ЕСЛИ (ИЛИ (ISTEXT (D3: D9); ISNUMBER (D3: D9)); SUM (C3: C9),»»)
Теперь поместим недостающее значение в ячейку D5 и проверим, все ли работает.
Таким образом, мы увидели, что, казалось бы, простая функция ЕСЛИ на самом деле дает нам много возможностей для операций с данными.