# 027 Лента Developer

Това е една важна мини тема:) За обединяване на всички функции за програмиране в Еxcel 2007 и 2010 е създадена специална лента наречена Developer. Явно подценявайки своите потребители тази лента по подразбиране не е активна.

Лента Developer

Ето как се активира ...

... в Excel 2010

1. Активира се лентата  File
2. Команда Options
3. Customize Ribbon
4. Активизира се Developer.
5. OK
   
   
   

   

   Excel 2010

... в  Excel 2007

1. Натиска  се Microsoft Office бутона (кръглия бутон горе в ляво)
2. Бутон Excel Options
3. Избира се групата Popular
4. Активизира се  Show Developer tab in the Ribbon.
5. OK
   
   

#026 Макроси и функции (основни понятия)

Това е първата тема която се явява преход към макросите и функциите. Ще има няколко такива. От гледна точка на потребителите се разграничават две основни понятия:

1. Макрос. Това е пакет от действия който трябва да се активизира по някакъв начин (ще разгледаме начините за активизиране в отделна тема). Макросът позволява да се извършват действия за промяна съдържанието и оформянето на група клетки. Може да се създават и  изтриват редове, колони и работни листи и т.н. Т.е. може да се прави всичко с компонентите на една работна книга!

2. Потребителска функция. Това е функция която връща резултат в текущата клетка. НЕ МОЖЕ ДА ПРОМЕНЯ СТОЙНОСТТА И ОФОРМЛЕНИЕТО НА ДРУГА КЛЕТКА! Това е много важно да се запомни. Много хора не правят разлика между макрос и потребителска функция.

Проблемът на макросите е че някой/нещо трябва да ги активира, а проблемът при функциите е че връщат стойност само в текущата клетка и не могат да правят нищо друго!

От програмистка гледна точка нещата са леко по-сложни:) Първо за да е кашата пълна в ранните версии на Excel съществуваше специален език за писане на макроси. Това са така наречените XLM (да не се бърка с XML!) макроси. Остатъци от тях може да намерите още. (например ако от контекстното меню което се появява върху името на лист изпълните Insert ще видите три вида работни листи които са свързани с този анахронизъм).

Стария вид макроси

В следващите версии Microsft вградиха в продуктите си езика Visual Basic For Application (ще използвам популярното съкращение VBA) който е много по-мощен и стандартен. Няма да се учудя обаче, ако лека полека вградят .Net езиците (сега има възможност за програмиране, но не е вградено в самия офис пакет). Така че в момента като препоръчителна среда за програмиране е VBA.

Във  VBA понятието Макрос не съществува! Може да обособим четири важни понятия върху които ще се концентрирам в различни теми:

1. Процедура (Sub). Това е аналог на Макроса. В крайна сметка всяка процедура се появява в списъкът с макроси. За това аз ще говоря за процедури в следващите теми и ще забравя понятието макрос! 

2. Процедура-Събитие (Sub). Това е особена процедура, която се активира при настъпване на дадено събитие. Например промяна съдържанието на клетка, натискане на десен бутон на мишката и т.н. При тези процедури в зависимост от събитието се подават различни параметри (зоната която е избрана, клетката която е променена и т.н.).

3. Функция (Function). Функциите за разлика от процедурите освен извършваните от тях действия връщат резултат (стойност) в мястото на тяхното извикване.

4. Функции за работа в работни листи (Function). Това са функции които могат да се използват като останалите функции в работните листи. За да може една функция да стане такава тя трябва да отговаря на определени условия. Най-важното от което е в тялото си да не променя обекти на Еxcel!

Два вида процедури и два вида функции.Това е по-добро разграничаване от понятията Макрос и Потребителска функция в началото!

Начин на съхранение. Във версиите на Excel преди 2007 XLS файловете позволяват съхранение в себе си на процедури и функции. При новите формати за по-лесен контрол на съдържанието се дефинират два основни формата XLSX който НЕ МОЖЕ да съдържа процедури и функции и XLSM който МОЖЕ да съдържа процедури и функции. Това означава, че когато правите примери с програмен код ще се наложи да записвате в XLSM или в стария XLS формат (който не поддържа всички визуални възможности на новите версии на Excel)!

Сигурност. Office 2007 и 2010 имат сложна система за предпазване на потребителите и няма да ни стигне цяла тема. Ако не сте пипали настройките по подразбиране те блокират кода във вашата работна книга, но ви позволяват да го активирате. Просто следете информационното съобщение. Понякога е в отделен прозорец понякога е като лента!

Отделен прозорец за разрешаване на код

Информационна лента

Както обърнах внимание това са настройките по подразбиране за 2007 и 2010, но при Excel 2003 настройката по подразбиране е да бъдат блокирани всички макроси без предупреждение и възможност за активиране! За целта се налага да изпълните командата Tools/Macro/Security и да активирате предупреждаващото съобщение.

Настройки на Excel 2003

#025 Уникални стойности

Зададоха ми задачата как да се извлекат уникалните стойности от зона. Създаване на индекс.

Примерни данни

Първи вариант
Първото което ми мина през главата е да показвам мускули:) Т.е. да използвам формули. Ето този вариант.
Стъпка 1. Създаваме помощна колонка D.  В клетка D2 въвеждам формулата:

=IF(COUNTIF($B$2:B2,B2)=1,ROW(B2),"") . Тази формулка "маркира" тези редове в които за първи път се среща дадената стойност. Тук магията е в зоната посочена в CountIF.

$B$2:B2. Това е  полуабсолютна зона. (Може да направя отделна тема за зоните). Хитростта е че тази зона ще се разширява при влачене на формулата надолу. Т.е. във втората клетка ще стане $B$2:B3 и т.н. Този трик е доста популярен. "Маркировката" се изразява в посочването номера на реда където за първи път се среща дадената стойност (Countif=1).

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

Стъпка 2. Формула за извличане. Формулата в клетка C2 e :
=IFERROR(INDEX(B:B,SMALL(D:D,ROW(A1))),"")

Тук се използва трикът с променящ се при размножение брояч вътре във формулата. Row(A1) ще осигури брояч със стойност 1,2,3 и т.н. (NB. Внимавайте ако вмъквате редове или местите формулата, защото А1 ще стане например A10 и броячът ще започне от 10!). Този брояч осигурява извличането на първата по големина стойност от колонка D (използва се Small), после втората и т.н. (В нашия случай 2,5,6....). Остава да превърнем тези числа (редове от таблицата) в конкретни стойности. За целта използваме Index (има отделна тема)!

"Пакетираме" в IFerror за да избегнем отговорите при липса на стойност (ние не знаем колко са уникалните стойности затова "дърпаме" формулата достатъчно надолу.

Както казах това е "мускулестия вариант":)

Ето ви по-простичко решение)

Втори вариант

Изпълняваме командата Data/Advanced Filter:

Data/Advanced filter

Правят се следните настройки в диалоговия прозорец

Използва се възможността за копиране само на уникалните стойности! (За повече информация книжките за начинаещи):):) И готово:) Няма мускули няма трикове:) 

Всеки само си избира кой вариант да избере:)

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

#023 Търсене (Първа част?)

Тази тема няма за цел в детайли да ви запознае с функциите за търсене. Искам да обърна внимание на разликите (и приликите) между функциите и да обърнете внимание на техните особености. Няма да се спирам в детайли на техните параметри. За целта си има клавиш F1:)

1. Функциите Find и Search служат за търсене вътре в текст. Т.е. може да ги използвате за претърсване вътрешността на клетки. Разликата между двете функции е, че Find прави разлика между малки и главни букви когато търси! Двете функции връщат позицията на намерения текст. Т.е. число! За съжаление вместо нула ако няма успешен резултат от търсенето се връща грешка (#Value) което си е досадно (трябва да се обработи или с ISError или IFError).

2. Функцията Match служи да търси в област от клетки или масив. В този случай разглежда цялата клетка като едно цяло! Връща число показващо номера на първия елемент отговарящ  на даденото условие. Връща ПОЗИЦИЯ (брои от едно)!! (NB! Функцията може да търси и "приблизително" но за целта елементите трябва да подредени. За повече вижте помощната информация.) . Бих ви заострил внимание на подразбиращата се стойност на третия параметър (тип на търсенето)! Тя  НЕ Е 0(!!) (точно търсене). Ако искате да търсите точно не прескачайте този параметър!

3. Функците Vlookup, Hlookpup, Lookup отново служат за търсене в област от клетки или масив. За разлика от Match те връщат СТОЙНОСТ. Стойността се извлича от областта за търсене или от друга област. Всъщност тези функции са "глезотии" за улеснение на потребителите.:) Могат да бъдат създадени с помощта на други функции. Ще дам примери по-късно.

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

Ето примерните данни:

Примерни данни

Пример 1. Да се намери стойността на колонка "C" за 1.10.2010 в колонка "A".

Тук се решава с "простия" Vlookup.

=VLOOKUP(DATEVALUE("1.10.2010"),A1:C6,3,FALSE) 

Функцията връща стойността на ТРЕТАТА колонка от блока търсейки в първата колона. Да не е прекалено скучно с тази формулка ви напомням, че датите и часовете са ЧИСЛА и ако търсите определена стойност трябва да я преобразувате в дата/час! В случая се налага да преобразуваме текстовата стойност в дата чрез DateValue! Ако датата беше записана като дата в дадена клетка (например e1) формулата щеше да изглежда:

=VLOOKUP(E1,A1:C6,3,FALSE)  без преобразуване. 

Но да се върнем към нашата задача. Функцията Vlookup често се явява нещо като Еверест и когато някой HR чуе че кандидата я знае направо подскача от радост. За мен това е излишна радост:) Vlookup си е много много базова функция:)

Ще дава още един вариант на решение на тази задача:

=INDEX(A1:C6,MATCH(DATEVALUE("1.10.2010"),A1:A6,0),3)

Хммм... Сега ще ме обвините, че е по-сложно! Да но е по-универсално! За това се опитайте да разберете как действа този пример и сте има няма няколко педи (не повече) над "Еверест-а " Vlookup:) В #010 разгледахме Index. Казах тогава че по-подходяща за показване на единична стойност. При нея се задава блок и се посочват координатите (ПОЗИЦИИТЕ!) на реда и на колоната. Колонката както при Vlookup е третата колона. "Далаверата" е, че контролираме по-добре и частта с реда. В случая използваме Match (зеления сегмент). Не забравяйте да укажете третия параметър (НУЛА за точно търсене)! Обърнете внимание че ние задаваме зоната за търсене в Match (A1:A6) и може да няма нищо общо със зоната за извличане в Index!

Пример 2 Да се намери датата която да отговаря числото 50 в третата колонка.

"Майсторите" на Vlookup-a загинаха:) Vlookup има "дефектът" ВИНАГИ да търси в първата колонка на блока. Този дефект е незабележим в голяма част от ситуациите, защото първата колонка най-често е колонката за търсене. Но не винаги :(

Ето решението (ако вече не сте се справили):

=INDEX(A1:C6,MATCH(50,C1:C6,0),1)

Просто е:) Търсим в третата колонка (C1:C6) и връщаме стойност от първата. Елементарно. Просто го запомнете;)

Пример 3 Да се намери стойността на трети ред за колонката съдържаща числото 20 в първия си ред. Тук се извършва хоризонтално търсене.

Този пример не е много подходящ за данните, но искам да ви покажа как да "ритнем" и Hlookup от нашите примери:) Ето "класическо" търсене в първия ред:

=HLOOKUP(20,A1:C6,3,FALSE)

Ето по-гъвкавия вариант:

=INDEX(A1:C6,3,MATCH(20,A1:C1,0)) 

Тук редът от който извличаме е константа, а Match определя колонката!  Нищо сложно:)  Запомнете втория вариант и забравете HLookup:)

Пример 4 Да намерим позицията първата клетка в колонка B която съдържа "А".

Първи опит: =MATCH("A",B1:B6,0)

Независимо, че сме сложи "A" и 0 (точно) търсене. За данните които съм дал функцията връща 2 (!), което е позицията на малкото "a". Както ви обърнах внимание Match (и братовчедите) не разбират от малки и главни букви. За целта ще се наложи малко "гимнастика":)

Втори опит (отговор): {=MATCH(TRUE,EXACT("A",B1:B6),0)} 

Тази формула я въвеждате без {}, но със Shift+Ctrl+Enter! Това е функция за работа с масиви.

Тук използваме функцията EXACT  която връща истина или лъжа ако имаме ПЪЛНО съвпадение между два текста. В случая резултатът от тази функция ще бъде {FALSE, FALSE,TRUE,FALSE,TRUE,FALSE}. 

Остава да намерим първия True с помощта на Match:) Елементарно:) За да проработи не забравяйте Ctrl+Shift+Enter при въвеждането. 

Пример 5. Да намерим позицията на първата клетка която вътре в себе си съдържа "А".

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

Отговор: =MATCH(TRUE,NOT(ISERROR(FIND("A",B1:B6))),0)


Пример 6: Да се намери най-голямата дата за която стойността в колонка C е по-голяма от 20.
Отговор 1:  {=MAX((C1:C6>20)*(A1:A6))}
Въведете с Ctrl+Shift+Enetr! Форматирайте резултатната  клетка като дата!
Тук нещата са леко екзотични:) Извършва се умножение на два масива. Първия е с нули и единици в зависимост от резултата на условието. Втория са датите.
{0,1,1,0,1,1}*{1.1.2010,1.2.2010, 1.10.2010, 1.9.2010, 1.8.2010, 1.7.2010} в резултат се получава 
{0,1.2.2010, 1.10.2010, 0, 1.8.2010, 1.7.2010}. Чрез функцията MAX извличаме най-голямата дата.
Отговор 2: =SUMPRODUCT(MAX((C1:C6>20)*(A1:A6)))
Тук използваме една възможност за "пакетиране" на Max с цел избягване на функцията за работа с масиви. Използваме Sumproduct.Тази функция е "нормална" и няма нужда от въвеждането и с Ctrl+Shift+Eneter!
Hint! Ако искате направо да се форматира като дата може да използвате

=TEXT(SUMPRODUCT(MAX((C1:C6>20)*(A1:A6))),"dd.mm.yyyy")


Пример 7: Да се намери най-малката дата за която стойността в колонка C е по-голяма от 20.

Може по инерция да решите, че всичко ви е ясно и да си помислите, че заменяйки MAX с MIN си решавате проблемът. Да ама не:) Ако обърнете внимание на резултатния масив от по-горния пример ще видите, че най-малката стойност е нула:( което автоматично ви реже възможността да използвате Min върху произведението на двата масива. За целта се налага леко да видоизменим по-горната формула:
Отговор: {=MIN(IF(C1:C6>20,A1:A6))}

Формулата е за работа с масиви (Ctrl+Shift+Enter!)  и няма как да бъде "пакетирана" и "нормализирана! В случая чрез IF-а се създава масив в който има само датите които ни интересуват. Без нулеви стойности!

NB! Много се дискутира до колко формулите за масиви са по-бавни от нормалните. Из доста от формули се водят доста оживени дискусии. Честно казано единствената причина да използвам SumProduct и други трикове за нормализация е, че формулите за работа с масиви се редактират по-трудно (има по-стара тема по въпросът). Не съм установил чак толкова огромна разлика във времената.

Толкова по темата за търсене за сега:) Ако се сетя още нещо или имате някакъв конкретен практически пример и въпроси може да има втора серия:)

#022 Динамични колонки

В този пример ще покажа как да направите динамична колонка и диаграма които зависят от избор на стойности от списък.

Динамична таблица

 При промяна съдържанието на клетките B1 или C1 автоматично се променя заглавието на колонка G, данните в нея и данните изобразявани в диаграмата.

1. Помощни колонки. Цялата магия се състои в добавянето на две допълнителни помощни колонки. Тези колонки може да бъдат на произволно място и спокойно може да ги скрием да не се виждат от потребителя. Тези колонки ще използваме за колонка G и за диаграмата. В примера ще използвам като помощни клетки клетките от H3 до H13 за първата година и клетките от I3 до I13 за втората година.

Помощни колони

• В клетката H3 въвеждаме "=B1" в клетката I3 "=C1". Така си осигуряваме динамичност на заглавията на двете колонки. 

• В клетката H4 въвеждаме :

=INDEX($B$4:$F$13,ROW(A1),MATCH($B$1,$B$3:$F$3,0))

• В клетката I4 въвеждаме:

=INDEX($B$4:$F$13,ROW(A1),MATCH($C$1,$B$3:$F$3,0))

Тук отново се използва "трикът" с променлив брояч вътре във формулата (Row(A1)).Той осигурява извличането на първи, втори, трети и т.н. ред от областта. Match частта осигурява номера на колоната в зависимост от стойността на B1 (C1). Дано вече сте усвоили тези два трика:)

Размножете формулите надолу и имаме две колонки които си променят съдържанието при промяна на B1 и C1!

2. Оформяне на таблицата. Тук трябва да отбележа само няколко неща.

• По потребителски ориентирано е да контролирате въвеждането на данни в B1 и C1. За целта изберете двете клетки. Изпълнете командата Data/Data Validation. И изберете контролиране от списък избирайки зоната B3:F3 в полето Source. Това ще позволи ако се сменят годините да не пипате нищо в областта за контрол.

Контрол на зоната за избор

• В клетката G3 въведете следната формула: 

="Сравнение между " & H3 & " и " & I3
 Така имаме динамично заглавие.

• В клетка G4 въведете формулата:  =H4/I4  . Размножете и оформете като процент.

Останалото го правите както си знаете:)

