教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows

 

 

Excel:時刻の基礎

   

 

 

 

 

 

 


 

 

 

 

 

 

 

 

 

 

 

 


 

 

 

 

 

 

 

 

 

 

 

 

Excelでは、時刻は実際は全て"シリアル値"という数値で格納されています。時間に関する様々な計算は、このシリアル値で認識されているからこそ可能となるのです。シリアル値という数字は見づらいので、通常は私達が理解しやすいようにhh:mmや、hh時mm分 等の形に変えて表示しています。入力時は、hh:mmの形で入力すればシリアル値として格納されます。

▼ 時刻のシリアル値の例

★1分(0:01)をシリアル値にするなら、1:00(0.0417÷60)=0.00069です。

 

↑のように、24時間でシリアル値「1」となります。よって、通常の数値は24で割ればシリアル値と同じなります。例えば、3時間 をシリアル値にするなら3÷24 →0.125で出せます。また上記のことから、シリアル値×24とすれば○時間を出すことができます。

 

【ポイント】

  • 数値÷24=シリアル値

  • シリアル値(hh:mm)×24=計算可能な数値(例10:50×24=10.75)


 

■ 24時間以上の表記

 

時刻表記(hh:mm)は24:00になると再度0:00〜からスタートします。(シリアル値は増え ます。24H=1で終ではありません。)表記が0.00に戻るので、時間の合計が出せない、という初心者の方もいらっしゃいます。

 

▼シリアル値の足し算 。24:00以上は0:00に戻るので合計が出ない例

 

このような場合は表示形式を変更します。

  1. 合計セルを右クリックし「セルの書式設定」をクリック

  2. 「表示形式」タブで「ユーザー定義」とし、[h]:mmの形にする
    → 43:29と表示される


 

■時給の計算

hh:mmは表示上の形で あり、実際に格納されているのはシリアル値です。ですから時間と時給をかけても実際はシリアル値×時給 であり正確な金額は出せません。(例えば、6時間(6:00)勤務で時給が1000の場合、6:00のシリアル値(0.25)×1000=250円、にな る)シリアル値を計算可能な時間数値に変換するには24をかけます。

 

▼ シリアル値×24 = 計算可能な時刻表示 (になる為、時給計算で使える)

時間

←のシリアル値×24

1:00

0.041667×24=1

6:00

0.25×24=6

6:30

0.27×24=6.5

12:00

0.5×24=12

24:00

1×24=24

 

◎ 例:出勤時刻〜退社時刻から日給を求めよう(時給 :1000円)

出勤 退社 合計時刻 日給
9:00 15:00 6:00 6,000
9:30 15:00 5:30 5,500
9:45 17:00 7:15 7,250

 

◆ 方法(1)  時刻(シリアル値)に24を掛け、時給をかける

合計時間*24*時給

◆ 方法(2)  HOUR関数とMINUTE関数を使う

(HOUR(合計時間)+(MINUTE(合計時間)/60))*時給

 

補足

  • 小数点以下をどうするか、については企業の給与システムのルールによります。例えば、6時間46分働いたとすると、計算対象は6:46(0.28…)×24=6.766666667…で 、時給を1000円をかけると6766.6667円になりますが【表示形式】だけで小数点以下を非表示にすると小数点以下第1位を四捨五入した「6767」となります。 小数点以下を切り捨てるのであれば、ROUNDDOWNなども式にいれる必要があります。

  • 多くの場合、時間はFLOOR関数やCEILING関数で丸めたり捨てたりします。例えば15分単位で退社時刻を捨てるなどです(17:03に退社しても17:00で計算)

  • HOUR関数やMINUTE関数を使いたい場合は、24時間以上の時間数と時給をかけるなら、Day関数もネストする必要があります。例えば98時間16分働いたとすると 次の式になります。(DAY(合計時間)*24+(HOUR(合計時間))+(MINUTE(合計時間)/60))*1000

 

 

 

教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows