スポンサーリンク

エクセルで前営業日、翌営業日を探す(祝日にも対応)

関数の応用

エクセルで前営業日、翌営業日を探す

どういう事かわかりやすくするため、具体例をあげましょう。

給料の振込が毎月25日で、その日が休日である場合は直前の平日に振り込むため、その日がいつなのかを調べたい。
(2020/1/25を指定したら、その日は土曜日なので1/24という結果が自動で返ってくる仕組みを作りたい)

今回はそれを、WORKDAY関数を使って、実現していきます。

WORKDAY関数とは

まずはじめにWORKDAY関数について。

指定した日付から、〇営業日後、または〇営業日前を探し出す関数で、設定次第で祝日にも対応できるというものです。

【使い方】
=WORKDAY(開始日,日数,[祭日])
日数が正数ならば開始日直後、負数ならば開始日手前の営業日を探します。

実際に使ってみましょう(前営業日)

例をもとに、実際に使ってみましょう。

今回の例題は冒頭でもあるとおり、毎月25日が給料日で、休日の場合直前の平日を探す仕組みを作ります。

祝日一覧を用意

この日は休日として扱うという、日付の一覧を用意します。

手作成でもいいですが、他サイトから一覧引っ張ってしまうと楽です。

今回は下図ような一覧を用意したものとします。

開始日一覧

次に、1年間分の25日リストを用意します。

1/25と2/25だけ入力してしまえば、あとはオートフィルで下までドラッグするだけで簡単に作れます。

WORKDAY関数を用意

先頭行に関数を入力します。

式:=WORKDAY(D4+1,-1,$B$4:$B$21)

あとは最終行までオートフィルで反映させます。

43854みたいな数字が表示されてしまった場合は、書式を日付にしてください。

注目すべきは7月で、7/25は土曜日、7/24はスポーツの日、7/23は海の日なので、土日祝日をしっかり判断して、7/22という結果が出ています。

今回の式の不思議な部分

表自体はこれで完成ですが、今回組んだ式、不思議な作りになってますよね。

式:=WORKDAY(D4+1,-1,$B$4:$B$21)

  • D4+1の+1って何?
  • 祭日はなんでB列全体指定にしてないの?

それぞれ理由はちゃんとあります。

まず、なぜ+1してるのかですが、+1しないと25日より手前の平日を探してしまうので、+1することで26日の手前を探すようにしています。

=WORKDAY("2020/2/25",-1)=2020/2/24
=WORKDAY("2020/2/26",-1)=2020/2/25

次に、祭日を絶対値指定している理由ですが、1つでも日付ではない余計なデータが混じると、#VALUEになってしまうからです。

例えば今回B4~B21にしていますが、B3~B21にして"日付"という文字まで範囲に含んでしまうと、それだけで#VALUEになってしまいます。

直後の平日を探すパターン(翌営業日)

続いて逆のパターン、直後の平日を探してみましょう。

住民税納付日を仮想敵として、10日が納付日で、休日の場合は直後の平日を探す仕組みを作ります。

式:=WORKDAY(D4-1,1,$B$4:$B$21)

プラスとマイナスが逆転してるだけですね。

D4+1だったのがD4-1に、日数が-1だったのが1になったことで、9日より1日後の平日を探すという動きになります。

まとめ

自分自身が毎年つかってます、これ。

給与に係る事務仕事してるもので、給与振り込み日の設定は毎年恒例となってますので。

さて、最後によけいな豆知識を置いておきます。

余計な事はするなよって言われてる気がした。

コメント

タイトルとURLをコピーしました