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

#007 Контролно число на ЕГН с една формула

Този пример спокойно може да го сложите в групата "не правете така в къщи":) Тази формула е толкова сложна, че не я препоръчвам дори на себе си. Една малка грешка и се получава грешен резултат. Но в крайна сметка се амбицирах преди време и реших да я направя.

Проблем: Да се изчисли контролната цифра на ЕГН в клетката A1.
Отговор: =IF(MOD(SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:9")),1))*({2;4;8;5;10;9;7;3;6})),11)=10, 0, MOD(SUMPRODUCT(VALUE(MID(A1, ROW( INDIRECT("1:9")) ,1))*({2;4;8;5;10;9;7;3;6})),11))

Nice А?:):) Ето анализът на формулата:

Контролно число: Последната цифра на ЕГН-то се нарича контролна и се изчислява по следния алгоритъм:
1. Всяка от първите девет цифри се умножава по съответно тегло. Теглата са: 2,4,8,5,10,9,7,3,6
2. Намира се сумата на тези произведения
3. Намира се остатъкът от делението на 11 на намерената в точка две сума
4. Ако остатъкът е по-малък от 10 това е контролното число, ако е равен на 10 контролното число е 0!

1. Извличане на първите девет цифрите от ЕГН-то  една по една: VALUE(MID(A1,ROW(INDIRECT("1:9")),1))

 Тук се симулира цикъл във формула. Няколко думи за неговото осъществяване:

• Ако стойностите не са много може просто да изброят заградени в {} и разделени със "," (хоризонтален масив)! В нашия случай трябваше да се напише {1,2,3,4,5,6,7,8,9}, което като се замисля не изглежда прекалено дълго и зле:)
• Когато стойностите са повече се използва конструкцията Row(начало:край). Например Row(1:9) или Row(1:999).... Тази конструкция има един недостатък. При местене на формулата тя се "настройва" за новото място. Не ни спасява и "трикът" с използването на абсолютни адреси например Row($1:$9). В този случай формулата не се променя  при преместване, но става каша ако изтриваме или вмъкваме редове в района на първи и девети ред. За това използвайте този метод предпазливо.
• Най-стабилния е метода използван в примера ROW(Indirect("начало:край"). В този случай формулата не зависи от "околната среда" и нейното местоположение:) Ще използвам този начин за правене на вътрешен цикъл и  в други примери.

Функцията Value преобразува извлечение символ в цифра. В интерес на истината съм подходил доста "консервативно" към проблемът, но и без това формулата си е сложна да правя други трикове в нея:) Като резултат се получава масив от първите девет цифри.

2. Сума на произведенията на цифрите и теглата:

SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:9")),1))*({2;4;8;5;10;9;7;3;6}))

Тук трябва да се обърне внимание на масивът с теглата. Той е "вертикален"! Ха сега:) Пак ви намерих занимание за четене:) Ето тук (на англйски): http://office.microsoft.com/en-us/excel-help/more-arrays-introducing-array-constants-in-excel-HA001087291.aspx Тази "подробност" ми коства около час когато правих формулата де;) Т.е. е важно, че разделителят на елементите е ";" а не ","! (тук възниква въпросът как се отбелязват вертикалните и хоризонталните масиви при друг вид езикови настройки (друг десетичен знак и друг знак за разделител на списъци) за което ще пиша в коментар под темата когато го тествам!)

Няма да се спирам защо се използва SumProduct а не Sum (четете по-старите теми)!

3. Намиране остатък от делението на 11.Нищо сложно при използването на функцията Mod.

MOD(SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:9")),1))*({2;4;8;5;10;9;7;3;6})) ,11)

4. Поставяне в If конструкция =IF(остатък=10,0,остатък).

Ми това е:)

#006 Брой на уникалните стойности в зона

В някой примери се налага да се преброят уникалните стойности в група клетки.  Формулата която се използва в случая е:

=SumProduct(1/CountIf(Зона,Зона)). Първия път когато срещнах тази формула ми се видя доста чудата и за това ще се опитам накратко да анализирам как действа с реален пример.

Пример: Да се намери броя на уникалните стойности в зоната A1:A8.

Зона клетки

Отговор: =SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8)). Ако въведете тази формула ще видите верния отговор 5.  Както се вижда формулата работи и за текстови и за числови данни. Зоната не е задължително да се състои от една колона.

1. COUNTIF(A1:A8,A1:A8) - До сега най-вероятно сте използвали CountIF с единична стойност за втория параметър (условието за броенето). Оказва се, че и условието може да бъде зона. Всъщност в този случай функцията се "развива" според втория параметър  (само по втория!) и се получават 8(!) отделни функции...

• COUNTIF(A1:A8,A1) = COUNTIF(A1:A8,"aaa")=3 ("aaa" го има три пъти!)
• COUNTIF(A1:A8,A2) = COUNTIF(A1:A8,"111")=2 ("111" го има два пъти!)
• COUNTIF(A1:A8,A3) = COUNTIF(A1:A8,"bbb")=1 ("bbb" го има само един път!)
• и т.н.

2. В резултат на изпълнението на функцията се получава масив от осем елемента:
{3,2,1,3,1,2,1,3}
3. Върху този масив се прилага операцията 1/{}. Което дава следния резултатен масив:
{1/3, 1/2, 1, 1/3, 1, 1/2, 1, 1/3}. Всъщност това е вид "нормализиране" на стойностите които ги има повече от един път.
4. Върху приложения масив се използва функцията Sumproduct (поради дискутирания "дефект" на Sum при работа с масиви) за намиране сумата на елементите която е 5 (броя на уникалните стойности).

Ето така действа тази "магическа" функция:)

#005 Формулите за масиви и как да се отървем от тях:)

Това е първи "сбъсък" с т. нар. формули за масив (Array formula)... Тази тема ще има продължения и често ще има примери, в които ще използвам такива формули... За начало се запознайте с тези базови понятия:

http://office.microsoft.com/bg-bg/excel-help/CH001003700.aspx
Доста трикове има тук (на английски):

http://office.microsoft.com/en-us/excel-help/guidelines-and-examples-of-array-formulas-HA010228458.aspx


Всъщност леко ви подведох:) Аз ще се опитвам всячески да избягвам такива формули...:) Ето малко примери и пояснения:

Пример 1: Да се намери общата дължина на текста в група клетки.

В "триковете" от втората връзка се вижда, че се препоръчва следната формула {=SUM(LEN(A1:A5))} (Ограждащите скоби {} не ги въвеждате а се появяват АВТОМАТИЧНО след като натиснете Ctrl+Shift+Enter при въвеждането на тази формула.... За повече подробности вижте препоръчаните връзки!

Формулите от този вид имат един досаден недостатък... При редактиране трябва да се сетите, че формулата е била такава! В реда за редактиране след двойно щракане (или F2) ограждащите скоби изчезват :( Т.е. ако решите да промените зоната и редактирате формулата ПАК трябва да натиснете Ctrl+Shift+Enter за въвеждането, което си е досадно и често и аз го забравям... ... и се появява досадната грешка #Value :) (в някой примери не се появява грешка, а грешен резултат, което е още по неприятно)

Да видим къде е проблемът и защо се налага използването на такъв вид формула... Частта Len(A1:A5) връща тип масив а не тип зона! Т.е. връща нещо от сорта на {5,4,3,3,2} където числата са дължините на текста в клетките... За съжаление функцията SUM не е чувала за понятието масив в нормален режим и за това използваме режим масив.

За щастие има друга функция, която е чувала:) Това е функцията SumProduct... Тази функция работи прекрасно с масиви. И примерът може да бъде заменен с "нормалната" формула =SUMPRODUCT(LEN(A1:A5)) !

Пример 2. Изчисляване средна стойност на група клетки изключвайки нулевите стойности... В "триковите" примери се дава формула за масив {=AVERAGE(IF(F1:F5<>0,F1:F5))}. Няма да се спирам на тази формула ами направо ще посоча нейните заместители. Нормалните =AVERAGEIF(F1:F5,"<>0") за Excel 2007 (и следващи версии) или по-съвместимото решение работещо и на по-старите версии =SUMIF(F1:F5,"<>0")/COUNTIF(F1:F5,"<>0").

Пример 3. Да се намери броят на разликите между две зони. Тук се търси "пълно" съвпадение на стойности и на тяхната позиция. Примерът който е даден е с формулата за масиви {=SUM(1*(A1:A4<>B1:B4))}. Както е отбелязано в текста, във формулата е използван един трик за преобразуване на масив от логически стойности в числа. Защото сравнението ще върне масив от вида {TRUE, FALSE, FALSE....} в зависимост от резултатите от сравнението и когато се умножи по 1 се получава {1,0,0...}. Същият ефект може да се постигне и с два знака минус пред масива. Т.е. формулата може да се представи и като {=SUM(--(A1:A4<>B1:B4))}.

Използвайки "нормалната" SumProduct се получава : =SUMPRODUCT(--(A1:A4<>B1:B4))