Приветствую всех на уроке по логическим функциям в Excel. Сегодня мы рассмотрим эту важную тему, которая позволит значительно сэкономить время при работе с большими объемами данных. Логические функции могут автоматизировать процессы вычислений, но нужно быть готовым к тому, что это сложная тема. Приготовьте лист бумаги, карандаш или ручку, чтобы повторять задачи вместе со мной.
Если вам что-то непонятно в этой статье, не стесняйтесь задавать мне вопросы в комментариях. Однако я настоятельно рекомендую перечитать непонятный отрывок из текста еще раз. Лично мне приходилось несколько раз перечитывать некоторые моменты, когда я учился этому в школе. Но как только вы освоите основы логики, и сможете приблизительно представлять себе некоторые части задач – все станет намного проще. Не забудьте добавить эту статью в закладки, чтобы иметь возможность обращаться к ней как к справочнику.
Список операторов
В отличие от обычного прогрммирования, в Excel существует несколько дополнительных операторов. Давайте рассмотрим полный список:
Обратите внимание на первые два оператора. Когда я еще занимался программированием на C#, в молодости, я не помню, чтобы там были точные операторы типа «ИСТИНА» и «ЛОЖЬ», кроме 1 и 0, или NULL и какого-то значения. В Excel оба эти операторы не имеют аргументов и их можно использовать так же, как и в программировании, чтобы в логическом выражении вставлять точные значения «ИСТИНА» и «ЛОЖЬ». Но как и в классическом программировании, вместо них можно использовать 1 и 0. Давайте теперь разберем каждый из операторов подробнее.
ИСТИНА и ЛОЖЬ
Давайте начнем с начала, а именно, с того, как записывается логическая команда в Excel? – она записывается как обычная функция, которая начинается со знака равно (=). Вы можете вставить её двумя способами:
- Вписать функцию вручную.
- Или использовать кнопку вставки функций (fx), которая находится слева от строки значения ячейки.
У функций «ИСТИНА» и «ЛОЖЬ» нет аргументов, значит, скобки, которые обязательно следуют после любой функции, будут пустыми. Зачем же нужны эти пустые логические функции? – на самом деле, их используют достаточно часто, но рассказывать о них особо смысла нет. Вы все поймете, когда мы разберем конкретный пример в самом конце.
И и ИЛИ
Оба оператора предполагают наличие двух аргументов, что означает, что они принимают на входе два значения информации. Давайте кратко вспомним, как работают эти операторы, начиная с оператора «И».
Оператор «И» возвращает значение «ИСТИНА» только в том случае, если оба аргумента имеют истинные значения. Вас все еще немного смущает? Давайте рассмотрим все возможные варианты ответов при применении этой функции к двум значениям. Для удобства чтения давайте обозначим «ИСТИНА» как единицу (1), а «ЛОЖЬ» как ноль (0):
1 И 1 = 1
1 И 0 = 0
0 И 1 = 0
0 И 0 = 0
Логический оператор ИЛИ возвращает значение ИСТИНА, если хотя бы один из аргументов истинный.
1 ИЛИ 0 = 1
1 ИЛИ 0 = 1
0 ИЛИ 1 = 1
0 ИЛИ 0 = 0
Как можно заметить из представленной выше информации, в Excel возможно использовать любое количество логических значений. Самым простым способом является вставка через окно функции. Однако можно также вручную вводить значения через знак точки с запятой:
ИЛИ(Логическое_значение1; Логическое_значение2; Логическое_значение3;…)
НЕ
Логическое отрицание (НЕ) представляет собой инверсию логических значений. Оно возвращает противоположный ответ: если исходное значение было ложным, то оно становится истинным, и наоборот.
НЕ 1 = 0
НЕ 0 = 1
ЕСЛИ
Если условие выполняется, то функция ЕСЛИ возвращает определенное значение, в противном случае она возвращает другое значение. Просто посмотрите на пример с возрастом. Давайте посмотрим, какая функция покажет в столбце «Зрелость» значение «Совершеннолетний» или «Несовершеннолетний». Мы будем использовать для этого столбец «Возраст». Вставляем функцию ЕСЛИ() и открываем окно ввода аргументов.
В данное поле необходимо ввести логическое выражение, используя адрес столбца «Возраст». Можно кликнуть на нужной ячейке или ввести адрес вручную. Для сравнения можно использовать следующие знаки:
Как они работают, вы скорее всего помните еще со школьных уроков. Логическое выражение для определения совершеннолетия, как правило, довольно простое:
Далее необходимо заполнить еще два аргумента:
То, что будет введено в эти поля, будет отображаться в таблице в зависимости от условий. Итак, логическая формула:
Показывает значение совершеннолетия для граждан, указанных в таблице. С помощью автозаполнения можно скопировать формулу с измененным адресом ячейки.
ЕСЛИОШИБКА
В случае возникновения ошибки в программе Excel, данная функция возвращает значение из указанного пользователем аргумента. Например, это может произойти при попытке деления на ноль. Если же ошибки нет, то функция возвращает обычное значение.
Зачем нужна эта функция? – Чаще всего она используется для быстрого обнаружения ошибок. В качестве примера, попробуем автоматически разделить числа.
При включении функции автозаполнения и выполнении деления на ноль в Excel возникает ошибка. Хотя данный пример довольно грубый, поскольку сам Excel выдает ошибку при неправильных расчетах.
ЕОШИБКА
Если в ячейке (или в списке ячеек) есть ошибка, функция вернет значение «ИСТИНА». Список возможных ошибок доступен выше. Рассмотрим предыдущий пример, но теперь в столбце «Частное» отображается обычное деление без функции «ЕСЛИОШИБКА()». При применении функции ЕОШИБКА() она вернет «ИСТИНА», так как в последней строке есть ошибка деления на ноль.
ЕПУСТО
Функция возвращает true, если выделенная ячейка пуста. Важно отметить, что ячейка должна быть действительно пустой, без ошибок и значений ноль (0). Однако функция не работает со списком ячеек. Например, если вы используете формулу:
=ЕПУСТО(C2-C12)
То получите следующий результат:
Функция работает как функция И — все выбранные ячейки должны быть пустыми. Если хотя бы одна содержит значение, ошибку или ноль (0), то вернется false.
0
Пример
Допустим, у нас есть таблица сотрудников компании. Наша задача — рассчитать премию, которая полагается каждому из работников. У нас есть два вида премии:
- 500 — стандартная премия, которая выдается всем сотрудникам.
- 1500 — увеличенная премия, которая полагается женщинам и пенсионерам, родившимся до 1980 года.
Если сотрудник новый и работает в компании менее 30 дней, то ему премия не полагается.
Давайте теперь разберем поэтапно формулу, которую нам нужно использовать. Ориентируйтесь на изображение ниже.
Я настоятельно рекомендую вам добавить это вручную, а не копировать у меня. Так как нам необходимо вывести значение, мы будем использовать:
ЕСЛИ(…;1500;500)
Теперь давайте заполним логическое выражение. Нам нужно выдать премию женщинам или сотрудникам с датой рождения до 1980 года – для этого мы используем ИЛИ:
Обратите внимание на сравнение ячейки пола. Здесь мы используем строковое значение «ж», которое обязательно должно быть в кавычках. Если же вы используете сравнение с цифрами или числами, то кавычки не нужны. Читается строка довольно просто:
Если у сотрудника дата рождения меньше 1980 ИЛИ он является женщиной, то…
Возвращается “ИСТИНА”, либо выполняется какое-то условие по ЕСЛИ. Однако необходимо учитывать, что сотрудник должен отработать в компании не менее 30 дней. Для этого добавляется еще одно условие:
Это условие будет возвращать ЛОЖЬ, если сотрудник проработал более 30 дней. Нам необходимо преобразовать эту ЛОЖЬ в ИСТИНУ с помощью оператора НЕ. То есть если сотрудник уже отработал больше месяца, тогда возвращается ИСТИНА. Обратите внимание, что мы умножаем это дополнительное условие с помощью обычного значка (*) – такая операция допустима. При этом этот знак умножения (*) работает также как логическое И.
Теперь у вас есть понимание того, как работают логические функции в программе Excel и какой результат они возвращают при корректном логическом выражении. Однако главной сложностью является необходимость одновременно держать в уме несколько переменных с логическими выводами.
