#052 Минути в часове или как да правим разлика между дробна част и остатък

Една мини тема породена от въпрос във блога.... "Как да се преобразуват минути в час и минути"?!? Темата е много близка до #020 Как се изваждат часове и #021 Kак да оформим сума на интервали , затова моля отново ги разгледайте!


Условие: Имаме сума на минути и искаме да ги преобразуваме в час и минути:

Варианти за преобразуване на минути в часове

Вариант 1
Всеки първокласник знае че един час се състои от 60 минути и спокойно решаваме да използваме деление и пишем формула =B7/60 (формулата в клетка B8).
Тук сме на 50% прави (може и 100%, но с известни уговорки) :)

Що се отнася до часовете сме получили вярно, но виж с дробната част имаме проблем! От една страна 90 минути са  час и 30 МИНУТИ, но от друга страна си е час и ПОЛОВИНА (0,5!).  Т.е. в нашия случай дробната част е във вид на ОТНОСИТЕЛЕН ДЯЛ  (т.е. 59-те минути възможен остатък се превръщат в проценти спрямо 60!). 15 минути са 25% (1/4), 30 секунди са 50% (1/2) и т.н. Именно поради тази причина е важно как ще бъде прочетен резултата. Абсолютно грешно е да се чете като час и 50 МИНУТИ (както беше казано правилното е да се чете като час и ПОЛОВИНА). По същия начин 1,25 трябва да се чете не Час и 25 МИНУТИ ами Час и ЧЕТВЪРТ! Та както казах резултата е верен, но с известни уговорки!

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

Вариант 2

Как да върнем секундите като секунди а не като десетична част?! Просто е... Знания от първи клас!:) На нас ни трябва ОСТАТЪКА, а не дробната част! Имам наблюдения от студентите си, че голяма част просто не правят разлика  между двете понятия! Минутите са ОСТАТЪК от делението на 60!  В Excel това става чрез функцията MOD!

=mod(B7;60) .

Трябва  да "сглобим" чрез оператора за конкатенация (слепване) на текст (&) часовете (ЦЯЛАТА част на делението на 60) , знакът ":" и ОСТАТЪКА (не дробната част!) от делението на 60! Използваме функцията TEXT за оформление на резултата.

Формулата в B9 е:

=TEXT(FLOOR(B7/60;1);"00") & ":" & TEXT(MOD(B7;60);"00")

NB! Резултатът е ТЕКСТ! Това означава, че може да го обработване с текстови функции, а не директно с функциите за дата и час!

NB2!  Вместо FLOOR(x;1) може да се използва и INT(x)!

  

Вариант 3

Не случайно ви накарах да прочетете #20 и #21 в началото (ако не сте ги прочели още, моля отделете им време!). Следващите два варианта са базиране на работата с типа време!

Правилото което трябва да запомните е: "За преобразуване от число във време ДЕЛИМ на 24 (не на 60 или на 100), а на време в число УМНОЖАВАМЕ по 24!".

Формулата в B10 е:

 =(B7/60)/24

Първото деление да се превърнат минутите в дробно число (час/минути), а второто деление е за преобразуването в тип Date/Time! Груба грешка щеше да е директно минутите да се делят само на 24!

NB! Сложим съм скобите за по-голяма прегледност. Може и без тях!

NB! За оформление използвайте потребителски формат (Custom Format). [hh]:mm! За повече информация вижте #21

Вариант 4

Тук използваме функцията Time чрез която се "сглобява" времето от час/минути/секунди!

Формулата в B11 е:

=TIME(0;B7;0)

NB! За оформление използвайте потребителски формат (Custom Format). [hh]:mm! За повече информация вижте #21

NB! Функцията генерира време само в рамките на 24 часа, независимо от използвания за оформлението формат! Ако във вашият вариант се получават сума на минутите по-голяма от 24 часа, то този вариант е неприложим!!

Умната с десетичния знак и с остатъка от делението! Различни неща са!:):)

Успех:)

#051 Сортировка с формули - втора част

NB! Преди да продължите, моля прочете първата част от темата #050 Сортировка с формули - първа част

II. Сортиране на данните

В тази част ще извършим сортирането на данните... Ето изходните данни върху които ще илюстрираме.

1. Създаване на помощни колони

Колонките C и D са помощни колонки (ПК звучи почти като "пожарен кран":) Разбира се може да ги сложите където си искате в работния лист и да ги скриете (и сложите по-умни имена)! Използвани са знанията от първата част на темата!

Формулата в C2 е:
=COUNTIF($A$2:$A$8;"<="&A2)-COUNTIF(A2:$A$8;"="&A2)+1
Формулата в D2 е:
=COUNTIF($B$2:$B$8;">="&B2)-COUNTIF(B2:$B$8;"="&B2)+1

Умишлено съм обърнал знака във втората формула да е ">="! Така получавам ранговете на заплатите в "обратен" ред (от голяма към малка заплата!). Ако искате заплатите и да са във възходящ ред просто изменете знака във формулата (и не забравяйте да я размножите надолу!)

В резултат на тези формули в двете помощни колонки получаваме ранговете ("нечестни") за имената и заплатите.

2. Създаване на имена

