Този урок демонстрира как да използвате Изклел SUMIF и SUMIFS Funcтиони в Excel и Google Sheets за сумиране на данни, които отговарят на определени критерии.
Преглед на функцията SUMIF
Можете да използвате функцията SUMIF в Excel за сумиране на клетки, които съдържат определена стойност, сумирани клетки, които са по -големи или равни на стойност и т.н.
(Забележете как се появяват входовете на формулата)
Синтаксис и аргументи на функцията SUMIF:
| 1 | = SUMIF (диапазон, критерии, [сума_диапазон]) |
диапазон - Обхватът на клетките, към които искате да приложите критериите.
критерии - Критериите, използвани за определяне на кои клетки да се добавят.
sum_range - [по избор] Клетките за събиране. Ако sum_range е пропуснат, клетките в диапазона се добавят заедно.
Какво представлява функцията SUMIF?
Функцията SUMIF е една от по -старите функции, използвани в електронни таблици. Използва се за сканиране през диапазон от клетки, които проверяват за определен критерий, и след това добавят стойности в диапазон, който съответства на тези стойности. Първоначалната функция SUMIF беше ограничена само до един критерий. След 2007 г. беше създадена функцията SUMIFS, която позволява множество критерии. По -голямата част от общата употреба остава същата между двете, но има някои критични разлики в синтаксиса, които ще обсъдим в тази статия.
Ако все още не сте го направили, можете да прегледате голяма част от подобната структура и примери в статията COUNTIFS.
Основен пример
Нека разгледаме този списък с регистрирани продажби и искаме да знаем общия доход.
Тъй като имахме разход, отрицателната стойност, не можем просто да направим основна сума. Вместо това искаме да сумираме само стойностите, които са по -големи от 0. „По -голямо от 0“ е това, което ще бъде нашият критерий във функция SUMIF. Нашата формула да заявим това е
| 1 | = SUMIF (A2: A7, "> 0") |
Пример с две колони
Докато оригиналната функция SUMIF е проектирана да ви позволи да приложите критерий към диапазона от числа, които искате да сумирате, голяма част от времето ще трябва да приложите един или повече критерии към други колони. Нека разгледаме тази таблица:
Сега, ако използваме оригиналната функция SUMIF, за да разберем колко банани имаме (изброени в клетка D1), ще трябва да дадем диапазона, който искаме да сума като последен аргумент и нашата формула би била такава
| 1 | = SUMIF (A2: A7, D1, B2: B7) |
Когато обаче програмистите в крайна сметка разбраха, че потребителите искат да дадат повече от един критерий, беше създадена функцията SUMIFS. За да се създаде една структура, която да работи за произволен брой критерии, SUMIFS изисква първо да се посочи диапазонът на сумите. В нашия пример това означава, че формулата трябва да бъде
| 1 | = SUMIFS (B2: B7, A2: A7, D1) |
ЗАБЕЛЕЖКА: Тези две формули получават един и същ резултат и могат да изглеждат сходни, затова обърнете специално внимание коя функция се използва, за да сте сигурни, че изброявате всички аргументи в правилния ред.
Работа с дати, множество критерии
Когато работите с дати в електронна таблица, въпреки че е възможно да въведете датата директно във формулата, най -добре е датата да е в клетка, така че да можете просто да се позовавате на клетката във формула. Например това помага на компютъра да знае, че искате да използвате датата 27.05.2020 г., а не числото 5, разделено на 27, разделено на 2022.
Нека да разгледаме следващата таблица, която записва броя посетители на сайт на всеки две седмици.
Можем да посочим началната и крайната точка на диапазона, който искаме да разгледаме в D2 и E2. Тогава нашата формула за сумиране на броя на посетителите в този диапазон може да бъде:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Забележете как успяхме да обединим сравненията на „=“ с препратките към клетки, за да създадем критериите. Също така, въпреки че и двата критерия бяха приложени към един и същ диапазон от клетки (A2: A7), трябва да изпишете диапазона два пъти, веднъж за всеки критерий.
Множество колони
Когато използвате множество критерии, можете да ги приложите към същия диапазон, както направихме в предишния пример, или да ги приложите към различни диапазони. Нека комбинираме нашите примерни данни в тази таблица:
Ние сме настроили някои клетки за потребителя да въведе това, което иска да търси в клетки E2 до G2. По този начин се нуждаем от формула, която ще събере общия брой на ябълките, събрани през февруари. Нашата формула изглежда така:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS с логика тип OR
До този момент примерите, които използвахме, бяха сравнения на база И, където търсим редове, които отговарят на всички наши критерии. Сега ще разгледаме случая, когато искате да потърсите възможността ред да отговаря на един или друг критерий.
Нека да разгледаме този списък с продажби:
Бихме искали да добавим общите продажби както за Адам, така и за Боб. За да направите това, имате няколко опции. Най -простото е да добавите два SUMIFS заедно, така:
| 1 | = SUMIFS (B2: B7, A2: A7, "Адам")+SUMIFS (B2: B7, A2: A7, "Боб") |
Тук сме накарали компютъра да изчисли нашите индивидуални резултати и след това да ги съберем.
Следващата ни опция е добра, когато имате повече диапазони от критерии, така че не искате да пренаписвате цялата формула многократно. В предишната формула ръчно казахме на компютъра да добави два различни SUMIFS заедно. Можете обаче да направите това, като напишете критериите си в масив, например:
| 1 | = SUM (SUMIFS (B2: B7, A2: A7, {"Адам", "Боб"})) |
Вижте как е конструиран масивът в къдравите скоби. Когато компютърът оцени тази формула, той ще знае, че искаме да изчислим функция SUMIFS за всеки елемент в нашия масив, като по този начин създадем масив от числа. Външната функция SUM след това ще вземе този масив от числа и ще го превърне в едно число. Преминавайки през оценката на формулата, тя ще изглежда така:
| 123 | = SUM (SUMIFS (B2: B7, A2: A7, {"Адам", "Боб"}))= SUM (27401, 43470)= 70871 |
Получаваме същия резултат, но успяхме да напишем формулата малко по -кратко.
Справяне с заготовки
Понякога вашият набор от данни ще има празни клетки, които трябва да намерите или да избегнете. Задаването на критериите за тях може да бъде малко сложно, така че нека разгледаме друг пример.
Обърнете внимание, че клетка A3 е наистина празна, докато клетка A5 има формула, връщаща низ с нулева дължина на „”. Ако искаме да намерим общата сума на наистина празни клетки, бихме използвали критерий „=“ и нашата формула ще изглежда така:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
От друга страна, ако искаме да получим сумата за всички клетки, които визуално изглеждат празни, ще променим критериите да бъдат „”, а формулата изглежда така
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Нека го обърнем: ами ако искате да намерите сумата от непразни клетки? За съжаление, настоящият дизайн няма да ви позволи да избегнете низ с нулева дължина. Можете да използвате критерий „“, но както можете да видите в примера, той все още включва стойността от ред 5.
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Ако не трябва да броите клетки, съдържащи низове с нулева дължина, ще искате да обмислите използването на функцията LEN вътре в SUMPRODUCT
SUMIF в Google Таблици
Функцията SUMIF работи абсолютно същото в Google Sheets като в Excel:
