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

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

Microsoft 365 позволяет максимально эффективно использовать время

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

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

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

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

ЕСЛИ(И()): ЕСЛИ(И(лог_выражение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, поэтому формула возвращает значение ЛОЖЬ.

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

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

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

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

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

Источник:
http://support.microsoft.com/ru-ru/office/%D0%B8%D1%81%D0%BF%D0%BE%D0%BB%D1%8C%D0%B7%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5-%D0%B5%D1%81%D0%BB%D0%B8-%D1%81-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F%D0%BC%D0%B8-%D0%B8-%D0%B8%D0%BB%D0%B8-%D0%B8-%D0%BD%D0%B5-d895f58c-b36c-419e-b1f2-5c193a236d97

Логические функции в программе Microsoft Excel

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

Основные операторы

Существует несколько операторов логических функций. Среди основных следует выделить такие:

Существуют и менее распространенные логические функции.

У каждого из вышеуказанных операторов, кроме первых двух, имеются аргументы. Аргументами могут выступать, как конкретные числа или текст, так и ссылки, указывающие адрес ячеек с данными.

Функции ИСТИНА и ЛОЖЬ

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

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

Функции И и ИЛИ

Функция И является связующим звеном между несколькими условиями. Только при выполнении всех условий, которые связывает данная функция, она возвращает значение ИСТИНА. Если хотя бы один аргумент сообщает значение ЛОЖЬ, то и оператор И в целом возвращает это же значение. Общий вид данной функции: =И(лог_значение1;лог_значение2;…) . Функция может включать в себя от 1 до 255 аргументов.

Функция ИЛИ, наоборот, возвращает значение ИСТИНА даже в том случае, если только один из аргументов отвечает условиям, а все остальные ложные. Её шаблон имеет следующий вид: =И(лог_значение1;лог_значение2;…) . Как и предыдущая функция, оператор ИЛИ может включать в себя от 1 до 255 условий.

Функция НЕ

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

Функции ЕСЛИ и ЕСЛИОШИБКА

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

Оператор ЕСЛИОШИБКА, в случае если аргумент является истиной, возвращает в ячейку его собственное значение. Но, если аргумент ошибочный, тогда в ячейку возвращается то значение, которое указывает пользователь. Синтаксис данной функции, содержащей всего два аргумента, выглядит следующем образом: =ЕСЛИОШИБКА(значение;значение_если_ошибка) .

Функции ЕОШИБКА и ЕПУСТО

Функция ЕОШИБКА проверяет, не содержит ли определенная ячейка или диапазон ячеек ошибочные значения. Под ошибочными значениями понимаются следующие:

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

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

Пример применения функций

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

Имеем список работников предприятия с положенными им заработными платами. Но, кроме того, всем работникам положена премия. Обычная премия составляет 700 рублей. Но пенсионерам и женщинам положена повышенная премия в размере 1000 рублей. Исключение составляют работники, по различным причинам проработавшие в данном месяце менее 18 дней. Им в любом случае положена только обычная премия в размере 700 рублей.

Попробуем составить формулу. Итак, у нас существует два условия, при исполнении которых положена премия в 1000 рублей – это достижение пенсионного возраста или принадлежность работника к женскому полу. При этом, к пенсионерам отнесем всех тех, кто родился ранее 1957 года. В нашем случае для первой строчки таблицы формула примет такой вид: =ЕСЛИ(ИЛИ(C4 . Но, не забываем, что обязательным условием получения повышенной премии является отработка 18 дней и более. Чтобы внедрить данное условие в нашу формулу, применим функцию НЕ: =ЕСЛИ(ИЛИ(C4 .

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

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

Как видим, логические функции являются очень удобным инструментом для проведения расчетов в программе Microsoft Excel. Используя сложные функции, можно задавать несколько условий одновременно и получать выводимый результат в зависимости от того, выполнены эти условия или нет. Применение подобных формул способно автоматизировать целый ряд действий, что способствует экономии времени пользователя.

Источник:
http://lumpics.ru/logical-functions-in-excel/

Логические функции в Excel. Часть №1

Доброго времени суток уважаемый читатель!

Вот я, наконец, то и добрался к своим любимым логическим функциям Excel. Признаю, что мне они наиболее ближе и удобнее в использовании нежели другие разделы функций, каюсь грешен, должны же быть и у меня любимчики… А если говорить серьезно, логические функции одни из самых распространённых в использовании функции и самые полезные в работе бухгалтеров, экономистов, да и в принципе любого специалиста финансовых дисциплин.

Читайте также  Вставка номеров страниц на листы - Служба поддержки Office

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

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

В своем арсенале Excel содержит 7 логических функций, хотя в зависимости от версий их может быть и меньше, и больше. Найти перечень доступных функций вы можете в панели управления на вкладке «Формулы», в блоке «Библиотека функций», нажав кнопку «Логические» с выпадающем перечнем логических функций. Или с помощью мастера функций: А вот собственно и перечень логических функций, которые мы будем рассматривать в первой части этой статье:

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

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

Функция ЕСЛИ возвращает один результат в случаях, если аргумент «логическое выражение» при вычислении получает ответ «ИСТИНА» и совсем другой, если ответ «ЛОЖЬ». Функция может содержать до семи логических условий внутри себя для аргумента «_значение если истина_», а для аргумент «_значение если ложь_» умеет выполнять функции или команды дальнейшего действия (к примеру, использование функции ПЕРЕЙТИ).

Синтаксис функции:

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

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

Пример №1:

Рассмотрим усложнённый пример со многими условиями, вложенными в функцию ЕСЛИ. У нас есть персонал, которому в зависимости от стажа работы, производится доплата к ставке заработной платы. Всего будет три доплаты, стаж работы 3 года – 10%, 10 лет – 20% и 20 лет – 30%. Для получения результата и автоматического расчёта доплаты в зависимости от стажа нам нужна формула такого вида:

=ЕСЛИ(C2>=20;»30%»;ЕСЛИ(C2>=10;»20%»;ЕСЛИ(C2>=3;»10%»;»0%»))).

Пример №2:

Рассмотрим еще один сложный пример, в котором мы будем определять, и идентифицировать виды банковских карточек по их номерам. Для первого аргумента используем текстовую функцию ЛЕВСИМВ для захвата первых 4 цифр, которые и будут условием определения. Для карты «Visa Electron» установим идентификатор 1234, для карты «Maestro» — 5678 и все остальные карты будут по умолчанию «MasterCard Standard». Теперь собственно формула, которая поможет нам определить результат будет такова:

=ЕСЛИ( ЛЕВСИМВ (B14;4)= «1234»; «Visa Electron»; ЕСЛИ ( ЛЕВСИМВ (B14;4)=»5678»; «Maestro»; «MasterCard Standard»)).

Абсолютно простая логическая функция ИСТИНА, не имеет никаких аргументов и умеет возвращать всего лишь логическое значение «ИСТИНА». Имеет смысл использовать только в связке с другими функциями.

Синтаксис функции:

=ИСТИНА().

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

Синтаксис функции:

=ЛОЖЬ().

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

Синтаксис функции:

= НЕ(_логическое_значение_), где:

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

Пример:

Обращаю внимание! Все логические функции в своих выражениях используют знаки сравнения «=», « », « =» или «<>», в результате чего получаются значения «ИСТИНА» и «ЛОЖЬ».

А на этом у меня пока всё, жду вас на страницах второй части статьи! Я очень надеюсь, что описанные всех логических функций в Excel в этой части были ёмкими и понятными. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

Богатство приносит не большое состояние, а скромные потребности.
Эпиктет (Epictetus)

Источник:
http://topexcel.ru/logicheskie-funkcii-v-excel-chast-1/

Логические операции в Excel

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

Кроме этого, логические функции могут применять с целью проверить, в какой мере содержимое ячейки соответствует определенному критерию. Также могут проверяться и другие логические значения.

Операторы сравнения

Каждое выражение содержит операторы сравнения. Они бывают следующими:

  1. = – значение 1 равно значению 2.
  2. > – значение 1 больше значения 2.
  3. = значение 1 или идентично значению 2, или больше него.
  4. значение 1 или больше значения 2 или меньше него.

Как следствие, Excel выдает один из двух возможных результатов: истина (1) или ложь (2).

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

Функция «Истина»

Для использования этой функции не нужно указывать никаких аргументов, и она всегда возвращает «Истина» (что соответствует цифре 1 двоичной системы счисления).

Пример формулы – =ИСТИНА() .

Функция «Ложь»

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

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

Ссылки на ячейки с логическими значениями также применяются в качестве параметров функции. Максимальное количество аргументов, которые можно использовать – 255. Но обязательное требование – наличие хотя бы одного из них в скобках.

Синтаксис этой функции такой:

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

На данном скриншоте видно, что каждый аргумент передает истинное значение, поэтому в результате использования этой формулы можно получить соответствующий результат.

Функция «Или»

Проверяет несколько значений на предмет соответствия определенному критерию. Если какой-то из них соответствует, то функция возвращает истинное значение (1). Максимальное количество аргументов в данной ситуации также равняется 255, и необходимо обязательно указывать один параметр функции.

Если говорить про функцию ИЛИ , то в случае с ней таблица истинности будет следующей.

Синтаксис формулы следующий:

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

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

Функция «Не»

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

Читайте также  Изменение заливки, контура или эффектов элемента диаграммы - Служба поддержки Office

То, какой результат будет выдан, зависит от того, какой исходный аргумент принимается функцией. Если, например, применять функцию «И» вместе с функцией «НЕ», то таблица будет следующей.

При использовании же функции «Или» в сочетании с функцией «Не», то таблица обретет такой вид.

Синтаксис этой функции очень прост: =НЕ(принимаемое логическое значение).

Эта функция по праву может называться одной из наиболее востребованных. Она проверяет определенное выражение на предмет соответствия конкретному условию. На результат влияет истинность или ложность данного утверждения.

Если говорить конкретно про эту функцию, то ее синтаксис будет несколько сложнее.

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

Давайте более детально рассмотрим пример, который был приведен на скриншоте выше. Здесь в качестве первого параметра выступает функция ИСТИНА , которая проверяется программой. По результатам такого чекинга возвращается второй аргумент. Третий же опускается.

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

Например, есть несколько кредитных карт, имеющих номера, начинающиеся первыми четырьмя цифрами, характеризующими платежную систему, обслуживающую карту. То есть, варианта два – Visa и Mastercard. Для проверки типа карты необходимо использовать такую формулу с применением двух вложенных ЕСЛИ .

=ЕСЛИ(ЛЕВСИМВ(A2;4)=”1111″; “Visa”;ЕСЛИ(ЛЕВСИМВ(A2;4)=”2222″;”Master Card”;”карта не определена”))

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

Аналогично можно добиться приличной вложенности и проверять содержимое ячейки или диапазона на предмет соответствия нескольким условиям.

Функция ЕСЛИОШИБКА

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

Эта формула имеет следующий синтаксис:

Как можно использовать функцию?

В примере, приведенном ниже, видна ошибка в первом аргументе функции. Поэтому формула возвращает ответ, что запрещено деление на ноль. Первым параметром функции могут быть любые другие формулы. Человек может самостоятельно решать, какое содержимое может быть там.

Как могут использоваться логические функции на практике

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

Изначально нужно сформировать такую таблицу.

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

Логическое выражение, содержащееся в первом аргументе функции, составлено с использованием операторов > и =. Простыми словами, изначально критерий следующий: при значении ячейки, большем или равном 8, выполняется формула, поставленная во втором аргументе. Выражаясь терминологическим языком, если первое условие оказывается истинным, то выполняется второй аргумент. Если же ложным – третий.

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

В этом случае необходимо в поле ввода формулы ввести такую строку.

=ЕСЛИ(И(C2>=8);B2/2;ЕСЛИ(И(C2>=5);B2/1,5;B2))

Функция ЕСЛИ допускает использование текстовых строк в аргументах, если это требуется.

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

Если при записи условия использовался логический оператор ИЛИ , то его необходимо расшифровывать следующим образом. Если в ячейке C2 находится число 10 или больше или если в ячейке D2 находится значение меньшее, чем 300, то необходимо выдать в соответствующей ячейке значение «списан».

Если же условие не выполняется (то есть, оказывается ложным), то формула автоматически возвращает пустое значение. Таким образом, если товар был продан ранее или лежит на складе меньше, чем необходимо, или он был уценен до значения, меньше порогового, то остается пустая ячейка.

Допускается применение и других функций как аргументов. Например, допустимо использование математических формул.

Предположим, есть несколько учеников, которые перед поступлением в гимназию сдают несколько экзаменов. В качестве проходного балла стоит оценка в 12. А чтобы поступить, обязательно необходимо, чтобы стояло хотя бы 4 балла по математике. В результате, Excel должен составить отчет о поступлении.

Для начала необходимо построить следующую таблицу.

Наша задача – сравнить сумму всех оценок с проходным баллом, а кроме этого убедиться, чтобы оценка по математике была ниже 4. И в графе с результатом необходимо указать «принят» или «нет».

Нам необходимо ввести следующую формулу.

С помощью логического оператора И необходимо проверить, насколько истинными являются эти условия. А чтобы определить итоговый балл, необходимо использовать классическую функцию СУММ .

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

Предположим, перед нами появляется необходимость понять, сколько стоят товары после оценки в целом. Если стоимость продукта оказывается ниже, чем среднее значение, то необходимо списать этот товар.

Для этого можно использовать ту же таблицу, которая была приведена выше.

Чтобы решить эту задачу, необходимо использовать такую формулу.

В этом случае, допустим, нам необходимо определить средние продажи. Для этого необходимо составить такую таблицу.

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

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

В качестве первого аргумента используется диапазон значений, которые будут проверяться. Во втором указывается условие, в нашем случае это ячейка B9. А вот в качестве третьего аргумента используется диапазон, который будет применяться для того, чтобы осуществлять расчет среднего арифметического значения.

Функция СРЗНАЧЕСЛИ позволяет сравнить значение ячейки B9 с теми значениями, которые располагаются в диапазоне B2:B7, где перечисляются номера магазинов. Если данные совпадают, то формула подсчитывает среднее арифметическое диапазона C2:C7.

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

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

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

Источник:
http://office-guru.ru/excel/functions-logicheskie/logicheskie-operacii-v-excel.html

Использование логических функций в Excel

Табличный редактор Эксель – очень гибкий и мощный инструмент для структурированной работы с данными. Среди большого количества его эффективных инструментов стоит отдельно выделить логические операторы, в основе использования которых лежит признание выражения истинным или ложным. Эти функции эффективно встроены в инструментарий формул и позволяют создавать гибкие условия для различных вычислений или заполнения ячеек на основе логических выражений. В данной статье будут описаны основные используемые функции, а также приведен практический пример использования логических операторов в Excel.

Основные логические функции, используемые в Эксель

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

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

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

Операторы ИСТИНА и ЛОЖЬ

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

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

Читайте также  Как сделать график в Excel

Операторы И и ИЛИ

Синтаксис оператора И выглядит следующим образом:

=И(лог_значение1; лог_значение2; …), возможное количество используемых аргументов – от 1 до 255.

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

Синтаксис оператора ИЛИ:

=ИЛИ(лог_значение1; лог_значение2; …), возможное количество используемых аргументов – от 1 до 255.

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

Оператор НЕ

Синтаксис функции НЕ: =НЕ(лог_значение).

Количество аргументов оператора НЕ – всегда один. Соответственно, результат функции (ИСТИНА/ЛОЖЬ) полностью зависит только от значения аргумента.

Операторы ЕСЛИ и ЕСЛИОШИБКА

Общий синтаксис функции ЕСЛИ представляет собой конструкцию:

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

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

Синтаксис оператора ЕСЛИОШИБКА имеет вид:

=ЕСЛИОШИБКА(значение;значение_если_ошибка).

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

Операторы ЕОШИБКА и ЕПУСТО

Оператор ЕОШИБКА имеет следующую структуру:

=ЕОШИБКА(значение)

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

Примеры значений в некорректно заполненных ячейках:

Аргумент функции – адрес конкретной ячейки или ссылка на диапазон ячеек.

Формула функции ЕПУСТО выглядит следующим образом:

=ЕПУСТО(значение)

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

Практический пример использования логических функций

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

В качестве исходных данных – таблица со сведениями о работниках, в которой указан их пол и возраст.

Нам необходимо произвести расчет премии. Ключевые условия, от которых зависит размер премии:

  • величина обычной премии, которую получат все сотрудники без исключения – 3 000 руб.;
  • сотрудницам женского пола положена повышенная премия – 7 000 руб.;
  • молодым сотрудникам (младше 1984 г. рождения) положена повышенная премия – 7 000 руб.;

Выполним необходимые расчеты, используя логические функции.

  1. Встаем в первую ячейку столбца, в которой хотим посчитать размеры премий и щелкаем кнопку “Вставить функцию” (слева от сроки формул).
  2. В открывшемся Мастере функций выбираем категорию “Логические”, затем в предложенном перечне операторов кликаем по строке “ЕСЛИ” и жмем OK.
  3. Теперь нам нужно задать аргументы функции. Так как у нас не одно, а два условия получения повышенной премии, причем нужно, чтобы выполнялось хотя бы одно из них, чтобы задать логическое выражение, воспользуемся функцией ИЛИ. Находясь в поле для ввода значения аргумента “Лог_выражение” кликаем в основной рабочей области книги на небольшую стрелку вниз, расположенную в левой верхней части окна программы, где обычно отображается адрес ячейки. В открывшемся списке функций выбираем оператор ИЛИ, если он представлен в перечне (или можно кликнуть на пункт “Другие функции” и выбрать его в новом окне Мастера функций, как мы изначально сделали для выбора оператора ЕСЛИ).
  4. Мы переключимся в окно аргументов функци ИЛИ. Здесь задаем наши условия получения премии в 7000 руб.:
    • год рождения позже 1984 года;
    • пол – женский;
  5. Теперь обращаем внимание на строку формул. Кликаем в ней на название первоначального оператора ЕСЛИ, чтобы переключиться в аргументы этой функции.
  6. Заполняем аргументы функции и щелкаем OK:
    • в значении “Истина” пишем цифру 7000;
    • в значении “Ложь” указываем цифру 3000;
  7. Результат работы логических операторов отобразится в первой ячейке столбца, которую мы выбрали. Как мы можем видеть, окончательный вид формулы выглядит следующим образом:
    =ЕСЛИ(ИЛИ(C2>1984;D2=»жен.»);7000;3000) .
    Кстати, вместо использования Мастера функций можно было вручную составить и прописать данную формулу в требуемой ячейке.
  8. Чтобы рассчитать премию для всех сотрудников, воспользуемся Маркером заполнения. Наведем курсор на правый нижний угол ячейки с формулой. После того, как курсор примет форму черного крестика (это и есть Маркер заполнения), зажимаем левую кнопку мыши и протягиваем выделение вниз, до последней ячейки столбца.
  9. Все готово. Благодаря логическим операторам мы получили заполненные данные для столбца с премиями.

Заключение

Логические операторы используются практически во всех сложных формулах, в которых значение в ячейке зависит от соблюдения одного или нескольких условий и позволяют строить гибкие конструкции, объединяя простые функции и расчеты в одной ячейке. Это дает возможность значительно сократить время на обработку данных и повысить эффективность работы, так как снимает большое количество промежуточных шагов в расчетах и вычислениях.

Источник:
http://microexcel.ru/logicheskie-funkczii/

Логические функции Excel

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

= Больше или равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

Функция ЕСЛИ (IF) имеет следующий синтаксис:

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

Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае — 20:

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например:

=ЕСЛИ(А1>=4;»Зачет сдал»;»Зачет не сдал»)

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

Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например:

Эта формула возвращает значение 10, если ячейка А1 содержит строку «Динамо», и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

Функции И, ИЛИ, НЕ

Функции И (AND), ИЛИ (OR), НЕ (NOT) — позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

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

Функция НЕ имеет только один аргумент и следующий синтаксис:

=НЕ(логическое_значение)

Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Приведем пример. Пусть Excel возвращает текст «Прошел», если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:

Если значение в ячейке А1 является целым числом, формула читается следующим образом: «Если значение в ячейке А1 равно 100, возвратить строку «Всегда». В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить «Обычно». В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку «Иногда». И, если ни одно из этих условий не выполняется, возвратить строку «Никогда». Всего допускается до 7 уровней вложения функций ЕСЛИ.

Функции ИСТИНА и ЛОЖЬ

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

=ИСТИНА()
=ЛОЖЬ()

Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение «Проходите», если выражение в ячейке А1 имеет значение ИСТИНА:

В противном случае формула возвратит «Стоп».

Функция ЕПУСТО

Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:

=ЕПУСТО(значение)

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

В начало страницы

В начало страницы

Источник:
http://on-line-teaching.com/excel/lsn024.html