Функция или и функция если в excel

Использование оператора «ЕСЛИ» в Microsoft Excel

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

Общее определение и задачи

«ЕСЛИ» является стандартной функцией программы Microsoft Excel. В ее задачи входит проверка выполнения конкретного условия. Когда условие выполнено (истина), то в ячейку, где использована данная функция, возвращается одно значение, а если не выполнено (ложь) – другое.

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

Пример использования «ЕСЛИ»

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

  1. Имеем таблицу заработной платы. Всем женщинам положена премия к 8 марту в 1000 рублей. В таблице есть колонка, где указан пол сотрудников. Таким образом, нам нужно вычислить женщин из предоставленного списка и в соответствующих строках колонки «Премия к 8 марта» вписать по «1000». В то же время, если пол не будет соответствовать женскому, значение таких строк должно соответствовать «0». Функция примет такой вид: «ЕСЛИ(B6=»жен.»; «1000»; «0»)» . То есть когда результатом проверки будет «истина» (если окажется, что строку данных занимает женщина с параметром «жен.»), то выполнится первое условие — «1000», а если «ложь» (любое другое значение, кроме «жен.»), то соответственно, последнее — «0».
  2. Вписываем это выражение в самую верхнюю ячейку, где должен выводиться результат. Перед выражением ставим знак «=».

Так мы получили таблицу со столбцом, заполненным при помощи функции «ЕСЛИ».

Пример функции с несколькими условиями

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

  1. Для примера возьмем все ту же таблицу с выплатами премии к 8 марта. Но на этот раз, согласно условиям, размер премии зависит от категории работника. Женщины, имеющие статус основного персонала, получают бонус по 1000 рублей, а вспомогательный персонал получает только 500 рублей. Естественно, что мужчинам этот вид выплат вообще не положен независимо от категории.
  2. Первым условием является то, что если сотрудник — мужчина, то величина получаемой премии равна нулю. Если же данное значение ложно, и сотрудник не мужчина (т.е. женщина), то начинается проверка второго условия. Если женщина относится к основному персоналу, в ячейку будет выводиться значение «1000», а в обратном случае – «500». В виде формулы это будет выглядеть следующим образом: «=ЕСЛИ(B6=»муж.»;»0″; ЕСЛИ(C6=»Основной персонал»; «1000»;»500″))» .
  3. Вставляем это выражение в самую верхнюю ячейку столбца «Премия к 8 марта».

Как и в прошлый раз, «протягиваем» формулу вниз.

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

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

  1. Например, в нашей ситуации премия к 8 марта в размере 1000 рублей выдается только женщинам, которые являются основным персоналом, а мужчины и представительницы женского пола, числящиеся вспомогательным персоналом, не получают ничего. Таким образом, чтобы значение в ячейках колонки «Премия к 8 марта» было 1000, нужно соблюдение двух условий: пол – женский, категория персонала – основной персонал. Во всех остальных случаях значение в этих ячейках будет рано нулю. Это записывается следующей формулой: «=ЕСЛИ(И(B6=»жен.»; C6=»Основной персонал»); «1000»; «0»)» . Вставляем ее в ячейку.

Копируем значение формулы на ячейки, расположенные ниже, аналогично продемонстрированным выше способам.

Пример использования оператора «ИЛИ»

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

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

«Протягиваем» результаты вниз.

Как видим, функция «ЕСЛИ» может оказаться для пользователя хорошим помощником при работе с данными в Microsoft Excel. Она позволяет отобразить результаты, соответствующие определенным условиям.

Отблагодарите автора, поделитесь статьей в социальных сетях.

ШКОЛА ПРОГРАММИРОВАНИЯ

Сообщение об ошибке

Функции Excel.Функция И(), ИЛИ()

На прошлом уроке «Функции Excel. Функция ЕСЛИ» была рассмотрена работа со встроенной функцией ЕСЛИ(). Все бы хорошо, но возникают такие ситуации, и довольно часто, когда вложенностью условия в условие не обойтись. Например, элементарно необходимо проверить следующее: 1 больше или равно x и x меньше или равно 5 . Реализовать такое условие несколькими ЕСЛИ довольно громоздко и проблематично, а в некоторых ситуациях — вообще невозможно. Для расширения функционала ЕСЛИ и облегчения составления формул с условием, в Excel имеются еще пара полезных функции — И() и ИЛИ().

Функция И() используется тогда, когда необходимо проверить несколько условий следующим образом — Условие 1 И Условие 2. При этом все условия должны быть истинными. Результатом работы данной функции является ИСТИНА или ЛОЖЬ (TRUE / FALSE). Пример: ЕСЛИ а = b И а=с ТОГДА значение 1 ИНАЧЕ значение 2.
Как видно из примера, значение 1 будет только в том случае, если все условия верны.

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

Функция ИЛИ() используется тогда, когда необходимо проверить несколько условий следующим образом — Условие 1 ИЛИ Условие 2. Результат функции будет истинным, если хотя бы одно из условий истинно. Пример: ЕСЛИ а = b ИЛИ а=с ТОГДА значение 1 ИНАЧЕ значение 2.

Синтаксис функций И() и ИЛИ() одинаков: Функция(Условие 1; Условие 2; Условие 3 и до 30-ти условий). Результат ИСТИНА или ЛОЖЬ.

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

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

Пример 1.
Столбец А, начиная с первой строки, содержит 56, 55, 88, 6, 74. Столбец В — 52, 55, 88, 4, 25. Столбец С — 53, 55, 88, 6, 25. С помощью функций ЕСЛИ и И необходимо определить строки, значения которых равны следующим образом А=В и В=С.

Переходим в ячейку D1 и с помощью мастера функций вводим следующее

Формула будет выглядеть так: “ =ЕСЛИ(И(A1=B1;B1=C1);»Все значения равны»;»Значения не равны») “

Пример 2.

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

Мастер функций выглядит так:

Формула: “ =ЕСЛИ(ИЛИ(A1=B1;B1=C1;A1=C1);»Есть равные значения»;»Нет равных значений»)”

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

И напоследок рассмотрим еще один пример с функцией И и ИЛИ из реальной жизни.

Пример 3.

В таблицу занесены процент выполнения заданий, необходимо проценты перевести в пятибалльную систему следующим образом:

1 – от 0% до 10%
2- от 11% до 30%
3- от 31% до 40%
4- от 41% до 75%
5-от 76% до 100%

Таблица следующего вида:

Составим формулу следующего вида:

=ЕСЛИ(ИЛИ(И(B1>0;B1 0,11;B1 0,31;B1 0,41;B1 0,76;B1

  • Добавить комментарий
  • Кол-во просмотров: 137052

Функция OR (ИЛИ) в Excel. Как использовать?

Функция OR (ИЛИ) в Excel используется для сравнения двух условий.

Что возвращает функция

Возвращает логическое значение TRUE (Истина), при выполнении условий сравнения в функции и отображает FALSE (Ложь), если условия функции не совпадают.

=OR(logical1, [logical2],…) – английская версия

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

Аргументы функции

  • logical1 (логическое_значение1) – первое условие которое оценивает функция по логике TRUE или FALSE;
  • [logical2]([логическое значение2]) – (не обязательно) это второе условие которое вы можете оценить с помощью функции по логике TRUE или FALSE.

Дополнительная информация

  • Функция OR (ИЛИ) может использоваться с другими формулами.

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

Например, если вы хотите проверить значение в ячейке А1 по условию: “Если значение больше “0” или меньше “100” то… “ – вы можете использовать следующую формулу:

=IF(OR(A1>100,A1 100;A1

  • Аргументы функции должны быть логически вычислимы по принципу TRUE или FALSE;
  • Текст и пустые ячейки игнорируются функцией;
  • Если вы используете функцию с не логически вычисляемыми значениями – она выдаст ошибку;
  • Вы можете тестировать максимум 255 условий в одной формуле.

Примеры использования функции OR (ИЛИ) в Excel

Пример 1. Используем аргументы TRUE и FALSE в функции OR (ИЛИ)

Вы можете использовать TRUE/FALSE в качестве аргументов. Если любой из них соответствует условию TRUE, функция выдаст результат TRUE. Если оба аргумента функции соответствуют условию FALSE, функция выдаст результат FALSE.

Она может использовать аргументы TRUE и FALSE в кавычках.

Пример 2. Используем ссылки на ячейки, содержащих TRUE/FALSE

Вы можете использовать ссылки на ячейки со значениями TRUE или FALSE. Если любое значение из ссылок соответствует условиям TRUE, функция выдаст TRUE.

Пример 3. Используем условия с функцией OR (ИЛИ)

Вы можете проверять условия с помощью функции OR (ИЛИ) . Если любое из условий соответствует TRUE, функция выдаст результат TRUE.

Пример 4. Используем числовые значения с функцией OR (ИЛИ)

Число “0” считается FALSE в Excel по умолчанию. Любое число, выше “0”, считается TRUE (оно может быть положительным, отрицательным или десятичным числом).

Пример 5. Используем функцию OR (ИЛИ) с другими функциями

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

На примере ниже показано, как использовать функцию вместе с IF (ЕСЛИ) :

На примере выше, мы дополнительно используем функцию IF (ЕСЛИ) для того, чтобы проверить несколько условий. Формула проверяет значение ячеек А2 и A3. Если в одной из них значение более чем “70”, то формула выдаст “TRUE”.

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

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

=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

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

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

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

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

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

Условие, которое нужно проверить.

Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

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

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

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

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

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

Ниже приведены формулы с расшифровкой их логики.

=ЕСЛИ(И(A2>0;B2 0;B4 50);ИСТИНА;ЛОЖЬ)

Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.

Если значение A7 («синий») НЕ равно «красный», возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.

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

Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.

Ниже приведены формулы с расшифровкой их логики.

Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.

=ЕСЛИ(И(A3>B2;A3 B2;A4 B2);ИСТИНА;ЛОЖЬ)

Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.

Использование операторов И, ИЛИ и НЕ с условным форматированием

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

На вкладке Главная выберите Условное форматирование > Создать правило. Затем выберите параметр Использовать формулу для определения форматируемых ячеек, введите формулу и примените формат.

«Изменить правило» с параметром «Формула»» />

Вот как будут выглядеть формулы для примеров с датами:

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

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

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

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

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

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

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

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

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

    ИЛИ(B3 =80;C4>=70;СРЗНАЧ(B4:D4)>=75);»Зачисл.»;»Не зач.»)’ class=’formula’>

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

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

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

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

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

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

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

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

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

Похожие статьи

Читать еще:  Основные функции эксель с примерами
Ссылка на основную публикацию
Похожие публикации
Adblock
detector