☰

Как сортировать по чч:мм:сс (длительность) в Excel

tag-icon • tag-icon microsoft-excel sorting
Как сортировать по чч:мм:сс (длительность) в Excel

У меня есть столбец данных, представляющий длительность, например, 33:15 — 30 минут и 15 секунд; 1:05:00 — 1 час и 5 минут и т. д.

Если я попытаюсь отсортировать его по алфавиту, то 1 час будет отсортирован раньше 30 минут.

Есть ли способ отформатировать данные, чтобы они сортировались правильно? Форматирование предпочтительнее, чем преобразование этих данных в секунды или что-то в этом роде.

решение1

Добро пожаловать в чудесный мир времени в 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.

решение2

Если ваши значения интерпретируются Excel как фактическое время, то они сортируются по числам, а не по алфавиту. Но есть проблемы.

Если вы введете минуты и секунды как 30:00, Excel интерпретирует это как 30 часов и 0 минут. Вам придется ввести 30 минут как 0:30:00 или 0:30. Таким образом, 30 минут, введенные таким образом, будут интерпретироваться как больше, чем один час тридцать минут, введенные как 1:30. Вам всегда следует проверять строку формул после ввода чисел, чтобы убедиться, что Excel записал значение, которое вы намеревались ввести.

CompWiz пропустил эту сложность, и процедуры Крейга просто корректируют ваши конкретные проблемы. Но знание того, что вам нужно вводить время как ч:мм:сс, даже если значение меньше часа, будет означать, что ваши значения всегда будут интерпретироваться правильно.

решение3

Короче говоря... время... как количество часов/минут/секунд на самом деле сортируется как символьные данные. 1 меньше 30, а 1h меньше 30m, потому что 1 меньше 3. Вам нужно хранить ваши данные как все секунды... т. е. 30 минут вводятся как 1800, а 1 час как 3600, и использовать сложное форматирование ячеек для их отображения в виде часов/минут/секунд...иливведите данные как 00:30:00 и 01:00:00 соответственно.

первый - этоправильныйметод, но очень сложный в реализации... второй гораздо проще и всегда будет работать, потому что 00: всегда предшествует 01:.

Связанный контент