2 Путь Суммы по месяцам в Excel

Sum_range: это диапазон, который вы хотите суммировать по месяцам.

Date_range: это диапазон дат, в котором вы будете искать месяцы.

Месяц_текст: это месяц в текстовом формате, для которого вы хотите просуммировать значения.

Теперь посмотрим на пример:

Как вы можете видеть на изображении выше, все даты относятся к 2019 году. Теперь нам просто нужно просуммировать значения в E2: G2 по месяцам в E1: G1.

Теперь, чтобы суммировать значения по месяцам, запишите эту формулу в E2:

Если вы хотите скопировать его в соседние ячейки, используйте link: / excel-generals-relative-and-absolute-reference-in-excel [absolute links] или `link: / excel-range-name-all-about- именованные диапазоны в Excel [именованные диапазоны] `как вы на изображении.

Это дает нам точную сумму за каждый месяц.

Давайте посмотрим на

часть. Здесь функция ТЕКСТ извлекает месяц из каждой даты в диапазоне A2: A9 в текстовом формате в массив. Преобразование в формулу в = СУММПРОИЗВ (B2: B9, [. Custom-span] # — # [. Custom-span] # — # (\ {«Янв»; «Янв»; «Фев»; «Янв»; «Фев «;» Мар «;» Янв «;» Фев «} = E1))

Затем ТЕКСТ (A2: A9, «MMM») = E1: здесь каждый месяц в массиве сравнивается с текстом в E1. Поскольку E1 содержит «Jan», каждое «Jan» в массиве преобразуется в TRUE, а остальные в FALSE. Это преобразует формулу в = SUMPRODUCT ($ B $ 2: $ B $ 9, — \ {TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})

Далее [.custom-span] # — # [. Custom-span] # — # (TEXT (A2: A9, «MMM») = E1), преобразует TRUE FALSE в двоичные значения 1 и 0. Формула преобразуется в = SUMPRODUCT ($ B $ 2: $ B $ 9, \ {1; 1; 0; 1; 0; 0; 1; 0}).

Наконец, СУММПРОИЗВ ($ B $ 2: $ B $ 9, \ {1; 1; 0; 1; 0; 0; 1; 0}): функция СУММПРОИЗВ умножает соответствующие значения в $ B $ 2: $ B $ 9 на массив \ { 1; 1; 0; 1; 0; 0; 1; 0} и складывает их. Следовательно, мы получаем сумму по значению как 20052 в E1.

Здесь мы добавили еще один критерий проверки года. В остальном все то же самое.

Давайте решим приведенный выше пример, запишем эту формулу в ячейку E1, чтобы получить сумму января 2017 года.

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

На изображении я использовал именованные диапазоны для копирования в соседние ячейки.

Теперь мы можем видеть сумму стоимости по месяцам и годам.

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

link:bbbb
=== (A2: A9, «yyyy») = TEXT (D2,0)):

ТЕКСТ (A2: A9, «гггг») преобразует дату в формате A2: A9 как годы в текстовом формате в массив. \ {«2018»; «2019»; «2017»; «2017»; «2019»; «2017»; «2019»; «2017»}.

В большинстве случаев год записывается в числовом формате. Чтобы сравнить число с текстом we, я преобразовал текст года в int с помощью TEXT (D2,0). Затем мы сравнили этот текстовый год с массивом лет как link: / excel-text-formulas-the-text-function-in-excel [TEXT] (A2: A9, «yyyy») = TEXT (D2,0) .

Это возвращает массив истинно-ложных \ {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Затем мы преобразовали true false в число с помощью оператора [.custom-span] # — # [. Custom-span] # — #.

Это дает нам \ {0; 0; 1; 1; 0; 1; 0; 1}.

Итак, наконец, формула будет переведена в = СУММПРОИЗВ (B2: B9, \ {1; 1; 0; 1; 0; 0; 1; 0}, \ {0; 0; 1; 1; 0; 1; 0 ; 1}). Где первый массив — это значения. Следующий соответствует месяц, а третий год. В итоге мы получаем нашу сумму значений как 2160.

===

Здесь Sum_range: это диапазон, который вы хотите суммировать по месяцам.

Date_range: это диапазон дат, в котором вы будете искать месяцы.

Дата начала: это начальная дата, с которой вы хотите суммировать. В этом примере это будет 1-е число данного месяца.

Здесь у нас есть некоторая ценность, связанная с датами. Это январь, февраль и март месяца 2019 года.

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

Итак, чтобы подготовить отчет, я подготовил формат отчета, который содержит месяц и сумму значений. В столбце месяца у меня действительно есть дата начала месяца. Чтобы просто увидеть месяц, выберите дату начала и нажмите CTRL + 1.

В произвольном формате напишите «ммм».

Теперь у нас есть данные. Суммируем значения по месяцам.

Запишите эту формулу в E3 для суммирования по месяцам.

Используйте link: / excel-generals-relative-and-absolute-reference-in-excel [абсолютные ссылки] или `link: / excel-range-name-all-about-named-range-in-excel [именованные диапазоны ] `перед копированием формулы.

Итак, наконец-то мы получили результат.

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

В приведенном выше примере первым критерием является сумма всех значений в B3: B10, где дата в A3: A10 больше или равна дате в D3. D3 содержит 1-янв. Это тоже переводится.

Следующие критерии являются суммой, только если Дата в A3: A10 меньше или равна EOMONTH (D3,0).

link: / excel-date-time-formulas-find-the-last-day-of-a-month [EOMONTH]

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

Наконец, формула тоже переводится.

Следовательно, в Excel мы получаем сумму по месяцам.

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

Если у ваших дат разные годы, лучше использовать сводные таблицы.

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

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

Если у вас есть какие-либо вопросы относительно этой статьи или любых других вопросов, связанных с Excel и VBA, раздел комментариев открыт для вас.

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]

link: / tips-sumifs-with-Date-in-excel [СУММЕСЛИМН с датами в Excel]

link: / tips-excel-sumif-not-blank-cells [СУММЕСЛИ с непустыми ячейками]

link: / summing-excel-sumifs-function [Как использовать функцию СУММЕСЛИМН в Excel]

link: / summing-sumifs-using-and-or-logic [СУММЕСЛИМН с использованием логики И-ИЛИ]

link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-повышения-продуктивности [50 ярлыков Excel для повышения производительности]: быстрее справляйтесь с задачей.

Эти 50 ярлыков заставят вас работать в Excel еще быстрее.

link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию VLOOKUP в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы.

link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция панели инструментов. Это поможет вам суммировать значения для конкретных условий.