☰

Excel で hh:mm:ss (期間) で並べ替える方法

tag-icon • tag-icon microsoft-excel sorting
Excel で hh:mm:ss (期間) で並べ替える方法

期間を表すデータの列があります。たとえば、33:15 - 30 分 15 秒、1:05:00 - 1 時間 5 分などです。

AZ で並べ替えようとすると、30 分よりも 1 時間が優先されます。

正しく並べ替えるためにデータをフォーマットする方法はありますか? このデータを秒数などに変換するよりも、フォーマット ソリューションが推奨されます。

答え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 は確信が持てません。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

何ですって? どうして #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 と入力した 1 時間 30 分よりも大きいと解釈されます。数値を入力した後は、必ず数式バーをチェックして、Excel が入力しようとした値を記録していなければなりません。

CompWiz はこの複雑さを見逃しており、Craig のルーチンは特定の問題に対してのみ修正します。ただし、値が 1 時間未満の場合でも時間を h:mm:ss として入力する必要があることを知っていれば、値は常に正しく解釈されます。

答え3

つまり、時間数、つまり時間/分/秒は、実際には文字データとしてソートされます。1 は 30 より小さく、1h は 30m より小さくなります。1 は 3 より小さいからです。データはすべて秒として保存する必要があります。つまり、30 分は 1800 と入力し、1 時間は 3600 と入力し、複雑なセル書式設定を使用して、時間/分/秒として表示します。またはデータをそれぞれ 00:30:00 と 01:00:00 として入力します。

1つ目はちゃんとした方法はありますが、実装が非常に複雑です... 2 番目ははるかに単純で、常に 00: が 01: の前に来るため、常に機能します。

関連情報