Много пъти искаме да изчислим някои стойности по месеци. Например колко продажби са направени за един перикуларен месец. Е, това може да стане лесно с помощта на обобщени таблици, но ако се опитвате да имате динамичен отчет, тогава можем да използваме SUMPRODUCT или SUMIFS формула за сумиране по месеци.
Нека започнем с решение SUMPRODUCT.
Ето общата формула за получаване на сума по месеци в Excel
= SUMPRODUCT (sum_range,-(TEXT (date_range, "MMM") = = month_text))
Sum_range : Това е диапазонът, който искате да сумирате по месеци.
Период от време : Това е периодът от време, в който ще търсите месеци.
Месец_текст: Това е месецът в текстов формат, за който искате да сумирате стойностите.
Сега нека видим пример:
Пример: Сумирани стойности по месеци в Excel
Тук имаме някаква стойност, свързана с дати. Тези дати са месец януари, февруари и март на 2019 година.
Както можете да видите на изображението по -горе, всички дати са 2019 година. Сега просто трябва да сумираме стойностите в E2: G2 по месеци в E1: G1.
Сега, за да сумирате стойностите според месеците, напишете тази формула в E2:
= СУМПРОДУКТ (B2: B9,-(ТЕКСТ (A2: A9, "MMM") = E1)))
Ако искате да го копирате в съседни клетки, използвайте абсолютни препратки или именувани диапазони, както вие в изображението.
Това ни дава точната сума за всеки месец.
Как работи?
Започвайки отвътре, нека разгледаме ТЕКСТ (A2: A9, "MMM") част. Тук функцията TEXT извлича месец от всяка дата в диапазон A2: A9 в текстов формат в масив. Превод във формула на = SUMPRODUCT (B2: B9,-({"Jan"; "Jan"; "Feb"; "Jan"; "Feb"; "Mar"; "Jan"; "Feb"} = E1) )
След това TEXT (A2: A9, "MMM")= Е1: Тук всеки месец в масива се сравнява с текст в E1. Тъй като E1 съдържа „Jan“, всеки „Jan“ в масива се преобразува в TRUE, а други във FALSE. Това превежда формулата на = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Следваща -(TEXT (A2: A9, "MMM") = E1), преобразува TRUE FALSE в двоични стойности 1 и 0. Формулата се превежда на = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Най -накрая СУМПРОДУКТ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): функцията SUMPRODUCT умножава съответните стойности в $ B $ 2: $ B $ 9 в масив {1; 1; 0; 1; 0; 0; 1; 0} и ги събира. Следователно получаваме сума по стойност като 20052 в E1.
SUM IF месеци от различна година
В горния пример всички дати са от една и съща година. Ами ако бяха от различни години? Горната формула ще сумира стойностите по месеци, независимо от годината. Например, януари 2018 г. и януари 2019 г. ще бъдат добавени, ако използваме горната формула. Което е погрешно в повечето случаи.
Това ще се случи, защото нямаме никакви критерии за годината в горния пример. Ако добавим и критерии за годината, тя ще работи.
Общата формула за получаване на сума по месец и година в Excel
= SUMPRODUCT (sum_range,-(TEXT (date_range, "MMM") = month_text)),-(TEXT (date_range, "yyyy") = TEXT (година, 0)))
Тук добавихме още един критерий, който проверява годината. Всичко останало е същото.
Нека решим горния пример, напишете тази формула в клетка E1, за да получите сумата от януари през 2017 година.
= СУМПРОДУКТ (B2: B9,-(ТЕКСТ (A2: A9, "MMM") = E1),-(TEXT (A2: A9, "гггг") = ТЕКСТ (D2,0)))
Преди да копирате в клетките по -долу използвайте именувани диапазони или абсолютни препратки. В изображението съм използвал именовани диапазони за копиране в съседните клетки.
Сега можем да видим и сумата на стойността по месеци и години.
Как работи?
Първата част на формулата е същата като предишния пример. Нека разберем допълнителната част, която добавя критериите за годината.
-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)): TEXT (A2: A9, "yyyy") преобразува датата в A2: A9 като години в текстов формат в масив. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
През повечето време годината се изписва в числов формат. За да сравним число с текст, ние преобразувахме int текст на година, използвайки TEXT (D2,0). След това сравнихме тази текстова година с масив от години като TEXT (A2: A9, "yyyy") = TEXT (D2,0). Това връща масив от true-false {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. След това преобразувахме true false в число с помощта на - operator. Това ни дава {0; 0; 1; 1; 0; 1; 0; 1}.
Така че накрая формулата ще бъде преведена на = SUMPRODUCT (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Където първият масив е стойности. Следващият е съпоставен месец, а третият е година. Накрая получаваме нашата сума от стойности като 2160.
Използване на функцията SUMIFS за сумиране по месец
Обща формула
= SUMIFS (sum_range, date_range, ”> =” & startdate, date_range, ”<=” & EOMONTH (start_date, 0))
Тук,Sum_range : Това е диапазонът, който искате да сумирате по месеци.
Период от време : Това е периодът от време, в който ще търсите месеци.
Начална дата : Това е началната дата, от която искате да сумирате. За този пример това ще бъде 1 -во от дадения месец.
Пример: Сумирани стойности по месеци в Excel
Тук имаме някаква стойност, свързана с дати. Тези дати са месец януари, февруари и март на 2019 година.
Просто трябва да сумираме тези стойности до месец. Сега беше лесно, ако имахме месеци и години отделно. Но те не са. Не можем да използваме помощна колона тук.
Така че, за да изготвя доклад, подготвих формат на отчет, който съдържа месец и сума от стойности. В колоната за месец всъщност имам начална дата на месеца. За да видите само месеца, изберете начална дата и натиснете CTRL+1.
В персонализиран формат напишете „ммм“.
Сега имаме готови нашите данни. Нека сумираме стойностите по месеци.
Напишете тази формула в E3, за да сумирате по месеци.
= SUMIFS (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Използвайте абсолютни препратки или наименувани диапазони, преди да копирате формулата.
И така, най -накрая получихме резултата.
И така, как работи?
Както знаем, функцията SUMIFS може да сумира стойности по множество критерии.
В горния пример първият критерий е сумиране на всички стойности в B3: B10, където датата в A3: A10 е по -голяма или равна на датата в D3. D3 съдържа 1-януари. Това също се превежда.
= SUMIFS (B3: B10, A3: A10, "> =" & "1-jan-2019", A3: A10, "<=" EOMONTH (D3,0))
Следващите критерии се сумират само ако Date in A3: A10 е по -малко или равно на EOMONTH (D3,0). Функцията EOMONTH просто връща сериен номер на последната дата на предоставения месец. Най -накрая формулата също се превежда.
= SUMIFS (B3: B10, A3: A10, "> = 1-jan-2019", A3: A10, "<= 31-jan-2019")
Следователно получаваме сумата по месеци в Excel.
Предимството на този метод е, че можете да регулирате началната дата за сумиране на стойностите.
Ако вашите дати имат различни години, най -добре е да използвате обобщени таблици. Обобщените таблици могат лесно да ви помогнат да разделите данните в годишен, тримесечен и месечен формат.
Така че да, момчета, ето как можете да сумирате стойностите по месеци. И двата начина имат свои собствени специалитети. Изберете кой начин ви харесва.
Ако имате въпроси относно тази статия или други заявки, свързани с Excel и VBA, секцията за коментари е отворена за вас.
Свързани статии:
Как да използвате функцията SUMIF в Excel
SUMIFS с дати в Excel
SUMIF с непразни клетки
Как да използвате функцията SUMIFS в Excel
SUMIFS използва логика ИЛИ ИЛИ
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност: По -бързо изпълнете задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате tФункцията VLOOKUP в Excel: Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията COUNTIF в Excel: Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel: Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.