Функция SUMIF в Excel и сумиране по множество условия

Excel е невероятно функционална програма. Дори вграденият набор от функции е достатъчен за изпълнение на почти всяка задача. И освен стандартните, познати на мнозина, има и такива, за които малко хора са чували. Но в същото време те не престават да бъдат полезни. Те са с по-тясна специализация и не винаги има нужда от тях. Но ако знаете за тях, тогава в критичен момент те могат да бъдат много полезни.

Днес ще говорим за една от тези функции − SUMMESLIMN.

Ако потребителят е изправен пред задачата да обобщи няколко стойности, фокусирайки се върху определени критерии, тогава е необходимо да използвате функцията SUMMESLIMN. Формулата, която използва тази функция, приема тези условия като аргументи, след това сумира стойностите, които им отговарят, и след това намерената стойност се въвежда в клетката, в която е записана. 

Подробно описание на функцията SUMIFS

Преди да разгледате функцията SUMMESLIMN, първо трябва да разберете каква е по-простата му версия – СУМЕСЛИ, тъй като именно на него се основава функцията, която разглеждаме. Вероятно всеки от нас вече е запознат с две често използвани функции – SUM (извършва сумиране на стойности) и АКО (тества стойност срещу определено условие).

Ако ги комбинирате, получавате друга функция - СУМЕСЛИ, който проверява данните спрямо определени от потребителя критерии и сумира само онези числа, които отговарят на тези критерии. Ако говорим за английската версия на Excel, тогава тази функция се нарича SUMIF. С прости думи, името на -език е директен превод на англоезичното. Тази функция може да се използва за различни цели. По-специално, може да се използва като алтернатива ВПР, тоест запишете

Основната разлика между функцията SUMMESLIMN  от обичайната функция СУМЕСЛИ е, че се използват няколко критерия. Неговият синтаксис е доста сложен на пръв поглед, но при по-внимателно разглеждане се оказва, че логиката на тази функция е много проста. Първо трябва да изберете диапазона, в който ще се проверяват данните, и след това да зададете условията за съответствие, с които ще се извърши анализът. И такава операция може да се извърши за доста голям брой състояния.

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

SUMIFS(сума_диапазон, условие_диапазон1, условие1, [условие_диапазон2, условие2], …)

На подходящи места е необходимо да поставите масиви от клетки, подходящи в конкретен случай. 

Нека разгледаме аргументите по-подробно:

1. Обхват_на_сума. Този аргумент, както и обхватът на условие 1 и условие 1, са задължителни. Това е набор от клетки, които трябва да бъдат сумирани.
2. Диапазон_условие1. Това е диапазонът, в който условието ще бъде проверено. Той е съчетан със следващия аргумент – Condition1. Сумирането на стойностите, съответстващи на критерия, се извършва в клетките, посочени в предишния аргумент.
3. Състояние1. Този аргумент указва критериите, спрямо които да се проверява. Може да се настрои например по следния начин: “> 32”.
4. Диапазон на условие 2, условие 2... Тук следните условия се задават по същия начин. Ако трябва да бъдат посочени повече от няколко условия, тогава се добавят аргументите Диапазон на условие 3 и Условие 3. Синтаксисът е същият за следните аргументи.

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

Можете да го използвате в няколко области наведнъж (ще дадем само няколко, списъкът всъщност е още по-дълъг):

1. Счетоводство. Например, добре е да използвате функцията SUMMESLIMN за създаване на обобщени отчети, по тримесечия за разходи над определена сума, например. Или създайте отчет за един продукт от определена ценова категория.
2. Управление продажби. И тук функцията може да бъде много полезна. Например, ние сме изправени пред задачата да обобщим само себестойността на стоките, които са били продадени на определен клиент в определен момент. И в такава ситуация функцията SUMMESLIMN може да бъде много полезно.
3. образование. Днес ще дадем още практически примери от тази област. По-специално, можете да го използвате, за да получите обобщение на оценките на учениците. Можете да избирате за отделен предмет или за отделни оценки. Човек може веднага да зададе няколко критерия, по които ще бъде избрана оценката, което е много удобно и може да спести много време.

Както можете да видите, диапазонът от приложения за тази функция е много широк. Но това не е единственото му достойнство. Нека да разгледаме още няколко предимства, които тази функция има:

