Excel суммирование по месяцам
Как суммировать данные по дням недели / месяцу / кварталу / году в Excel?
Допустим, вам нужно подсчитать ежемесячные итоги на основе всех заказов на покупку, выполненных в прошлом году. По сравнению с утомительным суммированием каждого ежемесячного итога по одному с помощью формул, есть несколько уловок, которые помогут вам легко рассчитать данные по будням, месяцам, кварталам или годам в Excel.
Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!
Суммирование данных за месяц / квартал / год с помощью сводной таблицы
Этот метод говорит о суммировании данных по месяцам, кварталам или годам с помощью сводной таблицы в Excel. См. Подробные инструкции следующим образом:
1. Выберите исходные данные, которые вы будете суммировать по месяцам / кварталам / годам, и нажмите Вставить > PivotTable (или Вставить > PivotTable > PivotTable). Смотрите скриншот:
2. В открывшемся диалоговом окне «Создание сводной таблицы» укажите место, в которое вы поместите созданную сводную таблицу, и щелкните значок OK кнопка. Смотрите скриншот выше:
3. Теперь справа от листа открывается панель «Поля сводной таблицы». Перетащите поле «Дата» (поле, по которому вы будете суммировать) в Ряды раздел и перетащите поле Сумма (поле, которое вы суммируете) в Ценности раздел. См. Снимок экрана ниже:
Внимание: Помимо перетаскивания, вы также можете щелкнуть правой кнопкой мыши поле Дата и выбрать Добавить в метки строк в контекстном меню, щелкните правой кнопкой мыши поле Сумма и выберите Добавить к значениям из контекстного меню. Смотрите скриншот выше:
4. Теперь перейдите к сводной таблице, щелкните правой кнопкой мыши любую дату в заголовки строк столбец и выберите группы из контекстного меню. Смотрите скриншот ниже:
5. В открывшемся диалоговом окне «Группировка» щелкните, чтобы выделить критерий, по которому вы будете суммировать By и нажмите OK кнопка. В нашем случае мы нажимаем, чтобы выделить Месяцев. Смотрите скриншот выше:
На данный момент мы создали сводную таблицу и суммируем суммы по месяцам, как показано ниже:
Суммируйте данные по дням недели / месяцам / годам с помощью Kutools for Excel
Вы могли заметить, что сводная таблица не может суммировать данные по дням недели. Не волнуйтесь! Этот метод представит утилиты Kutools for Excel для простого суммирования данных по дням недели, месяцам или годам следующим образом:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Выберите ячейки даты, по которым вы будете суммировать данные, и нажмите Кутулс > Формат > Применить форматирование даты.
2. В открывшемся диалоговом окне «Применить форматирование даты» щелкните, чтобы выделить форматирование даты, при котором дата отображается только в виде месяца, дня недели или года в зависимости от ваших потребностей, а затем щелкните значок Ok кнопка. Смотрите скриншот выше:
В нашем случае мы щелкаем, чтобы выделить формат даты «среда».
3. Продолжайте выбирать ячейки даты и нажмите Кутулс > К фактическому для преобразования дат в текст названий дней недели.
4. Выберите диапазон данных, который вы будете суммировать по дням недели, месяцу или году, и нажмите Кутулс > Content > Расширенные ряды комбинирования.
5. В открывшемся диалоговом окне «Объединить строки на основе столбца» выполните следующие действия:
(1) Выберите столбец Дата, по которому вы будете суммировать, и нажмите Основной ключ кнопка;
(2) Выберите столбец Сумма, который вы будете суммировать, и нажмите Рассчитать > Сумма.
(3) Задайте правила объединения для других столбцов и нажмите кнопку Ok кнопку.
Теперь вы увидите, что диапазон данных суммирован по дням недели, месяцам или годам в зависимости от ваших настроек. См. Снимок экрана ниже:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демо: суммирование данных по дням недели / месяцам / кварталам / годам в Excel
Суммируя недели, вы можете использовать формулу, основанную на функции СУММЕСЛИМН. В показанном примере, формула в F4 является:
Функция СУММЕСЛИМН может суммировать диапазоны на основе нескольких критериев.
В этой задаче мы настроим СУММЕСЛИМН подводить суммы по неделям, используя два критерия: (1) сроки больше или равны дате в колонке Е, (2) дата меньше, чем дата в колонке Е плюс 7 дней:
Когда эта формула копируется вниз, СУММЕСЛИМН генерирует сумму за каждую неделю.
Даты в колонке Е являются понедельниками. Первая дата жестко закодирована, а остальные понедельники рассчитываются с помощью простой формулы:
Сумма по будням
=СУММПРОИЗВ((ДЕНЬНЕД ( даты ) = Номер_Дня) * значения )
Подводя данные по будним дням (т.е. сумма по понедельникам, вторникам, средам, четвергам и пятницам), вы можете использовать функцию СУММПРОИЗВ вместе с функцией ДЕНЬНЕД.
В показанном примере, формула в H4 является:
СУММПРОИЗВ вместо СУММЕСЛИ
Вы можете спросить , почему мы не используем СУММЕСЛИ или СУММЕСЛИМН функцию? Это очевидный способ подвести отчет по дням недели. Тем не менее, без добавления вспомогательного столбца со значениями будних дней, нет никакого способа , чтобы создать критерии для СУММЕСЛИ, который принимает во внимание рабочие дни.
Вместо этого мы используем удобную функцию СУММПРОИЗВ, которая обрабатывает массивы изящно, без необходимости использовать Ctrl + Shift + Enter.
Мы используем СУММПРОИЗВ только с одним аргументом, который состоит из этого выражения:
Работая изнутри, функция ДЕНЬНЕД конфигурируется с дополнительным аргументом 2, что приводит к его рассчитать номера 1-7 за дни, с понедельника по воскресенье, соответственно. Это не обязательно, но это делает ему легче перечислить дни в порядке и забрать номера в столбце G в определенной последовательности.
ДЕНЬНЕД оценивает каждое значение в указанном диапазоне дат «» и рассчитывает число. Результатом является массив следующим образом:
Числа, рассчитанные ДЕНЬНЕД затем сравнивают со значением в G4, которое равно 1.
Результатом является массив истина/ложь значений.
Затем этот массив умножается на значения в названном «АМТС» диапазоне. СУММПРОИЗВ работает только с числами (не текстом или булевыми значениями), но математические операции автоматически преобразуют ИСТИНА/ЛОЖЬ значения в единицы и нули, так что мы имеем:
С помощью всего этого одного массива в процессе СУММПРОИЗВ суммирует элементы и рассчитывает результат.
Эта функция поможет подвести итоги по месяцу при помощи функции КОНМЕСЯЦА. В примере формула выглядит так:
Функция СУММЕСЛИМН может суммировать диапазоны на основе нескольких критериев.
В этом случае мы устанавливаем СУММЕСЛИМН суммировать суммы по месяцам , используя два критерия: один для соответствующей даты больше или равной первому дню месяца, второй, для соответствующей даты меньше или равной последнему дню месяца. Другими словами, нам нужна формула , как эта:
Более безопасная альтернатива
Он более безопасен, поскольку сроки построены с отдельными значениями год, месяц и день, а не вводятся как текст, который должен быть интерпретирован Excel).
Как правило, это болезненно, потому что если вы добавляете названия месяцев в виде текста (т.е. «январь», «Февраль», «март» и т.д.) в столбце Е вы должны пойти на лишние хлопоты, чтобы создать даты, которые можно использовать для критериев.
Тем не менее, в данном случае, мы используем простой трюк, чтобы сделать это легко: В колонке Е, вместо того, чтобы печатать названия месяцев, мы добавим фактические даты (1/1/2016, 2/1/2016, 3/1/2016), затем используем формат дат ( «мммм») для отображения названия месяцев.
Это позволяет легко строить критерии нужных нам для СУММЕСЛИ. Чтобы соответствующая дата была больше или равна первому числу месяца, мы используем:
И чтобы соответствующая дата была меньше или равна последнему дню месяца, мы используем:
Где КОНМЕСЯЦА автоматически возвращается в последний день.
Конъюнкция с операндом (&) необходима при построении критериев, которые использует логический оператор с числовым значением.
Сумма за месяц игнорируя год
=СУММПРОИЗВ (( МЕСЯЦ ( даты ) = месяц ) * количество )
Подводя данные по месяцам, игнорируя год, вы можете использовать формулу, основанную на функциях СУММПРОИЗВ и МЕСЯЦ.
В показанном примере, формула в Н5:
В результате получится сумма всех продаж в марте, независимо от года.
Внутри функции СУММПРОИЗВ, функция МЕСЯЦ используется для извлечения номера месяца для каждой даты в наборе данных. Сокращенный вариант результата массива выглядит следующим образом:
Каждое значение сравнивается с 3 (номер месяца март), чтобы получить результат, как этот:
Этот массив затем умножается на значения суммы, ассоциированных с каждой датой марта. Арифметическая операция изменяет ИСТИНА/ЛОЖЬ значения в единицы и нули, поэтому операция выглядит примерно так:
Где 100 это просто заполнитель на любую сумму. В результате получится один массив следующим образом:
Обратите внимание, что только «выжившие« суммы связаны с мартом, а остальные равны нулю.
СУММПРОИЗВ затем суммирует элементы в массиве и рассчитывает результат, 300 в сокращенном примере выше.
Среднее значение по месяцам
Чтобы высчитать среднее за месяц, вы можете использовать формулу, основанную на функции СРЗНАЧЕСЛИМН, с помощью функции КОНМЕСЯЦА.
В показанном примере, формула в F4 является:
Функция СРЗНАЧЕСЛИМН может находить среднее в диапазонах на основе нескольких критериев. В этом случае мы устанавливаем СРЗНАЧЕСЛИМН составлять среднее по месяцам , используя два критерия: (1) дата больше или равна первому дню месяца, (2) дата меньше или равна последнему дню месяца. Если мы жестко закодируем даты в январе 2016 года в формулу с использованием функции ДАТА, она будет выглядеть следующим образом:
Но мы не хотим, чтобы даты жестко кодировались, мы хотим, чтобы Excel создал эти даты для нас. Как правило, это сложно, потому что если вы добавляете названия месяцев в виде текста (т.е. «январь», «февраль», «март» и т.д.) в столбце Е вы должны пойти на лишние хлопоты, чтобы создать даты, которые можно использовать для критериев,
Тем не менее, в данном случае, мы используем простой трюк, чтобы сделать вещи проще: в колонке Е, вместо того, чтобы печатать названия месяцев, мы добавим фактические даты для первого числа каждого месяца (1/1/2016, 2/1/2016, 3 / 1/2016, и т.д.), а также используем пользовательский формат даты ( «МММ») для отображения названия месяцев.
Это позволяет легко строить критерии, нужные нам для СРЗНАЧЕСЛИМН. Чтобы соответствующие даты были больше или равны первому числу месяца, мы используем:
И чтобы соответствующие даты были меньше или равны последнему дню месяца, мы используем:
КОНМЕСЯЦА автоматически возвращается в последний день того же месяца, потому что мы поставляем ноль для месяца аргумента.
Примечание: конъюнкция с операндом (&) необходима при построении критериев на основе ссылки на ячейку.
В суммировании значений, которые находятся между двумя датами, можно использовать функцию СУММЕСЛИМН.
В примере показано, ячейка H7 содержит формулу:
Эта формула суммирует суммы в столбце D, если Дата в столбце C между датой в Н5 и Н6. В примере, Н5 содержит 15 июля 2019 и H6 содержит 15 августа 2019.
Функция СУММЕСЛИМН поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), и несколько критериев.
Чтобы соответствовать времени между двумя значениями, нам нужно использовать два критерия. СУММЕСЛИМН требует, чтобы каждому критерию вводился в качестве критерия/пара диапазон:
Обратите внимание, что мы должны заключить логические операторы в двойные кавычки ( «» ), а затем присоединиться с ссылками на ячейки с помощью амперсанда (&).
Если вы хотите включить Дату начала или окончания, а также сроки между ними, используйте больше или равно («>=») и меньше или равно («<=»).
Сумма, если Дата больше, чем
В сумме, если дата превышает определенную дату, вы можете использовать функцию СУММЕСЛИ.
В примере показано, ячейка H4 содержит формулу:
Эта формула суммирует суммы в столбце D, если Дата в столбце C больше 1 октября 2019 года.
Функция СУММЕСЛИ поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы можете использовать их, как вам нравится в ваших критериях.
В данном случае, мы хотим чтобы дата была больше, чем 1 октября 2019 года, поэтому мы используем оператор больше чем (>).
Обратите внимание, что мы должны поставить оператор «больше, чем» в двойные кавычки и присоединить к нему амперсанд (&).
ДАТА как ссылка на ячейку
Если вы хотите выставить дату на листе, так что он может быть легко изменена, используйте эту формулу:
где A1-ссылка на ячейку, которая содержит действительную дату.
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.
Как суммировать значения по месяцам и годам в Excel?
Если у вас есть диапазон данных, столбец A содержит некоторые даты, а столбец B - количество заказов, теперь вам нужно просуммировать числа на основе месяца и года из другого столбца. В этом случае я хочу подсчитать общее количество заказов за январь 2016 года, чтобы получить следующий результат. В этой статье я расскажу о некоторых трюках, которые помогут решить эту задачу в Excel.
Сумма значений на основе месяца и года с формулой
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Следующая формула может помочь вам получить общее значение на основе месяца и года из другого столбца, пожалуйста, сделайте следующее:
Введите эту формулу в пустую ячейку, в которой вы хотите получить результат: =SUMPRODUCT((MONTH(A2:A15)=1)*(YEAR(A2:A15)=2016)*(B2:B15)) (A2: A15 содержит ли ячейки даты, B2: B15 содержит значения, которые вы хотите суммировать, и число 1 указывает месяц январь, 2016 это год.) и нажмите Enter ключ для получения результата:
Сумма значений на основе месяца и года с Kutools for Excel
Если вас не интересует приведенная выше формула, здесь я могу представить вам удобный инструмент -Kutools for Excel, это может помочь вам решить и эту задачу.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Во-первых, вы должны скопировать и вставить данные в резервную копию исходных данных.
2. Затем выберите диапазон дат и нажмите Кутулс > Формат > Применить форматирование даты, см. снимок экрана:
3. В Применить форматирование даты диалоговом окне выберите формат даты месяца и года Мар-2001 что вы хотите использовать. Смотрите скриншот:
4. Нажмите Ok чтобы изменить формат даты на формат месяца и года, а затем выберите диапазон данных (A1: B15), с которым вы хотите работать, и продолжайте щелкать Кутулс > Слияние и разделение > Расширенные ряды комбинирования, см. снимок экрана:
5. В Расширенные ряды комбинирования диалоговом окне установите столбец Дата как Основной ключ, и выберите расчет для столбца "Заказ" под Рассчитать раздел, в данном случае я выбираю Сумма, см. снимок экрана:
6. Затем нажмите Ok кнопки, все номера заказов были добавлены вместе на основе одного месяца и года, см. снимок экрана:
Демо: значения суммы на основе месяца и года с Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Читайте также: