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

Коротко о синтаксисе

Функция ЕСЛИ имеет всего три аргумента:

=ЕСЛИ(заданное_условие; значение_если_ИСТИНА; значение_если_ЛОЖЬ)

Первый аргумент – это условие, благодаря которому формула может принимать решения. Условие проверяется в самую первую очередь и способно вернуть всего два значения – ИСТИНА или ЛОЖЬ. Если условие истинно, то формула вернет второй аргумент, в противном случае третий.

О том, как задавать условия в Excel, читайте статьи: Как задать простое логическое условие в Excel и Используем логические функции Excel для задания сложных условий .

Пример 1

Обратимся к примеру, приведенному на рисунках ниже. В данном примере функция ЕСЛИ в первую очередь проверят условие A1>25 . Если это так, то формула возвратит текстовую строку “больше 25”, в любом другом случае – “меньше или равно 25”.

Пример 2

Функция ЕСЛИ является очень гибкой и ее можно применять в различных ситуациях. Рассмотрим еще один пример. В таблице ниже приведены результаты переаттестации сотрудников фирмы:

В столбец C нам необходимо выставить результат экзамена, который должен содержать всего два варианта: Сдал или Не сдал . Те, кто набрал более 45 баллов – сдали экзамен, остальные нет.

Функция ЕСЛИ и несколько условий

Функции ЕСЛИ можно вкладывать друг в друга, если необходимо расширить варианты принятия решений в Excel. Например, для рассмотренного ранее случая переаттестации сотрудников, требуется проставить не результат, а оценку из ряда: Отлично, Хорошо и Плохо. Оценка Отлично ставится при количестве баллов более 60, оценка Хорошо при более 45 и оценка Плохо в остальных случаях.

Как видите, вместо второго и третьего аргументов функции ЕСЛИ можно подставлять новые функции ЕСЛИ , тем самым расширяя число условий, которое формула может обработать. Таким образом, Вы можете создать нужное количество вложений. Правда есть очевидный недостаток такой конструкции, после 3-5 вложений формула станет нечитаемой и громоздкой, и с ней будет невозможно работать.

В Excel существуют более благородные инструменты для обработки большого количества условий, например, функция ВПР или ПРОСМОТР .

Итак, в этом уроке мы рассмотрели логическую функцию ЕСЛИ во всей ее красе и примерах, а также разобрали простой пример с использованием сразу нескольких функций ЕСЛИ в одной формуле. Надеюсь, что эта информация была для Вас полезной. Удачи Вам и больших успехов в изучении Microsoft Excel!

Распространенный вопрос по Excel «Как записывать несколько условий в одной формуле?». Особенно часто применяется два и более условий при использовании функции ЕСЛИ. Сделать несколько условий в формуле ЕСЛИ довольно просто, главное знать основные принципы. Их и обсуждаем ниже.

На мой взгляд, показательно рассмотреть пример с решением системы условий. Такие задания часто задают в институтах, на парах по Excel.

Например, есть вот такая, довольно нагроможденная формула:

Разберем на примере, как перенести ее в Excel

Понятно, что эта формула будет состоять из 3 частей, как минимум:

SIN(B1)^2 =COS(B1) =EXP(1/B1)

Но как записать несколько этих функций в одну, еще и по условию? Для того чтобы разобраться, подробно посмотрим на функцию ЕСЛИ.

Ее состав следующий:

ЕСЛИ(Условие;если условие = ДА (ИСТИНА);если условие = НЕТ (ЛОЖЬ))

Т.е. если мы запишем простую формулу, что мы получим в итоге в ячейке B2?

Верно — отобразиться 100. Если же в А1 будет стоять любое другое значение кроме 1, то в B2 отобразится бы 0.

Вернемся к нашей системе условий. Теперь нам надо понимать как записать сразу два условия до первой точки с запятой. У нас в B1 пусто, а значит = 0, и только при выполнении обоих условий А1=1 и B1=0 (знак *) значение формулы будет равно 100.

Особо разберем * между скобками

Оператор И он же * означает, что должно выполняться оба условия одновременно, А1=1 и B1=0.

