道  具  箱



『企業システム戦略』の構築・実践や人材育成に役立つ道具を紹介します。

◆簡易日程計算(Excel/VBA)の説明
 製品構成展開されたツリーデータを使用して、製品を期日までに組立完成させるために 必要な日程(延べ日数等)を計算するExcel/VBAの要点を説明します。
 必要な方は、Excelファイルを自分のパソコンにダウンロードして下さい。


◆元のデータ

 TOPから下位階層に向かって構成品があり、
 各階層の作業日数が決まっています。
 TOPの完了予定日に納期を入力します。


階層,部番,日数,延べ日数,開始予定日,完了予定日
0,TOP,5,,,2021/6/28
1,Assy1,2,,,
2,Part1,3,,,
3,Mate1,15,,,
2,Part2,10,,,
3,Mate2,25,,,
1,Assy2,3,,,
2,Part3,7,,,
3,Mate1,15,,,
2,Part4,12,,,
3,Mate2,25,,,


 「祝日」シートのA列に祝日を入れておきます。

2021年1月1日,(金),元日
2021年1月11日,(月),成人の日
2021年2月11日,(木),建国記念の日
2021年2月23日,(火),天皇誕生日
2021年3月20日,(土),春分の日
2021年4月29日,(木),昭和の日
2021年5月3日,(月),憲法記念日
2021年5月4日,(火),みどりの日
2021年5月5日,(水),こどもの日
2021年7月22日,(木),海の日(2021年のみ)
2021年7月23日,(金),スポーツの日(2021年のみ)
2021年8月8日,(日),山の日(2021年のみ)
2021年8月9日,(月),振替休日
2021年9月20日,(月),敬老の日
2021年9月23日,(木),秋分の日
2021年11月3日,(水),文化の日
2021年11月23日,(火),勤労感謝の日


◆計算後のデータ

 TOPから各階層までの延べ日数、
 開始予定日、完了予定日が稼働日
 で出力されます。

階層,部番,日数,延べ日数,開始予定日,完了予定日
0,TOP,5,5,2021/6/21,2021/6/28
1,Assy1,2,7,2021/6/17,2021/6/21
2,Part1,3,10,2021/6/14,2021/6/17
3,Mate1,15,25,2021/5/24,2021/6/14
2,Part2,10,17,2021/6/3,2021/6/17
3,Mate2,25,42,2021/4/23,2021/6/3
1,Assy2,3,8,2021/6/16,2021/6/21
2,Part3,7,15,2021/6/7,2021/6/16
3,Mate1,15,30,2021/5/17,2021/6/7
2,Part4,12,20,2021/5/31,2021/6/16
3,Mate2,25,45,2021/4/20,2021/5/31


以下がVBAのコードです。


Sub SetSyoyou()

Dim DAY_T、DAYS As Long
Dim DAY_E, DAY_S As Date
Dim DAY_Pre(20) As Long
Dim Level, Level_pre, Row As Long
Dim PartNo As String

Set strShtName = ActiveSheet

Dim ws1 As Worksheet
Set ws1 = Worksheets("祝日")

Row = 2
PartNo = strShtName.Cells(Row, 2).Value

Do While Not (PartNo = "")

'現在行のLevel(1列目)と前行のLevel(1列目)を取得する。
 Level = strShtName.Cells(Row, 1).Value
 Level_pre = strShtName.Cells(Row - 1, 1).Value

'最上位の部品の延べ日数に日数をセットする。
 If Level = 0 Then
    If strShtName.Cells(Row, 3).Value <> "" Then
       DAY_T = strShtName.Cells(Row, 3).Value

    Else
       DAY_T = 0
    End If

     DAY_E = strShtName.Cells(Row, 6).Value
    Level_pre = 0

 Else

'Levelが下がった場合、現延べ日数をひとつ上のLevelの延べ日数に保持する。
   If Level_pre < Level Then

      DAY_Pre(Level - 1) = DAY_T

   End If

'ひとつ上のLevelの延べ日数に日数を加算して延べ日数を算出する。
   DAY_T = DAY_Pre(Level - 1) + strShtName.Cells(Row, 3).Value

 End If

'Excelに延べ日数をセットする。
 strShtName.Cells(Row, 4).Value = DAY_T

'Excelに開始予定日をセットする。
 DAY_S = WorksheetFunction.WorkDay(DAY_E, -DAY_T, ws1.Range("A1:A20"))
 strShtName.Cells(Row, 5).Value = DAY_S

'Excelに完了予定日をセットする。
 Days = strShtName.Cells(Row, 3).Value
 strShtName.Cells(Row, 6).Value = WorksheetFunction.WorkDay(DAY_S, Days, ws1.Range("A1:A20"))

 Row = Row + 1
 PartNo = strShtName.Cells(Row, 2).Value

 Loop
 On Error GoTo 0

End Sub