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

Произведем подсчет строк таблицы, удовлетворяющих сразу трем критериям, которые образуют Условие ИЛИ и Условие И. Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в столбце Фрукты значится Персики ИЛИ Яблоки, причем с остатком на складе не менее 10 (ящиков). 

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

Задача

Найдем число партий товара с двумя наименованиями фруктов, например, персики ИЛИ яблоки, с одновременным ограничением по остатку на складе >=10. Результат очевиден: 3.

Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =ИЛИ(И($A2=$D$2;$B2>=$F$2);И($A2=$E$2;$B2>=$F$2))

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

  • Формула =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;">="&F2)+СЧЁТЕСЛИМН(A2:A13;E2;B2:B13;">="&F2) подсчитывает сначала количество партий Яблоки с остатком >=10, затем количество партий Персики, также с остатком >=10. В случае, если пользователь введет в ячейках D2 и E2 одинаковые наименования фруктов, формула вернет удвоенный результат. Формула учитывающая этот случай приведена в файле примера и на рисунке выше.
  • Формула =СУММПРОИЗВ(((A2:A13=D2)+(A2:A13=E2))*(B2:B13>=F2))  Знак умножить (*) соответствует Условию И, а знак +  соответствует Условию ИЛИ. Внимание! При совпадении условий, например, персики ИЛИ персики, формула также дает удвоенный результат. Необходимо, либо включить проверку, либо запретить ввод одинаковых условий. 
  • Для понимания работы формулы массива =СУММ(((A2:A13=D2)+(A2:A13=E2))*(B2:B13>=F2)) не забывайте выделять интересующие части формулы в Строке формул и нажимать клавишу F9. Внимание! При совпадении условий, например, персики ИЛИ персики, формула также дает удвоенный результат.
  • Формула массива  =СЧЁТ(ЕСЛИ(((A2:A13=D2)+(A2:A13=E2))* (B2:B13>=F2);B2:B13)) представляет еще один вариант многокритериального подсчета значений. Формула дает правильный результат, даже при совпадении условий.
  • Формула =БСЧЁТА(A1:B13;A1;D13:E15) требует предварительного создания таблицы с условиями. Заголовки этой таблицы должны в точности совпадать с заголовками исходной таблицы. Размещение условий в разных строках соответствует Условию ИЛИ, в одной строке – Условию И.
  • Другой вариант - используйте формулу =БСЧЁТ(A1:B13;B1;D13:E15) с той же табличкой критериев.

Для понимания таблицы с критериями можно преобразовать предыдущую формулу ((A2:A13=D2)+(A2:A13=E2))*(B2:B13>=F2) в соответсвии с тождеством (a+b)*c = a*c+b*c. Умножить соответствует Условию И (и, соответственно, размещению в одной строке), а знак +  соответствует Условию ИЛИ (и, соответственно, размещению в разных строках).

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

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

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