BDAstyle

ビジネスデータ分析ツールの作成 with Excel

営業日数の計算 with Excel ― ある時点からある時点までのいろいろな条件下での営業日のカウント

ショートカット

Case1)土日(場合により祝祭日)定休の環境

Step 0シチュエーションの設定

ヘアサロンXRFM分析をおこなう際,営業日をもとにしたR(Recency)基準を利用しています。まずCase1では,ヘアサロンXが土日祝祭日休みであると仮定して計算をすすめます。

分析対象期間の最終日は20131227日営業終了時点とします。分析にあたって,対象のひとり,顧客ID_0247に関する直近の来店日を調べたところ,2013115日であることがわかりました。すなわち,総日数では下のカレンダーの淡いパープルの彩色部分が経過したことになります。

この間の営業日数をカレンダーから目視で数えると,

経過していることがわかります(濃いパープルの部分)。ここでは,これを目視によらず計算することが目的となります。

Step 1見出しの作成とデータの入力

カウント開始日と終了日,および祝祭日やその他の休業日を入力します。祝祭日やその他の日付を休業日としない場合,後者の入力は不要です。

Step 2NetWorkdays関数による営業日のカウント

営業日のカウントにはNetWorkdays関数を使用します。この関数の引数は,

NETWORKDAYS(開始日, 終了日, [その他の休日※])

となります(※公式の引数名とは異なります。以下に同じ。―"Office")。したがってStep 1のシートの構成であるとき,計算式は,

開始日を含む場合 =NETWORKDAYS($B$1,$B$2,E:E)
開始日を含まない場合 =NETWORKDAYS($B$1+1,$B$2,E:E)

と指定します。ただし祝祭日やその他の日付を休業日としない場合,最後の引数を指定する必要はありません。

Case2)特定の1曜日が定休の環境(ver.2010 or later)

Step 0シチュエーションの設定

Case1と同じ設定のまま,ここでは定休日を次のように変更します。

[定休日] Case1: 毎週土日曜日(2日) → Case2: 毎週曜日(1日)

また祝祭日は営業した(する)一方,1112日から2日間の臨時休業日が発生した(する)ものと仮定します。

この条件でこの期間の営業日数をカレンダーから目視で数えると,

であることがわかります(濃いパープルの部分)。ここでは,これを目視によらず計算することが目的となります。

Step 1見出しの作成とデータの入力

カウント開始日と終了日,および定休日(ここでは毎月曜)を除く休業日を入力します。定休日を除く休業日の発生しなかった(しない)場合,後者の入力は不要です。

Step 2NetWorkdays.Intl関数による営業日のカウント

土日以外の定まった1曜日を休日とする場合の営業日のカウントには,NetWorkdays.Intl関数を使用します(バージョン2010以降で対応)。この関数の引数は,

NETWORKDAYS.INTL(開始日, 終了日, [定休曜日※],[定休を除く休日※])

となります("Office":ver.2010向け)。引数「定休曜日」については,下表の該当する曜日番号で指定します。

日曜日 11
月曜日 12
火曜日 13
水曜日 14
木曜日 15
金曜日 16
土曜日 17

したがってこの場合,Step 1のシートの構成であるときの計算式は,

開始日を含む場合 =NETWORKDAYS.INTL($B$1,$B$2,12,E:E)
開始日を含まない場合 =NETWORKDAYS.INTL($B$1+1,$B$2,12,E:E)

と指定します。定休日を除く休業日の発生しなかった(しない)場合,最後の引数(下図ピンクの部分)を指定する必要はありません。

Case3)土日以外の特定の2曜日以上が定休の環境(ver.2010 or later)

Step 0シチュエーションの設定

Case1と同じ設定のまま,ここでは定休日を次のように変更します。

[定休日] Case1: 毎週土日曜日(2日) → Case2: 毎週月木曜日(2日)

また祝祭日は営業した(する)一方,1112日から2日間の臨時休業日が発生した(する)ものと仮定します。

この条件でこの期間の営業日数をカレンダーから目視で数えると,

であることがわかります(濃いパープルの部分)。ここでは,これを目視によらず計算することが目的となります。

Step 1見出しの作成とデータの入力

カウント開始日と終了日,および定休日(ここでは毎月木曜)を除く休業日を入力します。定休日を除く休業日の発生しなかった(しない)場合,後者の入力は不要です。

Step 2定休日の設定

特定の2曜日以上が定休の環境の場合,定休日の指定はフラグ立てによる指定の方が明快です。

この場合,7つの'0'による文字列

’0000000

(文字列として扱うため先頭に「'」を加えます。「'」は入力後のセルには表示されません)

を用意し,左から数字1文字ずつ,月・火・水・木・金・土・日曜日の営業の状態(「営業日」or「定休日」)を意味する2値の変数としてみなします。具体的には,定休となる曜日のみ'1'に変更します。すなわちこのケースでは,

’1001000

となります。

Step 3NetWorkdays.Intl関数による営業日のカウント

土日以外の定まった2曜日以上を休日とする場合の営業日のカウントも,NetWorkdays.Intl関数を使用します(バージョン2010以降で対応)。この場合,Step 2のシートの構成であるときの計算式は,

開始日を含む場合 =NETWORKDAYS.INTL($B$1,$B$2,$B$4,E:E)
開始日を含まない場合 =NETWORKDAYS.INTL($B$1+1,$B$2,$B$4,E:E)

となります。これはCase2と定休曜日の指定の方法が異なるだけです(下図参照)。またこちらも定休日を除く休業日の発生しなかった(しない)場合,最後の引数(下図ピンクの部分)を指定する必要はありません。

その他の参照