Excel。paymentday。末締めの翌末払い。
土日祝祭日に完全対応の日程表を作成してみる。
WORKDAY関数とEOMONTH関数
企業研修や、マンツーマン研修をはじめ、支払日関係でのご質問が多々あります。先日もご質問がありました。
そこで、今回から4回にわたって、
様々な支払い条件に基づいた日程表の作成方法をご紹介していきたいと思います。
まず今回は、定番中の定番。
【末締めの翌末払い】
しかも、土日祝祭日に完全対応した日程表を作ってみたいと思います。
会計経理が詳しくない方、主に学生さんにはなじみが薄いので、
【末締めの翌末払い】というのをご説明しますと、
バイトで月末に精算したら、入金されるのが翌月の末日というのが、
【末締めの翌末払い】ということになります。
ただ、現金で支払いが発生するのならいいのですが、土曜祝祭日だと、
金融機関がお休みということもありまして、会社間の取り決めで、
土日祝祭日の前日に振り込まれるとか、その翌日に振り込まれることになります。
そこで、今回は、この土日祝祭日に対応し、
しかも、土日祝祭日に該当している場合は、
前日と翌日のそれぞれを表示させる日程表を作成していきます。
当然のことながら、ラッキーマンデーの場合は、
3日前の金曜日が表示されるようになっております。
まず、準備するものは、同じシートでも構いませんが、
祝日一覧表を別シートに作っておきましょう。
こんな感じです。
このような祝祭日の一覧表を作成しておきませんと、
ラッキーマンデーには対応できませんね。
では、別のシートに移動して、支払日の日程表を作成していきましょう。
このような表を作成しておきます。フレームですね。ポイントは、C2の2015年。
これは、表示形式で2015を2015年と表示させています。ここがポイントの一つですね。
このC2を2016にすると、2016年の日程表がサクサクと出来上がります。
念のため、C2のユーザー定義書式がどのように設定されているのか確認しておきましょう。
0"年"と設定されています。
A5:A16には、1～12までの数字が入力されています。これが月になります。
では、B5をクリックして、まずは、2015年1月末日を作っていきましょう。
最初に末日が表示される関数があります。EOMONTH関数ですね。
では、EOMONTH関数のダイアログボックスを表示しましょう。
月は当月末日ですので、0と入力しておきます。
そして、開始日ですが、DATE関数を使って、1月の日付を作っていきますので、
開始日のボックスの中にDATE関数を作っていきます。
では、開始日のボックスにカーソルをいれて、名前ボックスの▼から、
DATE関数のダイアログボックスを表示していきましょう。
年には、C2を入力しますが、このあと、下方向に数式をコピーすることを考慮して、
絶対参照を設定しておきますので、$C$2と入力します。
月には、A5。
日には、その月の末日を求めたいだけなので、1で結構ですので入力しておきます。
そして、OKボタンをクリックしましょう。オートフィルハンドルを使って12月まで算出しましょう。
この計算式によって、2月が29日でも大丈夫な締日が完成しました。
続いて、やはり曜日が分かった方がいいので、
C列にB列の締日の曜日を算出していきましょう。
ここは、TEXT関数を使っていきます。
今回は条件付き書式でのアレンジは省略しますが、条件付き書式も加えたい場合には、
以前もBLOGで紹介しましたが、TEXT関数を使う方がよろしいかと思います。
では、C5をクリックして、TEXT関数のダイアログボックスを表示しましょう。
値には、締日のB5を入力します。
表示形式には"aaa"と入録します。
そして、OKボタンをクリックして、オートフィルハンドルを使って12月まで算出します。
これで、曜日が算出されました。E列やG列やI列も同じ方法になりますので、
省略させていただきます。
続いて、【翌末払い】のD列を作成していきますので、D5をクリックしましょう。
先ほども使いました、月末を算出することができる、
EOMONTH関数のダイアログボックスを表示しましょう。締日の翌月末を算出させます。
開始日は、締日のB5を入力します。
月は、翌月なので1を入力します。もし、翌翌末払いならば、2ということになります。
あとは、OKボタンをクリックしましょう。
そして、12月までオートフィルハンドルを使って算出しておきましょう。
また、E列も曜日も算出しておきます。
翌末払い日が算出できました。
【末締めの翌末払い】なので、土日祝祭日に該当していなければこれの日付でOKなのですが、
2月や5月などは、平日ではありませんので、この日ではよろしくないわけです。
そこで、支払日がこの土日祝祭日の前日の日にする場合の算出方法をご紹介していきましょう。
F5をクリックしましょう。そして、ここで登場するのがWORKDAY関数です。
このWORKDAY関数は、土日を除いた日を指定された日数で算出、
しかも指定された日がある場合には、それを除いてくれるという関数なのです。
では、F5をクリックして、WORKDAY関数のダイアログボックスを表示しましょう。
開始日には、翌末払いのD5をクリックして、+1します。
日数には、-1と入力します。
祭日は、祝日一覧のシートに移動して、範囲選択をしますので、
祝日一覧!$A$2:$A$18と入力します。ここも絶対参照を設定しておきましょう。
で、なんで、開始日をワザと+1して、日数を-1するのかというと、
2/28に+1するので、3/1の日曜日になりますね。
土日を除きその開始日の-1の日になりますので、
前日が土曜日でさらにその前の金曜日の日付が算出されるわけです。
金曜日でも+1しますので、土曜日になって-1日されますので、金曜日になるわけです。
つまりこの処理は、平日の場合、前の日にならないための処理にあたります。
仮に金曜日が祝祭日の場合でも、除外しますので、木曜日が算出される仕組みです。
では、OKボタンをクリックして、オートフィルハンドルを使って12月まで算出して、
ついでにお隣の曜日も算出しておきましょう。
これで、支払日が前日の場合が算出できました。今度は支払日の翌日だった場合を算出してきましょう。H5をクリックして、今回もWORKDAY関数を使いますので、WORKDAY関数のダイアログボックスを表示しましょう。
開始日は、D5-1と入力します。
日数には、1と入力します。
祭日は、先ほどと同じ、祝日一覧!$A$2:$A$18と入力します。
絶対参照も忘れないようにしましょう。
今回は、先ほどとは逆で、開始日から-1します。
日曜日だった場合-1しますので、土曜日になり、土日祝祭日を除いて+1しますので、
翌日が算出されることになります。
では、OKボタンをクリックして、オートフィルハンドルを使って算出して、曜日も算出しておきます。
これで、2015年の各月ごとの支払日の日程表が算出できましたね。
2016年の日程表を作成するためには、まず祝日一覧を2016年版にするのと、
C2を2016に入力しなおすことで完成されますので、
経理財務会計部門の方以外でも挑戦してみてはどうでしょうか？
次回は、【末締め翌20日払い】を作成していきます。
WORKDAY関数は今回と変わりませんが、その前が異なりますので、
この末締め翌20日払いの場合もよくご質問がありますので、ご紹介していきます。
