![如何在 Excel 中按 hh:mm:ss(持续时间)排序](https://linux22.com/image/1284600/%E5%A6%82%E4%BD%95%E5%9C%A8%20Excel%20%E4%B8%AD%E6%8C%89%20hh%3Amm%3Ass%EF%BC%88%E6%8C%81%E7%BB%AD%E6%97%B6%E9%97%B4%EF%BC%89%E6%8E%92%E5%BA%8F.png)
我有一列表示持续时间的数据,例如 33:15 - 30 分钟 15 秒;1:05:00 - 1 小时 5 分钟,等等。
如果我尝试按 AZ 排序,那么 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 忽略了这种复杂性,而 Craig 的例程只是针对您的具体问题进行了纠正。但要知道,即使值小于一小时,您也必须以 h:mm:ss 的形式输入时间,这意味着您的值将始终得到正确解释。
答案3
简而言之...时间...如小时/分钟/秒数实际上是按字符数据排序的。1 小于 30 且 1h 小于 30m,因为 1 小于 3。您需要将数据存储为全秒...即 30 分钟输入为 1800 而 1 小时输入为 3600 并使用一些复杂的单元格格式将它们显示为小时/分钟/秒...或者分别输入数据为00:30:00和01:00:00。
第一个是恰当的方法但实现起来非常复杂......第二种方法更简单并且总是有效,因为 00:每次都在 01:之前。