Функција СУМИФ у Екцел-у и збир према више услова

Екцел је невероватно функционалан програм. Чак је и уграђени скуп функција довољан да заврши скоро сваки задатак. А поред стандардних, многима познатих, постоје и они за које је мало ко чуо. Али у исто време, они не престају да буду корисни. Они имају ужу специјализацију, и не постоји увек потреба за њима. Али ако знате за њих, онда у критичном тренутку могу бити веома корисни.

Данас ћемо говорити о једној од таквих функција − СУММЕСЛИМН.

Ако је корисник суочен са задатком да сумира неколико вредности, фокусирајући се на одређене критеријуме, онда је неопходно користити функцију СУММЕСЛИМН. Формула која користи ову функцију узима ове услове као аргументе, затим сабира вредности које их испуњавају, а затим се пронађена вредност уноси у ћелију у којој је уписана. 

Детаљан опис функције СУМИФС

Пре разматрања функције СУММЕСЛИМН, прво морате да схватите шта је то једноставнија верзија – СУММЕСЛИ, пошто се управо на њему заснива функција коју разматрамо. Свако од нас је вероватно већ упознат са две функције које се често користе – СУМ (спроводи сабирање вредности) и АКО (тестира вредност у односу на одређени услов).

Ако их комбинујете, добијате још једну функцију - СУММЕСЛИ, који проверава податке у односу на критеријуме које је одредио корисник и сабира само оне бројеве који испуњавају те критеријуме. Ако говоримо о енглеској верзији Екцела, онда се ова функција зове СУМИФ. Једноставним речима, назив -лангуаге је директан превод оног на енглеском језику. Ова функција се може користити у различите сврхе. Конкретно, може се користити као алтернатива ВПР, односно записати

Главна разлика између функције СУММЕСЛИМН  од уобичајене функције СУММЕСЛИ је да се користи неколико критеријума. Њена синтакса је на први поглед прилично сложена, али након детаљнијег разматрања, испоставља се да је логика ове функције веома једноставна. Прво треба да изаберете опсег у коме ће се подаци проверавати, а затим поставити услове за усаглашеност са којима ће се вршити анализа. И таква операција се може извршити за прилично велики број услова.

Сама синтакса је:

СУМИФС(опсег_збира, опсег_услов1, услов1, [опсег_услова2, услов2], …)

На погодна места потребно је поставити низове ћелија које одговарају одређеном случају. 

Погледајмо аргументе детаљније:

1. Опсег_збира. Овај аргумент, као и опсег услова 1 и услова 1, је потребан. То је скуп ћелија које треба сабрати.
2. Опсег_услова1. Ово је опсег у коме ће се проверавати услов. Упарен је са следећим аргументом – Услов1. Сумирање вредности које одговарају критеријуму врши се унутар ћелија наведених у претходном аргументу.
3. Цондитион1. Овај аргумент специфицира критеријуме према којима се проверава. Може се подесити, на пример, на овај начин: „> 32“.
4. Опсег услова 2, услов 2... Овде су следећи услови постављени на исти начин. Ако је потребно навести више од неколико услова, тада се додају аргументи Опсег услова 3 и Услов 3. Синтакса је иста за следеће аргументе.

Функција омогућава максималну обраду до 127 парова услова и опсега. 

Можете га користити у неколико области одједном (даћемо само неке, списак је заправо још дужи):

1. Рачуноводство. На пример, добро је користити функцију СУММЕСЛИМН да креирате збирне извештаје, по кварталима за потрошњу преко одређеног износа, на пример. Или направите извештај о једном производу из одређене ценовне категорије.
2. Управљање продајом. Овде такође функција може бити веома корисна. На пример, суочени смо са задатком да сумирамо само цену робе која је продата одређеном купцу у одређено време. И у таквој ситуацији функција СУММЕСЛИМН може бити од велике помоћи.
3. Образовање. Данас ћемо дати више практичних примера из ове области. Конкретно, можете га користити за добијање резимеа оцена ученика. Можете бирати за један предмет или за појединачне оцене. Особа може одмах поставити неколико критеријума по којима ће бити одабрана процена, што је веома згодно и може уштедети много времена.

Као што видите, опсег апликација за ову функцију је веома широк. Али то није његова једина заслуга. Хајде да погледамо још неколико предности које ова функција има:

1. Могућност постављања више критеријума. Зашто је ово предност? Можете користити нормалну функцију СУММЕСЛИ! И све зато што је згодно. Нема потребе за посебним прорачунима за сваки од критеријума. Све радње се могу програмирати унапред, чак и раније. како ће се формирати табела података. Ово је одлична уштеда времена.
2. Аутоматизација. Модерно доба је доба аутоматизације. Само онај ко уме да правилно аутоматизује свој рад може много да заради. Због тога способност савладавања Екцел-а и функције СУММЕСЛИМН посебно је толико важно за сваку особу која жели да изгради каријеру. Познавање једне функције вам омогућава да извршите неколико радњи одједном, као једну. И овде прелазимо на следећу предност ове функције.
3. Штедети време. Само због чињенице да једна функција обавља неколико задатака одједном.
4. Једноставност. Упркос чињеници да је синтакса на први поглед прилично тешка због своје гломазности, у ствари, логика ове функције је врло једноставна. Прво се бира опсег података, затим опсег вредности, који ће се проверавати да ли су усклађени са одређеним условом. И наравно, сам услов такође мора бити прецизиран. И тако неколико пута. У ствари, ова функција се заснива само на једној логичкој конструкцији, што је чини једноставнијом од добро познате ВПР упркос чињеници да се може користити у исте сврхе, такође узимајући у обзир већи број критеријума. 

Особине коришћења функције СУМИФС

Постоји неколико карактеристика коришћења ове функције на које морате обратити пажњу. Пре свега, ова функција игнорише опсеге са текстуалним низовима или нултим вредностима, пошто се ови типови података не могу сабирати у аритметичком обрасцу, већ само спојени као низови. Ова функција не може ово да уради. Такође морате обратити пажњу на следеће услове:

1. Можете користити ове типове вредности као услове за одабир ћелија да бисте даље додали вредности које се у њима налазе: нумеричке вредности, логички изрази, референце ћелија итд. 
2. Ако се проверава текст, логички изрази или математички знаци, онда се такви критеријуми наводе кроз наводнике.
3. Не могу користити термине дуже од 255 знакова.
4. Могуће је користити приближне критеријуме за одабир вредности помоћу џокер знакова. Знак питања се користи за замену једног знака, а знак множења (звездица) је потребан за замену више знакова. 
5. Логичке вредности које се налазе у опсегу сумирања се аутоматски претварају у нумеричке вредности према њиховом типу. Тако се вредност „ТРУЕ“ претвара у један, а „ФАЛСЕ“ – у нулу. 
6. Ако је #ВРЕДНОСТ! грешка се појављује у ћелији, то значи да је број ћелија у опсезима услова и сумирања различит. Морате бити сигурни да су величине ових аргумената исте. 

Примери коришћења функције СУМИФС

функција СУММЕСЛИМН није тако компликовано као што се чини на први поглед, испоставило се. Али ради веће јасноће, погледајмо неколико практичних примера како можете да користите функцију СУММЕСЛИМН. Ово ће знатно олакшати удубљивање у тему.

Динамички опсег сумирања услова

Дакле, почнимо са првим примером. Рецимо да имамо табелу која садржи информације о томе како се ученици носе са наставним планом и програмом из одређеног предмета. Постоји скуп оцена, учинак се оцењује на скали од 10 поена. Задатак је пронаћи оцену на испиту оних ученика чије презиме почиње на слово А, а минимални резултат им је 5.

Табела изгледа овако.

Да бисмо израчунали укупан резултат на основу горе описаних критеријума, потребно је да применимо следећу формулу.

Хајде да детаљније опишемо аргументе:

1. Ц3:Ц14 је наш опсег сумирања. У нашем случају, то се поклапа са опсегом услова. Из њега ће бити одабрани бодови који се користе за израчунавање износа, али само они који потпадају под наше критеријуме.
2. „>5“ је наш први услов.
3. Б3:Б14 је други опсег сабирања који се обрађује да би одговарао другом критеријуму. Видимо да нема случајности са опсегом сумирања. Из овога закључујемо да опсег сабирања и опсег услова могу, али не морају бити идентични. 
4. „А*“ је други опсег, који одређује избор оцена само за оне ученике чије презиме почиње са А. У нашем случају, звездица означава било који број знакова. 

Након прорачуна добијамо следећу табелу.

Као што видите, формула је сумирала вредности на основу динамичког опсега и на основу услова које је одредио корисник.

Селективно сумирање по услову у Екцел-у

Претпоставимо сада да желимо да добијемо информације о томе која је роба испоручена у које земље током последњег квартала. Након тога пронађите укупан приход од пошиљака за јул и август.

Сама табела изгледа овако. 

Да бисмо одредили коначни резултат, потребна нам је таква формула.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Као резултат прорачуна изведених по овој формули, добијамо следећи резултат.

Пажња! Ова формула изгледа прилично велика иако смо користили само два критеријума. Ако је опсег података исти, онда можете значајно смањити дужину формуле, као што је приказано у наставку.

СУМИФС функција за сумирање вредности у више услова

Хајде сада да дамо још један пример за илустрацију. У овом случају табела остаје иста као у претходном случају. 

Користимо следећу формулу (али је пишемо као формулу низа, односно уносимо је преко комбинације тастера ЦТРЛ + СХИФТ + ЕНТЕР).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

После функције СУММЕСЛИМН ће сабрати низ вредности на основу критеријума наведених у формули (то јест, земље Кине и Грузије), резултујући низ се сабира уобичајеном функцијом ЗБИР, који је записан као формула низа.

Ако су услови прослеђени као константа низа за више од једног пара, онда ће формула дати нетачан резултат.

Хајде сада да погледамо табелу која садржи укупне вредности.

Као што видите, успели смо. И ти ћеш сигурно успети. Велики успех на овом пољу. Ово је врло једноставна функција коју може разумети особа која је тек крочила на пут учења Екцел-а. А већ знамо да је функција СУММЕСЛИМН омогућава вам да будете ефикасни у било којој области активности, од рачуноводства до чак образовања. Чак и ако градите каријеру у било којој другој области која није горе описана, ова функција ће вам и даље помоћи да зарадите новац. Због тога је она вредна.

Оно што је најважније, омогућава вам да уштедите време, што је, нажалост, ограничен ресурс. Чини се да постоји неколико секунди за примену две функције, али када морате да извршите огроман број операција које се понављају, онда се ове секунде збрајају у сате које можете потрошити на нешто друго. Зато вам препоручујемо да вежбате коришћење ове функције. Штавише, то је невероватно једноставно.