Функция БДСУММ() - Сложение с множественными условиями в EXCEL

Функция БДСУММ() , английский вариант DSUM(), суммирует числа в таблице данных, которые удовлетворяют заданным условиям.

Рассмотрим мощную функцию суммирования БДСУММ() , английский вариант DSUM( database, field, criteria ). Эту функцию имеет смысл использовать, когда необходимо просуммировать значения с учетом нескольких условий. Подробный анализ этих задач приводится в группе статей Сложение чисел с несколькими критериями .

Как показано в вышеуказанных статьях, без функции БДСУММ() можно вообще обойтись, заменив ее функциями СУММПРОИЗВ() , СУММЕСЛИМН() или формулами массива . Но, иногда, функция БДСУММ() действительно удобна, особенно при использовании многочисленных или сложных критериев, например, с подстановочными знаками . Сначала разберем синтаксис функции, затем решим задачи.

Синтаксис функции БДСУММ()

Для использования этой функции требуется чтобы:

• исходная таблица имела заголовки столбцов;
• критерии должны были оформлены в виде небольшой таблицы с заголовками;
• заголовки таблицы критериев совпадали с заголовками исходной таблицы (если критерий не задается формулой).

БДСУММ( база_данных;поле;условия ) База_данных представляет собой диапазон ячеек с данными связанными логически, т.е. таблицу. Верхняя строка таблицы должна содержать заголовки всех столбцов. Поле — Заголовок столбца, по которому производится суммирование (т.е. столбец с числами). Аргумент Поле можно заполнить введя:

• текст с заголовком столбца в двойных кавычках, например "Возраст" или "Урожай",
• число (без кавычек), задающее положение столбца в таблице (указанной в аргументе база_данных ): 1 — для первого столбца, 2 — для второго и т.д.
• ссылку на заголовок столбца.

Условия — интервал ячеек, который содержит задаваемые условия (т.е. таблица критериев). Структура таблицы с критериями отбора для БДСУММ() аналогична структуре для Расширенного фильтра .

Задачи

Предположим, что в диапазоне A 8:С13 имеется таблица продаж, содержащая поля (столбцы) Товар , Продавец и Продажи (см. рисунок выше и файл примера ).

Задача 1 (с одним числовым критерием).

Просуммируем все продажи, которые >3000.

Алгоритм следующий:

• Создадим в диапазоне F2:F3 табличку с критерием (желательно табличку располагать над исходной таблицей, чтобы она не мешала добавлению новых данных в таблицу), состоящую из заголовка Продажи (совпадает с названием заголовка столбца исходной таблицы, к которому применяется критерий) и собственно критерия (условия отбора) >3000.
• запишем саму формулу =БДСУММ(C8:C13;C8;F2:F3) Предполагая, что База_данных (исходная таблица) находится в С8:C13 (столбцы А (Товар) и В (Продавец) можно в данном случае не включать в Базу_данных, т.к. они не участвуют в критерии отбора и по ним не производится суммирование). С8 – это ссылка на заголовок столбца по которому будет производиться суммирование (т.е. столбец Продажи). F 2 : F3 – ссылка на табличку критериев

Альтернативное решение - = СУММЕСЛИ(C9:C13;F3) или = СУММЕСЛИ(C9:C13;">3000")

Задача 2 (с одним текстовым критерием)

Просуммируем все значения продаж продавца Белов .

Алгоритм следующий:

• Создадим новую табличку критериев, состоящую из заголовка Продавец (совпадает с названием заголовка столбца исходной таблицы, к которому применяется критерий) и собственно критерия (условия отбора);

