Главная » Статьи » Excel » Формулы рабочего листа

Небольшая хитрость касаемая логических формул

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

Часто в свои файлы Excel приходится вживлять множество формул, которые могут принимать 2 значения, противоположных друг другу. Например, мне часто требуется иметь набор ячеек для столбцов/строк, которые бы хранили статус их (строк/столбцов) видимости. Скажем, если A5 (люблю под это выделять первый столбец) содержит 1, то строка 5:5 должна быть видна и, если VBA программа обнаружит, что строка скрыта, то она сделает её видимой, и - наоборот.

Для таких вещей в Excel есть логический (булевый) тип, который состоит из двух констант: ИСТИНА (TRUE) и ЛОЖЬ (FALSE). Существуют также логические выражения, которые возвращают логический тип, как результат своего вычисления. Возвращаясь к примеру про видимость строки 5:5, в A5 могла бы находиться такая формула:

  = D5 <> ""

при этом, если D5 не пуста, то она вернёт значение ИСТИНА, в противном случае ЛОЖЬ.

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

  = ЕСЛИ( D5 <> "" ; 1 ; 0 )

Однако, мне в последнее время нравится такой способ:

  = ( D5 <> "" ) + 0

Данный метод имеет более лаконичную форму и, полагаю, чуть быстрее вычисляется. Обратите внимание, что скобки тут необходимы, в противном случае порядок вычислений будет нарушен. Excel в начале вычисляет логическое выражение в скобках, а затем вынуждено его преобразовать в число, так как завершающая операция - операция сложения. Как известно значение ИСТИНА преобразуется в 1, а ЛОЖЬ - в 0, поэтому прибавление нуля тут ничего изменить не может (впрочем как и умножение на 1, возведение в степень 1, вычитание 0, деление на 1).

Кстати, то обстоятельство, что у меня в A5 теперь числа (0 или 1), а не логические константы (ЛОЖЬ или ИСТИНА), никак не повлияет на возможность использования ячейки A5 в логических операторах. Точно так же будут отлично работать такие формулы:

   = ЕСЛИ( A5 ; "Видима" ; "Скрыта" )

или

   = И( A5 ; A1 > 0 )

Это происходит, так как 1 преобразуется в ИСТИНА, а 0 - в ЛОЖЬ. На самом деле в ИСТИНА будет преобразовано ЛЮБОЕ ЧИСЛОВОЕ значение, отличное от нуля. Любое!

Кстати последнюю формулу так же очень удобно переводить в числовую форму:

   = И( A5 ; A1 > 0 ) + 0

и дополнительных скобок уже не потребуется, достаточно тех, что уже есть у оператора И (AND).

Вот такая небольшая хитрость :)


Читайте также:

Категория: Формулы рабочего листа | Добавил: dsb75 (27.06.2015) | Автор: Батьянов Денис E W
Просмотров: 4849 | Комментарии: 7 | Теги: логические формулы, Boolean | Рейтинг: 5.0/2
Всего комментариев: 7
1
2 MichaelCH   (28.06.2015 15:23)
В продолжении темы,
С логическими значениями в Excel можно производить любые арифметические операции (сложение, умножение, деление, возведение в степень), при этом ИСТИНА будет преобразована в 1, а ЛОЖЬ - в 0 (в отличие от VBA, где False=0, а True=-1).
таким образом, формулу
Код
=И(A5;A1>0)+0

можно записать короче
Код
=A5*(A1>0)

Подразумевая, что в A5 могут быть значения 1/0 или ИСТИНА/ЛОЖЬ

1
3 dsb75   (28.06.2015 18:20)
Михаил, спасибо за дополнения. Вы правы, что в VBA True=-1, однако в логических выражениях к True всё равно будет преобразовано всё, что отлично от нуля.
Код
If -2 then MsgBox "Всё равно true"

0
5 MichaelCH   (28.06.2015 23:29)
Любые числовые значения отличные от нуля воспринимаются и в Excel и в VBA как ИСТИНА (True).
Я про обратное преобразование писал, когда логическое значение преобразуется в число: в Excel: ИСТИНА+0 = 1, а в VBA: True+0 = -1

при этом не всегда логические операции с числовыми значениями в Excel и VBA идентичны.
Например, выражение И(2;4) = ИСТИНА, в то время как в VBA: 2 And 4 = 0

0
6 dsb75   (28.06.2015 23:45)
Я знаю, что для вас это не явилось новостью, но надо же и мне вставить свои 5 копеек smile

Да, что касается "И(2;4)" и "2 And 4", то и там и там логическое умножение, только в первом случае над всем числом, а во втором над его битами.

0
1 MichaelCH   (28.06.2015 15:06)
Формулу:
Код
=(D5<>"")+0

можно также записать как
Код
=--(D5<>"")

Код
=(D5<>"")*1

Код
=Ч(D5<>"")


При этом логические значения ИСТИНА/ЛОЖЬ будут преобразованы в 1/0
Говорят (сам не замерял), что двойное (бинарное) отрицание работает быстрее чем умножение на единицу или сложение с нулем

0
4 dsb75   (28.06.2015 18:20)
Да, точно, два минуса перед скобками тоже мне очень нравится.

0
7 dsb75   (28.06.2015 23:49)
Насчёт того, что двойное отрицание работает быстрее, я думаю, что для Excel это не актуально, так как там всегда есть куда более существенные факторы, убивающие производительность...

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика