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

2021年6月13日

適正在庫のシミュレーションや輸配送のシミュレーションは、専用のソフトウェアがなくてもエクセルでかなりのことまで可能です。

但し、きめ細かなシミュレーションをするには膨大なデータを必要としますので、人手では追いつかないこともあります。

そのような時に、エクセルマクロ(VBA)を使って自動化できる人は最強です。

エクセルマクロというと、「私には無理」と拒否反応を示す人が多いのですが、最低限の知識とコツさえ掴んでしまえば、それほど難しくはありません。

むしろ、こんな便利な道具を使えないでいることは、今後の物流人生で大変な損失になるとさえ思います。

今回は物流シミュレーションで必要となるエクセルマクロの基本中の基本を解説します。

 

エクセルマクロを使えるようにする

まずエクセルの初期状態ではマクロが入ったファイルを開けませんので、エクセルの設定を変えておきます。

下記のサイトに設定のやり方が書かれてますので、参考にしてみて下さい。

 

【参考】

【エクセル初心者向け】VBAマクロを有効化するための初期設定

 

なお最初にお断りしておきますが、筆者のパソコンは英語仕様になっていますので、エクセルのメニューも英語表示です。

日本語版でも英語版でもメニューの表示順序は同じですので、日本語メニューを想像しながら読み進めて下さい。

 

まずは、エクセルのメニューにDeveloperタブ(日本語版だと開発タブ)を表示させます。

Developerタブはマクロプログラムを編集する画面(Visual Basic Editor)を呼び出す時に使います。

Fileをクリックし、次にOptionをクリックします。

 

 

次にCustomize Ribbonをクリックした後、Developerにチェックを入れ、OKをクリックします。

 

するとメニューにDeveloperタブが追加されます。

 

エクセルマクロでエクセルシートに数字を入力してみよう

マクロが使えるようになったところで、まずは簡単なマクロを作成して、どのようにプログラムが実行されるのかを見てみましょう。

まずは、プログラムを書く白紙の画面を呼び出します。

Developer >> Visual Basicをクリックします。

 

すると、次のようにマクロプログラムを編集する画面(Visual Basic Editor)が立ち上がります。

 

次にInsert >> Moduleをクリックします。

 

するとマクロプログラムを入力する画面が表示されます。

 

それでは、プログラムを入力していきます。

まずは、”Sub test()”と入力します。

続いてEnterキーを押すと、下図のように1行置いて、”End Sub”と自動入力されます。

 

これは「test」という名前のプログラムを定義するためのおまじないのようなもので、Sub test()とEnd Subの間にプログラムを書いていくことになります。

次にプログラムを入力する箇所に、下図のように入力してみます。

 

このプログラム命令の意味は、エクセルシートの(1,1)セルから(10,1)セルまで1を入力しなさいということです。

(1,1)セルとはA1セルで、(10,1)セルはA10セルのことです。

カッコの中は、行を先に、列を後に書く決まりです。

これでプログラムは完成です。

 

画面下のエクセルのアイコンにカーソルを合わせると、プログラム画面とエクセルシートを切り替えられるようになっていますので、エクセルシートに切り替えます。

 

エクセルシートに画面が切り替わったら、下図のようにView >> Macros >> View Macrosをクリックします。

 

すると下記のポップアップ画面が出てきますので、先ほど作った「test」プログラムが選択されていることを確認してRunをクリックします。

 

すると下図のように、一瞬でA1セルからA10セルに1が入力されます。

 

これはエクセルシートにA1セルからA10セルまで1を入力する作業を自動化しています。

これだけでなく、人がエクセル上で行う操作は、ほぼすべて自動化できるのがマクロの凄いところです。

 

また、一つの作業を自動化するプログラムは1種類ではなく、色々なプログラムの仕方があります。

先ほどの例ですと、下記のようにも書くことができます。

 

これはFor~Next処理と呼ばれ、繰り返し処理を行うプログラムです。

物流のシミュレーションで非常に多く使う処理ですので、覚えておくと便利です。

 

簡単に言うと、ForとNextで囲まれた式のIに最初は1を入れて実行して、次に2を入れて実行して、、、最後に10を入れて実行します。

最初にIに1を入れて実行するということは、

Range(Cells(1, 1), Cells(1, 1)) = 1

を実行することですので、(1,1)セル、つまりA1セルに1を入力します。

 

次にIに2を入れて実行するということは、

Range(Cells(2, 1), Cells(2, 1)) = 1

