#029 Условно събиране с DSUM

Това е продължение на #028. Много често се подминават така наречените функции за работа с бази от данни ( Database или D-функциите). Може би ще направя отделна тема за тях. При тях проблемът е че на пръв поглед изглеждат "разхвърляни". От една страна имаме входна област (база данни) от друга имаме зона критерии. Това разпределяне обаче опростява много самите формули. Ето примера за условно сумиране.

Условно форматиране

Тук имаме две зони. Зоната в която са данните и зоната за критерии. Както казах тази тема няма за цел да влиза в детайли и да разглежда видовете условия и начини за формиране на зоната за критерии. Само искам да обърна внимание, че при зоната за критерии се посочва СЪЩОТО име на колонка (колонки) както са в областта за данни!

Формулата за сумиране е: =DSUM(A1:B6,B1,D1:D3)
- Данни (заедно със заглавния ред)
- Какво сумираме (само заглавната клетка)
- Зоната с критерии (заедно със заглавния ред)

Това е:) Очаквайте още за D-функциите скоро:)

#028 Условно събиране с ИЛИ

Попитаха ме как да се намери сумата по условие когато става дума за ИЛИ.

Примерни данни

 

Въпросът беше да се намери сумата на клетките в колонка B за стойностите в колонка А "aaa" И "bbb". Въпросът не е коректен, защото просто няма как да са хем "aaa" И "bbb"":)  Връзката между двете условия  е ИЛИ! Това е важно за да може и формулата да бъде вярна.

Ако опитаме да приложим мощната функция SumIfS (която я има само в Excel 2007 и следващи) и напишем =SUMIFS(B2:B7,A2:A7,"aaa",A2:A7,"bbb") ще получим НУЛА! Защото тази формула означава "Сумирай клетките от колонка B за който първото условие (А2:A7="aaa") И второто условие (A2:A7="bbb") са верни! Двете условия са несъвместими и за това просто трябва да ги отделим в отделни суми. Сумата на числата за които първата колонка е "aaa" и към нея да добавим сумите на числата с "bbb"! Разделяме формулата на две части и резултатите ги  сумираме. Разделяй и владей:) Сума на два независими един от друг  SumIFS-a! Просто е:))

=SUMIFS(B2:B7,A2:A7,"aaa") + SUMIFS(B2:B7,A2:A7,"bbb")

След като във всяка една от функциите условието е едно може да се използва и по-старата функция SUMIF която си я има и в по-старите версии на Excel, но позволява само едно условие!! (NB! Не забравяйте, е параметрите са разположени по друг начин спрямо SumIfS!!)

=SUMIF(A2:A7,"aaa",B2:B7)+SUMIF(A2:A7,"bbb",B2:B7)

Да отидем малко по-далече. Много често за заместител на SumIF и на SumIFS се препоръчва Sumproduct (разгледайте всички тагнати теми за SumProduct). Не съм му фен, но все пак да дам и решението с нейна помощ. По инерция може да се напише:

=SUMPRODUCT((A2:A7="aaa")*(A2:A7="bbb"),B2:B7)

Ако разровите темите със SumProduct ще видите, че умножаването на двете условия е тяхното свързване. Но да не ви мътя главите тогава пропуснах (умишлено) да ви кажа, че умножението означава И! В нашия случай {Истина,Лъжа,Лъжа,Истина,Лъжа,Истина} И {Лъжа, Истина, Лъже,Лъжа,Истина,Лъжа}  дава масив от лъжи.

Сега е моментът да кажа, че ако искаме да свържем няколко условия с ИЛИ знакът е +! В този случай дава правилен резултатен масив {Истина,Истина,Лъжа,Истина,Истина,Истина} който се умножава от Sumproduct със зоната за сумиране (не забравяйте, че Истина е 1 а лъжата е 0!) И дава верен резултат:)
=SUMPRODUCT((A2:A7="aaa")+(A2:A7="bbb"),B2:B7)

Поуката от темата е да внимавате с ИЛИ и И съюзите:):) Ако използвате Sumproduct запомнете, че свързването на условията с * означава И, а със знака + означава ИЛИ!
 


Успех