У претходном чланку смо научили како да сумирамо горње или доње Н вредности. У овом чланку покушавамо да сумирамо горњу или доњу Н вредност са критеријумом.
Збир ТОП Н вредности са критеријумима
Како решити проблем?
За овај чланак ћемо морати да користимо функцију СУМПРОДУЦТ. Сада ћемо направити формулу од ових функција. Овде нам је дат опсег и критеријум. Морамо добити првих 5 вредности у распону и добити збир вредности на основу датих критеријума.
Општа формула:
= СУМПРОДУЦТ (ВЕЛИКИ ((листа = критеријуми) * (опсег), {1, 2,…., Н}})
листа: листа критеријума
Критеријуми: критеријуми који одговарају
домет: опсег вредности
вредности: бројеви одвојени зарезима, на пример, ако желите да пронађете прве 3 вредности, користите {1, 2, 3}.
Пример:
Овде имамо вредности скупа података од А1: Д50.
Прво, морамо пронаћи првих пет вредности помоћу функције ЛАРГЕ која се подудара са градом „Бостон“, а затим ће се операција збира извршити над тих 5 вредности. Сада ћемо користити следећу формулу да добијемо збир
Користите формулу:
= СУМПРОДУЦТ (ВЕЛИКИ ((Град = "Бостон") * (количина), {1, 2, 3, 4, 5}))
Објашњење:
- Градски „Бостон“ одговара поменутом опсегу Цитија. Ово враћа низ тачних и лажних.
- Функција ЛАРГЕ враћа првих 5 нумеричких вредности из опсега количина и враћа низ функцији СУМПРОДУЦТ.
= СУМПРОДУЦТ {193, 149, 138, 134, 123}
- Функција СУМПРОДУЦТ добија низ од топ 5 вредности, са низом од топ 5 бројева који враћа СУМ тих бројева.
Овде је распон града и количине дат као именовани опсег. Притисните Ентер да бисте добили СУМ првих 5 бројева.
Као што видите на горњој снимци, тај збир је 737. Збир вредности 193 + 149 + 138 + 134 + 123 = 737.
Горе наведене вредности можете проверити у скупу података помоћу опције филтера екцел. Примените филтер на заглавље Град и количина и кликните на дугме са стрелицом на заглављу града које се појави. Пратите доле наведене кораке.
Кораци:
- Изаберите ћелију заглавља града. Примените филтер помоћу пречице Цтрл + Схифт + Л.
- Кликните на стрелицу која се појављује као опција филтера.
- Изаберите опцију (Изабери све).
- Изаберите само град Бостон.
- Одмах изаберите заглавље количине.
- Сортирајте листу од највеће до најмање и можете видети свих првих 5 вредности које смо израчунали помоћу формуле.
Као што можете видети на горњем гифу свих 5 вредности које одговарају датим критеријумима. То такође значи да формула добро функционише како би израчунала ове вредности
ВЕЛИКИ Н бројеви
Горњи процес се користи за израчунавање збира неколико бројева од врха. Али да израчунамо за н (велики) број вредности у великом распону.
Користите формулу:
= СУМПРОДУЦТ (ВЕЛИКИ ((Град = "Бостон") * (количина), РЕД (ИНДИРЕКТАН ("1:10"))
Овде генеришемо збир првих 10 вредности добијањем низа од 1 до 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} коришћењем РОВ & ИНДИРЕЦТ Екцел функција.
Овде имамо збир првих 10 бројева који резултирају 1147.
Збир доњих Н вредности са критеријумима
Како решити проблем?
За овај чланак ћемо морати да користимо функцију СУМПРОДУЦТ. Сада ћемо направити формулу од ових функција. Овде нам је дат опсег и морамо да унесемо 5 вредности у опсег и добијемо збир вредности.
Општа формула:
{= СУМ (СМАЛЛ (ИФ (Цити = "Бостон", количина), {1, 2, 3, 4, 5}))}
Опсег: опсег вредности
Вредности: бројеви раздвојени зарезима, на пример, ако желите да пронађете доње 3 вредности, користите {1, 2, 3}.
Пример:
Све ово може бити збуњујуће за разумевање. Дакле, хајде да тестирамо ову формулу тако што ћемо је покренути на примеру приказаном испод.
Овде имамо опсег вредности од А1: Д50.
Овде је распон града и количине дат коришћењем именованог опсега екцел алата.
Прво, морамо пронаћи пет доњих вредности помоћу функције СМАЛЛ која одговара критеријумима, а затим се операција збира изводи преко тих 5 вредности. Сада ћемо користити следећу формулу да добијемо збир
Користите формулу:
{= СУМ (СМАЛЛ (ИФ (Цити = "Бостон", количина), {1, 2, 3, 4, 5}))}
НЕМОЈТЕ ручно користити коврчаве заграде. Витичасте заграде нанете помоћу Цтрл + Схифт + Ентер на место само Ентер.
Објашњење:
- СМАЛЛ функција са ИФ функцијом враћа доњих 5 нумеричких вредности које одговарају Цити "Бостон" и враћа низ у функцију СУМ.
= СУМ ({23, 27, 28, 28, 30}))
- Функција СУМ добија низ доњих 5 вредности, који има низ доњих 5 бројева и враћа СУМ оних бројева који се користе са ЦТРЛ + СХИФТ + ЕНТЕР.
Овде је распон града и количине дат као именовани опсег. Притисните Цтрл + Схифт + Ентер да бисте добили СУМ доњих 5 бројева јер је ово формула низа.
Као што можете видети на горњој снимци, тај износ је 136.
Горњи процес се користи за израчунавање збира неколико бројева са дна. Али да израчунамо за н (велики) број вредности у великом распону.
Користите формулу:
{ = СУМ (СМАЛЛ (ИФ (Цити = "Бостон", количина), РОВ (ИНДИРЕЦТ ("1:10")))) }
НЕМОЈТЕ ручно користити завојите заграде. Користите Цтрл + Схифт + Ентер уместо Ентер.
Овде генеришемо збир доњих 10 вредности добијањем низа од 1 до 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} коришћењем РОВ & ИНДИРЕЦТ Екцел функција.
Овде имамо збир доњих 10 бројева који ће резултирати 155.
Ево неколико опсервационих белешки приказаних испод.
Напомене:
- Формула ради само са бројевима.
- Формула функционише само ако у табели за претраживање нема дупликата
- Функција СУМПРОДУЦТ сматра нумеричке вредности (попут текста абц) и вредности грешака (попут #НУМ!, #НУЛЛ!) Као нулл вредности.
- Функција СУМПРОДУЦТ сматра логичку вриједност ТРУЕ као 1 и Фалсе као 0.
- Низ аргумената мора бити исте дужине као и функција.
Надамо се да је овај чланак о томе како вратити збир првих 5 вредности или 5 доњих вредности са критеријумима у Екцелу објашњен. Овде пронађите још чланака о функцијама СУМПРОДУЦТ -а. Молимо вас да своје питање поделите испод у пољу за коментаре. Ми ћемо вам помоћи.
Ако вам се допадају наши блогови, поделите их са пријатељима на Фацебооку. Такође нас можете пратити на Твиттер -у и Фацебоок -у. Волели бисмо да чујемо од вас, реците нам како можемо побољшати, допунити или иновирати наш рад и учинити га бољим за вас. Пишите нам на веб локацији е -поште
Како се користи функција СУМПРОДУЦТ у програму Екцел: Враћа СУМ након множења вредности у више низа у Екцелу.
ЗБИРАЈТЕ ако је датум између : Враћа ЗБОР вредности између датих датума или периода у Екцелу.
Зброј ако је датум већи од датума: Враћа ЗБОР вредности након датог датума или тачке у Екцелу.
2 начина збрајања по месецима у Екцелу: Враћа ЗБОР вредности у датом месецу у Екцелу.
Како збрајати више колона са условом: Враћа СУМ вредности у више колона са условом у екцелу
Како користити џокере у екцелу : Броји ћелије које се подударају са фразама користећи џокер знакове у Екцелу
Популарни чланци
50 Екцел пречица за повећање ваше продуктивности
Измените падајућу листу
Апсолутна референца у Екцелу
Ако са условним обликовањем
Ако са замјенским знаковима
Поглед по датуму
Претворите инче у стопе и инче у програму Екцел 2016
Придружите се имену и презимену у екцелу
Пребројите ћелије које одговарају А или Б
