Сложение с несколькими критериями в MS EXCEL (Часть 4.Два Условия (ИЛИ и И))

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

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

Задача

Найдем число партий товара с двумя наименованиями фруктов, например, персики ИЛИ яблоки, с одновременным ограничением по остатку на складе >=10. Затем просуммируем ящики у отобранных позиций. 

Решение

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

Сложение можно реализовать множеством формул (результат - 77 ящиков), приведем несколько:

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

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

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

Комментарии

оригинальный подарок (не проверено)

Автор, а у вас никто записи не тырит? А то у меня заколебали уже - копируют и копируют. И главное, что даже ссылку никто не удосужится поставить.

Creator

@#1 Не совсем понятна цель комментария: 1. Если Вы ищите сочуствия по поводу того, что у Вас кто-то что-то копирует, то мы искренне сочувствуем, что Вы по этому поводу переживаете. 2. Если это завуалированный намек на то, что эта статья скопирована у Вас, то это не правда: ВСЕ статьи на этом сайте оригинальны, хотя совпадения тематик статей безусловно возможны.


В любом случае, для предметного обсуждения предоставляйте больше информации: на каком сайте размещена похожая статья, в чем цель комментария.