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

スポンサードリンク

更新:2024/5/11;作成:2012/9/6

問題　　　　topへ

問題1：入場時刻と退場時刻が記録されています。滞在時間はいくらでしょう?
また、合計滞在時間をD6セルに求めなさい。　　　問題1の解答

問題2：ここは入場時刻～退場時刻の時間に対して課金されます。
ただし、12：00～13：00の間は無料となっています。課金される時間は何時間何分でしょう?　　問題2の解答

問題3：駐車料金が20分で100円の駐車場に入りました。料金はいくらでしょう　　　問題3の解答

解答
入場時刻と退場時刻はシリアル値になっていますので、そのまま引き算で計算できます
D3セルは =C3-B3 としました。
D6セルは =SUM(D3:D5) としました。
セルの表示形式は　h:mm　となっていると「0:06」と表示されるので、[h]:mm　とします。

時刻のシリアルとは小数になっているので、演算誤差を生じる可能性があります。
演算誤差を避ける計算をしてみたいと思います。
演算誤差については下記のサイトをご覧ください。
- Excel で浮動小数点演算の結果が正しくない場合がある
  http://support.microsoft.com/kb/78113/ja
整数値で計算する方法があります。
24時=1　となるので、24*60=1440をかけることで分単位(整数)になります。
D3セルに　=(ROUND(C3*1440,0)-ROUND(B3*1440,0))/1440　と入力して、下方向へ数式をコピーします。
Text関数を利用して、Excelの内部で丸め処理を行う方法もあります。
ただし、Text関数では文字列が返りますので、*1として数値へ変換します。
- 時刻の文字列であるので、Excelが計算ができる文字列と判定して計算をします。

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

20分で100円というのは、1分間あたり5円となりますので、　(C3-B3)*1440*5　となります。
ここで、20分刻みでの課金＝100円単位での支払い　と考えると、100円単位で丸め処理することも可能です。
丸め処理にはCEILING関数を使いました。
時刻から駐車時間を求めるには　C3-B3　となります。
20分単位ですので　　(C3-B3)/"0:20"　として、端数を切り上げればよさそうです。
よって、=ROUNDUP((C3-B3)/"0:20",0)*100　としました。
- 時刻を数式内で使う時は「"」で括ります。Excelが時刻のシリアル値に変換して計算してくれます。
20分単位ですので、駐車時間を分単位で求めると　ROUND((C3-B3)*1440,0)　としました。
20分単位ですので、　ROUND((C3-B3)*1440,0)/20　となり、端数は切り上げで求めることにしました。
よって、=ROUNDUP(ROUND((C3-B3)*1440,0)/20,0)*100　としました。
Bと同じ考え方の計算ですが「分」を出すのに、TEXT関数を使うこともできます。
TEXT(C3-B3,"[m]") で分数を出すことができます。
この部分で返される値は文字列ですが、Excelは計算できる文字列ということで、数値として扱って計算してくれます。