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

当サイトにリンクを張るのは自由です。WordとExcelの練習問題の動画を「Youtube」で無料公開しています。みんなで勉強しましょう!

【Excel】時刻のシリアル値と時間計算の基本を理解するための練習問題

実はExcelで時間の計算をするときには、内部では無限小数(割り切れない小数)の計算をしています。しかし、Excelは細かい小数の計算をするのがあまり得意ではないので、1秒未満の細かい時間を計算したり分析したりするのはあまりおすすめできません。科学的な計算をするのであれば、専門的なソフトを使うべきです。

事務職の人が分単位で時間給を計算したり、スポーツで秒単位のデータを管理するくらいであれば、問題なく使えると思います。

そこで、今回は、時刻のシリアル値と、Excelで時刻を扱うときの注意点、時刻の表示形式について出題します。

 

 

目次

1.日付のシリアル値(復習)

時刻のシリアル値を理解するにはまず日付のシリアル値と足し算引き算を理解する必要があります。

 

2.時刻の入力の基本練習

(1)時刻とオートフィル

問題

「4時」「4時間」「4:0」と入力してオートフィルしなさい。

f:id:waenavi:20190901192315j:plain

 

解説

「4時」と入力します。左揃えになります。これは単なる文字列であることを表しています。オートフィルをすると5時、6時となりますが、これは数字と文字が混ざった文字列だからです。Excelが時刻と認識しているわけではありません。

f:id:waenavi:20190902002808j:plain

 

「4時間」の場合も同じです。

f:id:waenavi:20190902002901j:plain

 

4:0」と入力します。

f:id:waenavi:20190902002954j:plain

 

自動的に右揃えになって「4:00」という表示になります。オートフィルをすると1時間ずつ増えます。

f:id:waenavi:20190902003018j:plain

 

(2)2時間間隔・30分間隔

問題

次のように入力しなさい。

f:id:waenavi:20190902013313j:plain

 

解説

2:00の次に4:00と入力します。

f:id:waenavi:20190902003113j:plain

 

この2つのセルを選択してオートフィルをすると2時間ずつ加算されます。これを繰り返すと0:00に戻ります(後述)。

f:id:waenavi:20190902003141j:plain

 

2:00の次に2:30と入力します。

f:id:waenavi:20190902003233j:plain

 

この2つのセルを選択してオートフィルをすると30分ずつ加算されます。

f:id:waenavi:20190902003254j:plain

 

(3)時刻の表示形式

問題

「12:34:56」と入力して、表示形式をhh時mm分の形式にしなさい。またmm分ss秒の形式にしなさい。

解説

「12:34:56」と入力します。

f:id:waenavi:20190902005918j:plain

 

表示形式をhh時mm分の形式にします。12時34分になります。56秒の部分が非表示になりますが、データとして消えたわけではありません。数式バーを見ると「12:34:56」になっています。

f:id:waenavi:20190902010012j:plain

 

ユーザー定義の表示形式で「mm分ss秒」の形式にします。時間はh、分はm、秒はsで表します。なお、表示形式のmは原則として「月」を表しますが、hの右側またはsの左側にあるmは例外的に「分」を表すものと解釈します。

34分56秒だけになります。この場合も12時間が消えたわけではなく、セルに保存されています。このように時間・分・秒を非表示にすることができます。

f:id:waenavi:20190902010135j:plain

 

3.時刻のシリアル値

問題

「0.5」「0.1」「0.01」と入力して表示形式をhh:mm:ssの時刻の形式にして、なぜこのような表示になるのか考えなさい。また、標準に戻しなさい。 

解説

「0.5」と入力します。これは単なる小数です。数式バーも小数です。

f:id:waenavi:20190902013745j:plain

 

時刻の表示にすると12時です。数式バーも時刻の表示に変わります。

f:id:waenavi:20190902013822j:plain

 

Excelでは1日=24時間を「1」として扱います。日付の場合は西暦1900年1月1日を1日目として、その経過日数をシリアル値として管理します。これによって、1900年以降の日付が整数で表され、その結果、足し算や引き算をするだけで日数の計算をすることができます。

f:id:waenavi:20190902014541j:plain

 

24時間より短い時間の場合は1より小さい小数になります。これもシリアル値といいます。シリアル値が小数の場合、24時間より短い時刻(時間)を表しています

f:id:waenavi:20190902014902j:plain

 

24時間のシリアル値が1なので、12時間のシリアル値は0.5です。つまり、Excelで12:00と入力するのと、小数の0.5を入力するのは同じなのです。6時間はその半分の0.25、3時間はさらにその半分の0.125です。

f:id:waenavi:20190902021228j:plain

 

「0.1」と入力します。

f:id:waenavi:20190902015617j:plain

 

