実務で使えるExcel入門セミナー  Excel上の日付と時刻のデータと関数の使い方

シェアする

  • このエントリーをはてなブックマークに追加

Excelでは、数値だけでなく日付や時刻も扱えて計算することもできます。日付や時刻のデータは数値とは違う特長がありますので、その違いを押さえておくと、スムーズに処理することができます。

スポンサーリンク
自動サイズレスボンシブ

日付、時刻データを扱うポイントはシリアル値

Excelでは、スラッシュ/ )で「年・月・日」に区切ることで、日付データとして認識されます。


たとえば、今日の日付「2015年4月27日」なら、

スクリーンショット 2015 04 27 10 10 06


こうですね。ここから、セルの書式設定で形式を変えて、

スクリーンショット 2015 04 27 10 12 25


こんな表示に変えることもできます。


また、時刻の場合は、コロン : )で、「時間・分・秒」を区切ります。


たとえば、11時25分30秒なら、

スクリーンショット 2015 04 27 14 37 55


こうですね。 
日付と同様に、セルの書式設定を使えば、

スクリーンショット 2015 04 27 14 44 26

このような表示も可能です。


日付ならスラッシュ( / )、時刻ならコロン( : )で区切れば、Excel上「日付のデータ」「時刻のデータ」として認識させることがでるので、あとは数値と同じように扱って、日付同士、時刻同士の差分を、引き算で計算することもできます。

スクリーンショット 2015 04 27 15 17 21
(「日付」の方は日数で、「時刻」の方は時間数、分数、秒数で差分を表示)


このような、引き算で計算ができるのは、Excel上で日付や時刻を識別するためのシリアル値を設定しているから。


表示では日付や時刻になっていても、データの上ではシリアル値という”数値”として認識されているから、計算も可能になるのです。


「シリアル値」については、もう少し詳しく説明しておきます。

日付のシリアル値

限定数が決まっているブランドの商品や、生産台数が決まっている車などで、個々の商品や車両を識別するために割り振られる「通し番号」のことを”シリアルナンバー”と言います。


Excelで使われる”シリアル値”もこれと同じ考え方で、日付や時刻に「通し番号」をつけて、それぞれの日付や時刻を特定できるようにしているのです。


つまり、表示上は「2015年4月27日」といった日付や「15時42分」といった時刻になっていますが、データ上は数値(シリアル値)が入力されているのです。


日付については、1900年の1月1日が基準になります。


1900年の1月1日のシリアル値が「1」で、あとは1日に1ずつ割り振られるので、以下、


1900年1月2日 : 「2」 
1900年1月3日 : 「3」 
1900年1月4日 : 「4」 
     ・ 
     ・ 
     ・ 
…と続き、今日の日付2015年4月27日は「42121」になります。


このように日付のシリアル値は決まります。

時刻のシリアル値

時刻のシリアル値は、深夜0時が基準になってシリアル値が「1」


「その時間、が何日分にあたるか」


でシリアル値がきまります。


深夜0時はちょうど丸1日が経過した時間(24時間)ということで、「1」が割り振られます。


では、深夜1時ならどうでしょう。1日のうちの1時間分しか経過していませんから、「1÷24」日分ということになります。


ですので、

スクリーンショット 2015 04 27 15 53 34


0.04166がシリアル値になります。


さらに1分ならどうでしょう。


1分が何日分にあたるかは、まずは、1分が何時間分になるかを計算して(1÷60(分))、


時間にした(1÷60(分))を、何日分にあたるかを計算するために24で割ります。


つまり、

スクリーンショット 2015 04 27 16 08 00


これが、1分に割り振られたシリアル値です。


同様に、1秒についても、

スクリーンショット 2015 04 27 16 17 03


このようにして計算されます。

実務におけるシリアル値のとらえかた

日付、時刻についてのシリアル値の決まり方は上述のようになっていますが、実務においてどのように理解しておくかについても補足しておきます。


実務上は、上に出てきたようなシリアル値を、実際に計算するようなことはので、詳しい算出の方法などは知る必要はありません。


知っておいていただきたいのは、日付、時刻の表示とデータの関係だです。


つまり、


・日付、時刻のデータはシリアル値という整数日付)と小数時刻)のこと


・日付、時刻の表示は、表示形式を指定してシリアル値を変換しているだけ


この2点が分かっていればOKです。


この2つが分かっていれば、「日付のつもりで入力したら整数が表示された」というエラーが起こっても簡単に修正することが可能ですし、どのようにすれば差分の計算が可能になるかも、自力で理解できるようになるからです。

日付、時刻の関数

Excel上の日付、時刻のデータは、シリアル値として割り振られた、整数と小数であることが分かりました。


それぞれの日付、時刻に割り振られているシリアル値をデータとして入力するれば、あとは数値と同様に処理ができる(差分の計算や関数への組み込みなど)ということです。


ですので、日付、時刻についての関数については、シリアル値に直すための関数をしっておくのがポイントです。

DATE関数 日付をシリアル値に変換します

まずは、日付をシリアル値に変換する「DATE関数」です。


=DATE(①年, ②月 ,③日)


と日付を順番に入力するだけです。

スクリーンショット 2015 04 27 16 33 39

スクリーンショット 2015 04 27 16 33 55


このように表示されます。 
通常、DATE関数を使うと表示形式が自動で日付に切り替わるので、この形になるのですが、データとしてはシリアル値の「42121」が入力されています。

TIME関数 時刻をシリアル値に変換する

次は時刻をシリアル値に変換する関数です。「TIME関数」を使います。


=TIME(①時間, ②分, ③秒)


順番に時間、分、秒と入力すれば、シリアル値に変換してくれます。

スクリーンショット 2015 04 27 16 39 07

スクリーンショット 2015 04 27 16 39 18


こちらも、TIME関数を使うとセルの表示形式が自動的に時刻に切り替わるので、この形になっていますが、データとしてはシリアル値である「0.59831」が入力されています。

DATEDIF関数

日付について、もう一つ関数を知っておきましょう。 
日付同士の期間を求める関数、DATEDIF関数です。


これまで説明してきたとおり、シリアル値は数値ですので引き算することで、期間を計算することができます。


それでも十分なのですが、このDATEDIF関数は、期間を計算するだけでなく、その計算結果の表示方法を指定できるところに特長があります。


=DATEDIF(①開始, ②終了, ③表示形式)


順番にこの3つを入力していきます。


たとえば、2001年8月3日から2015年4月27日までの期間を計算する場合、B23セルに「2001/8/2」、C23セルに「2015/4/27」と入力しているとすると、

スクリーンショット 2015 04 27 16 46 39


このように関数が入力された結果、

スクリーンショット 2015 04 27 16 49 04


「13」と表示されます。


最後の表示形式に注目してもらいたいのですが、”Y”となっています。 
これは、「期間を年数で表示せよ」と言う指示です。


他に、”M”なら

スクリーンショット 2015 04 27 16 52 29


月数になりますし、”D”なら、


スクリーンショット 2015 04 27 16 53 03


日数になります。 
さらに、”YM”とすると、

スクリーンショット 2015 04 27 16 53 26


年数以下の月数(「13年8ヶ月」なら「8」)が表示され、”MD”にすると、

スクリーンショット 2015 04 27 16 53 45


月数以下の日数(「13年8ヶ月24日」なら「24」)が表示されます。


もし、引き算で期間を求めるとすると、期間は日数で表示されるので、それ以外の方法で表示させた対場合は、DATEDIF関数が便利です。

まとめ

Excelの日付、時刻のデータはシリアル値で入力され、表示形式を指定することで、日付、時刻の形で表示されます。 
シリアル値を使えば、数値と同様に計算も可能なので、日付、時刻をシリアル値に変換する関数を知っておくと便利です。

おまけ

「今日はコレしかない」ということで、夏全開のプレイリストで、ランに出かけました。
暑いけど、気持ち良すぎました。

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする