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