【VBAサンプル付き】適正在庫シミュレーションをエクセルマクロで自動化（前編）
前々回は専用のソフトを使わずに、エクセルで適正在庫シミュレーションが簡単にできる方法を紹介しました。
>>【無料サンプル付き】エクセルで超簡単！適正在庫シミュレーション！誰でもシミュレーションできる方法
でも、このシミュレーションを何百アイテムも繰り返すのは、さすがに骨が折れます。
今回は、それをエクセルマクロ（VBA）を使って解決します。
エクセルマクロやVBAというと「それは私には無理」と思われそうですが、要点だけ抑えればそれほど難しくありません。
エクセルマクロを使えるようになると、適正在庫のシミュレーションだけでなく、輸送費も含めたトータル物流費のシミュレーションもできるようになりますし、何より面倒な作業を自動化できて大変便利です。
【マクロ初心者向け】物流シミュレーションをするためにこれだけは知っておきたいこと
で、物流シミュレーションで最低限必要になるマクロの基本中の基本を解説しましたので、今回はまず最も簡単にシミュレーションを自動化する方法を解説します。
ページの最後でサンプルファイルを無料でダウンロードできます。
適正在庫シミュレーションで自動化したいこと
まずはどのように自動化したいのか、目的をはっきりさせておきます。
前回は次のようなシミュレーションを作成しました。
これはある1商品アイテムについてのシミュレーションですので、他のアイテムについても行う場合には、売上データを商品アイテムごとに変更してやる必要があります。
そのためには、別のシートにアイテムごとの売上データを入力しておいて、そこから上図の計算シートにコピペしてこればよいでしょう。
また、他のアイテムのシミュレーションを行うと、前のアイテムのシミュレーション結果が上書きされてしまいますので、シミュレーション結果を別のシートに貯めていく必要もあります。
まずは、これらを自動化してみます。
売上データを下図のようにSales dataシートに入力します。
10アイテムにつき、1月1日から4月30日までの売上データが入力されています。
【無料サンプル付き】エクセルで超簡単！適正在庫シミュレーション！誰でもシミュレーションできる方法
で作成した1アイテムについての適正在庫シミュレーションシートの名前をCalculationとします。
売上の列には、Sales dataシートから1アイテム分ずつコピペしてこれば、そのアイテムについてのシミュレーション結果が計算されます。
シミュレーション結果が計算されたら、倉庫内在庫の計算結果（2月15日から4月30日まで）を次のようにResultシートにアイテムごとにコピペしていきます。
こうすることにより、10アイテムについて在庫推移のシミュレーション結果が自動的にResultシートに書き込まれます。
マクロ（VBA）を使える状態にする
マクロプログラム画面（VBAエディタ）を表示するために、Developer >> Visual Basicをクリックします。
するとVBAエディタが表示されますので、Insert >> Moduleをクリックします。
すると、下図のように白紙のプログラミング画面が開きます。
今回はTestというマクロ名でプログラムを作っていきます。
下図のように入力してEnterキーを押します。
すると下図のように表示されますので、SubとEnd Subの間にプログラムを書いていきます。
マクロ（VBA)を作成する
入力データを読み込む
このマクロでやりたいことは、まずSales dataシートのB列にある1アイテム目の売上データを、CalculationシートのB列にコピペしてくることです。
マクロを実行させる時、どこのシートから実行するか分かりませんので、確実にSales dataシートからデータをコピーしてこれるように、Sales dataシートに切り替える命令文を入力します。
これがシートを切り替える命令文になります。
続いて、最初のアイテムの1月1日から4月30日までの売上データを選択してコピーします。
（２，２）セルから（１２１，２）セルに売上データがありますので、それをSelectで選択して、その部分をコピーするという意味です。
次に、コピーした売上データをCalculationシートの売上欄にペーストします。
違うシートにペーストしますので、CalculationシートをSelectします。
続いて（６，２）セルから（１２５，２）セルにペーストしますので、
Range(Cells(6, 2), Cells(125, 2)).Select
でもよいのですが、（６，２）セル一点にペーストしても結果は一緒ですので、（６，２）セルをSelectして値を貼り付けています。
結果データを書き込む
これで売上データが新しいものに更新されましたので、エクセル関数により在庫や発注量などのデータが更新されます。
これらのうち倉庫内在庫データをResultシートにコピペします。
下図で赤字の部分です。
画面では切れていますが、（５１，４）セルから（１２５，４）セルまでをコピペします。
売上データをCalculationシートにコピペしたのと同じ要領で、在庫データをResultシートにコピペします。
最後に、Resultシートにアイテムコードが載っていないと、どのアイテムの結果データなのか分かりませんので、Sales dataシートからコピペしておきます。
それではここまでを実行してみましょう。
マクロ名はtestですから、下記のように実行します。
Developer >> Macrosをクリックします。
マクロ名testを選択し、Runをクリックします。
すると、結果は下図のようになります。
Resultシートに1アイテム目のシミュレーション結果が書き込まれましたね。
しかし、私たちがやりたいのは、10アイテムのシミュレーション結果を一括で行うことです。
For～Nextで繰り返し処理する
それを行わせるには、前回に解説したFor～Next処理で行います。
>> マクロ初心者向け｜物流シミュレーションをするためにこれだけは知っておきたいこと
Sales dataシートで、1アイテム目の売上データは（２，２）セルから（１２１，２）セルに入っていましたね。
これをコピーするのは、
Range(Cells(2, 2), Cells(121, 2)).Select
Selection.Copy
と書きます。
2アイテム目をコピーする時には
Range(Cells(2, 3), Cells(121, 3)).Select
Selection.Copy
3アイテム目をコピーするときは
Range(Cells(2, 4), Cells(121, 4)).Select
Selection.Copy
となります。
このようにアイテムごとにプログラムを書くのは面倒です。
このような時はFor～Nextを使って、まとめて
For I=2 to 11
Range(Cells(2, I), Cells(121, I)).Select
Selection.Copy
Next I
のように書けます。
ここでIが2から11となっているのは、1アイテム目のデータが2列目に入っていて、10アイテム目のデータが11列目に入っているためです。
ペースト先はアイテムが変わっても、Calculation シート上の（６，２）セル一か所ですので、Iで変化させる必要はありません。
結果データをResultシートにコピペする時は、アイテムが変わってもコピー元は（５１，４）セルから（１２５，４）セルまでで変わりませんので、Iを使う必要はありません。
しかしペースト先は2列目から11列目で変わりますので、Iを使って次のように書くことができます。
Range(Cells(2, I), Cells(2, I)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
まとめると下図のようになります。
最初に
Dim I As Long
が書かれていますが、Iという変数に整数が入りますよということを宣言しています。
今回の場合は２から１１まですべて整数ですので、このように宣言します。
ちなみに変数Iに1.5などの小数が入る場合には、Longの代わりにDoubleを使い、仮に「住所」などの文字列が入る場合にはStringを使います。
マクロ（VBA)を実行する
このマクロを実行すると、下図のように10アイテムについてのシミュレーションデータが一瞬のうちに表示されます。
これらのデータは適正発注をした場合の在庫推移を表していますので、グラフにして視覚的に示すのもよし、平均値を求めて今の在庫量と比較して在庫削減量を示すのもあなた次第です。
Pythonで複数SKUの適正在庫シミュレーションを自動化
このシミュレーションはPythonで実装すると、更に簡単、かつ高速に行うことができます。
興味のある方は、こちらもご覧下さい。
行列演算を使った適正在庫シミュレーションをPythonで実装する【複数SKU用】
まとめ
いかがでしたでしょうか？
今回は10アイテムのシミュレーションを行いましたが、1,000アイテムになっても、10,000アイテムになってもプログラムの行数は同じです。
For～Nextで繰り返し処理を行いますので、
For I = 2 To 11
の11を1,000や10,000に書き換えるたけで、大量の計算を自動でやってくれます。
勿論、パソコンでの計算には時間がかかりますが、あなたが寝る前にエクセルマクロを実行させておけば、100,000アイテムのシミュレーションでも、翌朝寝覚めた時には終わっているでしょう。
これがエクセルマクロの威力で、大量のデータを使って繰り返し処理を行う物流シミュレーションに大変適しています。
少し難しいと思った方、安心して下さい。
今回作ったファイルを公開しますので、ご自由にお使いください。
しかし、これはエクセルマクロの威力のほんの序の口です。
こちらで更に実践的なシミュレーションを行う方法を解説しています。
【VBAサンプル付き】適正在庫シミュレーションをエクセルマクロで自動化（後編）
【サンプル付き】最低発注ロットを考慮した在庫シミュレーションの作り方 by Excel VBA
【季節変動を移動平均で補正する】適正在庫シミュレーション by Excel VBA