Excel(エクセル)基本講座：時間・時刻の関数

• Excelでの時間・時刻のシリアル値から時,分,秒を取出す関数(HOUR関数,MINUTE関数,SECOND関数)
  時,分,秒の数値から時間・時刻のシリアル値に直す関数(TIME関数)
  文字列からシリアル値に直す関数(TIMEVALUE関数)
  について解説しています。
• 時給計算の仕方を例に挙げて説明しています。

シリアル値を数値へ(HOUR,MINUTE,SECOND)	数値をシリアル値へ(TIME関数)
文字列をシリアル値へ(TIMEVALUE関数)
時間の合計(表示形式)	勤務時間の計算について

シリアル値：Excelでは日付や時刻はシリアル値で処理されます。

• 日付は1900年1月1日を「1」、1900年1月2日を「2」・・・と、数値で表します。
• 時間・時刻は「1日(24時間)」を「1」として、12:00は「0.5」、6:00は「0.25」と小数点以下の数値で表します。
• なお、WindowsとMacintoshでは、シリアル値の基準が異なります。
  • Macintoshでは「1904年1月2日」が「１」となっています。
  • WindowsとMacintoshでExcelファイルをやり取りすると、日付がずれる場合があります。
    • 【ツール】→【オプション】で【計算方法】タブの『1904年から計算する』にチェックを入れて、シリアル値の起点を変更します。

時刻データから「時」「分」「秒」を取り出す(シリアル値から数値への変換)　　topへ
　アワー
=HOUR(時間)　　　：時間を取り出します。0〜23(時)の範囲の整数になります。
　ミニット
=MINUTE(時間)　：分を取り出します。　0〜59(分)の範囲の整数になります。
　セカンド
=SECOND(時間)　：秒を取り出します。　0〜59(秒)の範囲の整数になります。
　　　(注)　時間にはシリアル値を使用します。

• 【問題】
  • 出社時間、退社時間、時給からその日の日給を計算しましょう。
  • セルD5,D7,D8,D9,D10,D11に数式を入れましょう。

  • 	B	C	D
    2	時給計算
    3	時給	1,000
    4	出社時刻	退社時刻	勤務時間
    5	8:00:10	17:20:20	9:20:10
    6
    7	勤務時間	時間	9
    8		分	20
    9		秒	10
    10	時給計算例	シリアル値	9,336.11
    11		時間単位	9,333.33

• 【解答例】

  • 	B	C	D
    2	時給計算
    3	時給	1,000
    4	出社時刻	退社時刻	勤務時間
    5	8:00:10	17:20:20	=C5-B5
    6
    7	勤務時間	時間	=HOUR（D5)
    8		分	=MINUTE（D5)
    9		秒	=SECOND(D5)
    10	時給計算例	シリアル値	=D5*24*C3
    11		時間単位	=D7*C3+D8/60*C3

  1. 勤務時間=退社時間-出社時間　=C5-B5　で求められます。
  2. 日給の計算
     1. シリアル値での計算
        • 勤務時間（D5)×時給(C3)　では求めることができません。
          時間データにシリアル値が使われているためです。
          (24時間が「1.0」で、10時間は「0.4166667」となります。)
        • 勤務時間*24*時給　つまり、=D5*24*C3　とします。
     2. 時間単位での計算
        • 時間と分を取り出してから計算することもできます。
          この場合はHOUR、MINUTE関数を使って時間、分を取り出して計算します。　
        • 時間×時給＋（分÷60）×時給　=D7*C3+D8/60*C3　とします。(秒は無視しています)
        • =HOUR(D5)*C3+MINUTE(D5)/60*C3　と1セルでまとめることもできます。

(ステップアップ)時給の四捨五入

• 上記の結果から分かると思いますが、秒単位まで表示していた場合シリアル値での計算では秒単位の日給が計算され、実情と異なってしまいます。(通常は分単位までと思います)
• また、日給計算結果には小数点以下の金額が計算されていますので、日々の日給は整数の単位に四捨五入などして丸めておく必要があります。

