Преузмите пример радне свеске
Овај водич ће показати како израчунати међузбројеве по групама помоћу функције СУМИФС у Екцелу и Гоогле таблицама.
Табела међузбира према категорији или групи
Прво ћемо показати како да креирате динамичку збирну табелу међузбира из опсега података у програму Екцел 365 па надаље или у Гоогле табелама.
Користимо УНИКУЕ функцију и СУМИФС функцију за аутоматски међузбир Број производа од стране Група производа:
| 1 | = СУМИФС (Ц3: Ц11, Б3: Б11, Е3) |
За креирање ове табеле међузбира користимо стандардну апликацију функције СУМИФС да збројимо Број производа који се међусобно подударају Група производа. Међутим, пре него што је ово могуће, морамо да направимо листу јединствених Групе производа. Корисници Мицрософт Екцел 365 и Гоогле табела имају приступ функцији УНИКУЕ за креирање динамичке листе јединствених вредности из опсега ћелија. У овом примеру, у ћелију Е3 додајемо следећу формулу:
| 1 | = ЈЕДИНСТВЕНО (Б3: Б11) |
Када се унесе ова формула, испод ћелије се аутоматски ствара листа која приказује све јединствене вредности пронађене у Група производа опсег података. У овом примеру листа се проширила на Е3: Е5 тако да приказује све 3 јединствене Група производа вредности.
Ово је функција динамичког низа у којој величина листе резултата не мора бити дефинирана, а она ће се аутоматски смањивати и расти како се вриједности улазних података мијењају.
Имајте на уму да у програму Екцел 365 функција УНИКУЕ не разликује велика и мала слова, али у Гоогле таблицама јесте. Размотрите листу {„А“; „А“; “Б”; „Ц“}. Излаз функције УНИКУЕ зависи од програма:
- {"А"; “Б”; „Ц“} у програму Екцел 365
- {"А"; „А“; “Б”; „Ц“} у Гоогле табелама
Ако користите Екцел верзију пре Екцел 365, мораћете да примените другачији приступ. О овоме се говори у следећем одељку.
Табела међузбира по категоријама или групама - пре Екцел 365
Ако користите верзију програма Екцел пре Екцел 365, функција УНИКУЕ није доступна за употребу. Да бисте реплицирали исто понашање, можете комбиновати функцију ИНДЕКС и функцију МАТЦХ са функцијом ЦОУНТИФ да бисте креирали формулу низа за израду листе јединствених вредности из низа ћелија:
| 1 | {= ИНДЕКС ($ Б $ 3: $ Б $ 11, МАТЦХ (0, ЦОУНТИФ ($ Е $ 2: Е2, $ Б $ 3: $ Б $ 11), 0))} |
Да би ова формула функционисала, фиксне референце ћелија морају бити пажљиво написане, при чему функција ЦОУНТИФ упућује на опсег $ Е $ 2: Е2, који је опсег који почиње од Е2 до ћелије изнад ћелије која садржи формулу.
Формулу такође треба унети као формулу низа притиском на ЦТРЛ + СХИФТ + ЕНТЕР након што је написана. Ова формула је а Формула поља са 1 ћелије, које се затим могу копирати у ћелије Е4, Е5 итд. Не уносите ово као формулу низа за цео опсег Е3: Е5 у једној радњи.
На исти начин као у претходном примеру, функција СУМИФС се затим користи за међузбир Број производа од стране Група производа:
| 1 | = СУМИФС (Ц3: Ц11, Б3: Б11, Е3) |
Збир према категорији или групи - међузбири у табелама података
Као алтернатива горе приказаној методи сумарне табеле, можемо додати међузбире директно у табелу података. То ћемо показати коришћењем ИФ функција заједно са СУМИФС функцијом за додавање а Међузбир по групама у оригиналну табелу података.
| 1 | = ИФ (Б3 = Б2, "", СУМИФС (Ц3: Ц11, Б3: Б11, Б3)) |
Овај пример користи СУМИФС функцију угнежђену унутар ИФ функције. Поделимо пример на кораке:
За додавање збирне статистике директно у табелу података можемо користити функцију СУМИФС. Почињемо са збрајањем Број производа који одговарају релевантним Група производа:
| 1 | = СУМИФС (Ц3: Ц11, Б3: Б11, Б3) |
Ова формула производи међузбирну вредност за сваки ред података. За приказивање међузбројева само у првом реду података сваког Група производа, користимо ИФ функцију. Имајте на уму да се подаци морају већ сортирати према Група производа како би се осигурало да су међузбири исправно приказани.
| 1 | = ИФ (Б3 = Б2, "", СУМИФС (Ц3: Ц11, Б3: Б11, Б3)) |
ИФ функција упоређује сваки ред података Група производа вредност са редом података изнад њега, а ако имају исту вредност, приказује празну ћелију („”).
Ако је Група производа вредности су различите, приказује се збир. На овај начин, сваки Група производа збир се приказује само једном (у реду прве инстанце).
Сортирање скупова података по групама
Ако подаци већ нису сортирани, и даље можемо користити исту формулу за међузбир.
Горњи скуп података није сортиран према Група производа, тако да Међузбир по групама колона приказује сваки међузбир више пута. Да бисмо податке добили у жељеном формату, можемо изабрати табелу података и кликнути на „Сортирај од А до З“.
Закључавање ћелијских референци
Да бисмо наше формуле учинили лакшим за читање, приказали смо неке од формула без закључаних референци ћелија:
| 1 | = ИФ (Б3 = Б2, "", СУМИФС (Ц3: Ц11, Б3: Б11, Б3)) |
Али ове формуле неће радити исправно када их копирате и залепите на друго место у датотеци. Уместо тога, требало би да користите закључане ћелијске референце попут ове:
| 1 | = ИФ (Б3 = Б2, "", СУМИФС ($ Ц $ 3: $ Ц $ 11, $ Б $ 3: $ Б $ 11, Б3)) |
Прочитајте наш чланак о закључавању ћелијских референци да бисте сазнали више.
Коришћење изведених табела за приказивање међузбира
Да бисте уклонили захтев за претходно сортирање података према Група производа, уместо тога можемо да искористимо моћ изведених табела да резимирамо податке. Заокретне табеле аутоматски израчунавају међузбире и приказују укупне и међузбире у неколико различитих формата.
Збир према категорији или групи у Гоогле табелама
Ове формуле раде исто у Гоогле таблицама као и у Екцелу. Међутим, УНИКУЕ функција разликује велика и мала слова у Гоогле таблицама.
