Excle VBA 指定期間の稼働日数を取得する

スポンサーリンク
スポンサーリンク

稼働日数を取得する方法

土曜や日曜、祝日などの休みを除いた日数を
稼働日といいますが、Excel2010以降では
稼働日数を算出する際に便利な
「NETWORKDAYS.INTL」関数が用意されています。

このワークシート関数はVBAでも利用できるようになっています。
VBAで利用する場合は、WorksheetFunctionオブジェクトに用意されている
Networkdays_Intl【ネットワークデイズインテル】メソッドを使用します。

Networkdays_Intl【ネットワークデイズインテル】メソッド

指定した期間内の指定した休日を除いた稼働日を取得するには
WorksheetFunctionオブジェクトの
Networkdays_Intl【ネットワークデイズインテル】メソッドを使用します。

Networkdays_Intl【ネットワークデイズインテル】メソッドの書式

[]内は省略可能です。
オブジェクト.Networkdays_Intl( 開始日, 終了日, [休日,][祝日] )
【戻り値】倍精度浮動小数点型 (Double)


設定値(引数)の説明

  • オブジェクト(必須)
    WorksheetFunctionオブジェクトを指定します。
  • 開始日(必須)
    集計する期間の起算日を指定します。
  • 終了日(必須)
    集計する期間の終了日を指定します。
  • 休日(省略可)
    稼働日に含めない週末の曜日を週末を表す文字列値または、週末番号で指定します。
    週末を表す文字列は「0を稼働曜日」「1を非稼働曜日」で表し
    月曜日を起点に「0」と「1」で曜日の稼働曜日、非稼働曜日を表します。
    (例)土日を休日とする場合は「”0000011″」で表します。
    ※省略した場合は土曜日と日曜日が非稼働日になります。

    週末番号一覧表
    週末番号週末の曜日
    1 または省略土曜日と日曜日
    2日曜日と月曜日
    3月曜日と火曜日
    4火曜日と水曜日
    5水曜日と木曜日
    6木曜日と金曜日
    7金曜日と土曜日
    11日曜日のみ
    12月曜日のみ
    13火曜日のみ
    14水曜日のみ
    15木曜日のみ
    16金曜日のみ
    17土曜日のみ
  • 祝日(省略可)
    稼働日に含めない祝日のリストを指定します。
    セルに祝日の日付リストを作成してセル範囲を参照する方法が一般的ですが
    Array関数日付リテラルを配列に格納して参照する方法もあります。

インプットボックスを使用して稼働日をメッセージボックスに表示するコード例

Sub 稼働日土日祝()
Dim 開始日 As Date
Dim 終了日 As Date
Dim 稼働曜日 As String
Dim 最終行 As Long
Dim 祝日 As Range
最終行 = Sheets("祝日").Cells(Rows.Count, 1).End(xlUp).Row
Set 祝日 = Range(Cells(2, 1), Cells(最終行, 1))
On Error GoTo エラー処理
開始日 = InputBox("開始日を2017/1/1形式で入力してください。")
終了日 = InputBox("終了日を2017/1/1形式で入力してください。")
稼働曜日 = "0000011"
MsgBox "日数は" & DateDiff("d", 開始日, 終了日) + 1 & "日です。" & vbCrLf & _
"稼働日数は" & WorksheetFunction.NetworkDays_Intl(開始日, 終了日, 稼働曜日, 祝日)
Exit Sub
エラー処理:
MsgBox "日付の入力が正しくありません。"
End Sub

土日と祝日を稼働日に含めないコード例です。
祝日リストを祝日シートに作成します。

コードの解説

2行目
【Dim 開始日 As Date】
InputBox関数でユーザーから受け取る
期間の起算日を格納する変数「開始日」を日付型で宣言します。


3行目
【Dim 終了日 As Date】
InputBox関数でユーザーから受け取る
期間の終了日を格納する変数「終了日」を日付型で宣言します。


4行目
【Dim 稼働曜日 As String】
Networkdays_Intl【ネットワークデイズインテル】メソッドの引数「休日」で
使用する週末を表す文字列を格納する変数「稼働曜日」を
文字列型で宣言します。


5行目
【Dim 最終行 As Long】
シート祝日にある祝日のリストの最終行を格納する
変数「最終行」を長整数型で宣言します。


