Обща формула
=COUNTIFS(dates,">="&A1,dates,"<"&EDATE(A1,1))
Обобщение
За да създадете обобщен брой по месеци, можете да използвате функцията COUNTIFS и функцията EDATE с два критерия. В показания пример формулата в G5 е:
=COUNTIFS(dates,">="&F5,dates,"<"&EDATE(F5,1))
Обяснение
В този пример имаме списък със 100 броя в колони Б до Г. Всеки брой има дата и приоритет. Също така използваме посочения диапазон "дати" за 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), така че датата не се променя при копиране на формулата.
- Посочените диапазони "дати" и "приоритети" са автоматично абсолютни.
Подход на обобщена таблица
Осевата таблица е добро алтернативно решение на този проблем. Като цяло, обобщените таблици се настройват по-лесно и по-бързо, когато данните са добре структурирани.
