Обща формула
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Обобщение
За осредняване по месеци можете да използвате формула, базирана на функцията AVERAGEIFS, с помощта на функцията EOMONTH. В показания пример формулата във F4 е:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Тази формула използва посочените диапазони "суми" (D5: D104) и "дати" (C5: C104).
Обяснение
Функцията AVERAGEIFS може да осреднява диапазони въз основа на множество критерии. В този случай конфигурираме AVERAGEIFS да осреднява суми по месеци, като използваме два критерия: (1) дати на съвпадение, по-големи или равни на първия ден от месеца, (2) дати на съвпадение, по-малки или равни на последния ден от месеца. Ако твърдо кодираме дати за януари 2016 г. във формулата, използвайки функцията DATE, тя ще изглежда така.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Но ние не искаме да кодираме датите, а искаме Excel да генерира тези дати за нас. Обикновено това е мъка, защото ако добавите имена на месеци като текст (т.е. "януари", "февруари", "март" и т.н.) в колона F, трябва да преминете към допълнителни проблеми, за да създадете дати, които можете да използвате за критерии .
В този случай обаче използваме прост трик, за да улесним нещата: В колона F, вместо да въвеждаме имена на месеци, добавяме действителни дати за първото от всеки месец (1/1/2016, 2/1/2016, 3 / 1/2016 и др.) И използвайте персонализиран формат за дата ("mmm"), за да покажете имената на месеците.
Това улеснява изграждането на критериите, от които се нуждаем за AVERAGEIFS. За да съответстваме на дати, по-големи или равни на първото от месеца, използваме:
">="&E4
И за да съвпадаме с дати, по-малки или равни на последния ден от месеца, използваме:
"<="&EOMONTH(E4,0)
EOMONTH автоматично връща последния ден от същия месец, защото предоставяме нула за аргумента за месеците.
Забележка: конкатенацията с амперсанд (&) е необходима при изграждане на критерии, базирани на препратка към клетка.
Решение на Pivot Table
Осевата таблица е отлично решение, когато трябва да обобщите данните по година, месец, тримесечие и т.н., тъй като осевите таблици осигуряват контроли за автоматично групиране по дата. За сравнение едно до друго на формули спрямо обобщени таблици вижте това видео: Защо обобщени таблици.