時刻の表示にすると2時間24分0秒です。

f:id:waenavi:20190902015641j:plain

 

1=24時間なので、0.1は24時間の10分の1で、2.4時間となります。2.4時間は2時間と0.4時間を合わせた時間です。小数の「時間」は60倍で分に換算できます。0.4時間は0.4 x 60で、24分に相当します。したがって、シリアル値の0.1は2時間24分になります。

f:id:waenavi:20190902020155j:plain

 

「0.01」と入力します。

f:id:waenavi:20190902020255j:plain

 

時刻の表示にすると0時間14分24秒になります。

f:id:waenavi:20190902020314j:plain

 

1=24時間なので、0.01は24時間の100分の1で、0.24時間となります。0.24時間は0.24 x 60で、14.4分に相当します。小数の「分」は60倍で秒に換算できます。0.4分は0.4 x 60で、24秒に相当します。したがって、シリアル値の0.01は14分24秒になります。

f:id:waenavi:20190902020622j:plain

 

標準の表示形式にすると小数に戻ります。このように、Excelは時刻を小数で管理しています。

f:id:waenavi:20190902020727j:plain

 

4.時刻か文字列か

問題

「4時」「4:00」と入力して「m時s分」の表示形式にしなさい。また、標準の表示形式にしなさい。 

解説

「4時」と入力します。左揃えになります。これは単なる文字列であることを表しています。時刻のシリアル値は保存されていないので、表示形式を変えても表示は変わりません。

f:id:waenavi:20190902020849j:plain

 

「4:00」と入力します。右揃えになります。数式バーが「4:00:00」となっており、Excelが時刻として認識したことが分かります。セルの表示は「4:00」ですが、セルには小数のシリアル値が保存されています。

f:id:waenavi:20190902020944j:plain

 

「m時s分」の表示にすることも可能です。

f:id:waenavi:20190902021021j:plain

 

標準の表示形式にすると小数になります。

f:id:waenavi:20190902021052j:plain

 

5.1を足しても変わらないのはなぜ?

問題

「12:34:56」と入力して「h」の表示形式にしなさい。これに1を加算するといくつになるか。

解説

 「12:34:56」と入力します。

f:id:waenavi:20190902021517j:plain

 

ユーザー定義の表示形式で「h」だけにします。12だけになります。

f:id:waenavi:20190902025716j:plain

 

時間だけの表示にすると、分や秒が表示されませんがデータとして消えたわけではありません。数式バーは「12:34:56」のままです。セルに保存されている値は小数のシリアル値であって、12ではありません。

f:id:waenavi:20190902030115j:plain

 

1を足します。

f:id:waenavi:20190902030204j:plain

 

12のままです。12時34分56秒に、1=24時間を足しても12時34分56秒であることに変わりがないからです。

f:id:waenavi:20190902030251j:plain

 

表示形式を標準にすると1が加算され、1日増えていることが分かりますが、時刻は変わりません。

f:id:waenavi:20190902030441j:plain

 

*補足*1時間を加算するには、1ではなくて"1:00"を足します。

f:id:waenavi:20190902030946j:plain

 

6.24時間以上の時間

問題

0:00をオートフィルすると、なぜ0時に戻るのかを説明しなさい。また、24時間分を表示しなさい。

f:id:waenavi:20190902031117j:plain

 

解説

0:00と入力します。このシリアル値は0です。

f:id:waenavi:20190902031201j:plain

 

オートフィルをすると1時間ずつ増えます。

f:id:waenavi:20190902031247j:plain

 

ところが、23:00の次は0:00になります。この0:00のシリアル値を見てみましょう。

f:id:waenavi:20190902031358j:plain

 

標準の表示形式にします。シリアル値は1です。それ以降もシリアル値が増えています。

f:id:waenavi:20190902031504j:plain

 

時刻の表示形式にします。0時になります。それは、0時に、24時間を足しても0時だからです。しかし、1日=24時間分のシリアル値が増えています。

f:id:waenavi:20190902031618j:plain

 

1日を表示するには、日にちを表す「d」を使います。ユーザー定義の表示形式で「d日h時間」とします。1日と表示することができます。

f:id:waenavi:20190902031754j:plain

 

「hh:mm:ss」のように、時刻の表示形式は年・月・日を非表示にする形式でもあります。時刻の表示形式にすると24時間以上の時間を表すことができません。年・月・日を表示するためにはyyyy、m、dなどの表示形式を使います。

f:id:waenavi:20190902031910j:plain

 

別解

時刻の表示形式のhは、24時間未満の時間を表します。24時間以上の時刻を表示するには[h]とします。表示形式を[h]:mmにします。24時間、25時間、・・・となります。

f:id:waenavi:20190902032048j:plain

 

