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

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

Успех:)

#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

:)