Суммирование Excel по месяцам

Обновлено: 21.11.2024

Сегодня я покажу, как можно суммировать данные по месяцам, используя функции СУММЕСЛИ() и СУММЕСЛИМН() в Microsoft Excel.

Загрузить практическую рабочую тетрадь

Как СУММЕСЛИ по месяцам в Excel

Давайте посмотрим на этот набор данных. У нас есть отчет о продажах на некоторые даты за 2019 и 2020 годы компании Ernesto Group.

Теперь мы определим сумму общих продаж по месяцам, используя функцию СУММЕСЛИ() в Excel.

Случай 1. Сумма по месяцам того же года

В первую очередь мы определим сумму по месяцам того же года.

Это означает, что мы будем определять общий объем продаж за май 2019 г. и май 2020 г. отдельно и так далее.

Здесь мы будем использовать две функции. СУММЕСЛИМН() и КОНМЕСЯЦА()

СУММЕСЛИМН()

Синтаксис

Принимает диапазон ячеек с именем sum_range.
Принимает один или несколько диапазонов ячеек под названием критерии_диапазона и один или несколько критериев рядом.
Возвращает сумму ячеек диапазона sum_range, чьи смежные ячейки диапазона критерии_диапазона поддерживают заданные критерии.

Чтобы узнать больше о функции СУММЕСЛИМН(), перейдите по этой ссылке.

КОНМЕСЯЦА()

Синтаксис

=EOМЕСЯЦА(дата_начала, месяц)

Принимает дату с именем start_date и число с именем месяц.
Создает новый месяц, добавляя число с именем month к месяцу даты с именем start_date.
Затем вернуть последнюю дату нового месяца.

КОНМЕСЯЦ(6 марта-19,2) = 31 мая-19

Чтобы узнать больше о функции КОНМЕСЯЦА(), перейдите по этой ссылке.

Теперь давайте сначала определим общий объем продаж за месяцы 2019 года.

Прежде всего выберите ячейку и введите дату 01.01.2019.

Затем перейдите в раздел Главная>Числовой формат>Дополнительный числовой формат на панели инструментов Excel.

Появится диалоговое окно «Формат ячеек». В поле «Категория» выберите «Пользовательский». Затем в поле Тип напишите мммм.

Затем нажмите ОК. Вместо даты 01.01.2019 вы найдете январь.

Затем перетащите маркер заполнения до 12 ячеек. После перетаскивания нажмите «Параметры автозаполнения».

В списке вариантов выберите «Заполнить месяц».

Вы найдете названия 12 месяцев красиво вставленными вот так.

Затем в соседней ячейке к январю напишите эту формулу

Это вычисляет общий объем продаж за январь 2019 г.

А затем снова перетащите маркер заполнения, чтобы получить общий объем продаж за каждый месяц 2019 года.

Проделайте то же самое с остальными годами.

Случай 2: Сумма по месяцам разных лет

Теперь мы рассчитаем общий объем продаж каждого месяца, охватывающего все годы.

Это означает, что теперь мы рассчитаем общий объем продаж за июнь 2019 года и июнь 2010 года вместе.

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

Я ввожу эту формулу в ячейку D4 и называю столбец "Название месяца".

Примечание. Функция ТЕКСТ() принимает значение и формат в качестве аргумента и возвращает значение в этом формате.

Здесь он принимает дату в качестве аргумента и возвращает только название месяца даты.

Теперь перетащите маркер заполнения, чтобы вставить эту формулу в остальные ячейки.

Затем выберите ячейку и напишите Январь.

Затем перетащите маркер заполнения еще на 12 ячеек, чтобы получить названия всех месяцев года.

Теперь в ячейку, соседнюю с январем, вставьте эту формулу

Это вычисляет общий объем продаж за январь месяца за все годы.

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

Альтернативные варианты

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

На самом деле иногда они немного проще.

Вариант 1. Использование функции СУММПРОИЗВ()

Кроме того, это можно сделать с помощью функции СУММПРОИЗВ() Excel.

Прежде всего, для расчета общего объема продаж за каждый месяц разных лет вместе,

Запишите названия каждого месяца, с января по декабрь, в такой столбец.

Затем вставьте эту формулу в соседнюю ячейку января.

Затем перетащите маркер заполнения, чтобы получить общий объем продаж за каждый месяц.

А для расчета продаж по месяцам каждого года отдельно напишите названия лет в соседних строках и сохраните их в текстовом формате в разделе Главная>Числовой формат>Текст.

Затем вставьте эту формулу в ячейку F4.

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

Вариант 2. Использование сводной таблицы

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

Сначала выберите любую ячейку в ваших данных. Затем перейдите в меню Вставка>Сводная таблица>Сводная таблица.

Нажмите на сводную таблицу. Вы получите окно создания сводной таблицы.

Проверить существующий рабочий лист.

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

Я вставляю ячейку E4.

Затем нажмите ОК. Вы получите созданную сводную таблицу. Поместите чеки в поля «Дата» и «Общий объем продаж».

Вернувшись к рабочему листу, вы увидите, что даты были вставлены в столбец с названием "Ярлыки строк", а общий объем продаж был вставлен в столбец с названием "Сумма общего объема продаж".

Щелкните правой кнопкой мыши любую дату и выберите "Группа".

Затем в поле "Группировка" выберите "Месяцы" и нажмите "ОК".

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

Заключение

Используя эти методы, вы можете вычислить сумму любых данных по месяцам в Excel. У вас есть вопросы? Не стесняйтесь спрашивать в разделе комментариев.

Для суммирования по месяцам можно использовать формулу, основанную на функции СУММЕСЛИМН с помощью функции КОНМЕСЯЦА.

В показанном примере формула в F4 выглядит следующим образом:

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

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

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

Или более безопасная альтернатива:

(Надежнее, поскольку даты составляются с отдельными значениями года, месяца и дня, а не вводятся в виде текста, который должен интерпретироваться Excel).

Обычно это доставляет неудобства, потому что если вы добавите названия месяцев в виде текста (например, "Январь", "Февраль", "Март" и т. д.) в столбец E, вам придется приложить дополнительные усилия, чтобы создать даты, которые вы можете использовать для критериев.

Однако в этом случае мы используем простой прием, чтобы упростить задачу: в столбце E вместо названий месяцев мы добавляем фактические даты (01.01.2016, 01.02.2016, 01.03.2016). 2016), затем используйте собственный формат даты ("мммм") для отображения названий месяцев.

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

Чтобы сопоставить даты, меньшие или равные последнему дню месяца, мы используем:

где КОНМЕСЯЦА автоматически возвращает последний день.

Конкатенация с помощью амперсанда (&) необходима при построении критериев, в которых используется логический оператор с числовым значением.

Решение сводной таблицы

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

Как суммировать данные по дням недели/месяцам/кварталам/годам в Excel?

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

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

Суммирование данных по месяцам/кварталам/годам с помощью сводной таблицы

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

<р>1. Выберите исходные данные, которые вы будете суммировать по месяцам/кварталам/годам, и нажмите «Вставить» > «Сводная таблица» (или «Вставить» > «Сводная таблица» > «Сводная таблица»). Смотрите скриншот:

<р>2. В открывшемся диалоговом окне «Создать сводную таблицу» укажите место, куда вы поместите созданную сводную таблицу, и нажмите кнопку «ОК». Смотрите скриншот выше:

<р>3. Теперь панель «Поля сводной таблицы» открывается справа от рабочего листа, перетащите поле «Дата» (поле, по которому вы будете суммировать) в раздел «Строки» и перетащите поле «Сумма» (поле, которое вы будете суммировать) в раздел «Значения». Смотрите скриншот ниже:

Примечание. Помимо перетаскивания, вы также можете щелкнуть правой кнопкой мыши поле «Дата» и выбрать «Добавить к меткам строк» в контекстном меню, а затем щелкнуть правой кнопкой мыши поле «Сумма» и выбрать «Добавить к значениям» в контекстном меню. Смотрите скриншот выше:

<р>4. Теперь перейдите к сводной таблице, щелкните правой кнопкой мыши любую дату в столбце «Ярлыки строк» и выберите «Группа» в контекстном меню. Смотрите скриншот ниже:

<р>5. В открывшемся диалоговом окне «Группировка» щелкните, чтобы выделить критерий, по которому вы будете суммировать, в поле «По» и нажмите кнопку «ОК». В нашем случае мы нажимаем, чтобы выделить Месяцы. Смотрите скриншот выше:

На данный момент мы создали сводную таблицу и суммируем суммы по месяцам, как показано на снимке экрана ниже:

Суммируйте данные по дням недели/месяцам/годам с помощью Kutools for Excel

Возможно, вы заметили, что сводная таблица не может суммировать данные по дням недели. Не волнуйся! Этот метод представит утилиты Kutools for Excel для простого суммирования данных по дням недели, месяцам или годам следующим образом:

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас

<р>1. Выберите ячейки даты, по которым вы будете суммировать данные, и нажмите Kutools > Формат > Применить форматирование даты.

<р>2. В открывшемся диалоговом окне «Применить форматирование даты» щелкните, чтобы выделить форматирование даты, которое отображает дату только как месяц, день недели или год в зависимости от ваших потребностей, а затем нажмите кнопку «ОК». Смотрите скриншот выше:

В нашем случае мы нажимаем, чтобы выделить формат даты «Ср».

<р>3. Продолжайте выбирать ячейки даты и нажмите Kutools > Фактический, чтобы преобразовать даты в текст названий дней недели.

<р>4. Выберите диапазон данных, который вы будете суммировать по дням недели, месяцам или годам, и нажмите Kutools > Контент > Расширенные ряды комбинирования.

<р>5. В открывшемся диалоговом окне «Объединить строки на основе столбца» выполните следующие действия:
(1) Выберите столбец «Дата», по которому вы будете суммировать, и нажмите кнопку «Первичный ключ»;
(2) Выберите столбец «Сумма», который необходимо суммировать, и нажмите «Рассчитать» > «Сумма».
(3) Задайте правила объединения для других столбцов и нажмите кнопку "ОК".

Теперь вы увидите, что диапазон данных суммируется по дням недели, месяцам или годам в соответствии с вашими настройками. Смотрите скриншот ниже:

Демонстрация: суммирование данных по дням недели/месяцам/кварталам/годам в Excel

Kutools for Excel включает в себя более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Загрузите и получите бесплатную пробную версию прямо сейчас!

Если суммировать данные по месяцам в таблице, нам нужно использовать формулу, основанную на функции СУММЕСЛИМН. Мы можем предоставить набор условий или критериев в функции СУММЕСЛИМН для суммирования данных. Чтобы суммировать данные по месяцам, нам нужно добавить диапазон дат в качестве критерия.

Пример:

Смотрите скриншот ниже, если мы хотим суммировать общую сумму по месяцам, как мы можем это сделать?

Решение:

В C2 введите формулу =СУММЕСЛИМН($B$2:$B$10,$A$2:$A$10”,>=”&A2,$A$2:$A$10”,

Для функции СУММЕСЛИМН =СУММЕСЛИМН(сумма_диапазон, критерий_диапазон1, критерий1, [критерий_диапазон2, критерий2], …), в данном случае $B$2:$B$10 — это диапазон суммы, $A$2:$A$10 — это диапазон критериев, критерий 1 >=A2 (1.10.2020), критерий 2 = = 01.10.2020″,$A$2:$A$10»,

Результат:

Нажмите Enter, чтобы получить возвращаемое значение.

Примечания:

<р>1. Формула также работает, если формат даты отличается от мм/дд/гггг. Например, мы меняем дату в ноябре на другой формат даты в таблице. Затем формула обновляется до =СУММЕСЛИМН($B$2:$B$10,$A$2:$A$10”,>=”&A5,$A$2:$A$10”,

<р>2. Если мы просто хотим суммировать данные за период, мы можем обновить формулу до =СУММЕСЛИМН(диапазон суммы, диапазон критериев1»,>=”&дата начала, диапазон критериев2”, =”&A11, $A$2:$A$14”,< /p>

Связанные функции

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

В повседневной работе, если мы хотим суммировать числа из диапазона и суммировать только числа, равные X или Y в диапазоне, мы можем создать формулу со встроенными функциями Excel для получения результата. .

В нашей повседневной жизни нам может понадобиться суммировать суммы или продажи за определенный период, например за последние N дней. Суммировать числа в Excel легко, мы можем применить функцию СУММ. Но если мы хотим.

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

Если мы хотим добавить числа на основе некоторых условий на листе Excel, мы можем добавить критерии с помощью функции СУММЕСЛИМН, СУММЕСЛИМН может эффективно фильтровать данные с несколькими критериями. В этой статье мы познакомим вас с методом.

Чтобы сложить числа, нам нужно применить функцию СУММ. Но если мы хотим добавить числа на основе некоторых условий, мы можем добавить критерии с помощью функции СУММЕСЛИМН, СУММЕСЛИМН может эффективно фильтровать данные с несколькими критериями. В .

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

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

Мы ввели метод суммирования чисел на основе критерия «сумма, если ячейка начинается с» (см. «Как суммировать данные, если начинается с в Excel» на нашем веб-сайте). В этой статье мы покажем вам .

Читайте также: