У меня есть столбец данных, который представляет продолжительность, например, 33:15 - 30 минут и 15 секунд; 1:05:00 - 1 час 5 минут и т.д.
Если я попытаюсь отсортировать его по AZ, то за 30 минут сортируется за 1 час.
Есть ли способ отформатировать данные для правильной сортировки? Решение для форматирования предпочтительнее, чем преобразовывать эти данные в секунды или еще много чего.
Если ваши значения интерпретируются Excel как фактическое время, то они сортируются по номерам, а не по алфавиту. Но есть проблемы.
Если вы введете минуты и секунды как 30:00, Excel будет интерпретировать как 30 часов и 0 минут. Вы должны ввести 30 минут как 0:30:00 или 0:30. Таким образом, 30 минут, введенные таким образом, будут интерпретироваться как более 1 часа 30 минут, введенные как 1:30. Вы должны всегда проверять строку формулы после ввода чисел, чтобы убедиться, что Excel записал значение, которое вы намеревались ввести.
CompWiz пропустил эту сложность, и рутины Крейга как раз подходят для ваших конкретных проблем. Но, зная, что вы должны ввести время в формате h:mm:ss, даже если значение меньше часа, вы будете всегда правильно интерпретировать свои значения.
Добро пожаловать в удивительный мир времен в Excel. Поначалу удивительно, но мощно, когда вы знаете, как они работают.
Я не думаю, что есть способ сделать это с помощью простого форматирования в вашей ситуации. Тем не менее, это должно работать (я предполагаю, что все ваши времена в столбце A)
-Create this formula in B1 and copy it all the way down:
=IF(A1>=1,A1/60,A1)
-Format Column B as h:mm:ss
-Select Column B and Copy, then Paste Special, Values.
-Sorting Column B should now work fine.
Это короткий ответ. Если вы хотите понять, что происходит и как была получена эта формула, читайте дальше:
1.
-Start a new sheet.
-In A1, type 1:05:00
-Click on A1, then Format, Cells. Note it has applied a custom format of h:mm:ss
Excel довольно умен, и это число довольно однозначно, поэтому предполагается, что вы подразумевали часы: минуты: секунды и соответственно форматирует.
2.
-In A2, type 33:15
-Note how it automagically changed it to 33:15:00
-Click on A2, then Format, Cells. Note a custom format of [h]:mm:ss
Это неоднозначно. Вы имели в виду "33 минуты и 15 секунд" или "33 часа и 15 минут"? Excel не уверен. Его поведение предполагает, что вы имели в виду часы и минуты. [] Вокруг h в основном означает "показывать более 24 часов в разделе часов".
3.
-In A3, type 0:33:15 (note the 0: before)
-Click on A3, then Format, Cells. Note a custom format of h:mm:ss
Поскольку вы устранили неоднозначность, это еще раз предполагает, что вы подразумеваете часы: минуты: секунды снова и форматирует соответственно.
4.
-In A4, type 23:15
-Note how it leaves it as 23:15
-Click on A4, then Format, Cells. Note a custom format of h:mm
W ..T ..F? Как получилось, что он отформатирован иначе, чем в # 2? Поскольку вы ввели число менее 24 (то есть часов) - все еще неоднозначно, и все равно предполагается, что вы имели в виду часы и минуты ... но оно форматируется по-другому.
5.
-In A5, type 1:00:00
-Click on A5, then Format, Cells. Note a custom format of h:mm:ss
-Change the format to General and note that the underlying number is .041667 (i.e. the percentage of a day)
6.
-In A6, type 24:00:00
-Click on A6, then Format, Cells. Note a custom format of [h]:mm:ss
-Change the format to General and note that the underlying number is 1 (i.e. a full day)
Почти готово...
7.
-Now click on B2 and enter this formula:
=A2/60 (i.e. convert from hours to minutes)
-Click on B2, then Format, Cells. Note a custom format of [h]:mm:ss
-Note that it now shows 0:33:15, which is what you want
8.
-Now click on B1 and enter the same formula:
=A1/60 (i.e. convert from hours to minutes)
-Click on B1, then Format, Cells. Note a custom format of h:mm:ss
-Note that it shows 0:01:05 - damn - that's *not* what you want.
Оставайтесь на цели ...
9.
-Click on B1 again and enter this formula instead:
=IF(A1>=1,A1/60,A1)
-Click on B1, then Format, Cells. Enter a custom format of h:mm:ss
-Note that it still shows 1:05:00 (i.e. it didn't change it to 0:01:05)
Так что в основном эта формула:
-Checks to see if the number in a cell is greater than or equal to 1
-If it is greater than 1, divide by 60 (i.e. convert hours to minutes)
-If it's less than 1, leave it alone.
Короче ... времена ... как в количестве часов / минут / секунд фактически сортируются как символьные данные. 1 меньше 30, а 1 ч меньше 30 м, потому что 1 меньше 3. Вам нужно хранить данные в виде всех секунд ... то есть 30 минут вводятся как 1800, а 1 час - как 3600 и использовать некоторое сложное форматирование ячеек, чтобы отображать их как часы / минуты / секунды ... или вводить данные как 00:30:00 и 01:00:00 соответственно.
первый - правильный метод, но очень сложный для реализации ... второй - намного проще и всегда будет работать, потому что 00: предшествует 01: каждый раз.