Обобщение
За да сумирате по месец в колони, можете да използвате функцията SUMIFS заедно с функцията EOMONTH. В показания пример формулата в G5 е:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Тази формула използва три именувани диапазона: „количество“ (D5: D15), „клиент“ (B5: B15) и „дата“ (C5: C15).
Обяснение
Функцията SUMIFS е предназначена да сумира стойности в диапазон въз основа на един или повече критерии. В този случай се нуждаем от три критерия:
- Име на клиента = клиент в колона F
- Дата> = първо число (от датата в ред 4)
- Дата <= края на месеца (от датата в ред 4)
Ключов момент: имената на месеците в G4: I4 са действително валидни дати, форматирани с персонализиран формат на числото "mmm". Това ни позволява да използваме стойностите на датата в G4: I4 директно за критерии # 2 и # 3 по-горе.
Обратно в SUMIFS, първата двойка диапазон / критерии се използва за проверка на името на клиента:
client,$F5
Забележка F5 е смесена препратка, със заключена колона. Това позволява реда да се променя, докато формулата се копира през таблицата, но името на клиента винаги идва от колона F.
Втората двойка диапазон / критерии се използва за проверка на датите спрямо първото от месеца:
date,">="&G$4
Както бе споменато по-горе, стойностите в G4: I4 всъщност са дати: 1 януари 2019 г., 1 февруари 2019 г. и 1 март 2019 г. И така, този критерий просто проверява за дати, по-големи от датите от първия в месеца в ред 4. Забележете, че това е друга смесена препратка, този път със заключен ред. Това позволява на колоната да се променя, докато формулата се копира в таблицата, но запазва номера на реда фиксиран. Обединяването с амперсанд (&) е необходимо при изграждане на критерии, които използват логически оператор и стойност от друга клетка.
Третата двойка диапазон / критерии се използва за проверка на датите спрямо последния ден на месеца:
date,"<="&EOMONTH(G$4,0)
За да получим последния ден от всеки месец, използваме функцията EOMONTH на датата от заглавката на колоната в ред 4. При нула, предоставена за аргумента за месеците, EOMONTH връща последния ден от същия месец. Отново препратката към G4 се смесва, за да не се променя редът.
Решение на Pivot Table
Осевата таблица би била отлично решение за този проблем, тъй като може автоматично да се групира по месец, без изобщо да има формули. За сравнение едно до друго на формули спрямо обобщени таблици вижте това видео: Защо обобщени таблици.
