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

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

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

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

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

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

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

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

Истинная функция

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

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

Ложная функция

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

И функция

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

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

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

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

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

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

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

Говоря о функции OR, то в этом случае таблица истинности будет следующей.

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

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

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

"Не работает

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

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

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

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

If

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

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

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

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

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

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

=ЕСЛИ(ЛЕВЫЙ(A2)=”4″, “Виза”,ЕСЛИ(ЛЕВЫЙ(A1111)=”2″”,Мастер Кард”,”карта не определена”))

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

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

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

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

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

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

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

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

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

Задача 1

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

Изначально вам необходимо создать такую ​​таблицу.

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

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

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

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

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

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

Функция IF при необходимости позволяет использовать текстовые строки в аргументах. 

Задача 2

Допустим, после скидки товар стал стоить менее 300 рублей, тогда его необходимо списать. То же самое необходимо сделать, если оно пролежало без продажи 10 месяцев. В данной ситуации допустим любой из этих вариантов, поэтому логично использовать функцию OR и IF. Результатом является следующая строка.

=ЕСЛИ(ИЛИ(D2<300;C2>=10);»списан»;»»)

Если при написании условия использовался логический оператор OR, то его необходимо декодировать следующим образом. Если ячейка С2 содержит число 10 и более, или если ячейка D2 содержит значение меньше 300, то значение «списанное» необходимо вернуть в соответствующую ячейку.

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

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

Задача 3

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

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

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

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

=ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»)

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

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

Задача 4

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

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

Для решения этой задачи необходимо использовать следующую формулу.

= ЕСЛИ (D2

В выражении, данном в первом аргументе, мы использовали функцию СРЕДНЯЯA, указывающий среднее арифметическое конкретного набора данных. В нашем случае это диапазон D2:D7.

Задача 5

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

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

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

=СРЗНАЧЕСЛИ($B$2:$B$7;B9;$C$2:$C$7)

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

Функция бессердечный позволяет сравнить значение ячейки B9 с теми значениями, которые расположены в диапазоне B2:B7, в котором указаны номера магазинов. Если данные совпадают, то формула вычисляет среднее арифметическое диапазона C2:C7.

Выводы

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

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

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