7.60分以上の分・60秒以上の秒

問題

「0:90」と入力して、90分0秒の表示にしなさい。また、5400秒の表示にしなさい。

解説

0:90と入力します。

f:id:waenavi:20190902032223j:plain

 

0.0625となります。

f:id:waenavi:20190902032258j:plain

 

時刻の表示形式にすると1時間30分になります。0.0625とは1時間半のシリアル値です。

f:id:waenavi:20190902032317j:plain

 

ユーザー定義の表示形式で「m分s秒」とすると30分0秒となってしまいます。時刻の表示形式のmは60分未満の時間を表します。1時間30分の場合、1時間(h)が非表示になって30分0秒だけになってしまいます。

f:id:waenavi:20190902032412j:plain

 

60分以上の分を表すには[m]とします。表示形式を[m]分s秒にします。90分0秒になります。

f:id:waenavi:20190902032516j:plain

 

同様に、60秒以上の秒を表すには[s]とします。表示形式を[s]秒にします。5400秒になります。

f:id:waenavi:20190902032545j:plain

 

8.日付と時刻をあわせて入力した場合のシリアル値

(1)整数部分が日付、小数部分が時刻

問題

「12:00」「2021/11/11 12:00」と入力して標準の表示形式にしなさい。 

解説

「12:00」と入力します。

f:id:waenavi:20190902032703j:plain

 

標準の表示形式にすると0.5となります。これは12時間のシリアル値です。

f:id:waenavi:20190902032736j:plain

 

今度は「2021/11/11 12:00」と入力します。これは2011年11月11日の12時であることを表します。1つのセルに日付と時刻を両方入力するには半角のスペースを入れます。右揃えになりますからシリアル値が保存されているはずです。

f:id:waenavi:20190902033022j:plain

 

標準の表示形式にすると44511.5となります。これは2021年11月11日のシリアル値44511と12時間のシリアル値0.5を足した数値です。

f:id:waenavi:20190902033048j:plain

 

このように日付と時刻を入力した場合は、日付のシリアル値と時刻のシリアル値を加算した数値が保存されます。シリアル値の整数部分が日付を表し、小数部分が時刻を表すと考えてもよいです。

f:id:waenavi:20190902033446j:plain

 

さきほど「12:00」と入力した時のシリアル値は0.5でした。ということは、自動的に日付は補完されないということです。単に時刻だけを入力した場合と、日付+時刻を入力した場合ではシリアル値が異なることに注意します。

f:id:waenavi:20190902033631j:plain

 

(2)時刻の非表示

問題

「2021/11/11 12:00」と入力して曜日を表示しなさい。

解説

日付と時刻を入力します。

f:id:waenavi:20190902033755j:plain

 

表示形式を「aaa」にすると曜日になります。日付も時刻も非表示になりますが消えたわけではありません。このように、時刻を同時に入力しても、日付の表示形式には影響はありません。

f:id:waenavi:20190902033839j:plain

 

(3)日付だけの場合は午前0時を表す

問題

「2011/11/11」と入力して時刻の表示形式にしなさい。

解説

日付を入力します。日付の表示形式になります。

f:id:waenavi:20190902034026j:plain

 

これを時刻の表示形式にすると0時0分0秒となります。

f:id:waenavi:20190902034053j:plain

 

日付のシリアル値は整数なので小数部分はありません。したがって、日付を入力して時刻を省略した場合はその日の午前0時を表します。

f:id:waenavi:20190902034331j:plain

 

(4)時刻が0時とは限らない

問題

セルA1とセルA2に「2020/1/1」と入力されている。

f:id:waenavi:20190902034440j:plain

 

「=A1=A2」と入力したところ「FALSE」となった。考えられる原因を述べなさい。

f:id:waenavi:20190902034512j:plain

 

解説

通常は同じ日付を入力した時は、等号で判定すればTRUEとなります。それは同じシリアル値が入っているからです。

f:id:waenavi:20190902034615j:plain

 

しかし、FALSEになったということは、シリアル値が一致していないということです。そこで、表示形式を標準にしてシリアル値にします。一方は整数で、もう一方は小数になっています。

f:id:waenavi:20190902034716j:plain

 

表示形式によって、小数の部分、つまり時刻の部分を非表示にすることができます。表示上、同じ日付のように見えてもシリアル値が等しいとは限らないので注意が必要です。

f:id:waenavi:20190902034847j:plain

 

9.NOW関数と時刻のショートカットキー

問題

現在の時刻を入力しなさい。また、現在の日付と時刻を入力しなさい。

解説

時計を見ながら時刻を直接入力してもよいですが、Ctrlキーを押しながら:(コロン)を押すと現在の時刻が入ります。直接時刻を入力するのとCtrl+:で入力するのはまったく同じです。時刻だけを入力するので日付は保存されません。

