Note: The other languages of the website are Google-translated. Back to English
English English

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

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

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

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


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

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

=SUMPRODUCT((logical1)*(logical2))
  • logical1, logical2: Логические выражения, используемые для сравнения значений.

1. Для подсчета количества строк Apple, фактическая продажа которых превышает запланированную, примените следующую формулу:

=SUMPRODUCT(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))

Внимание: В приведенной выше формуле 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 for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (2)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
=СУММПРОИЗВ({Массив истинных/ложных}) больше не считает истинные значения в массиве (как в формулах СУММ или СЧЁТ).
Но вы можете принудительно преобразовать True/False в 1 и 0, добавив '--' оператор прямо перед массивом:
=СУММПРОИЗВ(--{Массив Истина/Ложь}).
Вы также можете ввести этот оператор сразу после знака умножения, что даст странный оператор '*--'.

В этом примере рабочие формулы будут такими:
=SUMPRODUCT(--($C$2:$C$10>$B$2:$B$10)*--($A$2:$A$10=E2))
Этот комментарий был сведен к минимуму модератором на сайте
Добрый день, профессор Х.

Вы правы в одном. Двойное отрицание (--) — это один из нескольких способов привести значения TRUE и FALSE к их числовым эквивалентам, 1 и 0. Получив 1 и 0, мы можем выполнять различные операции с массивами с помощью логической логики.

Но наша формула не нуждается в двойном отрицании (--), что делает формулу более компактной. Это связано с тем, что математическая операция умножения (*) автоматически преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0. Хорошего дня.

С уважением,
Мэнди
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места