Суммирование значений по году в Excel

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

• Суммирование значений по году в Excel с помощью функций СУММЕСЛИМН и ДАТА
• Суммирование значений по году в Excel с помощью функции СУММПРОИЗВ

Суммирование значений по году в Excel с помощью функций СУММЕСЛИМН и ДАТА

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

• sum_range: диапазон значений, которые необходимо просуммировать,
• date_range: диапазон дат, которые проверяются на соответствие конкретному году,
• year: конкретный год, за который требуется суммировать значения. ()год, 1,1 означает первый день года, а год, 12,31 — последний день года.)

Чтобы решить эту задачу в Excel, скопируйте или введите приведённую ниже формулу в пустую ячейку, а затем протяните её вниз по другим ячейкам для получения результата:

Объяснение этой формулы:

=SUMIFS($C$2:$C$12,$B$2:$B$12,«>,=»&,DATE(E2,1,1),$B$2:$B$12,"<="&,DATE(E2,12,31))

• $C$2:$C$12: это «диапазон_суммирования» — он содержит значения, которые нужно просуммировать.
• $B$2:$B$12,«>=»&DATE(E2,1,1): проверяет даты в диапазоне B2:B12, которые больше или равны 01,01.2020 — первому дню года, указанного в ячейке E2.
• $B$2:$B$12,«<=»&DATE(E2,12,31): проверяет даты в диапазоне B2:B12, которые меньше или равны 31,12.2020 — последнему дню года, указанного в ячейке E2.
• SUMIFS($C$2:$C$12,$B$2:$B$12,«>=»&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)): после выполнения условий значения в диапазоне C2:C12 будут найдены и просуммированы.

Суммирование значений по году в Excel с помощью функции СУММПРОИЗВ

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

• date_range: диапазон дат, которые проверяются на соответствие конкретному году;
• year: конкретный год, по которому требуется суммировать значения;
• sum_range: диапазон значений, которые нужно просуммировать.

Скопируйте или введите следующую формулу в пустую ячейку, а затем протяните её вниз по другим ячейкам для получения результата:

Объяснение этой формулы:

=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)

• YEAR($B$2:$B$12)=E2: функция ГОД извлекает год из каждой даты в диапазоне B2:B12 и сравнивает его со значением в ячейке E2. В результате вы получите массив вида: {ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}.
• (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}*$C$2:$C$12: в этом выражении значения ИСТИНА и ЛОЖЬ автоматически преобразуются в 1 и 0 соответственно, после чего полученный массив умножается на каждое значение из диапазона C2:C12, и вы получаете новый массив следующего вида: {347;0;398;430;0;379;412;0;0;420;0}.
• SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347,0,398,430,0,379,412,0,0,420,0}): в итоге функция СУММПРОИЗВ складывает все числа в этом массиве и возвращает результат 2386.

Используемые связанные функции:

Другие статьи: