Общая формула
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Резюме
Для усреднения по месяцам вы можете использовать формулу, основанную на функции СРЗНАЧЕСЛИМН с помощью функции EOMONTH. В показанном примере формула в F4:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
В этой формуле используются именованные диапазоны «суммы» (D5: D104) и «даты» (C5: C104).
Объяснение
Функция СРЗНАЧЕСЛИМН может усреднять диапазоны на основе нескольких критериев. В этом случае мы настраиваем СРЗНАЧ для усреднения сумм по месяцам с использованием двух критериев: (1) даты совпадения больше или равны первому дню месяца, (2) даты совпадений меньше или равны последнему дню месяца. Если бы мы жестко ввели даты января 2016 года в формулу с помощью функции ДАТА, это выглядело бы так.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Но мы не хотим жестко запрограммировать даты, мы хотим, чтобы Excel генерировал эти даты за нас. Обычно это неудобно, потому что если вы добавляете названия месяцев в виде текста (например, «Январь», «Февраль», «Март» и т. Д.) В столбце F, вам придется приложить дополнительные усилия, чтобы создать даты, которые можно использовать в качестве критериев. .
Однако в этом случае мы используем простой трюк, чтобы упростить задачу: в столбце F вместо ввода названия месяца мы добавляем фактические даты первого числа каждого месяца (01.01.2016, 01.02.2016, 3 / 1/2016 и т. Д.) И используйте произвольный формат даты («ммм») для отображения названий месяцев.
Это упрощает построение необходимых нам критериев для СРЕДНИЙ РАЗМЕР. Чтобы сопоставить даты, которые больше или равны первому числу месяца, мы используем:
">="&E4
И для сопоставления дат, меньших или равных последнему дню месяца, мы используем:
"<="&EOMONTH(E4,0)
EOMONTH автоматически возвращает последний день того же месяца, потому что мы указываем ноль для аргумента месяцев.
Примечание: конкатенация с амперсандом (&) необходима при построении критериев на основе ссылки на ячейку.
Решение сводной таблицы
Сводная таблица - отличное решение, когда вам нужно суммировать данные по годам, месяцам, кварталам и т. Д., Поскольку сводные таблицы предоставляют элементы управления для автоматической группировки по дате. Для параллельного сравнения формул и сводных таблиц см. Это видео: Зачем нужны сводные таблицы.
