Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать записи между двумя датами с критериями соответствия в Excel?

Как суммировать значения между двумя датами

Представьте себе, что мы работаем в торговой компании. Руководитель поручил нам подсчитать продажи за последние 15 дней. За определенный период времени.

Давайте посмотрим, как это делается.

Данные о продажах приводятся в таблице для каждого дня. Для выполнения задания требуется SUMMESLYMN.

Как работает функция СУММЕСЛИМН?

Функция СУММЕСЛИМН в Excel используется для суммирования значений в соответствии с различными критериями.

Синтаксис этой функции выглядит следующим образом

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

  • Summarize_range — диапазон данных, на котором должны быть рассчитаны условия заданных критериев для обобщения данных;
  • Range_conditions1, condition1 — диапазон, на котором проверяется первое условие функции. Criteria_range1(range_conditions1) и criteria1(condition1) образуют пару, определяющую диапазон, к которому применяется условие при поиске. Соответствующие значения ячеек в этом диапазоне суммируются в аргументе sum_range (диапазон_сумм).
  • [range_condition2], condition2] — (необязательно) — второй диапазон критерия, для которого рассчитываются данные;

Формула для суммирования значений между двумя датами

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

Как суммировать значения между двумя датами

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

После ввода этой формулы функция возвращает значение 559 134₽. Это значение отражает продажи в период с 1 июня по 15 июня 2018 года.

Для проверки выберите диапазон ячеек с данными о продажах, которые соответствуют этим датам, и в правом нижнем углу вы увидите сумму чисел

Как суммировать значения между двумя датами

Как работает эта формула

В нашей формуле мы использовали логические операторы в функции SUMMESLIMN, которые помогают нам суммировать данные по заданному диапазону дат.

Читайте так же:
Как преобразовать время в десятичное за 24 часа в Excel?

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

Как суммировать значения между двумя датами

  • Сначала указываем диапазон с данными о продажах (B2:B28), из которого нужно выбрать значения, которые мы будем добавлять
  • Далее указываем диапазон с данными, к которым будет применена проверка условия. В нашем случае это диапазон с датами (A2:A28)
  • Далее указываем условие относительно диапазона с датами, по которому формула должна определить, какие данные нужно добавить. Задаем первое условие, что дата должна быть больше или равна 01.06.2018
  • На последнем шаге задаем второе условие диапазону с датами (A2:A28), по которому формула должна добавить данные из периода меньше или равного 15.06.2018.

Соответственно, функция суммирует значения с 1 по 15 июня 2018 года.

Как суммировать значения между двумя динамическими датами

В предыдущем примере мы видели, как суммировать данные между двумя определенными датами, но что если мы хотим суммировать данные за последние 7 дней до даты открытия файла, например, если мы не хотим каждый раз вводить в формулу определенные даты?

Вот формула, которую мы можем использовать:

Как работает эта формула

В формуле выше мы используем функцию TODAY для автоматического вычисления текущей даты.

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

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

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

Любые вопросы, касающиеся примера, задавайте в комментариях.

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

Суммирование по множеству условий функцией БДСУММ (DSUM)

Существует несколько способов выборочного подсчета по нескольким условиям в больших таблицах: фильтры, сводные таблицы, функции СУММЕСЛИ и СУММЕСЛИН и т.д.

Читайте так же:
Как перечислить все свойства файла Excel на листе?

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

=БДСУММ( Исходные_данные ; Столбец_результата ; Диапазон_условий )

  • Source_data — диапазон, включающий таблицу с данными, которые мы анализируем, и строку заголовка.
  • Result_column — имя (из заголовка таблицы) или порядковый номер столбца, для которого мы хотим просуммировать данные.
  • Condition_range — диапазон, содержащий имена столбцов и условия на них.

База данных для анализа

С помощью команды Compose — Format as Table или нажатием Ctrl + T преобразуйте ее в «умную» таблицу, чтобы на нее можно было ссылаться в будущем. На появившейся вкладке Design назовите таблицу — например, DatabaseData.

Простая сумма по одному условию

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

Сумма по одному условию функцией БДСУММ

Обратите внимание на следующие пункты:

  • Не достаточно просто написать имя умной таблицы БазаДанных в первом аргументе, т.к. в этом случае ссылка не включает шапку, а для функции БДСУММ она необходима. Поэтому к имени добавляется тег [#Все] или, в английском варианте [#All].
  • Столбец, по которому нужно просуммировать данные можно задать либо названием («Стоимость»), либо номером (было бы 5).
  • Название столбца в желтом диапазоне условий должно один-в-один совпадать с названием в исходной таблице.
  • Функция БДСУММ не различает регистр символов (добрый = ДОБРЫЙ = Добрый = ДоБрЫй и т.д.)
  • Чтобы критерий в желтой ячейке А2 Excel не начал понимать как формулу (т.к. формулы обычно начинаются со знака равно) можно использовать текстовый формат или просто начать ввод в ячейку с апострофа:

Приблизительный и точный текстовый поиск

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

Точный и приблизительный поиск

  1. Для поиска точного совпадения используйте конструкцию ‘=’ (апостроф и знак равенства).
  2. Если вы хотите найти подстроку, т.е. все ячейки, содержащие нужное значение, окружите ее звездочками. В нашем случае все абаканские варианты (с «г», без «г», с пробелами перед/после и т.д.) будут объединены.
  3. Если ввести только значение без знаков равенства и звездочек, будут найдены и просуммированы все строки, содержание которых начинается с указанного значения, т.е. со звездочкой в конце.

Несколько условий со связками «И» — «ИЛИ»

Чтобы сложить несколько условий, связанных с оператором AND, ячейки с этими условиями должны находиться в одной строке. Например, если вы хотите сложить все продажи «Фанты» в Абакане (как бы это ни было написано), то это будет выглядеть следующим образом

Сумма по двум условиям с И

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

Несколько условий с ИЛИ

И, конечно, вы можете объединить эти два подхода, сочетая в одном запросе условия AND и OR одновременно:

Несколько условий с И и ИЛИ одновременно

Это сумма продаж «Фанты» и «Burn» в Абакане и Дубинине.

Суммирование по интервалу дат

Подсчет можно легко установить на основе интервала дат в диапазоне условий. Если интервал ограничен с обеих сторон, вам нужно будет задать два условия, связанные логическим «И» — поэтому вам придется написать условия в одной строке, т.е. добавить еще один столбец с тем же именем Transaction Date:

Суммирование по интервалу дат

В данном случае рассчитывается объем продаж Fanta за 2016 год и продаж Fructaim в течение 2016 года.

Условия для чисел

Для отбора по числовым критериям можно смело использовать обычные знаки неравенств >, <, >=, <=, как в обычных формулах Excel. Мы можем суммировать стоимость всех сделок с колой между 500 и 600, если, например, нам нужно суммировать все продажи любого вида колы:

Сумма по интервалу чисел

Исключения «все кроме»

Чтобы исключить записи по определенному параметру, используйте символ «*>», который в синтаксисе Excel означает «не равно». Если предположить, что нам нужно просуммировать все данные «Фанты», кроме Самары, и данные «Кваса», кроме Пензы, то это будет выглядеть следующим образом:

Исключения

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

Несколько исключений

Заключение

Надеюсь, теперь вы поняли, что функция BDSUM — это очень хороший инструмент и часто лучшая альтернатива классическим функциям выборочного вычисления, таким как SUMIF и SUMIFS. Более того, в той же категории базы данных вы можете найти ее «друзей», которые вычисляют больше, чем просто сумму:

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector