Excel。paymentday。末締めの翌20日払い。
土日祝祭日に完全対応の日程表を作成してみる。
WORKDAY関数とEOMONTH関数
前回に引き続き、土日祝祭日に完全対応した支払予定日の日程表を作成してみようというシリーズの今回は第2弾。
今回は、【末締めの翌20日払い】をご紹介していきます。
企業研修やマンツーマン研修でも支払の日程表作成の講座は人気があるんですよ。
まずは、別シートに祝祭日の一覧表を作成しておきましたね。
この祝祭日の一覧表がありませんと、判別できませんので必ず必要になります。
では、【末締め翌20日払い】の日程表を作成してくことにします。
下記のような表があります。
確認するのは、C2に2015と入力されていますが、
これは、ユーザー定義の表示形式を使って、2015年と表示させていますね。
前回詳細な作り方はご説明しておりますが、
再度ユーザー定義の表示形式のダイアログボックスを表示して確認しておきましょう。
種類には、0”年”
と設定されていますね。これで、2015年と表示することが出来るわけです。
そして、A列には、1～12までの数字が入力されています。月というわけですね。
これで、準備が完了しました。ので、
B5をクリックして、末日を作っていきます。末日を作るためには、
EOMONTH関数を使うのでしたね。
では、EOMONTH関数のダイアログボックスを表示しましょう。
開始日には、DATE関数をネストで作っていきますので、
先に月に0を入力しておきます。
これは、当月なので、0（ゼロ）を入力してきます。
では、開始日のボックスをクリックして、DATE関数のダイアログボックスを表示しましょう。
名前ボックスの▼から、ネストの関数を選択しましょう。
年には、2015が入力されているC2。
そしてオートフィルハンドルでコピーしますので絶対参照を設定しますので、$C$2。
月はA5を入力しましょう。
日には、1を入力します。
あとは、OKボタンをクリックすると、末日が完成しますので、オートフィルハンドルを使って、
12月まで数式をコピーしておきましょう。
またC列の曜日も前回同様にTEXT関数を使用して、C5:C16まで作成しておきます。
C5の数式は、
=TEXT(B5,"aaa")
さて次は、D列の翌20日を作っていきましょう。
ここは、DATE関数を使用しますので、DATE関数のダイアログボックスを表示しましょう。
年には、$C$2。これは2015が入っているセルですね。
月は、翌月としますので、MONTH関数をネストで入力しますので、後程。
日には、20日ですので、20と入力しましょう。
では、月をクリックして、MONTH関数のダイアログボックスを表示しましょう。
そして、MONTH関数のシリアル値には、B5をクリックします。
OKボタンをクリックすると、数式が完成します。
=DATE($C$2,MONTH(B5),20)
これでは、同月の20日が表示されていますので、
MONTH(B5)に＋1する必要がありますので、数式は、
=DATE($C$2,MONTH(B5)+1,20)
とすれば、翌月20日が作成することが出来ましたね。
オートフィルハンドルで数式をD16までコピーして、
E列の曜日も先ほどと同じように作成してコピーしておきましょう。
まず、ここまで作成できましたね。
F列からI列までは、前回ご紹介したのと同じになります。
繰り返しになりますが、簡単に記載しておきます。
まず、WORKDAY関数を作っていきます。
WORKDAY関数は、土日を除いた日を指定された日数で算出、
しかも指定された日がある場合には、それを除いてくれるという関数なのです。
では、F5をクリックして、WORKDAY関数のダイアログボックスを表示しましょう。
開始日には、翌末払いのD5をクリックして、+1します。
日数には、-1と入力します。祭日は、祝日一覧のシートに移動して、範囲選択をしますので、
祝日一覧!$A$2:$A$18と入力します。ここも絶対参照を設定しておきましょう。
オートフィルハンドルを使って12月まで算出して、ついでにお隣の曜日も算出しておきましょう。
これで、支払日が前日の場合が算出できました。
今度は支払日の翌日だった場合を算出してきましょう。
H5をクリックして、今回もWORKDAY関数を使いますので、
WORKDAY関数のダイアログボックスを表示しましょう。
開始日は、D5-1と入力します。
日数には、1と入力します。
祭日は、先ほどと同じ、祝日一覧!$A$2:$A$18と入力します。
絶対参照も忘れないようにしましょう。
では、OKボタンをクリックして、オートフィルハンドルを使って算出して、
曜日も算出しておきます。
これで完成しましたね。