f:id:waenavi:20190902035153j:plain

 

Ctrlキーを押しながら;(セミコロン)を押すと今日の日付が入ります。

f:id:waenavi:20190902035234j:plain

 

スペースを入力して、さらに、Ctrlキーを押しながら:(コロン)を押すと現在の時刻が入ります。これで、日付と時刻が入力できます。日付+時刻のシリアル値が保存されます。

f:id:waenavi:20190902035302j:plain

 

NOW関数は現在の日付と時刻を求める関数ですが、これも日付+時刻のシリアル値です。関数の挿入の画面にも「現在の日付と時刻を表すシリアル値を返します」とあります。

f:id:waenavi:20190902035402j:plain

 

NOW関数には引数がありません。この画面をそのままOKすれば関数が入ります。

f:id:waenavi:20190902035430j:plain

 

「=now()」と直接入力してもかまいません。

f:id:waenavi:20190902035531j:plain

 

これは関数なので再計算されるたびに時刻が変わります。ただし、毎秒で再計算されるわけではなく、セルの入力などの作業をした時に再計算されるため、NOWがタイマーや時計になるわけではありません。

f:id:waenavi:20190902035547j:plain

 

10.日付の判定

(1)以前を判定する

問題

A列に2022年2月1日~10日の日付が入力されている。2月5日までをTRUE、2月6日以降をFALSEと表示しなさい。

f:id:waenavi:20190902035835j:plain

 

誤答例

日付はシリアル値(整数)なので不等号で判定することができます。そこで「<="2022/2/5"*1」として2月5日以下と判定したとします。これは間違いです。

  • =A1<="2022/2/5"*1

f:id:waenavi:20190902040011j:plain

 

"2022/2/5"のように、日付だけを入力して時刻を省略した場合は、午前0時を表します。しかし、2月5日には0時から23時59分59秒まであります。「<="2022/2/5"*1」としてしまうと、2月5日をほとんど含みません。

f:id:waenavi:20190902041141j:plain

 

画面上、日付しか表示されていないからと言って必ずしも午前0時が入力されているとは限りません。時刻が含まれている場合はFALSEになってしまいます。

f:id:waenavi:20190902040420j:plain

 

解説

不等号で「以前」を判定する場合にはイコールを含まない形にします。例えば、「2月5日まで」であれば「2月6日より前」とします。

f:id:waenavi:20190902041708j:plain

 

<"2022/2/6"*1」とすれば、2月6日0時より小さいという意味になりますから、2月5日23時59分59秒まで含まれます。

f:id:waenavi:20190902041606j:plain

 

(2)以降を判定する

問題

A列に2022年2月1日~10日の日付が入力されている。2月6日以降をTRUE、2月5日までをFALSEと表示しなさい。

f:id:waenavi:20190902042116j:plain

 

誤答例

2月6日以降を「>"2022/2/5"*1」として2月5日より大きいと判定したとします。これは間違いです。

f:id:waenavi:20190902042237j:plain

 

これは2月5日午前0時より大きいという意味であり、2月5日午前0時0分1秒以降という意味になります。

f:id:waenavi:20190902042303j:plain

解説

不等号で「以降」を判定する場合にはイコールを含む形にします。例えば、「2月6日以降」であれば「>="2022/2/6"*1」とします。これで、2月6日0時以降という意味になります。

f:id:waenavi:20190902042533j:plain

f:id:waenavi:20190902042610j:plain

 

11.1秒未満の表示

問題

「0.0012」と入力して、時刻の表示にしなさい。また、1秒未満の部分を表示しなさい。 

解説

「0.0012」と入力します。

f:id:waenavi:20190902042831j:plain

 

時刻の表示にすると1分44秒となります。

f:id:waenavi:20190902052647j:plain

 

シリアル値の1は24時間なので、シリアル値の0.0012は、24時間x0.0012=0.0288時間となります。時間を3600倍すると秒になります。したがって、0.0264x3600=103.68秒、つまり1分43秒68となります。

f:id:waenavi:20190902052547j:plain

 

最後の「68」が表示されていません。四捨五入で1分44秒になります。

f:id:waenavi:20190902052747j:plain

 

h:mm:ssの形式では1秒未満が表示されません。そこで、ユーザー定義で「h:mm:ss.00」とします。0時間1分43秒68となります。

f:id:waenavi:20190902052838j:plain

 

ユーザー定義で「.0」「.00」「.000」をつけると1秒未満の時間を1000分の1秒まで表示できます。表示されない部分は四捨五入となります。

f:id:waenavi:20190902052958j:plain

 


解説は以上です。時刻の足し算引き算の解説へつづく・・・


 

 


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