Подсчет значений с множественными критериями (Часть 1. Условие И) в MS EXCEL

Произведем подсчет строк, удовлетворяющих сразу двум критериям, которые образуют Условие И.

В качестве исходной таблицы возьмем таблицу с двумя столбцами: текстовым «Фрукты» и числовым «Количество на складе» (См. файл примера).

Задача1

Рассмотрим задачу, когда критерии применяются к значениям из разных столбцов.

Найдем число партий товара с определенным Фруктом И с Количеством на складе не менее минимального (Условие И - условие при котором строка считается удовлетворяющей критерию, когда оба ее поля одновременно соответствуют критериям). Например, число партий персики (ячейка D2) с количеством ящиков на складе >=5 (ячейка Е2). Результат очевиден: 2. Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

Подсчет можно реализовать множеством формул, приведем несколько:

1. =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;">="&E2) Это решение является самым простым и понятным.

2. =СУММПРОИЗВ(--(A2:A13=D2);--(B2:B13>=E2)) Это решение сложнее, но позволяет понять работу функции СУММПРОИЗВ(), которая может быть полезна для подсчета с множественными критериями в других случаях. 

Разберем подробнее применение функции СУММПРОИЗВ():

  • Результатом вычисления A2:A13=D2 является массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение ИСТИНА соответствует персики. Результат можно увидеть, выделив A2:A13=D2, а затем нажав клавишу F9;
  • Двойное отрицание (--) преобразует вышеуказанный массив в числовой: {0:0:0:0:1:1:1:0:0:0:0:0}
  • Аналогично, второй массив возвращает {0:1:1:1:0:1:1:0:0:1:1:1}, где 0 соответствует значениям <5 (см. столбец «Количество ящиков на складе»). Первое значение (ячейка B2) =3, которое меньше 5 (не удовлетворяет критерию), поэтому первое значение в массиве  {0:1:1:1:0:1:1:0:0:1:1:1} =0. Второе значение (ячейка B3) =5, которое удовлетворяет критерию >=5, поэтому второе значение в массиве =1 и т.д.
  • Далее, функция попарно перемножает элементы массивов и суммирует их. Получаем – 2.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)). Здесь, знак Умножения (*) эквивалентен Условию И.

4. Формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2)) эквивалентна вышеупомянутой формуле =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2))  Единственное, после ее ввода нужно вместо ENTER нажать CTRL+SHIFT+ENTER

5. Формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула =БСЧЁТА(A1:B13;A1;D14:E15) требует предварительного создания таблицы с условиями. Заголовки этой таблицы должны в точности совпадать с заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И.

Здесь есть один трюк: в качестве второго аргумента функции БСЧЁТА() (поле) нужно ввести ссылку на заголовок столбца с текстовыми значениями, т.к. БСЧЁТА() подсчитывает текстовые значения. В случае использования БСЧЁТ() нужно записать другую формулу =БСЧЁТ(A1:B13;B1;D14:E15). Табличка с критериями не изменится.

Задача2

Рассмотрим задачу, когда критерии применяются к значениям из одного столбца.

Найдем число партий товара с Количеством на складе не менее минимального и не более максимального (Условие И - строка таблицы соответствует критерию, когда ее поле удовлетворяет обоим критериям одновременно). 

Решение стоится аналогично предыдущей задачи. Например, с использованием функции СЧЁТЕСЛИМН() формула выглядит так (см. лист один столбец в файле примера):

=СЧЁТЕСЛИМН(B2:B13;">="&D2;B2:B13;"<="&E2)

Альтернативное решение

Подсчитать количество строк, удовлетворяющим 2-м критериям (Условие И) можно без применения формул с помощью стандартного Автофильтра.

Установите автофильтр к столбцу Количество ящиков на складе, выделив заголовок столбца и нажав CTRL+SHIFT+L. Выберите числовой фильтр Между.

 

Введите критерии

Убедитесь, что результат такой же как в задаче2 - т.е. будет отобрано 7 строк (см. строку состояния в нижней части окна).

Примечание: подсчет значений с множественными критерями также рассмотрен в статьях Подсчет значений с множественными критериями (Часть 2. Условие ИЛИ), Часть3, Часть4.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (3 оценок)