「営業日数」の算出

ある日付からある日付までの日数ですが、会社の休日を除いた日数を算出します。
社内で人事関係のデータベースを作っていました。 関連部署の仕事の中で意外に苦労していたのが、休職の人の「欠勤」にあたる日数(営業日数)の算出なのです。 私の会社は普通に土曜、日曜、祝日と年末年始が休みなので、休職の人のその期間の「営業日数」として会社休日を除いた日数を算出する必要があります。 休職の期間が歴月の1日から末日に特定されていれば各月の営業日数を予め計算しておくことで算出でき簡単なことなのですが、 実際の休職の期間はそのような日付ではなく個人ごとにバラバラなので、人数が少ないものの大変な作業のようです。
今回は計算式の「ユーザー定義関数」利用のサンプルとしてもご覧下さい。
※このページのサンプルコードは12/29〜1/3を会社休日にするサンプルになっています。「祝日パラメータ」シートをご覧下さい。
プログラム修正なく祝日の変更に対応できないか!?   以前のこのページの方法では、年間各月の祝日の判定はプログラムソース記述で行なっているのが現状でした。
2019年になって、平成天皇の退位や新しい天皇の即位や「天皇誕生日」の移動などで、この数年は祝日判定プログラムを変更する必要が毎年発生してしまいます。
であれば、このような祝日変更をプログラムの修正なく対応できないものか、と考えたのです。

春分の日、秋分の日は特殊な計算が必要ですが、それ以外の祝日はテーブル登録から計算できないかと考える時期が来ました。
そこで考えたのが「年間カレンダーの作成2」です。営業日数算出のプロシージャも含めてあります。
祝日の定義は「祝日パラメータ」シートで設定するようになっており、会社休日の追加も行なえるようになっています。
このページもこの方法を利用する方法に変更しています。
ではこれを「関数化」できれば良いわけです。
先に利用例を見てみましょう。このページのサンプルは下記画像からダウンロードした中の「05_SumEigyoNissu1.xlsm」を利用しています。
営業日数の算出
(画像をクリックすると、このページのサンプルがダウンロードできます)
このように「期間開始日」「期間終了日」を入力して、「営業日数」「暦日数」を算出するというものですが、今回は日付入力にて即算出を行なわせるために「ユーザー定義関数」として実装してみました。
このように「SUMEIGYONISSU2」を「ユーザー定義関数」として計算式中に埋め込んであります。
多くの場合、年間の祝日の配列を予め別シートなどに用意して、「NETWORKDAYS関数」を用いて算出しようとするでしょう。でも「分析ツールアドイン」を組み込まなければならなかったり、祝日の配列を別途用意しなければなりません。
でも、ここで紹介している方法では、祝日は内部判定している上、「分析ツールアドイン」も用いません。 ですから、計算式のレベルでは、カッコ内の第1引数が「期間開始日」、第2引数が「期間終了日」ということだけ理解しておけば簡単に利用できます。 ただし、後述する必要なモジュールをインポートさせて、マクロを有効にしておかなければなりませんが、マクロは既にできているものを使うのでマクロの作成者以外はコード上の知識は必要ありません。

逆に起算日から営業日数後の判定営業日を算出する関数も用意してあります。
営業日数経過後の営業日
上の「営業日数の算出」では、期間開始日も営業日数計算に掛かりますが、「日数経過後の営業日の算出」では起算日は営業日数に関係なくゼロ扱いなので算出に加わりません。 このため「営業日数の算出」とは1日分の差異が発生します。
このように「SUMEIGYOBI2」を「ユーザー定義関数」として計算式中に埋め込んであります。

もう一つ、日付別に「営業日」かどうかを判定する関数を用意しています。
ここまで紹介したのは、日付と日付間での営業日数が何日なのかの算出でしたが、もう一つはカレンダーのように日付が整然と並んだ状態で「ある日が営業日なのかどうか」を判定する関数を紹介してみます。
この関数「CHECKEIGYOBI2」では、セットした日付が営業日なら「1」、そうでなければ「0」を返します。 ですから、この結果を並べていって期間に当たる範囲を合計すると、結果として「営業日数」にもなるわけです。
「営業日カレンダー」の画面
「日付」は先頭の日付(ここでは「2018/5/1」)だけを手入力するものとし、それ以降の日付や曜日などは全て計算式で求めています。

「3ヶ月予定表」の画面
このような横型の予定表でも利用することができ、このサンプルでは隠れている1行目に「CHECKEIGYOBI2」関数をセットしていて、 縦に黄色く見えている休日を意味する塗りつぶしの条件付き書式でも参照させています。

「祝日パラメータ」シートです。
「祝日パラメータ」シート
祝日の判定をプログラムソースコードで行なわないということで、このような「祝日パラメータ」シートを取り入れています。 祝日の改変はこのシート上で行なって下さい。
画面サンプルでは対応していませんが、ダウンロードされたサンプルでは新しい祝日法の改正に対応したものになっています。
なお、ここでの変更を他シートに反映されるためには再計算が必要になります。このシートは運用時は非表示で構いません。
この「祝日パラメータ」シートの設定項目や変更方法については「年間カレンダーの作成2(VBA応用)の中段の説明をご覧下さい。
「祝日パラメータ」シートを使いたくないという場合の方法もその次のページで説明しています。

マクロの方も見てみましょう。

'***************************************************************************************************
'   カレンダー及び日付処理関連ワークシート関数                  modCalendarForFomula1(Module)
'
'   本モジュールは「祝日パラメータ」シート及びmodAboutCalendar2を利用して営業日数などを関数利用します
'   「祝日パラメータ」シートのチェック処理を除外しているのでチェック済「祝日パラメータ」が前提となります
'
'   作成者:井上治  URL:http://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev  変更履歴内容------------------------------------------------------------------------>
'18/03/07(1.00)新規作成
'***************************************************************************************************
Option Explicit

'***************************************************************************************************
'   ■■■ ワークシート関数として利用するプロシージャ ■■■
'***************************************************************************************************
'* 処理名 :SUMEIGYONISSU2
'* 機能  :営業日数算出(土日祝日を除外)
'---------------------------------------------------------------------------------------------------
'* 返り値 :営業日数(Long)     ※算出不可の場合はゼロ
'* 引数  :Arg1 = 期間開始日(Date)
'*      Arg2 = 期間終了日(Date)
'---------------------------------------------------------------------------------------------------
'* 作成日 :2018年03月07日
'* 作成者 :井上 治
'* 更新日 :2018年03月07日
'* 更新者 :井上 治
'* 機能説明:ワークシート関数利用のためのプロシージャ
'* 注意事項:開始日、終了日自体も営業日判断に適用される ※期間開始日≦期間終了日のこと
'***************************************************************************************************
Public Function SUMEIGYONISSU2(dteDate1 As Date, dteDate2 As Date) As Long
    '-----------------------------------------------------------------------------------------------
    ' 営業日数算出(シート関数利用のため祝日シートチェックは除外)
    Call modAboutCalendar2.SumEigyoNissu(dteDate1, dteDate2, SUMEIGYONISSU2, 0, True)
End Function

'***************************************************************************************************
'* 処理名 :SUMEIGYOBI2
'* 機能  :営業日数経過後営業日算出(土日祝日を除外)
'---------------------------------------------------------------------------------------------------
'* 返り値 :営業日数経過後営業日(Date)
'* 引数  :Arg1 = 起算日(Date)
'*      Arg2 = 経過日数(Long)                              ※±可能
'---------------------------------------------------------------------------------------------------
'* 作成日 :2018年03月07日
'* 作成者 :井上 治
'* 更新日 :2018年03月07日
'* 更新者 :井上 治
'* 機能説明:経過日数は翌日(翌営業日)を「1」として算出される(前営業日は「-1」)
'* 注意事項:経過日数がゼロの場合は起算日をそのまま返す(土日祝判断なし)
'***************************************************************************************************
Public Function SUMEIGYOBI2(dteDate1 As Date, lngKeika As Long) As Date
    '-----------------------------------------------------------------------------------------------
    ' 営業日数経過後営業日算出(シート関数利用のため祝日シートチェックは除外)
    Call modAboutCalendar2.SumEigyoBi(dteDate1, lngKeika, SUMEIGYOBI2, True)
