Как сделать функцию или в excel

Примеры функций И ИЛИ в Excel для записи логических выражений

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

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

Примеры использования функций И ИЛИ в логических выражениях Excel

Пример 1. В учебном заведении для принятия решения о выплате студенту стипендии используют два критерия:

1. Средний балл должен быть выше 4,0.
2. Минимальная оценка за профильный предмет – 4 балла.

Определить студентов, которым будет начислена стипендия.

Для решения используем следующую формулу:

• E2>=4 – проверка условия «является ли оценка за профильный предмет не менее 4 баллов?»;
• СРЗНАЧ(B2:E2)>=4 – проверка условия «является ли среднее арифметическое равным или более 4?».

Запишем аналогичные формулы в остальных ячейках. В результате получим:

Как видно выше на рисунке студенты под номером: 2, 5, 7 и 8 — не получат стипендию.

Формула с логическим выражением для поиска истинного значения

Пример 2. На предприятии начисляют премии тем работникам, которые проработали свыше 160 часов за 4 рабочих недели (задерживались на работе сверх нормы) или выходили на работу в выходные дни. Определить работников, которые будут премированы.

Формула для расчета:

• B3>160 – условие проверки количества отработанных часов;
• C3=ИСТИНА() – условие проверки наличия часов работы в выходные дни.

Используя аналогичную формулу проведем вычисления для всех работников. Полученный результат:

То есть, оба условия возвращают значения ЛОЖЬ только в 4 случаях.

Формула с использованием логических функций ИЛИ И ЕСЛИ

Пример 3. В интернет-магазине выполнена сортировка ПК по категориям «игровой» и «офисный». Чтобы компьютер попал в категорию «игровой», он должен соответствовать следующим критериям:

1. CPU с 4 ядрами или таковой частотой не менее 3,7 ГГц;
2. ОЗУ – не менее 16 Гб
3. Видеоадаптер 512-бит или с объемом памяти не менее 2 Гб

Распределить имеющиеся ПК по категориям.

Для определения категории используем следующую формулу:

=4;C3>=3,7);D3>=16;ИЛИ(E3>=2;F3>=512));»Игровой»;»Офисный»)’ >

И(ИЛИ(B3>=4;C3>=3,7);D3>=16;ИЛИ(E3>=2;F3>=512)) – функция И, проверяющая 3 условия:

1. Если число ядер CPU больше или равно 4 или тактовая частота больше или равна 3,7, результат вычисления ИЛИ(B3>=4;C3>=3,7) будет значение истинное значение;
2. Если объем ОЗУ больше или равен 16, результат вычисления D3>=16 – ИСТИНА;
3. Если объем памяти видеоадаптера больше или равен 2 или его разрядность больше или равна 512 – ИСТИНА.

«Игровой» — значение, если функция И возвращает истинное значение;

«Офисный» — значение, если результат вычислений И – ЛОЖЬ.

Этапы вычислений для ПК1:

1. =ЕСЛИ(И(ИЛИ(ИСТИНА;ИСТИНА);ЛОЖЬ;ИЛИ(ЛОЖЬ;ЛОЖЬ));»Игровой»;»Офисный»)
2. =ЕСЛИ(И(ИСТИНА;ЛОЖЬ;ЛОЖЬ);»Игровой»;»Офисный»)
3. =ЕСЛИ(ЛОЖЬ;»Игровой»;»Офисный»)
4. Возвращаемое значение – «Офисный»

Результаты расчетов для всех ПК:

Особенности использования функций И, ИЛИ в Excel

Функция И имеет следующую синтаксическую запись:

=И( логическое_значение1 ;[логическое_значение2];…)

Функция ИЛИ имеет следующую синтаксическую запись:

=ИЛИ( логическое_значение1 ;[логическое_значение2];…)

Описание аргументов обеих функций:

• логическое_значение1 – обязательный аргумент, характеризующий первое проверяемое логическое условие;
• [логическое_значение2] – необязательный второй аргумент, характеризующий второе (и последующие) условие проверки.

Сравнения результатов, возвращаемых функциями И, ИЛИ в однотипных ситуациях:

1. Аргументами должны являться выражения, результатами вычислениями которых являются логические значения.
2. Результатом вычислений функций И/ИЛИ является код ошибки #ЗНАЧ!, если в качестве аргументов были переданы выражения, не возвращающие в результате вычислений числовые значения 1, 0, а также логические ИСТИНА, ЛОЖЬ.