За по-лесното изграждане на формулите съм създавал имена на области (така и не направих тема за именуването:() :

=$A$2:$A$8 - Имена
=$B$2:$B$8 - Заплати
=$C$2:$C$8 - ПК1
=$D$2:$D$8 - ПК2

NB! В общи линии не умна идея да пишете имената кирилица! За да стане още по-гъвкава таблицата може да използвате динамични имена (има тема!) които да се разширяват при добавяне на данни!

3. Сортировка

След тези подготовки самата сортировка е фасулска работа, ако си спомняте темата за Index/Match;))!

Формулата в А2 е:
=INDEX(Имена;MATCH(ROW(A1);ПК1;0))
Формулата в B2 е:
=INDEX(Заплати;MATCH(ROW(A1);ПК1;0))

NB! Обърнете внимание, че и в двете формули използваме помощна колонка ЕДНО!! Сортировката е според ПЪРВАТА колонка (в случая имената) и за това заплатите трябва да съответстват на имената!

Идеята е просто да търсим 1,2,3,.... в помощната колонка и да извличаме съответното име/заплата. "Броенето" се реализира чрез Row(..). Ако имате колонка в която да има 1,2,3 (примерно номерация) можете да използвате нейното съдържание.

Ако искаме да сортираме по заплати....

Формулата в А2 е:
=INDEX(Имена;MATCH(ROW(A1);ПК2;0))
Формулата в B2 е:
=INDEX(Заплати;MATCH(ROW(A1);ПК2;0))

NB! И на двете места използваме ВТОРА помощна колонка!

Ми това е:) Успех:):)

P.S. И едно предизвикателство :):) Да се тествате сами себе си:) Опитайте се сами да направите сортировка по два ключа (две колонки). Например в сегашния вариант ако имаме две еднакви имена по-напред се слага името което е било по-нагоре в изходните данни. Помислете как да се подрежда по-нагоре името с по-голяма заплата! В примера имаме две еднакви имена Мария Петрова и в нашия случа се подреждат "грешно"!

#050 Сортировка с формули - първа част

Темата е как без помощта на стандартните възможности Excel да се сортират данните от дадена област... Сортирането ще бъде извършено на ново място! Задачата се решава в две стъпки:
- номериране (в помощна колонка) данните според техния ранг
- копиране на данните на ново място според техния ранг
Както беше споменато това ще бъде извършено без използването на макроси или използването на стандартното сортиране!

В този тема ще обсъдим номерирането (и покрай него как се намира ранг (честен и нечестен):):)

I. Номериране на данните според техния ранг

1. Използване на функцията Ranк

Използването на израза "ранг" веднага може да ни наведе на идеята да използваме функцията =Rank.... В клетка B2 записваме формулата: =RANK(A2;$A$2:$A$7;1)

На първо четене това ни върши работа, но има две големи НО-та!:))

- какво да правим с дублажите
- какво да правим ако данните са текстови
Решаваме да поровим в помощната информация и откриваме, че има две разновидности на Rank! Rank.EQ и Rank.Avg. Да видим дали няма да ни свършат работа!

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

Като добавим, че функцията Rank(.(Eq|Adv)) не работи с текст я бракуваме! :) NB! Функцията е доста полезна в други ситуации за това не я забравяйте!!

2. Използване на условно броене!

Ще започнем от по-лесната за разбиране формула: =COUNTIF($A$2:$A$9;"<="&A2)

Тук няма голям трик (важно е да се сетите че ранга е всъщност броене!;) ... Броим колко стойности има по-малки и равни на дадената стойност! Просто се използва оператора & за долепяне на "<=" към стойността в клетката A2! Тази формула работи и при текстови данни!!

До тук добре... Решихме проблемите с текстовите данни, но остана "НО-то" с дублажите!

Да помислим какъв "ъпгрейд" трябва да направим на горната формула. От броенето трябва да изключим всички стойности равни на дадената стойност в СЛЕДВАЩИТЕ клетки!! Просто нали?:) (Тук е момента да осмислите предишната формула още веднъж!:)

Ето новата формула:
=COUNTIF($A$2:$A$7;"<="&A2)-COUNTIF(A3:$A$8;"="&A2)

Тук става малкоо по-сложно (не не много):) Трикът е, че половината от зоната A3:$A$B е относителна (променяща се) а втората е константа! Т.е. при влачене формулата ще взима "следващите" клетки! Има още един "крив" момент! Не случайно втората зона е малкоо по-дълга от зоната с данни (стига до A8 а не до A7!). Това се налага поради факта, че ако беше сложено само A7 втората част на формулата щеше да изглежда при достигане на края на зоната с данни (B7) по следния начин: COUNTIF(A8:$A$7;"="&A7)!! И CountIF се побърква:( За това и се наложи това "криво" решение....

Всъщност може и без него:):) Ха сега:) Ако искате се опитайте да го откриете сами...! Трябват знания по математика от първи клас и логика от трети;):):) Отново се опитайте да разберете какво сме правили до сега!

Та...

Формулата е ...

=COUNTIF($A$2:$A$7;"<="&A2)-COUNTIF(A2:$A$7;"="&A2)+1!

Просто броим (вадим) и стойността от текущия ред и след което добавяме единица! Така се избягва "кривата" формула с две различни по дължина зони. Единствено остава трикът с "полу" абсолютната зона във втората част на формулата!

Дава същите резултати ....

... и работи и с текст!

Супер!:) Постигнахме "нечестно" ранжиране:) Т.е. последователно номериране по принципа при равен ранг да се взема по-предното местоположение в таблицата.

To be continue (в следваща тема):)

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


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

Успех

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

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