Сумирайте стойности по години в Excel
Да предположим, че имате набор от данни, който съдържа списък с дати и суми на продажба, сега искате да получите общата сума въз основа на дадени години, както е показано на екранната снимка по-долу. В Excel нормалните функции SUMIFS и SUMPRODUCT могат да помогнат за бързото и лесно решаване на тази задача. Този урок ще демонстрира подробно как да използвате тези две функции.
- Сумиране на стойности по години в Excel с функции SUMIFS и DATE
- Сумирайте стойности по години в Excel с функция SUMPRODUCT
Сумиране на стойности по години в Excel с функции SUMIFS и DATE
За да сумирате общата стойност въз основа на дадена година, трябва да посочите първата и последната дата от годината с помощта на функцията ДАТА и след това да използвате функцията SUMIFS, за да сумирате всички стойности в рамките на посочените дати, общият синтаксис е:
- 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))
- $C$2:$C$12: е “sum_range”, който съдържа стойностите, които искаме да сумираме.
- $B$2:$B$12,">="&ДАТА(E2,1,1): проверява за датите в диапазон B2:B12, които са по-големи или равни на 01/01/2020 – първият ден от годината в клетка E2.
- $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 за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