6行目
【Dim 祝日 As Range】
祝日が入力されているセル範囲を格納する
変数「祝日」をオブジェクト型(Range)で宣言します。


7行目
【最終行 = Sheets(“祝日”).Cells(Rows.Count, 1).End(xlUp).Row】
シート祝日の祝日リストの最終行を取得して
変数「最終行」に代入します。


8行目
【Set 祝日 = Range(Cells(2, 1), Cells(最終行, 1))】
祝日リストのセル範囲を取得して
変数「祝日」に代入します。オブジェクト型変数なので
Setキーワードを使用して代入します。


9行目
【On Error GoTo エラー処理】
ユーザーが期間を入力するインプットボックスに
Date型(日付)以外の値を入力するとエラーになるので
OnErrorステートメントとGoToステートメントを使用して
エラーになった場合、16行目の「エラー処理:」ラベルまで
処理をジャンプして処理を終了します。


10行目
【開始日 = InputBox(“開始日を2017/1/1形式で入力してください。”)】
InputBox関数を使用してユーザーに開始日を入力してもらい
値を変数「開始日」に代入します。


11行目
【終了日 = InputBox(“終了日を2017/1/1形式で入力してください。”)】
InputBox関数を使用してユーザーに期間の終了日を入力してもらい
値を変数「終了日」に代入します。


12行目
【稼働曜日 = “0000011”】
引数「休日」に指定する週末を表す文字列を変数「稼働曜日」に
代入します。この例では土日を週末として非稼働日としています。
土日の場合は引数を省略できますが、非稼働の曜日を変更する場合が
あることを想定して記述しています。
非稼働曜日を変更する場合はこの文字列を変更します。


13行目~14行目
【MsgBox “日数は” & DateDiff(“d”, 開始日, 終了日) + 1 & “日です。” & vbCrLf & _
“稼働日数は” & WorksheetFunction.NetworkDays_Intl(開始日, 終了日, 稼働曜日, 祝日)】
ユーザーが入力した期間の日数をも求めるため指定した期間の間隔を取得する
DateDiff【デイトディフ】関数を使用して期間の間隔を
取得して1日を加算することにより指定期間の日数を取得します。
Networkdays_Intl【ネットワークデイズインテル】メソッドの引数に各変数を設定して
稼働日を取得し、MagBox関数を使用して取得した値を表示します。


  • 実行結果




    セルに期間を入力してセルに日数と稼働日を取得するコード例

    B1セルに開始日、B2セルに終了日を入力してマクロを実行すると
    D2セルに日数、E2セルに稼働日数を返すコード例です。



    Sub 稼働日土日祝セル()
    Dim 開始日 As Date
    Dim 終了日 As Date
    Dim 稼働曜日 As String
    Dim 最終行 As Long
    Dim 祝日 As Range
    Dim 日数 As Variant
    Dim 稼働日 As Variant
    Sheets("祝日").Activate
    最終行 = Cells(Rows.Count, 1).End(xlUp).Row
    Set 祝日 = Range(Cells(2, 1), Cells(最終行, 1))
    Sheets("稼働日").Activate
    On Error GoTo エラー処理
    開始日 = Range("B1").Value
    終了日 = Range("B2").Value
    稼働曜日 = "0000011"
    Range("D2:E2").NumberFormatLocal = "G/標準"
    Range("D2").Value = DateDiff("d", 開始日, 終了日) + 1
    Range("E2").Value = WorksheetFunction.NetworkDays_Intl(開始日, 終了日, 稼働曜日, 祝日)
    Exit Sub
    エラー処理:
    MsgBox "日付の入力が正しくありません。"
    End Sub
    

    コードの解説

    基本的には上記のInputBox関数とMsgBox関数を使用した例と同じですが、
    16行目の
    【Range(“D2:E2”).NumberFormatLocal = “G/標準”】
    値が返るセルの表示形式を
    NumberFormatLocal【ナンバーフォマットローカル】プロパティで「標準」に指定しないと
    Excelが自動的に日付形式に変更して正しい値が取得できなくなります。


    実行結果


    以上で
    指定期間の稼働日数を取得するについての解説を終了します。
    ありがとうございました。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク