Общая формула
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Резюме
Чтобы усреднить время ответа по месяцам, вы можете использовать формулу, основанную на функции СРЗНАЧЕСЛИМН, вместе с функцией МЕСЯЦ.
В показанном примере формула в G5:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Объяснение
В этой формуле используются именованные диапазоны «даты» (B5: B25) и «длительности» (D5: D25). Столбец D «Продолжительность» выражен в минутах и рассчитывается путем вычитания даты открытия из даты закрытия.
Функция СРЗНАЧЕСЛИМН предназначена для усреднения диапазонов на основе нескольких критериев. В этом случае мы настраиваем СРЕДНЕЛИН для усреднения продолжительности по месяцам, используя два критерия: (1) соответствие дат, больше или равных первому дню месяца, (2) соответствие дат, меньших или равных последнему дню месяца.
Чтобы заключить даты в скобки по месяцам, мы используем простой трюк, чтобы упростить задачу: в столбце F вместо ввода названий месяцев («Январь», «Фев», Март и т. Д.) Мы добавляем фактические даты для первого из каждый месяц (01.01.2016, 01.02.2016, 01.03.2016 и т. д.). Затем мы используем настраиваемый формат даты («ммм») для отображения названий месяцев.
Это значительно упрощает построение необходимых нам критериев для СРЗНАЧЕСЛИМН с использованием значений в столбце F. Чтобы сопоставить даты, которые больше или равны первому числу месяца, мы используем:
">="&F5
Для сопоставления дат, меньших или равных последнему дню месяца, мы используем:
"<="&EOMONTH(F5,0)
Мы заставляем EOMONTH возвращать последний день того же месяца, задавая ноль в качестве аргумента месяцев .
Примечание: конкатенация с амперсандом (&) необходима при построении критериев на основе ссылки на ячейку.
Решение сводной таблицы
Сводная таблица - отличное решение, когда вам нужно суммировать или усреднить данные по годам, месяцам, кварталам и так далее, поскольку сводные таблицы предоставляют элементы управления для автоматической группировки дат. Для параллельного сравнения формул и сводных таблиц см. Это видео: Зачем нужны сводные таблицы.