1. Функция И чаще всего используется для расширения возможностей других логических функций (например, ЕСЛИ). В данном случае функция И позволяет выполнять проверку сразу нескольких условий.
2. Функция И может быть использована в качестве формулы массива. Для проверки вектора данных на единственное условие необходимо выбрать требуемое количество пустых ячеек, ввести функцию И, в качестве аргумента передать диапазон ячеек и указать условие проверки (например, =И(A1:A10>=0)) и использовать комбинацию Ctrl+Shift+Enter. Также может быть организовано попарное сравнение (например, =И(A1:A10>B1:B10)).
3. Как известно, логическое ИСТИНА соответствует числовому значению 1, а ЛОЖЬ – 0. Исходя из этих свойств выражения =(5>2)*(2>7) и =И(5>2;2>7) эквивалентны (Excel автоматически выполняет преобразование логических значений к числовому виду данных). Например, рассмотрим этапы вычислений при использовании данного выражения в записи =ЕСЛИ((5>2)*(2>7);”true”;”false”):

• =ЕСЛИ((ИСТИНА)*(ЛОЖЬ);”true”;”false”);
• =ЕСЛИ(1*0;”true”;”false”);
• =ЕСЛИ(0;”true”;”false”);
• =ЕСЛИ(ЛОЖЬ;”true”;”false”);
• Возвращен результат ”false”.

1. Функция ИЛИ чаще всего используется для расширения возможностей других функций.
2. В отличие от функции И, функция или эквивалентна операции сложения. Например, записи =ИЛИ(10=2;5>0) и =(10=2)+(5>0) вернут значение ИСТИНА и 1 соответственно, при этом значение 1 может быть интерпретировано как логическое ИСТИНА.
3. Подобно функции И, функция ИЛИ может быть использована как формула массива для множественной проверки условий.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

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

логическое_значение1 — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. =ИЛИ(A1>100;A2>100) Т.е. если хотя бы в одной ячейке (в A1 или A2 ) содержится значение больше 100, то формула вернет ИСТИНА, а если в обоих ячейках значения =ИЛИ(ЛОЖЬ;ЛОЖЬ) вернет ЛОЖЬ, а формулы =ИЛИ(ИСТИНА;ЛОЖЬ) или =ИЛИ(ЛОЖЬ;ИСТИНА) или =И(ИСТИНА;ИСТИНА) или =И(ЛОЖЬ;ЛОЖЬ;ИСТИНА) вернут ИСТИНА.

Функция воспринимает от 1 до 255 проверяемых условий. Понятно, что 1 значение использовать бессмысленно, для этого есть функция ЕСЛИ() . Чаще всего функцией ИЛИ() на истинность проверяется 2-5 условий.

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

Сама по себе функция ИЛИ() имеет ограниченное использование, т.к. она может вернуть только значения ИСТИНА или ЛОЖЬ, чаще всего ее используют вместе с функцией ЕСЛИ() : =ЕСЛИ(ИЛИ(A1>100;A2>100);»Бюджет превышен»;»В рамках бюджета»)

Т.е. если хотя бы в одной ячейке (в A1 или A2 ) содержится значение больше 100, то выводится Бюджет превышен , если в обоих ячейках значения И()

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

Эквивалентность функции ИЛИ() операции сложения +

В математических вычислениях EXCEL интерпретирует значение ЛОЖЬ как 0, а ИСТИНА как 1. В этом легко убедиться записав формулы =ИСТИНА+0 и =ЛОЖЬ+0

Следствием этого является возможность альтернативной записи формулы =ИЛИ(A1>100;A2>100) в виде =(A1>100)+(A2>100) Значение второй формулы будет =0 (ЛОЖЬ), только если оба аргумента ложны, т.е. равны 0. Только сложение 2-х нулей даст 0 (ЛОЖЬ), что совпадает с определением функции ИЛИ() .

Эквивалентность функции ИЛИ() операции сложения + часто используется в формулах с Условием ИЛИ, например, для того чтобы сложить только те значения, которые равны 5 ИЛИ равны 10: =СУММПРОИЗВ((A1:A10=5)+(A1:A10=10)*(A1:A10))

Проверка множества однотипных условий

Предположим, что необходимо сравнить некое контрольное значение (в ячейке B6 ) с тестовыми значениями из диапазона A6:A9 . Если контрольное значение совпадает хотя бы с одним из тестовых, то формула должна вернуть ИСТИНА. Можно, конечно записать формулу =ИЛИ(A6=B6;A7=B6;A8=B6;A9=B6) но существует более компактная формула, правда которую нужно ввести как формулу массива (см. файл примера ): =ИЛИ(B6=A6:A9) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )

Вместо диапазона с тестовыми значениями можно также использовать константу массива : =ИЛИ(A18:A21>)

В случае, если требуется организовать попарное сравнение списков , то можно записать следующую формулу: =ИЛИ(A18:A21>=B18:B21)

Если хотя бы одно значение из Списка 1 больше или равно (>=) соответствующего значения из Списка 2, то формула вернет ИСТИНА.

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

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 и что для этого надо?

