日付計算の関数

エクセルには、日付の計算をする便利な関数が用意されています。これらの中には、標準ではインストールされない関数もあります。
NAMEなどのエラーが出る場合は、オプションの分析ツールをインストールし、更にアドインで設定してください。
 *日割り計算を追加しました。 2010/4/10

★1年前・1ヶ月前

A1に日付が入っているとして、この1ヶ月前や1年前の日付を求めるには、DATE・YEAR・MONTH・DAY関数を使います。YEAR(A1)−1で1年前の年になるので、=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))で、1年前の日付が作れます。同様に、MONTH(A1)−1とすれば、1ヶ月前になります。

また、EDATE関数を使えば、=EDATE(A1、−12)で12ヶ月前(1年前)になります。

★年齢を計算する

TODAY()で本日の日付が出せます。これから、生年月日を引けば日数(日齢)が求まりますが、これを365で割っても、閏年の関係から正確な年齢にはなりません。

このようなとき、DATEDIF関数を使うと便利です。

=DATEDIF(A1,TODAY(),"y")    ・・A1に生年月日があるとして
 で年齢が計算されます。

=DATEDIF(A1,TODAY(),"ym")   とすると、何歳何ヶ月の月数の部分が計算されます。

★月末を求める関数 EOMONTH

=EOMONTH(A1,n) で、A1にある日付のnヵ月後の月末の日付を求めることができます。
例えば、A1に3月1日があるとすると、nに0を指定すると当月の月末3月31日が、1を指定すると翌月の月末4月30日が得られます。nに−1を入れれば、前月の月末になります。
この関数は、「分析ツール・アドイン」を設定していないと使えません。nameエラーになるときは、ツールメニューのアドインを開き、分析ツールにチェックを入れてOKします。この後で、インストールが始まりますが、CDを要求される場合もあります。

★追加の関数を使えるようにするには

 

ツールメニューを開き、アドインをクリックします。一覧から、分析ツールにチェックを入れます。

★営業日数を求める  NETWORKDAYS 関数

開始日と終了日の間の、土日・祭日を除く日数を求めます。この場合、祭日は地域によって異なるので、日付データを入れておく必要がありますが、範囲の指定を省略することもできます。下の例は、E2:E10に祭日(日付のシリアル値)のリストを置いたものです。*この関数も、上のようにアドイン・分析ツールが必要です。

 

★料金の日割り計算をする

1か月分の料金を、日割りで計算する場合、必要なものは 基点となる日付・その月の月末までの日数・1か月分の料金です。

1か月分の料金をA2に入れ、基点となる日付をB2に入れると、残りは自動で計算するようにします。

サンプルファイルをダウンロードするには ここを右クリックしてください。

★バイオリズムグラフ

バイオリズムは、最近は流行りませんが、仕事や車の運転などのミスや事故を防ぐために、今も特定の職場では活用されています。 
身体は23日周期・感情は28日周期・知性は33日周期で好・不調がサインカーブで繰り返され、0を通過するときに事故が起きるとされています。 
生年月日を入れて、今日をはさむ1週間のバイオリズムを計算し、これをグラフに表してみましょう。エクセルの関数やグラフの、良い練習教材になります。

A3に生年月日を入れます。 
B3には、=TODAY() と入れて、今日の日付が自動ではいる関数を入れます。

TODAY関数は、()に入れる引数はないのですが、関数なので=と()は省略できません。

A4から縦に、23・28・33の定数を入れ、そのタイトルとして、B4から、身体・感情・知性の項目を入れます。

これは、後でグラフの項目名になるもので、グラフの範囲に入れたいので、このように定数の右に入れます。

 

C3には、=B3 - 3 を、D3には、=C3 +  1 を入れます。自動で書式は日付になりますが、こちらは年は不要なので、セルの書式を○月○日に変えておきます。C3には、3日前の日付が入り、その後は1日ごとに進みます。

D3に書いた関数は、そのまま右へドラッグして、「以下同様」の関数を入れます。 
関数を使うポイントは、このようにドラッグで書き加えていくことです。そのために、書き方を工夫します。ドラッグでコピー・貼り付けしたときに、うまく座標が変わるように工夫します。

D3の =C3 + 1 の式は、となり+1なので、そのまま+十字をI3までドラッグするだけです。

バイオリズムを計算する式を、C4に入れます。この式は、C4からI6までコピーできるように、座標を一部固定しました。

分かりにくい人は、下の式をコピーして、C4の数式バーに貼り付けて、エンターキィで抜けてください。

=SIN(2*PI()*MOD(C$3-$A$3,$A4)/$A4

これを、折れ線グラフにします。

グラフを作る範囲は、B3からI6です。B3には、=TODAY()という関数が入っていますが、ここが項目の交差点なので、グラフ範囲のスタートになります。これで、日付と身体・感情・知性の3本の折れ線グラフを作ります。

挿入の、折れ線グラフを選び、マーカーつき折れ線を選びます。

あっという間に、グラフが出来ます。

バイオリズムは、それぞれの周期で-1から+1までを変化するサインカーブです。それぞれの線が、0を通過するときが要注意の日です。それが分かりやすいように、オリジナルの危険指数グラフを追加してみました。

B7に 危険指数 という項目を書いて、 C7に以下の関数を書いて、これをI列までコピーします。

=1-2*SQTR(ABS(C4*C5*C6))  見やすいように太字にしました。$で固定するものはありません。 

3つの値を掛け算して、その値の絶対値を取り、平方根を求めて、0に近い数値が危険指数を大きくするように作った関数です。危険指数が0.8を超える日は、事故やミスに注意してください。

グラフ範囲を選択し、この表範囲を右下のコーナーをドラッグして広げます。

これで、グラフが自動的に追加されます。

もう少し詳しい説明は、 ブログに載せました
完成品のexcelファイルは、ここからダウンロードできます。

メニューへもどる