1. Home » 
2. エクセル練習問題：目次 » 
3. 時間の計算

エクセル練習問題：時間の計算

問題1の解答例　　　　topへ

• 解答
  

  	B	C	D
  2	入場時刻	退場時刻	滞在時間
  3	9:09	17:05	7：56
  4	9:08	17:13	8:05
  5	9:04	17:09	8:05
  6		合計	24:06

• 入場時刻と退場時刻はシリアル値になっていますので、そのまま引き算で計算できます
  

  	B	C	D
  2	入場時刻	退場時刻	滞在時間
  3	9:09	17:05	=C3-B3
  4	9:08	17:13	=C4-B4
  5	9:04	17:09	=C5-B5
  6		合計	=SUM(D3:D5)

  • D6セルの表示形式は　h:mm　となっていると「0：25」と表示されるので、[h]:mm　とします。

• 演算誤差を考慮すると・・・

  • 時刻のシリアルとは小数になっているので、演算誤差を生じる可能性があります。
    演算誤差を避ける計算をしてみたいと思います。
  • 演算誤差については下記のサイトをご覧ください。
    • Excel で浮動小数点演算の結果が正しくない場合がある
      http://support.microsoft.com/kb/78113/ja
  • 整数値で計算する方法があります。
    24時=1　となるので、24*60=1440をかけることで分単位(整数)になります。
    

    	B	C	D
    2	入場時刻	退場時刻	滞在時間
    3	9:09	17:05	=(ROUND(C3*1440,0)-ROUND(B3*1440,0))/1440
    4	9:08	17:13	=(ROUND(C4*1440,0)-ROUND(B4*1440,0))/1440
    5	9:04	17:09	=(ROUND(C5*1440,0)-ROUND(B5*1440,0))/1440
    6		合計	=SUM(D3:D5)

  • Text関数を利用して、Excelの内部で丸め処理を行う方法もあります。
    ただし、Text関数では文字列が返りますので、*1として数値へ変換します。
    • 時刻の文字列であるので、Excelが計算ができる文字列と判定して計算をします。

    	B	C	D
    2	入場時刻	退場時刻	滞在時間
    3	9:09	17:05	=TEXT(C3-B3,"h:mm")*1
    4	9:08	17:13	=TEXT(C4-B4,"h:mm")*1
    5	9:04	17:09	=TEXT(C5-B5,"h:mm")*1
    6		合計	=SUM(D3:D5)

問題2の解答例　　　　topへ

• 解答
  

  	B	C	D
  2	入場時刻	退場時刻	課金時間
  3	9:10	11:40	2:30
  4	11:04	12:30	0:56
  5	10:50	13:15	1:25
  6	12:10	14:10	1:10
  7	13:05	14:48	1:43

• 12：00〜13：00の間は計算しないわけですので、12：00までと13：00以降とに分けて計算することができます。
  • 12：00までの計算は　MIN("12:00",C3)-B3　で求めることができます。
    13：00以降は　C3-MAX("13:00",B3)　となります。
  • しかし、負(マイナス)の時間となる計算はエラーになるので、負のときは最大値の「0」となるようにMax関数を使っています。
  • =MAX(0,MIN("12:00",C3)-B3)+MAX(0,C3-MAX("13:00",B3))

  	B	C	D
  2	入場時刻	退場時刻	課金時間
  3	9:10	11:40	=MAX(0,MIN("12:00",C3)-B3)+MAX(0,C3-MAX("13:00",B3))
  4	11:04	12:30	=MAX(0,MIN("12:00",C4)-B4)+MAX(0,C4-MAX("13:00",B4))
  5	10:50	13:15	=MAX(0,MIN("12:00",C5)-B5)+MAX(0,C5-MAX("13:00",B5))
  6	12:10	14:10	=MAX(0,MIN("12:00",C6)-B6)+MAX(0,C6-MAX("13:00",B6))
  7	13:05	14:48	=MAX(0,MIN("12:00",C7)-B7)+MAX(0,C7-MAX("13:00",B7))

  • TEXT関数を組み合わせると、
    =TEXT(MAX(0,MIN("12:00",C3)-B3),"h:mm")*1+TEXT(MAX(0,C3-MAX("13:00",B3)),"h:mm")*1
    となります。

問題3の解答例　　　　topへ

• 解答例
  なお、D列の表示形式は「G/標準」とします。
  

  	B	C	D
  2	入車時刻	出車時刻	駐車料金
  3	9:10	11:40	800
  4	11:04	12:30	500
  5	10:50	13:15	800

1. 時刻から駐車時間を求めるには　C3-B3　となります。
   20分単位ですので　　(C3-B3)/"0:20"　として、端数を切り上げればよさそうです。
   よって、=ROUNDUP((C3-B3)/"0:20",0)*100　としました。
   • 時刻を数式内で使う時は「"」で括ります。Excelが時刻のシリアル値に変換して計算してくれます。

   	B	C	D
   2	入車時刻	出車時刻	駐車料金
   3	9:10	11:40	=ROUNDUP((C3-B3)/"0:20",0)*100
   4	11:04	12:30	=ROUNDUP((C4-B4)/"0:20",0)*100
   5	10:50	13:15	=ROUNDUP((C5-B5)/"0:20",0)*100

2. 20分単位ですので、駐車時間を分単位で求めると　ROUND((C3-B3)*1440,0)　としました。
   20分単位ですので、　ROUND((C3-B3)*1440,0)/20　となり、端数は切り上げで求めることにしました。
   よって、=ROUNDUP(ROUND((C3-B3)*1440,0)/20,0)*100　としました。
   

   	B	C	D
   2	入車時刻	出車時刻	駐車料金
   3	9:10	11:40	=ROUNDUP(ROUND((C3-B3)*1440,0)/20,0)*100
   4	11:04	12:30	=ROUNDUP(ROUND((C4-B4)*1440,0)/20,0)*100
   5	10:50	13:15	=ROUNDUP(ROUND((C5-B5)*1440,0)/20,0)*100

3. Bと同じ考え方の計算ですが「分」を出すのに、TEXT関数を使うこともできます。
   TEXT(C3-B3,"[m]") で分数を出すことができます。
   この部分で返される値は文字列ですが、Excelは計算できる文字列ということで、数値として扱って計算してくれます。
   

   	B	C	D
   2	入車時刻	出車時刻	駐車料金
   3	9:10	11:40	=ROUNDUP(TEXT(C3-B3,"[m]")/20,0)*100
   4	11:04	12:30	=ROUNDUP(TEXT(C4-B4,"[m]")/20,0)*100
   5	10:50	13:15	=ROUNDUP(TEXT(C5-B5,"[m]")/20,0)*100

4. 20分で100円というのは、1分間あたり5円となりますので、　(C3-B3)*1440*5　となります。
   ここで、20分刻みでの課金＝100円単位での支払い　と考えると、100円単位で丸め処理することも可能です。
   丸め処理にはCEILING関数を使いました。
   

   	B	C	D
   2	入車時刻	出車時刻	駐車料金
   3	9:10	11:40	=CEILING((C3-B3)*1440*5,100)
   4	11:04	12:30	=CEILING((C4-B4)*1440*5,100)
   5	10:50	13:15	=CEILING((C5-B5)*1440*5,100)

スポンサードリンク

PageViewCounter

Since2006/2/27