Суммирование по множеству условий функцией БДСУММ (DSUM)
Для выборочного подсчета по нескольким условиям в больших таблицах можно использовать несколько способов: фильтры, сводные таблицы, функции СУММЕСЛИ и СУММЕСЛИМН и т.д.
Еще одним, относительно экзотическим, но весма мощным инструментом является функция БДСУММ (DSUM) из категории Работа с базой данных (Database). При внешней простоте, она позволяет гибко фильтровать списки по нескольким сложным и связанным между собой условиям и подсчитывает сумму найденных записей по заданному столбцу. Синтаксис функции таков:
=БДСУММ(Исходные_данные; Столбец_результата; Диапазон_условий)
где
- Исходные_данные - диапазон, включающий в себя таблицу с данными, которые мы анализируем и строкой заголовка.
- Столбец_результата - название (из шапки таблицы) или порядковый номер столбца, по которому нужно просуммировать данные.
- Диапазон_условий - диапазон, содержащий названия столбцов и условия по ним.
Чтобы удобнее было ссылаться эту таблицу в будущем, конвертируем ее в "умную" командой Форматировать как таблицу на вкладке Главная (Home - Format as Table) или сочетанием клавиш Ctrl+T. На появившейся затем вкладке Конструктор (Design) зададим ей имя - например БазаДанных.
Простая сумма по одному условию
Начнем с простого случая. Допустим, нам нужно просуммировать стоимость по всем сделкам сока Добрый. Это будет выглядеть следующим образом:
Обратите внимание на следующие моменты:
- Не достаточно просто написать имя умной таблицы БазаДанных в первом аргументе, т.к. в этом случае ссылка не включает шапку, а для функции БДСУММ она необходима. Поэтому к имени добавляется тег [#Все] или, в английском варианте [#All].
- Столбец, по которому нужно просуммировать данные можно задать либо названием ("Стоимость"), либо номером (было бы 5).
- Название столбца в желтом диапазоне условий должно один-в-один совпадать с названием в исходной таблице.
- Функция БДСУММ не различает регистр символов (добрый = ДОБРЫЙ = Добрый = ДоБрЫй и т.д.)
- Чтобы критерий в желтой ячейке А2 Excel не начал понимать как формулу (т.к. формулы обычно начинаются со знака равно) можно использовать текстовый формат или просто начать ввод в ячейку с апострофа:
Приблизительный и точный текстовый поиск
При необходимости, можно легко реализовать не только точный, но и приблизительный текстовый поиск. Посмотрите внимательно на следующие варианты суммирования выручки по городу Абакан и разницу между ними:
- Если нужен поиск точного соответствия, то используем конструкцию '= (апостроф и знак равно).
- Если нужен поиск подстроки, т.е. всех ячеек, которые содержат нужное значение, то его надо заключить в звездочки. В нашем случае будут просуммированы все варианты Абакана (с "г.", без "г.", с пробелами перед-после и т.п.)
- Если просто ввести значение без равно и звездочек, то будут найдены и просуммированны все строки, где содержимое начинается с указанного значения, т.е. это равноценно звездочке в конце.
Несколько условий со связками "И" - "ИЛИ"
Если нужно просуммировать данные по нескольким условиям, связанным друг с другом логическим оператором И (AND), то ячейки с этими условиями должны быть в одной строке. Например, если нужно просуммировать все продажи Fanta по Абакану (в любом виде его написания), то это будет выглядеть так:
Если же нужно связать несколько условий логическим оператором ИЛИ (OR), то их нужно расположить в разных строчках. Например, если нужно просуммировать деньги по всем вариантам написания "города на Неве", коих великое множество:
И конечно же, можно комбинировать оба подхода, сочетания в одном запросе условия со связками И и ИЛИ одновременно:
В этом случае вычисляется сумма продаж Fanta в Абакане и Burn у Дубинина.
Суммирование по интервалу дат
В диапазоне условий можно легко задать интервал дат для подсчета. Если интервал ограничен с двух сторон, то нужно будет задать два условия, связанных между собой логическим "И" - поэтому придется писать условия в одной строке, т.е. добавить еще один столбец с тем же названием Дата сделки:
В данном случае вычисляется сумма продаж Fanta за 2016 год и Фруктайм до 2016 года.
Условия для чисел
Для отбора по числовым критериям можно смело использовать обычные знаки неравенств >, <, >=, <= как и в обычных формулах Excel. Например, если нам нужно просуммировать все продажи любых видов колы, где сумма сделки была в интервале 500-600:
Исключения "все кроме"
Если нужно при суммировании исключить записи по какому-либо параметру, то можно использовать символы "<>" обозначающие "не равно" в синтаксисе Excel. Допустим, нам нужно просуммировать все данные по Fanta кроме Самары и по Квасу кроме Пензы - это будет выглядеть так:
Обратите внимание, что если нужно просуммировать Fant'у И Квас по всем городам, кроме Самары И Пензы, то конструкция таблицы условий будет уже немного другая - для каждого товара нужно исключить каждый город:
Заключение
Надеюсь, вы уже поняли, что функция БДСУММ является очень неплохим инструментом и, зачастую, более удобной альтернативой классическим функциям выборочного подсчета типа СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS). Кроме того, в той же категории Работа с базой данных (Database) можно найти ее "подруг", вычисляющих не только сумму:
- БСЧЁТ (DCOUNTA) - количество непустых ячеек (в нашем случае - строк заказов)
- ДМИН (DMIN) - минимальное (например, худшая сделка)
- ДМАКС (DMAX) - максимальное (лучшая сделка)
- ДСРЗНАЧ (DAVERAGE) - среднее арифметическое (например, средний чек)
Ссылки по теме
- Выборочные вычисления по одному или нескольким условиям
- Расширенный фильтр и немного магии
- Суперфильтр на VBA
почему два раза начинается или тут какая та ошибка
Можно ли в одной ячейке сделать суммирование-2D, чтобы собрать сумму ремонта по 2017 году (2018, 2019 и т.д.), имея данные за несколько лет, разбитых помесячно?
в A1 оставить пустым, а в a2
Вычисления можно разносить по строкам и столбцам, аналогично обычному применению, но с единственным отличием., что заголовок критерия всегда остается не заполненным, несмотря на то что указывается
Способ интересен тем что комбинировать условие может оказаться проще, а также появляются дополнительные возможности, брать значения с определенных строк по номеру, учитывать длину текстовых значений , использовать массивы значений .... любые фантазии.
Хотел сказать, что последний пример "если нужно просуммировать Fant'у И Квас по всем городам, кроме Самары И Пензы" работает в такой конструкции только.
У вас сумма по Фанте и Квасу, не исключая города.
В самом последнем примере
Может ли быть для БДСУММ в качестве критерия не только дата, но и время суток (часы : минуты)?
У меня данный критерий по такому типу не сработал: