Макросы

Основная формула

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

Как работает

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

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

=СУММЕСЛИМН ( сумма ; дата ; "> =" & E4 ; дата ; "<=" & КОНМЕСЯЦА( E4 ; 0 ))

Эта формула использует названные диапазоны "количество" (C4: С9) и "Дата" (B4: B9).

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

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

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

= СУММЕСЛИМН( сумма ; дата ; "> =" & "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 )

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

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

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

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