Arte SheetsНаучи. Опитай. Разбери.

Сумиране по условия

SUMIF по категория: сумиране на разходи и върнати суми

Сумирай редовете за избрана бюджетна категория със SUMIF, като запазиш отрицателните сторнирани суми и провериш общия сбор.

Дръж покупките и връщанията в един общ дневник

Общностна градина записва пет движения: Seeds 24, Tools 60, Seeds 18, Tools -12 и Compost 35. Отрицателният запис за Tools е върнат артикул, който намалява изразходваната сума за инструменти. Въведи категориите в A2:A6 и сумите със знак в B2:B6.

Постави Tools в D2 като категорията, която искаш да сумираш. Всички суми са в една обща валута; в това упражнение няма превалутиране или начисляване на данъци.

РедКатегорияСума
2Семена (Seeds)24
3Инструменти (Tools)60
4Семена (Seeds)18
5Инструменти (Tools)-12
6Компост (Compost)35

Избери редовете по категория и събери сумите им

SUMIF първо сравнява A2:A6 с категорията в D2. След това събира съответните стойности от B2:B6. За Tools участващите суми са 60 и -12, затова резултатът е 48.

Връщането не е изключение, което да премахваш от диапазона. Отрицателният му знак е начинът, по който дневникът отразява намалението. Филтрирането само на положителни суми би отговорило на друг въпрос: брутни покупки преди връщанията.

=SUMIF(A2:A6,D2,B2:B6)

Променяй въпроса, без да редактираш формулата

Замени Tools в D2 със Seeds. Сумата става 42 (24 плюс 18). Избери Compost и резултатът е 35. Клетката за избор се проверява много по-лесно, отколкото редактирането на текст в кавички вътре във формулата всеки път.

Можеш да запишеш категорията и директно като "Tools" във формулата, но не поставяй кавички около D2, когато имаш предвид препратка към клетка. "D2" търси категория, чийто текст буквално е D2.

Нулевият сбор не доказва липса на записи

Въведи Water — категория, която не присъства в дневника — и SUMIF ще върне 0. Сега отново избери Tools и промени връщането в B5 на -60: сборът пак е 0, въпреки че два записа отговарят на условието.

Следователно нулата може да означава липса на съвпадащи редове, само нулеви стойности или положителни и отрицателни суми, които взаимно се неутрализират. Прегледай редовете или преброй съвпаденията за категорията, преди да решиш кое обяснение важи. Не надписвай всеки нулев сбор с „Няма покупки“.

Защити изходните диапазони при копиране на обобщението

Да приемем, че D2, D3 и D4 съдържат списъка Tools, Seeds и Compost. Заключи изходните диапазони във формулата по-долу, преди да я копираш надолу до тези етикети. Клетката за избор D2 остава относителна, за да може следващият ред да използва D3.

Поддържай двата изходни диапазона подравнени. Категорията в A5 описва сумата в B5, а не в B4. Когато добавяш нови редове към дневника, разширявай и двата диапазона еднакво. Панелът за упражнение изчислява формулата, но не симулира влачене на манипулатора за запълване.

=SUMIF($A$2:$A$6,D2,$B$2:$B$6)

Премини към SUMIFS, когато добавиш второ условие

Сумата по категория изисква едно условие. Отчет за Tools, закупени през избран месец, изисква и условие за дата. Това е естествената следваща стъпка със SUMIFS и отделна колона за дата.

Редът на аргументите се променя: при SUMIF диапазонът със сумите е трети, докато при SUMIFS е първи. Не добавяй просто буквата S към името, оставяйки аргументите в стария ред. Продължи към следващия урок за пълен пример с няколко условия.

Провери знака с целенасочена промяна

Възстанови първоначалните данни и избери Tools. Промени B5 от -12 на 12: резултатът се покачва от 48 на 72. Формулата продължава да работи правилно, но промененият знак е обърнал смисъла на записа.

След това върни -12 и смени категорията в A5 на Seeds. Сега Tools дава общо 60, защото върнатата сума вече принадлежи към друга категория. Тези две промени проверяват едновременно съвпадението по категория и знаковата аритметика.

Опитай примера

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

Допълнително четене