Excel функция БДСУММ (DSUM) – Info Tech Steps

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

Excel 2007 . .

excel-2007 Excel , . , .

= (_; ; )

, :

_  , – ;

  , ;

  , .

,  ” “.

1.

 «» ( , ,  ” “) . , , . «» – ? «_» .

2.

, 8 000 . . .

!    ,   , . ,  «, .»,   «, .».

( )  !

3.

:

, ,  .

 « ». ()  «»  « »,  « »   .  .

4.

 « ».   «_»  .  «»  , .  D1 –  «».  «»  , . :

:

 .  «»  .

pdf – : http://yadi.sk/d/MWHfN6mtJQugP

     

banner_sendsay3.png

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

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

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

Пример 1.

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

таблица условий.

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

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

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

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

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

БДСУММ.

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

Задачи

Предположим, что в диапазоне 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″)

Возвращаемое значение

Функция БДСУММ возвращает числовое значение.

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

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

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

Пример 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 для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

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

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

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

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

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

Примечания:

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

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

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

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

Пример

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

Дерево

Высота

Возраст

Урожай

Доход

Высота

=”=Яблоня”

>3

<5

=”=Груша”

Дерево

Высота

Возраст

Урожай

Доход

Яблоня

18

20

14

105 ₽

Груша

12

12

10

96 ₽

Вишня

13

14

9

105 ₽

Яблоня

14

15

10

75 ₽

Груша

9

8

8

77 ₽

Яблоня

8

9

6

45 ₽

Формула

Описание

Результат

=БДСУММ(A4:E10;”Доход”;A1:A2)

Общий доход от яблонь (строки 5, 8 и 10).

225 ₽

=БДСУММ(A4:E10;”Доход”;A1:F3)

Общий доход от яблонь высотой 3–5 метров и всех грушевых деревьев (строки 6, 8 и 9).

248 ₽

К началу страницы

Нужна дополнительная помощь?

Тип функции

  • Функция рабочего листа (WS)

Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL

​Смотрите также​ 0 соответственно и​ с учетом установленных​ отбора значений (все​ массива.​ 0*1*1 = 0​ функция БДСУММ извлекает​ от других слов,​ будет суммировать все​из поиска исключаются​

​Проданное количество​ превышает установленный уровень.​ в ячейке B1.​ 1048576 значений:​ 4-х задач является​D14:E15​ этих похожих функций​

excel-dvojnoe-uslovie_1.png

Источники

  • https://excel2.ru/articles/funkciya-summeslimn-slozhenie-s-neskolkimi-kriteriyami-v-ms-excel-chast-2uslovie-i-summeslimn
  • https://excelhack.ru/kak-summirovat-znacheniya-mezhdu-dvumya-datami/
  • https://exceltable.com/funkcii-excel/primery-funkcii-summeslimn
  • https://naprimerax.org/posts/68/funktciia-summesli-v-excel
  • https://mir-tehnologiy.ru/summeslimn-v-excel-primery/
  • https://www.planetaexcel.ru/techniques/2/167/
  • https://excelpedia.ru/formuli-v-excel/summy-s-datami
  • https://my-excel.ru/excel/summesli-v-excel-po-dvum-kriterijam.html
Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...