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

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

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

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

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

• диапазон_суммирования: Диапазон значений, которые необходимо просуммировать;
• диапазон_дат: Диапазон дат, которые будут проверяться на определенный год;
• год: Конкретный год, по которому вы хотите суммировать значения. (год, 1,1 означает первый день года, и год,12,31 означает последний день года.)

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

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

=СУММЕСЛИМН($C$2:$C$12;$B$2:$B$12;">="&ДАТА(E2;1;1);$B$2:$B$12;"<="&ДАТА(E2;12;31))

• $C$2:$C$12: это «диапазон_суммирования», который содержит значения, которые мы хотим суммировать.
• $B$2:$B$12;">="&ДАТА(E2;1;1): проверяет даты в диапазоне B2:B12, которые больше или равны 01/01/2020 – первый день года в ячейке E2.
• $B$2:$B$12;"<="&ДАТА(E2;12;31): проверяет даты в диапазоне B2:B12, которые меньше или равны 31/12/2020 – последний день года в ячейке E2.
• СУММЕСЛИМН($C$2:$C$12;$B$2:$B$12;">="&ДАТА(E2;1;1);$B$2:$B$12;"<="&ДАТА(E2;12;31)): После выполнения условий значения в диапазоне C2:C12 будут найдены и просуммированы.

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

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

• диапазон_дат: Диапазон дат, которые будут проверяться на определенный год;
• год: Конкретный год, по которому вы хотите суммировать значения;
• диапазон_суммирования: Диапазон значений, которые необходимо просуммировать.

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

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

=СУММПРОИЗВ((ГОД($B$2:$B$12)=E2)*$C$2:$C$12)

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

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

Больше статей: