わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】24時間を超える時刻の足し算引き算、0時よりも前の時刻の処理

Excelで時刻の足し算引き算がうまくいかないことがあります。関数を使わなくても何時間前、何時間後を計算することはできるのですが、その方法を知らない人は多いと思います。練習する問題集もなければ、テキストにもそんなに詳しく載っていないので知らない人が多いのも当然です。

時刻の計算の練習をするなら、関数よりもまず足し算と引き算を練習することをおすすめします。

そこで、今回は、時刻の足し算引き算と24時間を超えた場合の処理、0時より前の時刻の扱いについて出題します。

 

 

目次

1.時刻のシリアル値(復習)

Excelで時刻の計算をするにはまず時刻のシリアル値について理解する必要があります。詳しくはこちらの記事をご覧ください。

 

2.1日と1時間

問題

「2012/3/4 12:34」と入力して次の計算をしなさい。
(1)1日を足しなさい。また、1日を引きなさい。
(2)1時間を足しなさい。また、1時間を引きなさい。

解説

「2012/3/4 12:34」と入力します。

f:id:waenavi:20190902163741j:plain

 

1を足します。

f:id:waenavi:20190902163814j:plain

 

1日=24時間のシリアル値は1なので1日増えます。

f:id:waenavi:20190902163841j:plain

 

1を引くと1日減ります。

f:id:waenavi:20190902163934j:plain

 

今度は「"1:00"」を足します。ダブルクォーテーションで囲んで時刻の形式で足します。1時間増えます。

f:id:waenavi:20190902163906j:plain

 

「"1:00"」を引くと1時間減ります。

f:id:waenavi:20190902163957j:plain

 

3.時刻の足し算

(1)時刻の文字列に1を掛ける

問題

「="2:30"」と入力したときの計算結果は時刻(シリアル値)と言えるか。シリアル値にして、時刻の表示形式にしなさい。

解説

="2:30"」と入力すると左揃えになります。ダブルクォーテーションで囲んだものは文字列であって時刻でもシリアル値でもありません。

f:id:waenavi:20190902164110j:plain

 

セルに小数のシリアル値が保存されるわけではありません。時刻の形をした文字列を時刻(シリアル値)にするためには、足し算・引き算・掛け算・割り算の四則演算が必要です。最も簡単な方法は1を掛けることです。小数になります。これは2時間半のシリアル値です。

f:id:waenavi:20190902164143j:plain

 

時刻の表示形式にすると2分30秒になります。

f:id:waenavi:20190902164226j:plain

 

(2)時間を足す

問題

2時間半と4時間40分を足しなさい。

解説

「2:30」「4:40」と入力します。

f:id:waenavi:20190902164359j:plain

 

これらを足します。これは2時間半と4時間40分を足したと考えてもいいですし、2時30分から4時間40分経過した時刻と考えてもかまいません。6時間70分ではなく「7:10」となりました。

f:id:waenavi:20190902164449j:plain

 

ここで表示形式を標準にしてシリアル値を表示します。Excelは時間を小数のシリアル値で管理しています。1=24時間です。

f:id:waenavi:20190902164651j:plain

 

2:30と4:40を小数にして、0.104と0.194を足した結果0.298にあたる時刻を表示しています。

f:id:waenavi:20190902164933j:plain

 

別解

ダブルクォーテーションで囲んだ時刻文字列で足し算をするとそれぞれシリアル値になります。小数になります。

  • ="2:30"+"4:40"

f:id:waenavi:20190902165022j:plain

 

時刻の表示形式にすると7:10となります。

f:id:waenavi:20190902165146j:plain

 

(3)24時を超える場合

問題

23時に就寝して、6時間半後に起きる場合の起床時間を求めなさい。

解説

「23:00」「6:30」と入力します。

f:id:waenavi:20190902165247j:plain

 

これらを足します。23時の6時間半後は5時半です。

f:id:waenavi:20190902165311j:plain

 

ここで表示形式を標準にしてシリアル値を表示します。答えが1を超えています。

f:id:waenavi:20190902165404j:plain

 

23時間+6時間半=1日と5時間30分です。1以上の小数の場合、整数部分が日数を表し、小数点より右側の小数部分が、24時間未満の時間を表します。

