петък, 23 март 2012 г.

#049 "Умна" страница календар

Скоро помагах по правенето на едни отчетни таблици. В тези таблици има аналогични листи за всеки месец. Замислих се, че до сега съм правил висш пилотаж с бутони за размножаване на страниците и донастройка на таблицата с код. Реших да не ползвам код и направя "умен" работен  лист който сам да се настройва!  Ето резултата: Споделени таблици (таблицата се казва Calendar-Sheet.xls). Старал съм се да е работоспособен и за старите версии на Excel преди Excel 2007!


Лист с календар


Листа показва името на месеца,  датите, названието на дните и номера на седмицата. Наричам този лист "умен", защото ако го копирате с име "2" ще се получи календар за месец Февруари, ако е с  име "3" календар за месец Март и т,.н. Т.е. спокойно вие може да добавите вашите данните, формули и после само да копирате този лист за останалите месеци!
NB! - Най-лесно можете да копирате  работен лист като влачите неговото име със задържан клавиш CTRL. Не забравяйте след като го копирате да смените неговото име с новия месец!

Имена на листите
 Цялата магия се състои в използване името на листа! Ето малка дисекция на формулите намиращи се в този лист.

Формула в клетка АI1 (внимавайте да не я изтриете по погрешка!). Може да я скриете. Там се взема името на листа и се преобразува в число. 
=VALUE(MID(CELL("filename";AH1);FIND("]";CELL("filename";AH1))+1;255))

  • CELL("filename";AH1) - Функцията Cell е много екзотична. В случая се използва нейния параметър "filename". Като втори параметър се посочва произволна клетка от таблицата (важно е да не съдържа съобщение за грешка!). Тук "далаверата" че освен името на файла ( заедно с пътя до него!) функцията връща и име на работния лист! (например: "F:\Documenti\[calendar-sheet.xls]1")
  • FIND("]";CELL()) - Намираме знака "]" който означава край на името
  • MID(CELL();FIND))+1;255)) - Извличаме от намерената позиция +1 (т.е. от следващия знак) всичко останало. Избрал съм мързеливия подход написвайки голямо число за брой знаци:) Може и по прецизно да се вземе точния брой знаци, но при положение, че не дава грешка защо да не се възползваме от тази възможност:) Така получаваме името на листа. Този трик може да използвате ако в други случаи ви трябва името на листа!
  • =Value(Mid()) - Преобразуваме текста в числова стойност! Разбира се ако вашите таблици имат имена съдържащи нецифрови символи тази стъпка не се прави!

 Формула в клетка C4: "Конструираме" първата дата в месеца.

=DATE(YEAR(NOW());$AI$1;1)

Формула в клетка D4 (и всички останали дати): Тъй като месеците имат различен брой дни се прави проверка дали получената нова дата (+1 на предходния ден) е все още в същия месец!
=IF(ISNUMBER(C4);IF(MONTH(C4+1)=$AI$1;C4+1;"");"") 

Формула в клетка C5: Име на деня от седмицата. Винаги го връща на български независимо от регионалните настройки! За повече информация вижте темата #013 в блога!=TEXT(C4;"[$-402]dddd") 

Формула в клетка C1: Показва името на месеца и годината.=UPPER(TEXT(C4;"[$-402]MMMM")) & " " & YEAR(C4)


Копирайте листа колкото пъти ви трябва, променете неговото име и ползвайте със здраве:)

Успех
















неделя, 18 март 2012 г.

#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) Умножава двата масива (т.е. там където има код се взема сумата (т.е се умножава по единица), там където няма код сумата се умножава по нула). След което се  намира сумата на произведенията.


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