End Function

'***************************************************************************************************
'* 処理名 :CHECKEIGYOBI2
'* 機能  :その日が営業日かを判定
'---------------------------------------------------------------------------------------------------
'* 返り値 :判定結果(営業日=1, 休日=0)
'* 引数  :Arg1 = 対象日付(Date)
'---------------------------------------------------------------------------------------------------
'* 作成日 :2018年03月07日
'* 作成者 :井上 治
'* 更新日 :2018年03月07日
'* 更新者 :井上 治
'* 機能説明:
'* 注意事項:当該日付が日付でない場合はゼロを返す
'***************************************************************************************************
Public Function CHECKEIGYOBI2(vntDate As Variant) As Integer
    '-----------------------------------------------------------------------------------------------
    Static prevY As Long                                            ' 読み込んだ年
    Static prevM As Long                                            ' 読み込んだ月
    Static tblCalendar() As g_typAboutCalendar2                     ' カレンダーテーブル
    Dim dteDate As Date                                             ' 対象日付
    Dim currY As Long                                               ' 対象日付の年
    Dim currM As Long                                               ' 対象日付の月
    Dim currDIX As Long                                             ' 対象日付の日INDEX
    CHECKEIGYOBI2 = 0
    ' 対象日付チェック
    If IsDate(vntDate) Then
        dteDate = CDate(vntDate)
    Else
        Exit Function
    End If
    ' 当該営業日から年・月・日の算出
    currY = Year(dteDate)                                       ' 年
    currM = Month(dteDate)                                      ' 月
    currDIX = Day(dteDate) - 1                                  ' 日付位置INDEX
    '-----------------------------------------------------------------------------------------------
    ' 年月の変異により該当月のカレンダーを取得し直す
    If ((currY <> prevY) Or (currM <> prevM)) Then
        prevY = currY
        prevM = currM
        ' カレンダーテーブル作成(当月1ヶ月用)
        Call modAboutCalendar2.GetCalendarTable1(prevY, prevM, tblCalendar, True)
    End If
    '-----------------------------------------------------------------------------------------------
    ' 営業日判定
    With tblCalendar(currDIX)
        ' 土日祝日以外は営業日
        If ((.Yobi <> 0) And (.Yobi <> 6) And (.Syuku = 0)) Then
            CHECKEIGYOBI2 = 1
        Else
            CHECKEIGYOBI2 = 0
        End If
    End With
End Function

'----------------------------------------<< End of Source >>----------------------------------------
これらがワークシート関数として呼び出されているモジュールです。
ここではカレンダー作成や祝日判定などの細かい記述はありませんが、これらはVBA側で行なっている「modAboutCalendar2」に依存させています。 「modAboutCalendar2」の呼び出し先プロシージャについては「年間カレンダーの作成2」をご覧下さい。

これらの機能の「実装」について
カレンダー関連の処理を行なっているワークブックにこれらの機能を実装させるには、このサンプルから以下を持ち込んで下さい。
・「祝日パラメータ」シート
modAboutCalendar2.bas(Module)
modCalendarForFomula1.bas(Module)
なお、本カレンダー関連の計算式上での反映は再計算が必要です。
また、本機能のワークシート関数利用では「祝日パラメータ」シートのチェック及び並べ替えが行なわれないので、事前にチェック及び並べ替えを行なっておく必要があります。