Как задать условие в Excel? Используйте функцию ЕСЛИ, позволяющую логически сравнивать параметры и ожидаемые результаты. Она проверяет заданные параметры и в зависимости от правдивости показывает результат. Правильна запись имеет следующий вид: «ЕСЛИ (логическое выражение; [функция, когда правда]; [функция, когда ложь])». Ниже подробно рассмотрим, что это за опция, разберем особенности и примеры ее использования.

Что за функция в Excel

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

• И — все параметры должны иметь значения истины или лжи;
• ИЛИ — только один параметр Excel должен соответствовать заданному критерию;
• НЕ — указание факта, что сведения не соответствуют заданному показателю.

Как задать условие

Чтобы разобраться, как сделать условие в Excel, достаточно знать принципы правильной записи. С позиции синтаксиса правильная запись имеет следующее выражение: «ЕСЛИ (логическое выражение; [функция, когда правда]; [функция, когда ложь])». Как отмечалось, дополнительно применяются опции И, НЕ или ИЛИ в формулах форматирования. Если задать их правильно, опцию ЕСЛИ можно не использовать.

В большинстве случаев формат установки формул в Excel имеет следующий вид:

• И: =ЕСЛИ (И (условие; другое условие); параметр, когда ИСТИНА; параметр, когда ЛОЖЬ)

• ИЛИ: =ЕСЛИ (ИЛИ (условие; другое условие); показатель, когда ИСТИНА; показатель, когда ЛОЖЬ)

• НЕ: =ЕСЛИ (НЕ (условие); параметр, когда ИСТИНА; показатель, когда ЛОЖЬ)

Несколько примеров

Чтобы полностью разобраться, как в Экселе работает функция ЕСЛИ, необходимо привести несколько особенностей применения.

Базовый пример

Для начала рассмотрим, как задать основное правило для Excel на примере премии для мужчин к 23 февраля. В распоряжении бухгалтера таблица с зарплатой и приказ о перечислении денежного вознаграждения для мужчин в размере 2000 р к празднику.

1. Сделайте соответствующие колонки «Премия к 23 февраля» и пишите по 2000.
2. Задайте необходимый пол в таблице.
3. Поставьте следующую формулу: «ЕСЛИ(B6=»муж.»; «2000»; «0»)». Следовательно, если строка не будет соответствовать женщине, в выделенной графе будет ноль. Как только проверка натыкается на мужчину выполняется правило, и прописывается цифра 2000. Перед ней поставьте знак «равно».
4. Жмите на «Ввод», чтобы формула отобразилась в нижней ячейке. Для этого наведите на нужную ячейку в Excel, жмите на левую кнопку мышки и ведите курсор к низу.

Два и более условий

Бывают ситуации, когда нужно в Экселе задать не одно условие, а несколько. Для этих целей также применяется опция ЕСЛИ. В ситуации, когда условие в ячейке выполнено, отображается нужный результат. Когда оно не выполняется учитывается дополнительная команда. Для примера можно использовать ту же таблицу, что с премией к 23 февраля. В отличие от прошлого варианта мужчины, относящиеся к базовому персоналу, получат по 2000 р, а те, кто относится к дополнительному — 500 р.

Для начала нужно задать условие для Excel, что, если работник — женщина, выплата равна нулю. В ситуации, когда это мужчина, проверяется соответствие второму правилу. Так, если мужчина входит в группу базового персонала он получает 2000, а в ином случае — 500. Формула будет иметь следующий вид «=ЕСЛИ(B6=»жен.»;»0″; ЕСЛИ(C6=»Базовый персонал»; «2000»;»500″))». Эту строчку необходимо задать в верхней ячейке «Премия к 23 февраля». Как и в первом случае протяните расчет вниз.

Одновременное выполнение двух правил

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

• пол — женский;
• категория сотрудников — основные.

В остальных ситуациях вознаграждения не будет. Для записи в Excel используется следующая формула «=ЕСЛИ(И(B6=»муж.»; C6=»Базовый персонал»); «2000»; «0»)». Поставьте ее в нужную ячейку и копируйте на все секции.

Применение ИЛИ

Дополнительно к условию ЕСЛИ можно задать в Excel функцию ИЛИ. С ее помощью можно выявить ситуацию, когда параметр является правдивым. К примеру, вознаграждение предусмотрено только для мужчин на 23 февраля и для базовых сотрудников. Следовательно, для мужчин из вспомогательных работников или женщин такая опция не предусмотрена. В таких обстоятельствах запись имеет следующий вид — «=ЕСЛИ(ИЛИ(B6=»жен.»; C6=»Вспомогательный персонал»); «0»; «1000»)». Эту информацию необходимо записать в соответствующую ячейку Excel и протянуть вниз.

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

В комментариях расскажите, удалось ли вам разобраться, как задать условие в Excel, применяете ли вы подобные методы, и какие еще варианты можно использовать для решения поставленной задачи.