#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)));"---")

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

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