Този урок предоставя подробна демонстрация как да използвате функциите SUMIF и SUMIFS в Google Sheets с формули и примери.
SUMIF е една от математическите функции в Google Sheets, която се използва за условно сумиране на клетки. По принцип функцията SUMIF търси конкретно условие в диапазон от клетки и след това събира стойностите, които отговарят на даденото условие.
Например, имате списък с разходите в таблици на Google и искате да сумирате само разходите, които са над определена максимална стойност. Или имате списък с артикули за поръчка и съответните им количества и искате да знаете само общата сума на поръчката на конкретен артикул. Тук е полезна функцията SUMIF.
SUMIF може да се използва за сумиране на стойности въз основа на числово състояние, текстово състояние, условие за дата, заместващи знаци, както и въз основа на празни и непразни клетки. Google Sheets има две функции за сумиране на стойности въз основа на критерии: SUMIF и SUMIFS. Функцията SUMIF събира числа въз основа на едно условие, докато SUMIFS сумира числа въз основа на множество условия.
В този урок ще обясним как да използвате функциите SUMIF и SUMIFS в Google Sheets за сумиране на числа, които отговарят на определено условие(а).
Функция SUMIF в Google Sheets – Синтаксис и аргументи
Функцията SUMIF е просто комбинация от функцията SUM и IF. Функцията IF сканира обхвата от клетки за дадено условие, а след това функцията SUM сумира числата, съответстващи на клетките, които отговарят на условието.
Синтаксис на функция SUMIF:
Синтаксисът на функцията SUMIF в Google Sheets е както следва:
=SUMIF(диапазон, критерии, [сума_диапазон])Аргументи:
обхват - Диапазонът от клетки, в който търсим клетките, които отговарят на критериите.
критерии – Критериите, които определят кои клетки трябва да бъдат добавени. Можете да базирате критерия на число, текстов низ, дата, препратка към клетка, израз, логически оператор, заместващ знак, както и други функции.
сума_диапазон – Този аргумент е незадължителен. Това е диапазонът от данни със стойности за сумиране, ако съответният запис на диапазон отговаря на условието. Ако не включите този аргумент, вместо това „диапазонът“ се сумира.
Сега нека видим как да използваме функцията SUMIF за сумиране на стойности с различен критерий.
Функция SUMIF с числови критерии
Можете да сумирате числа, които отговарят на определени критерии в диапазон от клетки, като използвате един от следните оператори за сравнение, за да създадете критерии.
- по-голямо от (>)
- по-малко от (<)
- по-голямо или равно на (>=)
- по-малко или равно на (<=)
- равно на (=)
- не е равно на ()
Да предположим, че имате следната електронна таблица и се интересувате от общите продажби, които са 1000 или повече.
Ето как можете да въведете функцията SUMIF:
Първо изберете клетката, където искате да се появи изходът от сумата (D3). За да сумирате числа в B2:B12, които са по-големи или равни на 1000, въведете тази формула и натиснете „Enter“:
=SUMIF(B2:B12,">=1000",B2:B12)В тази примерна формула аргументите за диапазон и sum_range (B2:B12) са еднакви, тъй като числата на продажбите и критериите се прилагат към един и същ диапазон. И ние въведохме числото преди оператора за сравнение и го оградихме в кавички, защото критериите винаги трябва да бъдат затворени в двойни кавички с изключение на препратка към клетка.
Формулата търси числа, които са по-големи или равни на 1000, след което събира всички съвпадащи стойности и показва резултата в клетка D3.
Тъй като аргументите range и sum_range са еднакви, можете да постигнете същия резултат без аргументите sum_range във формулата, както следва:
=SUMIF(B2:B12,">=1000")
Или можете да предоставите препратка към клетка (D2), която съдържа числото вместо критериите за число, и да присъедините оператора за сравнение с тази препратка към клетка в аргумента на критериите:
=SUMIF(B2:B12,">="&D2)Както можете да видите, операторът за сравнение все още се въвежда в двойни кавички, а операторът и препратката към клетка са свързани с амперсанд (&). И не е нужно да поставяте препратка към клетка в кавички.
Забележка: Когато препращате към клетката, която съдържа критерии, уверете се, че не оставяте начално или крайно разстояние в стойността в клетката. Ако вашата стойност има някакво ненужно пространство преди или след стойността в препоръчаната клетка, тогава формулата ще върне „0“ като резултат.
Можете също да използвате други логически оператори по същия начин, за да направите условия в аргумента на критериите. Например, за да сумирате стойности по-малко от 500:
=SUMIF(B2:B12,"<500")
Сума, ако числата са равни на
Ако искате да добавите числа, равни на определено число, можете или да въведете само числото, или да въведете числото със знак за равенство в аргумента на критерия.
Например, за да сумирате съответните суми на продажби (колона B) за количества (колона C), чиито стойности са равни на 20, опитайте някоя от тези формули:
=SUMIF(C2:C12,"=20",B2:B12)
=SUMIF(C2:C12,"20",B2:B12)
=SUMIF(C2:C12,E2,B2:B12)
За да сумирате числата в колона B с количество, което не е равно на 20 в колона C, опитайте тази формула:
=SUMIF(C2:C12,"20",B2:B12)
Функция SUMIF с текстови критерии
Ако искате да добавите числа в диапазон от клетки (колона или ред), съответстващ на клетките, които имат конкретен текст, можете просто да включите този текст или клетката, която съдържа текста в аргумента за критерии на вашата формула SUMIF. Моля, имайте предвид, че текстовият низ винаги трябва да бъде затворен в двойни кавички (“ “).
Например, ако искате общата сума на продажбите в региона „Запад“, можете да използвате следната формула:
=SUMIF(C2:C13,"Запад",B2:B13)В тази формула функцията SUMIF търси стойността „West“ в диапазона от клетки C2:C13 и събира съответната стойност на продажбите в колона B. След това показва резултата в клетка E3.
Можете също да се обърнете към клетката, която съдържа текст, вместо да използвате текста в аргумента за критерии:
=SUMIF(C2:C12,E2,B2:B12)
Сега нека вземем общите приходи на всички региони с изключение на „Запад“. За да направим това, ще използваме не е равно на оператора () във формулата:
=SUMIF(C2:C12,""&E2,B2:B12)
SUMIF със заместващи карти
В горния метод функцията SUMIF с текстови критерии проверява диапазона спрямо точно посочения текст. След това сумира числата, съпоставени с точен текст и игнорира всички други числа, включително частично съвпадащ текстов низ. За да сумирате числата с частично съвпадащи текстови низове, трябва да настроите един от следните заместващи знаци във вашите критерии:
?(въпросителен знак) се използва за съвпадение на всеки единичен знак, навсякъде в текстовия низ.*(звездичка) се използва за намиране на съвпадащи думи заедно с произволна последователност от знаци.~(тилда) се използва за съпоставяне на текстове с въпросителна (?) или звездичка (*).
Ще представим тази примерна електронна таблица за продукти и техните количества за сумиране на числа със заместващи знаци:
Звездичка (*) Заместващ знак
Например, ако искате да сумирате количествата на всички продукти на Apple, използвайте тази формула:
=SUMIF(A2:A14,"Apple*",B2:B14)Тази формула SUMIF намира всички продукти с думата „Apple“ в началото и произволен брой знаци след нея (означени с „*“). След като съвпадението бъде намерено, то обобщава количество числа, съответстващи на съвпадащите текстови низове.
Също така е възможно да се използват множество заместващи знаци в критериите. Освен това можете да въвеждате заместващи знаци с препратки към клетки вместо директен текст.
За да направите това, заместващите знаци трябва да бъдат затворени в двойни кавички (“ “) и свързани с препратката(ите) към клетката:
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)Тази формула сумира количествата от всички продукти, които имат думата „Redmi“ в тях, независимо къде се намира думата в низа.
Въпросителен знак (?) Заместващ знак
Можете да използвате заместващия знак за въпроси (?), за да съпоставите текстови низове с всеки единичен знак.
Например, ако искате да намерите количества от всички варианти на Xiaomi Redmi 9, можете да използвате тази формула:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Горната формула търси текстови низове с думата „Xiaomi Redmi 9“, последвана от всеки единичен знак и сумира съответните количество числа.
Тилда (~) Заместващ знак
Ако искате да съвпаднете с действителен въпросителен знак (?) или знак със звездичка (*), поставете знака тилда (~) преди заместващия знак в частта за условие на формулата.
За да добавите количествата в колона B със съответния низ, който има знак звездичка в края, въведете формулата по-долу:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
За да добавите количества в колона B, които имат въпросителен знак (?) в колона A на същия ред, опитайте следната формула:
=SUMIF(A2:A14,"~?",B2:B14)
Функция SUMIF с критерии за дата
Функцията SUMIF може също да ви помогне условно да сумирате стойности въз основа на критерии за дата – например числа, съответстващи на определена дата, или преди дата, или след дата. Можете също да използвате всеки от операторите за сравнение със стойност на дата, за да създадете критерии за дата за сумиране на числа.
Датата трябва да бъде въведена във формат за дата, поддържан от таблици на Google, или като препратка към клетка, която съдържа дата, или като се използва функция за дата, като ДАТА() или ДНЕС().
Ще използваме тази примерна електронна таблица, за да ви покажем как работи функцията SUMIF с критерии за дата:
Да предположим, че искате да сумирате сумите на продажбите, които са се случили на или преди (<=) 29 ноември 2019 г. в горния набор от данни, можете да добавите тези числа за продажби, като използвате функцията SUMIF по един от следните начини:
=SUMIF(C2:C13,"<=29 ноември 2019 г.",B2:B13)Горната формула проверява всяка клетка от C2 до C13 и съвпада само за онези клетки, които съдържат дати на или преди 29 ноември 2019 г. (29/11/2019). След това сумира сумата на продажбите, съответстваща на тези съвпадащи клетки от диапазона от клетки B2:B13 и показва резултата в клетки E3.
Датата може да бъде предоставена на формулата във всеки формат, който се разпознава от Google Таблици, като „29 ноември 2019 г.“, „29 ноември 2019 г.“ или „29/11/2019“ и т.н. Запомнете стойността на датата и операторът трябва винаги се поставя в двойни кавички.
Можете също да използвате функцията DATE() в критериите вместо директна стойност за дата:
=SUMIF(C2:C13,"<="&ДАТА(2019,11,29),B2:B13)
Или можете да използвате препратка към клетка вместо дата в частта с критериите на формулата:
=SUMIF(C2:C13,"<="&E2,B2:B13)
Ако искате да добавите сумите на продажбите въз основа на днешната дата, можете да използвате функцията ДНЕС() в аргумента за критерии.
Например, за да сумирате всички суми за продажби за днешна дата, използвайте тази формула:
=SUMIF(C2:C13,ДНЕС(),B2:B13)
Функция SUMIF с празни или непразни клетки
Понякога може да се наложи да сумирате числата в диапазон от клетки с празни или непразни клетки в същия ред. В такива случаи можете да използвате функцията SUMIF, за да сумирате стойности въз основа на критерии, при които клетките са празни или не.
Сума, ако е празна
Има два критерия в Google Sheets за намиране на празни клетки: „“ или „=“.
Например, ако искате да сумирате цялата сума на продажбите, която съдържа низове с нулева дължина (визуално изглежда празна) в колона C, използвайте двойни кавички без интервал между тях във формулата:
=SUMIF(C2:C13,"",B2:B13)
За да сумирате цялата сума на продажбите в колона B с пълни празни клетки в колона C, включете „=“ като критерии:
=SUMIF(C2:C13,"=",B2:B13)
Сума, ако не е празна:
Ако искате да сумирате клетки, които съдържат стойност (не празни), можете да използвате „“ като критерий във формулата:
Например, за да получите общата сума на продажбите с всякакви дати, използвайте тази формула:
=SUMIF(C2:C13,"",B2:B13)
SUMIF Въз основа на множество критерии с логика ИЛИ
Както видяхме досега, функцията SUMIF е проектирана да сумира числа въз основа само на един критерий, но е възможно да се сумират стойности въз основа на множество критерии с функцията SUMIF в Google Sheets. Това може да стане чрез свързване на повече от една функция SUMIF в една формула с логика ИЛИ.
Например, ако искате да сумирате сумата на продажбите в региона „Запад“ или „Юг“ (логика ИЛИ) в посочения диапазон (B2:B13), използвайте тази формула:
=SUMIF(C2:C13,"Запад",B2:B13)+SUMIF(C2:C13,"Юг",B2:B13)Тази формула сумира клетки, когато поне едно от условията е TRUE. Следователно е известно като „ИЛИ логика“. Той също така ще сумира стойности, когато са изпълнени всички условия.
Първата част на формулата проверява диапазона C2:C13 за текста „West“ и сумира стойностите в диапазона B2:B13, когато съвпадението е изпълнено. Секундната част проверява за текстовата стойност „Юг“ в същия диапазон C2:C13 и след това сумира стойности със съответстващия текст в същия sum_range B2:B13. След това и двете суми се събират и се показват в клетка E3.
В случаите, когато е изпълнен само един критерий, той ще върне само тази сума.
Можете също да използвате няколко критерия вместо само един или два. И ако използвате множество критерии, по-добре е да използвате препратка към клетка като критерий, вместо да пишете директната стойност във формулата.
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
SUMIF с логика ИЛИ добавя стойности, когато е изпълнен поне един от посочените критерии, но ако искате да сумирате стойности само когато са изпълнени всички посочени условия, трябва да използвате новата функция SUMIFS().
Функция SUMIFS в Google Таблици (няколко критерия)
Когато използвате функцията SUMIF за сумиране на стойности въз основа на множество критерии, формулата може да стане твърде дълга и сложна и сте склонни да правите грешки. Освен това SUMIF ще ви позволи да сумирате стойности само в един диапазон и когато някое от условията е TRUE. Тук идва функцията SUMIFS.
Функцията SUMIFS ви помага да сумирате стойности въз основа на множество критерии за съвпадение в един или повече диапазони. И работи на логиката И, което означава, че може да сумира стойности само когато са изпълнени всички дадени условия. Дори ако едно условие е невярно, то ще върне '0' като резултат.
Синтаксис и аргументи на функцията SUMIFS
Синтаксисът на функцията SUMIFS е както следва:
=SUMIFS(обхват_обхват, критерий_диапазон1, критерий1, [диапазон_критерий2, ...], [критерий2, ...])Където,
- сума_диапазон – Диапазонът от клетки, съдържащи стойностите, които искате да сумирате, когато са изпълнени всички условия.
- критерии_диапазон1 – Това е диапазонът от клетки, където проверявате за критерии1.
- критерии 1 – Това е условието, което трябва да проверите спрямо критерии_диапазон1.
- крiteria_range2, Criterion2, …– Допълнителните диапазони и критерии за оценка. И можете да добавите повече диапазони и условия към формулата.
Ще използваме набора от данни в следващата екранна снимка, за да демонстрираме как функцията SUMIFS работи с различни критерии.
SUMIFS с текстови условия
Можете да сумирате стойности въз основа на два различни текстови критерия в различни диапазони. Например, да приемем, че искате да разберете общата сума на продажбите на доставения артикул от палатка. За това използвайте тази формула:
=SUMIFS(D2:D13,A2:A13,"Палатка",C2:C13,"Доставено")В тази формула имаме два критерия: „Палатка“ и „Доставено“. Функцията SUMIFS проверява за елемента „Палатка“ (критерии1) в диапазона A2:A13 (диапазон_критерии1) и проверява за състоянието „Доставено“ (критерии2) в диапазона C2:C13 (диапазон_критерии2). Когато и двете условия са изпълнени, тогава той сумира съответната стойност в диапазона от клетки D2:D13 (обхват_обхват).
SUMIFS с числови критерии и логически оператори
Можете да използвате условни оператори, за да създадете условия с числа за функцията SUMIFS.
За да намерите общите продажби на повече от 5 количества от който и да е артикул в щата Калифорния (CA), използвайте тази формула:
=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")Тази формула има две условия: „>5“ и „CA“.
Тази формула проверява за количества (Qty) по-големи от 5 в диапазона D2:D13 и проверява за състоянието „CA“ в диапазона B2:B13. И когато и двете условия са изпълнени (което означава, че има в един и същи ред), сумата се сумира в E2:E13.
SUMIFS с критерии за дата
Функцията SUMIFS също ви позволява да проверявате множество условия в същия диапазон, както и различни диапазони.
Да предположим, че искате да проверите общата сума на продажбите на доставените артикули след 31.05.2021 г. и преди датата на 6.10.2021 г., след което използвайте тази формула:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Горната формула има три условия: 31/5/2021, 10/5/2021 и Доставено. Вместо да използваме директни стойности за дата и текст, ние се позовахме на клетки, съдържащи тези критерии.
Формулата проверява за дати след 31/5/2021 (G1) и дати преди 10/6/2021 (G2) в същия диапазон D2:D13 и проверява за състоянието „Доставено“ между тези две дати. След това сумира съответната сума в диапазона E2:E13.
SUMIFS с празни и не празни клетки
Понякога може да искате да намерите сумата от стойности, когато съответната клетка е празна или не. За да направите това, можете да използвате един от трите критерия, които обсъдихме преди: „=“, „“ и „“.
Например, ако искате да сумирате само количеството артикули „Палатка“, за които датата на доставка все още не е потвърдена (празни клетки), можете да използвате критерии „=“:
=SUMIFS(D2:D13,A2:A13,"Палатка",C2:C13,"=")Формулата търси елемента „Палатка“ (критерии1) в колона A със съответните празни клетки (критерии2) в колона C и след това сумира съответната сума в колона D. „=“ представлява напълно празна клетка.
За да намерите общата сума на артикулите „Палатка“, за които е потвърдена датата на доставка (не празни клетки), използвайте „“ като критерий:
=SUMIFS(D2:D13,A2:A13,"Палатка",C2:C13,"")Току-що сменихме “=” с “” в тази формула. Той намира сбора от елементи на палатка с непразни клетки в колона C.
SUMIFS с логика ИЛИ
Тъй като функцията SUMIFS работи на логиката И, тя сумира само когато са изпълнени всички условия. Но какво ще стане, ако искате да сумирате стойност въз основа на множество критерии, когато някой от критериите е изпълнен. Номерът е да използвате множество функции SUMIFS.
Например, ако искате да добавите сумата на продажбите за „Раница за велосипеди“ ИЛИ за „Раница“, когато статусът им е „Поръчан“, опитайте тази формула:
=SUMIFS(D2:D13,A2:A13,"Сталачка за велосипеди",C2:C13,"Поръчани") +SUMIFS(D2:D13,A2:A13,"Раница",C2:C13,"Поръчани")Първата функция SUMIFS проверява два критерия „Раница“ и „Поръчано“ и сумира стойностите на сумата в колона D. След това втората SUMIFS проверява два критерия „Раница“ и „Поръчано“ и сумира стойностите на сумата в колона D. И след това , двете суми се събират заедно и се показват на F3. С прости думи, тази формула се сумира, когато се поръча или „база за велосипед“ или „раница“.
Това е всичко, което трябва да знаете за функциите SUMIF и SUMIFS в Google Sheets.