Если между скобками поставить + (или), то достаточно будет одного из условий. Например только если А1=1, то уже будет отображаться 100.

Мы готовы к написанию формулы, будем это делать по частям

Запишем первое условие

ЕСЛИ((B1>-2)*(B1<9);SIN(B1)^2);

Если условие выполняется, то выполняется первая формула с синусом
Если нет, второе условие

ЕСЛИ((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1)

Во всех же остальных случаях будет выполнятся формула =EXP(1/B1)
Итого получается:

ЕСЛИ((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1);EXP(1/B1)))

Запись нескольких формул в одной

Если в ячейки B1 будет текст, то формула выдаст ошибку. Поэтому я часто применяю формулу .

Представим что вся наша формула из предыдущего пункта это один условный аргумент А

Тогда =ЕСЛИОШИБКА(А;»»)

Или для нашего примера

ЕСЛИОШИБКА(ЕСЛИ((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1);EXP(1/B1)));"")

Пример можно скачать

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

Для таких случаев в Excel предусмотрено несколько вариантов: использование ЕСЛИ() внутри другого ЕСЛИ() , функции И() и ИЛИ() . Далее мы познакомимся с этими способами.

Использование ЕСЛИ() внутри другой функции ЕСЛИ()

Давайте рассмотрим вариант на основе изученной ранее функции =ЕСЛИ(А1>1000;"много"; "мало") . Что если вам необходимо вывести другую строку, когда число в А1 является, например, большим, чем 10.000? Другими словами, если выражение А1>1000 верно, вы захотите запустить другую проверку и посмотреть, верно ли, что А1>10000. Такой вариант вы можете создать, применив вторую функцию ЕСЛИ() внутри первой в качестве аргумента значение _если_истина: =ЕСЛИ(А1>1000;ЕСЛИ(А1>10000;"очень много"; "много");"мало") .

Если А1>1000 является истинным, запускается другая функция ЕСЛИ() , возвращающая значение «очень много», когда А1>10000. Если же при этом А1 меньше или равно 10000, возвращается значение «много». Если же при самой первой проверке число А1 будет меньше 1000, выведется значение «мало».

Обратите внимание, что с таким же успехом вы можете запустить вторую проверку, в случае если первая будет ложной (то есть в аргументе значение_если_ложь функции еслио). Вот небольшой пример, возвращающий значение «очень мало», когда число в А1 меньше 100: =ЕСЛИ(А1>1000;"много";ЕСЛИ(А1<100;"очень мало"; "мало")) .

Расчет бонуса с продаж

Хорошим примером использования одной проверки внутри другой проверки является расчет бонуса с продаж персоналу. который работает в Клуб — отель Гелиопарк Талассо, Звенигород . В данном случае, если значение равно X, вы хотите получить один результат, если У — другой, если Z
— третий. Например, в случае вычисления бонуса за успешные продажи возможны три варианта:

1. Продавец не достиг планового значения, бонус равен 0.
2. Продавец превысил плановое значение менее чем на 10%, бонус равен 1 000 рублей.
3. Продавец превысил плановое значение более чем на 10%, бонус равен 10 000 рублей.

Вот формула для расчета такого примера: =ЕСЛИ(Е3>0;ЕСЛИ(Е3>0.1;10000;1000);0) . Если значение в Е3 является отрицательным, то возвращается 0 (нет бонуса). В случае когда результат положительный, проверяется, больше ли он 10%, и в зависимости от этого выдается 1 000 или 10 000. Рис. 4.17 показывает пример работы формулы.

Функция И()

Часто бывает необходимо провести проверку только на верность одновременно двум условиям. Например, выплата бонуса осуществляется, только когда продажи определенного товара превысили план и также общие продажи превысили план. Если одно из данных условий не выполняется (или оба условия), премия не выплачивается. В булевой логике это называется логическим И, поскольку оба условия должны быть истинны для истинности всего выражения.

В Excel выражения логического И обрабатываются с помощью функции И() : И(логическое_значение1;логическое_значение2;…). Каждый аргумент представляет собой логическое значение для проверки. Вы можете ввести столько аргументов, сколько вам необходимо.

Еще раз отметим работу функции:

