Одна из самых популярных функций в приложении Excel – ЕСЛИ . Это логическое сравнение значения и получаемого результата. Если говорить проще, то функция может звучать так:
ЕСЛИ условие истинно, то выполняем это, а иначе делаем что-либо еще
Синтаксис этой функции достаточно прост:
ЕСЛИ (логическое_условие; значение_в_случае_ИСТИНА; значение в случае_ЛОЖЬ)
Разберем подробнее:
- Логическое_условие – значение, которое будем проверять
- Значение_в_случае_ИСТИНА – действие, когда требование выполняется
- Значение в случае_ЛОЖЬ – действие, когда Логическое_условие не выполняется
На скриншотах ниже показан самый просто вариант использования. Мы сначала проверяем
заданное условие А1>25
. ЕСЛИ это требование выполняется
, тогда выводим в ячейку «больше 25
», иначе «меньше или равно 25
».
Теперь более сложное задание. Ниже мы рассмотрим пример переаттестации сотрудников предприятия. Изначально таблица выглядит так:
Нам необходимо в столбце С вывести результаты переаттестации, которые могут принимать бинарное значение: СДАЛ или НЕ СДАЛ . Критерии у нас будут такими: кто набрал более 45 балов , тот считается сдавшим экзамен, ну а все остальные нет.
Для выполнения задачи необходимо :
Используем несколько условий
При необходимости, функцию ЕСЛИ можно вложить друг в друга. Таким образом, мы расширим варианты решений.
Для примера, возьмем переаттестацию сотрудников, которую рассмотрели раньше. Изменим критерии результата и выставим каждому оценку : Плохо, Хорошо и Отлично. Отлично будем ставить, когда баллы превысят 60 . Оценку Хорошо можно будет получить, набрав от 45 до 60 балов . Ну и в остальных случаях ставим Плохо .
Как видно из примера, вместо второго и третьего значения функции можно подставлять условие. Таким способом добавляем необходимое число вложений. Однако стоит отметить , что после добавления 3-5 вложений работать с формулой станет практически невозможно, т.к. она будет очень громоздкой.
Другие варианты использования функции Находим сумму столбцов или ячеекК примеру, есть необходимость просуммировать каждую вторую ячейку столбца. В этом случае поможет использование оператора СУММЕСЛИ
. Он позволит работать только с теми столбцами, которые попадают под наши требования. Мы будет рассматривать случай, когда необходимо суммировать элементы в нечетных строках.
Для решения задачи, нам потребуется вставить дополнительный столбец и пронумеровать строки 1 и 0. Именно эти данные мы будем использовать в нашей формуле. В примере видно, какую формулу мы использовали.
Синтаксис : СУММЕСЛИ(диапазон_ячеек; используемое_условие; [диапазон_суммирования])
- Диапазон – обязательный аргумент. Тут задается диапазон, который оценивается на соответствие условию.
- Используемое_условие – тут указываем, какие именно ячейки необходимо суммировать.
- Диапазон_суммирования – указываем, какой именно диапазон использовать для суммирования.
В этом случае мы можем посчитать, сколько раз заданный элемент встречается на листе. Для этого используем функцию СЧЕТЕСЛИ
. Она считает количество ячеек, которые совпадают
с заданным значением.
В этом примере мы считаем, сколько клиентов находится в каждом городе. В формуле мы задаем первым диапазон, а вторым значения, которые следует искать.
Используем СЧЕТЕСЛИ и СУММЕСЛИ при подсчете данныхВ примере мы постараемся определить усредненный объем дохода
от клиентов по каждому городу. Для этого поделим суммарный доход города на количество клиентов.
Если у нас есть постоянно обновляемый список данных, в котором по мере роста могут появляться дубликаты , то поиск вхождений в этом списке может оказаться затруднительным. Для решения этой задачи проще всего воспользоваться функцией ВПР и СЧЕТЕСЛИ .
Для начала, добавим
дополнительный столбец, который добавить порядковый номер вхождения к имени клиента. В результате, первое упоминание элемента будет выдавать Имя1, следующее Имя2 и т.д.
Используем поиск по клиенту Кристина Агилера. Используя формулу ВПР («Кристина Агилера3», диапазон_поиска, 2, ЛОЖЬ), мы получим третий номер этого клиента. В формуле, последним значением ставим ЛОЖЬ, поскольку список не сортирован, и нам необходимо точное совпадение элементов.
Как сократить количество вложений ЕСЛИВерсии до EXCEL 2003 включительно поддерживали до 7 уровней вложенности ЕСЛИ. Начиная с версии Excel 2007, это ограничение убрали . Но стоит заметить, что ниже 3-4 уровня вложенности мало кто опускается.
Для того чтобы уменьшить количество использования вложений ЕСЛИ, можно использовать функцию ВЫБОР . Она работает со значениями или действиями из заданного списка по номеру индекса.
Синтаксис : ВЫБОР (номер_индекса; значение_индекса1; значение_индекса2; [значение_индекса3];…)
Для примера, функция ВЫБОР (1; «Третий»; «Второй»; «Первый») , вернет нам слово «Третий », если мы ее добавим в ячейку.
Стоит отметить, что имеются некоторые ограничения. В частности, в качестве индекса могут выступать только числа
.
Применяется к: Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel 2016 для Mac Excel для Mac 2011 Excel Online Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel Starter 2010 Excel 2013 для разработчиков Excel 2007 для разработчиков Excel 2010 для разработчиков Excel Mobile Excel для Windows Phone 10 Excel для телефонов с Android Меньше
Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.
ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)
Но что делать, если необходимо проверить несколько условий, где, допустим, все условия должны иметь значение ИСТИНА или ЛОЖЬ (И ), только одно условие должно иметь такое значение (ИЛИ ) или вы хотите убедиться, что данные НЕ соответствуют условию? Эти три функции можно использовать самостоятельно, но они намного чаще встречаются в сочетании с функцией ЕСЛИ.
Используйте функцию ЕСЛИ вместе с функциями И, ИЛИ и НЕ, чтобы оценивать несколько условий.
Синтаксис
ЕСЛИ(И()): ЕСЛИ(И(лог_выражение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