1. Възможност за задаване на множество критерии. Защо това е предимство? Можете да използвате нормалната функция СУМЕСЛИ! И всичко това, защото е удобно. Не е необходимо да се правят отделни изчисления за всеки от критериите. Всички действия могат да бъдат програмирани предварително, дори преди. как ще се формира таблицата с данни. Това е страхотно спестяване на време.
2. Автоматизация. Модерната епоха е ерата на автоматизацията. Само човекът, който знае как правилно да автоматизира работата си, може да спечели много. Ето защо способността да овладеете Excel и функцията SUMMESLIMN по-специално, е толкова важно за всеки човек, който иска да изгради кариера. Познаването на една функция ви позволява да извършвате няколко действия едновременно, като едно. И тук преминаваме към следващото предимство на тази функция.
3. Спестяване на време. Просто поради факта, че една функция изпълнява няколко задачи наведнъж.
4. Простота. Въпреки факта, че синтаксисът е доста тежък на пръв поглед поради своята обемност, всъщност логиката на тази функция е много проста. Първо се избира диапазон от данни, след това диапазон от стойности, които ще бъдат проверени за съответствие с определено условие. И разбира се, самото условие също трябва да бъде посочено. И така няколко пъти. Всъщност тази функция се основава само на една логическа конструкция, което я прави по-проста от добре познатата ВПР въпреки факта, че може да се използва за същите цели, като се вземат предвид и по-голям брой критерии. 

Характеристики на използването на функцията SUMIFS

Има няколко характеристики при използването на тази функция, на които трябва да обърнете внимание. На първо място, тази функция игнорира диапазони с текстови низове или нули, тъй като тези типове данни не могат да се добавят заедно в аритметичен модел, а само свързани като низове. Тази функция не може да направи това. Трябва също да обърнете внимание на следните условия:

1. Можете да използвате тези типове стойности като условия за избиране на клетки, за да добавите допълнително стойностите, съдържащи се в тях: числови стойности, булеви изрази, препратки към клетки и т.н. 
2. Ако се проверява текст, логически изрази или математически знаци, тогава тези критерии се посочват чрез кавички.
3. Не могат да се използват термини, по-дълги от 255 знака.
4. Възможно е да се използват приблизителни критерии за избор на стойности с помощта на заместващи знаци. Въпросителният знак се използва за заместване на един знак, а знакът за умножение (звездичка) е необходим за заместване на няколко знака. 
5. Булевите стойности, които са в диапазона на сумиране, автоматично се преобразуват в числови стойности според техния тип. Така стойността “TRUE” се превръща в единица, а “FALSE” – в нула. 
6. Ако #VALUE! грешка се появи в клетка, това означава, че броят на клетките в обхвата на условието и сумирането е различен. Трябва да се уверите, че размерите на тези аргументи са еднакви. 

Примери за използване на функцията SUMIFS

функция SUMMESLIMN не е толкова сложно, колкото изглежда на пръв поглед, оказва се. Но за по-голяма яснота, нека да разгледаме някои практически примери за това как можете да използвате функцията SUMMESLIMN. Това ще улесни много по-лесното задълбочаване в темата.

Динамичен обхват на сумиране на състоянието

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

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

За да изчислим общия резултат въз основа на критериите, описани по-горе, трябва да приложим следната формула.

Нека опишем аргументите по-подробно:

1. C3:C14 е нашият диапазон на сумиране. В нашия случай той съвпада с обхвата на условието. От него ще бъдат избрани точки, използвани за изчисляване на сумата, но само тези, които попадат в нашите критерии.
2. “>5” е нашето първо условие.
3. B3:B14 е вторият диапазон на сумиране, който се обработва, за да съответства на втория критерий. Виждаме, че няма съвпадение с обхвата на сумиране. От това заключаваме, че обхватът на сумиране и обхватът на условието могат или не могат да бъдат идентични. 
4. “A*” е вторият диапазон, който определя избора на оценки само за онези студенти, чието фамилно име започва с A. В нашия случай звездичката означава произволен брой знаци. 

След изчисленията получаваме следната таблица.

Както можете да видите, формулата обобщава стойностите въз основа на динамичния диапазон и въз основа на условията, зададени от потребителя.

Селективно сумиране по условие в 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;{«Китай»;»Грузия»}))

След функцията SUMMESLIMN ще сумира масива от стойности въз основа на критериите, посочени във формулата (т.е. страните от Китай и Грузия), полученият масив се сумира от обичайната функция СУМА, който се записва като формула за масив.

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

Сега нека да разгледаме таблицата, съдържаща сумите.

Както виждате, успяхме. Вие също със сигурност ще успеете. Голям успех в тази област. Това е много проста функция, която човек, който току-що е стъпил на пътя на изучаването на Excel, може да разбере. И вече знаем, че функцията SUMMESLIMN ви позволява да бъдете ефективни във всяка сфера на дейност, от счетоводство до дори образование. Дори ако изграждате кариера в друга област, която не е описана по-горе, тази функция пак ще ви помогне да печелите пари. Ето защо тя е ценна.

Най-важното е, че ви позволява да спестите време, което, за съжаление, е ограничен ресурс. Изглежда, че има няколко секунди за прилагане на две функции, но когато трябва да извършите огромен брой повтарящи се операции, тогава тези секунди добавят часове, които могат да бъдат изразходвани за нещо друго. Затова ви препоръчваме да практикувате използването на тази функция. Освен това е невероятно просто.