Ребята, всем привет! 👋 Не открою секрет, что логические функции являются неотъемлемыми компонентами многих формул. Данные функции используются в случаях, когда результат обработки зависит от выполнения некоторого условия, заданного в виде логического выражения. Именно их мы сегодня и разберем.
📚 Немного теории...
✍️ 1. ФУНКЦИЯ ЕСЛИ
ЕСЛИ(Лог_выражение;Значение_если_истина;Значение_если_ложь) – возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
IF(Logical_test;Value_if_true;Value_if_false)
👉 Лог_выражение [Logical_test] – выражение, относительно которого можно судить: истина это или ложь. Необходимо задать условие, используя ссылки на адреса ячеек (что и с чем сравниваем): >, >=, <, <=, <>, =. Можно использовать функции: И [AND], ИЛИ [OR].
👉 Значение_если_истина [Value_if_True] – ввести текст, число или формулу для определения значения в случае, если условие будет выполнено.
👉 Значение_если_ложь [Value_if_False] – ввести текст, число или формулу для определения значения в случае, если условие не будет выполнено.
✍️ 2. ФУНКЦИИ, объединяющие несколько условий в одно
⚠ Если все условия должны быть выполнены одновременно, то стоит воспользоваться функцией И.
И(Логическое_значение1;Логическое_значение2;) – проверяет, все ли аргументы имеют значение ИСТИНА, и возвращает значение ИСТИНА, если истинны все аргументы.
AND(Logical1;Logical2;)
⚠ Если достаточно выполнения только одного из указанных условий, то стоит воспользоваться функцией ИЛИ.
ИЛИ(Логическое_значение1;Логическое_значение2;) – проверяет, имеет ли хотя бы один из аргументов значение ИСТИНА.
Значение ЛОЖЬ возвращается только в том случае, если все аргументы имеют значение ЛОЖЬ.
OR(Logical1;Logical2;)
🔔 ВАЖНО!
Использование только функций И и ИЛИ позволяет получить ответ в ячейке как ИСТИНА или ЛОЖЬ, поэтому их часто используют в логической функции ЕСЛИ, чтобы задать более сложные условия.
✍️ 3. Вложенность функции ЕСЛИ
Применяется для задач, где необходимо определить какое либо значение по определенным условиям, например:
✍️ 4. ЕСЛИОШИБКА
ЕСЛИОШИБКА(Значение;Значение_если_ошибка) – возвращает указанное значение, если вычисление по формуле вызывает ошибку, в противном случае возвращает результат формулы.
IFERROR(Value;Value_if_error)
👉 Значение [Value] – обязательный аргумент (формула), проверяемый на возникновение ошибок.
👉 Значение_если_ошибка [Value_if_error] – обязательный аргумент. Значение, возвращаемое при ошибке при вычислении по формуле – может быть числом, текстом или формулой для вычисления. Возможны следующие типы ошибок: # Н/Д, # ЗНАЧ!, # ССЫЛКА!, # ДЕЛ/0!, # ЧИСЛО!, # ИМЯ? и # ПУСТО!
✍️ 5. Функция ЕСЛИМН и ПЕРЕКЛЮЧ
В 2019 версии Excel категория логических функций пополнилась ещё двумя функциями: ЕСЛИМН и ПЕРЕКЛЮЧ.
Функция ЕСЛИМН может использоваться вместо множественного вложения функции ЕСЛИ в ЕСЛИ, а ПЕРЕКЛЮЧ больше напоминает сочетание 2-х функций: ВПР и ЕСЛИОШИБКА, т.е. решает задачи выбора.
ЕСЛИМН(Логическая_проверка1;Если_значение_истина1;
[Логическая_проверка2;Если_значение_истина2];…) – проверяет соответствие одному или нескольким условиям и возвращает значение для первого условия со значением ИСТИНА.
IFS(Logical_test1;Value_if_true1;[ Logical_test2;Value_if_true2];…)
👉 Логическая_проверка1 [Logical_test1] – выражение, относительно которого можно оценить: истина это или ложь. Необходимо задать условие, используя ссылки на адреса ячеек (что и с чем сравниваем): >, >=, <, <=, <>, =. Можно использовать функции для объединения условий: И [AND], ИЛИ [OR].
👉 Если_значение_истина1 [Value_if_True1] – ввести текст, число или формулу для определения значения в случае, если условие будет выполнено.
ПЕРЕКЛЮЧ(Выражение;Значение1; Результат1;
[По_умолчанию_или_значение2;Результат2];…) – вычисляет значение на основе списка значений и возвращает результат, соответствующий первому совпавшему значению. Если совпадение отсутствует, то возвращается необязательное значение.
SWITCH(Expression;Value1;Result1; Value2;Result2;[Default_or-value3;Result3];…)
👉 Выражение [Expression] – выражение, относительно которого ведется оценка.
👉 Значение1 [Value1] – значение для сравнения с выражением.
👉 Результат1 [Result1] – значение (решение) в случае, если значение1 совпадает с выражением.
👉 По_умолчанию [Default] – значение в случае, если не было совпадения значения1 и т.п. с выражением.
📝 Как обстоят дела на практике?
✅ Пример 1, начислить бонус менеджерам (в размере 5%), чьи продажи за месяц более 250000
=ЕСЛИ(F2>250000;5;0) - в зависимости от продаж за месяц (соответствующая ячейка в столбце G) вычисляется бонус: если продажи за месяц составили более 250000, то начисляемый бонус равен 5%, в противном – бонус не начисляется.
✅ Пример 2, рассчитать премию менеджеров в зависимости от условия
(напомню, подобную задачу с применение функции ВПР мы решали в предыдущем уроке)
Пусть в условии дано:
=ЕСЛИ(И(F2>=$A$2;F2<$A$3);$B$2;ЕСЛИ(И(F2>=$A$3;F2<$A$4);$B$3;ЕСЛИ(F2>=$A$4;$B$4;0))) - в зависимости от продаж месяц (соответствующая ячейка в столбце F) начисляем % премии: если продажи составили от 100 000 до 200 000, то премия - 10%, если продажи составили от 200 000 до 350 000, то премия - 20%, если продажи составили 350 000 и более, то - 35%, иначе - премия не начисляется)
Альтернативным решением данной задачи ток же будет применение следующей формулы:
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$B$4;3-СУММПРОИЗВ(--(F3<={100000:200000:350000}));2);0)
✅ Пример 3, рассчитать бонус менеджеров в зависимости от условия.
Пусть дано условие: Если кол-во продаж ИЛИ Сумма продаж более 250000,0, то бонус 5%, иначе - бонус не начисляется:
=ЕСЛИ(ИЛИ(B3>50;C3>250000);5;0) - в зависимости от количества продаж (соответствующая ячейка в столбце B) и Суммы продаж (соответствующая ячейка в столбце С) определяется будет ли начислен бонус в размере 5%.
✅ Пример 4, усложним предыдущий пример и рассчитаем размер бонуса (в руб.) в зависимости от следующего условия.
Пусть дано условие:
1. С помощью функции ЕСЛИ определяется % бонуса:
=ЕСЛИ(F2>=$A$4;$C$4;ЕСЛИ(F2>=$A$3;$C$3;$C$2)), где F2 - ячейка со значением количества продаж
2. Затем, все умножаем на значение оклада и делим на 100 (т.к значение бонуса было принято в числовом значении, а не в виде %)
=(ЕСЛИ(F2>=$A$4;$C$4;ЕСЛИ(F2>=$A$3;$C$3;$C$2))*J2)/100
✅ Пример 5. В некоторых случаях, как уже указывалось выше (в т.ч. см. пример 2). При вычислении может возникнуть ошибка.
"Обойти" ошибку или задать заведомо нужное значение в случае ошибки поможет функция ЕСЛИОШИБКА
Для предыдущего примера 4 возможно следующее решение:
На этом сегодня все. Продолжение следует...
В следующих уроках более подробно рассмотрим:
☑ текстовые функции
☑ функции для работы с датами
А так же:
☑ Создание условия с использованием формулы
☑ Защита ячеек, листов и рабочих книг Excel
☑ Установка ограничений на ввод данных
☑ Поиск неверных данных и др.
#excel #встроенные функции excel #логические функции excel #и excel #или excel # если excel #еслиошибка excel