Как использовать или в excel

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

логическое_значение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.

Например, =И(A1>100;A2>100) Т.е. если в обеих ячейках A1 и A2 содержатся значения больше 100 (т.е. выражение A1>100 — ИСТИНА и выражение 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) Значение второй формулы будет =1 (ИСТИНА), только если оба аргумента истинны, т.е. равны 1. Только произведение 2-х единиц даст 1 (ИСТИНА), что совпадает с определением функции И() .

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

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

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

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

Вместо диапазона с границами можно также использовать константу массива : =И(A18:A21>)

OR function

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More. Less

Use the OR function, one of the logical functions, to determine if any conditions in a test are TRUE.

Example

Technical Details

The OR function returns TRUE if any of its arguments evaluate to TRUE, and returns FALSE if all of its arguments evaluate to FALSE.

One common use for the OR function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the OR function as the logical_test argument of the IF function, you can test many different conditions instead of just one.

OR(logical1, [logical2], . )

The OR function syntax has the following arguments:

Description

Required. The first condition that you want to test that can evaluate to either TRUE or FALSE.

Optional. Additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions.

• The arguments must evaluate to logical values such as TRUE or FALSE, or in arrays or references that contain logical values.
• If an array or reference argument contains text or empty cells, those values are ignored.
• If the specified range contains no logical values, OR returns the #VALUE! error value.
• You can use an OR array formula to see if a value occurs in an array. To enter an array formula, press CTRL+SHIFT+ENTER.

Examples

Here are some general examples of using OR by itself, and in conjunction with IF.

Description

Displays TRUE if A2 is greater than 1 OR less than 100, otherwise it displays FALSE.

Displays the value in cell A3 if it is greater than 1 OR less than 100, otherwise it displays the message «The value is out of range».

=IF(OR(A250),A2,»The value is out of range»)

Displays the value in cell A2 if it’s less than 0 OR greater than 50, otherwise it displays a message.

Sales Commission Calculation

Here is a fairly common scenario where we need to calculate if sales people qualify for a commission using IF and OR.

• =IF(OR(B14>=$B$4,C14>=$B$5),B14*$B$6,0) — IF Total Sales are greater than or equal to (>=) the Sales Goal, OR Accounts are greater than or equal to (>=) the Account Goal, then multiply Total Sales by the Commission %, otherwise return 0.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

Примеры формул с использованием функций ИЛИ И ЕСЛИ в Excel

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

В Excel содержится несколько логических функций, таких как ЕСЛИ, ЕСЛИОШИБКА, СУММЕСЛИ, И, ИЛИ и другие. Две последние на практике, как правило, самостоятельно не используют, поскольку результатом их вычислений может являться один из только двух возможных вариантов (ИСТИНА, ЛОЖЬ). При совместном использовании с функцией ЕСЛИ, они способны значительно расширить ее функционал.

Примеры использования формул с функциями ЕСЛИ, И, ИЛИ в Excel

Пример 1. При расчете стоимости количества потребленных кВт электроэнергии для абонентов учитывают следующие условия:

1. Если в квартире проживают менее 3 человек или за месяц было потреблено менее 100 кВт электроэнергии, ставка за 1 кВт составляет 4,35 рубля.
2. В остальных случаях ставка за 1кВт составляет 5,25 рубля.

Рассчитать сумму к оплате за месяц для нескольких абонентов.

Вид исходной таблицы данных:

Выполним расчет по формуле:

Растянем формулу для остальных ячеек с помощью функции автозаполнения. Результат расчета для каждого абонента:

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

Формула с функциями ЕСЛИ и СРЗНАЧ для отбора значений при условии

Пример 2. Абитуриенты, поступающие в университет на специальность «инженер-механик», обязаны сдать 3 экзамена по предметам математика, физика и русский язык. Максимальный балл за каждый экзамен – 100. Средний проходной балл за 3 экзамена составляет 75, при этом минимальная оценка по физике должна составить не менее 70 баллов, а по математике – 80. Определить абитуриентов, которые успешно сдали экзамены.

Вид исходной таблицы:

