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

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

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

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

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

Сделаем последующую таблицу критерий:

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

• 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 денька).

Вид начальной таблицы:

Вид таблиц критерий:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Бдсумм(данные;5;о4:р5),

мы найдем, на какую сумму Джен продала сияние для губ.

Рис. 4-2 Примеры внедрения функций баз данных

Эту же формулу также можно ввести в виде

Бдсумм(данные;«Сумма»;о4:р5).

Как видно из рис 4-2, Джен продала сияние для губ на сумму $5461,61.

ПРИМЕР 2. Какое среднее количество губной помады реализует Джен всякий раз в Восточном регионе?

Можно вычислить это значение, введя в ячейку N8 формулу

Дсрзнач(данные;4;о7:q)8)

Используя 4 в качестве значения параметра поля, мы указываем столбец Единицы, и спектр критериев O7:Q8 задает строчки базы данных, в которых Имя — Джен, значение поля Продукт — губная по­мада и значение поля Положение — восток.

Применение функции ДСРЗНАЧ гарантирует нам, что мы вычисляем среднее количе­ство проданной продукции для отмеченных строк. Как видно из рис. 4-2, в сред­нем Джен продавала 42,25 единицы губной помады за одну транзакцию в Вос­четком регионе.

ПРИМЕР 3. Какую сумму составляют реализации Эмили и реализации в Восточном регионе?

В ячейке N11 (рис. 4-2) мы можем вычислить суммарный размер продаж ($76156,48) тор­гового агента Эмили либо продаж, сделанных в Восточном регионе, ис­пользуя формулу

Бдсумм(данные;5;о10:р12).

Аспекты в спектре О10:Р12 указывают реализации в Восточном регионе либо торгового агента Эмили.

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

ПРИМЕР 4. На какую сумму продали губной помады Колин и Зарет в Восточном регионе?

Формула БДСУММ(данные;5;О13:Q15) в ячейке N14 вычисляет суммарный до­ход от реализации губной помады торговыми агентами Колин и Зарет ($1073,20) в Восточном регионе.

Направьте внимание, что O14:Q14 содержит аспекты, которые отбирают губную помаду, проданную в Восточном регионе агентом Колин, a O15:Q15 — губную помаду, проданную в Восточном регионе агентом Зарет. Вспомяните, что аспекты в различных строчках интерпретируются как ИЛИ.

ПРИМЕР 5. Сколько продаж губной помады осуществлено вне Восточного региона?

В ячейке N17 мы вычисляем полное количество транзакций по продаже губной помады (164) вне Восточного региона по формуле

Бсчёт(данные;4;о16:р17).

Используем функцию БСЧЁТ для решения данной задачки, поэтому что нам нужен таковой аспект, чтоб функция подсчитала число строк, со­держащих реализации губной помады и регионы, хорошие от Восточного.

Excel интерпретирует выражение <>восток в спектре критериев, как «не восток».

Потому что функция СЧЕТ считает числа, мы должны сослаться на столбец, содержащий числовые значения. Столбец 4 (Единицы) содержит чис­ла, потому мы указали его в формуле. Формула БСЧЁТ(данные;3;О16:Р17) верну­ла бы 0, потому что 3-ий столбец базы данных (столбец J рабочего листа) не со­держит числовых значений.

Естественно же, корректное значение воз­вратит и формула

Бсчёта(данные;3;о16:р17),

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

Сравните с функцией СЧЕТЗ, которая употребляется для подсчета количества непустых ячеек в интервале либо массиве.

ПРИМЕР 6. На какую сумму продала губной помады Джен в 2004г.?

Основная задачка в этом примере указать лишь реализации, осуществленные в 2004 г. Включив в одну строчку спектра критериев ссылку на поле Дата, и используя выражения >=1/1/2004 и <1/1/2005, мы охватываем лишь про­дажи 2004 г.

Таковым образом, введя в ячейку N19 формулу

БДСУММ(данныe;5;O18:R19),

мы найдем общую сумму продаж губной помады торговым агентом Джен ($1690,79) в период с 01.01.2004 по 01.01.2005.

ПРИМЕР 7. Сколько единиц продукта продано по стоимости не ниже $3,20?

Этот пример содержит вычисляемый аспект. Вычисляе­мый аспект отбирает строчки базы данных на основании того, поистине либо неверно значение вычисляемого условия для каждой строчки. В этом примере мы желаем отобрать строчки, для которых отношение Сумма/Единицы >=$3,20.

Запомните. При установке вычисляемого аспекта (рис. 4-3) заголовок в первой строке, выше вычисляемого аспекта, не должен быть заголовком столбца.

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

Таковым о­разом, для указания строк, для которых средняя стоимость выше либо равна $3,20, мы должны ввести =(L5/K5)>=3,2 в спектр критериев под заголовком, кото­рый не совпадает с заголовком столбца. Если 1-ая строчка данных не удо­влетворяет этому условию, вы увидите значение ЛОЖЬ в соответственной ячейке листа, но Excel продолжит обработку всех строк, для которых стоимость за единицу продукции выше либо равна $3,20.

Рис. 4-3 Пример вычисляемого аспекта

Введя в ячейку N22 формулу

БДСУММ(данные;4;О21:О22),

мы найдем полное количество проданного това­ра (1127) для которого стоимость выше либо равна $3,20. Направьте внимание, что ячейка О22 содержит формулу =(L5/K5)>=3,2.

ПРИМЕР 8. На какую сумму любой торговый агент продал продукт всякого вида?

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

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

Введем имя хоть какого торгового агента в ячейку Х26 и наименование про­дукции хоть какого вида в ячейку Y26 (рис. 4-4). Потом введем в ячейку Q25 формулу БДСУММ(данные;5;Х25:Y26), которая подсчитает общий доход от продаж ка­рандаша для глаз, выполненных Бетси (рис.4-4).

Дальше введем имя всякого торгового агента в спектр ячеек Q26:Q33 и заглавие всякого вида продукции в диа­пазон R25:V25.

Потом выделим спектр с таблицей данных (Q25:V33) и щел­кнем в меню Данные команду Таблица подстановки. В поле Подставлять значения по столбцам в укажем ячейку Y26, а в поле Подставлять значения по строчкам в — ячейку X26.

Приобретенный итог показан на рис. 4-4.

Рис. 4-4 Совместное внедрение таблицы подстановки с функцией БДСУММ

Любая запись в таблице под­становки вычисляет доход, приобретенный для различных композиций Имя/Про­дукт, потому что имена, обозначенные в таблице подстановки, помещаются в ячейку Х26, а виды продукции — в ячейку Y26. К примеру, мы обусловили, что Эшли продала губной помады на $3245,44.

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

Полезные ухищрения, применяемые при определении спектра критериев

Приведем несколько приемов, которые посодействуют нам установить соответственный спектр критериев.

Представим, заголовок столбца в первой строке диапа­зона критериев соответствует столбцу, содержащему текстовые данные (напри­мер, столбец Н):

*Эшли* показывает записи, содержащие строчку Эшли в столбце Н;

А?Х показывает записи столбца Н, начинающиеся с знака А и имеющие третьим эмблемой X (2-ой знак быть может хоть каким!);

<>*В* показывает записи столбца Н, которые не содержат знак В.

Если заголовок столбца в первой строке диапа­зона критериев соответствует столбцу, содержащему числовые значения (напри­мер, столбец I) то:

>100 показывает записи столбца I, содержащие значение, превышающее 100;

<>100 показывает записи столбца I, содержащие значение, не равное 100;

>=1000 показывает записи столбца I, содержащие значение не меньше 1000.

ПРИМЕР 9. В базе данных, для каждой торговой транзакции указаны доход, дата и код продукта. Есть ли легкий метод узнать доход от транзакции, зная дату и код продукта?

Файл dget.xls (рис. 4-5) содержит базу данных, в которой указаны доход, дата и код продукта для набора транзакций продаж.

Для определения дохода от транзакции при узнаваемых дате транзак­ции и коде продукта используем функцию БИЗВЛЕЧЬ.

Синтаксис функции БИЗВЛЕЧЬ последующий:

