Функция ЕСЛИ одна из самых популярных и часто используемых функций Excel. Используя ее совместно с операторами сравнения и другими логическими функциями Excel, можно решать достаточно сложные задачи. В этом уроке мы попробуем разобрать ее действие на простых примерах, а также научимся использовать сразу несколько функций ЕСЛИ в одной формуле.
Коротко о синтаксисеФункция ЕСЛИ имеет всего три аргумента:
=ЕСЛИ(заданное_условие; значение_если_ИСТИНА; значение_если_ЛОЖЬ)
Первый аргумент – это условие, благодаря которому формула может принимать решения. Условие проверяется в самую первую очередь и способно вернуть всего два значения – ИСТИНА или ЛОЖЬ. Если условие истинно, то формула вернет второй аргумент, в противном случае третий.
О том, как задавать условия в Excel, читайте статьи: Как задать простое логическое условие в Excel и Используем логические функции Excel для задания сложных условий .
Пример 1Обратимся к примеру, приведенному на рисунках ниже. В данном примере функция ЕСЛИ в первую очередь проверят условие A1>25 . Если это так, то формула возвратит текстовую строку “больше 25”, в любом другом случае – “меньше или равно 25”.
Функция ЕСЛИ является очень гибкой и ее можно применять в различных ситуациях. Рассмотрим еще один пример. В таблице ниже приведены результаты переаттестации сотрудников фирмы:
В столбец C нам необходимо выставить результат экзамена, который должен содержать всего два варианта: Сдал или Не сдал . Те, кто набрал более 45 баллов – сдали экзамен, остальные нет.
Функция ЕСЛИ и несколько условий
Функции ЕСЛИ можно вкладывать друг в друга, если необходимо расширить варианты принятия решений в Excel. Например, для рассмотренного ранее случая переаттестации сотрудников, требуется проставить не результат, а оценку из ряда: Отлично, Хорошо и Плохо. Оценка Отлично ставится при количестве баллов более 60, оценка Хорошо при более 45 и оценка Плохо в остальных случаях.
Как видите, вместо второго и третьего аргументов функции ЕСЛИ можно подставлять новые функции ЕСЛИ , тем самым расширяя число условий, которое формула может обработать. Таким образом, Вы можете создать нужное количество вложений. Правда есть очевидный недостаток такой конструкции, после 3-5 вложений формула станет нечитаемой и громоздкой, и с ней будет невозможно работать.
В Excel существуют более благородные инструменты для обработки большого количества условий, например, функция ВПР или ПРОСМОТР .
Итак, в этом уроке мы рассмотрели логическую функцию ЕСЛИ во всей ее красе и примерах, а также разобрали простой пример с использованием сразу нескольких функций ЕСЛИ в одной формуле. Надеюсь, что эта информация была для Вас полезной. Удачи Вам и больших успехов в изучении Microsoft Excel!
Сегодня мы расскажем о функции табличного редактора Excel «Если». Она имеет отношение к логическим возможностям приложения. Ее можно отнести к наиболее востребованным функциям во время работы.
Ключевые возможностиВ Excel формула «Если» помогает выполнять разного рода задачи, когда требуется сравнить определенные данные и получить результат. Данное решение дает возможность применять ветвящиеся алгоритмы, а также создавать дерево решений.
Примеры использования
Функция выглядит таким образом: =ЕСЛИ (задача; истина; ложь). Первая составная часть — логическое выражение. Оно может быть фразой или числом. К примеру, «10» или «без НДС» — это логические выражения. Данный параметр обязательно необходимо заполнить. Истина — это значение, которое отобразится как результат, если выражение будет верным. Ложь — данные, которые будут выданы, когда задача не будет верной.
Равенство параметров двух ячеек
Для понимания возможностей функции «Если» в Excel, примеры просто необходимы, и далее мы перейдем к их рассмотрению. Вводим в клетку C1 показатель 8. Далее в поле по адресу D1 вписываем следующую формулу: =ЕСЛИ(C1, =, =6; B1>10000); «проблемный клиент»; «»). Отсюда следует, что если будет обнаружен человек, который соответствует указанным условиям, программа укажет напротив его имени требуемый комментарий. Для всех прочих участников перечня аналогичная клетка останется пустой. Рассмотрим пример для случая, когда ситуация является критической. Введем соответствующий комментарий. В результате формула будет иметь следующий вид: =ЕСЛИ(ИЛИ(A1>=6; B1>10000); «критическая ситуация»; «»). В таком случае если программа обнаружит совпадения как минимум по одному из параметров (срок, сумма задолженности), пользователь увидит соответствующее примечание. В первой ситуации сообщение «проблемный клиент» выдавалось лишь тогда, когда были выполнены оба заданных условия.
Задачи высокого уровня сложности
Функция табличного редактора Excel «Если» используется, чтобы обойти встроенные ошибки при а также еще в нескольких случаях. Первая ситуация обозначается редактором, как «ДЕЛ/0» и встречается достаточно часто. Как правило, она возникает в тех случаях, когда подлежит копированию формула «A/B», при этом показатель B в отдельных ячейках равен нулю. Избежать этого можно посредством возможностей рассматриваемого нами оператора. Итак, необходимая формула будет иметь следующий вид: =ЕСЛИ(B1=0; 0; A1/B1). Отсюда следует, что если клетка B1 будет заполнена параметром «ноль», редактор выдаст «0», в обратном случае Excel поделит показатель A1 на данные B1 и отобразит результат.
СкидкаНа практике часто встречается и ситуация, которая будет рассмотрена далее. Речь идет о расчете скидки, исходя из общей суммы средств, потраченных на приобретение определенного товара. Используемая в этом случае матрица может иметь следующий вид: менее 1000 — 0%; 1001-3000 — 3%; 3001-5000 — 5%; более 5001 — 7%. Рассмотрим ситуацию, когда в Excel присутствует база данных посетителей, а также информация о сумме потраченных ими на покупки средств. Теперь необходимо рассчитать скидку для каждого клиента. С этой целью используем следующее выражение: =ЕСЛИ(A1>=5001; B1*0,93; ЕСЛИ(А1>=3001; B1*0,95;..). Система проверяет общую сумму покупок. Когда она превышает показатель в 5001 рублей, происходит умножение на 93 процента стоимости товара. В случае преодоления отметки в 3001 единицу, имеет место аналогичное действие, но уже с учетом 95%. Приведенную формулу с легкостью можно применять на практике. Объем продаж и показатели скидок задаются по усмотрению пользователя.
Применение описанных возможностей возможно для решения различного рода задач. Основной этап — правильное составление формулы, чтобы не получить ошибочного результата. Теперь вы знаете, как использовать оператор в Excel, если несколько условий в задаче.
Программа Excel, безусловно, обладает очень богатым функционалом. И среди множества разнообразных инструментов особое место занимает оператор «ЕСЛИ». Он помогает в решении совершенно различных задач, и к этой функции пользователи обращаются намного чаще, чем к остальным.
В этой статье мы поговорим о том, что же такое оператор «ЕСЛИ», а также рассмотрим область применения и принципы работы с ним.
Определение функции “ЕСЛИ” и ее назначениеОператор «ЕСЛИ» – это инструмент программы Эксель для проверки определенного условия (логического выражения) на выполнение.
То есть, представим, что у нас есть какое-то условие. Задача “ЕСЛИ” – проверить соблюдение данного условия и вывести в ячейку с функцией значение, основанное на результате проверки.
Сама формула функции в программе представляет собой следующее выражение:
=ЕСЛИ(условие; [значение если условие выполнено]; [значение если условие не выполнено])
Использование функции “ЕСЛИ” на примереВероятно, вышеизложенная информация может показаться не такой понятной. Но, на самом деле ничего сложного здесь нет. И для того, чтобы лучше понять назначение функции и ее работу рассмотрим пример ниже.
У нас есть таблица с наименованиями спортивной обуви. Представим, что скоро у нас будет распродажа, и всей женской обуви нужно проставить скидку 25%. В одном из столбцов в таблице как раз прописан пол для каждого наименования.
Наша задача - в столбце “Скидка” вывести значение «25%» для всех строк с наименованиями для женского пола. И соответственно, значение «0», если в столбце «Пол» стоит значение «мужской»
Заполнение данных вручную займет немало времени, и есть высокая доля вероятности где-то ошибиться, особенно, если список длинный. Гораздо проще в этом случае будет автоматизировать процесс при помощи оператора «ЕСЛИ».
Для выполнения этой задачи потребуется составить следующую формулу ниже:
=ЕСЛИ(B2=”женский”;25%;0)
- Логическое выражение: B2=”женский”
- Значение в случае, условие выполняется (истина) – 25%
- Значение, если условие не выполняется (ложь) – 0.
Данную формулу пишем в самой верхней ячейке столбца “Скидка” и нажимаем Enter. Не забываем ставить знак равно (=) перед формулой.
После этого для данной ячейки будет отображен результат согласно нашему логическому условию (на забываем настроить формат ячеек – процентный). Если проверка выявит, что пол «женский» – будет выведено значение 25%. В противном случае значение ячейки будет равно 0. Собственно говоря, что нам и требовалось.
Теперь осталось только скопировать данное выражение на все строки. Для этого подведите курсор мыши к правому нижнему краю ячейки с формулой. Указатель мышки при этом должен превратиться в крестик. Зажмите левую кнопку мыши и растяните формулу на все строки, которые нужно проверить согласно заданным условиям.
Вот и все, теперь мы применили условие ко всем строкам и получили результат для каждой из них.
Мы только что рассмотрели пример использования оператора «ЕСЛИ» с одним логическим выражением. Но в программе также имеется возможность задавать больше одного условия. При этом сначала будет проводиться проверка по первому, и в случае его успешного выполнения сразу отобразится заданное значение. И только если не будет выполнено первое логическое выражение, в силу вступит проверка по второму.
Рассмотрим наглядно на примере все той же таблицы. Но на этот раз усложним задачу. Теперь нужно проставить скидку на женскую обувь в зависимости от вида спорта.
Первое условия – это проверка пола. Если “мужской” – сразу выводится значение 0. Если же это “женский”, то начинается проверка по второму условию. Если вид спорта бег – 20%, если теннис – 10%.
Пропишем формулу для этих условий в нужной нам ячейке.
=ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%))
Щелкаем Enter и получаем результат согласно заданным условиям.
Также в Эксель существует возможность вывести данные по одновременному выполнению двух условий. При этом значение будет считаться ложным, если хотя бы одно из условий не выполнено. Для этой задачи применяется оператор «И» .
Рассмотрим на примере нашей таблицы. Теперь скидка 30% будет проставлена только, если это женская обувь и предназначена для бега. При соблюдении этих условий одновременно значение ячейки будет равно 30%, в противном случае – 0.
Для этого используем следующую формулу:
=ЕСЛИ(И(B2=”женский”;С2=”бег”);30%;0)
Нажимаем клавишу Enter, чтобы отобразить результат в ячейке.
Аналогично примерам выше, растягиваем формулу на остальные строки.
В этом случае значение логического выражения считается истинным при выполнении одного из условий. Второе условие при этом может не выполняться.
Поставим задачу следующим образом. Скидка 35% предоставляется только на мужскую обувь для тенниса. Если это мужская обувь для бега или любая женская обувь, скидка равна 0.
В данном случае нужна следующая формула:
=ЕСЛИ(ИЛИ(B2=”женский”; C2=”бег”);0;35%)
После нажатия Enter мы получим требуемое значение.
Растягиваем формулу вниз и скидки для всего ассортимента готовы.
Использовать функцию ЕСЛИ можно не только прописывая ее вручную в ячейке или строке формул, но также и через Построитель формул.
Давайте разберем, как это работает. Допустим, нам снова как и в первом примере нужно проставить скидку на всю женскую обувь в размере 25%.
Заключение
Одним из самых популярных и полезных инструментов в Excel является функция ЕСЛИ , которая проверяет данные на совпадение заданным нами условиям и выдает результат в автоматическом режиме, что исключает возможность ошибок из-за человеческого фактора. Поэтому, знание и умение применять этот инструмент позволит сэкономить время не только на выполнение многих задач, но и на поиски возможных ошибок из-за “ручного” режима работы.
Excel для Office 365 Excel для Office 365 для Mac Excel для Интернета Excel 2019 Excel 2016 Excel 2019 для Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 для Mac Excel для Mac 2011 Excel Web App Excel 2007 для разработчиков Excel 2010 для разработчиков Excel 2013 для разработчиков Excel для Windows Phone 10 Excel Starter 2010 Меньше
Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.
ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)
Но что делать, если необходимо проверить несколько условий, где, допустим, все условия должны иметь значение ИСТИНА или ЛОЖЬ (И ), только одно условие должно иметь такое значение (ИЛИ ) или вы хотите убедиться, что данные НЕ соответствуют условию? Эти три функции можно использовать самостоятельно, но они намного чаще встречаются в сочетании с функцией ЕСЛИ.
Технические сведения
Используйте функцию ЕСЛИ вместе с функциями И, ИЛИ и НЕ, чтобы оценивать несколько условий.
Синтаксис
ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))
ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))
ЕСЛИ(НЕ()): ЕСЛИ(НЕ(лог_выражение1), значение_если_истина; [значение_если_ложь]))
Общие сведения об использовании этих функций по отдельности см. в следующих статьях: , ИЛИ , НЕ . При сочетании с оператором ЕСЛИ они расшифровываются следующим образом:
И : =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
ИЛИ : =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
НЕ : =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
Ниже приведены примеры распространенных случаев использования вложенных операторов ЕСЛИ(И()), ЕСЛИ(ИЛИ()) и ЕСЛИ(НЕ()). Функции И и ИЛИ поддерживают до 255 отдельных условий, но рекомендуется использовать только несколько условий, так как формулы с большой степенью вложенности сложно создавать, тестировать и изменять. У функции НЕ может быть только одно условие.
|
ЕСЛИ(И(A2>0;B20;B450);ИСТИНА;ЛОЖЬ) |
Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА. |
|
ЕСЛИ(НЕ(A7="красный");ИСТИНА;ЛОЖЬ) |
Если значение A7 ("синий") НЕ равно "красный", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. |
Обратите внимание, что во всех примерах есть закрывающая скобка после условий. Аргументы ИСТИНА и ЛОЖЬ относятся ко внешнему оператору ЕСЛИ. Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах.
Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.
Ниже приведены формулы с расшифровкой их логики.
|
ЕСЛИ(A2>B2;ИСТИНА;ЛОЖЬ) |
Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА. |
|
ЕСЛИ(И(A3>B2;A3B2;A4B2);ИСТИНА;ЛОЖЬ) |
Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ. |
Использование операторов И, ИЛИ и НЕ с условным форматированием
Вы также можете использовать операторы И, ИЛИ и НЕ в формулах условного форматирования. При этом вы можете опустить функцию ЕСЛИ.
На вкладке Главная выберите Условное форматирование > Создать правило . Затем выберите параметр Использовать формулу для определения форматируемых ячеек , введите формулу и примените формат.
Вот как будут выглядеть формулы для примеров с датами:
|
Если A2 больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий. |
|
|
И(A3>B2;A3B2;A4A5) , она вернет значение ИСТИНА, а ячейка будет отформатирована. |
Примечание: Наиболее распространенная ошибка заключается в том, чтобы ввести формулу в условное форматирование без знака равенства (=). Если вы сделаете это, вы увидите, что в диалоговом окне "условное форматирование" добавляется знак равенства и кавычки к формуле = = "или (a4>B2; a4