Подсчет строк, если они соответствуют нескольким критериям в Excel
Подсчитайте количество строк в диапазоне на основе нескольких критериев, некоторые из которых зависят от логических тестов, работающих на уровне строк, функция СУММПРОИЗВ в Excel может оказать вам услугу.
Например, у меня есть отчет по продукту с запланированными и фактическими продажами, теперь я хочу подсчитать строки, содержащие Apple, фактическая продажа которых превышает запланированную, как показано ниже. Для решения этой задачи наиболее эффективной является функция СУММПРОИЗВ.
Подсчитайте строки, если они соответствуют нескольким критериям, с функцией СУММПРОИЗВ
Подсчитайте строки, если они соответствуют нескольким критериям, с функцией СУММПРОИЗВ
Общий синтаксис для подсчета строк, если они соответствуют нескольким критериям, с помощью функции СУММПРОИЗВ в Excel:
- logical1, logical2: Логические выражения, используемые для сравнения значений.
1. Для подсчета количества строк Apple, фактическая продажа которых превышает запланированную, примените следующую формулу:
Внимание: В приведенной выше формуле C2: C10> B2: B10 это первое логическое выражение, которое сравнивает значения в столбце C со значениями в столбце B; A2: A10 = E2 - второе логическое выражение, которое проверяет, существует ли ячейка E2 в столбце A.
2, Затем нажмите Enter ключ, чтобы получить нужный результат, см. снимок экрана:
Пояснение к формуле:
=SUMPRODUCT(($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}.
- SUMPRODUCT(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))= SUMPRODUCT({1;0;1;0;0;0;0;1;0}): СУММПРОИЗВ суммирует числа в массиве и возвращает результат: 3.
Используемая относительная функция:
- SUMPRODUCT:
- Функцию СУММПРОИЗВ можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.
Другие статьи:
- Подсчитать строки, если они соответствуют внутренним критериям
- Предположим, у вас есть отчет о продажах продукции в этом и прошлом году, и теперь вам может потребоваться подсчитать продукты, продажи в которых в этом году больше, чем в прошлом году, или продажи в этом году меньше, чем в прошлом году, как показано ниже. показан снимок экрана. Обычно вы можете добавить вспомогательный столбец для расчета разницы продаж за два года, а затем использовать COUNTIF для получения результата. Но в этой статье я представлю функцию СУММПРОИЗВ, чтобы получить результат напрямую, без какого-либо вспомогательного столбца.
- Подсчет совпадений между двумя столбцами
- Например, у меня есть два списка данных в столбце A и столбце C, теперь я хочу сравнить два столбца и подсчитать, найдено ли значение в столбце A в столбце C в той же строке, что и на скриншоте ниже. В этом случае функция СУММПРОИЗВ может быть лучшей функцией для решения этой задачи в Excel.
- Подсчитать количество ячеек равно одному из многих значений
- Предположим, у меня есть список продуктов в столбце A, теперь я хочу получить общее количество конкретных продуктов Apple, Grape и Lemon, которые перечислены в диапазоне C4: C6 из столбца A, как показано на скриншоте ниже. Обычно в Excel простые функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН не работают в этом сценарии. В этой статье я расскажу о том, как быстро и легко решить эту задачу с помощью комбинации функций СУММПРОИЗВ и СЧЁТЕСЛИ.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
