Булевы функции в Excel. Все о применении логических функций в Excel

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

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

Список логических функций в Excel

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

1. ИСТИНА
2. ЛЕЖА
3. IF
4. IFERROR
5. OR
6. И
7. НЕ
8. ЭОШИБКА
9. ISBLANK

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

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

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

НЕ оператор

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

Синтаксис этого оператора следующий: =НЕ(истина или ложь).

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

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

Принцип построения оператора AND очень прост: =И(аргумент1; аргумент2; …). Максимальное количество аргументов, которые может использовать эта функция, — 255. Синтаксис оператора OR похожи, но механика работы немного другая. Если одна из функций из списка выдает результат ИСТИНА, то это число будет возвращено как целая логическая последовательность.

Операторы IF и ISERROR

У этих двух функций очень важное назначение — они непосредственно задают критерий на соответствие которому необходимо проверять то или иное выражение. Для более глубокого понимания того, как работает оператор IFERROR, необходимо сначала описать функцию IF. Его общая структура немного сложнее предыдущих: =ЕСЛИ(логическое_выражение, значение_если_истина, значение_если_ложь).

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

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

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

1. Значение. Проверяется само выражение. Если оно окажется правдой, то возвращается это значение.
2. Значение в случае ошибки. Это текст, число или функция, которая будет отображаться или выполняться, если результат проверки первого аргумента был ЛОЖЬ.

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

Операторы ISERROW и ISEMPLAND

Первая функция из приведенного выше содержит только одно значение и имеет следующий синтаксис: =ЕОШИБКА(значение). Задача этого оператора — проверить, насколько хорошо заполнены ячейки (одна или во всем диапазоне). Если окажется, что заполнение было неправильным, он вернет истинный результат. Если все хорошо – ложь. Может применяться непосредственно как критерий для другой функции.

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

• #ИМЯ?;
• #Н/Д;
• #УДАЛ/0!;
• #ЧИСЛО!;
• #ТАК;
• #ПУСТОЙ!;
• #СВЯЗЬ!.

Функция ISBLANK В целом, это невероятно просто. Он содержит только один параметр — проверяемую ячейку/диапазон. Если есть ячейка, в которой нет ни текста, ни цифр, ни непечатаемых символов, то возвращается результат ИСТИНА. Соответственно, если данные есть во всех ячейках диапазона, то пользователь получает результат ЛЕЖА.

Таблица-памятка «Логические функции в Excel»

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

Логические функции и примеры решения задач

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

Задача 1. Предположим, у нас осталась часть товара после определенного времени продажи. Его необходимо переоценить по следующим правилам: если продать его за 8 месяцев не удалось, разделите его цену на 2 раза. Для начала создадим диапазон, описывающий исходные данные. Это выглядит так.

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

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

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

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

Задача 2. Давайте применим этот критерий к товару, который имеется на складе. Допустим, если после сделанной выше уценки его стоимость стала меньше 300 рублей или он простоял без продажи более 10 месяцев, то его просто снимают с продажи. Формула следующая.

Давайте проанализируем это. В качестве критерия мы использовали функцию OR. Необходимо предусмотреть такую ​​вилку. Если ячейка D2 содержит число 10, то значение «списано» автоматически отобразится в соответствующей строке столбца E. То же самое касается и другого условия. Если ни один из них не выполняется, то просто возвращается пустая ячейка.

Задача 3. Допустим, у нас есть выборка студентов, пытающихся поступить в старшую школу. Для этого им необходимо сдать экзамены по нескольким предметам, показанным на скриншоте ниже. Чтобы считаться имеющими право на поступление в это учебное заведение, они должны набрать в общей сложности 12 баллов. При этом важным условием является то, что оценка по математике должна быть не менее 4 баллов. Задача — автоматизировать обработку этих данных, а также составить отчет о том, какие студенты зашли, а какие нет. Для этого сделаем такую ​​таблицу.

Итак, наша задача — заставить программу посчитать, сколько всего будет баллов, посмотреть проходной результат и выполнить сравнение. После этих операций функция должна поместить результат в ячейку, в которую он помещается. Возможны два варианта: «принято» или «нет». Для реализации этой задачи введите аналогичную формулу (просто подставьте свои значения): =ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»).

С логической функцией И мы можем убедиться, что выполняются сразу два условия. В данном случае мы использовали функцию SUM для подсчета общего балла. В качестве первого условия (в первом аргументе функции И) мы указали формулу B3>=4. В этой графе указана оценка по математике, которая не должна быть ниже 4 баллов.

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

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

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

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

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

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

1. Диапазон для проверки.
2. Условие, которое необходимо проверить.
3. Усреднение диапазона.

В результате получается следующая формула (на скриншоте).

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