• Если все выражения возвращают ИСТИНА (или любое положительное число), И() возвращает ИСТИНА.
• Если один или более аргументов возвращают ЛОЖЬ (или 0), И() возвращает ЛОЖЬ.

Чаще всего И() применяется внутри функции ЕСЛИ() . В таком случае, когда все аргументы внутри И() вернут ИСТИНА, функция ЕСЛИ() пойдет по своей ветке значение если истина. Если одно или более из выражений в И() вернет ЛОЖЬ, функция ЕСЛИ() пойдет по ветке значение_если_ложь.

Вот небольшой пример: =ЕСЛИ(И(С2>0;В2>0);1000;"нет бонуса") . Если значение в В2 будет больше нуля и значение в С2 будет больше нуля, формула вернет 1000, в противном случае выведется строка «нет бонуса».

Разделение значений по категориям

Полезным применением функции и () является разделение по категориям в зависимости от значения. Например, у вас имеется таблица с результатами какого-то опроса или голосования, и вы хотите разделить все голоса на категории в соответствии со следующими возрастными рамками: 18-34,35-49, 50-64,65 и более. Предполагая, что возраст респондента находится в ячейке В9, следующие аргументы функции и () проводят логическую проверку на принадлежность возраста диапазону: =И(В9>=18;В9

Если ответ человека находится в ячейке С9, следующая формула выведет результат голосования человека, если срабатывает проверка на соответствие возрастной группе 18-34: =ЕСЛИ(И(В9>=18;В9

• 35-49: =ЕСЛИ(И(В9>=35;В9
• 50-64: =ЕСЛИ(И(В9>=50;В9
• 65+: =ЕСЛИ(В9>=65;С9;"")

Функция ИЛИ()

Существуют ситуации, когда вам необходимо решение, только когда одно из условий принимает положительное значение (ИСТИНА). Например, вы можете решить выплатить бонус сотрудникам, когда общие продажи превышают план или когда продажи отдельного товара превысили прогноз. В булевой логике это называется логическим ИЛИ .

Такие условия проверяются в Excel с помощью функции ИЛИ() : ИЛИ(логическое_значение1; логическое_значение2;...). Каждый аргумент представляет собой логическое значение для проверки. Вы можете ввести столько аргументов, сколько вам необходимо. Результат работы ИЛИ() зависит от следующих условий:

• Если один аргумент или более возвращает ИСТИНУ (любое положительное число), ИЛИ() возвращает ИСТИНУ.
• Если все аргументы возвращают ЛОЖЬ (нулевое значение), результатом работы ИЛИ() будет ЛОЖЬ.

Так же как и И() , чаще всего функция ИЛИ() используется внутри проверки ЕСЛИ() . В таком случае, когда один из аргументов внутри ИЛИ() вернет ИСТИНА, функция ЕСЛИ() пойдет по своей ветке значение_если_истина. Если все выражения в ИЛИ() вернут ЛОЖЬ, функция ЕСЛИ() пойдет по ветке значение_если_ложь . Вот небольшой пример: = ЕСЛИ(ИЛИ(С2>0;В2>0);1000;"нет бонуса") .

В случае когда в одной из ячеек (С2 или В2) будет положительное число, функция вернет 1000. Только когда оба значения будут отрицательны (или равны нулю), функция вернет строку "нет бонуса".

Однажды жизненные обстоятельства заставляют осваивать новые программные инструменты для решения повседневных или офисных задач. Вручную выполнять долго и неудобно, к тому же увеличивается вероятность совершить ошибку. Поэтому рассмотрим, как работает СУММЕСЛИ в Excel и покажем примеры использования.

Синтаксис функции и принцип работы

О возможностях сложения известно каждому пользователю, хоть раз запускавшего Excel. СУММЕСЛИ – логичное продолжение базовой СУММ, отличие которой заключается в суммировании по условию.

Синтаксис СУММЕСЛИ выглядит следующим образом:

=СУММЕСЛИ(диапазон; критерий; [диапазон суммирования]) , где:

• диапазон – список значений, к которым будет применено ограничение;
• критерий – конкретное условие;
• диапазон суммирования – список значений, которые будут суммироваться.

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

Примеры использования

Рассмотрим все возможные ситуации применения одноименной функции.

Общий вид

Разберем простой пример, призванный наглядно показать преимущества использования СУММЕСЛИ.

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

Допустим, нам нужно выяснить, сколько всего единиц товара находится на складе. Тут все просто – используем СУММ и указываем нужный интервал. Но что делать, если интересует количество единиц одежды? Тут на помощь и приходит СУММЕСЛИ. Функция будет иметь следующий вид:

=СУММЕСЛИ(C3:C12;»одежда»;F3:F12) , где:

• C3:C12 – тип одежды;
• «одежда» – критерий;
• F3:F12 – интервал суммирования.

Внимание! В функции СУММЕСЛИМН аргумент диапазона суммирования стоит в начале формулы. Будьте внимательны!

С динамическим условием

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

Для вставки новой строки нужно нажать ПКМ на интересующей ячейке и выбрать «Вставить» – «Строку».

Далее просто введите новые данные или скопируйте их с другого места. Итоговое значение соответственно изменится. Аналогичные трансформации происходят и при редактировании или удалении строк.

На этом я заканчиваю. Вы увидели основные примеры использования функции СУММЕСЛИ в Excel. Если есть какие-то рекомендации или вопросы – милости прошу в комментарии.

Логические выражения

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

Ниже представлен список операторов сравнения Excel:

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

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

Синтаксис:

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

Переместить курсор в ячейку D 2 . С помощью мастера функций выбрать из категории Логические функцию ЕСЛИ (рис. 27), а затем щелкнуть на кнопке ОК.

Диалоговое окно Аргументы функции (рис. 28) содержит три поля ввода. В поле Лог _ выражение необходимо ввести условие, которое определяет, превышает ли проданное кол-во товара 5 шт., следовательно введем в это поле С2>5 . В поле Значение_если_истина необходимо ввести формулу, которая вычисляет стоимость товара с учетом скидки, тогда введем в это поле B2*C2-B2*C2*0,1 . В поле Значение_если_ложь необходимо ввести формулу, которая вычисляет стоимость товара без учета скидки (условие С2>5 - Ложь), в тогда введем в это поле B2*C2 (см. рис. 29). Теперь щелкнем на кнопке OK.

Скопировать полученную формулу в смежные ячейки. Результаты вычислений формул приведены на рис. 30.

Функции ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь. С помощью таких вложенных функций Если можно конструировать более сложные проверки. Рассмотрим на примерах, как необходимо использовать вложенной функцией ЕСЛИ .

На рис. 31 приведена таблица с данными. Необходимо:

Определить, на какую сумму продано товаров каждого вида.

Определить, на какую сумму продано товаров каждого вида с учетом скидки (сумма с учетом скидки = сумма - сумма * скидка). Скидка начисляется по следующему принципу: если продано товара на сумму более 2500 грн., то скидка составит 5%, если продано товара на сумму менее 1100 грн., то скидка составит 0%, в остальных случаях скидка составит 2%.

Для того чтобы выполнить первый пункт задания необходимо в ячейку D2 ввести формулу =C2*B2 .

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

Переместим курсор в ячейку E2 и с помощью мастера функций введем следующую формулу (рис. 33 – 34). Результаты решения приведены на рис. 35.

Щелкнуть тут

Рис. 35

Функции ИЛИ, И

Функции И и функция ИЛИ из категории Логические способны заметно улучшить наглядность и понятность сложных логических проверок. Функция ИЛИ и функция И позволяют задавать в формуле одновременно несколько условий, т.е. дают возможность создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

ИЛИ(логическое_значение1;логическое_значение2; ...)

И(логическое_значение1; логическое_значение2; ...)

Аргументы функций И, ИЛИ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Функция ИЛИ ИСТИНА , если хотя бы одно из логических выражений истинно, а функция И возвращает логическое значение ИСТИНА , только если все логические выражения истинны.

Предположим, что нужно вывести сообщение «Светофор», если содержимое ячейки B4 или «красный», или «зеленый», или «желтый». Если же она содержит любую другую информацию, то необходимо вывести сообщение «Это не светофор!!!».

ЕСЛИ(ИЛИ(B4= "зеленый" ;B4= "красный" ;B4= "желтый" );"Светофор"; "Это не светофор!!!")

Предположим, что нужно вывести на экран содержимое ячейки B4 , если она содержит число строго между 1 и 100 (1<=B4<=100), и сообщение "Значение вне интервала" в противном случае.

ЕСЛИ(И(B4>=1; B4<=100); B4; «Значение вне интервала»)

Есть список дат, необходимо определить, является ли выходным днем введенная дата. Решения задачи на рис. 36

Формулу в ячейке В2 можно вводить с клавиатуры (необходимо помнить синтаксис используемых вложенных функций) или с помощью мастера функции. Рассмотрим, как вводить формулу с помощью мастера функций:

Переместим курсор в ячейку В2, щелкнем на кнопке вставка функции и в категории логические выберем функцию Если .

Щелкнем в поле Лог _ выражение функции Если, а затем щелкнем на стрелке вниз в строке формул и выберем Другие функции (см. рис. 37). Теперь в категории Логические можно выбрать функцию ИЛИ .

Щелкнем в поле Логическое_выражение_1 функции ИЛИ , а затем щелкнем на стрелке вниз в строке формул и выберем Другие функции. В категории Дата и время выберем функцию ДЕНЬНЕД и в поле Дата_в_числом_формате введем А2 , а в поле Тип введем 2 .

ИЛИ ИЛИ , для того чтобы закончить ввод условия в первое поле этой функции (набираем = 6).

Щелкнем в поле Логическое_выражение_2 функции ИЛИ и повторим действия пункта 3 и 4, чтобы ввести второе условие ДЕНЬНЕД(А2;2)=7.

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

Информация о результатах первого модульного контроля и посещаемости студентами занятий выведена в таблице см. рис. 38. Необходимо вывести следующую информацию: Если средний бал студента меньше или равен 3,5 и он пропустил более 49 часов занятий по неуважительной причине, то необходимо вызвать родителей в деканат; если средний бал студента больше или 4,5 и он пропустил не более 10 часов занятий по неуважительной причине, то необходимо отправить родителям письмо благодарность. Решение задачи представлено на рис. 39.

Функции СЧЁТЕСЛИ и СУММЕСЛИ

Эти функции надо искать не в категории Логические , а в категориях Статистические и Математические , соответственно (или в полном алфавитном перечне).

СЧЁТЕСЛИ - подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию, а СУММЕСЛИ - суммирует значения ячеек, удовлетворяющих заданному условию. Функция СУММЕСЛИ используется в тех случаях, когда необходимо суммировать не весь диапазон, а только ячейки, отвечающие некоторым условиям (критериям).

Синтаксис:

СЧЁТЕСЛИ (диапазон; критерий)

диапазон - диапазон, в котором нужно подсчитать ячейки.

критерий - критерий (условие) в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать.

Функция СЧЁТЕСЛИ работает следующим образом: вычисляется количество ячеек диапазона, значение которых удовлетворяют условию (критерию) .

В задаче, рассмотренной в примере 10 необходимо определить количество студентов, у которых средний балл >=4.5.

Тогда в ячейку С15 необходимо ввести формулу: =СЧЁТЕСЛИ(B11:B13;">=4,5"). Результат приведен на рис. 40

Синтаксис:

СУММЕСЛИ (диапазон; критерий; диапазон_суммирования)

диапазон - диапазон ячеек проверяемых на критерий (условие).

критерий - критерий (условие) в форме числа, выражения или текста, определяющего суммируемые ячейки.

диапазон_суммирования - фактические ячейки для суммирования.

Функция СУММЕСЛИ работает следующим образом: ячейки из «диапазон_суммирования » суммируются, только если соответствующие им ячейки в аргументе «диапазон » удовлетворяют условию (критерию) . В тех случаях, когда диапазон вычисляемых ячеек (где проверяется условие) и диапазон фактических ячеек для суммирования совпадают, аргумент «диапазон_суммирования» можно не указывать.

В таблице на рис. 41 приведены объемы партий товара, полученные магазином. Необходимо просуммировать только объемы тех партий, значения которых превышают 20. Решение на рис. 42