Подсчет строк, соответствующих нескольким критериям в Excel

Подсчитайте количество строк в диапазоне на основе нескольких критериев, некоторые из которых зависят от логических тестов, работающих на уровне строк. Функция СУММПРОИЗВ в Excel может помочь вам.

Например, у меня есть отчет о продуктах с запланированными и фактическими продажами, и теперь я хочу подсчитать строки, содержащие «Apple», где фактические продажи больше, чем запланированные, как показано на скриншоте ниже. Для решения этой задачи наиболее эффективной функцией является СУММПРОИЗВ.

Подсчет строк, соответствующих нескольким критериям, с использованием функции СУММПРОИЗВ

Подсчет строк, соответствующих нескольким критериям, с использованием функции СУММПРОИЗВ

Чтобы подсчитать строки, соответствующие нескольким критериям, используя функцию СУММПРОИЗВ в Excel, общий синтаксис выглядит так:

• логическое1, логическое2: Логические выражения, используемые для сравнения значений.

1. Чтобы подсчитать количество строк «Apple», где фактические продажи больше, чем запланированные, примените следующую формулу:

Примечание: В приведенной выше формуле C2:C10>B2:B10 — это первое логическое выражение, которое сравнивает значения в столбце C со значениями в столбце B; A2:A10=E2 — второе логическое выражение, которое проверяет, существует ли значение ячейки E2 в столбце A.

2. Затем нажмите клавишу Enter, чтобы получить необходимый результат, см. скриншот:

Объяснение формулы:

=СУММПРОИЗВ(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))

• $C$2:$C$10>$B$2:$B$10: Это логическое выражение используется для сравнения значений в столбце C со значениями в столбце B в каждой строке. Если значение в столбце C больше, чем значение в столбце B, отображается ИСТИНА, в противном случае — ЛОЖЬ, и возвращаются массивы значений, такие как: {ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА}.
• $A$2:$A$10=E2: Это логическое выражение используется для проверки, существует ли значение ячейки E2 в диапазоне A2:A10. Таким образом, вы получите результат, например: {ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ}.
• ($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2): Операция умножения используется для перемножения этих двух массивов в один массив, который возвращает результат: {1;0;1;0;0;0;0;1;0}.
• СУММПРОИЗВ(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))= СУММПРОИЗВ({1;0;1;0;0;0;0;1;0}): Эта функция СУММПРОИЗВ суммирует числа в массиве и возвращает результат: 3.

Используемая связная функция:

Больше статей: