#046 Търсене (втора част) или кога сумирането е търсене и кога не е :)

За по-лесно разбиране на нещата тук ви съветвам на прочетете първо Тема 23 и Тема 24 и цитираните в тях теми!


Бях помолен да се се "боря" със следния казус... Търсене по два критерия...

Проблем 1. При зададена таблица са данните да се върне резултат според две условия за търсене. Данните във входната таблица нямат дублажи!

Таблица в която търсим

Таблица с резултатна колонка

За по-лесно разчитане на таблицата съм именувал зоните в началната таблица (жълтите клетки) както следва:
A2:A8 - "к1"
B2:B8 - "к2"
C2:C8 -  "р"

Вариант 1:  Ще използваме SumifS ! Условна Сума?!? Даже когато данните нямат дублажи?! Понякога ни е трудно да осъзнаем, че когато елемента е един, сумата е равна на този елемент! Т.е. извеждайки условната  сума според даден критерий (или критерии) ние връщаме стойността на този елемент! :) Т.е. имаме "магия" как сумата се явява търсеното число.

В клетка C2 (на резултатната таблица) формулата е:
 =SUMIFS(р;к1;A2;к2;B2)

Вариант 2: Без да повтарям по-горните разсъждения вместо SumifS ще използвам Sumproduct (Sumifs го няма в Excel преди 2007!). За повече информация вижте темите за SumProduct.

В клетка C2 (на резултатната таблица) формулата е: 
=SUMPRODUCT(--(A2=к1);--(B2=к2);р)

Двата варианта имат един "дефект" (може би да е "ефект"?!)!  Ако все пак в началния списък има дублажи, горните две формули ще върнат СУМАТА на всички резултати, а не само първия срещнат елемент!! Ние обаче може да не искаме тази функционалност! Т.е. функциите за сума козината си менят, но нрава не!:):)

Да продължим с разсъжденията.... Ами ако резултатът не е число?!? Тук "магията" търсенето да се трансформира в условно сумиране изобщо не минава дори и да няма дублажи! Ето как се решават тези два казуса.

Проблем 2. При зададена таблица са данните да се върне резултат според две условия за търсене. Данните в резултатната колона може да са текст!

Таблица в която търсим

Таблица с резултатна колонка

За по-лесно разчитане на таблицата съм именувал зоните в началната таблица (жълтите клетки) както следва:

A2:A8 - "кк1"
B2:B8 - "кк2"
C2:C8 -  "рр"

Вариант 1: В клетка C2 (на резултатната таблица) формулата е:

 {=INDEX(рр;MATCH(A2&B2;кк1&кк2;0))} Формулата е CSE! (Въвежда се чрез Ctrl+Shift+Enter без {}!) 

Използваме оператора & за "слепване" (по научно "конкатенация") на два символни низа. Така правим едно общо условие чрез което търсим. По същия начин процедираме и с двете зони в които се намират критериите. За останалото се обърнете към темите които препоръчах в началото и помощната информация за функциите Match и Index (в блога има също теми за тях)!

Вариант 2: Понеже мразя CSE функции ще "пакетирам" Match да проработи със сложни масиви.

=INDEX(рр;SUMPRODUCT(MATCH(A2&B2;кк1&кк2;0)))

Вариант3: Ако искаме да не дава #N/A грешка ако няма съвпадение още едно "пакетиране":)

=IFERROR(INDEX(рр;SUMPRODUCT(MATCH(A2&B2;кк1&кк2;0)));"---")

ще показва "---" там където няма съвпадение (Можете да сложите какъвто искате текст, който да се появява при грешка!)

Когато след много мъки стигнете до тази формула и доволни, че сте я разбрали, не бързайте да  си тръгвате! :) Сега е момента да ви кажа, че тя е доста рискова! В някой ситуации може да се насадите на пачи яйца:)
В голяма беда сте, ако имате следните две ситуации (или подобни на тях):
Критерий1: Склад1 Критерий2: 12
Критерий1: Склад11 Критерий2: 2

В резултат на използването на оператора & ще получите едно и също нещо! Склад112 !

Т.е. ако търсите Склад1, 12 може да се "натресете" на резултата за Склад11,2!!! И ако не си проверявате нещата да се получи ГОЛЯМ проблем.
Внимавайте когато използвате & и преценявайте опасността от този начин на търсене в зависимост от конкретната ситуация!

Вариант4: "Разделяне" на двете проверки.

{=INDEX(рр;MATCH(1;(A2=кк1)*(B2=кк2);0))} (CSE функция!)

Тук хитростта е, че в резултат на операцията сравнение се създават два масива: масив с нули и единици в зависимост къде A2 се намира в първата зона и масив от нули единици в зависимост от това къде B2 се открива във втората зона. Ако в резултат на проверките са се получили примерени масиви {1,0,1,0,0} и {0,1,1,0,0},  то при  тяхното умножение се получава единица там където имаме единици и в двата изходни масива (т.е. там където И двете условия са верни!) В по-горния пример резултатът ще е масива {0,0,1,0,0}. Там чрез Match (0 означава точно търсене)  търсим индекса на първата ЕДИНИЦА (В примера ще върне 3)! (NB! Ако имаме дублажи, може и да имаме повече от една единица в резултатния масив, но Match ще върне индексът на първата намерена!). Чрез функцията Index и полученото число (резултатът от Match  всъщност е номера на първия ред където И двете условия са верни!) се извлича съответния резултат.

Вариант 5: Двойно пакетирана (за избягване на CSE и съобщения за грешки) функция!

=IFERROR(INDEX(рр;SUMPRODUCT(MATCH(1;(A2=кк1)*(B2=кк2);0)));"---")

Красиво:) И работещо както за резултат текст така и за резултат число!  Мисля, че за вас няма да е проблем да напишете такава функция за три или повече условия:)

Успех:)
П.П Както казах примерите могат да работят и без имен, а чрез  директно посочване на адресите на съответните зони!

#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 запомнете, че свързването на условията с * означава И, а със знака + означава ИЛИ!
 


Успех

#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:)