Summa pa gadiem

Vispārējā formula

=SUMPRODUCT((YEAR(dates)=A1)*amounts)

Kopsavilkums

Lai summētu vērtības pa gadiem, varat izmantot formulu, kuras pamatā ir SUMPRODUCT funkcija kopā ar Funkcija YEAR . Parādītajā piemērā formula šūnā G5 ir:

=SUMPRODUCT((YEAR(data[Date])=F5)*data[Amount])

kur datus ir Excel tabula diapazonā B5:D16. Kad formula tiek kopēta, tā atgriež kopējo summu par katru gadu, kas parādīts F ailē.

Paskaidrojums

Šajā piemērā mērķis ir aprēķināt kopējo summu katram gadam, kas tiek rādīts kolonnā F. Visi dati ir an Excel tabula sauca datus diapazonā B5:D16. Galvenais izaicinājums ir tas, ka mums ir datumi kolonnā B, bet mums nav atsevišķu gadu vērtību, ar kurām strādāt. Vienkāršākais veids, kā atrisināt šo problēmu, ir ar SUMPRODUCT funkcija izmantojot formulu, kas parādīta šūnā G5. Problēmu var atrisināt arī ar SUMIFS funkcija , vai ar a dinamiskā masīva formula . Visas trīs pieejas ir izskaidrotas tālāk.

SUMPRODUCT funkcija

Parādītajā piemērā formula šūnā G5 ir:

kāds daļas numurs ir starp skaitļiem 20112 un 21017

=SUMPRODUCT((YEAR(data[Date])=F5)*data[Amount])

Strādājot no iekšpuses, pirmais solis ir izvilkt gada vērtības no datumiem kolonnā B, kas tiek darīts ar funkciju YEAR:

YEAR(data[Date]) // get years

Tā kā kolonnā ir 12 datumi, YEAR atgriež 12 rezultātus an masīvs kā šis:

{2020;2020;2021;2022;2020;2021;2021;2022;2020;2021;2022;2022}

Katrs gads šajā masīvā atbilst datumam [datums]. Pēc tam gada vērtības tiek salīdzinātas ar gadu šūnā F5:

YEAR(data[Date])=F5

Rezultāts ir 12 TRUE un FALSE vērtību masīvs, piemēram:

{TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

Ņemiet vērā, ka TRUE vērtības atbilst datumiem, kas ir 2020. gadā, vērtība F5. Pēc tam šis masīvs tiek reizināts ar summām kolonnā D. Matemātiskā darbība automātiski pārvērš vērtības TRUE un FALSE uz 1s un 0s, lai mēs varētu vizualizēt šo darbību šādi:

{1;1;0;0;1;0;0;0;1;0;0;0}*data[Amount]

Atcerieties, ka 1s pirmajā masīvā atbilst datumiem, kas radušies 2021. gadā, savukārt 0 norāda datumus citos gados. Nulles pirmajā masīvā efektīvi 'atceļ' summas citos gados. Iegūtais masīvs tiek atgriezts SUMPRODUCT šādi:

=SUMPRODUCT({1500;1250;0;0;1850;0;0;0;1250;0;0;0})

Ja ir jāapstrādā tikai viens masīvs, SUMPRODUCT summē masīvu un atgriež gala rezultātu 5850. Kad formula tiek kopēta, šūnas atsauce F5, kas ir relatīva, mainās katrā jaunā rindā, un mēs iegūstam pareizo kopējo summu katram gadam. parādīts.

SUMIFS funkcija

Vēl viens veids, kā atrisināt šo problēmu, ir SUMIFS funkcija un šāda formula:

=SUMIFS(data[Amount],data[Date],">="&DATE(O5,1,1),data[Date],"<="&DATE(O5,12,31))

Ja esat iesācējs SUMIFS, Šis raksts aptver pamatus.

Šī formula ir sarežģītāka nekā SUMPRODUCT formula, jo SUMIFS nevar tieši iegūt gada vērtības. Šis ierobežojums ir sīkāk apspriests šeit . Tā vietā mums ir jāizveido divi datumi katram gadam ar Funkcija DATE :

kā atrast korelāciju Excel

DATE(O5,1,1) // start date
DATE(O5,12,31) // end date

Sākuma datumam mēs vienkārši iekodējam 1 mēnesis un diena argumenti, lai iegūtu datumu 1. janvārī. Beigu datumam mēs kodējam 12 mēnesis un 31 par diena lai izveidotu 31. decembra datumu. Abiem datumiem, gadā nāk no šūnas O5, kas satur 2020.

Excel apvieno vairākas šūnas vienā, atdalot ar komatu

Mums ir nepieciešami arī loģiskie operatori, kas katru gadu 'iekavās'. Mēs sasaistīt mums nepieciešamie operatori ir šādi:

">="&DATE(O5,1,1) // start date
"<="&DATE(O5,12,31) // end date

Prasība savienot operatorus, piemēram, šo, atkal ir SUMIFS funkcijas 'iezīme', kas koplieto šo savdabīgo sintakse ar astoņas citas funkcijas .

Kad funkcija DATE ir novērtēta un savienošana ir pabeigta, mums ir:

=SUMIFS(data[Amount],data[Date],">=43831",data[Date],"<=44196")

Numurs 43831 ir Excel sērijas numura datums 2020. gada 1. janvārim. Numurs 44196 ir 2020. gada 31. decembra sērijas numurs.

Piezīme: izmantojot vienu no divām iepriekš minētajām opcijām, varat izmantot UNIKĀLA funkcija lai vispirms izgūtu unikālos gadus no datumiem, ja jums ir mūsdienīga Excel versija.

Dinamiskais masīva risinājums

Šo problēmu var atrisināt arī ar vienu dinamiskā masīva formulu, piemēram:

=LET(
years,YEAR(data[Date]),
values,data[Amount],
uyears,SORT(UNIQUE(years)),
sums, BYROW(uyears, LAMBDA(r, SUM(--(years=r)*values))),
VSTACK({"Year","Total"},HSTACK(uyears, sums))
)