Трябва да създам таблица в Excel, която изчислява ежедневните часове за обучение. всеки ред има следните полета: Дата, Разстояние, Час и Минути/км.
Основният ми проблем е, че искам да покажа времето във формат mm:ss
. Например: 24 минути и 3 секунди трябва да са 24:03
, но "Excel" го превръща в 00:03:00 (3 минути след полунощ). Опитах да настроя „специално форматиране“, но все още „Excel настоява“ за форматиране на часовете. Нещо повече, обучението може да надхвърли 60 минути и аз все още го искам само като mm:ss
Вторият ми въпрос е след като приключих с проблема с форматирането, какъв е лесният начин за изчисляване на време/разстояние?
Excel 2007: Как да покажа формат mm:ss не като DateTime (напр. 73:07)?
Отговори (7)
въведете стойностите като 0:mm:ss
и форматирайте като [m]:ss
тъй като сега това е в минути и секунди, простата аритметика ще ви позволи да изчислите статистиката си
0:74:03
, и то го изведе според очакванията - 74:03
- person SeanC; 21.06.2012
Excel показва 24:03 като 3 минути, когато го форматирате като време, защото 24:03 е същото като 00:03 (във военно време).
Използвайте общ формат, за да добавите времена
Вместо да се опитвате да форматирате като време, използвайте Общия формат и следната формула:
=number of minutes + (number of seconds / 60)
Пример: за 24 минути и 3 секунди:
=24+3/60
Това ще ви даде стойност 24,05.
Правете това за всеки период от време. Да приемем, че въведете тази формула в клетки A1
и A2
. След това, за да получите общата сума на изминалото време, използвайте тази формула в клетка A3
:
=INT(A1+A2)+MOD(A1+A2,1)
Преобразувайте обратно в минути и секунди
Ако поставите =24+3/60
във всяка клетка, ще имате стойност 48.1 в клетка A3
.
Сега трябва да преобразувате това обратно в минути и секунди. Използвайте следната формула в клетка A4
:
=MOD(A3,1)*60
Това взема десетичната част и я умножава по 60. Не забравяйте, че в началото разделихме на 60, така че за да го преобразуваме обратно в секунди, трябва да умножим.
Бихте могли да направите това и отделно, т.е. в клетка A3 използвайте тази формула:
=INT(A1+A2)
и тази формула в клетка A4
:
=MOD(A1+A2,1)*60
Ето екранна снимка, показваща окончателните формули:
mm+s/60
, където mm
е броят на минутите, а s
е броят на секундите. Ако въведете 24:03
и използвате Left
върху него, получавате 1
, а не 24
.
- person JimmyPena; 20.06.2012
За да се улесни живота при въвеждане на множество дати/часове, е възможно да се използва персонализиран формат, за да се премахне необходимостта от въвеждане на двоеточието и водещия "час" 0. Това обаче изисква второ поле за цифровата дата, която да бъде съхранена, т.к. показаната дата от персонализирания формат е в база 10.
Показване на число като време (няма нужда да въвеждате двоеточия, но няма преобразуване на време)
За показване на времената на листа и за въвеждането им, без да се налага да въвеждате двоеточието, задайте формат на клетката по избор и използвайте:
0/:00
След това въведете вашето време. Например, ако искате да въведете 62:30, тогава просто ще въведете 6230 и вашият персонализиран формат визуално ще вмъкне двоеточие 2 десетични точки отдясно.
Ако трябва да покажете само времената, спрете до тук.
Преобразуване на число във време
Ако трябва да можете да изчислявате с времената, ще трябва да ги конвертирате от база 10 във формат за време.
Това може да се направи със следната формула (променете A2
към съответната препратка към клетка):
=TIME(0,TRUNC(A2/100),MOD(A2,100))
=TIME
започва преобразуването на число във време- Нямаме нужда от часове, така че въведете
0,
в началото на формулата, тъй като форматът винаги еhh,mm,ss
(за да показвате часове и минути вместо минути и секунди, поставете 0 в края на формулата). - За минутите
TRUNC(A2/100),
изхвърля най-десните 2 цифри. - За секундите
MOD(A2,100)
запазва най-десните 2 цифри и изхвърля всичко вляво.
Горната формула е намерена и адаптирана от тази статия: PC Mag.com - Лесно въвеждане на дата и час в Excel
Като алтернатива можете да пропуснете персонализираното форматиране 0/:00
и просто да въведете времето си в клетка, към която да се позовавате на ръба на видимото работно пространство, или на друг лист, както бихте направили за персонализираното форматиране (т.е.: 6230 за 62:30)
След това променете формата на показване на клетките с формулата на [m]:ss
, както предложи @Sean Chessire.
Ето екранна снимка, за да покажа какво имам предвид.
Ако използвате ръчно въведени данни, можете да въведете данните си като mm:ss,0
или mm:ss.0
в зависимост от избора на език/регион вместо 00:mm:ss
.
Трябва да посочите формата на клетката си като [m]:ss
, ако искате да виждате всички минути и секунди във формат вместо часове, минути и секунди във формат.
като текст:
=CONCATENATE(TEXT(cell;"d");" days ";TEXT(cell;"t");" hours ";MID(TEXT(cell;"hh:mm:ss");4;2);" minutes ";TEXT(cell;"s");" seconds")
5. В полето Format Cells щракнете върху Custom в списъка Category. 6.В полето Тип в горната част на списъка с формати въведете [h]:mm;@ и след това щракнете върху OK. (Това е двоеточие след [h] и точка и запетая след mm.) След това можете да добавите часове. Форматът ще бъде в списъка Тип следващия път, когато имате нужда от него.
От MS, работи добре.
http://office.microsoft.com/en-us/excel-help/add-or-subtract-time-HA102809662.aspx
Един удобен трик за въвеждане на изминали времена в Excel е да имате две нули и двоеточие преди броя на минутите, подробностите следват. За операции за копиране и поставяне в Excel, без изобщо да се притеснявате за форматирането, можете да използвате формата 00:XX:XX, където XX са две цифри, общо ‹ 60. В този случай Excel ще отрази 0:XX:XX в съдържанието на клетката показва и съхранява данните като 12:XX:XX AM. Ако поставите данни във формат 00:XXX:XX в Excel или 00:XX:XX, където или XX > 59, това ще бъде преобразувано в част от деня.
Например 00:121:12 става 0,0841666666666667, което, ако се умножи по броя на секундите в деня, 86 400, става 7272 s. След това 00:21:12 по подразбиране ще покаже 0:21:12, съхранено като 00:21:12. Накрая 00:21:60 става 0,0152777777777778, също част от деня.
Това предложение е направено само за да се избегне необходимостта да се притеснявате за конкретно форматиране в Excel и да оставите програмата да се тревожи за това. Забележете, че за данни на Excel, вътрешно форматирани като 12:XX:XX AM, можете да използвате само определени команди на Excel, например можете да вземете средна стойност. Изваждането обаче ще работи само когато резултатът е положително число. Така че преобразуването на времето в секунди, части от деня или друго реално число се предлага за достъп до по-пълно покритие на аритметични операции.
Например, ако има колона със смесени часови формати или часове, които са отрицателни и няма да се показват, ако промените числовото форматиране на Общо, всички времена ще бъдат преобразувани във части от деня.