3. Оформяне на диаграмата. Тук няма нищо специално. Трябва използвате данните от колонки H и I за данни за сериите. Тук няма магия и няма да ви обяснявам как се прави диаграма  за да не ви обидя;) Просто използвайте данните от I и H!! Все пак блога е за трикове...;)

Данни за диаграмата

Е има една "мини магия" за заглавието за да стане и то динамично:) Сложете заглавие. Изберете го.  Щракнете първо върху лентата за формули! След което изберете клетката G3!

Създаване на динамично заглавие на диаграмата

4. Скривате помощните колонки и сте готови:)

#021 Kак да оформим сума на интервали

Това е "бонус" допълнение към #020. Става дума за сумиране на интервали:

Ето таблицата от #020 допълнена със сума:

Суми

В клетка C2 е формулата =(B2-A2+(B2<A2)) клетката е форматирана като Time.  В клетка  E2 формулата е =(B2-A2+(B2<A2))*24 и клетката е Comma style.
Както се вижда, че при сумиране на колонката в която продължителността е Time Excel не позволява сумата да надвиши 24.
Този проблем се решава просто с промяна на формата на клетка C5. Използва се Custom Format [h]:mm.

Custom number format

И всичко е ОК. Появява се сумата 33:30 (33 часа и 30 МИНУТИ). В дясната колонка сумата е 33.50 (33 часа и ПОЛОВИНА). Това повтаряне като мантра от моя страна е да запомните разликите:) Умишлено давам и двата варианта,а  изборът си остава за вас.

:)

#020 Как се изваждат часове

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

Ето примера.

Изваждане на часове

Типично "студентско" примeрче :) Въведени са данни в колонки A и B в клетка C2 е въведена формулата =B2-A2. Дръпната надолу и готовоооо:) Изглежда добре. Но има едни досадни проблемчета които рядко се отбелязват при обучението. 

Какво не му е на ред?! Ако обърнете внимание при въвеждането на формулата и се дръпне надолу клетките сами се оформят от тип Time (час). Тук Excel се прави на умен и крие ревниво своите тайни. Тайната е че данните от тип Time са си дробни числа като един часа е 1/24 два часа е  2/24 и т.н.  На кой му пука ще кажете вие. Но да проверим дали е така.

Да добавим още една колона и да се опитаме да покажем, че тези които са работили по-малко от 2 часа е зле а другите са добре.
Добавяме колона  D и в клетката D2 запишем следната функция: =IF(C2<2,"лошо","добре") (Преподавателя ни е научил на IF):) Да видим какво се получава.

Лошо, Лошо??

Хмм... Тук започваме да стопляме, че нещата не са както трябва. Защо при 18 часа е ЛОШО? Отговорът ви го дадох по-горе. В клетка C3 се намира стойността на "18 часЪТ" а не "18 часа"! Т.е. в колонка C имаме ЧАСОВЕ а не Продължителност!!! Тук ви трябва малко чист въздух и да продължите да четете:)

Оказва се, че не сме получили това което искаме. Какви са вариантите. Единия е да превърнем часа в продължителност. Да добавим още една колонка и в клетката E2 да въведем формулата =C2*24 с цел да преобразуваме час в число.

Нова колона

Ха... Стана още по-зле... ?!?! Какво става тук... Спокойно. Вродената интелигентност на Excеl по някога идва в повече. Проблемът е, че упорито настоява за оформяне от тип Time! Лесно можем да го вкараме в правия път оформяйки колонката като число (Comma style).

Оформяне като число

Всичко е Ок. Май:) Но трябва да внимавате в терминологията. Да огледаме все пак детайлно. За целта ще сравним съдържанието на клетките C2 (1:30) и E2 (1.50). Разделителя  в първия случай е ":" което означава, че се чете като "Един час и тридесет МИНУТИ". Във втория случай разделителя е десетична точка и се чете като "Един час и ПОЛОВИНА"! Което е едно и също, но казано с числа:) Мисля че е ясно, че 15:15 ще бъде представено като 15.25 (четвърт) и т.н.:)

Оказва се, че един интервал може да бъде представен по два начина. Като ЧАС или ПРОДЪЛЖИТЕЛНОСТ (число)! Кой е по-добрия не знам. Изговарят се по различен начин. От вас зависи. Но имайте предвид, че за да превърнете ЧАСА в число трябва да умножите по 24 (не по 100)!! И обратно трябва да разделите на 24 да преобразувате числото в час. Т.е. формулата в d2 може да се запише като:

=IF((C2*24)<2,"лошо","добре")  с корекция на час в число или като 

=IF(C2<(2/24),"лошо","добре") с корекция на число в час....

Ако още не ви боли глава да кажа как се вадят часове когато втория час е по-малък от началния. Например начало:23:00 край:14:00 . В този случай към разликата трябва да се добави единица. (което е 24 ЧАСА!).  Така се извършва корекцията на отрицателното число.

Ето формулата : =IF(B2<A2,B2-A2+1,B2-A2)   Тук Excel няма да усети че става дума за Часове и ще се наложи вие да форматирате клетката като час!

Hint: По-горната формула може да бъде изписана по-хитро без If...

=(B2-A2+(B2<A2)) тук трикът е в това че ако B2<A2 ще се върне истина (Което е 1) и ще се извърши корекцията иначе ще върне False (което е 0) и няма да има корекция... Хитро :)

Запомнете тази формулa:) Но не забравяйте че това връща ЧАС. За ЧИСЛО формулата е :

=(B2-A2+(B2<A2))*24

:) 

#019 Контролни списъци които зависят един от друг (Adv)

Както вече споменах в тази тема ще разгледам по-сложен контрол с помощта на списъци които зависят един от друг. Ако не сте прочели #018 (и свързаните с нея теми) го направете сега. Методът използван в #018 има два недостатъка. 

Първият недостатък в използвания в #018 е във фактът, че ако в първия контролен списък има текст с интервали не е възможно да създадем именувана област съдържаща интервали. Този проблем се решава лесно. Ако в първия списък имаме стойност например "малко дете", създаваме именувана област с име "малкодете" (без интервали!). След което видоизменяме контролиращия списък (в примера #018 клетка B2) като =indirect(substitute(B1," ",""). Просто използваме вече дискутирана функция Substitute за премахване на интервалите. Елементарно;)

