Сумиране по условия
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, защото върнатата сума вече принадлежи към друга категория. Тези две промени проверяват едновременно съвпадението по категория и знаковата аритметика.
Опитай примера
Използвай началните стойности или ги промени, за да провериш как работи формулата.