2022年11月6日これまで適正在庫シミュレーションのやり方を数回に渡って解説してきましたが、これだけでは実用に足りないということにお気づきでしょうか?【無料サンプル付き】現場を納得させる適正在庫シミュレーションをエクセルで作る方法ではエクセルシート上で、エクセル関数だけを使って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”シート上の各セルには下記のように入力しておきます。リードタイムの3つは安全在庫や需要予測在庫を計算するための条件ですので、自由にユーザーが変えられるようにしておきます。その他のセルは、最終的にすべて在庫補充目標量を求めるための計算式になります。このような計算式になるのかは、下記のサイトで解説していますので参考にして下さい。安全在庫の役割りと標準偏差を使った計算方法をわかりやすく解説します。適正在庫にするための計算式【リードタイムや発注サイクルが長い場合】マクロ(VBA)を作成する変数を定義するさてここまできたところで、いよいよマクロプログラムを書いていきます。マクロ名をMain2として新しいモジュールを追加します。次にConditionシートに入力してある8つの数値を代入するために、8つの変数を定義します。発注間隔と納品リードタイムは整数なので”Long”で、その他は小数になる可能性がありますので”Double”で定義します。クリックすると拡大します次に定義した8つの変数にConditionシートの数値を順に代入していきます。Conditionシートを選択します。Worksheets(“Condition").Select次に許容欠品率の数値は(3,3)セルに入力されていますので、変数KEPPIN_RITSUに代入します。3)以下同様に7つの変数に数値を代入するために、下記のようにプログラミングしていきます。クリックすると拡大します前の計算データを削除する次に計算シートである“Work”シートを選択し、前回に行ったシミュレーションデータを削除します。前回にマクロで入力されたセルに、今回のシミュレーションでは入力されない可能性があり、これを行わないと前回のデータが残ってしまう可能性があるためです。A列とB列は日付と売上データが入りますのでそのままにして、それ以外に計算値が入る部分(3,3)セルから(500,16)セルまでを削除しておきます。クリックすると拡大しますさてここからが本番です。在庫補充目標量を書き込むやりたいことは、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シートの(I,3)セルにはConditionシートの(10,3)セルを逐次書き込んでいきます。毎回WorkシートとConditionシートを切り替えるのはマクロと言えども時間がかかります。始めにConditionシートの(10,3)セルの値をWorkシートの(2,3)セルに書き込んでおき、この値を(I,3)セルに書き込んでいくことにします。Iと書くことによって、(48,3)セルから(122,3)セルまで(2,3)セルの値が書き込まれるようにするということです。これで(I,3)セルの値が書き込まれました。倉庫内在庫量を書き込む次は(I,4)セルです。当日の倉庫内在庫=前日の倉庫内在庫―当日の出荷量(=売上)+当日の入荷量で計算できます。16))と書くことで計算値が書き込まれます。発注量を書き込む次は(I,5)セルです。これは発注量ですので、発注間隔が毎日であれば毎日計算して書き込みますが、発注間隔が3日であれば3日に1回だけ計算すればよいものです。これを判断するためにMod関数を使います。<Mod関数についてはこちら>エクセルマクロで場合分けしたデータ数をカウントする方法をわかりやすく解説つまり発注間隔が3日であれば、Iを3で割って余りがゼロになる日だけ発注量を計算するのです。Iここで発注量の計算に注意が必要です。発注量は在庫補充目標量と現在の在庫量との差で計算できますが、現在の在庫量として倉庫内在庫だけでなく未入荷在庫もすべて加えないといけません。リードタイムが7日であれば、6日分の未入荷在庫をすべて加えるということです。ついてはこちら>エクセルマクロで「合計」を計算する2通りの方法をわかりやすく解説また現在の在庫量が在庫補充目標量より大きい場合、発注量がマイナスになってしまいます。この場合は勿論発注しませんので、次の式により発注量をゼロに設定します。If未入荷在庫と入荷量を書き込む次に未入荷在庫と入荷量(I,6)から(I,16)を計算します。今回は未入荷在庫の列を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の列に戻らないといけませんので、カウンターを使います。<カウンターについてはこちら>エクセルマクロで「合計」を計算する2通りの方法をわかりやすく解説これをするには”MINYUKA”という変数(この変数名は何でもいいです)を定義します。下記のようにMod関数を使うことによってMINYUKAが11の時は1に、21の時も1に戻すことができます。MINYUKAは常に10で割った時の余りになるからです。Ifここまでこれば、(I,5)セルに書き込まれた発注量を未入荷在庫と入荷量に書き込むことができます。MINYUKA)セルにまで書き込まれることになります。16)セルに入荷量が書き込まれます。マクロ(VBA)を実行する以上をまとめると、下記のようなマクロプログラムになります。Subこのマクロを実行すると、発注間隔1日でリードタイムが3日の場合のシミュレーション結果は下図のようになります。クリックすると拡大しますまた発注間隔3日でリードタイムが5日の場合のシミュレーション結果は下図のようになります。クリックすると拡大しますこのように発注間隔やリードタイムの設定条件を自由に変えて、汎用的なシミュレーションができるようになりました。需給調整/業務改善の求人数が多いのはここだ!まとめ最後は少し難しく感じたかもしれませんが、他人のプログラムを読むのはそんなものですので安心して下さい。For~Next、If~Then~Else、Mod関数を理解していれば、決して理解不能ではないはずですので頑張って読んでみて下さい!今回のファイルは無料でダウンロードできます。フリーダウンロードはこちらさてここまでで適正在庫シミュレーションができるようになりましたが、これを発展させると倉庫コストと輸配送コストを含めた物流ネットワーク全体の最適化シミュレーションまで可能になります。ソリューション開発、物流コンサルティングの場で応用できます。VBA[第4弾]大量ブック・大量シート操作編!50個のExcelブックを一気にデータ収集するマクロ3時間講義シリーズ延べ5万人が学んだExcelマクロVBA講座の人気シリーズから待望の[第4弾]が登場!今度のマクロは、大量のExcelブック、大量のワークシートからデータ収集。本格実用マクロを手を動かして作りながら学ぶVBAプログラミング。基礎を効率的に学べるベーシック講座です。ついて豊富な図を用いて説明していきます。...【Pythonで実装する】適正在庫シミュレーションソフトを作ってみた。【区間推定の使い方】タイヤの摩耗を具体例にわかりやすく解説します。TOP28【作業分析6】MMCモデル|最適なトラックドック数をSimpyでシミュレーション【作業分析5】トラックの待機台数の多さを人の表情でアニメーション(2)カンボジアの物流でお困りのことがあればお気軽にご相談下さい。学生時代にアジアの雑踏にハマる。誰もが羨むメーカーを退職し「旅するように働く」ことを目指し物流業界に転職。理工学と経営学のダブル修士。2023年1月からカンボジアのオートレスビーチ在住。