Преузмите пример радне свеске
Овај водич ће показати како се користи функција СУМИФС за сумирање података који одговарају одређеним месецима у Екцелу и Гоогле табелама.
Зброј ако по месецу
Прво ћемо показати како се сумирају подаци који одговарају датумима који падају у одређени месец и годину.
Можемо користити функцију СУМИФС, заједно са функцијама ДАТЕ, ИЕАР, МОНТХ и ЕОМОНТХ да збројимо Број продаје унутар сваког Месец дана.
| 1 | = СУМИФС (Ц3: Ц9, Б3: Б9, "> =" & ДАТЕ (ГОДИНА (Е3), МЕСЕЦ (Е3), 1), Б3: Б9, "<=" & ЕОМОНТХ (Е3,0)) |
Да бисмо изградили горњу формулу, почињемо са дефинисањем распона датума за сваки месец. Користимо функцију ДАТЕ да дефинишемо први дан у месецу (нпр. 5/1/2021). То можемо учинити тако што ћемо „тешко кодирати“ датум у формулу:
| 1 | "> =" & ДАТЕ (2021,5,1) |
Или, још боље, можемо да учинимо критеријуме флексибилним позивањем на датум унутар ћелије Е3, издвајањем године и месеца датума (и постављањем дана једнаким 1), као што је приказано у горњем примеру:
| 1 | "> =" & ДАТУМ (ГОДИНА (Е3), МЕСЕЦ (Е3), 1) |
За дефинисање последњег дана у месецу можемо користити функцију ЕОМОНТХ:
| 1 | "<=" & ЕОМОНТХ (Е3,0) |
Спајајући све ове критеријуме, можемо написати следећу формулу СУМИФС:
| 1 | = СУМИФС (Ц3: Ц9, Б3: Б9, "> =" & ДАТЕ (ГОДИНА (Е3), МЕСЕЦ (Е3), 1), Б3: Б9, "<=" & ЕОМОНТХ (Е3,0)) |
Закључавање ћелијских референци
Да бисмо олакшали читање наших формула, приказали смо формуле без закључаних референци ћелија:
| 1 | = СУМИФС (Ц3: Ц9, Б3: Б9, "> =" & ДАТЕ (ГОДИНА (Е3), МЕСЕЦ (Е3), 1), Б3: Б9, "<=" & ЕОМОНТХ (Е3,0)) |
Али ове формуле неће радити исправно када их копирате и залепите на друго место у датотеци. Уместо тога, требало би да користите закључане ћелијске референце попут ове:
| 1 | = СУМИФС ($ Ц $ 3: $ Ц $ 9, $ Б $ 3: $ Б $ 9, "> =" & ДАТЕ (ИЕАР (Е3), МОНТХ (Е3), 1), $ Б $ 3: $ Б $ 9, "<=" & ЕОМОНТХ (Е3,0)) |
Прочитајте наш чланак о закључавању ћелијских референци да бисте сазнали више.
Форматирање месечних вредности
У овом примеру, навели смо месеце у колони Е. Ове месечне вредности су заправо датуми обликовани тако да изостављају дан, користећи обликовање прилагођеног броја.
Прилагођени формат података је „ммм гггг“ за приказивање маја 2022.
Збир по месецима током више година
Горњи пример сумира податке са датумима који су падали унутар одређеног месеца и године. Уместо тога, помоћу функције СУМПРОДУЦТ можете сумирати податке са датумима који падају у року од месец дана у било којој години.
| 1 | = СУМПРОДУЦТ (Ц3: Ц8,-(МЕСЕЦ (Б3: Б8) = МЕСЕЦ (Г3))) |
У овом примеру користимо функцију СУМПРОДУЦТ за извођење компликованих прорачуна „збир ако“. Прођимо кроз формулу.
Ово је наша коначна формула:
| 1 | = СУМПРОДУЦТ (Ц3: Ц8,-(МЕСЕЦ (Б3: Б8) = МЕСЕЦ (Г3))) |
Прво, функција СУМПРОДУЦТ наводи листу Број продаје за сваки Датум продаје а затим упоређује сваки месец Датум продаје против наведеног Месец дана, враћа ТРУЕ ако се месеци подударају, или ФАЛСЕ ако се не подударају:
| 1 | = СУМПРОДУЦТ ({30; 42; 51; 28; 17; 34},-({ТРУЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ})) |
Затим двоструке цртице (-) претварају ТРУЕ и ФАЛСЕ вредности у 1с и 0с:
| 1 | = СУМПРОДУЦТ ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Функција СУМПРОДУЦТ затим множи сваки пар уноса у низовима:
| 1 | = СУМПРОДУЦТ ({30; 0; 51; 0; 17; 0}) |
Коначно, бројеви у низу се сабирају:
| 1 | =98 |
Више детаља о коришћењу логичких исказа и команде “-” у функцији СУМПРОДУЦТ можете пронаћи овде
Зброј ако по месецима у Гоогле табелама
Ове формуле раде потпуно исто у Гоогле таблицама као и у Екцелу.
