Excel(エクセル)関数の技：時刻・時間の計算に関する技

• 時刻・時間の計算に関する処理について説明しています

時刻・時間のシリアル値　　topへ

• Excelでは時刻・時間をシリアル値という値で処理します。
• 8:10：15(時：分：秒)のように「:」(コロン)で区切って入力することで、Excelは時刻が入力されたと判断してシリアル値として扱います。
• 0:00:00(午前0時)から23:59:59(午後11時59分59秒)までの時刻は、小数で0〜0.99999999の範囲の値で表わされます
  • 1日=24時間が1となっているので、1時間=1:00は1/24=0.0416666666666667となります。
    • 1/24=0.041666666666666・・・と割り切れない値なのでExcelでは0.0416666666666667と表示されます。Excelの有効桁数は15桁ですので、15桁に丸められます。
    • セルの表示形式を標準とするとシリアル値をみることができます。
      
  • 24時間以上の場合は表示形式を[h]:mmなどとすることにより、25:00と表示することが出来ます。
    • 25:00と入力すると、時刻の表示形式h:mmが適用され1:00と表示されます。
      表示形式を[h]:mmとすると25:00と表示されます。
      

時刻・時間の計算　　topへ

• セル参照で計算する
  • セル番地同士の計算
    
• 数式内でシリアル値を使う
  • シリアル値として計算するときはダブルクォーテーションで囲みます
    
  • TIME関数を利用する
    

時刻・時間の丸め処理　　topへ

• 単位時間で切り下げる
  • FLOOR関数を利用し、30分単位で切り下げる例です。
    
• 単位時間で切り上げる
  • CEILING関数を利用し、30分単位で切り上げる例です。
    
• 単位時間で四捨五入する
  • MROUND関数を利用して、30分単位に四捨五入する例です。
    なお、MROUND関数を使うには「分析ツール」を組み込む必要があります。(Excel2003以前の場合。Excel2007は不要)
    

シリアル値を数値に直す　　topへ

• 時給の計算などをするとき、数値に直して計算する必要があります。
  • 24:00=1　なので　シリアル値に24を掛けると時間単位になります。
    
  • 分単位に直すには24*60=1440を掛けます
    

時刻・時間の比較　　topへ

• Excelでは時刻・時間の形式（「：」（コロン)で区切って）でセルに入力すると、自動的にシリアル値に変換してくれます。
  ところが、数式内で使用するとき「"」で括ると文字列として扱われることがあります。
• 時刻・時間を比較するとき、セル同士で比較する場合はそれほど問題にはならないと思いますが数式内での比較には注意が必要です。
  • 時刻9：00を過ぎての出勤は遅刻とする例です。
    • =IF(A2>"9:00","遅刻","OK")　とすると・・・比較がうまくできていません。
      これは"9:00"を文字列として比較しているため、A列の数値(シリアル値)が小さいと判定されているためです。
      
    • =IF(A2>"9:00"*1,"遅刻","OK")のように、"9:00"*1とするとExcelがシリアル値に変換してくれます。
      • 関数を使うと、
        • =IF(A2>TIMEVALUE("9:00"),"遅刻","OK")
        • =IF(A2>VALUE("9:00"),"遅刻","OK")
        のように書くこともできます。
      
    • または、TIME関数を使って=IF(A2>TIME(9,0,0),"遅刻","OK")　とする方法も使えます。
      

数値を時刻・時間に変換する　　topへ

• 時刻が数値で入力されているデータがあり、それをシリアル値に変換して計算したいときがあります。
  • 出社時刻850、退社時刻1710と入力されていたとします。
    1. 850の場合：INT(B2/100)*60+MOD(B2,100)のように分単位に直して計算することができます。
       =(INT(B2/100)*60+MOD(B2,100)-(INT(A2/100)*60+MOD(A2,100)))/1440
       
    2. TEXT関数を利用してシリアル値に直して計算する方法も使えます。
       
  • 出社時刻8.50、退社時刻17.10とドット区切りで入力されていたとします。
    1. 整数部分を*60とし、小数部分を*100として分単位に直して計算します。
       • =(INT(B2)*60+MOD(B2,1)*100-(INT(A2)*60+MOD(A2,1)*100))/1440
       小数誤差が気になるので、
       • =(ROUND(INT(B2)*60+MOD(B2,1)*100,0)-ROUND(INT(A2)*60+MOD(A2,1)*100,0))/1440
       としてみました。
       
    2. 小数の桁数をTEXT関数で小数点以下2桁にします。TEXT(B2,"0.00")
       更に、「.」小数点を「:」コロンにSUBSTITUTE関数で置き換えてシリアル値として計算します。
       • =SUBSTITUTE(TEXT(B2,"0.00"),".",":")-SUBSTITUTE(TEXT(A2,"0.00"),".",":")
       小数誤差が気になるので、もうひとつTEXT関数を組み合わせると、
       • =TEXT(SUBSTITUTE(TEXT(B2,"0.00"),".",":")-SUBSTITUTE(TEXT(A2,"0.00"),".",":"),"h:mm")*1
       となります。
       

　PageViewCounter
　
　Since2006/2/27