Сложение с множественным выбором в MS EXCEL

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

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

Задача

Найдем строки, в которых в столбце Фрукты значатся Яблоки ИЛИ Апельсины ИЛИ Груши. Затем найдем сумму ящиков на складе в отобранных строках. Критерии (без повторов) будем вводить в ячейки Е5, Е6 и Е7 (на рисунке ниже выделены желтой заливкой).

Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =ИЛИ($A5=$E$5;$A5=$E$6;$A5=$E$7)

Решение

Общее количество ящиков с наименованиями фруктов Яблоки ИЛИ Апельсины ИЛИ Груши =150. Подсчет можно реализовать множеством формул, приведем только несколько:

  • Очевидная формула =

    =СУММЕСЛИ(A5:A16;E5;B5:B16)+
    СУММЕСЛИ(A5:A16;E6;B5:B16)+
    СУММЕСЛИ(A5:A16;E7;B5:B16)

     представляет сумму трех однокритериальных условий.

  • Формула =СУММПРОИЗВ(((A5:A16=E5)+(A5:A16=E6)+(A5:A16=E7))*(B5:B16))
  • Формула =БДСУММ(A4:B16;B4;D16:D19) требует предварительного создания таблички с условиями. Заголовок этой таблички должен в точности совпадать с заголовком исходной таблицы, по которому производится отбор строк (см. статью БДСУММ()). Размещение условий в разных строках соответствует Условию ИЛИ, см. диапазон D16:E19.

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

Данную задачу можно решить с помощью Автофильтра и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() (см. файл примера лист2)

1. В ячейку В3 введите формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B5:B16) Формула суммирует только те значения в диапазоне B5:B16, которые отобраны в данный момент Автофильтром (если Автофильтр не применен, то формула суммирует все значения диапазона).

2. Примените Автофильтр к исходной таблице, выделив заголовок и нажав CTRL+SHIFT+L

3. Выберите для столбца Фрукты нужные наименования:

4. Нажмите ОК

Как видим, суммарное количество ящиков для фруктов Яблоки, Апельсины, Груши как и для предыдущего решения =150.

Примечание: О сложении с множественными критериями можно прочитать в этом разделе Сложение с несколькими критериями.

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

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

Комментарии

MCH

Еще варианты:
=СУММПРОИЗВ(СУММЕСЛИ(A5:A16;E5:E7;B5:B16))

формула массива:
=СУММ((A5:A16=ТРАНСП(E5:E7))*B5:B16)