Преузмите пример радне свеске
Овај водич показује како се користи Екцел СУМПРОДУЦТ функција у програму Екцел.
СУМПРОДУЦТ Преглед функција
Функција СУМПРОДУЦТ множи низове бројева и сумира резултујући низ.
Да бисте користили функцију СУМПРОДУЦТ Екцел радног листа, изаберите ћелију и откуцајте:
(Обратите пажњу на то како се појављују уноси формуле)
Функција СУМПРОДУЦТ Синтакса и улази:
| 1 | = СУМПРОДУЦТ (низ1, низ2, низ3) |
арраи1 - Низови бројева.
Шта је функција СУМПРОДУЦТ?
Функција СУМПРОДУЦТ једна је од моћнијих функција у програму Екцел. То је име, могло би вас навести да верујете да је намењено само основним математичким прорачунима, али може се користити за много више.
Арраис
СУМПРОДУЦТ захтева уносе низа.
Дакле, прво, шта подразумевамо под „низом“? Низ је једноставна група ставки (нпр. Бројеви) распоређених у одређеном редоследу, баш као и низ ћелија. Дакле, да имате бројеве 1, 2, 3 у ћелијама А1: А3, Екцел би ово прочитао као низ {1,2,3}. У ствари, можете унети {1,2,3} директно у Екцел формуле и она ће препознати низ.
У наставку ћемо говорити више о низовима, али прво погледајмо једноставан пример.
Основна математика
Погледајмо основни пример СУМПРОДУЦТ -а, користећи га за израчунавање укупне продаје.
Имамо нашу таблицу производа и желимо израчунати укупну продају. Бићете у искушењу да само додате нову колону, узмете продату количину * цену, а затим сумирате нову колону. Уместо тога, можете једноставно користити функцију СУМПРОДУЦТ. Прођимо кроз формулу:
| 1 | = СУМПРОДУЦТ (А2: А4, Б2: Б4) |
Функција ће учитати опсеге бројева у низове, помножити их један са другим, а затим збрајати резултате:
| 1234 | = СУМПРОДУЦТ ({100, 50, 10}, {6, 7, 5})= СУМПРОДУЦТ ({100 * 6, 50 * 7, 10 * 5})= СУМПРОДУЦТ ({600, 350, 50}= 1000 |
Функција СУМПРОДУЦТ је успела да нам помножи све бројеве И изврши сабирање.
Пондерисана
Још један случај када је корисно користити СУМПРОДУЦТ је када морате израчунати пондерисани просек. То се најчешће дешава при бављењу школским задацима, па размотримо следећу табелу.
Можемо видети колико квизови, тестови и домаћи задаци вреде за укупну оцену, као и колики је тренутни просек за сваку поједину ставку. Укупну оцену тада можемо израчунати писањем
| 1 | = ПОДИЗВОД (Б2: Б4, Ц2: Ц4) |
Наша функција поново множи сваку ставку у низовима пре сабирања укупног броја. Ово функционише тако
| 123 | = СУМПРОДУЦТ ({30%, 50%, 20%}, {73%, 90%, 95%})= СУМПРОДУЦТ ({22%, 45%, 19%})= 86% |
Више колона
Још једно место које бисмо могли да користимо је СУМПРОДУЦТ са још више колона које је потребно помножити једна са другом. Погледајмо пример где морамо израчунати запремину у комадима дрвета.
Уместо да стварамо помоћну колону за израчунавање укупне продаје за сваки ред, то можемо учинити помоћу једне формуле. Наша формула ће бити
| 1 | = ПОДИЗВОД (Б2: Б5, Ц2: Ц5, Д2: Д5) |
Прве ставке сваког низа множиће се једна с другом (нпр. 4 * 2 * 1 = 8). Затим, 2. (4 * 2 * 2 = 16) и 3рдитд. Све у свему, ово ће произвести низ производа који изгледају као {8, 16, 16, 32). Тада би укупна запремина била збир тог низа, 72.
Један критеријум
У реду, додајмо још један слој сложености. Видели смо да СУМПРОДУЦТ може да обрађује низове бројева, али шта ако желимо да проверимо критеријуме? Па, такође можете да креирате низове за логичке вредности (Боолеан вредности су вредности које су ТРУЕ или ФАЛСЕ).
На пример, узмите основни низ {1, 2, 3}. Направимо одговарајући низ који означава да ли је сваки број већи од 1. Овај низ би изгледао као {ФАЛСЕ, ТРУЕ, ТРУЕ}.
Ово је изузетно корисно у формулама, јер можемо лако претворити ТРУЕ / ФАЛСЕ у 1 / 0. Погледајмо пример.
Користећи доњу табелу, желимо да израчунамо „Колико је продатих јединица било црвено?“
То можемо учинити помоћу ове формуле:
| 1 | = СУМПРОДУЦТ (А2: А4, -(Б2: Б4 = "Црвено")) |
"Држи се! Шта је са симболом двоструки минус? " кажете. Сећате ли се како сам рекао да можемо претворити из Тачно/Нетачно у 1/0? То радимо тако што компјутер приморавамо да изврши математичку операцију. У овом случају кажемо „узми негативну вредност, а затим опет негативну“. Када то испишемо, наш низ ће се променити овако:
| 123 | {Тачно, Тачно, Нетачно}{-1, -1, 0}{1, 1, 0} |
Дакле, назад на пуну формулу СУМПРОДУЦТ, она ће се учитати у наше низове, а затим помножити, овако
| 123 | = СУМПРОДУЦТ ({100, 50, 10}, {1, 1, 0})= СУМПРОДУЦТ ({100, 50, 0})= 150 |
Запазите како је 3рд ставка је постала 0, јер све помножено са 0 постаје нула.
Више критеријума
У нашу функцију можемо учитати до 255 низова, тако да свакако можемо учитати више критеријума. Погледајмо ову већу табелу у коју смо додали месец продаје.
Ако желимо да знамо колико је продатих артикала било црвено и били у месецу фебруару, могли бисмо написати нашу формулу као
| 1 | = СУМПРОДУЦТ (А2: А4, -(Б2: Б4 = "Ред"), -(Ц2: Ц4 = "Феб")) |
Рачунар би тада проценио наше низове и помножио се попречно. Већ смо описали како се Труе/Фалсе низови мењају у 1/0, па ћу за сада прескочити тај корак.
| 123 | = СУМПРОДУЦТ ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= СУМПРОДУЦТ ({0, 50, 0})= 50 |
У нашем примеру смо имали само један ред који је одговарао свим критеријумима, али са стварним подацима можда сте имали више редова који су вам потребни заједно.
Сложени критеријуми
У реду, до овог тренутка можда нећете бити импресионирани јер су сви наши примјери могли бити изведени употребом других функција попут СУМИФ или ЦОУНТИФ. Сада ћемо учинити нешто те друге функције не може урадити. Раније је наша колона Месец имала стварне називе месеци. Шта ако уместо тога има датуме?
Не можемо сада да радимо СУМИФ, јер СУМИФ не може да испуни критеријуме који су нам потребни. СУМПРОДУЦТ ипак може да се носи са нама манипулишући низом и радећи дубљи тест. Већ смо манипулисали низовима када смо превели Тачно/Нетачно у 1/0. Овим низом ћемо манипулисати функцијом МОНТХ. Ево потпуне формуле коју ћемо користити
| 1 | = СУМПРОДУЦТ (А2: А4, -(Б2: Б4 = "Ред"), -(МЕСЕЦ (Ц2: Ц4) = 2)) |
Погледајмо 3рд низ ближе. Прво, наша формула ће издвојити број месеца из сваког датума у Ц2: Ц4. Ово ће нам дати {1, 2, 2}. Затим проверавамо да ли је та вредност једнака 2. Сада наш низ изгледа као {Фалсе, Труе, Труе}. Поново радимо двоструки минус и имамо {0, 1, 1}. Сада смо се вратили на слично место које смо имали у примеру 3, а наша формула ће нам моћи рећи да је у фебруару продато 50 јединица које су биле црвене.
Двоструки минус наспрам множења
Ако сте већ видели функцију СУМПРОДУЦТ у употреби, можда сте видели мало другачију нотацију. Уместо да користите двоструки минус, можете писати
| 1 | = СУМПРОДУЦТ (А2: А4*(Б2: Б4 = "Ред")*(МЕСЕЦ (Ц2: Ц4) = 2)) |
Формула ће и даље радити на исти начин, само ручно говоримо рачунару да желимо да помножимо низове. СУМПРОДУЦТ је ионако то хтео да уради, тако да нема промене у начину на који математика функционише. Извођење математичке операције претвара наше Тачно/Нетачно у 1/0 исте. Па, чему разлика?
Већину времена то није превише важно и своди се на преференције корисника. Постоји бар један случај где је потребно множење.
Када користите СУМПРОДУЦТ, рачунар очекује да сви аргументи (низ1, низ2 итд.) Буду исте величине. То значи да имају исти број редова или колона. Међутим, са СУМПРОДУЦТ -ом можете учинити оно што знате као дводимензионални прорачун низа који ћемо видети у следећем примеру. Када то учините, низови су различитих величина, па морамо заобићи ту проверу „све исте величине“.
Две димензије
Сви претходни примери су имали низове у истом смеру. СУМПРОДУЦТ може да реши ствари које се одвијају у два смера, као што ћемо видети у следећој табели.
Ево наше табеле продатих јединица, али подаци су преуређени тамо где се категорије налазе на врху. Ако желимо да сазнамо колико је артикала било црвено и било у категорији А, можемо писати
| 1 | = СУМПРОДУЦТ ((А2: А4 = "Црвено")*(Б1: Ц1 = "А")*Б2: Ц4) |
Шта се дешава овде?? Испоставило се да ћемо се умножавати у два различита правца. То је теже замислити само написаном реченицом, па имамо неколико слика које ће нам помоћи. Прво, наши критеријуми редова (је ли црвени?) Множиће се у сваком реду у низу.
| 1 | = СУМПРОДУЦТ ((А2: А4 = "ЦРВЕНО")*Б2: Ц4) |
Затим ће се критеријуми колоне (да ли је то категорија А?) Помножити сваку колону
| 1 | = СУМПРОДУЦТ ((А2: А4 = "Црвено")*(Б1: Ц1 = "А")*Б2: Ц4) |
Након што оба критеријума ураде свој посао, преостале су само нуле 5 и 10. СУМПРОДУЦТ ће нам затим дати укупан број 15 као одговор.
Сећате ли се како смо разговарали о томе да низови морају бити исте величине, осим ако радите две димензије? То је било делимично тачно. Поново гледа низове које смо користили у формули. Тхе висина два наша низа су исти, а ширина два наша низа су иста. Дакле, и даље морате бити сигурни да ће се ствари исправно поставити, али то можете учинити у различитим димензијама.
Две димензије и комплекс
Много пута су нам представљени подаци који нису у најбољем распореду који одговара нашим формулама. Могли бисмо покушати да га ручно преуредимо, или можемо бити паметнији са својим формулама. Размотримо следећу табелу.
Овде имамо податке за наше артикле и продају помешане за сваки месец. Како бисмо сазнали колико је ствари Боб продао током целе године?
Да бисмо то урадили, користићемо две додатне функције: СЕАРЦХ и ИСНУМБЕР. СЕАРЦХ функција ће нам омогућити да потражимо кључну реч „итемс“ унутар ћелија заглавља. Резултат ове функције ће бити или бројем или грешком (ако кључна реч није пронађена). Затим ћемо користити ИСНУМБЕР за конверзију то излаз у наше Боолеан вредности. Наша формула ће изгледати као испод.
Требало би да сте већ прилично упознати са првим низом. Створиће излаз попут {0, 1, 0, 1}. Следећи низ критеријума о коме смо управо говорили. Створиће се број за све ћелије са „ставкама“ у њима, и грешка за остале {5, #Н/А !, 5, #Н/А!}. ИСНУМБЕР затим претвара ово у логичко {Труе, Фалсе, Труе, Фалсе}. Затим, када множимо, задржаће се само вредности из прве и треће колоне. Након што се сви низови множе једни против других, једини бројеви који нису нула биће они који су овде истакнути:
| 1 | = СУМПРОДУЦТ ((А2: А5 = "Боб")*(ИСНУМБЕР (СЕАРЦХ ("Ставке", Б1: Е1))*Б2: Е5)) |
СУМПРОДУЦТ ће све то збројити и добићемо коначни резултат 29.
ПОДИЗВОД Ор
Појављују се многе ситуације у којима бисмо желели да можемо сумирати вредности ако наша колона критеријума има једну вредност ИЛИ другу вредност. То можете постићи у СУМПРОДУЦТ -у додавањем два низа критеријума један против другог.
У овом примеру желимо да саберемо јединице продате и за црвену и за плаву.
Наша формула ће изгледати овако
| 1 | = СУМПРОДУЦТ (А2: А7, (Б2: Б7 = "Ред")+(Б2: Б7 = "Блуе")) |
Погледајмо ред критеријума Ред. Он ће произвести низ који изгледа овако: {1, 1, 0, 0, 0, 0}. Низ плавих критеријума ће изгледати као {0, 0, 1, 0, 1, 0}. Када их додате заједно, нови низ ће изгледати као {1, 1, 1, 0, 1, 0}. Можемо видети како су се два низа спојила у један низ критеријума. Функција ће тада то помножити са нашим првим низом и добићемо {100, 50, 10, 0, 75, 0}. Уочите да су вредности за Греен поништене. Последњи корак ЗБОРНОГ ПРОИЗВОДА је сабирање свих бројева како бисмо дошли до решења 235.
Овде једна реч опреза. Будите пажљиви када се низови критеријума међусобно не искључују. У нашем примеру, вредности у колони Б могу бити или црвене или плаве, али знали смо да то никада не може бити обоје. Размислите да ли смо написали ову формулу:
| 1 | = СУМПРОДУЦТ (А2: А7, (А2: А7> = 50)+(Б2: Б7 = "Плаво")) |
Наша намера је да пронађемо плаве предмете који су продати или су били у количини већој од 50. Међутим, ови услови нису искључиви, јер један ред може имати преко 50 у колони А и бе Блуе. То би довело до тога да први низ критеријума изгледа као {1, 1, 0, 1, 1, 0}, а други низ критеријума је {0, 0, 1, 0, 1, 0}. Њихово збрајање производи {1, 1, 1, 1, 2, 0}. Видите ли како сада имамо 2? Ако се остави на миру, СУМПРОДУЦТ би на крају удвостручио вредност у том реду, променивши 75 у 150, и добили бисмо погрешан резултат. Да бисмо то исправили, постављамо спољну проверу критеријума у наш низ, на следећи начин:
| 1 | = СУМПРОДУЦТ (А2: А7, -((А2: А7> = 50)+(Б2: Б7 = "Плаво")> 0)) |
Сада, након што су два унутрашња поља критеријума сабрана, проверићемо да ли је резултат већи од 0. Ово ће се ослободити 2 које смо раније имали, а уместо тога ћемо имати низ попут {1, 1, 1 , 1, 1, 0} који ће дати тачан резултат.
САЖЕТАК Тачно
Већина функција у Екцелу не разликује велика и мала слова, али понекад морамо бити у могућности да извршимо претрагу имајући на уму осетљивост на велика и мала слова. Када је жељени резултат нумерички, то можемо постићи употребом ЕКСАЦТ унутар функције СУМПРОДУЦТ. Размотрите следећу табелу:
Желимо да пронађемо резултат за ставку „АБЦ123“. Нормално, функција ЕКСАЦТ ће упоредити две ставке и вратити Боолеов излаз који наводи да ли су две ставке баш тако исти. Међутим, пошто смо унутар СУМПРОДУЦТ -а, наш рачунар ће знати да имамо посла са низовима и моћи ће да упореди једну ставку са сваком ставком у низу. Наша формула ће изгледати овако
| 1 | = СУМПРОДУЦТ (-ЕКСАЦТ ("АБЦ123", А2: А5), Б2: Б5) |
Функција ЕКСАЦТ ће затим проверити сваку ставку у А2: А5 да види да ли се подудара са вредношћу и величином слова. Ово ће произвести низ који изгледа као {0, 1, 0, 0}. Када се помножи са Б2: Б5, низ постаје {0, 2, 0, 0}. Након коначног збрајања добијамо решење 2.
СУМПРОДУЦТ у Гоогле табелама
Функција СУМПРОДУЦТ ради потпуно исто у Гоогле табелама као и у Екцелу:
САЖЕТАК Примери у ВБА
Такође можете да користите функцију СУМПРОДУЦТ у ВБА. Тип: апплицатион.ворксхеетфунцтион.сумпродуцт (низ1, низ2, низ3)
Извршавање следећих ВБА наредби
| 1 | Опсег ("Б10") = Апплицатион.ВорксхеетФунцтион.СумПродуцт (Опсег ("А2: А7"), Опсег ("Б2: Б7")) |
ће дати следеће резултате
За аргументе функције (низ1 итд.) Можете их унијети директно у функцију или дефинирати варијабле које ћете умјесто тога користити.
