どうもマサヤです！
先日、私が作った営業日判定カレンダーをアナタに提供します！
土日祝日以外にもお勤め先によって他の休日がありますよね。
カレンダーを作る際はこの会社毎の休日も含めないといけません。
今回、提供するカレンダーは会社毎の休日も反映できるものです。
・来月、週報を提出する●営業日が何日かが分る。
・Excel起動時の条件分岐用として使う。
・VBAやRPAなどの自動化処理判定として使う。
使い方はアナタ次第です。アレンジしてお使いください！
【動画】独自の営業日判定カレンダーとは？
百聞は一見にしかず！ということで、動画をどうぞ！
F列に独自の休日を設定することができます。
休日を設定すると、J列が「祝」となりK・L列の営業日が変動します。
カレンダーの作り方
参考にどのように作っているかを紹介します。
「Excelの数式は基本しか作れない」とお悩みのアナタ！
紹介する数式や組み合わせを理解することでレベルアップできますよ！
曜日の形式や休日判定
まずは簡単な曜日から説明していきます。
曜日を自動で休日・平日と判定させるまでに必要な処理は以下となります。
・日付を参照して曜日に変換する。
・祝日と独自弓術の場合は祝日判定させる。
・条件付き書式で休日と祝日に色を付ける。
日付を曜日形式に変換し、条件付き書式で色を付ける
サンプルのI列には日付が入っています。
【曜日のJ列】は【日付のI列】をそのまま参照しています。
J4を例に挙げると数式は↓となります。
シンプルですね。でも、今の状態はシリアル値もしくは日付が表示されているはずです。
曜日に変換する必要があるので、J4セルの表示形式を「aaa」に変更します。
これで、日付が曜日に変更されました。
日付を曜日に変換する方法・休日の場合は色を変更する方法などはこちら！
祝日・独自休日の判定
最初に祝日と独自休日を設定する枠を用意しましょう。
サンプルでは【休日】はセルB14:D159に、【独自休日】はセルF2:G103に枠を用意しています。
まずは祝日枠を埋めますが手打ちする必要はありませんよ。
ネットで「祝日 一覧」と検索すれば、たくさんヒットしますので活用しましょう。
独自休日についてはご自身もしくは会社独自の休日をお好きに設定してください。
いよいよ、曜日部分を祝日判定させます。
J4を例に挙げて数式を説明しますね。
数式が長い！もうわからん！・・・となりそうですね。
たくさんの関数が使われているので1つずつ分解して理解していきましょう。
分解して理解するのがExcel上達の早道です。
まずは一番外であるIF関数を確認しましょう
↓
条件：AND(ISERROR(VLOOKUP(I4,$F$4:$F$103,1,FALSE)),ISERROR(VLOOKUP(I4,$B$15:$B$159,1,FALSE)))
真 ：I4
偽 ：”祝”
条件としてANDが使われていますので、AND条件を分解してみましょう。
AND(ISERROR(VLOOKUP(I4,$F$4:$F$103,1,FALSE)),ISERROR(VLOOKUP(I4,$B$15:$B$159,1,FALSE)))
↓
条件1：ISERROR(VLOOKUP(I4,$F$4:$F$103,1,FALSE))
条件2：ISERROR(VLOOKUP(I4,$B$15:$B$159,1,FALSE))
ISERRORとVLOOKUPが使われているのが解ります。
VLOOKUP関数
では、条件1のVLOOKUPをかみ砕いてみましょう。
条件1：ISERROR(VLOOKUP(I4,$F$4:$F$103,1,FALSE))
↓
条件1：ISERROR(VLOOKUP(I列の日付,独自祝日のセル範囲,独自祝日範囲の1列目（日付）,一致))
独自祝日のセル範囲に対してI列の日付が存在しているかを確認。
存在していた場合、独自祝日のセル範囲にの1列目にある日付へ返す。
存在しない場合はエラーを返します。
条件2は祝日に対して同じように判定させています。
VLOOKUPの詳細を知りたいアナタはこちらをどうぞ！
ISERROR関数
そして、ISERRORの役割はVLOOKUPでエラーが帰ってきた場合、TRUEを返します。
エラーでない場合は、FALSEを返します。
ISERROR(VLOOKUP(I列の日付,独自祝日のセル範囲,独自祝日範囲の1列目（日付）,一致))
↓ VLOOKUPを判定、独自休日には1月1日はないためエラーとなります。
FALSE
AND関数
次はAND関数です。
ANDは条件①・条件②が両方ともTRUEであれば、TRUEを返します。
どれか一つでもFALSEであれば、FALSEを返します。
↓ 条件1は独自休日に1月1日は無いためTRUEが返され
↓ 条件２は休日に1月1日が有るためFALSEが返されます。
AND（TRUE,FALSE）
↓ FALSEが一つでもあるので、FALSEが返されます。
FALSE
↓IF関数とAND関数の組み合わせを動画で紹介しています。
IF関数
ANDでFALSEが返されました。
よって結果は下記のようになります。
=IF(AND(ISERROR(VLOOKUP(I4,$F$4:$F$103,1,FALSE)),ISERROR(VLOOKUP(I4,$B$15:$B$159,1,FALSE))),I4,”祝”)
↓ AND関数の結果がFALSEであったため、祝がセルに反映されます。
=IF(TRUE, I列の日付, “祝”)
処理の内容は祝日・独自休日に日付があればJ列に祝が入ります。
無ければI列の日付が入り、曜日として表示されます。
まとめ
長くなりましたので、ページを分割することにします。
次回は
・週の営業日判定
・月の営業日判定
・指定日付に対して何営業日かを判定
の解説をしていきます！
RPAで自動的に祝日や会社独自の休日を計算したり、ネットから取得してくれる！
・・・・・・当然、そんな魔法のようなツールではありません。
そこで、営業判定用に作ったのがこの独自営業カレンダーです！
もちろん、RPA以外でも使い道は色々とあると思います。
VBA判定用に使うもよし、数式の条件として使ってよしです。
自由にカスタマイズしてお使いください！
コメント