Редактор электронных таблиц Excel — это очень гибкий и мощный инструмент для работы с данными в структурированном виде. Среди множества мощных инструментов табличного редактора Excel особенно выделяются булевы операторы, которые основаны на распознавании выражения как истинного или ложного.
Они эффективно встроены в инструменты формул и позволяют создавать гибкие условия для различных вычислений или популяций клеток на основе булевых выражений. В этой статье мы опишем основные используемые функции и приведем практический пример использования булевых операторов в Excel.
Основные логические функции, используемые в Эксель
См. также «Как подсчитать количество значений в столбце Excel».
Список наиболее часто используемых логических операторов можно свести к следующему набору:
- TRUE;
- ЛОЖЬ;
- IF;
- ИЛИ; ИФ;
- ИЛИ;
- И;
- НЕ;
- ЭОШИБЛ;
- ЭПО.
В большинстве случаев их достаточно для построения сложных логических конструкций и задания условий.
Каждый из вышеперечисленных операторов имеет аргументы (кроме TRUE и FALSE) — это могут быть числовые значения, текстовые значения или ссылки на данные, содержащиеся в других ячейках.
Операторы ИСТИНА и ЛОЖЬ
Функция TRUE не имеет собственных аргументов и почти всегда используется в качестве строительного блока для других логических выражений, поскольку принимает одно конкретно определенное значение.
Функция FALSE, напротив, может принимать любое значение, которое не является истиной. Как и TRUE, он почти всегда используется в качестве строительного блока для других сложных выражений.
Операторы И и ИЛИ
Синтаксис оператора AND следующий:
=I(log_value1; log_value2; …), возможное количество используемых аргументов — от 1 до 255.
Оператор AND используется в качестве элемента строки для нескольких условий логического выражения. Важно, что все аргументы оператора должны быть TRUE (если их больше одного), иначе вся логическая строка возвращает FALSE.
Синтаксис оператора OR:
=I(log_value1; log_value2; …), возможное количество используемых аргументов — от 1 до 255.
В отличие от AND, функция OR вернет TRUE, даже если хотя бы один из используемых аргументов непротиворечив, а все остальные аргументы ложны.
Оператор НЕ
Синтаксис оператора NOT следующий: =NE(log_value).
Количество аргументов оператора NOT всегда равно единице. Соответственно, результат функции (истина/ложь) зависит только от значения аргумента.
Операторы ЕСЛИ и ЕСЛИОШИБКА
Общий синтаксис функции IF представляет собой конструкцию:
=If(логическое_выражение;значение_если_истина;значение_если_ложь).
Оператор IF используется для построения сложных, расширенных логических строк. Суть функции заключается в проверке условия, введенного в виде логического выражения. Если условие истинно, функция возвращает одно выражение в качестве результата функции, но если логическое выражение ложно, функция возвращает другое условие в качестве результата функции.
Синтаксис оператора IFRICT следующий:
=ESLIO ERROR(value;value_if_error).
Функция проверяет, истинно ли логическое выражение для первого аргумента, и если да, то возвращает его значение в качестве результата. Если выражение ложно, то в качестве результата возвращается значение второго аргумента, указанного в функции.
Операторы ЕОШИБКА и ЕПУСТО
См. также «Как подсчитать количество символов в ячейке Excel».
Оператор ERROR имеет следующую структуру:
=EO ERROR(значение)
Используется для проверки того, правильно ли уже заполнена ячейка (или диапазон), и возвращает TRUE, если ячейка недействительна, в противном случае возвращает FALSE.
Примеры значений в неправильно заполненных ячейках:
- #NAME?
- #N/D;
- #DEL/0!
- #NUMBER!
- #NAME!
- #NUMBER!
- #NUMER!
Аргументом функции является адрес конкретной ячейки или ссылка на диапазон ячеек.
Формула для функции EPUST выглядит следующим образом:
=EPUST(value).
Функция оператора проверяет ячейку или диапазон ячеек и возвращает TRUE, если в ячейке или диапазоне ячеек нет данных, и FALSE, если данные в ячейке или диапазоне ячеек есть. Аргументом функции является адрес конкретной ячейки или ссылка на диапазон ячеек.
Практический пример использования логических функций
Следующий пример представляет собой попытку частично использовать описанные выше функции для решения близкой к реальной задачи, включающей расчет премии, зависящей от определенных условий.
Исходные данные представляют собой таблицу с информацией о сотрудниках, их поле и возрасте.
Нам нужно рассчитать премию. Ключевыми условиями, от которых зависит бонус, являются:
- размер обычной премии, которую получат все сотрудники без исключения, составляет 3 000 рублей;
- сотрудницы имеют право на более высокую премию в размере 7 000 рублей;
- молодые сотрудники (до 1984 года рождения) имеют право на повышенную премию в размере 7 000 рублей;
Выполним необходимые вычисления с помощью логических функций.
- Перейдите к первой ячейке столбца, в котором вы хотите рассчитать страховые взносы, и нажмите кнопку «Вставить функцию». (слева от условий формулы).
- Когда откроется мастер функций, выберите «Boolean», затем нажмите «IF» в списке операторов и нажмите OK.
- Теперь нам нужно задать аргументы для функции. Поскольку у нас есть не одно, а два условия для получения более высокой премии, и нам нужно удовлетворить хотя бы одному из них, чтобы задать булево выражение, мы воспользуемся функцией ИЛИ. Как только вы окажетесь в поле для ввода значения аргумента «Log_expression», нажмите на маленькую стрелку вниз в основной рабочей области книги, которая находится в левом верхнем углу окна программы, где обычно отображается адрес ячейки. В открывшемся списке функций выберите оператор OR, если он присутствует в списке (или вы можете нажать на «Другие функции» и выбрать его в новом окне мастера функций, как мы делали это изначально для выбора оператора IF).
- Мы переходим к окну аргументов функции OR. Здесь мы указываем наши условия для получения бонуса в размере 7000 рублей:
- год рождения позже 1984;
- пол — женский;
- Теперь обратимся к строке формул. Щелкните на имени исходного оператора IF, чтобы перейти к аргументам функции.
- Заполните аргументы функции и нажмите OK:
- В значение «True» введите число 7000;
- В значении «Ложь» мы вводим число 3000;
- Результат работы логических операторов будет отображен в первой ячейке выбранного нами столбца. Как мы видим, окончательный вид формулы выглядит следующим образом:
=ЕСЛИ(ИЛИ(C2>1984;D2=»жен.»);7000;3000).
Кстати, вместо использования Мастера функций вы могли бы вручную составить и сохранить эту формулу в нужной ячейке.
- Для расчета премии для всех сотрудников воспользуемся маркером Fill. Переместите курсор в правый нижний угол ячейки с формулой. Когда курсор примет форму черного креста (это маркер заполнения), удерживая левую кнопку мыши, перетащите выделение вниз до последней ячейки в столбце.
- Вот и все. Благодаря логическим операторам мы заполнили данные для столбца с вкладами.
Заключение
Логические операторы используются почти во всех сложных формулах, где значение в ячейке зависит от одного или нескольких условий, и позволяют строить гибкие структуры, комбинируя простые функции и вычисления в одной ячейке. Это значительно сокращает время обработки и повышает производительность за счет устранения большого количества промежуточных этапов в расчетах и вычислениях.