Vispārēja formula
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Kopsavilkums
Lai vidēji mēnesī aprēķinātu, jūs varat izmantot formulu, kuras pamatā ir funkcija AVERAGEIFS, izmantojot funkciju EOMONTH. Parādītajā piemērā formula F4 ir:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Šajā formulā tiek izmantoti nosauktie diapazoni "summas" (D5: D104) un "datumi" (C5: C104).
Paskaidrojums
Funkcija AVERAGEIFS var noteikt vidējos diapazonus, pamatojoties uz vairākiem kritērijiem. Šajā gadījumā mēs AVERAGEIFS konfigurējam kā vidējās summas mēnesī, izmantojot divus kritērijus: (1) spēles datumi ir lielāki vai vienādi ar mēneša pirmo dienu, (2) spēles datumi ir mazāki vai vienādi ar mēneša pēdējo dienu. Ja mēs formātā, izmantojot funkciju DATE, stingri kodētu datumus 2016. gada janvārim, tas izskatās šādi.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Bet mēs nevēlamies kodēt datumus, bet vēlamies, lai Excel mums ģenerētu šos datumus. Parasti tas ir sāpīgi, jo, pievienojot mēneša nosaukumus kā tekstu (ti, "janvāris", "februāris", "marts" utt.) F kolonnā, jums ir jāpieņem papildu problēmas, lai izveidotu datumus, kurus varat izmantot kritērijiem .
Tomēr šajā gadījumā mēs izmantojam vienkāršu triku, lai atvieglotu lietas: F slejā tā vietā, lai ierakstītu mēnešu nosaukumus, mēs pievienojam faktiskos datumus katram mēnesim pirmajam (2016. gada 1. janvāris, 2016. gada 1. janvāris, 3 / 1/2016 utt.) Un izmantojiet pielāgotu datuma formātu ("mmm"), lai parādītu mēnešu nosaukumus.
Tas ļauj viegli izveidot kritērijus, kas mums vajadzīgi AVERAGEIFS. 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:
"<="&EOMONTH(E4,0)
EOMONTH automātiski atgriež tā paša mēneša pēdējo dienu, jo mēneša argumentam mēs piegādājam nulli.
Piezīme: savienošana ar zīmi (&) ir nepieciešama, veidojot kritērijus, pamatojoties uz šūnas atsauci.
Pagrieziena galda risinājums
Pagrieziena tabula ir lielisks risinājums, ja jums jāsavāc dati pēc gada, mēneša, ceturkšņa utt., Jo rakurstabulas nodrošina automātiskās grupēšanas pēc datuma vadīklas. Lai salīdzinātu formulas un rakurstabulas, skatiet šo videoklipu: Kāpēc rakurstabulas.
