Загальна формула
=COUNTIFS(dates,">="&A1,dates,"<"&EDATE(A1,1))
Резюме
Щоб створити підсумковий підрахунок за місяцями, ви можете використовувати функцію COUNTIFS та функцію EDATE із двома критеріями. У наведеному прикладі формула в G5 має вигляд:
=COUNTIFS(dates,">="&F5,dates,"<"&EDATE(F5,1))
Пояснення
У цьому прикладі ми маємо список із 100 випусків у стовпцях B - D. Кожне видання має дату та пріоритет. Ми також використовуємо названий діапазон "дати" для C5: C104 та "пріоритети" для D5: D105. Починаючи зі стовпця F, ми маємо підсумкову таблицю, яка показує загальний підрахунок за місяць, а потім загальний підрахунок за місяць за пріоритетом.
Ми використовуємо функцію COUNTIFS для створення підрахунку. Перший стовпець зведеної таблиці (F) - це дата першого числа кожного місяця 2015 року. Щоб сформувати загальний підрахунок за місяць, нам потрібно надати критерії, які відокремлять усі проблеми, що з’являються у кожному місяці.
Оскільки ми маємо фактичні дати у стовпці F, ми можемо побудувати необхідні нам критерії, використовуючи саму дату та другу дату, створену за допомогою функції EDATE. Ці два критерії відображаються всередині COUNTIFS приблизно так:
dates,">="&F5,dates,"<"&EDATE(F5,1)
Грубо перекладене: "дати, більші або рівні даті в F5 і менші за дату в F5 плюс один місяць". Це зручний спосіб формування "дужок" для кожного місяця на основі однієї дати.
Коли формула копіюється в стовпець G, COUNTIFS генерує правильний підрахунок для кожного місяця.
Примітка: якщо ви не хочете бачити повні дати в стовпці F, просто застосуйте спеціальні формати дат "mmm" або "mmmm", щоб відображати лише назви місяців.
З пріоритетом
Щоб генерувати підрахунок за пріоритетом, нам потрібно розширити критерії. Формула в H5 така:
=COUNTIFS(dates,">="&$F5,dates,"<"&EDATE($F5,1),priorities,H$4)
Тут ми додали додатковий критерій, названий діапазон "пріоритетів" у парі з H4 для самих критеріїв. У цій версії формули ми отримуємо підрахунок за місяцями, розбитий за пріоритетом, який береться безпосередньо з заголовка в рядку 5. Ця формула використовує як змішані посилання, так і абсолютні посилання для полегшення копіювання:
- Посилання на H4 має заблокований рядок (H $ 4), тому пріоритет не змінюється під час копіювання формули.
- Посилання на F5 має стовпець заблокований ($ F5), тому дата не змінюється під час копіювання формули.
- Названі діапазони "дати" та "пріоритети" автоматично є абсолютними.
Підхід до зведеної таблиці
Зведена таблиця є хорошим альтернативним рішенням цієї проблеми. Загалом, зведені таблиці легше та швидше встановлювати, коли дані добре структуровані.
