Пример функции БДСУММ для суммирования по условию в базе Excel

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

Примеры использования функции БДСУММ в Excel

Пример 1. В таблицу записываются данные о выданных кредитов клиентам менеджерами банка на протяжении нескольких дней. Определить, какую сумму средств в долг выдали менеджер_1 и менеджер_3 за весь период.

Вид исходной таблицы данных:

Создадим следующую таблицу условий:

Для определения суммы выданных кредитов двумя указанными менеджерами запишем формулу:

=БДСУММ(A10:D28;D10;C4:C6)

Описание аргументов:

A10:D28 – диапазон ячеек, в которых содержится база данных;
D10 – ссылка на ячейку, содержащую название столбца с данными, которые будут суммированы в соответствии с используемыми критериями;
C4:C6 – диапазон ячеек, в которых содержится таблица условий.

Полученный результат:

В результате функция автоматически суммирует по условиям, указанным в таблице критериев.

Суммирование в базе данных по условию с помощью функции БДСУММ

Пример 2. Используя таблицу из первого примера определить, кредиты на какую общую сумму были выданы вторым менеджером в период с 5.09 по 15.09?

Для решения составим следующую таблицу условий:

Поскольку условия, связанные логическим И, записываются в одну строку, было создано два столбца «Дата». Для расчета запишем следующую формулу:

Описание аргументов:

Пример1!A10:D28 – ссылка на таблицу данных, содержащейся на листе с названием «Пример1»;
Пример1!D10 – ссылка на столбец таблицы, содержащего данные о сумме выданных кредитов;
Пример2!A2:C3 – ссылка на таблицу условий, содержащейся на текущем листе.

Результат расчетов:

Сравнение суммы значений при определенных условиях в Excel

Пример 3. В call-центре компании работают несколько менеджеров. По завершению звонка клиенты оценивают качество работы менеджеров по 10-бальной шкале. Найти общую сумму баллов первого и третьего менеджеров за последние 2 дня. Сравнить их с суммой баллов второго менеджера за весь период (3 дня).

Вид исходной таблицы:

Вид таблиц условий:

Для расчета общей суммы баллов, заработанных первым и третьим менеджером на протяжении двух последних дней, используем формулу:

=БДСУММ(A1:D21;D1;F1:G3)

Для определения суммы баллов, заработанных менеджером за 3 дня, используем формулу:

=БДСУММ(A1:D21;D1;G5:G6)

Полученные значения:

Можно предположить, что менеджер №2 работает эффективнее любого другого менеджера.

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

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

=D2>=СРЗНАЧ($D$2:$D$21)

Описание аргументов:

D11 – относительная ссылка на первую ячейку данных столбца «Балл»;
$D$11:$D$30 – абсолютная ссылка на диапазон ячеек столбца «Балл».

Поскольку ссылка D11 является относительной, при выполнении функции БДСУММ логическое выражение =D11>=СРЗНАЧ($D$11:$D$30) будет вычисляться последовательно для каждой ячейки столбца «Балл». Расчет будет проводиться для значений, при которых выражение возвращает значение ИСТИНА.

Для расчета используем формулу:

=БДСУММ(A1:D21;D1;G9:G10)

Полученный результат:

Особенности использования функции БДСУММ в Excel

Функция БДСУММ используется наряду с прочими функциями для работы с базами данных (ДСРЗНАЧ, БСЧЁТ,БИЗВЛЕЧЬ и др.) и имеет следующий синтаксис:

=БДСУММ(база_данных; поле; условия)

Описание аргументов (все являются обязательными для заполнения):

база_данных – аргумент, принимающий данные ссылочного типа. Ссылка может указывать на базу данных либо на список, данные в котором являются связанными;
поле – аргумент, принимающий текстовые данные, характеризующие название поля в базе данных (заголовок столбца таблицы), или числовые значения, характеризующие порядковый номер столбца в списке данных. Отсчет начинается с единицы, то есть первый столбец списка может быть обозначен числом 1. Еще один вариант заполнения аргумента поле – передача ссылки на требуемый столбец (на ячейку, в которой содержится его заголовок);
условия – аргумент, принимающий ссылку на диапазон ячеек, содержащих одно или несколько критериев поиска в базе данных. При создании критериев необходимо указывать заголовки столбцов исходной таблицы (базы данных), к которым они относятся. Фактически, требуется создать таблицу критериев, подобную той, которая необходима для использования расширенного фильтра.

Примечания:

Если в качестве базы данных используется умная таблица, аргумент база_данных должен содержать название таблицы и тег [#Все]. Пример записи: =БДСУММ(УмнаяТаблица[#Все];”Имя_столбца”;A1:A5).
Наименования столбцов в таблице критериев должны совпадать с названиями соответствующих столбцов в базе данных.
При записи критерия поиска в виде текстовой строки следует учитывать, что функция БДСУММ нечувствительна к регистру.
Если требуется просуммировать значения, содержащиеся во всем столбце базы данных, можно создать таблицу условий, которая содержит название столбца исходной таблицы, а в качестве критерия будет выступать пустая ячейка.
На результат вычислений функции БДСУММ не влияет место расположения таблицы условий, однако рекомендуется размещать ее над базой данных.
Заданные критерии могут соответствовать условиям с логическими связками И и ИЛИ:

Для связки данных логическим условием И необходимо перечислить их в одной строке, то есть создать таблицу условий с двумя и более столбцами, каждый из которых содержит название столбца и условие;
Если требуется организовать связку условий с использованием логического ИЛИ, тогда столбец таблицы условий должен состоять из названия и расположенных под ним двух и более условий;
Логические связки И и ИЛИ можно комбинировать, то есть таблица условий может содержать несколько столбцов, каждый из который содержит несколько условий, если требуется.

Функция БДСУММ относится к числу функций, используемых для работы с базами данных. Поэтому, для получения корректных результатов она должна использоваться для таблиц, созданных в соответствии со следующими критериями:

Наличие заголовков, относящихся к каждому столбцу таблицы, записанных в одной ячейке. Объединение ячеек или наличие пустых ячеек в заголовках не допускается.
Отсутствие объединенных и пустых ячеек в области хранения данных. Если данные отсутствуют, следует явно указывать значение 0 (нуль).
Все данные в столбце должны быть релевантными его заголовку и быть одного типа. Например, если в таблице содержится столбец с заголовком «Стоимость», все ячейки расположенного ниже вектора (диапазона ячеек шириной в один столбец) должны содержать числовые значения, характеризующие стоимость какого-либо товара. Если стоимость неизвестна, необходимо ввести значение 0.
В базе данных строки именуют записями, а столбцы – полями данных.

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