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

Сума значень за роками в Excel

Припустимо, що у вас є діапазон даних, який містить список дат і сум продажу, тепер ви хочете отримати загальну суму на основі даних років, як показано нижче. У Excel звичайні функції SUMIFS і SUMPRODUCT можуть допомогти швидко і легко вирішити це завдання. Цей підручник детально продемонструє, як використовувати ці дві функції.


Підсумуйте значення за роками в Excel з функціями SUMIFS і DATE

Щоб підсумувати загальне значення на основі даного року, ви повинні вказати першу та останню дату року за допомогою функції DATE, а потім використовувати функцію SUMIFS для підсумування всіх значень у межах зазначених дат, загальний синтаксис:

=SUMIFS(sum_range,date_range,">="&DATE(year,1,1),date_range,"<="&DATE(year,12,31)
  • sum_range: діапазон значень, які потрібно підсумувати;
  • date_range: діапазон дат для перевірки для конкретного року;
  • year: конкретний рік, на основі якого потрібно підсумувати значення. (рік, 1,1 вказує на перший день року, і рік, 12,31, XNUMX вказує на останній день року.)

Щоб вирішити це завдання в 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,">="&ДАТА(E2,1,1): перевіряє дати в діапазоні B2:B12, які більше або дорівнюють 01 – перший день року в клітинці E01.
  • $B$2:$B$12,"<="&ДАТА(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

Тут вам може допомогти ще одна проста формула, створена функцією SUMPRODUCT, загальний синтаксис:

=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: Функція YEAR повертає номер року з кожної клітинки дати в діапазоні B2:B12. Цей вираз перевіряє, чи дорівнює рік в E2 року в діапазоні B2:B12. І ви отримаєте такий масив: {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}.
  • (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: У цьому виразі значення TRUE та FALSE будуть примусово до 1s і 0s, і цей масив множиться на кожну суму в діапазоні 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}): функція SUMPRODUCT нарешті підсумовує всі числа в цьому масиві і повертає результат у вигляді 2386.

Використана відносна функція:

  • SUMIFS:
  • Функція SUMIFS в Excel може допомогти підсумувати значення клітинок на основі кількох критеріїв.
  • SUMPRODUCT:
  • Функцію SUMPRODUCT можна використовувати для множення двох або більше стовпців або масивів разом, а потім отримати суму добутків.
  • ДАТА:
  • Функція ДАТА може допомогти нам об’єднати номер року, місяця та дня з окремих клітинок до дійсної дати.
  • РІК:
  • Функція YEAR повертає рік на основі заданої дати у 4-значному форматі серійного номера.

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

  • Сума значень за місяць (з роком чи без)
  • У цьому підручнику докладно демонструються дві формули, які допомагають підсумовувати значення за певним місяцем і роком або підсумовувати значення за певним місяцем ігнорувати рік у Excel.
  • Сума значень за тиждень або день тижня
  • У цьому підручнику покроково демонструються формули, які допоможуть вам підсумувати значення в певному діапазоні на основі вказаного номера тижня або дня тижня в Excel.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Бажаєте виконувати щоденну роботу швидко та якісно? Kutools для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

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

Вкладка Office - Увімкнути читання та редагування з вкладками в Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL