【Excel VBA初心者向け】エクセルシートに入力してコピペするまでを自動化する
適正在庫のシミュレーションや輸配送のシミュレーションは、専用のソフトウェアがなくてもエクセルでかなりのことまで可能です。
但し、きめ細かなシミュレーションをするには膨大なデータを必要としますので、人手では追いつかないこともあります。
そのような時に、エクセルマクロ（ＶＢＡ）を使って自動化できる人は最強です。
エクセルマクロというと、「私には無理」と拒否反応を示す人が多いのですが、最低限の知識とコツさえ掴んでしまえば、それほど難しくはありません。
むしろ、こんな便利な道具を使えないでいることは、今後の物流人生で大変な損失になるとさえ思います。
今回は物流シミュレーションで必要となるエクセルマクロの基本中の基本を解説します。
Excel VBAを使えるようにする
まずエクセルの初期状態ではマクロが入ったファイルを開けませんので、エクセルの設定を変えておきます。
下記のサイトに設定のやり方が書かれてますので、参考にしてみて下さい。
【参考】
【エクセル初心者向け】VBAマクロを有効化するための初期設定
なお最初にお断りしておきますが、筆者のパソコンは英語仕様になっていますので、エクセルのメニューも英語表示です。
日本語版でも英語版でもメニューの表示順序は同じですので、日本語メニューを想像しながら読み進めて下さい。
まずは、エクセルのメニューにDeveloperタブ（日本語版だと開発タブ）を表示させます。
Developerタブはマクロプログラムを編集する画面（Visual Basic Editor）を呼び出す時に使います。
Fileをクリックし、次にOptionをクリックします。
次にCustomize Ribbonをクリックした後、Developerにチェックを入れ、OKをクリックします。
するとメニューにDeveloperタブが追加されます。
Excel VBAでエクセルシートに数字を自動入力
Excel VBAが使えるようになったところで、まずは簡単なマクロを作成して、どのようにプログラムが実行されるのかを見てみましょう。
まずは、プログラムを書く白紙の画面を呼び出します。
Developer >> Visual Basicをクリックします。
すると、次のようにマクロプログラムを編集する画面（Visual Basic Editor）が立ち上がります。
次にInsert >> Moduleをクリックします。
するとExcel VBAプログラムを入力する画面が表示されます。
それでは、プログラムを入力していきます。
まずは、”Sub test()”と入力します。
続いてEnterキーを押すと、下図のように1行置いて、”End Sub”と自動入力されます。
これは「test」という名前のプログラムを定義するためのおまじないのようなもので、Sub test()とEnd Subの間にプログラムを書いていくことになります。
次にプログラムを入力する箇所に、下図のように入力してみます。
このプログラム命令の意味は、エクセルシートの（１，１）セルから（１０，１）セルまで１を入力しなさいということです。
（１，１）セルとはA1セルで、（１０，１）セルはA10セルのことです。
カッコの中は、行を先に、列を後に書く決まりです。
これでプログラムは完成です。
画面下のエクセルのアイコンにカーソルを合わせると、プログラム画面とエクセルシートを切り替えられるようになっていますので、エクセルシートに切り替えます。
エクセルシートに画面が切り替わったら、下図のようにView >> Macros >> View Macrosをクリックします。
すると下記のポップアップ画面が出てきますので、先ほど作った「test」プログラムが選択されていることを確認してRunをクリックします。
すると下図のように、一瞬でA1セルからA10セルに1が入力されます。
これはエクセルシートにA1セルからA10セルまで1を入力する作業を自動化しています。
これだけでなく、人がエクセル上で行う操作は、ほぼすべて自動化できるのがマクロの凄いところです。
また、一つの作業を自動化するプログラムは1種類ではなく、色々なプログラムの仕方があります。
先ほどの例ですと、下記のようにも書くことができます。
これはFor～Next処理と呼ばれ、繰り返し処理を行うプログラムです。
物流のシミュレーションで非常に多く使う処理ですので、覚えておくと便利です。
簡単に言うと、ForとNextで囲まれた式のIに最初は１を入れて実行して、次に２を入れて実行して、、、最後に１０を入れて実行します。
最初にIに１を入れて実行するということは、
Range(Cells(1, 1), Cells(1, 1)) = 1
を実行することですので、（１，１）セル、つまりA1セルに１を入力します。
次にIに２を入れて実行するということは、
Range(Cells(2, 1), Cells(2, 1)) = 1
を実行することですので、（２，１）セル、つまりA2セルに１が入力されます。
これを１０まで繰り返しますので、A1セルからA10セルまで１が入力されるのです。
最初に書いたプログラム
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つずつ入力されます。
コンピューターの処理が早いために、どちらも一瞬で処理されてしまいますが。。。
Excel VBAで自動でコピペ
このFor～Next処理のようにマクロには沢山の処理文がありますが、よく使うものは限られていますので少しずつ覚えていけば大丈夫です。
行いたい処理をネットで検索すれば、解説サイトはいくらでも出てきます。
例えばコピペの処理文を調べたい場合は、Googleで「Excel VBA コピペ」と検索すれば沢山出てきます。
A1セルからA10セルをB1セルからB10セルに値だけをコピペする処理は、次のように書かれています。
Range(Cells(1, 1), Cells(10, 1)).Copy
Cells(1, 2).PasteSpecial
Application.CutCopyMode = False
このようにネットで調べても良いのですが、Excel VBAにはもっと便利な機能があります。
それは「自分でやったエクセルの処理をExcel VBAプログラムに自動変換」してくれる機能です。
この機能を使って、A1セルからA10セルをB1セルからB10セルに値だけをコピペする処理をExcel VBAプログラムにしてみましょう。
下図のようにView >> Macros >> Record Macro…をクリックします。
これからの操作をExcel VBAに記録（変換）しますよという意味です。
すると下図のようなポップアップ画面が現れますので、OKをクリックします。
これにより、以降のエクセル操作がすべて「Macro1」というExcel VBAプログラムに変換されます。
次にA1セルからA10セルをコピーして、B1セルに値だけを貼り付けます。
物流のシミュレーションでは計算結果をコピペするケースが多いため、そのまま貼り付けしてしまうと計算の参照先が変わってしまい、違った計算結果になることが多いため、値だけ貼り付けを使うことが多いです。
値だけを貼り付けたら、下図のようにView >> Macros >> Stop RecordingをクリックしてExcel VBAの記録を終了されます。
ここからの処理はExcel VBAプログラムに変換されません。
次に実際に記録されたExcel VBAプログラムを見てみます。
View >> Macros >> View Macrosをクリックします。
すると下図のようなポップアップ画面が現れますので、先ほど記録されたMacro1のプログラムが選択されていることを確認してEditをクリックします。
RunをクリックするとMacro1が実行されてしまいますので気を付けて下さい。
するとMacro1という名前のプログラムとして下記のように記録されていることが分かります。
これは先ほどのエクセル操作をExcel VBAプログラムに変換したものです。
中身を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
はコピー操作を無効にしています。
どういうことかと言いますと、コピー＆貼り付けをしても、コピーが有効のままだと下図のようにコピーされた部分が点線で囲まれています。
この点線を消す命令です。
以上のように、エクセル上でやる操作をどうやってプロラム文にするか分からない場合には、自分でやる操作をExcel VBAに記録してから、その処理文をコピペして使えばよいので便利です。
Udemyの関連講座
Excel VBA[第4弾]大量ブック・大量シート操作編！50個のExcelブックを一気にデータ収集するマクロ3時間講義
シリーズ延べ5万人が学んだExcelマクロVBA講座の人気シリーズから待望の[第4弾]が登場！今度のマクロは、大量のExcelブック、大量のワークシートからデータ収集。本格実用マクロを手を動かして作りながら学ぶVBAプログラミング。
いちばん理解できる統計学ベーシック講座その１【確率分布・推定・検定】
統計学の基礎を効率的に学べるベーシック講座です。統計学の入り口となる「確率分布・推定・検定」について豊富な図を用いて説明していきます。統計学や確率思考という一生モノのスキルを一緒に身につけましょう！