Общая формула
=COUNTIFS(dates,">="&A1,dates,"<"&EDATE(A1,1))
Резюме
Чтобы создать итоговый счетчик по месяцам, вы можете использовать функцию СЧЁТЕСЛИМН и функцию ДАТАФИКА с двумя критериями. В показанном примере формула в G5:
=COUNTIFS(dates,">="&F5,dates,"<"&EDATE(F5,1))
Объяснение
В этом примере у нас есть список из 100 вопросов в столбцах от B до D. Каждая проблема имеет дату и приоритет. Мы также используем именованный диапазон «даты» для C5: C104 и «приоритеты» для D5: D105. Начиная со столбца F, у нас есть сводная таблица, в которой показано общее количество за месяц, за которым следует общее количество за месяц по приоритетам.
Мы используем функцию СЧЁТЕСЛИМН для генерации подсчета. Первый столбец сводной таблицы (F) - это дата первого числа каждого месяца 2015 года. Чтобы получить общее количество за месяц, нам необходимо предоставить критерии, которые будут изолировать все проблемы, возникающие в каждом месяце.
Поскольку у нас есть фактические даты в столбце F, мы можем построить необходимые нам критерии, используя саму дату и вторую дату, созданную с помощью функции EDATE. Эти два критерия появляются внутри СЧЁТЕСЛИМН, вот так:
dates,">="&F5,dates,"<"&EDATE(F5,1)
В грубом переводе: «даты больше или равные дате в F5 и меньше даты в F5 плюс один месяц». Это удобный способ генерировать «скобки» для каждого месяца на основе одной даты.
Когда формула копируется в столбец G, СЧЁТЕСЛИМН генерирует правильное количество для каждого месяца.
Примечание: если вы не хотите видеть полные даты в столбце F, просто примените настраиваемые форматы даты «ммм» или «мммм», чтобы отображались только названия месяцев.
С приоритетом
Чтобы произвести подсчет по приоритету, нам нужно расширить критерии. Формула в H5:
=COUNTIFS(dates,">="&$F5,dates,"<"&EDATE($F5,1),priorities,H$4)
Здесь мы добавили дополнительный критерий, именованный диапазон «приоритетов» в паре с H4 для самого критерия. В этой версии формулы мы получаем подсчет по месяцам с разбивкой по приоритету, который берется непосредственно из заголовка в строке 5. Эта формула использует как смешанные, так и абсолютные ссылки для облегчения копирования:
- Ссылка на H4 заблокировала строку (H $ 4), поэтому приоритет не меняется при копировании формулы.
- Ссылка на F5 заблокировала столбец ($ F5), поэтому дата не меняется при копировании формулы.
- Именованные диапазоны «даты» и «приоритеты» автоматически являются абсолютными.
Подход сводной таблицы
Сводная таблица - хорошее альтернативное решение этой проблемы. Как правило, сводные таблицы легче и быстрее настраивать, когда данные хорошо структурированы.
