数値を時間に変換する(Microsoft Excel)
サムには、時間を含むワークシートがたくさんあります。問題は、時刻が「13:00」ではなく「1300」の形式になっていることです。したがって、Excelはそれらを時間として認識するのではなく、通常の数値として認識します。サムは、それらを実際の時間値に変換することを望んでいます。
このタスクに取り組む方法はいくつかあります。 1つの方法は、次に示すように、TIME関数を使用して値を時間に変換することです。
=TIME(LEFT(A1,2),RIGHT(A1,2),)
この数式は、セルA1の時間が常に4桁であることを前提としています。そうでない場合(たとえば、0427ではなく427になる可能性があります)、式を少し変更する必要があります。
=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),)
数式は基本的に左端の数字をプルしてTIME関数のhours引数に使用し、次に右端の2桁をminutes引数に使用します。 TIMEは、セル内でそのようにフォーマットされた実際の時間値を返します。
TIMEVALUE関数を使用して、同様の定型的なアプローチをとることができます。
=TIMEVALUE(REPLACE(A1,LEN(A1)-1,0,":"))
この数式は、REPLACEを使用してコロンを適切な場所に挿入し、TIMEVALUEが結果を時間値に変換します。結果のセルをフォーマットして、必要な時刻が表示されるようにする必要があります。
公式アプローチのもう1つのバリエーションは、次のようにTEXT関数を使用することです。
=--TEXT(A1,"00\:00")
これは実際の時間値を返します。これは、時間として表示されるように適切にフォーマットする必要があります。
もう1つのアプローチは、元の時刻を単純に計算して、Excelで使用される時刻値に変換することです。時間の値が1日の派閥の一部にすぎないことに気づいたら、これは簡単です。したがって、時間値は0から1までの数値であり、時間を24(1日の時間)で除算し、分を1440(1日の分)で除算することによって導出されます。これを行う式は次のとおりです。
=INT(A1/100)/24+MOD(A1,100)/1440
これにより、元の値の時間部分が決定され、24で除算されます。次に、分部分(元の値から残った部分)が1440で除算され、最初の部分に追加されます。その後、結果を時間としてフォーマットでき、完全に機能します。
これまでに説明したすべての式は、変換を行うために新しい列を利用します。これは便利ですが、数式を必要とせずに、実際に値をインプレースで変換したい場合があります。これは、マクロが役立つ場合があります。次のマクロは、選択したセルを時間値に変換し、セルを適切にフォーマットします。
Sub NumberToTime() Dim rCell As Range Dim iHours As Integer Dim iMins As Integer For Each rCell In Selection If IsNumeric(rCell.Value) And Len(rCell.Value) > 0 Then iHours = rCell.Value \ 100 iMins = rCell.Value Mod 100 rCell.Value = (iHours + iMins / 60) / 24 rCell.NumberFormat = "h:mm AM/PM" End If Next End Sub
マクロは整数除算を使用して時間数(iHours)を決定し、余りをiMinsに詰め込みます。次に、これは時間値に調整されてセルに戻され、セルは時間としてフォーマットされます。必要に応じて、セル形式をExcelでサポートされている他の時間形式に変更できます。
注:
このページ(または_ExcelTips_サイトの他のページ)で説明されているマクロの使用方法を知りたい場合は、役立つ情報を含む特別なページを用意しました。
link:/ excelribbon-ExcelTipsMacros [ここをクリックして、新しいブラウザタブでその特別なページを開きます]
。
_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。
このヒント(2775)は、Microsoft Excel 97、2000、2002、および2003に適用されます。Excel(Excel 2007以降)のリボンインターフェイス用のこのヒントのバージョンは、次の場所にあります。
link:/ excelribbon-Converting_Numeric_Values_to_Times [数値を時間に変換する]
。