SUMIF функција во Excel и сумирање според повеќе услови

Excel е неверојатно функционална програма. Дури и вградениот сет на функции е доволен за да се заврши речиси секоја задача. А покрај стандардните, познати на многумина, има и такви за кои ретко кој слушнал. Но, во исто време, тие не престануваат да бидат корисни. Имаат потесна специјализација и не секогаш има потреба од нив. Но, ако знаете за нив, тогаш во критичен момент тие можат да бидат многу корисни.

Денес ќе зборуваме за една од таквите функции − САМЕСЛИМН.

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

Детален опис на функцијата SUMIFS

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

Ако ги комбинирате, добивате друга функција - СУММЕСЛИ, кој ги проверува податоците според критериумите одредени од корисникот и ги сумира само оние броеви кои ги исполнуваат тие критериуми. Ако зборуваме за англиската верзија на Excel, тогаш оваа функција се нарекува SUMIF. Со едноставни зборови, името -language е директен превод на оној на англиски јазик. Оваа функција може да се користи за различни цели. Особено, може да се користи како алтернатива VPR, односно запишете

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

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

SUMIFS(опсег_збир, опсег_услов1, услов1, [опсег_состојба2, услов2], ...)

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

Да ги погледнеме аргументите подетално:

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

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

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

1. Сметководство. На пример, добро е да се користи функцијата САМЕСЛИМН да креирате збирни извештаи, по квартали за трошење над одредена сума, на пример. Или креирајте извештај за еден производ од одредена категорија на цени.
2. Управување со продажбата. Тука, исто така, функцијата може да биде многу корисна. На пример, соочени сме со задача да ги сумираме само трошоците за стоки што биле продадени на одреден клиент во одредено време. И во таква ситуација, функцијата САМЕСЛИМН може да биде многу корисна.
3. Образование. Попрактични примери од оваа област ќе дадеме денес. Конкретно, можете да го користите за да добиете резиме на оценките на учениците. Можете да изберете за еден предмет или за поединечни оценки. Едно лице може веднаш да постави неколку критериуми според кои ќе се избере проценката, што е многу погодно и може да заштеди многу време.

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

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

Карактеристики на користење на функцијата SUMIFS

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

1. Можете да ги користите овие типови вредности како услови за избор на ќелии за дополнително додавање на вредностите содржани во нив: нумерички вредности, булови изрази, референци на ќелии и така натаму. 
2. Доколку се проверуваат текст, логички изрази или математички знаци, тогаш таквите критериуми се одредуваат преку наводници.
3. Не може да се користат термини подолги од 255 знаци.
4. Можно е да се користат приближни критериуми за избор на вредности со помош на џокери. Прашалникот се користи за замена на еден знак, а знакот за множење (ѕвездичка) е потребен за замена на повеќе знаци. 
5. Булованите вредности кои се во опсегот на сумирање автоматски се претвораат во нумерички вредности според нивниот тип. Така, вредноста „TRUE“ се претвора во една, а „FALSE“ во нула. 
6. Ако е # ВРЕДНОСТ! грешка се појавува во ќелија, тоа значи дека бројот на ќелии во опсезите на состојбата и сумирањето е различен. Треба да бидете сигурни дека големини на овие аргументи се исти. 

Примери за користење на функцијата SUMIFS

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

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

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

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

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

Ајде да ги опишеме аргументите подетално:

1. C3:C14 е нашиот опсег на сумирање. Во нашиот случај, тоа се совпаѓа со опсегот на услови. Од него ќе бидат избрани поени кои се користат за пресметување на износот, но само оние кои потпаѓаат под нашите критериуми.
2. „>5“ е нашиот прв услов.
3. B3:B14 е вториот опсег на сумирање што се обработува за да одговара на вториот критериум. Гледаме дека нема совпаѓање со опсегот на сумирање. Од ова заклучуваме дека опсегот на собирање и опсегот на условот може или не мора да бидат идентични. 
4. „А*“ е вториот опсег, кој го одредува изборот на оценки само за оние студенти чие презиме започнува со А. Во нашиот случај, ѕвездичка значи кој било број на знаци. 

По пресметките, ја добиваме следната табела.

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

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

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

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

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

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

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

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

SUMIFS функцијата за собирање вредности низ повеќе услови

Сега да дадеме уште еден пример за илустрација. Во овој случај, табелата останува иста како и во претходниот случај. 

Ја користиме следнава формула (но ја пишуваме како формула за низа, односно ја внесуваме преку комбинацијата на копчиња CTRL + SHIFT + ENTER).

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

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

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

Сега да ја погледнеме табелата што ги содржи збирките.

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

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