Во многих функциях Excel используется логические выражения ИСТИНА и ЛОЖЬ, при этом есть нюансы, которые стоит знать, чтобы максимально эффективно использовать условия при создании формул.
Так ИСТИНА или ЛОЖЬ в Excel имеет несколько форм и для лучшего понимания давайте рассмотрим несколько примеров.
В первом столбце находятся некоторые значения, а во втором - простая формула с функцией ЕСЛИ.
Здесь в качестве условия используется ссылка на соответствующую ячейку со значением из первого столбца, и, в зависимости от значения, функция возвращает соответствующие выражения ИСТИНА или ЛОЖЬ. То есть такая простая формула позволит узнать, как именно реагирует Эксель на то или иное значение.
Итак, можем сделать следующие выводы. Как ИСИНА Excel воспринимает любое положительное или отрицательное число, а также само выражение ИСТИНА. То есть истиной будет любое не нулевое значение, а вот как раз ноль или пустую ячейку Эксель воспринимает как ЛОЖЬ. В случае с текстом и вовсе появляется ошибка. Это означает, что текстовые значения не получится совмещать в формулах с другими логическими выражениями, так как в результате получим ошибку.
Но что нам дает полученный результат?
Дело в том, что в формулах мы можем миксовать логические условия с помощью простых математических действий - сложения и умножения. Поэтому, как мне кажется, стоит понимать механику этих процессов. Для этого снова обратимся к примеру.
Что будет, если умножить логические выражения ИСТИНА? В Excel в аргументах функций логические значения ИСТИНА и ЛОЖЬ можно заменить на 1 и 0 соответственно, а значит можно ожидать, что при перемножена значений ИСТИНА мы получим 1, при умножении ИСТИНЫ на ЛОЖЬ или ЛОЖЬ на ЛОЖЬ - 0.
Аналогично будет и с числовыми значениями - при умножении на ИСТИНУ получаем само значение, а на ЛОЖЬ - ноль. А вот с текстовыми значениями такая операция не пройдет и мы получим ошибку. Это как раз то, о чем я говорил ранее - не получится в аргументах функции совмещать текстовые значения и другие логические условия.
По аналогии будет работать и сложение.
Но как все это можно использовать на практике? Давайте разберем на примере функции ФИЛЬТР, которая относится к функциям динамических массивов.
Итак, есть список сотрудников, которые проходили аттестацию.
Необходимо убрать из списка сотрудников, которые не присутствовали или получили ноль баллов. С задачей как раз легко справится функция ФИЛЬТР.
Выбираем весь диапазон таблицы (A2:A20), а затем подставляем условие, что значение из второго столбца (B2:B20) должны быть не равны нулю. Так как пустая ячейка интерпретируется Эксель как нулевая, то такое условие в данном случае будет универсальным. Также в качестве проверки можно использовать знак больше.
Получим необходимый результат. Однако мы можем не прописывать условие, а лишь сослаться на столбец с баллами и получим тот же результат.
Почему это работает? Если с помощью клавиши F9 посмотреть рассчитанные значения второго аргумента, который задает условие, то мы увидим нулевые значения.
Функция ФИЛЬТР возвращает значения, которые удовлетворяют заданному условию, то есть им сопоставляется выражение ИСТИНА. Как я показал ранее, Эксель воспринимает ноль, как ЛОЖЬ, а ненулевые значения, как ИСТИНА. Это означает, что нулевые значения функцией ФИЛЬТР игнорируются по умолчанию, а значит они будут автоматически исключены из итогового диапазона.
Но эта логика сломается, если мы внесем в исходную таблицу текст. Например, вместо пустых ячеек, свидетельствующей об отсутствии на аттестации сотрудника, напишем это текстом.
В итоге получим ошибку и о причине ее появления я рассказал ранее - Эксель не может в качестве условия миксовать текст и логические выражения. Тем не менее, первый вариант формулы отработал верно, ведь текст не равен нулю, а значит проверка вернет значение ИСТИНА.
Допустим, исходная таблица находится именно в таком виде, то есть содержит в том числе и текстовые строки. Чтобы их отфильтровать воспользуемся функцией ЕЧИСЛО. Она проверяет является ли указанное значение числом и если да, то возвращает ИСТИНА, иначе - ЛОЖЬ. Соответственно можем подставить эту функцию в ранее созданную формулу.
В таком виде текстовые значения будут игнорироваться, но останутся нули. Необходимо добавить условие, чтобы в итоговом диапазоне не отображались и нулевые значения, и введенный текст. То есть нам необходимо задействовать два условия, связав их логическим оператором И.
Внутри формулы при работе с условиями такой оператор можно заменить на знак умножения, то есть умножим первое условие на второе.
Получим нужный результат и чтобы понять, как это работает, давайте разделим условия и выведем их в отдельных столбцах.
Ну а теперь логика должна быть понятна, ведь ее мы рассматривали ранее. При перемножении логических выражений мы получим массив нулей и единиц, по которым затем функция фильтрует указанный диапазон. Таким образом знак умножения заменяет собой логический оператор И, связывающий условия.
Но как быть, если нужно получить значения, соответствующие хотя бы одному из указанных условий? То есть как мы можем задействовать логический оператор ИЛИ?
Например, давайте получим список сотрудников, которые либо отсутствовали на аттестации, либо набрали менее 3 баллов. Логический оператор ИЛИ в данном случае можно заменить знаком сложения.
Чтобы понять, почему это работает, снова разобьем условия на отдельные столбцы. Опять же, сложение логических операторов мы рассмотрели ранее, поэтому итоговый столбец, который будет определять параметры фильтрации, выглядит так:
В данном случае у нас нет комбинации ИСТИНА-ИСТИНА, но если бы такая комбинация была, то в итоге мы бы получили 2, а, как мы рассмотрели ранее, любое не нулевое числовое значение в условии воспринимается Экселем, как ИСТИНА, поэтому и в этом случае мы бы получили верный результат.
Чтобы скачать курс по основам Microsoft Excel нажмите на эту ссылку
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм