Подсчет строк, соответствующих нескольким критериям в 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.
Используемая связная функция:
- СУММПРОИЗВ:
- Функция СУММПРОИЗВ может использоваться для перемножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.
Больше статей:
- Подсчет строк, соответствующих внутренним критериям
- Предположим, у вас есть отчет о продажах продуктов за этот год и прошлый год, и теперь вам нужно подсчитать продукты, где продажи в этом году больше, чем в прошлом, или продажи в этом году меньше, чем в прошлом, как показано на скриншоте ниже. Обычно можно добавить вспомогательный столбец для расчета разницы в продажах между двумя годами, а затем использовать функцию СЧЁТЕСЛИ для получения результата. Но в этой статье я расскажу, как использовать функцию СУММПРОИЗВ для получения результата напрямую без использования вспомогательного столбца.
- Подсчет совпадений между двумя столбцами
- Например, у меня есть два списка данных в столбце A и столбце C, и теперь я хочу сравнить эти два столбца и подсчитать, если значение в столбце A найдено в столбце C в той же строке, как показано на скриншоте ниже. В этом случае функция СУММПРОИЗВ может быть лучшей функцией для решения этой задачи в Excel.
- Подсчет количества ячеек, равных одному из многих значений
- Предположим, у меня есть список продуктов в столбце A, и теперь я хочу получить общее количество определенных продуктов Apple, Grape и Lemon, которые перечислены в диапазоне C4:C6 из столбца A, как показано на скриншоте ниже. Обычно в Excel простые функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН не будут работать в этом сценарии. В этой статье я расскажу, как быстро и легко решить эту задачу с помощью комбинации функций СУММПРОИЗВ и СЧЁТЕСЛИ.
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
Kutools для Excel имеет более 300 функций, гарантируя, что то, что вам нужно, находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите сотни кликов мышью каждый день, попрощайтесь с болью в руке от использования мышки.
- Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Привносит эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.