#024 SumProduct или дисекция на трикови формули

Вчера пак обяснявах в един форум за SumProduct трик, който не беше разбран и реших да се опитам (дано е успешно) да разбуля тайната на SumProduct и да обърна внимание на подводни камъни при използването и. Тази тема може да се каже е противоположна на #005, защото ще видите, че много често ще използвам формули за масиви. Припомням, че това са формули които се въвеждат с Ctrl+Shift+Enter (за това и ще ги означавам с CSE) и след тяхното въвеждане имат {} около тях. За повече прочетете първата препоръчана връзка в #005!

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

0. Начин на действие. =SUMPRODUCT(B1:B6,C1:C6). Това е "класическото" приложение на SumProduct. Служи за умножение на две зони и намиране на сумата на произведенията. Т.е. ще се изчисли B1*C1+B2*C2+...+B6*C6. Спокойно може да напишете формулата като =SUMPRODUCT(B1:B6*C1:C6). Така е по-ясна. Всъщност този начин на записване ви поставя въпросът не може ли да използваме SUM?? Отговорът е ДА! Но като CSE формула!

{=SUM(B1:B6*C1:C6)} . Всъщност Sum има дефекта да не може да обработва масиви в нормален режим.

1. Да се намери сумата на числата за колона C за който са изпълнени две условия:

Съдържанието на колона A да е ABCD И колонка B да е по-голяма от 7.

След малко ровене в Google може да достигнете до :

=SUMPRODUCT(--(A1:A6="ABCD"),--(B1:B6>7),C1:C6)

Да видим как работи тази "магическа" формула. Тук се използва трикът с двата минуса за преобразуване на логически стойности в единици и нули. Т.е. първият масив ще съдържа {1,0,1,0,1,0}, втория {1,1,1,1,0,1} в резултат на тяхното умножение ще се получи {1,0,1,0,0,0}. И резултатния масив се умножава по стойностите на колонка C (тук са числа). 1х100+1х90=190.

Нищо сложно. Тази формула може да се запише и като
=SUMPRODUCT((A1:A6="ABCD")*(B1:B6>7)*C1:C6). Тук при самото умножени се извършва преобразуването и няма нужда от минусите! Ако сте разбрали предходната точка вече сте наясно, че може да се използва и CSE формулата {=SUM((A1:A6="ABCD")*(B1:B6>7)*C1:C6)} !

И двете решения обаче изглеждат доста странно дори и да са ви станали ясни. В Excel 2007 са се погрижили за по-лесното сумиране по няколко критерия. За целта си има функция SumIfS !

=SUMIFS(C1:C6,A1:A6,"ABCD",B1:B6,">7") 

Единствен недостатък е, че става за Excel 2007 и следващи. Но няма нищо "триково" в нея:)

2. Да се намери БРОЯТ на елементите за който са изпълнени две условия:

Съдържанието на колона A да е ABCD И колонка B да е по-голяма от 7.

=SUMPRODUCT(--(A1:A6="ABCD"),--(B1:B6>7)) . Отново SUMPRODUCT но този път за броене!?! Ако сравните с първата формула от предходния пример, ще видите че липсва само частта с колонка C (реалната стойност). Всъщност тук намираме сумата на нулите и единиците от резултатния масив (маркиран в зелено в горния пример). Тъй като в резултат на умножението се получава единица там където е вярно нашата цел е да съберем тези единици. Което е БРОЯ.  (NB! ако елементите са единици тяхната сума е равна на техния брои. Което се знае от всеки първокласник:):)

Ето още два варианта на същата формула: =SUMPRODUCT((A1:A6="ABCD")*(B1:B6>7)) и CSE {=SUM((A1:A6="ABCD")*(B1:B6>7))} . Както се вижда от този пример SumProduct може да се използва както за сумиране така и за броене!

За Excel 2007 и следващи: =COUNTIFS(A1:A6,"ABCD",B1:B6,">7")  , което си е по-класическо.

3. Да се намери най-голямата дата от колонка D за стойност на колонка A "АBCD". 

С помощта на Google стигаме до следната формула която дава верен резултат (не забравяйте да оформяте резултатната клетка като дата!):

=SUMPRODUCT(MAX((A1:A6="ABCD")*(D1:D6)))

не случайно съм оцветил в червено това решение. Но да видим защо работи първо:) 

Умножение на масиви: (A1:A6="ABCD")*(D1:D6) . Вече сте се досетили, че първия масив ще съдържа нули и единици в зависимост от условието и след това като се умножи по датите (които са числа) ще останат датите или НУЛИ в зависимост от стойността на условието!

С MAX извличаме най-голямата дата. За какво ни е SumProduct?! За нищо:) Т.е. за да накара функцията  Max да започне да работи с масиви. в нормален режим Т.е. спокойно може да си заменим функцията с CSE варианта {=MAX((A1:A6="ABCD")*(D1:D6))}

Дали ще ползвате единия вариант или другия си е ваше решение. Но този вариант не е универсален!! За това не му се доверявайте винаги. За това ще видим в следващия пример.

4. Да се намери най-голямото число от колонка E за стойност на колонка A "АBCD". 

Тук по инерция може да напишем

=SUMPRODUCT(MAX((A1:A6="ABCD")*(E1:E6))) и..... ще получим НУЛА:) Сега е моментът да погледнете в предходната точка и да осъзнаете защо съм оцветил в червено нулата:) Там където условието е грешно в резултатния масив има нула. Която е МАКСИМАЛНАТА стойност в нашия случай (за да е гадно умишлено сложих всички стойности да са отрицателни):))

Така че "триковия" пример не работи в случай на отрицателни стойности!!!  Нямаше да работи с Мin за дати, защото щеше а има нула което е по-малко за най-малката дата! Мисля че е ясно,че и CSE формулата от предния пример няма да работи защото тя отново използва умножение с масиви. Т.е. ТРИКЪТ не е толкoва трик след като не може да се приложи във всички ситуации и не може лесно да бъде заменена функцията Max с Min!

 Ето правилния вариант (CSE): {=MAX(IF(A1:A6="ABCD",E1:E6))} За съжаление на някой любители на Sumproduct няма да може да се накара IF да проработи в нормален режим. Тук няма умножение на масиви. Просто се вземат само стойностите за който е вярно условието. Запомнете този вариант и въпреки, че решенията в точка 3 работят е по-добре да използвате:
{=MAX(IF(A1:A6="ABCD",D1:D6))}!

Ето два бързи примера:)

5. Да се намери най-малкото число от колонка E за стойност на колонка A "АBCD". 

{=MIN(IF(A1:A6="ABCD",E1:E6))}

6. Да се намери най-малкото число от колонка Е за  за стойност на колонка A "АBCD" И стойност в B>7.

=MIN(IF(((A1:A6="ABCD")*(B1:B6>7)),E1:E6))

Mоже да се каже че в тази тема ви показах как да си направите функции MinIf и MaxIf:)