Для определения зачисленных студентов используем формулу:

• И(B4>=80;C4>=70;СРЗНАЧ(B4:D4)>=75) – проверяемые логические выражения согласно условию задачи;
• «Зачисл.» – результат, если функция И вернула значение ИСТИНА (все выражения, представленные в виде ее аргументов, в результате вычислений вернули значение ИСТИНА);
• «Не зач.» – результат, если И вернула ЛОЖЬ.

Используя функцию автозаполнения (сделав двойной щелчок по маркеру курсора в нижнем правом углу), получим остальные результаты:

Формула с логическими функциями И ЕСЛИ ИЛИ в Excel

Пример 3. Субсидии в размере 30% начисляются семьям со средним уровнем дохода ниже 8000 рублей, которые являются многодетными или отсутствует основной кормилец. Если число детей свыше 5, размер субсидии – 50%. Определить, кому полагаются субсидии, а кому – нет.

Вид исходной таблицы:

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

Выполним расчет для первой семьи и растянем формулу на остальные ячейки, используя функцию автозаполнения. Полученные результаты:

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

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

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

Как видно, по умолчанию можно выполнить проверку только одного условия, например, больше ли e3 числа 20? С использованием функции ЕСЛИ такую проверку можно выполнить так:

В результате будет возвращена текстовая строка «больше». Если нам потребуется узнать, принадлежит ли какое-либо значение указанному интервалу, потребуется сравнить это значение с верхней и нижней границей интервалов соответственно. Например, находится ли результат вычисления e3 в интервале от 20 до 25? При использовании одной лишь функции ЕСЛИ придется ввести следующую запись:

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

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

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

