Сума значень за роками в Excel
Припустимо, що у вас є діапазон даних, який містить список дат і сум продажу, тепер ви хочете отримати загальну суму на основі даних років, як показано нижче. У Excel звичайні функції SUMIFS і SUMPRODUCT можуть допомогти швидко і легко вирішити це завдання. Цей підручник детально продемонструє, як використовувати ці дві функції.
- Підсумуйте значення за роками в Excel з функціями SUMIFS і DATE
- Підсумуйте значення за роками в Excel з функцією SUMPRODUCT
Підсумуйте значення за роками в Excel з функціями SUMIFS і DATE
Щоб підсумувати загальне значення на основі даного року, ви повинні вказати першу та останню дату року за допомогою функції DATE, а потім використовувати функцію SUMIFS для підсумування всіх значень у межах зазначених дат, загальний синтаксис:
- 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))
- $ 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, загальний синтаксис:
- 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.
- Сума значень за тиждень заданої дати
- Щоб підсумувати значення за тижнем, на який припадає певна дата, можна застосувати формулу на основі функції SUMIFS.
- Сума значень за тиждень або день тижня
- У цьому підручнику покроково демонструються формули, які допоможуть вам підсумувати значення в певному діапазоні на основі вказаного номера тижня або дня тижня в Excel.
Найкращі інструменти для підвищення продуктивності офісу
Kutools for Excel - Допоможе вам виділитися з натовпу
Kutools for Excel Має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Office Tab - Увімкніть читання та редагування з вкладками в Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
