Обща формула
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Обобщение
За да сумирате по месец, можете да използвате формула, базирана на функцията SUMIFS, с помощта на функцията EOMONTH. В показания пример формулата във F4 е:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Тази формула използва посочените диапазони "количество" (C4: C9) и "дата" (B4: B9).
Обяснение
Функцията SUMIFS може да сумира стойности в диапазони въз основа на множество критерии.
В този случай ние конфигурираме SUMIFS да сумира суми по месец, като използваме два критерия: единият да съответства на дати, по-големи или равни на първия ден от месеца, един да отговаря на дати, по-малки или равни на последния ден от месеца. С други думи, ние се нуждаем от формула като тази:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Или колкото по-устойчивата на куршуми алтернатива:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Тази версия е по-надеждна, тъй като датите се конструират с отделни стойности за година, месец и ден и не се въвеждат като текст, който трябва да се интерпретира от Excel.
Обикновено това е мъка, защото ако добавите имена на месеци като текст (т.е. "януари", "февруари", "март" и т.н.) в колона E, трябва да преминете към допълнителни проблеми, за да създадете дати, които можете да използвате за критерии . В този случай обаче използваме прост трик, за да улесним нещата: В колона Е, вместо да въвеждаме имена на месеци, добавяме действителни дати (1/1/2016, 2/1/2016, 3/1/2016), след това използвайте персонализиран формат на датата ("mmmm"), за да покажете имената на месеците.
Това улеснява изграждането на критериите, от които се нуждаем за SUMIFS. За да съответстваме на дати, по-големи или равни на първото от месеца, използваме:
">="&E4
И за да съвпадаме с дати, по-малки или равни на последния ден от месеца, използваме това:
"<="&EOMONTH(E4,0)
С нула за месеци, функцията EOMONTH автоматично връща последния ден от месеца, предоставен от датата.
Обединяването с амперсанд (&) е необходимо при изграждане на критерии, които използват логически оператор с числова стойност.
Решение на Pivot Table
Осевата таблица е отлично решение, когато трябва да обобщите данните по година, месец, тримесечие и т.н., тъй като те ще направят този вид групиране за вас без никакви формули. За сравнение едно до друго на формули спрямо обобщени таблици вижте това видео: Защо обобщени таблици.
