Бдсумм в excel как сделать

Бдсумм в excel как сделать

В этой статье описаны синтаксис формулы и использование функции БДСУММ в Microsoft Excel.

Описание

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

Синтаксис

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

Аргументы функции БДСУММ описаны ниже.

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

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

Условия — обязательный аргумент. Диапазон ячеек, который содержит задаваемые условия. В качестве аргумента «условия» можно использовать любой диапазон, который содержит хотя бы один заголовок столбца и хотя бы одну ячейку с условием, расположенную под заголовком столбца.

Замечания

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

Например, если диапазон G1:G2 содержит заголовок столбца «Доход» в ячейке G1 и значение 10 000 ₽ в ячейке G2, можно определить диапазон «СоответствуетДоходу» и использовать это имя как аргумент «условия» в функции баз данных.

Несмотря на то, что диапазон условий может находиться в любом месте листа, не следует помещать его под списком. Это связано с тем, что данные, добавляемые в список, вставляются в первую строку после списка. Если эта строка уже содержит данные, Microsoft Excel не сможет добавить новые данные в список.

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

Чтобы выполнить операцию над целым столбцом базы данных, вставьте пустую строку под строкой заголовков столбцов в диапазоне условий.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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

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

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

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

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

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

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

• 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 дня).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задачи

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

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

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

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

Суммирование по нескольким условиям в Excel

Суммирование в Excel по нескольким условиям на практике.

Источник проблемы и цель задачи.

При работе в Excel встречаются ситуации, когда требуется провести суммирование не по каждой позиции, а только по тем, которые соответствуют заданному признаку. Например, вычислить количество составов, отправленных в указанный город, найти наличие на складе только выбранного товара, рассчитать затраты по конкретной статье расходов и так далее. Позиции для такого суммирования выбираются либо по соответствия одному условию, либо по одновременному соответствую нескольким условиям. Сравните две задачи:

1. Найти количество вагонов, отправленных в город N.

2. Найти количество вагонов для сыпучих грузов, с автоматической разгрузкой, отправленных в город N во втором квартале текущего года.

В первом случае условие только одно. Такое суммирование по условию в Excel несложно, с расчетами справится рядовая формула массива или функция СУММЕСЛИ. Эти способы подробно рассмотрены в прошлых материалах.

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

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

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

Работаем с СУММПРОИЗВ.

Вначале рассмотрим функцию СУММПРОИЗВ в различных ситуациях.

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

=СУММПРОИЗВ((«Диапазон проверки 1 условия»=«1 условие»)*(«Диапазон проверки 2 условия»=«2 условие»)*…* «диапазон ячеек для суммирования»).

Естественно, в реальности кавычки не ставим. Исключение – ситуация, когда одно из условий содержит явно заданный текст.

Сразу приведем типичную задачу

Имеется отчет следующего вида:

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

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

• Диапазон для суммирования можно указать без скобок
• Условия, напротив, заключаем в круглые скобки.
• Явный текст условий пишем в кавычках
• Вместо явного текста условий можно указать ячейки, содержащие данный текст

Если поместить нужные критерии в ячейки J1:J3, то формулу для расчета, указанную выше, можно задать так:

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

Чтобы этого не случилось, то, если требуется проверить наличие теста в ячейках, применяйте комбинацию ЕЧИСЛО и ПОИСК. В предыдущем случае это выглядит так

Вначале функция ПОИСК ищет вхождение нужного текста в содержимое ячейки. Если такое вхождение найдено, другими словами – если текст ячейки содержит нужное нам слово или строку, то функция ЕЧИСЛО выдаст результат ИСТИНА. В этом случае подстановочные знаки не требуются, н могут понадобится, если известны начало и конец строки в ячейке, но неизвестна середина этой строки.

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

Та же задача решается и по-другому. Дело в том, что знак умножения в функции СУММПРОИЗВ работает как логическое ИЛИ. А вот знак плюс срабатывает как логическое И.

Ситуация 3. Критерии для суммирования требуется отследить как в одной колонке, так и в нескольких. В качестве примера рассмотрим ситуацию, когда требуется найти итоговое количество для рубашек и брюк с ценой свыше 5000. Такая ситуация представляет собой общий случай первых двух. Для нахождения нужных критериев в одной колонке используем вторую формулу, описанную выше, дальше же поступаем так, как описано в ситуации 1.

