Excel。paymentday。10日締めの翌10日後払い。
日程表は今までのスキル総決算！
WORKDAY関数とDATE関数とEOMONTH関数
現場といいますか、会社の取引や支払は様々なケースがありますよね。前回まではまだ支払までの日数があったりしましたが、
まだ取引先様とのやり取りが少ない等々、支払い日数が短いパターンというのがありまして、
今回は、土日祝祭日に完全対応した支払予定日の日程表を
作成してみようというシリーズの最終回。
今回は、
【10日締めの翌10日後払い】
をご紹介していきます。もし、翌5日後払いでも、
この後紹介していきます日程表の作り方をアレンジしてもらえれば対応可能です。
この手の日程表の作成は、ビジネス実践向けのマンツーマン講座で紹介をしておりますが、
様々な日付関係の関数が登場しますので、作れるようになるとExcel力もアップしますよ。
さて、毎回ご紹介しておりますが、
まずは祝祭日の一覧表を作っておきませんと避けることが出来ませんので、
確認しておきましょう。
そして、日程表のシートは、フレームを作成してあります。
A2には、2015と入力しており、2015年と表示するために、
前回同様にユーザー定義を使った表示形式で、2015年と表示しております。
B2には、A2と同様に、1と入力して、ユーザー定義を使って表示形式で1月と表示させています。
そして、左揃えの設定もしております。
確認の為、ユーザー定義の表示形式のダイアログボックスを表示しておきます。
日程表のA列も確認しておきます。
A5:A7までには1と10と20が入力されており、ここも日をつけて表示したいので、
表示形式のユーザー定義で表示を変えております。
A8には、末日と入力しております。末日は月によって28/29/30/31と変化しますので、
数値として入力することが出来ません。
では、B列の締日から作成してきますので、B5をクリックし、
DATE関数のダイアログボックスを表示しましょう。
ここには、DATE関数を使って算出していきますが、
末日のB8はEOMONTH関数を使っていきますので、順を追って説明していきます。
年には、A2をクリックして、絶対参照を設定しますので、$A$2とします。
オートフィルでB7までコピーをするために絶対参照を設定します。
月には、B2をクリックして、こちらも、絶対参照を設定しますので、$B$2。
そして、締日がそれぞれ異なりますので、
日には、A5を入力します。あとはOKボタンをクリックします。
オートフィルハンドルを使ってB7までコピーします。
面倒なのは、B8の末日。これは、EOMONTH関数を使わないといけませんので、
B8をクリックして、EOMONTH関数のダイアログボックスを表示しましょう。
先に、以前ご紹介しましたように月には0（ゼロ）を入力しておきます。
そして、開始日には、DATE関数をネストで入れていきますので、
開始日のボックスをクリックしてDATE関数のダイアログボックスを表示します。
年には、A2を入力。
月には、B2を入力。
日には、1を入力します。その月のどの日にちでもOKですので、
取りあえず1で入力しておくといいでしょう。
これで、OKボタンをクリックすると、末日も完成します。
ついでに、C列の曜日も以前紹介しておりますようにTEXT関数を使用して作成しておきます。
C5には、
=TEXT(B5,"aaa")
というTEXT関数を作成しておきましょう。
続いてD列の翌10日後払いを作っていきますが、ここが面倒なことになります。
オートフィルハンドルを使ってコピーという訳にはいかないからです。
1つずつ、数式の作り方が変わりますので、一つずつ確認していきましょう。
ですので、この【10日締めの翌10日後払い】は、総決算。スキル上達間違いなし。
それでは、
D5から確認しますので、DATE関数のダイアログボックスを表示します。
年には、A2をクリックして、絶対参照を設定しますので、$A$2。
月には、B2をクリックして、絶対参照を設定しますので、$B$2。
日には、10日になりますので、10と入力します。
OKボタンをクリックして、10日が完成しました。これを下のD6にコピーします。
続いて、D6をクリックして、20日を作っていきます。
これは、日のところだけ修正するだけですので、日を20と修正すれば完成します。
修正後OKボタンをクリックします。
これで、20日も完成しました。
続いていきましょう。D7には、20日の10日後なので、30日。
ならばいいのですが、だいたいケースとしては末日になることが多いので、
末日になるのでしたら、EOMONTH関数を使う必要が出てきますので、
EOMONTH関数のダイアログボックスを表示します。
開始日には、B7をクリックします。
月には当月の末日になりますので、0（ゼロ）と入力してOKボタンをクリックします。
そして、最後。末日が締日だったものは、翌月の10日になりますので、
DATE関数で数式を作成していきますので、DATE関数のダイアログボックスを表示しましょう。
年には、A2を入力します。
さきに、日には、10と入力します。
月には、締日の翌月になりますので、MONTH関数を使いますので、
MONTH関数のダイアログボックスを表示しましょう。
シリアル値には、B8をクリックして、OKボタンをクリックしましょう。
これで、D列の翌10日後支払も完成しましたので、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ボタンをクリックして、オートフィルハンドルを使って算出して、曜日も算出しておきます。これで完成しましたね。
このように、様々な支払パターンが存在しますので、試してアレンジしてみてはいかがでしょうか？