Запись «<>» означает неравенство, то есть, больше либо меньше некоторого значения. В данном случае оба выражения возвращают значение ИСТИНА, и результатом выполнения функции ЕСЛИ будет текстовая строка «верно». Однако, если бы выполнялась проверка ИЛИ(ОСТАТ(EXP(3);1)<>0;EXP(3)0 возвращает ИСТИНА.

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

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

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

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

Логические функции в Excel практическая работа

Синтаксис обеих функций выглядит следующим образом:

Логическое значение 1 является обязательным аргументом, а количество последующих зависит от поставленной задачи. Теперь рассмотрим как работают эти функции. У нас есть форма о результатах тестов, которые проводились среди сотрудников и указан проходной балл. Нам нужно узнать кто из сотрудников справился со всеми тестами, а кто нет. Для этого укажем в формуле, что будем проверять И первый тест, И второй, И третий, И четвёртый, каждый должен быть не менее 7 (ячейка D8):

В результате в ячейке G3 мы получили результат ИСТИНА, поскольку все условия выполнены. Обязательно используем абсолютные ссылки и копируем формулу до конца столбца. Теперь у нас есть результат для всех сотрудников:

Поскольку в ячейках E4, F4 и F6 у нас значение 6, условие не выполнилось и возвратился результат ЛОЖЬ и мы узнали, кто из сотрудников провалил хотя бы один тест, а кто справился со всеми и может продолжать работать. Но слова «ИСТИНА» или «ЛОЖЬ» не совсем корректно отображают смысл результатов. Поэтому добавим к функции ещё два выражения – «Отлично» будет использоваться, если ИСТИНА, и «Есть пересдачи» будет использоваться, если ЛОЖЬ. В этом нам поможет функция ЕСЛИ, в которую мы встроим функцию И:

Скопируем функцию до конца столбца и получим готовую таблицу:

Напротив ячеек, значение которых меньше 7, мы получили результат «Есть пересдачи».

Теперь рассмотрим работу функции ИЛИ. У нас всё те же тесты и сотрудники, но сейчас нам нужно проверить, есть ли у нас отличники хотя бы по одному тесту. То есть, есть ли среди значений ячеек C19, D19, E19, F19 величина 10:

Мы получили значение ИСТИНА, поскольку значение ячейки С19 равно 10. Теперь копируем нашу формулу до конца и получаем результат по остальным сотрудникам:

Для более корректной информативности заменим результат «ИСТИНА» на фразу «Есть оценки 10», а результат «ЛОЖЬ» — на фразу «Нет оценок 10». Для этого также используем функцию ЕСЛИ, в которую вложим формулу ИЛИ:

В ячейке С26 у нас расположено значение 10, поэтому формула возвратила значение если ИСТИНА – «Есть оценки 10». Скопируем формулу до конца столбца и получим готовую таблицу:



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

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

В ячейке G33 мы построили вложенную функцию и скопировали её до конца столбца. Результаты у нас получились те же, что и с использованием ЕСЛИ+И, однако, сама формула очень длинная, в ней легко будет запутаться, если нам нужно будет добавлять новые условия. К тому же работа с ней отнимет больше времени, хотя выполняют они задачу корректно и одинаково. Поэтому лучше отдать предпочтение функции ЕСЛИ с использованием функции И:

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

Результаты у нас получились те самые. Однако, если сравнить формулы этих двух примеров, функция ЕСЛИ+ИЛИ значительно короче и аккуратнее, особенно при добавлении новых условий:

Как работают формулы ЕСЛИ+И+ИЛИ, ЕСЛИ+ИЛИ+И

Иногда функции И, ИЛИ, ЕСЛИ используются одновременно. Прежде, чем углубиться в некоторые такие примеры, укрепим использование уже знакомых нам формул. У нас есть информация о бюджете различных отделов. Мы хотим проверить, превышает ли сумма бюджета определённого отдела значение 100 000 И превышают ли абсолютные расходы с начала года значение 50 000. Если эти два условия будут выполнены, укажем слово «Выполнено», а если нет – знак «-». Указываем абсолютные ссылки для ячеек с условиями, иначе при копировании формулы, значения тоже будут опускаться:

Мы получили результат «-», поскольку только одно условие выполнилось – 70 000 больше 50 000, но 80 000 не больше 100 000. Скопируем нашу формулу до конца столбца и определим, какие отделы соответствуют условиям:

В этом примере мы задали определенные условия, которые должны были выполниться одновременно. Теперь рассмотрим пример, где расширим функционал формулы через добавление функции ИЛИ. Оставим два условия, известные нам из предыдущего примера – бюджет превышает 100 000, абсолютные расходы превышают 50 000 и добавим новые условия через функцию ИЛИ: был ли перерасход бюджета в прошлом году ИЛИ был ли перерасход бюджета больше, чем на 20%. Теперь у нас 4 условия, но только в случае, если три из них верны – мы получим значение «Выполнено»:

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

Производственный отдел и НИОКР выполнили наши условия, поскольку бюджет превышает 100 000 И абсолютные расходы превышают 50 000, кроме этого, отдел НИОКР имеет превышение бюджета больше, чем на 20%, а производственный отдел имеет превышение, но меньше чем на 20% — этого достаточно для выполнения наших условий.

Теперь выполним поиск отделов, которые будут соответствовать одному из заданных условий. Принцип работы тот же, что и в примере с выполнением условий «Бюджет больше 100 000 И абсолютные расходы больше 50 000», но на этот раз изменим требования – нам нужны отделы, которые выполняют ХОТЯ БЫ одно требование: бюджет больше 150 000 или расходы абсолютные больше 160 000. В этом нам помогут функции ЕСЛИ + ИЛИ:

Копируем формулу и видим, что статус «Выполнено» получили те отделы, которые удовлетворили одно из требований:

Наконец, рассмотрим последнюю комбинацию функций ЕСЛИ + ИЛИ + И. В этом случае нам надо добавить последнюю функцию И в качестве третьего условия функции ИЛИ. Для функции И мы укажем два требования – наличие превышения бюджета, превышение больше 10%:

Эта комбинация ищет соответствие ХОТЯ БЫ одному требованию из трёх:

1. Бюджет больше 150 000.
2. Абсолютные расходы больше 160 000.
3. Наличие превышения прошлогоднего бюджета, показатель превышения больше 10%.

Копируем формулу до конца таблицы и проверим, какие отделы выполнили наши требования:

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

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