вторник, 14 август 2012 г.

#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 часа, то този вариант е неприложим!!

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

Успех:)








петък, 10 август 2012 г.

#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. И едно предизвикателство :):) Да се тествате сами себе си:) Опитайте се сами да направите сортировка по два ключа (две колонки). Например в сегашния вариант ако имаме две еднакви имена по-напред се слага името което е било по-нагоре в изходните данни. Помислете как да се подрежда по-нагоре името с по-голяма заплата! В примера имаме две еднакви имена Мария Петрова и в нашия случа се подреждат "грешно"!

четвъртък, 9 август 2012 г.

#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 (в следваща тема):)