Сумма значений по годам в 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))
- 2 канадских доллара: 12 канадских долларов: это «диапазон_суммирования», содержащий значения, которые мы хотим суммировать.
- $ B $ 2: $ B $ 12, "> =" & DATE (E2,1,1): проверяет даты в диапазоне B2: B12, которые больше или равны 01 - первому дню года в ячейке E01.
- $ B $ 2: $ B $ 12, "<=" & DATE (E2,12,31): проверяет даты в диапазоне B2: B12, которые меньше или равны 12 - последнему дню года в ячейке E31.
- 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)
- ГОД ($ B $ 2: $ B $ 12) = E2: Функция ГОД возвращает номер года из каждой ячейки даты в диапазоне B2: B12. Это выражение проверяет, совпадает ли год в E2 с годом в диапазоне B2: B12. И вы получите такой массив: {ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ}.
- (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}*$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.
Используемая относительная функция:
- SUMIFS:
- Функция СУММЕСЛИМН в Excel может помочь суммировать значения ячеек на основе нескольких критериев.
- SUMPRODUCT:
- Функцию СУММПРОИЗВ можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.
- ДАТА:
- Функция ДАТА может помочь нам объединить номера года, месяца и дня из отдельных ячеек в действительную дату.
- ГОД:
- Функция ГОД возвращает год на основе заданной даты в формате 4-значного серийного номера.
Другие статьи:
- Сумма значений по месяцам (с учетом года или без него)
- В этом руководстве подробно показаны две формулы, которые помогут суммировать значения по определенному месяцу и году или суммировать значения по определенному месяцу без учета года в Excel.
- Сумма значений по неделям заданной даты
- Чтобы суммировать значения по неделям, на которые приходится заданная дата, вы можете применить формулу, основанную на функции СУММЕСЛИМН.
- Суммарные значения по недельному номеру или буднему дню
- В этом руководстве шаг за шагом демонстрируются формулы, которые помогут вам суммировать значения в определенном диапазоне на основе указанного номера недели или дня недели в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