f:id:waenavi:20190902165511j:plain

 

そして、時刻同士の足し算をすると時刻の表示となり、日数は非表示となるため「1日」の部分は表示されません。表示されていないだけで計算結果には1日(24時間)が含まれています。

f:id:waenavi:20190902170029j:plain

 

(4)値の貼り付け

問題

2分半と3分半を加算しなさい。また、計算結果をコピーして、別のセルに値だけ貼り付けなさい。

解説

「0:2:30」「0:3:30」と入力します。これで2分半と3分半になります。

f:id:waenavi:20190902170123j:plain

 

これらを足すと6分になります。コピーします。

f:id:waenavi:20190902170156j:plain

 

値の貼り付けをすると小数になります。これは6分のシリアル値です。ちなみに、6分のシリアル値(=1/240)は割り切れないので無限に続く小数になります。

f:id:waenavi:20190902170250j:plain

f:id:waenavi:20190902170328j:plain

 

時刻の表示形式にします。

f:id:waenavi:20190902170351j:plain

 

(5)時刻をSUMで合計する

問題

1時間1分~12時間12分をすべて足しなさい。また、「d日h時間m分」の形式にしなさい。

f:id:waenavi:20190902170631j:plain

 

解説

1:01~12:12を入力します。

f:id:waenavi:20190902170724j:plain

 

時刻の表示になっていますが、小数と同じなのでオートSUMボタンの合計が使えます。

f:id:waenavi:20190902170801j:plain

 

合計すると7:18になりますが、24時間以上の部分が隠れていることは明らかです。

f:id:waenavi:20190902170909j:plain

 

24時間以上の時間を表示するには表示形式を[h]:mmとします。

f:id:waenavi:20190902171000j:plain

 

表示形式を「d日h時間m分」にすると日数と時間と分が表示できます。

f:id:waenavi:20190902171113j:plain

 

4.24時間を超えた判定

問題

A列とB列の時間を足して30時間を超えていればTRUE、30時間以下であればFALSEと表示しなさい。

f:id:waenavi:20190902171934j:plain

 

解説

A列とB列を足します。

f:id:waenavi:20190902172304j:plain

 

24時間が隠れていることは明らかです。「30:00」と比較をすれば30時間を超えているかどうかが分かります。

f:id:waenavi:20190902172326j:plain

 

ダブルクォーテーションで囲んで1を掛けます。

f:id:waenavi:20190902172353j:plain

 

これで完成です。

f:id:waenavi:20190902172428j:plain

 

表示形式を[h]:mmとします。

f:id:waenavi:20190902172505j:plain

 

ちょうど30時間の時はFALSE、30時間を超えたらTRUEになります。

f:id:waenavi:20190902172613j:plain

 

5.時刻の引き算

(1)時間を引く

問題

移動に80分かかることが分かっている場合、午前7時までに到着するには何時何分に出発すればよいか。

解説

80分ということは1時間20分です。これを7時から引きます。両方とも小数のシリアル値なので引き算をすることができます。

f:id:waenavi:20190902172717j:plain

 

5時40分です。

f:id:waenavi:20190902172735j:plain

 

(2)秒が表示されないことがある

問題

2時間から5秒を引きなさい。

解説

「2:0」「0:0:5」と入力します。

f:id:waenavi:20190902172828j:plain

 

引き算をすると1時間59分になります。秒が非表示になっています。非表示にはなりますが四捨五入になるわけではありません。

f:id:waenavi:20190902172846j:plain

 

表示形式を「h:mm:ss」の形式にします。1時間59分55秒となります。

f:id:waenavi:20190902172924j:plain

 

別解

ダブルクォーテーションで囲んで計算することもできます。小数になります。

  • ="2:0"-"0:0:5"

f:id:waenavi:20190902173008j:plain

 

表示形式を「h:mm:ss」の形式にします。

f:id:waenavi:20190902173024j:plain

 

(3)マイナスは#####になる

問題

「7:00」から「9:00」を引くことは可能か。

解説

「7:0」「9:0」と入力します。

f:id:waenavi:20190902173118j:plain

 