を実行することですので、(2,1)セル、つまりA2セルに1が入力されます。

 

これを10まで繰り返しますので、A1セルからA10セルまで1が入力されるのです。

 

最初に書いたプログラム

Range(Cells(1, 1), Cells(10, 1)) = 1

ではA1セルからA10セルまで一遍に1を入力しましたが、

For I = 1 To 10

Range(Cells(I, 1), Cells(I, 1)) = 1

Next

では、A1セルからA10セルまで1つずつ入力されます。

コンピューターの処理が早いために、どちらも一瞬で処理されてしまいますが。。。

 

エクセルマクロでコピペをしてみよう

このFor~Next処理のようにマクロには沢山の処理文がありますが、よく使うものは限られていますので少しずつ覚えていけば大丈夫です。

行いたい処理をネットで検索すれば、解説サイトはいくらでも出てきます。

 

例えばコピペの処理文を調べたい場合は、Googleで「マクロ コピペ」と検索すれば沢山出てきます。

 

A1セルからA10セルをB1セルからB10セルに値だけをコピペする処理は、次のように書かれています。

Range(Cells(1, 1), Cells(10, 1)).Copy

Cells(1, 2).PasteSpecial

Application.CutCopyMode = False

 

このようにネットで調べても良いのですが、マクロにはもっと便利な機能があります。

それは「自分でやったエクセルの処理をマクロプログラムに自動変換」してくれる機能です。

この機能を使って、A1セルからA10セルをB1セルからB10セルに値だけをコピペする処理をマクロプログラムにしてみましょう。

 

下図のようにView >> Macros >> Record Macro…をクリックします。

これからの操作をマクロに記録(変換)しますよという意味です。

 

すると下図のようなポップアップ画面が現れますので、OKをクリックします。

これにより、以降のエクセル操作がすべて「Macro1」というマクロプログラムに変換されます。

 

次にA1セルからA10セルをコピーして、B1セルに値だけを貼り付けます。

物流のシミュレーションでは計算結果をコピペするケースが多いため、そのまま貼り付けしてしまうと計算の参照先が変わってしまい、違った計算結果になることが多いため、値だけ貼り付けを使うことが多いです。

 

値だけを貼り付けたら、下図のようにView >> Macros >> Stop Recordingをクリックしてマクロの記録を終了されます。

ここからの処理はマクロプログラムに変換されません。

 

次に実際に記録されたマクロプログラムを見てみます。

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

 

すると下図のようなポップアップ画面が現れますので、先ほど記録されたMacro1のプログラムが選択されていることを確認してEditをクリックします。

RunをクリックするとMacro1が実行されてしまいますので気を付けて下さい。

 

するとMacro1という名前のプログラムとして下記のように記録されていることが分かります。

これは先ほどのエクセル操作をマクロプラムに変換したものです。

 

中身を1つずつ見ていきます。

Range(“A1:A10”).Select

はA1セルからA10セルを選択しています。

Range(Cells(1, 1), Cells(10, 1)).Select

と同じ意味です。

つまり同じ操作でも、異なる表現方法があるということです。

 

A1のようにセルを指定する方が簡単ですが、例えば1つ下のセルを選択することをプログラムに書きたい時には不便です。

その点、Cells(1,1)のようにセルを指定しておけば、Cells(1+1,1)のように1つ下のセルを簡単に表現できるので便利です。

 

次の

Selection.Copy

では先に指定したA1セルからA10セルの部分をコピーしなさいという命令文です。

 

次の

Range(“B1”).Select

Range(Cells(1, 2), Cells(1, 2)).Select

と同じ意味で、B1セルを選択しなさいという命令です。

 

次の

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

は少し長いですが、選択されたセルに値だけを貼り付けなさいという命令文です。

こういう命令文はそのままコピペして使えばいいので、詳しいパラメータまで理解する必要はありません。

 

最後の

Application.CutCopyMode = False

はコピー操作を無効にしています。

どういうことかと言いますと、コピー&貼り付けをしても、コピーが有効のままだと下図のようにコピーされた部分が点線で囲まれています。

この点線を消す命令です。

 

以上のように、エクセル上でやる操作をどうやってプロラム文にするか分からない場合には、自分でやる操作をマクロに記録してから、その処理文をコピペして使えばよいので便利です。

 

ここまでエクセルマクロの基本中の基本を解説してきました。

次回はいよいよエクセルマクロを使って、適正在庫シミュレーションの自動化をしていきます。