Excel(エクセル)請求書締め日を簡単入力|EOMONTH関数

サンプルファイルのダウンロード

例題のサンプルファイルは、コチラをご活用ください。

EOMONTH関数

締め日を考える時に使う関数は､月末の日付を求めるEOMONTH関数（イーオーマンス）です｡
EOMONTH関数の書式は次の通りです｡

=EOMONTH(日付,何か月後)
今日の日付に対する月末は次の計算式で求められます｡

=EOMONTH(TODAY(),0)
｢今日に対して､0か月後の月末｣という意味です｡
月末が求まるということは､その1日後は翌月の月初になります｡
次の計算式を使えば翌月の1日を求めることができます｡

=EOMONTH(TODAY(),0)+1
+1は次の日､という意味です｡
Excelは1日を1で表わすことができます｡
つまり⁺20すれば翌月20日ということになります｡

=EOMONTH(TODAY(),0)+20
当月20日は先月の末日に+20します｡
先月は何か月後の月数を-1にすることで求めることができるので､当月の20日は計算式です｡
=EOMONTH(TODAY(),-1)+20

締め日を計算してみる

サンプルファイルの「締め日25日締め」シートをご覧ください。
次のような納品の一覧表があります。

セルB2の納品日の日付から、この一覧表の締め日の欄の計算式を作成してみましょう。
締め日は毎月25日です。
セルF2に、先ほどのEOMONTH関数を入れ、コピーしてみます。
=EOMONTH(B2,-1)+25

本来は11月25日を過ぎたら12月25日の締め日になって欲しかったのですが、11月の納品日は全て11月25日の締め日になってしまいました。

EOMONTH関数は、指定した日付の月末を出すので、11月になってしまったのです。
もしも11月25日以降なら、何か月後の-1を0にする、という判定が必要です。
もしもどうだったら、という判定をする関数がIF関数です。

IF関数の書式は次のとおりです。
=IF(どこがどうだったら,成立している場合,成立していない場合)

今回は、
セルB2の日が25日を超えていたら、0、
そうではなければ-1にするということになります。
日を、年と月の要素を省き、日のみの25日と計算しなければなりません。

そこで使うのはDAY関数で、日付から日の要素のみを取り出し数値に変換する関数です。
この場合のIF関数とDAY関数の組み合わせの計算式は次の通りです。
IF(DAY(B2)>25,0,-1)

この計算式をEOMONTHの中に組み入れます。
=EOMONTH(B2,IF(DAY(B2)>25,0,-1))+25

もう一度日本語で考えると、次のようになります。
もしも、セルB2の数値が25日を過ぎていたら当月末、過ぎていなければ先月末の日付の25日後
つまり、25日を過ぎていなければ当月の25日、過ぎていたら翌月の25日になるのです。
ではセルF2に入力し、下にコピーしてみましょう。

取引先ごとに設定された締め日に対応する

上記の例では、締め日が25日締めと決まっている場合でしたが、取引先ごとに設定された締め日に合わせるというケースもあるかもしれません。
その場合は次のように考えていきます。

サンプルファイルの「締め日請求先に合わせる」シートをご覧ください。
まず納品先ごとに締め日をI列から一覧表にし、納品日一覧表にも取引先ごとの締め日が何日か表す欄をF列に設けます。
月末日締めの場合、毎月の月末の日は31日、30日、29日、28日と色々なケースがあるので「末」とだけ記入します。
(サンプルファイルにはすでに入力されています)

ではF列の締め欄に入力する計算式を説明します。
ここは、他の表から連携して値を持ってくるということになります。
それをする関数がVLOOKUP関数です。

VLOOKUP関数の書式は次の通りです。
=VLOOKUP(探す値,探される範囲,出したいのは探される範囲の何番目か,完全一致で探すか近似値で探すか)
セルF2に入力される計算式は、セルC2の値を、I2からJ4までの範囲で探し、その範囲内の2列目を出します。探し方は完全一致です。

これをVLOOKUP関数にすると次のような計算式になります。
=VLOOKUP(C2,$I$2:$J$4,2,FALSE)

セルC2の値を探します。
I2からJ4までの範囲で探します。
ここに$が付いていますが、これは下にコピーした時でも絶対にこの範囲を指定したい絶対参照にするためです。
列番号は範囲内の2列目です。
最後、今回は完全一致で探すのでFALSEと書きました。
もし探す値が数値で一致するものをその数値よりも小さい範囲で最も近い値を探す場合はTRUEと書きます。

ではこの計算式をセルF2に入力し、下にコピーしてみましょう。
それぞれの取引先ごとの締めの日付が表示されました。
ここは日数の数値が求まるので、もしも数値ではなく日付で表示されていたら標準の表示形式にします。

ではセルG2の締め日を計算します。
ポイントになってくるのは「末」と表示された締めの場合の対処です。
「末」で表示されていれば、 何か月後の指定は0になります。そうでなければ-1になります。
EOMONTH関数で求めた月末に足す日数も、「末」の場合は0日、そうではない場合はそこに表示された締めの日数になります。

先ほどの計算式を思い出してみると次のようになります。
この赤文字の部分を「末」に対応した計算式に変更すれば良いのです。
=EOMONTH(B2,IF(DAY(B2)>25,0,-1))+25

まずIF関数で判定している25日より後かどうかで0か1か判定しているところですが、この外側にセルF2が「末」ならば0とするIF関数を作成します。

また25日と指定しているところは締めの日を指定するので、 セルF2を指定します。
「末」はセル参照でもなく数値でもなく、文字列ですので” ”で囲みます。

IF(F2=”末”,0,IF(DAY(B2)>F2,0,-1))
もしもセルF2が「末」ならば0、そうでなくてもしB2の日付がセルF2の数値以降ならば0、そうでなければ-1という日本語訳になります。

最後の25日を足している部分もIF関数で、「末」なら0、そうでなければセルF2の締めの数値とします。

IF(F2=”末”,0,F2)
全部合わせて、セルG2へ入力する計算式は次のようになります。

=EOMONTH(B2,IF(F2=”末”,0,IF(DAY(B2)>F2,0,-1)))+IF(F2=”末”,0,F2)
入力してコピーしましょう。

サンプルファイルのダウンロード

例題のサンプルファイル完成版は、コチラです。

まとめ

今回は請求書に使う、納品日から締め日を求める計算式を紹介しました。
締め日を求める上で必要な関数は月末を求めるEOMONTH関数です。締め日が毎月末であれば、EOMONTH関数だけで求めることができます。しかし月末ではない日付の場合は、その日付を納品日が過ぎているかどうかで翌月かどうか変わるようにしなければなりませんでした。そのために年月日のデータから日要素だけを取り出すDAY関数が必要でした。
またそのDAY関数で求めたもので判定するためにIF関数を使いました。
もしも請求先ごとに締め日が違う場合は、他に一覧表を用意して、その一覧表から請求先ごとに締め日を調べるためにVLOOKUP関数が必要でした。
このような複数の種類の関数を組み合わせることによって、一つの関数では実現が難しい計算も出来るようになります。
関数を組み合わせるのは少し難しいですが、応用範囲が広いので練習して使えるようになりましょう。