Вторият проблем е по-сложен и изисква малко по-сложни действия. Проблемът е, че първия списък е статичен. Потребителя не може да въвежда стойности в него. Когато направим и първия контролен списък динамичен се оказва, че няма как да използваме Indirect по простата причина, че при разработката на приложението ние не знаем какво ще реши да въведе потребителя в първия списък (и респективно да създадем съответната именувана област) :( 

Пример: Да се оценяват студенти в зависимост от различни методи за оценки.

Оценките да зависят от избрания метод (държава)

Оценките на студентите (жълтите клетки) да са съобразени избрания метод за оценяване в клетката B1. Потребителя да може да добавя и редактира начините за оценяване в отделна област.

Да предположим, че областта за видовете оценки започва от клетка F1. (Hint! По-добре е самата област да бъде на друг лист!)

За за въвеждане на видовете оценявания на начало клетка F1

В посочената зона потребителя ще въвежда в първия ред с думи типът на оценяването и вертикално разрешените стойности. Дал съм примери. За повече информация може да надзърнете на адрес http://en.wikipedia.org/wiki/Grade_%28education%29 (Типът оценяване от колонка L няма да го намерите там):):)  Както се вижда различните начини на оценяване имат различен брой разрешени стойности.

1. Създаване на динамична именувана област за видовете оценявания. Тук се използва техниката от #001. За име на областта задайте "GradeType" а за Refers to: =OFFSET(Sheet1!$F$1,0,0,1,COUNTA(Sheet1!$1:$1)-2). Областта е хоризонтална. (NB! това "магическо" оцветено в червено -2 е заради фактът че на първи ред имаме две пълни клетки в повече (А1 и В1)!! Ето за това и препоръчвам зоната да бъде някъде отделно където няма да има такива "смущения"!)

Команда Formulas/Define name

2. Създаване на именувана област за оценяване. Тази област трябва да е динамична вертикално в зависимост от броя на оценките и зависи от стойността на B1.  Задава се име на областта "Grade" и сочи към формулата :

=OFFSET(Sheet1!$F$1,1,MATCH(Sheet1!$B$1,GradeType,0)-1, COUNTA(OFFSET(Sheet1!$F$1,1,MATCH(Sheet1!$B$1,GradeType,0)-1,100,1)))

Не се плашете много:) Вече говорихме за Offset. Сигурно скоро ще има тема и за Match. В случая Match намира колонката на съответния тип оценяване в зависимост от съдържанието на B1.

MATCH(Sheet1!$B$1,GradeType,0)-1 (NB! -1 е защото Offset Брои от НУЛА!).

Часта : COUNTA(OFFSET(Sheet1!$F$1,1,MATCH(Sheet1!$B$1,GradeType,0)-1,100,1)))

намира колко пълни клетки има вертикално.   Тъй като трябва да броим в някаква област предварително задаваме област от 100 (множко е) реда и с CountA намираме реалния брой редове. 

3. Контрол на клетката с видовете оценявания. За клетката B1 се изпълнява Data Validation с контрол по списък =Gradetype след което се избират жълтите клетки и се задава DataValidation по списъка Grade.

Това е:) "Само" три стъпки:)

#018 Контролни списъци които зависят един от друг (easy)

Отново ще дискутираме темата за DataValidation с помощта на списъци. Този път ще се спра на темата как да направим списък който се влияе от друг списък. Няма да е зле отново да прочетете #001, #002 и #004.

Първия пример е доста статичен (скучен бих казал), но в доста голям брой случаи в моята практика използваната  техника върши работа.

Свързани списъци

Да направим списък за контрол, който да се променя в зависимост от избрания пол.

Стъпка 1. Контролираме въвежданите стойности в полето Пол. Понеже стойностите са константни може да използваме Data Validation с твърдо зададени стойности. Избираме B1 активираме Data/Data Validation и въвеждаме стойностите в списъка.

Статични стойности за контрол

Стъпка 2. Създаваме списъците за контрол. Въвеждаме данните за облеклата на мъжа и жената. След което се избират данните за мъжа и се задава име  на областта  "мъж" (точно каквато е стойността за валидиране!). По аналогичен начин се създава и именувана област "жена" с облеклата за жената. NB! Не забравяйте да натискате Enter след като въведете името на избраните клетки!

Област с мъжките дрехи

Област с женските дрехи

Стъпка 3. Валидиране на клетките в зависимост от стойността на пола. Избираме клетките които ще контролираме (в случая само клетка B2). Активираме командата Data Validation. Избора се контрол по списък и в полето Source въвеждаме =Indirect(B1).

Свързан списък

Това е:)  Сега когато се избере пол в клетката B1  видовете дрехи се променят. Този пример има недостък, че зоните "мъж" и "жена" са статични. Т.е. ако добавим нови стойности ще се наложи да правим промяна в името. За да направите примера още по лесен за потребителя, вместо посочения от мен начин за именуване използвайте динамично именуване (описано в #001) при създаване на областта "мъж" и на областта "жена" (разбира се няма да бъде A1 ами D1 (Е1) като начало на зона).  Така потребителя сам лесно може да си дописва дрехи в съответната област и тя сама ще се разширява. Успех:)

Цялата "магия" се крие във функцията Indirect. Тя служи за преобразуване съдържанието на клетката B1 в адрес (в случая именувана зона). Както вече обърнах внимание е важно името на зоните да бъде същото както е името на стойностите в B1! Мисля, че сте се досетили, че ако стойностите в първия списък са повече за всяка от тях трябва да създадете съответната именувана област!

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

#017 Търсене и броене със Substitute

Функцията Substitute има за цел да подменя един текст с друг... Синтаксисът  и е =Substitute( текст в който се подменя, стария текст, новия текст, [коя появя на текстът да се подмени, ако се пропусне се подменят всички намерени текстове] )

Ето два примера:

• =SUBSTITUTE("0000001","0","*")  резултат: ******1
• =SUBSTITUTE("003592300838","00","+",1) Резултат: +3592300838 (замества със знака "+" само първите намерени две нули! (този пример е само за илюстрация и ако работите с мобилни номера ще се наложи по-сложни трикове!)

Функцията обаче се използва и в по-сложни формули по малко по-специален (и често труден за разбиране) начин.

Да се преброи колко пъти даден текст (или знак) го има в друг текст. Ето израза:
=Len(текстът в който търсим)-Len(Substitute(текстът в които търсим, текстът (знакът) който искаме да преброим, "") )
Трикът е, че се премахва търсения текст (подменя се с "") и се намира разликата между дължините на оригиналния и променения текст.

Пример 1: Да се преброят тиретата в даден текст.
Отговор: =LEN("123-345-678-987")-LEN(SUBSTITUTE("123-345-678-987","-",""))

Пример 2: Да се преброят интервалите в дадена клетка
Отговор: =LEN(A7)-LEN(SUBSTITUTE(A7," ","")) (Между първите кавички има интервал, а между вторите няма!)

Второ приложение е да се намери позицията на N-тото появяване на даден текст/знак.
=Find(char(7),Substitute(текст в който търсим, текст който търсим, Char(7), появяването което търсим)

Тук се прилага следния трик. Подменя се този текст/символ с нещо което знаем че го няма в текстът. Например  с Char(7) (камбанка):) Припомням за последния параметър на Substitute който позволява избирателна промяна. След което се намира позицията на този символ със Find.

Ето примери:
Пример 3: Да се намери позицията на ВТОРОТО тире в текст:
Отговор: =FIND(CHAR(7),SUBSTITUTE("123-345-678-987","-",CHAR(7),2))

Пример 4: Да се намери позицията на ТРЕТИЯ интервал в дадена клетка
Отговор: =FIND(CHAR(7),SUBSTITUTE(A7," ",CHAR(7),3))


И като десерт два сложни примера за тези които искат да си размърдат мозъците:)))

Пример 5: Да се намери броят на цифрите в дадена клетка. Не се броят точки и други символи. Само цифрите от нула до девет!
Отговор: =SUMPRODUCT(LEN(A8)-LEN(SUBSTITUTE(A8,ROW(INDIRECT("1:10"))-1,"")))

Тук се прави вътрешен цикъл за да се използва трикът с броенето. Единия вариант е в да се сложи масив {0,1,2,3...9} . Другия вариант е с Row (беше дискутиран вече в по-стари теми). Поради фактът че нулев ред няма (т.е. Row(0) ще даде грешка!) се брои от едно до десет и се вади единица и по този начин се получава броене от нула до девет! Задал съм в червено този "цикъл".

Пример 6. Да се извлече ПОСЛЕДНАТА дума от клетка. Думите са разделени с интервал. Не знаем колко думи има! Думите са повече от една (ако не сме сигурни, трябва да се направи проверка, която в отговора съм спестил!).

Отговор:
=MID(A11,FIND(CHAR(7),SUBSTITUTE(A11," ",CHAR(7), LEN(A11)-LEN(SUBSTITUTE(A11," ",""))))+1,32767)

Тук се използва функцията MID за извличане. За намирането на началната позиция се използва комбинация от двата метода за търсене и броене. Чрез броене се намира броя (т.е. колко интервала има) и след това се намира позицията на последния (=броя) интервал. Вместо да се прави сложна гимнастика за определяне броя на символите за извличане просто се посочва 32767 (текстът в една клетка не може да бъде повече от 32767!) и функцията Mid взема всичко до края. (За лимитите ето ви справка http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx )

Това е:)

#016 Закръгляне или кошмарът на счетоводителя

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

Грешно изградена таблица

Това е типична "студентска" таблица:)  Таблицата е направена по бързия метод... Данните са въведени в колона B. В клетка C1 формулата е =B1/3 а в клетка D1 е =B1-C1. Формулите са дръпнати надолу. В реда "Общо"  е използвана функцията SUM (или просто е натиснат бутона AutoSum) после е натиснат бутона за форматиране в паричен вид (Currency) с валута "лв." и готово. Студентът получава отлична оценка на изпита и всички са доволни:) Само не и човек който може да смята добре.... Лошото е, че тези таблици се създават и на работното място и както казах всеки грамотен човек добре работещ с числа ще каже, че Excel е голям боклук:)

Да проверим: 1666.67 + 3333.33 = 5000 ... Ура тук е вярно! Но.... 5 х 333.33 е 1666.65 (а не 1666.67!) и 5 х 666.67 е 3333.35 (а не 3333.33!)... Голяма работа:) +2 стотинки в едната колона и -2 стотинки в другата:) Кажете го на вашия счетоводител:)

Целия проблем е че както вече споменах Format/Numbers (в случая паричен формат с два знака)  НЕ ЗАКРЪГЛЯ реално съдържанието на клетките ами го закръгля само при показване. Т.е. съдържането на клетките реално е с повече от два знака след десетичната точка (333.3333333.... и 666.66666666666666). Както казах това е само визуална магия... Пуканки за очите:) Изглежда красиво, лесно се прави, но за съжаление е грешно. 

Вариант 1: Първия начин е ние да контролираме закръглянето. За целта в клетката C1 въвеждаме =ROUND(B1/3,2). Дърпаме надолу. И готово (няма нужда от пипане на формулата в D1!). Вече в колонката C1 имаме РЕАЛНО 333.33 и в колонката D реално 666.67 . Без закръгляни или скрити цифри... В резултат на което и сумите отдолу ще бъде 1666.65 и 3333.35! И счетоводителят е доволен:) Този вариант ни дава пълен контрол над данните. За повече информация вижте функциите за закръгляне Round, RoundUp, RoundDown, Ceiling, Trunc и INT. 

Вариант 2: Просто да кажем на Excel да работи с числата ТОЧНО както се виждат. Т.е. "Това което се вижда е точно това което участва в аритметичните операции". В този случай Excel във ваша чест умишлено ще "греши" в зависимост от оформлението:) Визуалната "магия" става реална:) Имайте предвид, че тази настройка важи за ЦЯЛАТА книга!

Ето как става това. 

1. Влиза се в File/Options за Excel 2010  или от офис бутона/Excel Options за Excel 2007.
2. Влиза се в Advanced групата
3. Включва се  Set precision as displayed като преди това се указва за коя книга се отнася! Excel учтиво ще ви предупреди за това че ще се загуби точност. Ако искате да не се губи точност (нормалното състояние) просто изключете настройката.

Настройки на закръгляне спрямо визуализацията на числата

Ми това е:) Имайте предвид тези тънкости когато правите сериозни таблици в които има деление.... Успех:)

#015 Относителни, смесени, абсолютни и още по-абсолютни адреси:)

Тази тема е по-скоро за начинаещи, но ми се иска все пак да обърна внимание. Моят опит със студенти често показва, че се пропускат детайлите, а както знаем дяволът е именно в детайлите.  Става дума за адресите които използваме във формулите. Всеки минал първо ниво на Excel знае, че има два основни вида. Относителни (A1) адреси, които се променят при преместване на формулата и абсолютни ($A$1)  които не се променят. Основните усилие, които се хвърлят от преподавателите е да втълпят на студентите за наличието на тези два вида и разликите между  тях.

Оказва се обаче, че рядко се илюстрира прилагането на междинния тип адреси или така наречените смесени ($A1, A$1) адреси, при които само единия компонент (реда или колоната) е абсолютен (константа) а другия е относителен. Когато проверявам входното ниво на обучаеми по Excel им проверявам знанията с помощта на тази таблица:

Таблица за умножение

Пример: При въведени данни в колонка  A и първия ред (оранжевите области) да се въведе само ЕДНА формула (в клетка B2) която при размножаване да създава таблицата за умножение (жълтите клетки).

Вариант 1 : Формулата в клетка B2: =$A2*B$1. Използват се смесени адреси.  (NB! припомням само за полезния клавиш F4, който променя типът на адресът).  Размножавате надолу и после надясно и готово. За мое съжаление успеваемостта за този пример е нулева. (Не че и аз не водя курсове за начинаещи и това е камък и в моята градинка):) Тази формула е полезно да се знае за да се вникне в детайлите.

Вариант 2: Чрез имена. Изберете клетките от A2 до A11 и в областта за имена  (горе в ляво) им задайте име "redove". (NB! след въвеждане на името натиснете Enter!)

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

 По аналогичен начин задайте име на зоната B1:K1 "koloni". След което формулата в клетка B2 е: =redove*koloni . Чисто и просто (това в случая не значи "къпана блондинка":) Умен Excel:) Красиво е... Но както казах е добре все пак да се знаят типовете адреси. Ще имам отделна тема в която ще покажа различните начини на именуване и използване на имената.

Още малко за абсолютните адреси. Да предположим, че искаме да намерим квадратът на съдържанието на клетката A1. Вариантът на формулата е =$A$1^2 (^ е степенуване). Тази формула може да се мести без да се променя адресът на клетката. НО. Абсолютните адреси имат един "дефект" (зависи от гледната точка и конкретния пример). Въпреки че са абсолютни при вмъкване и изтриване на редове или колони (чрез командите Insert или  Delete) те се променят. Може да си направите експеримент да вмъкнете колонка на мястото на колонка A. В този случай формулата ще се видоизмени на =$B$1^2.  Ако изтриете първи ред ще се получи "гадното" съобщение #Ref във формулата, защото редът липсва.

Оказва се, че абсолютния адрес не е на 100% абсолютен!  Запомнете това! Ако искаме наистина абсолютен адрес се използва функцията Indirect("адрес или зона"). Примерът се видоизменя като =Indirect("A1")^2. Тази формула ВИНАГИ ще връща квадратът на съдържанието на клетка A1!

#014 Колко дена има в даден месец

В Analysis ToolPak add-in-са има функция ЕОMONTH, но съм виждал прекалено много инсталации с не инсталирани (не активирани)  add-in модули за това ще дам един по-универсален пример. =Day(Date(Година,Месец+1,0)). Ако искаме самата дата а не броя на дните просто се маха заграждащия Day. Годината е необходима за месец февруари. В останалите случай може просто да се сложи произволна година. Трикът е че взема нулевия ден на СЛЕДВАЩИЯ месец:)

Пример 1: Да се намери броя на дните (брой не дата!) в текущия месец.
Отговор : =DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0))

 Пример 2: Да се намери последната ден в месеца (като дата) за дата в клетката A1
Отговор: =DATE(YEAR(A1),MONTH(A1)+1,0)

Пример 3: Колко дена има в 2008 година:)
Отговор: =SUMPRODUCT(DAY(DATE(2008,ROW(INDIRECT("1:12"))+1,0)))
Това е мноооого ма много триково решение с цел да ви припомня как се прави вътрешен цикъл във формула:))) И не ви съветвам да се увличате по тях.
Иначе ... =DATE(2008,12,31)-DATE(2008,1,1)+1 :) Явно съм пропуснал да кажа, че датите могат да се вадят като числа. И да припомня едно правило от първи клас. Брой=Крайната_стойност-началната_стойност+1!! :):) Може и =DATEDIF("1.1.2008","31.12.2008","d")+1 :)

Дано вече усещате мощността на Excel (а още не съм започнал с макросите):)

Enjoy:)

#013 Петък 13 или локализация на датите:)

Когато писах предишния пример за датите се сетих за петък 13-ти,който беше скоро, и за един много познат проблем. Как да се изобрази с думи деня от седмицата. Разбира се всеки петокласник би трябвало да се сети за функцията Text :) =Text(дата, "dddd"). По-сложно става ако искаме ВИНАГИ текстът да е български (или друг конкретен език). Посочения пример има един дефект, че се влияе от текущата локализация на Excel.

