【VBAサンプル付き】適正在庫シミュレーションをエクセルマクロで自動化（後編）
これまで適正在庫シミュレーションのやり方を数回に渡って解説してきましたが、これだけでは実用に足りないということにお気づきでしょうか？
【無料サンプル付き】現場を納得させる適正在庫シミュレーションをエクセルで作る方法
ではエクセルシート上で、エクセル関数だけを使って1アイテムについてシミュレーションする方法を解説しました。
続いて、
【VBAサンプル付き】適正在庫シミュレーションをエクセルマクロで自動化（前編）
では先のシミュレーションを複数アイテムについて行うために、マクロを使って自動化する方法を解説しました。
しかし、これらの方法では、リードタイムや発注間隔を変えてシミュレーションしたい場合に、エクセルシート上で関数を人手で変更しないといけません。
つまり汎用的でないのです。
今回は、この問題をマクロ(VBA)を使って解決します。
前回までの2回に渡って、物流シミュレーションに最低限必要な3つのマクロの機能について解説しました。
それは繰り返し処理（For～Next）、場合分け（If～Then～Else）、算術計算でした。
これらを理解していれば、この問題を解決できます。
このページの最後からサンプルファイルを無料ダウンロードできます。
今までの適正在庫シミュレーションで足りない機能
以前、エクセルシート上で行ったシミュレーションは、下図のようにリードタイム3日、発注間隔1日を条件としていました。
リードタイムが3日でしたので、発注の翌日には未入荷在庫1になり、翌々日には未入荷在庫2になり、3日後に入荷となるように関数を入力していました。
ところがリードタイムが4日の条件に変わると、3日後まで未入荷在庫で、4日後に入荷となるように関数の入力を変える必要があります。
また発注間隔についても同様です。
毎日発注する条件でしたので発注量の列には毎日数値が入りますが、1日おきの発注になると2日に1日は発注量がゼロになります。
これらをエクセル関数で対応しようとすると、膨大な条件を記述する必要があり現実的でありません。
この課題をマクロで解決しようというのが今回の目的です。
条件設定シートを作成する
エクセルシート上でのシミュレーションでは、下図のように計算シート上に各種条件を入力していました。
計算を行うシートと条件を入力するシートは、分けておいた方がスッキリしますので、下図のように条件シートを”Condition”というシート名にして分けておきます。
そして計算シートは”Work”というシート名で、下図のように記入しておきます。
画面では下が切れていますが、4月30日までの売上データが122行まで入力されています。
ここで、先ほどの”Condition”シート上の各セルには下記のように入力しておきます。
C3（許容欠品率）＝1%（数値）
C4（発注間隔）＝1（数値）
C5（納品リードタイム）＝3（数値）
C6（売上平均）＝AVERAGE(Work!B3:B47)
C7（売上標準偏差）＝STDEV(Work!B3:B47)
C8（安全在庫）＝SQRT(C4+C5)*C7*NORMSINV(1-C3)
C9（需要予測在庫）＝C6*(C4+C5)
C10（在庫補充目標量）＝SUM(C8:C9)
許容欠品率、発注間隔、納品リードタイムの3つは安全在庫や需要予測在庫を計算するための条件ですので、自由にユーザーが変えられるようにしておきます。
その他のセルは、最終的にすべて在庫補充目標量を求めるための計算式になります。
なぜこのような計算式になるのかは、下記のサイトで解説していますので参考にして下さい。
【安全在庫の計算式】標準偏差と安全係数から計算する式をわかりやすく
適正在庫を維持するための発注数の決め方をわかりやすく【定期発注方式の場合】
マクロ（VBA）を作成する
変数を定義する
さてここまできたところで、いよいよマクロプログラムを書いていきます。
マクロ名をMain2として新しいモジュールを追加します。
次にConditionシートに入力してある8つの数値を代入するために、8つの変数を定義します。
発注間隔と納品リードタイムは整数なので”Long”で、その他は小数になる可能性がありますので”Double”で定義します。
次に定義した8つの変数にConditionシートの数値を順に代入していきます。
まずConditionシートを選択します。
Worksheets(“Condition").Select
次に許容欠品率の数値は（３，３）セルに入力されていますので、変数KEPPIN_RITSUに代入します。
KEPPIN_RITSU = Worksheets(“Condition").Cells(3, 3)
以下同様に7つの変数に数値を代入するために、下記のようにプログラミングしていきます。
前の計算データを削除する
次に計算シートである“Work”シートを選択し、前回に行ったシミュレーションデータを削除します。
前回にマクロで入力されたセルに、今回のシミュレーションでは入力されない可能性があり、これを行わないと前回のデータが残ってしまう可能性があるためです。
A列とB列は日付と売上データが入りますのでそのままにして、それ以外に計算値が入る部分（３，３）セルから（５００，１６）セルまでを削除しておきます。
さてここからが本番です。
在庫補充目標量を書き込む
やりたいことは、1月1日から2月14日までの売上データから在庫補充目標量を計算し、2月15日から4月30日までの在庫シミュレーションをすることです。
ということは、2月15日から4月30日までの在庫補充目標量は、2月14日までの売上データから計算された値で固定しますが、その他（D列からP列）はマクロで順に計算して埋めていくことになります。
このような繰り返し処理を行うにはFor～Nextを使うのでしたね。
2月15日のデータは48列で4月30日のデータは122列に入りますので、For～Nextの中でIを48から122まで変化させます。
まず、在庫補充目標量はConditionシートで計算された数値で固定しますので、Workシートの（Ｉ，３）セルにはConditionシートの（１０，３）セルを逐次書き込んでいきます。
しかし、毎回WorkシートとConditionシートを切り替えるのはマクロと言えども時間がかかります。
ですので、始めにConditionシートの（１０，３）セルの値をWorkシートの（２，３）セルに書き込んでおき、この値を（Ｉ，３）セルに書き込んでいくことにします。
つまり、Workシートの（２，３）セルには”=Condition!C10”を入力することによってConditionシートの（１０，３）セルの値を取り込んでおき、マクロに
For I = 48 To 122
Range(Cells(I, 3), Cells(I, 3)) = Range(Cells(2, 3), Cells(2, 3))
Next I
と書くことによって、（４８，３）セルから（１２２，３）セルまで（２，３）セルの値が書き込まれるようにするということです。
これで（Ｉ，３）セルの値が書き込まれました。
倉庫内在庫量を書き込む
次は（Ｉ，４）セルです。
これは倉庫内在庫量ですので、
当日の倉庫内在庫＝前日の倉庫内在庫―当日の出荷量（＝売上）＋当日の入荷量
で計算できます。
つまり（Ｉ，４）セルは
Range(Cells(I, 4), Cells(I, 4)) = Range(Cells(I – 1, 4), Cells(I – 1, 4)) – Range(Cells(I, 2), Cells(I, 2)) + Range(Cells(I, 16), Cells(I, 16))
と書くことで計算値が書き込まれます。
発注量を書き込む
次は（Ｉ，５）セルです。
これは発注量ですので、発注間隔が毎日であれば毎日計算して書き込みますが、発注間隔が3日であれば3日に1回だけ計算すればよいものです。
これを判断するためにMod関数を使います。
＜Mod関数についてはこちら＞
Excel VBAで場合分けしたデータ数をカウントする方法をわかりやすく解説
つまり発注間隔が3日であれば、Ｉを3で割って余りがゼロになる日だけ発注量を計算するのです。
つまり次のようになります。
For I = 48 To 122
Range(Cells(I, 4), Cells(I, 4)) = Range(Cells(I – 1, 4), Cells(I – 1, 4)) – Range(Cells(I, 2), Cells(I, 2)) + Range(Cells(I, 16), Cells(I, 16))：倉庫内在庫を計算する
If (I – 47) Mod ORDER_CYCLE = 0 Then：発注日かどうかを判定する
Range(Cells(I, 5), Cells(I, 5)) = Range(Cells(I, 3), Cells(I, 3)) – Range(Cells(I, 4), Cells(I, 4)) – WorksheetFunction.Sum(Range(Cells(I, 6), Cells(I, 15)))：発注日であれば発注量を計算する
Else
Range(Cells(I, 5), Cells(I, 5)) = 0：発注日でなければ発注量をゼロにする
End If
Next I
ここで発注量の計算に注意が必要です。
発注量は在庫補充目標量と現在の在庫量との差で計算できますが、現在の在庫量として倉庫内在庫だけでなく未入荷在庫もすべて加えないといけません。
リードタイムが7日であれば、6日分の未入荷在庫をすべて加えるということです。
＜WorksheetFunction.Sum についてはこちら＞
Excel VBAで「合計」を計算する２通りの方法をわかりやすく解説
また現在の在庫量が在庫補充目標量より大きい場合、発注量がマイナスになってしまいます。
この場合は勿論発注しませんので、次の式により発注量をゼロに設定します。
If Range(Cells(I, 5), Cells(I, 5)) < 0 Then
Range(Cells(I, 5), Cells(I, 5)) = 0
End If
未入荷在庫と入荷量を書き込む
次に未入荷在庫と入荷量（Ｉ，６）から（Ｉ，１６）を計算します。
今回は未入荷在庫の列を10列に分けています。
これは複数日の未入荷在庫が混ざらないようにするためです。
どういうことかと言いますと、未入荷在庫の列が5列しかない場合、もしリードタイムが7日であれば、6日目の未入荷在庫の計算式が複雑になってしまいますので、簡単にするために多めに取っています。
マクロでやりたいことは下図のようになります。
2月25日に2014個発注したとすると、2月26日から3月1日までは2014個が未入荷在庫1に入り、3月2日に入荷量となります。
同様に2月26日に発注された1676個は、2月27日から3月2日までは未入荷在庫2に入り、3月3日に入荷されます。
そして未入荷在庫10まで行ったら、次は未入荷在庫1の列に戻らないといけませんので、カウンターを使います。
＜カウンターについてはこちら＞
Excel VBAで「合計」を計算する２通りの方法をわかりやすく解説
これをするには”MINYUKA”という変数（この変数名は何でもいいです）を定義します。
そして、下記のようにMod関数を使うことによってMINYUKAが11の時は1に、21の時も1に戻すことができます。
なぜなら、MINYUKAは常に10で割った時の余りになるからです。
CNT = CNT + 1
MINYUKA = CNT Mod 10
If MINYUKA = 0 Then
MINYUKA = 10
End If
ここまでこれば、（Ｉ，５）セルに書き込まれた発注量を未入荷在庫と入荷量に書き込むことができます。
発注した数量は、
発注日の翌日
から
発注日にリードタイムを足した日の1日前
まで 未入荷在庫になり、
10列ある未入荷在庫の列のうち、MINYUKA列の列に書き込まれますので、
(I + 1, 5 + MINYUKA)セルから(I + LEAD_TIME – 1, 5 + MINYUKA)セルにまで書き込まれることになります。
つまり、
Range(Cells(I + 1, 5 + MINYUKA), Cells(I + LEAD_TIME – 1, 5 + MINYUKA)) = Range(Cells(I, 5), Cells(I, 5))
となります。
一方、（Ｉ，５）セルに書き込まれた発注量が入荷するのはリードタイム日後ですので、(I + LEAD_TIME, 16)セルに入荷量が書き込まれます。
つまり、
Range(Cells(I + LEAD_TIME, 16), Cells(I + LEAD_TIME, 16)) = Range(Cells(I, 5), Cells(I, 5))
となります。
マクロ（VBA）を実行する
以上をまとめると、下記のようなマクロプログラムになります。
Sub Main2()
'
Dim I As Long
Dim Row As Long
Dim KEPPIN_RITSU As Double
Dim ORDER_CYCLE As Long
Dim LEAD_TIME As Long
Dim HEIKIN As Double
Dim HENSA As Double
Dim SAFETY_STOCK As Double
Dim CYCLE_STOCK As Double
Dim HOJYU_MOKUHYO As Double
Dim CNT As Long
Dim MINYUKA As Long
Worksheets(“Condition").Select
KEPPIN_RITSU = Worksheets(“Condition").Cells(3, 3)
ORDER_CYCLE = Worksheets(“Condition").Cells(4, 3)
LEAD_TIME = Worksheets(“Condition").Cells(5, 3)
HEIKIN = Worksheets(“Condition").Cells(6, 3)
HENSA = Worksheets(“Condition").Cells(7, 3)
SAFETY_STOCK = Worksheets(“Condition").Cells(8, 3)
CYCLE_STOCK = Worksheets(“Condition").Cells(9, 3)
HOJYU_MOKUHYO = Worksheets(“Condition").Cells(10, 3)
Worksheets(“Work").Select
Range(“C3:P500").Select
Selection.ClearContents
Range(Cells(47, 4), Cells(47, 4)) = Range(Cells(2, 4), Cells(2, 4))
Row = Cells(Rows.Count, 2).End(xlUp).Row：売上データの最終行を取得
CNT = 0
For I = 48 To Row
Range(Cells(I, 3), Cells(I, 3)) = Range(Cells(2, 3), Cells(2, 3))
Range(Cells(I, 4), Cells(I, 4)) = Range(Cells(I – 1, 4), Cells(I – 1, 4)) – Range(Cells(I, 2), Cells(I, 2)) + Range(Cells(I, 16), Cells(I, 16))
If (I – 47) Mod ORDER_CYCLE = 0 Then
CNT = CNT + 1
MINYUKA = CNT Mod 10
If MINYUKA = 0 Then
MINYUKA = 10
End If
Range(Cells(I, 5), Cells(I, 5)) = Range(Cells(I, 3), Cells(I, 3)) – Range(Cells(I, 4), Cells(I, 4)) – WorksheetFunction.Sum(Range(Cells(I, 6), Cells(I, 15)))
If Range(Cells(I, 5), Cells(I, 5)) < 0 Then
Range(Cells(I, 5), Cells(I, 5)) = 0
End If
Range(Cells(I + 1, 5 + MINYUKA), Cells(I + LEAD_TIME – 1, 5 + MINYUKA)) = Range(Cells(I, 5), Cells(I, 5))
Range(Cells(I + LEAD_TIME, 16), Cells(I + LEAD_TIME, 16)) = Range(Cells(I, 5), Cells(I, 5))
Else
Range(Cells(I, 5), Cells(I, 5)) = 0
End If
Next I
'
End Sub
このマクロを実行すると、発注間隔1日でリードタイムが3日の場合のシミュレーション結果は下図のようになります。
また発注間隔3日でリードタイムが5日の場合のシミュレーション結果は下図のようになります。
このように発注間隔やリードタイムの設定条件を自由に変えて、汎用的なシミュレーションができるようになりました。
まとめ
最後は少し難しく感じたかもしれませんが、他人のプログラムを読むのはそんなものですので安心して下さい。
しかし、For～Next、If～Then～Else、Mod関数を理解していれば、決して理解不能ではないはずですので頑張って読んでみて下さい！
今回のファイルは無料でダウンロードできます。
さてここまでで適正在庫シミュレーションができるようになりましたが、これを発展させると倉庫コストと輸配送コストを含めた物流ネットワーク全体の最適化シミュレーションまで可能になります。
つまり、物流の上流企画ができるようになりますので、荷主企業での物流企画、物流会社でのソリューション開発、物流コンサルティングの場で応用できます。