• Условие отбора должно быть записано в специальном формате: ="=Белов" (будут суммироваться Продажи только строк, у которых в столбце Продавец содержится точно слово Белов (или белов , беЛОв , т.е. без учета РЕгиСТра ). Если имеются строки с Продавцами « Иван Белов», «Белов Иван» и пр., то суммирование по ним производиться не будет. Примечание : Если в качестве критерия указать не ="= Белов " , а просто Белов , то, будут суммироваться Продажи строк, у которых в столбце Продавец содержатся значения, начинающиеся со слова Белов (например, « Белов Иван », Белов , белов ). Чтобы просуммировать продажи, в том числе и для продавца « Иван Белов », необходимо в качестве критерия указать ="=*Белов". Этот критерий учитывает значения, заканчивающиеся на Белов. Звездочка ( *) - это подстановочный знак .Если в качестве критерия указать *Белов (или ="=*Белов*") , то будут подсчитаны числа, в соответствующих ячейках которых содержится слово Белов.
• Теперь можно наконец записать саму формулу =БДСУММ(B8:C13;C8;B2:B3) Предполагая, что База_данных (исходная таблица) находится в B8:C13 (столбец А ( Товар ) можно в данном случае не включать в Базу_данных, т.к. он не участвует в формировании условия и по нему не производится суммирование). С8 – это ссылка на заголовок столбца по которому будет производиться суммирование (т.е. столбец Продажи ). B2:B3 – ссылка на табличку критериев.

Альтернативное решение - = СУММЕСЛИ(B9:B13;"белов";C9:C13)

Задача 3 (Два критерия к разным столбцам строки, Условие И)

Найдем сумму продаж >3000 только продавца Белов . Т.е. нужно отобрать строки, у которых в столбце Продавец значится Белов , а в столбце Продажи значение >3000, затем просуммировать значения продаж в отобранных строках (см. также статью про Условие И ).

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

Формула для сложения: = БДСУММ(B8:C13;C8;F2:G3)

Альтернативное решение - =СУММЕСЛИМН(C9:C13;B9:B13;G3;C9:C13;F3) или =СУММЕСЛИМН(C9:C13;B9:B13;"белов";C9:C13;">3000")

Задача 4 (Два текстовых критерия к одному столбцу, условие отбора ИЛИ)

Найдем сумму продаж продавцов Белов ИЛИ Батурин . Т.е. нужно отобрать строки, в которых в столбце Продавец значится Белов ИЛИ Батурин (см. также статью про Условие ИЛИ ).

В этом случае, критерии отбора должны размещаться под заголовком, название которого совпадает с заголовком столбца исходной таблицы, по которому будет производиться поиск (столбец Продавец ). Критерии должны располагаться друг под другом (в разных строках), т.к. отбираются строки, у которых в поле Продавец значение Белов ИЛИ строки, у которых в поле Продавец значение Батурин (функция БДСУММ () как бы совершает 2 прохода по таблице с разными критериями для одного поля).

Записать саму формулу можно так =БДСУММ(B8:C13;C8;B2:B4)

Альтернативное решение - =СУММЕСЛИ(B9:B13;"белов";C9:C13)+СУММЕСЛИ(B9:B13;"батурин";C9:C13)

Задача 5 (Два критерия к разным столбцам, условие отбора ИЛИ)

Найдем сумму продаж Белова ИЛИ Продаж >6000 Т.е. нужно отобрать строки, в которых в столбце Продавец значится Белов ИЛИ в столбце Продажи имеется значение >6000 .

Критерии должны располагаться в разных строках и в разных столбцах, т.к. отбираются строки, у которых в поле Продавец значение Белов ИЛИ строки, у которых в поле Продажи значение >6000 (функция БДСУММ () как бы совершает 2 прохода по таблице с разными критериями для 2-х разных полей).

Записать саму формулу можно так =БДСУММ(B8:C13;C8;G2:H4)

Альтернативное решение - = СУММЕСЛИ(B9:B13;G3;C9:C13)+СУММЕСЛИ(C9:C13;H4)-СУММЕСЛИМН(C9:C13;B9:B13;G3;C9:C13;H4) или = СУММЕСЛИ(B9:B13;"белов";C9:C13)+СУММЕСЛИ(C9:C13;">6000")-СУММЕСЛИМН(C9:C13;B9:B13;"белов";C9:C13;">6000")

Задача 6 (Два текстовых критерия к разным столбцам, условие отбора И)

Найдем сумму продаж товара Фрукты продавца Белов . Т.е. нужно отобрать строки, в которых в столбце Продавец значится Белов И в столбце Товар значится Фрукты .

В этом случае, критерии отбора должны размещаться под заголовками, названия которых совпадают с заголовками столбцов исходной таблицы, по которым будет производиться поиск (столбцы Продавец и Товар ).

Записать саму формулу можно так =БДСУММ(A8:C13;C8;A2:B3)

Альтернативное решение - =СУММЕСЛИМН(C9:C13;A9:A13;"фрукты";B9:B13;"белов")

Задача 7 (Условия отбора, созданные в результате применения формулы)

Просуммируем продажи, которые выше среднего.

В качестве условия отбора можно использовать значение, вычисляемое при помощи формулы. Формула должна возвращать результат ИСТИНА или ЛОЖЬ.

Для этого введем в ячейку С3 файла примера формулу =C9>СРЗНАЧ($C$9:$C$13) , а в С2 вместо заголовка введем произвольный поясняющий текст, например, « Больше среднего » (заголовок не должен повторять заголовки исходной таблицы).

Обратите внимание на то, что диапазон нахождения среднего значения введен с использованием абсолютных ссылок ( $C$9:$C$13 ), а среднее значение всех продаж таблицы СРЗНАЧ($C$9:$C$13) сравнивается с первым значением диапазона, ссылка на который задана относительной адресацией ( C9 ). При вычислении функции БДСУММ() EXCEL увидит, что С9 — это относительная ссылка, и будет перемещаться по диапазону вниз по одной записи и возвращать значение либо ИСТИНА, либо ЛОЖЬ (больше среднего или нет). Если будет возвращено значение ИСТИНА, то соответствующая строка таблицы будет учтена при суммировании. Если возвращено значение ЛОЖЬ, то строка учтена не будет.

Записать формулу можно так =БДСУММ(C8:C13;C8;C2:C3)

Альтернативное решение - =СУММЕСЛИ(C9:C13;">"&СРЗНАЧ($C$9:$C$13))

Задача 8 (Три критерия)

Найдем сумму продаж Белова , которые выше среднего, а также продажи Батурина .

Записать формулу можно так =БДСУММ(B8:C13;C8;B2:C4)

Альтернативное решение - =СУММЕСЛИМН(C9:C13;C9:C13;">"&СРЗНАЧ($C$9:$C$13);B9:B13;"Белов")+СУММЕСЛИ(B9:B13;"Батурин";C9:C13)

Задача 9 (Один текстовый критерий, учитывается РегиСТр)

Сумма продаж Товара ФРУкты (первые три буквы - ЗАГЛАВНЫЕ (т.е. прописные))

Записать формулу можно так =БДСУММ(A8:C13;C8;E2:E3)

Альтернативное решение - =СУММПРОИЗВ(СОВПАД("ФРУкты";A9:A13)*C9:C13)