• 四捨五入の例です（9333となります）　　　　　　　　　
  • =ROUND(D5*24*C3,0)では秒単位まで計算され「9336」となるので、時と分だけ取出して計算します。
  • =ROUND(HOUR(D5)*C3+MINUTE(D5)/60*C3,0）
• 30分単位で勤務時間を切り捨てて計算する場合（9000となります）
  　　　　　　　　　
  • =FLOOR(D5,"0:30")*24*C3

（おまけ）時刻を数値に変換する

• 時刻「10：30」を数値の「10.5」や「時.分」に変換する例です。
  

(関連事項)

• ROUND 、FLOOR関数はこちらへ
• 勤務時間の計算について

時刻に対応する小数(シリアル値)を返します　　　topへ
　タイム
=TIME(時,分,秒)
　　時：0 〜 32767 の範囲で指定します。23 を超える値は 24 で除算され、剰余が時間として計算されます。
　　分：0 〜 32767 の範囲で指定します。59 を超える値は時と分に変換されます。
　　秒：0 〜 32767 の範囲で指定します。59 を超える値は時、分、秒に変換されます。

• 【問題】
  • 時刻の時、分、秒が別々のセルに表示してあります。
  • E4セルには『時：分：秒』　E5セルには『時：分』と表示しなさい。

  • 	B	C	D	E
    2	時	分	秒	時：分：秒
    3	11	22	3	11：22：3
    4	13	10		13：10

• 【解答例】

  • 	B	C	D	E
    2	時	分	秒	時：分：秒
    3	11	22	3	=TIME(B3,C3,D3)
    4	13	10		=TIME(B4,C4,0)

• 【操作例】
  1. E4:E5セルは次のように表示されました。
     
  2. 【書式】→【セル】で表示形式を変更します。
     • E4セルは『13:30:55』
     • E5セルは『13:30』
       と、それぞれ設定します。
       

時間の合計　　表示形式に注意が必要です。　　　　topへ
　　　標準では、24時表示(時刻表示)になっているため、24時を超える値は正しく表示されません。
　　　下図のように、合計のセルの表示形式を[h]:mmとします。

• 【操作手順】
  1. B8セルを選択します。
  2. メニューバーの【書式】→【セル】を選択します。
  3. 【表示形式】タブで「ユーザー定義」を選択し、種類に「[h]:mm」と入力します。
  4. なお、違いが分かるようにセルB7,B8には　=SUM(B2:B5)　と同じ式が入れてあります。
     B7セルの表示形式は　h:mm　、B8セルの表示形式は　[h]:mm　とした例です。
     

文字列で表された時刻を小数(シリアル値)に変換します　　topへ
　タイムバリュー
=TIMEVALUE(時刻文字列)
　　時刻のシリアル値：0〜0.99999999 の値で、0:00:00(午前0時)〜23:59:59 (午後11時59分59秒) までの時刻を返します。

• 文字データを時刻データ(0〜1の小数、シリアル値）に変換します。
• TIMEVALUE関数は24時までのシリアル値になります。24:00以上を扱う時はVALUE関数を使うことができます。

• 【問題】
  • 文字列の時刻データがB2〜B5に入力されています。
  • それぞれの時刻をシリアル値に変換して、C列、D列に表示しなさい。

  • 	B	C	D
    2	6:00	0.25
    3	12:00	0.5
    4	24:00	0	1
    5	36:00	0.5	1.5

  • 24:00以上を表示するにはセルの表示形式をユーザー定義で「[h]:mm」とします。
  • 文字列として時刻を表示するには「'6:00」のように「'」（アポストロフィ）を頭に付けます。
    または、=TEXT（"6:00","h:mm")　のようにTEXT関数で変換します。
• 【解答例】
  • C4セル以降のようにTIMEVALU関数では24:00以上はうまく計算されません。
    TIMEVALUE("24:00")=0　となります。
  • 24:00以上を計算する時には、D列のようにVALUE関数を使用するか、=B4　として表示形式を数値にします。

  • 	B	C	D
    2	6:00	=TIMEVALUE(B2)
    3	12:00	=TIMEVALUE(B3)
    4	24:00	=TIMEVALUE(B4)	=VALUE(B4)
    5	36:00	=TIMEVALUE(B5)	=VALUE(B5)

【ステップアップ】時間を単位時間で切り捨てる

• 時給計算などで、30分単位で切り捨てて計算する場合には
  =FLOOR(値,"0:30")　とします。
• しかし、時間は24時間を1としたシリアル値で換算されていますので、小数部で誤差が出ることがあります。
  FLOOR関数でうまくいかない場合もありますので、そういった場合には、
  (キーボードからの入力ではほとんど問題ないですが、オートフィルなどで連続データを入力した時などにこの問題を生じます。）
  • =FLOOR((TIMEVALUE(TEXT(D5,"hh:mm"))),"0:30")*24*D3
  のようにTIMEVALUE関数を使う方法があります。
• FLOOR関数はこちらへ

PageViewCounter

Since2006/2/27