БИЗВЛЕЧЬ(база_данных;поле#;аспект).

Для обозначенных значений база_данных (спектр ячеек) и поле# (номер столбца в спектре, начиная с самого левого) функция БИЗВЛЕЧЬ возвращает запись столбца поле#, удовлетворяющую аспекту.

Если не окажется записи, удовлетворяющей аспекту, функция БИЗВЛЕЧЬ возвращает ошибку #ЗНАЧ (#VALUE).

Если аспекту удовлетворяет наиболее одной записи, функция БИЗВЛЕЧЬ возвра­щает ошибку #ЧИСЛО! (#NUM!).

Пусть наша база данных расположена в диапазо­не ячеек B7:D32 (рис. 4-5). Представим, что мы желаем выяснить доход, приобретенный в итоге про­дажи продукта с кодом 62426 от 09.01.2006. Если в обозначенный денек была выполнена только одна транзакция такового вида, то формула (введенная в ячейку G9) БИ3BJIEЧЬ(B7:D32;1;G5:H6) вернет доход от данной транзакции, равный $980.

Рис. 4-5 Пример функции БИЗВЛЕЧЬ

Обратите вни­мание, мы употребляли 1 для аргумента поле#, потому что заголовок Доход разме­щен в первом столбце базы данных.

Спектр критериев G5:H6 гарантирует, что мы отберем транзакцию, содержащую код продукта 62426 за 09.01.2006.

(пункт 4.1. Получение итоговых данных)

Какое количество блеска для губ продала Зарет в протяжении 2004 и 2005 гг. (файл s91_l_3.xls)?

Сделайте таблицу подстановки, которая содержит суммарный доход всякого торгового агента и количество проданного им продукта (файл s91_l_3.xls).

Сколько блеска для губ продала Колин вне Западного региона (файл s91_l_3.xls)?

Файл s91_4.xls содержит для избранных домов последующую ин­формацию:

число ванных комнат;

число спальных комнат.

Используя эту информацию, ответьте на последующие вопросцы.

4.1. Какова средняя стоимость всех домов, имеющих полное количество ван­ных и спальных комнат >= 6?

4.2. Сколько продается домов с полным количеством ванных и спальных комнат <=5 и ценой выше $300000?

4.3. Сколько домов имеют по очень мере 3 ванных комнаты, но суммарное количество ванных и спальных комнат <=6?

4.4. Какова наивысшая стоимость для домов с площадью не наиболее 3000 квад­ратных футов и полным количеством ванных и спальных комнат <=6? (Совет: используйте функцию ДМАКС для решения данной задачки.)

Здесь вы сможете бросить комментарий к избранному абзацу либо сказать о ошибке.

Функции СЧЁТ и СУММ в Excel

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

Для подсчета количества ячеек, которые содержат числа, используйте функцию СЧЁТ (COUNT).

СЧЕТЕСЛИ

Для подсчета ячеек по одному аспекту (к примеру, больше 9), используйте функцию СЧЕТЕСЛИ (COUNTIF).

СЧЁТЕСЛИМН

Чтоб подсчитать ячейки, основываясь на нескольких аспектах (к примеру, содержащие “green” и больше 9), применяйте функцию СЧЁТЕСЛИМН (COUNTIFS).

Для суммирования спектра ячеек используйте функцию СУММ (SUM).

СУММЕСЛИ

Чтоб суммировать значения ячеек на базе 1-го аспекта (к примеру, больше 9), используйте функцию СУММЕСЛИ (SUMIF). В этом случае для проверки условия и суммирования употребляется один столбец, потому в функции довольно заполнить всего два аргумента:

Чтоб суммировать значения ячеек на базе 1-го аспекта (к примеру, “green”), также используйте функцию СУММЕСЛИ (SUMIF). В этом случае для проверки условия и суммирования употребляются различные столбцы, потому в функции необходимо заполнить три аргумента, крайний – это спектр для суммирования.

СУММЕСЛИМН

Для суммирования значений ячеек на базе нескольких критериев (к примеру, “blue” и “green”), используйте функцию СУММЕСЛИМН (SUMIFS). 1-ый аргумент – это спектр для суммирования.

Примечание: Аналогичным образом можно применять функцию СРЗНАЧЕСЛИ (AVERAGEIF) и СРЗНАЧЕСЛИМН (AVERAGEIFS), чтоб высчитать среднее значение ячеек на базе 1-го либо нескольких критериев.