#047 Търсене (трета част) Търсене в съдържанието на клетки

Продължаваме борбата с търсенето:) Отново да напомня, че въпреки "обратния" ред на подреждане на темите, е желателно да четете темите в правилния им ред (т.е. #001, #002 ...)... Често дадена тема използва по-стари "трикове".... За това не четете "новините" първо:):)

Ето задачата.... Дадени са кодове разположени в една клетка... Да се направи Vlookup подобна функция, но да търси във вътрешността на клетките....

Фигура 1: Изходни данни

Трябва да се търси в колонката "Кодове", като всеки код трябва да се третира като самостоятелна стойност!

Предварителна информация:

За търсене на текст във друг текст се използват функциите Find и Search. Разликата е в това че Find прави разлика между малки и главни букви. За повече информация ето синтаксиса им:Функция Find Функция Search.
Проблемът е, че са малко неудобни за ползване. Ако текстът го има връща число показващо къде се намира, ако обаче го няма връща грешка от тип #VALUE! а не НУЛА (както правят подобните функции в "културните" езици за програмиране)! T.e ако имате желание да правите трикове от типа Find(.....)>0 с тайната надежда да получите масив съдържащ True, False сте в грешка... Масивът ще съдържа True,#Value стойности.... По-конкретните примери ще видите как елиминирам този "бъг":):) В примера по-долу ще използвам Find по две причини: защото търся число и няма значение регистъра и защото съм мързелив и Find е по-кратка:):) Ако обаче искате търсене на съвпадение в текст без отчитане на регистъра, ще се наложи да използвате Search!

Подготовка на изходните данни:

Както се вижда кодовете са разделени със знак запетая. Това разграничава един код от друг. Важно е да не използваме на сляпо функциите за търсене, защото може да изпаднем в конфузна ситуация. Какво имам предвид.

        Проблем1: Нека да потърсим кодът "23" в изходните данни.... Ще видите че точно този текст го има както в първите пет клетки или като част на кодовете ("123"  ,  "12345" и "123456") или като самостоятелен код! Т.е. ще е груба грешка да търсим чрез =Find("23";A2)! Мисля, че се досетихте,че по-хитро ще е да търсим =Find(",23,";A2). Да търсим стойността заградена със запетайки!

        Проблем2: Когато си мислите, че всичко е цветя и рози може да се усетите, че първия и последния код в списъка не са заградени и от двете страни със запетая! Правилното търсене е =Find(",23,";"," & A2 & ",")!!! Т.е. на нас ни трябва подобрена версия на изходния списък в който нещата да са в по-стандартен вид, при който всеки код да изглежда по един и същи начин! За целта съм създал помощна колонка.

Помощна колонка

В клетката D2 е записана формулата: ="," & SUBSTITUTE(A2;" ";"") & ","

Освен, че съм добавил запетайки преди и след списъка за всеки случай съм махнал и излишните интервали от списъка с кодове (страх лозе пази):):) Разбира се тази колонка може да се намира достатъчно отдалечено от данните или да е скрита, за да не пречи на въвеждащия.Така имаме колонка в която всяка стойност е заградена със знак запетая и от двете страни! В нея ще търсим.

Именуване на области:

За по-компактни формули съм именувал областите с данни както следва:

D2:D7 - kodove (НЕ колонка А!!)

B2:B7 - gradove

C2:C7 - sumi

Резултатна таблица

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

В клетка B2 формулата е: 

{=IFERROR(INDEX(gradove;MATCH(TRUE;FIND("," & A2 & ",";kodove)<>0;0));"---")}

Бележки по формулата:

• Формулата е CSE (въвежда се със Ctrl+Shift+Enter без {})!
• Чрез FIND("," & A2 & ",";kodove)<>0 търсим стойността от A2 (като и нея заградим в ","!!!) в кодове и чрез <>0 получаваме масив съдържащ True, #Value .... Tук не е проблем, че едната стойност е грешка. В случая на нас ни трябва True стойностите (първата True стойност)!
• Чрез MATCH(TRUE;FIND(...)<>0;0) търсим първото появяване на TRUE в този масив... Така  получаваме реда който ни трябва..
• INDEX(gradove;MATCH(...)) ни дава името на града.
• "Пакетираме" с IFError да не се появяват досадните #N/A грешки (на тяхно място се появяват досадните "---":):) =IFERROR(INDEX(...);"---") 

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

 В клетка C2 формулата е:
{=IFERROR(INDEX(sumi;MATCH(TRUE;FIND("," & A2 & ",";kodove)<>0;0));"---")}

Тук няма интрига:) Формулата е аналогична на предходната. Само извличаме данните от колонка Сума.

  

Пример 3 : Да се изведе сумата на града с даден код. Ако има дублажи да се изведе сумата на ВСИЧКИ  градове с посочения код!

В клетка D2 формулата е:

{=SUMPRODUCT(--IFERROR(FIND("," & A2 & ",";kodove)<>0;False);sumi)}

Тук идеята е да "умножим" два масива . Единия масив съдържа  едно или нула  в зависимост дали кодът го има или не, а втория масив съдържа сумите. 

Бележки по формулата:

• Формулата е CSE (въвежда се със Ctrl+Shift+Enter без {})!
• Чрез FIND("," & A2 & ",";kodove) търсим стойността от A2 (като и нея заградим в ","!!!) в кодовете. Резултата ще е число или ГРЕШКА (вижте предварителните бележки!).
• Чрез израза IFERROR(FIND()<>0;False) се прави"трикът" да се получи True/False масив. Ако Find върне стойност изразът Find()<>0 ще върне True, ако е грешка "пакетиращата" функция ще върне False! 
• --IFERROR() "обръща" True/False масива в единици и нули
• SUMPRODUCT(--IFERROR();sumi) Умножава двата масива (т.е. там където има код се взема сумата (т.е се умножава по единица), там където няма код сумата се умножава по нула). След което се  намира сумата на произведенията.

Успех...... :) :)