Глава 11. Булева логіка: критерії І, АБО

Це глава з книги: Майкл Гирвіна. 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 вони часто використовуються для створення звітів, вибірки даних або розрахунків, заснованих на наборі критеріїв. Крім того, я зазвичай використовую синонім булева математика замість булевої логіки, тому що використовую множення для І критерію, і підсумовування - для АБО критерію.

Примітка: ось деякі синонімічні терміни, які ви можете зустріти в літературі:

• І = поєднання = одночасно = перетин = все = множення
• АБО = диз'юнкція = альтернатива = об'єднання = принаймні один = один або більше = додавання

Нижче наведені всі можливі комбінації з двох і трьох логічних значень в варіанті І критерію і АБО критерію:

Для І критерію використовується множення, так що якщо хоч один елемент дорівнює 0 (БРЕХНЯ), кінцевий результат буде 0 (БРЕХНЯ). І критерій поверне значення 1 (ІСТИНА) в єдиному випадку - коли всі елементи рівні 1.

Для АБО критерію використовується додавання, так того якщо хоч один елемент дорівнює 1 (ІСТИНА), кінцевий результат буде 1 (ІСТИНА). АБО критерій поверне значення 0 (БРЕХНЯ) в єдиному випадку - коли всі елементи рівні 0.

Ключова концепція, яка використовується при побудові великих формул, полягає в тому, що критерії І, АБО є фільтрами для відбору «потрібних» даних. Давайте розглянемо деякі приклади.

І критерій (рис. 11.1-11.7). У всіх прикладах використовується 4 критерії. Це означає, що всі чотири перевірки повинні повернути значення ІСТИНА для того щоб елемент даних був включений в розрахунок.

Мал. 11.1. Формули [1] - [4] використовують І критерії; [5] - логічне множення; [6] - логічне множення і ділення; [7] - функцію ЯКЩО і І критерій.

Примітки: докладніше про формулах [1], [2], [3] і [5] см. главу 10 , [4], [6] і [7] - главу 4 .

Мал. 11.2. Зведена таблиця за допомогою фільтрів підсумовує на основі І критерію

Мал. 11.3. Опція Таблиця з відповідними фільтрами і рядком Підсумки дозволяє обчислити середню (використовуються І критерій); докладніше про Таблицях см. главу 7

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

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

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

Мал. 11.7. Замість того, щоб, використовувати функцію І, ви можете перемножити оператори порівняння, і отримати в допоміжному стовпці одиниці і нулі

Щоб краще зрозуміти, як працює І критерій, давайте докладніше вивчимо пару формул, з рис. 11.1.

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

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

АБО критерій. При запуску логічного тесту АБО досить щоб хоча б одна перевірка повернула значення ІСТИНА, щоб звістка тест повернув значення ІСТИНА. Тільки в тому випадку, коли всі умови повернули значення БРЕХНЯ, тест поверне БРЕХНЯ. Характерні фрази при описі критерію АБО: «принаймні один», «один або більше».

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

Мал. 11.8. Відповідь на питання «Gidget або Rodger?» Не може повернути два значення ІСТИНА; критерій АБО, застосовуваний до однієї осередку вирішує проблему

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

Мал. 11.9. Кредит може бути виданий, якщо чисті активи> 100 000 або рейтинг> = 3,5

Чому важливо розрізняти питання АБО, які можуть давати більше одного значення істина, і питання АБО, які повертають не більше одного значення істина? Це важливо для побудови формул. Ви повинні бути особливо обережні з питаннями АБО, які можуть повертати кілька значень істинності. Я б сказав, що є два типи формул з логічним АБО.

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

• питання АБО, які вказують на одну клітинку;
• зазвичай це означає, що питання АБО відносяться до одного стовпцю.

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

• питання АБО, які вказують на різні осередки;
• зазвичай це означає, що питання АБО відносяться до кількох стовпців.

Щоб глибше зрозуміти дві зазначені різновиди тестів, порівняйте формули в наступних двох прикладах. На рис. 11.10 показаний набір формул, в яких критерій АБО, не можете повернути більше одного значення ІСТИНА.

Мал. 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. Формули аналогічні рис. 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. Кожен логічний тест АБО звертається у двох різних осередків і, отже, може повернути два значення ІСТИНА; формули масііва звертаються до двох стовпчиках (В і С)

Формула 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.16. Розрахунок суми і середнього значення на основі критерію АБО, що застосовується до двох стовпчиках; логічна перевірка в кожному осередку може повернути більше одного значення ІСТИНА; формула [4] дає невірні відповідь через подвійного рахунку по ряду 4 (зайві 77 970)

Мал. 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.19 наведені дані, де є три І критерію, при тому що останній І критерій включає АБО критерій: чисті активи> 100 000 І чистий прибуток> =: 37 500 І (рейтинг 1> = 3,5 АБО рейтінг2> = 6). У конкретній комірці логічний тест АБО може повернути більше одного значення ІСТИНА. Логічний тест АБО вказує на кілька стовпців.

Мал. 11.19. Критерії І, АБО, коли логічний тест АБО вказує на кілька стовпців

Цей приклад ілюструє користь від допоміжного стовпця, в якому зручно розмістити логічний тест. Завдяки цьому формула [1] виглядає гранично простий. Якщо ви хочете обійтися без допоміжного стовпця, ви можете використовувати формули [2], [3] або [4]. Зверніть увагу, що визначення максимального і середнього значення (формули [5] і [6]) в якості фільтра використана функція ЯКЩО (однакова для обох формул). В аргумент лог_вираз функції ЯКЩО поміщений фрагмент формули: (B2: B7> B10) * (E2: E7> D10) * ((C2: C7> = C10) + (D2: D7> = C12)). Зверніть увагу, що тут є два множення (для операторів І) і одне додавання (для оператора АБО). Такого роду логічні структури типові для критеріїв І, АБО.

Майте на увазі, що якщо формули вам не потрібні, для обробки критерію АБО можна використовувати фільтр, розширений фільтр і зведені таблиці, подібно до того, як показано на рис. 11.2-11.5.