Резюме
Для подсчета строк с помощью логики ИЛИ можно использовать формулу, основанную на функции СУММПРОИЗВ. В показанном примере формула в G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
где группа (B5: B15), цвет1 (C5: C15) и цвет2 (D5: D15) являются именованными диапазонами.
Объяснение
Одна из более сложных проблем в Excel - подсчет строк в наборе данных с помощью логики «ИЛИ». Существует два основных сценария: (1) вы хотите подсчитать строки, в которых значение в столбце - «x» ИЛИ «y» (2) вы хотите подсчитать строки, в которых значение «x» существует в одном столбце ИЛИ в другом. .
В этом примере цель состоит в том, чтобы подсчитать строки, в которых group = "a" И Color1 OR Color2 являются "красными". Это означает, что мы работаем со сценарием 2 выше.
С СЧЁТЕСЛИМН
Сначала вы можете воспользоваться функцией СЧЁТЕСЛИМН, которая изначально обрабатывает несколько критериев. Однако функция СЧЁТЕСЛИМН объединяет условия с помощью логики И, поэтому все критерии должны быть ИСТИНА, чтобы их можно было включить в подсчет:
=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")
Перевод: подсчет строк, где группа - «а», а цвет1 - «красный» + подсчет строк, где группа - «а», а цвет2 - «красный» - подсчитать строки, где группа - это «а», а цвет1 - «красный», а цвет2 - « красный »(во избежание двойного счета).
Это работает, но вы можете видеть, что это несколько сложная и избыточная формула.
С булевой логикой
Лучшее решение - использовать логическую логику и обработать результат с помощью функции СУММПРОИЗВ. (Если вам нужен учебник по булевой алгебре, это видео дает введение.) В показанном примере формула в G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
где группа (B5: B15), цвет1 (C5: C15) и цвет2 (D5: D15) являются именованными диапазонами.
Первая часть проблемы - проверить наличие group = "a", что мы делаем следующим образом:
(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 автоматически оценивает значения ИСТИНА и ЛОЖЬ как единицы и нули во время любой математической операции, поэтому результатом приведенного выше выражения является такой массив:
(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;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)
Следующим шагом является объединение этих двух массивов с помощью «логики И». Для этого мы используем умножение (*), поскольку умножение соответствует логике И в булевой алгебре.
После умножения двух массивов мы получаем один массив из единиц и нулей, который передается непосредственно в функцию СУММПРОИЗВ:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Функция СУММПРОИЗВ возвращает сумму чисел, 2, в качестве окончательного результата. Это количество строк, в которых group = "a" И Color1 OR 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 являются две строки, соответствующие критериям, как показано ниже:
Если вы хотите узнать больше об этих новых функциях, у нас есть обзор и обучающее видео.
