Создание условных формул
Проверка того, являются ли условия истинными или ложными, и логические сравнения между выражениями являются общими для многих задач. Для создания условных формул можно использовать функции AND, OR, NOT и IF .
Например, функция ЕСЛИ использует следующие аргументы.
Формула, использующая функцию IF
logical_test: условие, которое необходимо проверить.
value_if_true: возвращаемое значение, если условие имеет значение True.
value_if_false: возвращаемое значение, если условие имеет значение False.
Дополнительные сведения о создании формул см. в разделе «Создание или удаление формулы».
Что вы хотите сделать?
- Создание условной формулы, которая приводит к логическому значению (TRUE или FALSE)
- Создание условной формулы, которая приводит к другому вычислению или значениям, отличным от TRUE или FALSE
Создание условной формулы, которая приводит к логическому значению (TRUE или FALSE)
Для выполнения этой задачи используйте функции и операторы AND, OR и NOT , как показано в следующем примере.
Пример
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
Копирование примера
- Выделите пример, приведенный в этой статье. Выделение примера в справке
- Нажмите клавиши CTRL+C.
- В Excel создайте пустую книгу или лист.
- Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.
Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.
- Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.
Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.
Описание (результат)
Определяет, больше ли значение в ячейке A2, чем значение в A3, а также если значение в A2 меньше значения в A4. (FALSE)
Определяет, больше ли значение в ячейке A2 значения В3 или значение в A2 меньше значения В4. (TRUE)
Определяет, не равна ли сумма значений в ячейках A2 и A3 24. (FALSE)
Определяет, не равно ли значение в ячейке A5 «Spets». (FALSE)
Определяет, не равно ли значение в ячейке A5 «Spets» или значение в A6 равно «Мини-приложениям». (TRUE)
Дополнительные сведения о том, как использовать эти функции, см. в разделах «Функции AND», «OR» и «НЕ».
Создание условной формулы, которая приводит к другому вычислению или значениям, отличным от TRUE или FALSE
Для выполнения этой задачи используйте функции и операторы IF, AND и OR , как показано в следующем примере.
Пример
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
Копирование примера
-
Выделите пример, приведенный в этой статье.
Важно: Не выделяйте заголовки строк или столбцов.
Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.
- Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.
Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.
Описание (результат)
=ЕСЛИ(A2=15, «ОК», «Не ОК»)
Если значение в ячейке A2 равно 15, возвращается «ОК». В противном случае возвращается сообщение «Не ОК». (ОК)
=ЕСЛИ(A2<>15, «ОК», «Не ОК»)
Если значение в ячейке A2 не равно 15, возвращается значение «ОК». В противном случае возвращается сообщение «Не ОК». (Не ОК)
Если значение в ячейке A2 не меньше или равно 15, возвращается значение «ОК». В противном случае возвращается сообщение «Не ОК». (Не ОК)
=ЕСЛИ(A5<>«SPROCKETS», «ОК», «Не ОК»)
Если значение в ячейке A5 не равно «SPETS», возвращается значение «ОК». В противном случае возвращается сообщение «Не ОК». (Не ОК)
Если значение в ячейке A2 больше значения В3, а значение в A2 также меньше значения в A4, возвращается «ОК». В противном случае возвращается сообщение «Не ОК». (Не ОК)
=ЕСЛИ(И(A2<>A3, A2<>A4), «ОК», «Не ОК»)
Если значение в ячейке A2 не равно A3, а значение в A2 также не равно значению в A4, возвращается «ОК». В противном случае возвращается сообщение «Не ОК». (ОК)
Если значение в ячейке A2 больше значения в A3 или значение в A2 меньше значения в A4, возвращается «ОК». В противном случае возвращается сообщение «Не ОК». (ОК)
=IF(OR(A5<>«Sprockets», A6<>«Widgets»), «OK», «Not OK»)
Если значение в ячейке A5 не равно «Spets» или значение в A6 не равно «Widgets», возвращается «ОК». В противном случае возвращается сообщение «Не ОК». (Не ОК)
=ЕСЛИ(ИЛИ(A2<>A3, A2<>A4), «ОК», «Не ОК»)
Если значение в ячейке A2 не равно значению в A3 или значение в A2 не равно значению в A4, возвращается «ОК». В противном случае возвращается сообщение «Не ОК». (ОК)
Дополнительные сведения о том, как использовать эти функции, см. в разделах «Функция ЕСЛИ», «И» и » ИЛИ».
Функция УСЛОВИЯ
Функция ЕСЛИМН проверяет соответствие одному или нескольким условиям и возвращает значение для первого условия, принимающего значение ИСТИНА. Функцию ЕСЛИМН можно использовать вместо нескольких вложенных операторов ЕСЛИ. Ее гораздо проще читать при наличии нескольких условий.
Примечание: Эта функция доступна в Windows или Mac, если у вас есть Office 2019 или у вас есть подписка На Microsoft 365. Если вы являетесь подписчикомMicrosoft 365, убедитесь, что у вас установлена последняя версия.
Простой синтаксис
Как правило, синтаксис функции ЕСЛИМН выглядит следующим образом:
=ЕСЛИМН(условие1; значение1; [условие2; значение2]; [условиe3; значение3])
Обратите внимание, что функция ЕСЛИМН позволяет проверить до 127 различных условий. Однако не рекомендуется вкладывать слишком много условий с операторами ЕСЛИ или ЕСЛИМН. Это связано с тем, что несколько условий нужно вводить в правильном порядке и их может быть сложно добавлять, проверять и обновлять.
Технические подробности
- ЕСЛИМН(лог_выражение1;значение_если_истина1,[лог_выражение2;значение_если_истина2],[лог_выражение3;значение_если_истина3]. )
лог_выражение1 (обязательно)
Условие, принимающее значение ИСТИНА или ЛОЖЬ.
значение_если_истина1 (обязательно)
Результат, возвращаемый, если условие лог_выражение1 принимает значение ИСТИНА. Может не указываться.
лог_выражение2…лог_выражение127 (необязательно)
Условие, принимающее значение ИСТИНА или ЛОЖЬ.
значение_если_истина2. значение_если_истина127 (необязательно)
Результат, возвращаемый, если условие лог_выражениеN принимает значение ИСТИНА. Каждый аргумент значение_если_истинаN соответствует условию лог_выражениеN. Может не указываться.
Пример 1
Формула для ячеек A2:A6:
- =ЕСЛИМН(A2>89;»A»;A2>79;»B»;A2>69;»C»;A2>59;»D»,ИСТИНА,»F»)
Расшифровка: ЕСЛИ(значение A2 больше 89, возвращается «A», если значение A2 больше 79, возвращается «B», и т. д. Для всех значений меньше 59 возвращается «F»).
Пример 2
В ячейке G7 находится следующая формула:
- =ЕСЛИМН(F2=1;D2;F2=2;D3;F2=3;D4;F2=4;D5;F2=5;D6;F2=6;D7;F2=7;D8)
Расшифровка: ЕСЛИ(значение в ячейке F2 равно 1, возвращаемое значение в ячейке D2, если значение в ячейке F2 равно 2, возвращается значение в ячейке D3, и т. д. Если ни одно из других условий не выполняется, возвращается значение в ячейке D8.
Замечания
- Чтобы задать результат по умолчанию, введите значение ИСТИНА для последнего аргумента лог_выражение. Если ни одно из других условий не выполняется, возвращается соответствующее значение. Это показано в строках 6 и 7 примера 1 (с 58 баллами).
- Если условие лог_выражение указывается без соответствующего аргумента значение_если_истина, появляется сообщение «Указано недостаточно аргументов».
- Если аргумент лог_выражение принимает значение, отличное от ИСТИНА или ЛОЖЬ, функция возвращает ошибку #ЗНАЧ!.
- Если условия со значением ИСТИНА не найдены, возвращается ошибка #Н/Д!
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
ИЛИ (функция ИЛИ)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
ИЛИ — одна из логических функций, с помощью которой можно определить наличие условий, которые принимают значение ИСТИНА.
Пример
Технические подробности
Функция ИЛИ возвращает значение ИСТИНА, если в результате вычисления хотя бы одного из ее аргументов получается значение ИСТИНА, и значение ЛОЖЬ, если в результате вычисления всех ее аргументов получается значение ЛОЖЬ.
Обычно функция ИЛИ используется для расширения возможностей других функций, выполняющих логическую проверку. Например, функция ЕСЛИ выполняет логическую проверку и возвращает одно значение, если при проверке получается значение ИСТИНА, и другое значение, если при проверке получается значение ЛОЖЬ. Использование функции ИЛИ в качестве аргумента «лог_выражение» функции ЕСЛИ позволяет проверять несколько различных условий вместо одного.
ИЛИ(логическое_значение1;[логическое значение2];. )
Аргументы функции ИЛИ описаны ниже.
Логическое_значение1
Обязательный аргумент. Первое проверяемое условие, вычисление которого дает значение ИСТИНА или ЛОЖЬ.
Логическое_значение2;.
Необязательные аргументы. Дополнительные проверяемые условия, вычисление которых дает значение ИСТИНА или ЛОЖЬ. Условий может быть не более 255.
- Аргументы должны принимать логические значения (ИСТИНА или ЛОЖЬ) либо быть массивами либо ссылками, содержащими логические значения.
- Если аргумент, который является ссылкой или массивом, содержит текст или пустые ячейки, то такие значения игнорируются.
- Если заданный диапазон не содержит логических значений, функция ИЛИ возвращает значение ошибки #ЗНАЧ!.
- Можно воспользоваться функцией ИЛИ в качестве формулы массива, чтобы проверить, имеется ли в нем то или иное значение. Чтобы ввести формулу массива, нажмите клавиши CTRL+SHIFT+ВВОД.
Примеры
Ниже представлено несколько общих примеров использования функции ИЛИ как отдельно, так и в сочетании с функцией ЕСЛИ.
Возвращает значение ИСТИНА, если число в ячейке A2 больше 1 ИЛИ меньше 100. В противном случае возвращает значение ЛОЖЬ.
Возвращает значение ячейки A3, если оно меньше 100 ИЛИ больше 1. В противном случае возвращает сообщение «Значение вне допустимого диапазона».
=ЕСЛИ(ИЛИ(A250);A2;»Значение вне допустимого диапазона»)
Возвращает значение ячейки A2, если оно меньше 0 ИЛИ превышает 50. В противном случае возвращает сообщение «Значение вне допустимого диапазона».
Расчет комиссионных
Ниже приводится решение довольно распространенной задачи: с помощью функций ЕСЛИ и ИЛИ определяется, заработал ли торговый сотрудник комиссионные, а также вычисляется их размер.
- =ЕСЛИ(ИЛИ(B14>=$B$4;C14>=$B$5);B14*$B$6;0) — ЕСЛИ общие продажи больше целевых продаж или равны им (>=) ИЛИ число договоров больше целевого или равно ему (>=), общие продажи умножаются на процент комиссионных. В противном случае возвращается значение 0.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий
Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.
Функция ЕСЛИ в Excel
Функция имеет следующий синтаксис.
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
значение_если_истина – значение или формула, которое возвращается при наступлении указанного в первом параметре события.
значение_если_ложь – это альтернативное значение или формула, которая возвращается при невыполнении условия. Данное поле не обязательно заполнять. В этом случае при наступлении альтернативного события функция вернет значение ЛОЖЬ.
Очень простой пример. Нужно проверить, превышают ли продажи отдельных товаров 30 шт. или нет. Если превышают, то формула должна вернуть «Ок», в противном случае – «Удалить». Ниже показан расчет с результатом.
Продажи первого товара равны 75, т.е. условие о том, что оно больше 30, выполняется. Следовательно, функция возвращает то, что указано в следующем поле – «Ок». Продажи второго товара менее 30, поэтому условие (>30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.
Однако это был демонстрационный пример. Чаще формулу Эксель ЕСЛИ используют для более сложных проверок. Допустим, есть средненедельные продажи товаров и их остатки на текущий момент. Закупщику нужно сделать прогноз остатков через 2 недели. Для этого нужно от текущих запасов отнять удвоенные средненедельные продажи.
Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.
В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.
Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.
Формула ЕСЛИ в Excel – примеры нескольких условий
Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.
Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.
При написании формулы легко запутаться, поэтому рекомендуется смотреть на всплывающую подсказку.
В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку
Функция Excel ЕСЛИМН
Функция Эксель ЕСЛИ в целом хорошо справляется со своими задачами. Но вариант, когда нужно записывать длинную цепочку условий не очень приятный, т.к., во-первых, написать с первого раза не всегда получается (то условие укажешь неверно, то скобку не закроешь); во-вторых, разобраться при необходимости в такой формуле может быть непросто, особенно, когда условий много, а сами расчеты сложные.
В MS Excel 2016 появилась функция ЕСЛИМН, ради которой и написана вся эта статья. Это та же ЕСЛИ, только заточенная специально для проверки множества условий. Теперь не нужно сто раз писать ЕСЛИ и считать открытые скобки. Достаточно перечислить условия и в конце закрыть одну скобку.
Работает следующим образом. Возьмем пример выше и воспользуемся новой формулой Excel ЕСЛИМН.
Как видно, запись формулы выглядит гораздо проще и понятнее.
Стоит обратить внимание на следующее. Условия по-прежнему перечисляем в правильном порядке, чтобы не произошло ненужного перекрытия диапазонов. Последнее альтернативное условие, в отличие от обычной ЕСЛИ, также должно быть обязательно указано. В ЕСЛИ задается только альтернативное значение, которое наступает, если не выполняется ни одно из перечисленных условий. Здесь же нужно указать само условие, которое в нашем случае было бы B2>=1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.
Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.