Глава 11. Булева логика: критерии И, ИЛИ

Рубрика: 7. Полезняшки Excel

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                              Оглавление                                Следующая глава

В предыдущих главах вы уже использовали И критерий при написании формул. Например:

Рис. 10.24 – суммирование с использованием двух критериев
=СУММЕСЛИМН(C8:C19;A8:A19;">="&A2;A8:A19;"<="&B2;B8:B19;C2)

Рис. 10.25 – также суммирование с использованием двух критериев =БДСУММ(A7:C19;C7;A1:C2)

Рис. 4.39 – нахождение минимума на основе двух критериев
=АГРЕГАТ(15;6;$C$3:$C$13/(($A$3:$A$13<>$F$2)*($B$3:$B$13=E5));1)

Рис. 10.26 – суммирование с использованием трех критериев; формула массива, основанная на булевой логике с И критерием: =СУММПРОИЗВ(--(A8:A19>=A2);--(A8:A19<=B2);--(B8:B19=C2);C8:C19)

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

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

ИСТИНА = 1, ЛОЖЬ = 0

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

Я употребляю термины И критерий, ИЛИ критерий, потому что в Excel они часто используются для создания отчетов, выборки данных или расчетов, основанных на наборе критериев. Кроме того, я обычно использую синоним булева математика вместо булевой логики, потому что использую умножение для И критерия, и суммирование – для ИЛИ критерия.

Примечание: вот некоторые синонимичные термины, которые вы можете встретить в литературе:

  • И = сочетание = одновременно = пересечение = все = умножение
  • ИЛИ = дизъюнкция = альтернатива = объединение = по крайней мере один = один или более = добавление

Ниже приведены все возможные комбинации из двух и трех логических значений в варианте И критерия и ИЛИ критерия:

Рис. 11.0. Комбинации логических значений

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

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

Ключевая концепция, которая используется при построении больших формул, заключается в том, что критерии И, ИЛИ являются фильтрами для отбора «нужных» данных. Давайте рассмотрим некоторые примеры.

И критерий (рис. 11.1–11.7). Во всех примерах используется 4 критерия. Это означает, что все четыре проверки должны вернуть значение ИСТИНА для того чтобы элемент данных был включен в расчет.

Рис. 11.1. Примеры использования И критерия

Рис. 11.1. Формулы [1]–[4] используют И критерии; [5] – логическое умножение; [6] – логическое умножение и деление; [7] – функцию ЕСЛИ и И критерий.

Примечания: подробнее о формулах [1], [2], [3] и [5] см. главу 10, [4], [6] и [7] – главу 4.

Рис. 11.2. Сводная таблица с помощью фильтров суммирует на основе И критерия

Рис. 11.2. Сводная таблица с помощью фильтров суммирует на основе И критерия

Рис. 11.3. Опция Таблица с соответствующими фильтрами

Рис. 11.3. Опция Таблица с соответствующими фильтрами и строкой Итоги позволяет вычислить среднее (используются И критерий); подробнее о Таблицах см. главу 7

Рис. 11.4. Опция Фильтр

Рис. 11.4. Опция Фильтр позволяет фильтровать данные, используя И критерий; выделите ячейки А1:D1, перейдите на закладку Данные в область Сортировка и фильтр и нажмите кнопку Фильтр; с помощью кнопок в правой части каждого столбца выберите требуемые значения

Рис. 11.5. Расширенный фильтр

Рис. 11.5. Расширенный фильтр может сделать выборку на основе И критерия; создайте область критериев, перейдите на закладку Данные в область Сортировка и фильтр и нажмите кнопку Дополнительно; в открывшемся окне Расширенный фильтр заполните поля и нажмите Ok

Рис. 11.6. Функция И

Рис. 11.6. Функция И с четырьмя логическими проверками в дополнительном столбце. Можно обойтись и без дополнительного столбца, если использовать вместо СЧЁТЕСЛИ функцию СЧЁТЕСЛИМН; подробнее см. главу 15

Рис. 11.7. Перемножение операторов сравнения

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

Чтобы лучше понять, как работает И критерий, давайте подробнее изучим пару формул, с рис. 11.1.

1. =СУММПРОИЗВ(--(A2:A6=A10);--(B2:B6=B10);--(C2:C6=C10);--(D2:D6>D10))

Рис. 11.7.2. Пошаговая работа формулы СУММПРОИЗВ

2. =АГРЕГАТ(15;6;D2:D6/((A2:A6=A10)*(B2:B6=B10)*(C2:C6=C10)*(D2:D6>D10));1)

Рис. 11.7.4. Пошаговая работа формулы АГРЕГАТ

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

Далее показаны два примера, которые иллюстрируют работу логического критерия ИЛИ. На рис. 11.8 показан список менеджеров (столбец А) и значение формулы с критерием ИЛИ (столбец B). Логическое ИЛИ выясняет: «Имя менеджера Gidget или Rodger?». Обратите внимание, что, поскольку использовано два отдельных вопроса к одной ячейке, вы не можете получить в столбце В два значения ИСТИНА. Все ответы сводятся к нулям и единицам.

Рис. 11.8. Ответ на вопрос, Gidget или Rodger

Рис. 11.8. Ответ на вопрос «Gidget или Rodger?» не может вернуть два значения ИСТИНА; критерий ИЛИ, применяемый к одной ячейке решает проблему

Второй пример (рис. 11.9) демонстрирует несколько иную конструкцию логической проверки ИЛИ. Вместо того, что задать два вопроса к одной ячейке (как на рис. 11.8), здесь используется два вопроса к двум ячейкам. Логическое ИЛИ проверяет, что по каждому из клиентов, либо величина чистых активов превышает $ 100 000, либо кредитный рейтинг не меньше 3,5. В этом случае в столбце В можно встретить значение 2, когда обе проверки возвращают значение ИСТИНА.

Рис. 11.9. Кредит может быть выдан

Рис. 11.9. Кредит может быть выдан, если чистые активы > 100 000 или рейтинг >= 3,5

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

Логический тест ИЛИ, который не может вернуть более одного значения ИСТИНА:

  • вопросы ИЛИ, которые указывают на одну ячейку;
  • обычно это означает, что вопросы ИЛИ относятся к одному столбцу.

Логический тест ИЛИ, который может вернуть более одного значения ИСТИНА:

  • вопросы ИЛИ, которые указывают на различные ячейки;
  • обычно это означает, что вопросы ИЛИ относятся к нескольким столбцам.

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

Рис. 11.10. Никакая ячейка в столбце А не может одновременно содержать имена Gidget и Rodger

Рис. 11.10. Примеры формул на основе критерия ИЛИ, возвращающего не более одного значения ИСТИНА

Формула [1]. Поскольку обе функции СЧЁТЕСЛИ извлекают данные из одного и того же столбца (А), а в одной ячейке не может одновременно быть и Gidget и Rodger, критерий ИЛИ для каждой ячейки из диапазоне А2:А6 вернет, либо 0, либо 1.

Формула [2]. Вы используете БСЧЁТА (а не БСЧЁТ), потому что вы подсчитываете вхождение текста, а не чисел. Критерии ИЛИ для функции базы данных должны быть введены в соседних строках в одном или разных столбцах (в отличие от критериев И, которые должны быть в одной строке в соседних столбцах). Если вам нужно подсчитать количество ячеек с текстовыми строками нулевой длины, БСЧЁТА справится и с этим.

Формула [3]. (A2:A6=C2)+(A2:A6=C3) → {ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}+{ЛОЖЬ:ИСТИНА: ЛОЖЬ:ИСТИНА:ЛОЖЬ} → {1:1:0:1:0}. Нет необходимости в двойном отрицании, потому что вы уже использовали математический оператор сложения, преобразующий логические значения в числа.

Формула [4]. {=СУММ(ЕСЛИ((A2:A6=C2)+(A2:A6=C3);1))} → {=СУММ(ЕСЛИ({1:1:0:1:0};1))} → {=СУММ({1:1:ЛОЖЬ:1:ЛОЖЬ})}. Аргумент лог_выражение функции ЕСЛИ содержит массив чисел. Это работает, потому что лог_выражение интерпретирует любое ненулевое значение, как ИСТИННА, и ноль как ЛОЖЬ.

Формула [5]. =СУММПРОИЗВ(--ЕЧИСЛО(ПОИСКПОЗ(A2:A6;C2:C3;0))) → =СУММПРОИЗВ(--ЕЧИСЛО ({1:2:#Н/Д:2:#Н/Д})) → =СУММПРОИЗВ(--{ИСТИНА:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ}) → =СУММПРОИЗВ({1:1:0:1:0}). Поместите критерии ИЛИ в аргумент просматриваемый_массив функции ПОИСКПОЗ; поместите исследуемые элементы (А2:А6) в аргумент искомое_значение. ПОИСКПОЗ вернет положение элемента (из диапазона А2:А6) в списке С2:С3 (порядковый номер в этом списке), а если элемента в списке нет, то ошибку #Н/Д. В главе 6 мы показали, что, если поместить массив в аргумент функции, то функция вернет массив. В нашем случае ПОИСКПОЗ возвращает позицию, или сообщение об ошибке для каждого элемента в списке. Вы поместили в аргумент искомое_значение пять элементов, и поэтому ПОИСКПОЗ вернул массив из пяти элементов. На следующем шаге функция ЕЧИСЛО вернет ИСТИНА, если значение число и ЛОЖЬ, если –  #Н/Д. Двойное отрицание превратит логические значения в числа, а СУММПРОИЗВ их просуммирует. Эта формула особенно удобна, если у вас много критериев ИЛИ.

Эти пять формулы удивительные! Но чтобы в полной мере сравнивать их, давайте посмотрим, как они справляются с большим набором данных. На рис. 11.11 приведено 500 строк и 6 критериев.

Рис. 11.11. Формулы для 500 строк и 6 критериев

Рис. 11.11. Формулы аналогичные рис. 11.10, но для 500 строк и 6 критериев

Видно, что формулы [1], [3] и [4] значительно усложнились, в то время, как формулы [2] и [5] остались короткими. С точки зрения набора, формулы [2] и [5] предпочтительнее. В тоже время, самой быстрой является формула [1]. Немного уступают ей в скорости работы формулы [2] и [5], а самые медленные [3] и [4].

Использование критерия ИЛИ, который может возвращать более одного значения ИСТИНА. Как показано на рис. 11.13, если вы не учтете, что критерий ИЛИ может вернуть два значения ИСТИНА, ваша формула может посчитать дважды одно и тоже вхождение. В примере, логическая проверка ИЛИ позволят отобрать клиентов, у которых чистые активы превышают $ 100 000 или кредитный рейтинг больше или равен 3,5. Эти два вопроса задаются двум различным столбцам, так что существует возможность получить два ответа ИСТИННА, что может привести к двойному счету. Например, компания Fruits Inc. отвечает обоим критериям. Формулы [4] и [5] дважды посчитают ее, что приведет к неверному ответу. Давайте подробнее рассмотрим работу этих формул.

Рис. 11.13. Логический тест ИЛИ обращается у двум различным ячейкам

Рис. 11.13. Каждый логический тест ИЛИ обращается у двум различным ячейкам и, следовательно, может вернуть два значения ИСТИНА; формулы масиива обращаются к двум столбцам (В и С)

Формула [1]. {=СУММ(ЕСЛИ((B2:B6>E3)+(C2:C6>=E5);1))} → {=СУММ(ЕСЛИ({1:2:0:1:0};1))} → {=СУММ({1:1:ЛОЖЬ:1:ЛОЖЬ})}. Красота этой формулы заключается в том, что вы можете разместить проверку на основе логического ИЛИ в аргументе лог_выражение функции ЕСЛИ. В свою очередь, аргумент лог_выражение интерпретирует любое ненулевое число, как ИСТИННА, а ноль, как ЛОЖЬ. Т.е., для аргумента лог_выражение {1:2:0:1:0} = ({ИСТИННА:ИСТИННА:ЛОЖЬ: ИСТИННА:ЛОЖЬ}. Далее функция ЕСЛИ заменяет ИСТИНА на 1, а ЛОЖЬ оставляет без изменения (аргумент значение_если_ложь опущен). Обратите внимание, что в формуле [1] вдвое меньше операций с массивами, чем в формуле [3], что позволяет формуле [1] быстрее выполнять рассчеты.

Формула [2]. Поскольку используются критерии ИЛИ, вы должны разместить область критериев в разных столбцах (G3 и H4).

Формула [3]. =СУММПРОИЗВ(--((B2:B6>E3)+(C2:C6>=E5)>0)) → Благодаря внутренним скобкам в первую очередь выполняются два оператора сравнения массивов =СУММПРОИЗВ(--({ЛОЖЬ: ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ}+{ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}>0)) → На втором шаге логическое ИЛИ складывает два логических массива; так как применен математический оператор, логические значения преобразуются в числа =СУММПРОИЗВ(--({1:2:0:1:0}>0)) → Следующий оператор сравнения возвращает новый логический массив =СУММПРОИЗВ(--{ИСТИНА:ИСТИНА: ЛОЖЬ:ИСТИНА:ЛОЖЬ}) → Двойное отрицание преобразует логические значения в числовые =СУММПРОИЗВ({1:1:0:1:0}) → Вы используете только часть СУММ функции СУММПРОИЗВ, так как у вас единственный аргумент массив1; формула возвращает одно значение → 3

Примечание: эта формула содержит два оператора массива: "--" и ">0" и, поэтому, работает медленнее, чем формула [1].

Формуле [4]. Эта формула дважды сосчитает клиентов, у которых и активы более $100 000, и кредитный рейтинг >= 3,5.

Формуле [5]. Эта формула также дает двойной счет, т.к. (B2:B6>E3)+(C2:C6>=E5) → {ЛОЖЬ:ИСТИНА: ЛОЖЬ:ИСТИНА:ЛОЖЬ}+{ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} → {1:2:0:1:0}

Рассмотренные концепции могут быть применены также для функций суммирования, нахождения среднего, минимума и максимума (рис. 11.15–11.17).

Рис. 11.15. Расчет суммы и среднего значения на основе критерия ИЛИ

Рис. 11.15. Расчет суммы и среднего значения на основе критерия ИЛИ, применяемого к одному столбцу; логическая проверка в каждой ячейке не может вернуть более одного значения ИСТИНА

Рис. 11.16. Расчет суммы и среднего значения на основе критерия ИЛИ, применяемого к двум столбцам

Рис. 11.16. Расчет суммы и среднего значения на основе критерия ИЛИ, применяемого к двум столбцам; логическая проверка в каждой ячейке может вернуть более одного значения ИСТИНА; формула [4] дает неверные ответ из-за двойного счета по ряду 4 (лишние 77 970)

Рис. 11.17. Расчет МИН и МАКС с использованием критерия ИЛИ

Рис. 11.17. Расчет МИН и МАКС с использованием критерия ИЛИ, применяемого к двум столбцам; логическая проверка в каждой ячейке может вернуть более одного значения ИСТИНА; обратите внимание, что в формуле [2] у функции АГРЕГАТ первый параметр (15) соответствует функции НАИМЕНШИЙ; при этом внутри формулы используется деление, чтобы отфильтровать (не включать в расчет) нулевые значения; в формуле [4] первый параметр (14) соответствует функции НАИБОЛЬШИЙ; здесь используется обычное умножение, так как нули не повлияют на результат (при этом и деление подошло бы); формулы [1] и [3] с функцией ЕСЛИ содержат на один оператор массива больше, чем формулы [2] и [4].

Одновременное использование критериев И, ИЛИ в одной формуле. Когда у вас есть логический тест, который сочетает в себе критерии И, ИЛИ, вы действуете в зависимости от того, может ли критерий ИЛИ вернуть более одного значения ИСТИНА. На рис. 11.18 приведены данные, где есть три И критерия, при том что последний И критерий включает ИЛИ критерий: дата >= 18/03/2013 И дата <=12/05/2013 И (регион = West ИЛИ Региона = Midwest). Поскольку логический тест ИЛИ указывает на один столбец, оператор сравнения не может вернуть более одного значения ИСТИНА. Обратите внимание, если у вас много ИЛИ критериев формулы [4] и [6] выиграют юлагодаря простоте написания.

Рис. 11.18. Критерии И, ИЛИ, когда логический тест ИЛИ указывает на один столбец

Рис. 11.18. Критерии И, ИЛИ, когда логический тест ИЛИ указывает на один столбец

На рис. 11.19 приведены данные, где есть три И критерия, при том что последний И критерий включает ИЛИ критерий: чистые активы > 100 000 И чистая прибыль >= 37 500 И (рейтинг 1 >= 3,5 ИЛИ рейтинг2 >= 6). В конкретной ячейке логический тест ИЛИ может вернуть более одного значения ИСТИНА. Логический тест ИЛИ указывает на несколько столбцов.

Рис. 11.19. Критерии И, ИЛИ, когда логический тест ИЛИ указывает на несколько столбцов

Рис. 11.19. Критерии И, ИЛИ, когда логический тест ИЛИ указывает на несколько столбцов

Этот пример иллюстрирует пользу от вспомогательного столбца, в котором удобно разместить логический тест. Благодаря этому формула [1] выглядит предельно простой. Если вы хотите обойтись без вспомогательного столбца, вы можете использовать формулы [2], [3] или [4]. Обратите внимание, что определения максимального и среднего значения (формулы [5] и [6]) в качестве фильтра использована функция ЕСЛИ (одинаковая для обеих формул). В аргумент лог_выражение функции ЕСЛИ помещен фрагмент формулы: (B2:B7>B10)*(E2:E7>D10)* ((C2:C7>=C10)+(D2:D7>=C12)). Обратите внимание, что здесь есть два умножения (для операторов И) и одно сложение (для оператора ИЛИ). Такого рода логические структуры типичны для критериев И, ИЛИ.

Имейте в виду, что если формулы вам не нужны, для обработки критерия ИЛИ можно использовать фильтр, расширенный фильтр и сводные таблицы, подобно тому, как показано на рис. 11.2–11.5.


Прокомментировать