Перейти к основному содержанию

Подсчет строк, если они соответствуют нескольким критериям в 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 - поможет вам выделиться из толпы

Популярные опции: Найдите, выделите или определите дубликаты  |  Удалить пустые строки  |  Объедините столбцы или ячейки без потери данных  |  Раунд без формулы ...
Супер ВПросмотр: Несколько критериев  |  Множественное значение  |  На нескольких листах  |  Нечеткий поиск...
Адв. Выпадающий список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Выпадающий список с множественным выбором...
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  Сравнить столбцы с Выберите одинаковые и разные ячейки ...
Рекомендуемые функции: Сетка Фокус  |  Просмотр дизайна  |  Большой Формулный Бар  |  Менеджер книг и листов | Библиотека ресурсов (Авто текст)  |  Выбор даты  |  Комбинировать листы  |  Шифровать/дешифровать ячейки  |  Отправлять электронные письма по списку  |  Суперфильтр  |  Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы ...)  |  50+ График Тип (Диаграмма Ганта ...)  |  40+ Практических Формулы (Рассчитать возраст по дню рождения ...)  |  19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути ...)  |  12 Конверсия Инструменты (Числа в слова, Конверсия валюты ...)  |  7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить ячейки Excel ...)  |  ... и более

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...


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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
=SUMPRODUCT({Array of True/False}) doesn't count the True values in the array anymore (as of the SUM or COUNT formulaes).
But you can force the convertion of True/False to 1 and 0 by adding the '--' operator right before the array:
=SUMPRODUCT(--{Array of True/False}).
You can also type this operator right after the multiplication sign, giving the strange '*--' operator.

In this exemple, a working formulae would be:
=SUMPRODUCT(--($C$2:$C$10>$B$2:$B$10)*--($A$2:$A$10=E2))
This comment was minimized by the moderator on the site
Hello Professor X,

You are right in one way. The double negative (--) is one of several ways to coerce TRUE and FALSE values into their numeric equivalents, 1 and 0. Once we have 1s and 0s, we can perform various operations on the arrays with Boolean logic.

But our formula doesn't need the the double negative (--), making the formula more compact. This is because the math operation of multiplication (*) automatically converts the TRUE and FALSE values to 1s and 0s. Have a nice day.

Sincerely,
Mandy
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations