Excel сумма дней по месяцам

= СУММЕСЛИМН( значения ; Диапазон_Значений ; «> =» & A1 ; Диапазон_Значений; «<=» & КОНМЕСЯЦА( A1 ; 0 ))

Эта функция поможет подвести итоги по месяцу при помощи функции КОНМЕСЯЦА. В примере формула выглядит так:

=СУММЕСЛИМН(C4:C9;B4:B9; «>=» & E4; B4:B9; «<=» &КОНМЕСЯЦА( E4; 0 ))

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

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

= СУММЕСЛИМН( сумма ; дата ; «> =» & «1/1/2016» ; дата ; «<=» & «1/31/2016» )

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

=СУММЕСЛИМН ( сумма ; дата ; «> =» & ДАТА ( 2016 ; 1 ; 1 ); дата ; «<=» & ДАТА ( 2016 ; 1 ; 31 ))

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

Как правило, это болезненно, потому что если вы добавляете названия месяцев в виде текста (т.е. «январь», «Февраль», «март» и т.д.) в столбце Е вы должны пойти на лишние хлопоты, чтобы создать даты, которые можно использовать для критериев.

Тем не менее, в данном случае, мы используем простой трюк, чтобы сделать это легко: В колонке Е, вместо того, чтобы печатать названия месяцев, мы добавим фактические даты (1/1/2016, 2/1/2016, 3/1/2016), затем используем формат дат ( «мммм») для отображения названия месяцев.

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

«> =» & E4

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

«<=» &КОНМЕСЯЦА( E4 ; 0 )

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

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

Сумма за месяц игнорируя год

=СУММПРОИЗВ (( МЕСЯЦ ( даты ) = месяц ) * количество )

Подводя данные по месяцам, игнорируя год, вы можете использовать формулу, основанную на функциях СУММПРОИЗВ и МЕСЯЦ.

В показанном примере, формула в Н5:

=СУММПРОИЗВ((МЕСЯЦ(B4:B19)=3)*E4:E19)

В результате получится сумма всех продаж в марте, независимо от года.

Внутри функции СУММПРОИЗВ, функция МЕСЯЦ используется для извлечения номера месяца для каждой даты в наборе данных. Сокращенный вариант результата массива выглядит следующим образом:

{ 1 ; 1 ; 1 ; 2 ; 2 ; 2 ; 3 ; 3 ; 3 }

Каждое значение сравнивается с 3 (номер месяца март), чтобы получить результат, как этот:

{ ЛОЖЬ ; ЛОЖЬ ; ЛОЖЬ ; ЛОЖЬ ; ЛОЖЬ ; ЛОЖЬ ; ИСТИНА ; ИСТИНА ; ИСТИНА }

Этот массив затем умножается на значения суммы, ассоциированных с каждой датой марта. Арифметическая операция изменяет ИСТИНА/ЛОЖЬ значения в единицы и нули, поэтому операция выглядит примерно так:

{ 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 1 ; 1 ; 1 } * { 100 ; 100 ; 100 ; 100 ; 100 ; 100 ; 100 ; 100 ; 100 }

Где 100 это просто заполнитель на любую сумму. В результате получится один массив следующим образом:

{ 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 100 ; 100 ; 100 }

Обратите внимание, что только «выжившие« суммы связаны с мартом, а остальные равны нулю.

СУММПРОИЗВ затем суммирует элементы в массиве и рассчитывает результат, 300 в сокращенном примере выше.

Среднее значение по месяцам

=СРЗНАЧЕСЛИМН( значения ; даты ; «> =» & A1 ; даты ; «<=» & КОНМЕСЯЦА( A1 ))

Чтобы высчитать среднее за месяц, вы можете использовать формулу, основанную на функции СРЗНАЧЕСЛИМН, с помощью функции КОНМЕСЯЦА.

В показанном примере, формула в F4 является:

=СРЗНАЧЕСЛИМН(D5:D12;C5:C12;»>=F5″;C5:C12;»»<=КОНМЕСЯЦА(F5;0))

Функция СРЗНАЧЕСЛИМН может находить среднее в диапазонах на основе нескольких критериев. В этом случае мы устанавливаем СРЗНАЧЕСЛИМН составлять среднее по месяцам , используя два критерия: (1) дата больше или равна первому дню месяца, (2) дата меньше или равна последнему дню месяца. Если мы жестко закодируем даты в январе 2016 года в формулу с использованием функции ДАТА, она будет выглядеть следующим образом:

=СРЗНАЧЕСЛИМН( суммы ; сроки ; «> =» & ДАТА ( 2016 ; 1 ; 1 ); даты ; «<=» & ДАТА ( 2016 ; 1 ; 31 ))

Кодирование дат

Но мы не хотим, чтобы даты жестко кодировались, мы хотим, чтобы Excel создал эти даты для нас. Как правило, это сложно, потому что если вы добавляете названия месяцев в виде текста (т.е. «январь», «февраль», «март» и т.д.) в столбце Е вы должны пойти на лишние хлопоты, чтобы создать даты, которые можно использовать для критериев,

Тем не менее, в данном случае, мы используем простой трюк, чтобы сделать вещи проще: в колонке Е, вместо того, чтобы печатать названия месяцев, мы добавим фактические даты для первого числа каждого месяца (1/1/2016, 2/1/2016, 3 / 1/2016, и т.д.), а также используем пользовательский формат даты ( «МММ») для отображения названия месяцев.

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

«> =» & E4

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

«<=» & КОНМЕСЯЦА( E4 ; 0 )

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

Примечание: конъюнкция с операндом (&) необходима при построении критериев на основе ссылки на ячейку.

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

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

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

• Повторное использование чего угодно: добавление наиболее часто используемых или сложных формул, диаграмм и все остальное в избранное и быстро используйте их в будущем.
• Более 20 функций текста: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
• Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
• Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
• Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
• Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
• Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

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

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

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

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

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

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

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

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

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

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

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

Суммирование данных по дням недели/ месяц/год с Kutools for Excel

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

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

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

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

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

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

4. Выберите диапазон данных, который вы будете суммировать по дням недели, месяцу или году, и нажмите Kutools > Content > Advanced Combine Rows .

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

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

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

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

Статьи по теме:

Вычислить среднее значение по году/месяцу/дате в Excel

Вычислить среднее значение за неделю в Excel

Среднее за день/месяц/квартал/час со сводной таблицей в Excel

Источник

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

= СУММЕСЛИМН( значения ; Диапазон_Значений ; «> =» & A1 ; Диапазон_Значений; «

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

Предположим, что расчетный цикл счета составляет 30 дней, и вы хотите, чтобы средства были на счете за 15 дней до даты вычета за март 2013 г. Вот как это сделать, используя формулу или функцию для работы с датами.

1. В ячейке A1 введите 08.02.2013.

2. В ячейке B1 введите =A1-15.

3. В ячейке C1 введите =A1+30.

4. В ячейке D1 введите =C1-15.