Резюме
Для підрахунку рядків з логікою АБО можна використовувати формулу, засновану на функції SUMPRODUCT. У наведеному прикладі формула в G6 має вигляд:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
де групи (B5: B15), color1 (C5: C15) і color2 (D5: D15) названі діапазонами.
Пояснення
Однією з найскладніших проблем у Excel є підрахунок рядків у наборі даних з "АБО логікою". Є два основних сценарії: (1) ви хочете підрахувати рядки, де значення в стовпці дорівнює "x" АБО "y" (2) ви хочете підрахувати рядки, де значення "x" існує в одному стовпці АБО іншому .
У цьому прикладі метою є підрахунок рядків, де group = "a" І Color1 АБО Color2 "червоні". Це означає, що ми працюємо із сценарієм 2 вище.
З COUNTIFS
Спочатку ви можете звернутися до функції COUNTIFS, яка обробляє кілька критеріїв. Однак функція COUNTIFS поєднує умови з логікою AND, тому всі критерії повинні мати значення TRUE, щоб бути включеними в рахунок:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Це робить COUNTIFS непрацездатним, якщо ми не використовуємо кілька екземплярів COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Переклад: підрахувати рядки, де група - "a", а color1 - "червона" + підрахувати рядки, де група - "a", а color2 - "червона" - підрахувати рядки, де група - "a", а color1 - "червона" і color2 - " червоний "(щоб уникнути подвійного підрахунку).
Це працює, але ви бачите, що це дещо складна і зайва формула.
З логічною логікою
Кращим рішенням є використання логічної логіки та обробка результату за допомогою функції SUMPRODUCT. (Якщо вам потрібен буквар з булевої алгебри, це відео пропонує вступ.) У наведеному прикладі формула в G6 така:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
де групи (B5: B15), color1 (C5: C15) і color2 (D5: D15) названі діапазонами.
Перша частина задачі - це перевірити на групу = "а", що ми робимо так:
(group="a")
Оскільки діапазон B5: B15 містить 11 комірок, цей вираз повертає масив з 11 значень TRUE і FALSE, як це:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Кожна ІСТИНА являє собою рядок, де група - "А".
Далі нам потрібно перевірити значення "червоний" у стовпці1 або стовпці2. Ми робимо це з двома виразами, об’єднаними додаванням (+), оскільки додавання відповідає логіці АБО в булевій алгебрі:
(color1="red")+(color2="red")
Excel автоматично обчислює значення TRUE та FALSE як 1s та 0s під час будь-якої математичної операції, тому результат з наведеного вище виразу є таким масивом:
(2;0;0;1;1;0;1;0;0;0;1)
Перше число в масиві - 2, оскільки і Color1, і Color2 "червоні" у першому рядку. З причин, пояснених нижче, нам потрібно захиститися від цієї ситуації, перевіривши значення більше нуля:
((2;0;0;1;1;0;1;0;0;0;1))>0
Тепер ми знову маємо масив значень TRUE і FALSE:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
У наведеній нижче таблиці викладено, як Excel оцінює кольорову логіку, пояснену вище:
На даний момент ми маємо результати тестування Group = "a" в одному масиві:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
І результати тестування "червоного" в Color1 або Color2 в іншому масиві:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Наступним кроком є об'єднання цих двох масивів разом з "І логікою". Для цього ми використовуємо множення (*), оскільки множення відповідає логіці І в булевій алгебрі.
Помноживши два масиви разом, ми маємо єдиний масив 1s і 0s, який доставляється безпосередньо до функції SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Функція SUMPRODUCT повертає суму чисел, 2, як кінцевий результат. Це кількість рядків, де group = "a" І Color1 АБО Color2 "червоний".
Щоб уникнути подвійного підрахунку
Ми не хочемо подвійно рахувати рядки, де і Color1 і Color2 є "червоними". Ось чому ми перевіряємо результати з (color1 = "red") + (color2 = "red") для значень, більших за нуль у коді нижче:
((color1="red")+(color2="red"))>0
Без цієї перевірки 2 із першого рядка даних відображатиметься у кінцевому масиві та призведе до неправильної повернення формули 3 як остаточного підрахунку.
Опція ФІЛЬТР
Одна приємна річ логічної логіки полягає в тому, що вона чудово працює з найновішими функціями Excel, такими як XLOOKUP та FILTER. Наприклад, функція ФІЛЬТР може використовувати точно ту ж логіку, що описана вище, для вилучення відповідних рядків:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Результатом FILTER є два рядки, які відповідають критеріям, як показано нижче:
Якщо ви хочете дізнатись більше про ці нові функції, ми маємо огляд та відеотренінг.
