【無料サンプル付き】マクロで簡単!自分でできる適正在庫シミュレーションの自動化!

2021年7月26日

前々回は専用のソフトを使わずに、エクセルで適正在庫シミュレーションが簡単にできる方法を紹介しました。

>>【無料サンプル付き】エクセルで超簡単!適正在庫シミュレーション!誰でもシミュレーションできる方法を教えます

 

でも、このシミュレーションを何百アイテムも繰り返すのは、さすがに骨が折れます。

今回は、それをエクセルマクロ(VBA)を使って解決します。

 

エクセルマクロやVBAというと「それは私には無理」と思われそうですが、要点だけ抑えればそれほど難しくありません。

エクセルマクロを使えるようになると、適正在庫のシミュレーションだけでなく、輸送費も含めたトータル物流費のシミュレーションもできるようになりますし、何でも面倒な作業を自動化できて大変便利です。

前回は物流シミュレーションで最低限必要になるマクロの基本中の基本を解説しましたので、今回はまず最も簡単にシミュレーションを自動化する方法を解説します。

>> マクロ初心者向け|物流シミュレーションをするためにこれだけは知っておきたいこと

 

ページの最後でサンプルファイルを無料でダウンロードできます。

 

何を自動化したいのか?

まずはどのように自動化したいのか、目的をはっきりさせておきます。

前回は下図のようなシミュレーションを作成しました。

 

これはある1商品アイテムについてのシミュレーションですので、他のアイテムについても行う場合には、売上データをアイテムごとに変更してやる必要があります。

そのためには、別のシートにアイテムごとの売上データを入力しておいて、そこから上図の計算シートにコピペしてこればよいでしょう。

 

また、他のアイテムのシミュレーションを行うと、前のアイテムのシミュレーション結果が上書きされてしまいますので、シミュレーション結果を別のシートに貯めていく必要もあります。

まずは、これらを自動化してみます。

 

売上データを下図のようにSales dataシートに入力します。

10アイテムにつき、1月1日から4月30日までの売上データが入力されています。

 

前々回に作成した1アイテムについての適正在庫シミュレーションシートの名前をCalculationとします。

売上の列には、Sales dataシートから1アイテム分ずつコピペしてこれば、そのアイテムについてのシミュレーション結果が計算されます。

 

シミュレーション結果が計算されたら、倉庫内在庫の計算結果(2月15日から4月30日まで)を次のようにResultシートにアイテムごとにコピペしていきます。

 

こうすることにより、10アイテムについて在庫推移のシミュレーション結果が自動的にResultシートに書き込まれます。

 

前準備をする

マクロプログラム画面(VBAエディタ)を表示するために、Developer >> Visual Basicをクリックします。

 

するとVBAエディタが表示されますので、Insert >> Moduleをクリックします。

 

すると、下図のように白紙のプログラミング画面が開きます。

 

今回はTestというマクロ名でプログラムを作っていきます。

下図のように入力してEnterキーを押します。

 

すると下図のように表示されますので、SubEnd Subの間にプログラムを書いていきます。

 

コピペをする

このマクロでやりたいことは、まずSales dataシートのB列にある1アイテム目の売上データを、CalculationシートのB列にコピペしてくることです。

マクロを実行させる時、どこのシートから実行するか分かりませんので、確実にSales dataシートからデータをコピーしてこれるように、Sales dataシートに切り替える命令文を入力します。

これがシートを切り替える命令文になります。

 

続いて、最初のアイテムの1月1日から4月30日までの売上データを選択してコピーします。

(2,2)セルから(121,2)セルに売上データがありますので、それをSelectで選択して、その部分をコピーするという意味です。

 

次に、コピーした売上データをCalculationシートの売上欄にペーストします。

 

違うシートにペーストしますので、CalculationシートをSelectします。

続いて(6,2)セルから(125,2)セルにペーストしますので、

Range(Cells(6, 2), Cells(125, 2)).Select

でもよいのですが、(6,2)セル一点にペーストしても結果は一緒ですので、(6,2)セルをSelectして値を貼り付けています。

 

これで売上データが新しいものに更新されましたので、エクセル関数により在庫や発注量などのデータが更新されます。

これらのうち倉庫内在庫データをResultシートにコピペします。

下図で赤字の部分です。

画面では切れていますが、(51,4)セルから(125,4)セルまでをコピペします。

 

売上データをCalculationシートにコピペしたのと同じ要領で、在庫データをResultシートにコピペします。

 

最後に、Resultシートにアイテムコードが載っていないと、どのアイテムの結果データなのか分かりませんので、Sales dataシートからコピペしておきます。

 

 

それではここまでを実行してみましょう。

マクロ名はtestですから、下記のように実行します。

Developer >> Macrosをクリックします。

 

マクロ名testを選択し、Runをクリックします。

 

すると、結果は下図のようになります。

Resultシートに1アイテム目のシミュレーション結果が書き込まれました。

しかし、私たちがやりたいのは、10アイテムのシミュレーション結果を一括で行うことです。

 

For~Nextで繰り返し処理

それを行わせるには、前回に解説したFor~Next処理で行います。

>> マクロ初心者向け|物流シミュレーションをするためにこれだけは知っておきたいこと

 

Sales dataシートで、1アイテム目の売上データは(2,2)セルから(121,2)セルに入っていましたね。

これをコピーするのは、

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 シート上の(6,2)セル一か所ですので、Iで変化させる必要はありません。

 

結果データをResultシートにコピペする時は、アイテムが変わってもコピー元は(51,4)セルから(125,4)セルまでで変わりませんので、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という変数に整数が入りますよということを宣言しています。

今回の場合は2から11まですべて整数ですので、このように宣言します。

ちなみに変数Iに1.5などの小数が入る場合には、Longの代わりにDoubleを使い、仮に「住所」などの文字列が入る場合にはStringを使います。

 

このマクロを実行すると、下図のように10アイテムについてのシミュレーションデータが一瞬のうちに表示されます。

 

これらのデータは適正発注をした場合の在庫推移を表していますので、グラフにして視覚的に示すのもよし、平均値を求めて今の在庫量と比較して在庫削減量を示すのもあなた次第です。

 

まとめ

いかがでしたでしょうか?

今回は10アイテムのシミュレーションを行いましたが、1,000アイテムになっても、10,000アイテムになってもプログラムの行数は同じです。

For~Nextで繰り返し処理を行いますので、

For I = 2 To 11

の11を1,000や10,000に書き換えるたけで、大量の計算を自動でやってくれます。

 

勿論、パソコンでの計算には時間がかかりますが、あなたが寝る前にエクセルマクロを実行させておけば、100,000アイテムのシミュレーションでも、翌朝寝覚めた時には終わっているでしょう。

これがエクセルマクロの威力で、大量のデータを使って繰り返し処理を行う物流シミュレーションに大変適しています。

少し難しいと思った方、安心して下さい。

今回作ったファイルを公開しますので、ご自由にお使いください。

 

しかし、これはエクセルマクロの威力のほんの序の口です。

次回以降は、更に実践的なシミュレーションを行う方法を紹介していきます。

【マクロで解決!】誰でも使える適正在庫シミュレーションの作り方

【エクセルマクロ】最低発注ロットを考慮した在庫シミュレーションの作り方

【エクセルマクロ】季節変動を考慮した在庫シミュレーションの作り方