До сада смо научили како да збројимо целу одговарајућу колону табеле у Екцелу. Али како да сумирамо вредности када треба да подударамо колону и ред. У овом чланку ћемо научити како да направите збир одговарајућих редова и колона.
Постоје две формуле за то, али прво, хајде да размотримо сценарио.
Овде имам табелу која бележи продају коју су запослени обавили у различитим месецима. Име запослених може да се понови. Погледајте доњу слику:
Морамо да добијемо збир маја месеца у коме је продавац Доналд.
Метод 1: Збрајање одговарајућег заглавља колоне и заглавља помоћу функције СУМПРОДУЦТ.
Тхе СУМПРОДУЦТ функција је најсвестранија функција када се ради о збрајању и пребројавању вредности са лукавим критеријумима. Општа функција која се сабира подударањем колоне и реда је:
| = СУМПРОДУЦТ ((колоне)*(заглавља_ступаца = заглавље_ступце)*(заглавља_реда = заглавље_реда) |
Колоне:То је дводимензионални опсег колона које желите да сумирате. Не би требало да садржи заглавља. У горњој табели је Ц3: Н7.
заглавља колона:То је опсег заглављаколоне које желите да сумирате. У горњим подацима то је Ц2: Н2.
цолумн_хеадинг: То је наслов који желите да упоредите. У горњем примеру, то је у Б13.
Без даљег одлагања употребимо формулу.
ред_хеадерс:То је опсег заглављаредове које желите да сумирате. У горњим подацима то је Б3: Б10.
ров_хеадинг: То је наслов који желите да упоредите у редовима. У горњем примеру, то је у Ф13.
Без даљег одлагања употребимо формулу.
Напишите ову формулу у ћелију Д13 и допустите Екцелу да учини магију (не постоји магија) …
| = СУМПРОДУЦТ ((Ц3: Н10)*(Ц2: Н2 = Б13)*(Б3: Б10 = Е13)) |
Ово враћа вредност:
Сада, када промените месец или продавца, збир ће се променити према наслову реда и наслову колоне.
Како то функционише?
Ова једноставна логичка логика.
(Ц2: Н2 = Б13): Ова наредба ће вратити низ ТРУЕ и ФАЛСЕ. Све подударне вредности у колони имају вредност труе, а остале ће имати вредност фалсе. У овом случају имат ћемо само једно Труе јер распон Ц2: Н2 садржи само једну инстанцу Маи на 5тх Лоцатион.
(Б3: Б10 = Е13): Ово ће радити исто као горе и вратиће низ ТРУЕ и ФАЛСЕ. Све одговарајуће вредности ће имати ТРУЕ, а остале ће имати ФАЛСЕ. У овом случају имаћемо 2 тачне вредности као опсег Б3: Б10 има две инстанце „Доналда“.
(Ц2: Н2 = Б13)*(Б3: Б10 = Е13): Сада множимо низове враћене изразима. Ово ће имплементирати и логику и добићемо низ од 1 и 0. Сада ћемо имати 2Д низ који ће садржати 2 1 и остатак 0.
(Ц3: Н10)*(Ц2: Н2 = Б13)*(Б3: Б10 = Е13)= На крају множимо 2Д низ са 2Д табелом. Поново ће вратити низ 0 и бројеве који одговарају критеријумима.
Коначно, СУМПРОДУЦТ функција ће сумирати низ који ће резултирати жељеним излазом.
Метод 2: Збрајање одговарајућег заглавља колоне и заглавља помоћу функције СУМ и ИФ
Општа формула за збрајање одговарајућих редова и колона помоћу функција СУМ и ИФ Екцел је:
| = СУМ (ИФ (цолумн_хеадерс = цолумн_хеадинг, ИФ (ров_хеадерс = ров_хеадинг, цолумнс))) |
Све променљиве су исте као у горе објашњеној методи. Овде их само треба користити различитим редоследом.
Напишите ову формулу у ћелију Д13:
| = СУМ (ИФ (Ц2: Н2 = Б13, ИФ (Б3: Б10 = Е13, Ц3: Н10))) |
Ово враћа тачан одговор. Погледајте снимак екрана испод:
Како то функционише?
Логика је иста као прва метода СУМПРОДЦУТ само је механизам другачији. Ако то укратко објасним, унутрашња ИФ функција враћа 2Д низ исте димензије као и табела. Овај низ садржи број два подударна реда. Затим унутрашња функција ИФ одговара насловима са две колоне у овом низу и враћа 2Д низ који садржи само бројеве који одговарају и колони и заглављу. Сви остали елементи низа ће бити ФАЛСЕ.
Коначно, функција СУМ сумира овај низ и добијамо наш збир.
Па да, момци, на овај начин можете сажети одговарајуће редове и колоне из табеле у Екцелу. Надам се да вам је било објашњење и корисно. Ако имате било каквих недоумица у вези са овом темом или било које друге сумње везане за Екцел/ВБА, питајте у одељку за коментаре испод.
Како збројити колону у Екцелу подударањем наслова | Ако желите да добијете збир колоне само помоћу назива колоне, то можете учинити на 3 једноставна начина у Екцелу. Синтакса методе СУМПРОДУЦТ за сабирање одговарајуће колоне је:
СУМИФ са 3Д референцом у Екцелу |Забавна је чињеница да уобичајено Екцел 3Д референцирање не ради са условним функцијама, попут функције СУМИФ. У овом чланку ћемо научити како да 3Д референцирање ради са функцијом СУМИФ.
Релативна и апсолутна референца у Екцелу | Референцирање у екцелу важна је тема за сваког почетника. Чак и искусни екцел корисници греше у референцирању.
Референца динамичког радног листа | Дајте референтне листове динамички користећи ИНДИРЕЦТ функцију програма Екцел. Ово је једноставно…
Проширивање референци у Екцелу | Проширујућа референца се шири када се копира надоле или надесно. За то користимо знак $ пре броја колоне и реда. Ево једног примера…
Све о апсолутним референцама | Подразумевани тип референце у екцелу је релативан, али ако желите да референца ћелија и опсега буде апсолутна, користите знак $. Ево свих аспеката апсолутног позивања у Екцелу.
Популарни чланци:
50 Екцел пречица за повећање продуктивности | Убрзајте свој задатак. Ових 50 пречица ће ваш рад учинити још бржим у Екцелу.
Функција ВЛООКУП у Екцелу | Ово је једна од најчешће кориштених и популарних функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
ЦОУНТИФ у програму Екцел 2016 | Бројте вредности са условима користећи ову невероватну функцију. Не морате филтрирати податке да бисте рачунали одређену вредност. Цоунтиф функција је неопходна за припрему ваше контролне табле.
Како се користи функција СУМИФ у програму Екцел | Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.