☰

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분"을 의미하셨나요? 엑셀은 확실하지 않습니다. 그 동작은 시간과 분을 의미한다고 가정하는 것입니다. h 주위의 []는 기본적으로 "시간 섹션에 24시간 이상 표시"를 의미합니다.

삼.

-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분으로 입력한 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으로 입력합니다.

첫 번째는적절한방법이지만 구현하기가 매우 복잡합니다. 두 번째 방법은 훨씬 간단하고 항상 작동합니다. 왜냐하면 00:이 항상 01: 앞에 오기 때문입니다.

관련 정보