簡易日程計算（Excel/VBA)

　製品構成展開されたツリーデータを使用して、製品を期日までに組立完成させるために必要な日程（延べ日数等）を計算するExcel/VBAの要点を説明します。

まず、Excelには以下のようにデータが入力されている前提です。
TOPから下位階層に向かって構成品があり、各階層の作業日数が決まっています。

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

階層,部番,日数,延べ日数,開始予定日,完了予定日
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,,,

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

　１行目から最下層のMateまで各階層の必要日数を遡って着手予定日、完了予定日を計算します。各階層までの延べ日数は配列に保持します。
　（ここが、ミソです。）

　Top（製品）をAssy1とAssy2から組み立てるのに5日必要です。従って、Topの組み立ては納期（完了予定日）の5日前から開始する必要があります。次にPart1とPart2からAssy1を組み立てるのに2日必要です。従ってAssy1の組み立てはAssy1の完了予定日から２日前に開始する必要があります。ここまでの延べ日数は7日です。Mate1からPart1を作るのに3日必要です。従って、Mate1はPart1完成予定日の3日前には必要です。ここまでの延べ日数は10日です。さらにMate1の調達日数は15日必要です。従って、Mate1の手配は15日前に必要です。ここまでの延べ日数は25日です。
　同様にTop→Assy1→Part2→Mate2の各階層までの開始予定日、完了予定日、延べ日数を計算します。以下、Top→Assy2→Part3→Mate1,Top→Assy2→Part4→Mate2も同様に計算します。

以下に日程を計算するVBAのサンプルコードと要点の説明を記載します。
コピーしてExcelのVBエディタに貼り付けて実行して下さい。

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

　いかがでしたか。
　ERPやPLMが無くても、数千行くらいのBOMであればこれで十分です。
　納期に間に合わせるためにいつから作業を開始して完了しなければならないか、延べ日数はどれくらいかなどを知ることができます。必要に応じて各階層に安全余裕日数（バッファ）を設けてもよいでしょう。
　しかし、この日程計算を手作業や関数で行うのは結構大変ですよね。

PR広告！TechAcademy [テックアカデミー] PR広告！システム開発業者を完全無料でご紹介します！【EMEAO!】