Генеричка формула
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Резиме
За просечно израчунавање по месецима можете да користите формулу засновану на функцији АВЕРАГЕИФС, уз помоћ функције ЕОМОНТХ. У приказаном примеру, формула у Ф4 је:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Ова формула користи именоване опсеге „износи“ (Д5: Д104) и „датуми“ (Ц5: Ц104).
Објашњење
Функција АВЕРАГЕИФС може да просечно креће опсеге на основу више критеријума. У овом случају конфигуришемо АВЕРАГЕИФС за просечне износе по месецима користећи два критеријума: (1) датуми подударања већи или једнаки првом дану у месецу, (2) датуми подударања мањи или једнаки последњем дану у месецу. Када бисмо чврсто кодирали датуме за јануар 2016. у формулу помоћу функције ДАТЕ, то би изгледало овако.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Али не желимо да кодирамо датуме, већ желимо да их Екцел генерише за нас. Обично је то мука, јер ако додате називе месеци као текст (нпр. „Јануар“, „фебруар“, „март“ итд.) У колону Ф, морате направити додатне проблеме да бисте креирали датуме које можете користити за критеријуме .
Међутим, у овом случају користимо једноставан трик да бисмо олакшали ствари: У колону Ф, уместо да укуцамо имена месеци, додајемо стварне датуме за први у месецу (1/1/2016, 1/2016, 3 / 1/2016, итд.) И користите прилагођени формат датума („ммм“) за приказ имена месеца.
Ово олакшава изградњу критеријума који су нам потребни за АВЕРАГЕИФС. За подударање датума већих или једнаких првом у месецу користимо:
">="&E4
А да бисмо подударали датуме мање или једнаке последњем дану у месецу, користимо:
"<="&EOMONTH(E4,0)
ЕОМОНТХ аутоматски враћа последњи дан истог месеца јер за аргумент месеци достављамо нулу.
Напомена: спајање знаком & (&) је неопходно када се граде критеријуми засновани на референци ћелије.
Решење за изведене табеле
Изведена табела је изврсно решење када треба да сумирате податке по години, месецу, кварталу итд., Јер изведене табеле пружају контроле за аутоматско груписање по датуму. За упоредно поређење формула у односу на пивот табеле погледајте овај видео: Зашто пивот табеле.
