Note: The other languages of the website are Google-translated. Back to English
English English

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

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


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

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

=SUMIFS(sum_range,date_range,">="&DATE(year,1,1),date_range,"<="&DATE(year,12,31)
  • 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))


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

=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 с функцией СУММПРОИЗВ

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

=SUMPRODUCT((YEAR(date_range)=year)*sum_range)
  • 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-значного серийного номера.

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


Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места