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

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

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

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

=БДСУММ(диапазон вашей базы данных; поле поиска; условие поиска), где

При работе с функцией БДСУММ стоить отметить несколько условий, на которые стоит обращать внимание при работе:

• При выполнении работы над целым столбиком заполненным данными, обязательно стоит вставить пустую строку под заголовками столбиков в указанном диапазоне критериев;
• Диапазон критериев не должен иметь пересечения со списком;
• Хотя данные для формирования диапазона условий вы можете помещать в любое место на листе, но тем не менее, помещать его после списка не следует, так как данные которые мы добавляем в список, будут вставляться в первую строку после списка и если в строке есть данные, то добавить новые не получится;
• Можно использовать для условий любой диапазон, который содержит, как минимум один заголовок и хотя бы одну из ячеек, которая расположена под заголовком и содержит в себе условие.

Итак, теоретическую часть, я считаю выполненной, приступим теперь к практическому применению функции БДСУММ в своей работе, для этого рассмотрим несколько примеров для выполнения, я примеры сделал по принципу расширенного фильтра, но вместо сбора значений по критерию будет суммирование:

Функция БДСУММ с одним числовым критерием

Итак, для начала рассмотрим простой пример с одним числовым критерием, для этого выберем столбик «Урожай» и укажем что нам надо деревья с урожайность «>=10». Что бы получить результат нам нужна формула такого вида (советую использовать абсолютные ссылки):

где, $B$6:$G$12 диапазон в котором мы будем суммировать, E6 – столбик в котором мы будем суммировать и E2:E3 диапазон в которые мы ввели критерии для суммирования. В итоге формула нашла 3 позиции на общую сумму 34.

Для получения аналогично результата вы можете воспользоваться также такими формулами:

Функция БДСУММ с одним текстовым критерием

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

=БДСУММ($B$6:$G$12;E6;C2:C3), как видим произошло только изменения диапазона критерия.

Суммирование по двум критериям по разным столбцам

Пример усложняется применением двух критериев, но ничего принципиально нового мы применять не будем, укажем текстовый критерий «с.Серово» и числовой критерий «>=10», оставив поле суммирование «Урожай», мы получим изменение формулы только по последнему аргументу, как результат. Теперь наша формула будет выглядеть так:

=БДСУММ($B$6:$G$12;E6;C2:E3), снова видите изменения только адреса диапазона критерия.

Альтернативный вариант можно получить с помощью функции СУММЕСЛИМН и так:

Суммирование по одному из двух условий в одном столбике

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

=БДСУММ($B$6:$G$12;E6;C2:C4), здесь снова изменяем диапазон критерия, но не в ширину, а в высоту.

Также заменителем, вы можете использовать сумму функции СУММЕСЛИ:

=СУММЕСЛИ(C7:C12;C3;E7:E12)+СУММЕСЛИ(C7:C12;C4;E7:E12).

Суммирование по одному из двух условий в двух разных столбиках

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

=БДСУММ($B$6:$G$12;D6;C2:D4), принцип формирования формулы сохраняется, окромя диапазона критерия, который включает в себя три строки: заголовок и два критерия.

Суммирование по двум текстовым критериям по двум столбикам

Использование результата формулы для получения критерия отбора и суммирования

А если вам очень интересно альтернативное решение вопроса, то тогда испробуйте вариант с функцией СУММЕСЛИ в таком виде:

Функция БДСУММ по трём критериям

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

Суммирование по текстовому критерию с учётом регистра

Как я упоминал ранее, функция БДСУММ может производить поиск не только с подстановочными символами, но и с учётом регистра букв, вот сейчас именно такой вариант. Для начала определим условие по отбору критерия, если встречается название «с.ИВАНОВО», заглавными, то мы производим суммирование, для определения этого критерия нам нужна формула:

=БДСУММ($B$6:$G$12;D6;$C$2:$C$3) Ну что же, я думаю, что функция БДСУММ в Excel, мною была описана деталь и во многих подробностях, поэтому вопросов будет мало, а пользы много. Если у вас возникли вопросы пишите комментарии, жду ваши лайки и отзывы. О других функциях вы можете ознакомиться в «Справочнике функций» на моем сайте.

«За деньги можно, конечно, купить очаровательного пса, но никакие деньги не смогут заставить его радостно вилять хвостом.
»
Д. Биллингс

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

Excel суммировать по условию — Офис Ассист

• При выполнении работы над целым столбиком заполненным данными, обязательно стоит вставить пустую строку под заголовками столбиков в указанном диапазоне критериев;
• Диапазон критериев не должен иметь пересечения со списком;
• Хотя данные для формирования диапазона условий вы можете помещать в любое место на листе, но тем не менее, помещать его после списка не следует, так как данные которые мы добавляем в список, будут вставляться в первую строку после списка и если в строке есть данные, то добавить новые не получится;
• Можно использовать для условий любой диапазон, который содержит, как минимум один заголовок и хотя бы одну из ячеек, которая расположена под заголовком и содержит в себе условие.

Оба фильтра выбирают из массива данные, соответствующие условиям отбора, и скрывают данные, которые не соответствуют заданным условиям. При этом расширенный фильтр имеет свои особенности отбора и обработки данных. Для лучшего понимания работы расширенного фильтра сравним его с работой автоматического фильтра.