Bendroji formulė
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Santrauka
Vidutiniškai pagal mėnesį galite naudoti formulę, pagrįstą funkcija AVERAGEIFS, naudodamiesi funkcija EOMONTH. Parodytame pavyzdyje formulė F4 yra:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Šioje formulėje naudojami įvardyti diapazonai „sumos“ (D5: D104) ir „datos“ (C5: C104).
Paaiškinimas
Funkcija „AVERAGEIFS“ gali vidutiniškai įvertinti intervalus pagal kelis kriterijus. Tokiu atveju AVERAGEIFS vidutines sumas pagal mėnesį konfigūruojame taikydami du kriterijus: (1) rungtynių datos yra didesnės arba lygios pirmajai mėnesio dienai, (2) rungtynių datos yra mažesnės arba lygios paskutinei mėnesio dienai. Jei į formulę, naudojant DATE funkciją, užkoduotume 2016 m. Sausio datas, tai atrodytų taip.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Bet mes nenorime koduoti datos, mes norime, kad „Excel“ generuotų šias datas mums. Paprastai tai yra skausmas, nes jei F stulpelyje pridedate mėnesių pavadinimus kaip tekstą (pvz., „Sausis“, „vasaris“, „kovas“ ir kt.), Turite susidurti su papildomomis problemomis, kad sukurtumėte datas, kurias galite naudoti pagal kriterijus .
Tačiau šiuo atveju mes naudojame paprastą triuką, kad būtų lengviau: F stulpelyje vietoj to, kad įvestumėte mėnesių pavadinimus, mes įtraukiame faktines kiekvieno mėnesio pirmojo datas (2016 1 1, 2016 2 1, 3 / 1/2016 ir kt.) Ir naudokite pasirinktinį datos formatą („mmm“), kad būtų rodomi mėnesių pavadinimai.
Tai leidžia lengvai nustatyti mums reikalingus AVERAGEIFS kriterijus. Norėdami suderinti datas, didesnes arba lygias mėnesio pirmajam, naudojame:
">="&E4
Norėdami suderinti datas, mažesnes arba lygias paskutinei mėnesio dienai, naudojame:
"<="&EOMONTH(E4,0)
EOMONTH automatiškai grąžina paskutinę to paties mėnesio dieną, nes mėnesiams pateikiame nulį.
Pastaba: kuriant kriterijus remiantis langelio nuoroda, būtina susieti ampersandą (&).
„Pivot Table“ tirpalas
„Pivot“ lentelė yra puikus sprendimas, kai reikia apibendrinti duomenis pagal metus, mėnesį, ketvirtį ir pan., Nes suvestinės lentelės suteikia automatinio grupavimo pagal datą valdiklius. Norėdami palyginti formules ir suvestines lenteles, žr. Šį vaizdo įrašą: Kodėl reikia sukti lenteles.
