Vispārēja formula
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Kopsavilkums
Apkopojot katru mēnesi, ar funkciju EOMONTH palīdzību varat izmantot formulu, kuras pamatā ir funkcija SUMIFS. Parādītajā piemērā formula F4 ir:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Šajā formulā tiek izmantoti nosauktie diapazoni "summa" (C4: C9) un "datums" (B4: B9).
Paskaidrojums
Funkcija SUMIFS var summēt vērtības diapazonos, pamatojoties uz vairākiem kritērijiem.
Šajā gadījumā mēs konfigurējam SUMIFS, lai summētu summas pa mēnešiem, izmantojot divus kritērijus: vienu, lai atbilstu datumiem, kas ir lielāki vai vienādi ar mēneša pirmo dienu, vienu - datumiem, kas ir mazāki vai vienādi ar mēneša pēdējo dienu. Citiem vārdiem sakot, mums ir nepieciešama šāda formula:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Vai arī ložu necaurlaidīgāka alternatīva:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Šī versija ir stabilāka, jo datumi tiek veidoti ar atsevišķām gada, mēneša un dienas vērtībām un netiek ievadīti kā teksts, kas Excel ir jāinterpretē.
Parasti tas ir sāpes, jo, ja E slejā pievienojat mēnešu nosaukumus kā tekstu (ti, "janvāris", "februāris", "marts" utt.), Jums ir jāpieliek papildu problēmas, lai izveidotu datumus, kurus varat izmantot kritērijiem . Tomēr šajā gadījumā mēs izmantojam vienkāršu triku, lai padarītu lietas ērtākas: E slejā tā vietā, lai ierakstītu mēnešu nosaukumus, mēs pievienojam faktiskos datumus (2016. gada 1. janvāris, 2016. gada 1. janvāris, 2016. gada 3. janvāris), pēc tam izmantojiet pielāgotu datuma formātu ("mmmm"), lai parādītu mēnešu nosaukumus.
Tas ļauj viegli izveidot kritērijus, kas nepieciešami SUMIFS. Lai saskaņotu datumus, kas ir lielāki vai vienādi ar mēneša pirmo, mēs izmantojam:
">="&E4
Lai saskaņotu datumus, kas ir mazāki vai vienādi ar mēneša pēdējo dienu, mēs izmantojam šo:
"<="&EOMONTH(E4,0)
Ja mēnešiem ir nulle, funkcija EOMONTH automātiski atgriež mēneša pēdējo dienu, ko nodrošina datums.
Savienošana ar zīmi (&) ir nepieciešama, veidojot kritērijus, kuros izmantots loģisks operators ar skaitlisku vērtību.
Pagrieziena galda risinājums
Pagrieziena tabula ir lielisks risinājums, kad jums jāapkopo dati pēc gada, mēneša, ceturkšņa utt., Jo viņi veiks jums šāda veida grupēšanu bez jebkādām formulām. Lai salīdzinātu formulas un rakurstabulas, skatiet šo videoklipu: Kāpēc rakurstabulas.