7時から9時を引きます。マイナス2時間になるはずですが、#####の表示になります(後述)。

f:id:waenavi:20190902173225j:plain

 

シリアル値はもともと1900年1月1日からの経過日数であり、マイナスは想定していません。マイナスのシリアル値は存在しないので、日付や時刻の表示形式にすることはできません。

f:id:waenavi:20190902173450j:plain

 

日付や時刻の「表示形式にならない」だけなので、標準にすればマイナスの小数が表示されます。

f:id:waenavi:20190902173652j:plain

 

正確に言えば、セルに保存されている値がマイナスの場合、表示形式の日付や時刻(年yyyy、月m、日d、時h、分m、秒sなど)が使えません。小数の引き算は可能ですが、日付や時刻としての結果が表示できません(後述)。

 

6.0時より前になっても正しく表示する

問題

午前7:00の9時間前を計算して、22:00と表示するにはどうすればよいか。 

解説

午前7時の9時間前を計算するには7:00から9:00を引かなければなりませんが、マイナスになってしまい表示できません。22:00にはなりません。

f:id:waenavi:20190902173807j:plain

 

そこで、24時間を加算して31:00と入力します。

f:id:waenavi:20190902173848j:plain

 

7:00と表示されていますが、セルに保存されているのは「1日+7時間」です。引き算すると、22:00になります。

f:id:waenavi:20190902173931j:plain

 

表示形式を[h]:mmにします。31時から9時を引いて22時になります。

f:id:waenavi:20190902174031j:plain

 

別解その1

どうしても「7:00」のままで答えを22:00にしたい場合は、計算式で1を足します。7時-9時+1です。

f:id:waenavi:20190902174241j:plain

 

1というのは24時間のことです。24時間を加算することによって22:00となります。

f:id:waenavi:20190902174318j:plain

 

別解その2

7時から9時間を引くときは24時間を加算して22時としますが、逆に、9時から7時間を引くと2時です。24時間は加算しません。

  • 小さい時刻(7時)-大きい時刻(9時)+24時間
  • 大きい時刻(9時)-小さい時刻(7時)

f:id:waenavi:20190902174632j:plain

 

つまり、答えがマイナスになる時だけ1を加算すればよいです。そこで、引き算をする数を比較して、最初の数が小さい時に1を加算します。

  • (最初の時刻A)-(引く時間B)+(A<B)

f:id:waenavi:20190902175626j:plain

 

7.マイナスになったら24時間を足す

問題

B列からA列を引きなさい。ただし、B列のシリアル値がA列より小さい場合は1をたしなさい。

f:id:waenavi:20190902180530j:plain

 

解説

開始時刻と終了時刻があって、その間の時間を求めるときには、終了時刻から開始時刻を引けばよいです。

f:id:waenavi:20190902180428j:plain

 

引き算をすると######になっているものがあります。

f:id:waenavi:20190902180629j:plain

 

開始時刻より終了時刻のほうが前(シリアル値が小さい)ものがあり、引き算をするとマイナスになります。マイナスになっていないものは、もともと24時間以上の時刻が入っているものと考えられます。

f:id:waenavi:20190902185139j:plain

 

そこで、さきほどの設問と同じように、終了時刻のほうが小さい場合には1を足します。

  • (終了時刻)-(開始時刻)+(終了時刻<開始時刻)

f:id:waenavi:20190902185440j:plain

 

これで、完成です。

f:id:waenavi:20190902185508j:plain

 

24時間以上の時刻を入力する可能性がある場合は、あらかじめ[h]:mmの設定を入れておいたほうが分かりやすいです。

f:id:waenavi:20190902185554j:plain

 

8.マイナスを表示するには関数が必要

B列からA列を引いたときにマイナスになる場合に、マイナスを表示するには絶対値を求めたうえで、マイナスを付けた表示形式にします。TEXT関数とABS関数が必要です。

  • =TEXT(ABS(B2-A2),IF(B2<A2,"-","")&"h:mm")
  • =IF(B2<A2,"-","")&TEXT(ABS(B2-A2),"h:mm")

f:id:waenavi:20190902191114j:plain

 


解説は以上です。時刻の掛け算と換算の解説へつづく・・・


 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]