Много пута желимо да израчунамо неке вредности по месецима. На пример, колико је продаје обављено у пертикуларном месецу. Па, ово се може лако урадити помоћу изведених табела, али ако покушавате да имате динамички извештај онда можемо користити формулу СУМПРОДУЦТ или СУМИФС за сумирање по месецима.
Почнимо са СУМПРОДУЦТ решењем.
Ево опште формуле за добијање збира по месецима у Екцелу
= СУМПРОДУЦТ (сум_ранге,-(ТЕКСТ (ранге_ дате, "МММ") = монтх_тект))
Сум_ранге : То је распон који желите збројити по мјесецима.
Дате_ранге : То је период у којем ћете гледати месецима.
Месец_текст: То је месец у текстуалном формату за који желите да сумирате вредности.
Погледајмо сада пример:
Пример: Збирне вредности по месецима у Екцелу
Овде имамо неку вредност повезану са датумима. Ови датуми су јануар, фебруар и март месеца 2019.
Као што можете видети на горњој слици, сви датуми су 2019. година. Сада само треба да сумирамо вредности у Е2: Г2 по месецима у Е1: Г1.
Сада да збројимо вриједности према мјесецима напишите ову формулу у Е2:
= СУМПРОДУЦТ (Б2: Б9,-(ТЕКСТ (А2: А9, "МММ") = Е1)))
Ако желите да га копирате у суседне ћелије, користите апсолутне референце или именоване опсеге као на слици.
Ово нам даје тачан збир сваког месеца.
Како то ради?
Полазећи изнутра, погледајмо ТЕКСТ (А2: А9, "МММ") парт. Овде функција ТЕКСТ издваја месец из сваког датума у опсегу А2: А9 у текстуалном формату у низ. Превођење у формулу = СУМПРОДУЦТ (Б2: Б9,-({"Јан"; "Јан"; "Феб"; "Јан"; "Феб"; "Мар"; "Јан"; "Феб"} = Е1) )
Затим, ТЕКСТ (А2: А9, "МММ")= Е1: Овде се сваки месец у низу упоређује са текстом у Е1. Пошто Е1 садржи „Јан“, сваки „Јан“ у низу се претвара у ТРУЕ, а други у ФАЛСЕ. Ово формулу преводи у = СУМПРОДУЦТ ($ Б $ 2: $ Б $ 9,-{ТРУЕ; ТРУЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ})
Следеће -(ТЕКСТ (А2: А9, "МММ") = Е1), претвара ТРУЕ ФАЛСЕ у бинарне вредности 1 и 0. Формула се преводи у = СУМПРОДУЦТ ($ Б $ 2: $ Б $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Коначно СУМПРОДУЦТ($ Б $ 2: $ Б $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): функција СУМПРОДУЦТ множи одговарајуће вредности у $ Б $ 2: $ Б $ 9 у низ {1; 1; 0; 1; 0; 0; 1; 0} и сабира их. Отуда добијамо збир по вредности као 20052 у Е1.
ЗБИРАЈТЕ Месеце из различите године
У горњем примеру сви датуми су из исте године. Шта ако су из различитих година? Горња формула ће сумирати вредности по месецима, без обзира на годину. На пример, додаћемо јануар 2018. и јануар 2019. ако користимо горњу формулу. Што је у већини случајева погрешно.
То ће се догодити јер у горњем примеру немамо критеријуме за годину. Ако додамо и критеријуме за годину, то ће успети.
Општа формула за добијање збира по месецима и годинама у Екцелу
= СУМПРОДУЦТ (сум_ранге,-(ТЕКСТ (ранге_ дате, "МММ") = монтх_тект),-(ТЕКСТ (дате_ранге, "ииии") = ТЕКСТ (година, 0)))
Овде смо додали још један критеријум који проверава годину. Све остало је исто.
Решимо горњи пример, напишите ову формулу у ћелију Е1 да бисте добили збир јануара 2017. године.
= СУМПРОДУЦТ (Б2: Б9,-(ТЕКСТ (А2: А9, "МММ") = Е1),-(ТЕКСТ (А2: А9, "ииии") = ТЕКСТ (Д2,0)))
Пре копирања у доње ћелије користите именоване опсеге или апсолутне референце. На слици сам користио именоване опсеге за копирање у суседне ћелије.
Сада можемо видети и збир вредности по месецима и годинама.
Како то функционише?
Први део формуле је исти као и претходни пример. Да разумемо додатни део који додаје критеријуме за годину.
-(ТЕКСТ (А2: А9, "ииии") = ТЕКСТ (Д2,0)): ТЕКСТ (А2: А9, "ииии") претвара датум у А2: А9 као године у текстуалном формату у низ. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Већину времена година је написана у нумеричком формату. Да бисмо упоредили број са текстом, претворили смо инт текст године користећи ТЕКСТ (Д2,0). Затим смо ову текстуалну годину упоредили са низом година као ТЕКСТ (А2: А9, "ииии") = ТЕКСТ (Д2,0). Ово враћа низ труе-фалсе {ФАЛСЕ; ФАЛСЕ; ТРУЕ; ТРУЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ; ТРУЕ}. Затим смо претворили труе фалсе у број користећи - оператор. Ово нам даје {0; 0; 1; 1; 0; 1; 0; 1}.
Тако ће коначно формула бити преведена у = СУМПРОДУЦТ (Б2: Б9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Где су први низ вредности. Следећи је одговарајући месец, а трећи је година. Коначно добијамо збир вредности као 2160.
Коришћење функције СУМИФС за сумирање по месецима
Општа формула
= СУМИФС (сум_ранге, дате_ранге, ”> =” & стартдате, дате_ранге, ”<=” & ЕОМОНТХ (датум_почетка, 0))
Овде,Сум_ранге : То је распон који желите збројити по мјесецима.
Дате_ранге : То је период у којем ћете гледати месецима.
Датум почетка : То је датум почетка са којег желите да збројите. За овај пример, то ће бити 1. у датом месецу.
Пример: Збирне вредности по месецима у Екцелу
Овде имамо неку вредност повезану са датумима. Ови датуми су јануар, фебруар и март месеца 2019.
Само треба да збројимо ове вредности до тог месеца. Сада је било лако ако смо имали месеце и године одвојено. Али нису. Овде не можемо да користимо никакву колону за помоћ.
Зато сам за припрему извештаја припремио формат извештаја који садржи месец и збир вредности. У колони месец, заправо имам датум почетка месеца. Да бисте само видели месец, изаберите датум почетка и притисните ЦТРЛ+1.
У прилагођеном формату напишите „ммм“.
Сада су наши подаци спремни. Збројимо вредности по месецима.
Напишите ову формулу у Е3 да бисте сабрали по месецима.
= СУМИФС (Б3: Б10, А3: А10, "> =" & Д3, А3: А10, "<=" & ЕОМОНТХ (Д3,0))
Користите апсолутне референце или именоване опсеге пре него што препишете формулу.
Дакле, коначно смо добили резултат.
Дакле, како то функционише?
Као што знамо да функција СУМИФС може да сумира вредности по више критеријума.
У горњем примеру, први критеријум сумира све вредности у Б3: Б10 где је датум у А3: А10 већи или једнак датуму у Д3. Д3 садржи 1-Јан. Ово се такође преводи.
= СУМИФС (Б3: Б10, А3: А10, "> =" & "1-јан-2019", А3: А10, "<=" ЕОМОНТХ (Д3,0))
Следећи критеријуми су збирни само ако је датум у А3: А10 је мањи или једнак ЕОМОНТХ (Д3,0). Функција ЕОМОНТХ само враћа серијски број задњег датума у мјесецу. Коначно се преводи и формула.
= СУМИФС (Б3: Б10, А3: А10, "> = 1-јан-2019", А3: А10, "<= 31-јан-2019")
Дакле, добијамо збир по месецима у екцелу.
Предност ове методе је што можете подесити датум почетка за сумирање вредности.
Ако ваши датуми имају различите године, најбоље је користити заокретне табеле. Заокретне табеле могу вам помоћи да лако раздвојите податке у годишњем, кварталном и месечном формату.
Па да, момци, овако можете збројити вриједности по мјесецима. Оба начина имају своје специјалитете. Изаберите који начин желите.
Ако имате било каквих питања у вези са овим чланком или било којим другим питањима везаним за Екцел и ВБА, одељак за коментаре је отворен за вас.
Везани чланци:
Како се користи функција СУМИФ у програму Екцел
СУМИФС са датумима у Екцелу
СУМИФ са ћелијама које нису празне
Како се користи функција СУМИФС у програму Екцел
ЗБИРУЈЕ помоћу АНД-ИЛИ логике
Популарни чланци
50 Екцел пречица за повећање ваше продуктивности: Убрзајте свој задатак. Ових 50 пречица ће учинити да радите још брже на Екцелу.
Како се користи тФункција ВЛООКУП у Екцелу: Ово је једна од најчешће кориштених и најпопуларнијих функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
Како се користи функција ЦОУНТИФ у програму Екцел: Бројте вредности са условима користећи ову невероватну функцију. Не морате да филтрирате податке да бисте рачунали одређене вредности. Цоунтиф функција је неопходна за припрему ваше контролне табле.
Како се користи функција СУМИФ у програму Екцел: Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.