ブログトップ

カクレ理系のやぶにらみ

tamm.exblog.jp

時間のある方はお読みください。軽い気持ちで読み始めると頭が痛くなります。

2011年 09月 26日 ( 1 )

 Excel では日付を1900年1月1日を1とするシリアル値(連番)として扱います。これは、1900年以降であればある特定の1日に対し、絶対に他の日付とダブらないひとつの数値が割り当てられているということを意味します。したがって、Excelでは日付として表示されていても、実際には数値がデータとして格納されていることになります。このことにより、日付は足したり引いたりして計算ができるようになっています。
 ちなみに、1900年1月1日よりも前の日付は単なる文字列として扱われますから、これを計算対象にすることはできません。

 さて、日付計算には大雑把に分けて次の2種類があります。

1)ある日付を基準にしてほかの日付を計算する。

(計算例)
・今日から6ヶ月後の日付を計算する。
・今日から2年間毎月末日の日付を計算する。

2)2つの日付の期間を計算する。

(計算例)
・自分が生まれた日から今日までの年数と月数、日数を計算する。
・今度の自分の誕生日まであと何日あるかを計算する。

 これらの計算をするために覚えておくべき関数は次の6種類です。逆に言えば、これら6種類の関数さえ覚えておけば、たいていの日付計算が可能になるということです。

1)TODAY()
 今日の日付を求める関数。計算結果はシリアル値となり、日付で表示されます。引数はありません。

2)DATE(年,月,日)
 日付に対応したシリアル値を求める関数。計算結果は日付として表示されます。また、引数には数値を用います。Date関数は以下に述べるYear関数・Month関数・Day関数とセットで使います。

3)YEAR(シリアル値)
 表示されている日付から年を取り出す関数です。結果は数値となります。

4)MONTH(シリアル値)
 表示されている日付から月を取り出す関数です。結果は数値となります。

5)DAY(シリアル値)
 表示されている日付から日を取り出す関数です。結果は数値になります。

 YEAR関数、Month関数、DAY関数はそれぞれDATE関数の引数として使うことができるので、これら4つの関数はセットで使うためにあると考えてよいと思います。

6)Datedif(開始日,終了日,集計単位)
 2つの日付の期間を計算する関数です。期間を集計する単位は次の通りであり、数式を入力する際は文字列であることを示すために ” ”で囲まなければなりません。

Y  期間内の満年数(1年未満の端数は切捨て)
M  期間内の満月数(1か月に満たない端数は切捨て)
D  期間内の満日数(切捨ては行いません)
MD 1ヶ月未満の日数(Mで切捨てられた日数に相当します)
YM 1年未満の月数(Yで切捨てられた月数に相当します。なお、日数は切捨てします)
YD 1年未満の日数(YMで切り捨てられた日数に相当します)



(計算例1)今日から6ヶ月後の日付を計算する
 今日から6ヶ月後というのは、今月に6を足せば導くことができます。ただし、6ヶ月後というのですから、日にちは1日だけ遡る必要があります。


c0136904_2118171.jpg



計算式を表示させるとこうなります。
c0136904_21185488.jpg



 セルH3には今日の日付が入っているわけですから、Month(H3)+6 というのは今月から6ヶ月後の月を求める計算式となり、Day(H3)-1 というのは今日の日付の1日前の日付を求める式となります。(セルH3の日付を変えれば、H4の計算結果も変わります。)


(計算例2)今日から2年間毎月末日の日付を計算する。
 月末の日付というのは、月によって30日になったり31日になったり、さらに2月の場合は28日になることもあれば29日になることもあって非常に複雑です。しかし、見方を変えると月末の日付の翌日は必ず1日になるのですから、月末の日付=翌月の1日-1という計算が成り立つことがわかります。


c0136904_21193675.jpg



これも計算式を表示させるとこうなります。
c0136904_21201120.jpg





 ここで、翌月末日というのは翌々月の1日-1のことですから、

=DATE(YEAR(C5),MONTH(C5)+2,1)

 これは翌々月の1日を求める式であり、そこから1日遡ることによって、翌月の末日の日付を求めることができるようになります。

=DATE(YEAR(C5),MONTH(C5)+2,1) -1


(計算例)自分が生まれた日から今日までの年数と月数、日数を計算する。
 期間を求める計算はDatedif関数を使います。


c0136904_21205073.jpg




 セルD11には次の計算式が入っています。

=DATEDIF(C3,C5,"Y")

 また、セルF11には次の計算式が入っています。

=DATEDIF(C3,C5,"YM")

 同様に、セルH11には次の計算式が入っています。

=DATEDIF(C3,C5,"MD")

 なお、集計単位YM、MDを使わない場合、次のように補助計算のためのセルを用いれば同じ結果を求めることができます。


c0136904_21212532.jpg




 ここでセルC8には、誕生日から満年齢数を経過した年(すなわち今年)の誕生日の日付を求める数式が入っています。この日付と今日の日付の期間月数を求めたものがセルF11に入っています。
 また、セルC9には、今年の誕生日からセルF11で求めた月数を経過した月(すなわち今月)の日付を求める計算式が入っているので、今日の日付までの期間日数を求める数式がセルH11に入っています。


(計算例)今度の自分の誕生日まであと何日あるかを計算する。


c0136904_2127559.jpg



 これは今までの応用です。今度の誕生日というのは生年月日から満年齢経過した次の年の誕生日のことですから、

 YEAR(自分が生まれた年月日)+自分の満年齢+1 で次の誕生日の年が何年になるかを求めることができます。(今年の誕生日が過ぎていれば来年になりますし、まだ誕生日が来ていなければ今年になります。)
 あとはDATE関数でその日付を求めたうえで、DATEIF関数で今日の日付を開始日とした期間日数を求めればよいのです。


c0136904_21222887.jpg

by T_am | 2011-09-26 21:27 | Excel のあの手この手