Загальна формула
=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 тощо). Потім ми використовуємо спеціальний формат дати ("ммм") для відображення назв місяців.
Це значно полегшує побудову критеріїв, які нам потрібні для AVERAGEIFS, використовуючи значення у стовпці F. Для відповідності датам, більшим або рівним першого числа місяця, ми використовуємо:
">="&F5
Для відповідності датам, меншим або рівним останньому дню місяця, ми використовуємо:
"<="&EOMONTH(F5,0)
Ми отримуємо EOMONTH, щоб повернути останній день того ж місяця, подавши нуль для аргументу місяців .
Примітка: конкатенація амперсандом (&) необхідна при побудові критеріїв на основі посилання на клітинку.
Рішення зведеної таблиці
Зведена таблиця - відмінне рішення, коли потрібно узагальнити або усереднити дані за роками, місяцями, кварталами тощо, оскільки зведені таблиці забезпечують автоматичне групування дат. Порівняльне порівняння формул із зведеними таблицями див. У цьому відео: Навіщо зведені таблиці.