Като лирично отклонение с цел да прочетете и за други функции може да предложа едно сложно решение.... =CHOOSE(WEEKDAY(A1,2),"понеделник","вторник", "сряда", "четвъртък", "петък", "събота","неделя")

Всъщност този пример го давам на студентите когато обяснявам Choose и Weekday... Но да си призная си е и за запълване на повече време в часа ми докато студентите търсят проклинайки ме  (мен и Кирил и Методий) буквите по клавиатурата:)) 

Но да се върнем към нашия проблем с локализацията. Всъщност пред форматиращия стринг може да се постави указател за локализация. Например =TEXT(NOW(),"[$- локален_код]dddd"). Трикът е в числото в скобите. Списък на стойностите може да вземете от адрес http://msdn.microsoft.com/en-us/goglobal/bb964664.aspx. Стойността вземате от втората колонка в посочената таблица.

Ми това е... Да проверим: =TEXT("13/8/2010","[$-402]dddd").  Може и на македонски ;) : =TEXT("13/8/2010","[$- 042f]dddd")

Enjoy :)

#012 Функция DateDif

Таман написах, че няма да давам обяснения за параметрите на функция и се сетих за "култовата" функция DateDif :) Култова е по-простата причина, че официално я няма нито в списъка от функции нито в помощната информация...Но си работи:) Дали някой юнак от MS се е изфукал пред гаджето си или някой садист само чака да я махне от следващите версии на Excel и да вдига невинно рамена на ядосаните потребители дето са я използвали не знам;) Или просто някой пиян програмист;) Но си е факт, че тази функция от доста отдавна си я има:

= DATEDIF(начална дата, крайна дата, вид на интервала)

 NB! Началната дата ТРЯБВА да е по-малка от крайната в противен случай функцията връща грешка!

Мерна единица	Описание
Y	Разлика в цели години
M	Разлика в цели месеци
D	Брой дни
MD	Разлика в дни игнорирайки влиянието на месеца и годината (все едно са един и същи месец и година)
YD	Разлика в дни игнорирайки влиянието на година (все едно са е една и съща година)
YM	Разлика в месеци игнорирайки влиянието на годината (все едно са в една и съща година)

Примери

Ето един често използван пример (разбира се има нужда да го пипнете да не пише 1 "дена", но това си остава като за домашно):)
="Вие сте на " & DATEDIF($A$1,TODAY(),"y") & " години "  &  DATEDIF($A$1,TODAY(),"ym")  & " месеца и " & DATEDIF($A$1,TODAY(),"md") & " дена"

в А1 е датата ви на раждане.... И се ужасете колко сте стари (поне аз де):))

NB! Отново обръщам внимание на особения статут на тази функция!

#011 Най-дългия текст в група клетки

Ето един пример в който няма как да се разминем с формула за масив.

Примерен текст

Задача 1: Да се намери максималната дължина на текстът в група клетки.
Отговор: =MAX(LEN(A1:A5)). Чудесно! Оказва се, че функцията MAX си работи перфектно с върнатия от Len масив (за разлика от SUM) :)

Задача 2:  Да се намери броя на клетките които имат дължина колкото максималната.
Отговор: =SUMPRODUCT(--(LEN(A1:A5)=MAX(LEN(A1:A5)))). Трикчето с двата минуса и SumProduct вместо Sum работи.

Задача 3: Да се изкара стойността на първата клетка имаща дължина равна на максималната.

Отговор: {=INDEX(A1:A5,MATCH(MAX(LEN(A1:A5)),LEN(A1:A5),0))} Както се вижда това е функция за масив и се въвежда чрез Ctrl+Shift+Enter (разбира се без {}!). Проблем се оказва функцията Match. В случая използваме тази функция за намиране на първата стойност в масива дължини MATCH(MAX(LEN(A1:A5)),LEN(A1:A5),0) . По принцип комбинацията Index/Match  е доста срещана и в някой от следващите типове ще дам повече обяснения.

#010 Функция Offset и функция Index

Сега сравних #004 и #009 и установих, че съм използвал различен подход при пакетирането на стойности. В единия случай съм използвал Offset, а в другия Index. Да кажем че е с учебна цел:) Ако сте забелязали до сега не коментирам синаксисът на дадена функция. Има си хелпове и Google:) Но все пак да обърна внимание на някоки важни различия между Index и Offset:

• Функцията индекс връща единична стойност базирана на координатите на ред и колона (колоната не е задължителна) в зона от клетки. Докато функцията Offset връща област или клетка. Указател по-скоро. Именно за това в някой примери (не винаги) се налага да се използва функцията N за да се превърне указателя в числова стойност.  Както се вижда Index е по-подходяща в случаите когато се изисква връщане на единична стойност, но Offset е много по-мощна. Може да се каже, че съм използвал излишна функцията Offset в #004 при колонката за "пакетиране" на резултата и по-правилното е използването на Index в #009.
• Друго важно различие е, че индекс използва точни координати докато Offset използва отместване като началната точка е с отместване 0,0! За да стане по-ясно запомнете че "Index брои от единица, Offset от нула":):)

Ето един пример:

Данни

Задача: Имаме зона от клетки. Да се извлече втората клетка от зоната. (В случая стойността "BBB").
Отговор:

• С функция Index: =INDEX(A1:A4,2,1) . Втори ред първа колона. Човешки:) NB! В случая може да се пропусне номера на колоната. =INDEX(A1:A4,2)
• С функция Offset: =OFFSET(A1:A4,1,0,1,1). ОТМЕСТВАНЕ(!)  ЕДИН ред надолу и НУЛА колонки в дясно спрямо A1. Височина на резултатния блок 1 (тук не е отместване ами БРОЙ редове!), широчина 1 (тук не е отместване ами БРОЙ колони!). Доста по-различно от Index, но даващо същия резултат. Може да опитате.

Сега може отново да видите #004 и да се опитате да замените Offset (става въпрос за колонка К) и да се опитате да Fix-нете заменяйки Offset с Index:) Но и да не успеете нали си работи;)