兵┃法┃に┃学┃ぶ┃！┃(創刊 2003年12月15日）
━┛━┛━┛━┛━┛━┛
『┃企┃業┃シ┃ス┃テ┃ム┃戦┃略┃家┃』┃養┃成┃講┃座┃
━┛━┛━┛━┛━┛━┛━┛━┛━┛━┛━┛━┛━┛━┛━┛
【Vol.864】2021/5/24
企業システム戦略研究会
http://www.kigyo-systems.com/about.html
【Excelで簡易日程計算】
BOMを使って簡易的に日程計算するための
Excel/VBAを紹介します。
◆元のデータ
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 = 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
いかがでしたか。
なお、この例のように完了予定日から
作業日数を遡って、開始予定日を計算する方法を
「バックワードスケジューリング」と言います。
納期が決まってから、各作業をいつ開始するのか
を計画する場合に有効です。
逆に、開始予定日をから作業日数を進めて
完了予定日を計算する方法を
「フォワードスケジューリング」と言います。
作業開始予定から納期回答（見積）を行う
場合に有効です。
また、このプログラムは固定リードタイムによる
古典的な日程計算ですが、
高度なスケジューラーでは、
設備の能力や競合なども考慮して
最適な日程を計算します。
スケジューラーが正しい計算をするにも
BOMの精度が求められます。
ITを活用して、いつ、何をすべきかを
正しく見極めるには、
その元となるBOMのようなマスターデータが
正しくなっている必要があります。
良い道具があっても、
正しい情報が無いと戦いには勝てないですね。
*********************************************
孫子の兵法 虚実篇
故に戦いの地を知り戦いの日を知れば
https://www.kigyo-systems.com/mag/Vol00253.html
*********************************************
付記：BOMデータは製品構成の階層順、
（工程としては下から上）になっていますが、
WBSデータのように作業が上から下へ時系列順に
なっていても、延べ日数は同様のロジックで
計算が可能です。
ただし、その場合は開始予定日より、
上から下への作業順に延べ日数が加算累計され、
各作業の完了予定日を出力することになるので
フォワードスケジューリングになります。
■自動でしゃべるパワーポイント（VBAマクロ）
ノート欄のテキストを音声合成エンジンが読み上げ、
画面も自動で切り替わるVBAマクロです！
音声を録音するよりとっても楽です。
失敗して録音をやり直す手間もなく、
部分的な変更も楽々です。
実際のVBAコードやサンプルデモ。
https://www.kigyo-systems.com/tools.html
■間違いだらけのシステム構築（無料冊子ダウンロード）
～企業システムの強化書～
https://www.kigyo-systems.com/books/book.html
■『企業システム戦略』入門【無料講座】
http://www.kigyo-systems.com/ask3.html#form
■１２０社１０００人以上が受講！！
経験と勘に頼ったレビューからの脱却！
レビューを体系的に学び、成果につなげる！
『転ばぬ先の杖－ドキュメントレビュー』
http://www.kigyo-systems.com/ask4.html
『賢者は他者の経験に学ぶ－危険予知訓練』
http://www.kigyo-systems.com/ask33.html
『オーダーメイドの社内研修』
http://www.kigyo-systems.com\sch.html
『教材』※無料サンプルあり
http://www.kigyo-systems.com\buy.html
＜講演実績＞
https://www.kigyo-systems.com/ask3semi.html#jisseki
■『企業システム戦略』コンサルティングサービス
業務標準化とマニュアル作成から始める
http://www.kigyo-systems.com\ask3.html
■『企業システム戦略』の実践に役立つ書籍。
http://www.kigyo-systems.com/bookix.html
◆『仕様書・設計書に潜むリスクを中心とした
ドキュメント・レビュー!!実践とチェックポイント』
◆『100の失敗事例に学ぶ !! ITプロジェクトの危険予知訓練』
補助教材：IT危険予知訓練ツール
http://www.kigyo-systems.com/tools.html
◆『兵法に学ぶ !!ITプロマネの極意』
その他の「おすすめの本」
http://www.kigyo-systems.com/books.html
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
企業システム戦略研究会オフィシャルブログ
http://ameblo.jp/kigyo-systems/
Facebook
https://www.facebook.com/kigyosystems
Ttwitter
https://twitter.com/senryakuka
研究テーマ
「BRMS* によるスマート・エンタープライズ実現
と企業価値最大化に関する研究」
*Business Rule Management System
Research theme
「Study on the Smart Enterprise Implementation
and Maximize Corporate Value by BRMS」
経済産業大臣認定情報処理技術者
システムアナリスト、プロジェクトマネージャ、システム監査技術者
日本ITストラテジスト協会会員
日本生産管理学会会員、標準化研究学会会員
読者登録・配信中止
http://www.kigyo-systems.com/mag.html