Используем БДСУММ.

Теперь поговорим о функции БДСУММ.

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

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

А- таблица для обработки

В – колонка обрабатываемой таблицы, по которой проводится суммирование. Допускается как заголовок нужного столбца, так и порядковый номер в таблице.

С – проверочный диапазон. Содержит заголовки таблицы. Для каждого заголовка задается условие, которое проверяется при расчете в колонке с указанным заголовком.

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

• Условия задаются указанием критериев, с которыми должны совпадать данные в заданном столбце.
• Текстовые значения задаем в виде формулы явного сравнения. Если требуется найти все донные по товару с конкретным названием, указываем его формулой =«=товар». Слово товар в формуле меняем на нужное наименование конкретной позиции. Аналогично, если точное название неизвестно, то в этой формуле добавляем знак звездочки. В частности, если название товара начинается с слова «весы», то указываем это так:=«=весы*»
• Наименование столбца с условием должно совпадать с столбцами таблицы значений.
• При необходимости допускается указание несколько критериев, одновременно совпадающих по разным столбцам.
• Если необходимо в одной колонке указать несколько разных критериев, их располагают построчно (логическое ИЛИ).
• Если критерием служит некий диапазон значений, или ячейка из колонки должна соответствовать одновременно нескольким условиям, то данную колонку в диапазоне проверки дублируют, указывая в каждой копии в одной строке отдельный вариант критерия (логическое И).
• В каждой строке задается отдельное условие. При этом допускается построчное совпадение условий в одной колонку для разных условий в другой колонке диапазона проверки.
• Допускается применение в условиях подстановочных символов.
• Допускается в качестве условий проверки применять логические функции, функции поверки свойств и их комбинации. В качестве поверяемой ячейки указывается адрес проверяемой ячейки нужной колонки таблицы. В таком случае заголовок диапазона проверки не должен совпадать с заголовками полей исходной таблицы.
• Порядок колонок в диапазоне проверки не зависит от порядка колонок в таблице со значениями.
• Колонки исходной таблицы в формуле называются полями, а строки – записями Учитывайте это при изучении функции

В качестве иллюстрации приведем несколько примеров, используя таблицу «отчет».

1. необходимо найти общее количество брюк и рубашек. В этом случае в диапазоне проверки используем единственную графу «наименование номенклатуры», в которую внесем нужные наименования. Учитывая, что таблица с данными располагается в диапазоне A1:G186, диапазон проверки лежит в диапазоне J1:J3, а заголовок столбца «количество» находится в ячейке D1, получаем следующую формулу.

Тот же результат можно получить, если вместо D1 ввести цифру 4, так как «количество» – четвертый столбец в таблице. На рисунке ниже приведены обе формулы и результаты вычислений.

2. Предположим, что точное название товара неизвестно. Возможно, в колонке «наименование номенклатуры» дополнительно указан цвет товара, его код или что-то еще. Применим для условий подстановочные знаки в виде звездочек. Расположив диапазон проверки в ячейках J10:J12 , получаем формулу

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

3. Усложним задачу, записав предыдущее условие в виде формулы. Применим комбинацию функций ИЛИ, ЕЧИСЛО, ПОИСКПОЗ, как было указано выше .

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

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

В диапазоне условий пропишем в графе «проверка наименования» построчно две формулы

В графе «тест по датам», учитывая, что номера месяцев первого полугодия меньше 7, а номера месяцев 2 полугодия больше 6, указываем напротив предыдущих условий соответствующие им формула проверки дат

5. Найдем количество по рубашкам с ценой в диапазоне от 3000 до 7000, с учетом того, что нас интересуют именно рубашки. В диапазон проверки вносим графу «наименование номенклатуры» и указываем в ней формулу =«=рубашка». С ценами сложнее. Для их проверки необходимо использовать логическое И, так как цена должна одновременно быть и больше 300и меньше 7000. Поэтому в диапазон проверки добавляем две колонки «Цена». В первой из них указываем:

>3000, а во второй <7000. При размещении диапазона проверки по адресу J1:L1 конечная формула будет выглядеть так

Подведем итог.

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

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

На этом наше занятие подошло к концу. Желаем всем хорошего дня и успешной работы. Файл исходной таблицей для самостоятельной тренировки по материалу урока доступен по ссылке ниже.