Обща формула
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Обобщение
За да осредните времето за отговор по месец, можете да използвате формула, базирана на функцията AVERAGEIFS, заедно с функцията EOMONTH.
В показания пример формулата в G5 е:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Обяснение
Тази формула използва посочените диапазони "дати" (B5: B25) и "продължителности" (D5: D25). Продължителността на колона D е в минути, изчислена чрез изваждане на датата на отваряне от датата на затваряне.
Функцията AVERAGEIFS е проектирана да осреднява диапазони въз основа на множество критерии. В този случай конфигурираме AVERAGEIFS на средна продължителност по месеци, като използваме два критерия: (1) съвпадение на дати, по-големи или равни на първия ден от месеца, (2) съвпадащи дати, по-малки или равни на последния ден от месеца.
За да разделим дати по месеци, използваме прост трик, за да улесним нещата: В колона F, вместо да въвеждаме имена на месеци ("Jan", "Feb", Mar "и т.н.), добавяме, добавяме действителни дати за първия от всеки месец (1/1/2016, 2/1/2016, 3/1/2016 и др.),. След това използваме персонализирания формат на датата ("mmm"), за да покажем имената на месеците.
Това улеснява много изграждането на критериите, които са ни необходими за AVERAGEIFS, като се използват стойности в колона F. За да съвпадаме с дати, по-големи или равни на първото от месеца, използваме:
">="&F5
За да съответстваме на дати, по-малки или равни на последния ден от месеца, използваме:
"<="&EOMONTH(F5,0)
Получаваме EOMONTH да върне последния ден от същия месец, като предоставим нула за аргумента за месеците .
Забележка: конкатенацията с амперсанд (&) е необходима при изграждане на критерии, базирани на препратка към клетка.
Решение на Pivot Table
Осева таблица е отлично решение, когато трябва да обобщите или осредните данни по години, месеци, тримесечия и т.н., тъй като осевите таблици осигуряват контрол за автоматично групиране на датите. За сравнение едно до друго на формули спрямо обобщени таблици вижте това видео: Защо обобщени таблици.
