物流シミュレーションに必要な機能はたったこれだけ!最短距離でマクロを理解【算術計算】

2021年7月30日

マクロVBA)には色々な機能がありますが、物流のシミュレーションで使う機能は僅かです。

折角時間をかけて膨大な量のマクロの解説本やマニュアルを読んでも、そのほとんどは物流シミュレーションには必要のない機能です。

今回から2回に渡って、最短距離で物流シミュレーションができるようになるために、よく使う処理機能だけに絞って解説していきます。

 

具体的には、繰り返し処理算術計算のやり方場合分けについて押さえておけば、ほとんどの物流シミュレーションに対応できます。

繰り返し処理については、

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

の記事でFor~Next処理を解説しました。

その復習も兼ねて、第一回の今回は算術計算のやり方を解説していきます。

 

算術計算をやってみよう

下図はある商品の1月1日から1月20日までの売上データです。

この合計と平均を計算して、計算結果をシートに書き込むマクロを作成してみます。

 

まずは、For~Nextを使って合計を計算してみましょう。

エクセルシートで合計を求める場合にはSUM関数を使えば簡単ですが、違うやり方で計算してみます。

 

まずC1セルにB1セルと同じ値が来るように“=B1”と入力します。

次にC2セルに“=C1+B2”と入力します。

これによって、1月1日の売上と1月2日の売上が足されます。

続いてC2セルをC20セルまでコピペします。

すると、C列にはB列の累計値が計算され、C20セルはB1セルからB20までの合計値になります。

 

これをFor~Nextを使ってマクロで書くと次のようになります。

 

For~NextでIを1から20まで変化させますので、始めに

Dim I As Long

でIが整数であることを定義します。

 

次に累計値を入れる箱として、Goukeiという変数を定義します。

売上値は整数のため累計値も整数になりますので、

Dim Goukei As Long

と定義します。

この変数の名前は何にしてもよいので、後で自分がプログラムを見た時にわかり易い名前にしておきます。

 

次に累計値を入れる箱であるGoukei変数をゼロにリセットするために、、

Goukei=0

としておきます。

そしてこのGoukeiに売上データを上から順に加えていきます。

 

最初にGoukei (=0)に(1,2)セルの値を加えたものを、新たなGoukeiにします。

つまり、

となります。

 

次に新たなGoukei(=472)に(2,2)セルの値を加えたものが、新たなGoukeiになります。

これを最後の(20,2)セルまで繰り返すと、すべての売上の合計がGoukei変数に入ることになります。

それを一発で表す式が

For I = 1 To 20

Goukei = Goukei + Range(Cells(I, 2), Cells(I, 2))

Next I

となるのです。

 

最後に計算結果を同じシート上に表示するために、(1,4)セルに「Total:」を表示し、(1,5)セルにGoukei変数の値を表示させます。

Range(Cells(1, 4), Cells(1, 4)) = “Total :"

Range(Cells(1, 5), Cells(1, 5)) = Goukei

 

この時、

Range(Cells(1, 5), Cells(1, 5)) = “Goukei”

のように書くと、(1,5)セルにはGoukeiという文字が表示されるだけになってしまいますので注意して下さい。

Goukeiという変数に入っている値を表示させたい場合には、“ ”で囲まずに書きます。

 

さて、これで売上の合計を計算して表示するtest1というマクロ名のプログラムが完成しましたので、これを実行させてみましょう。

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

 

そして、出てくるポップアップ画面でtest1を選択してRunをクリックします。

 

すると下のように合計値が表示されました。

 

ここで一つ便利なマクロ関数(VBA関数)を紹介しておきます。

この例ではデータが20行までにしか入っていないことが分かっていましたので、

For I=1 to 20

のように書くことができましたが、データ数が変わるとその度に20を書き換える必要があり面倒です。

この時、データが何行目まで入っているかを調べる関数があると非常に便利です。

Cells(Rows.Count, 1).End(xlUp)

の関数がそれで、1列目(A列)に入っているデータの最終行を返してくれます。

 

従って、新たな変数、例えばRowという変数を

Dim Row as Long

のように最初に定義して、

Row=Cells(Rows.Count, 1).End(xlUp)

のように書けば、上の例ではRowに20が入ります。

 

ですので、

For I=1 to 20

の代わりに

For I=1 to Row

と書くことができて、データ数が100になっても1,000になってもマクロプログラムを書き換える必要がなくなり便利です。

 

さて、このようにして合計値をマクロで求めることができますが、エクセル関数のSUMがマクロでも使えるともっと便利ですよね。

実はそのためにマクロではエクセル関数を使えるようになっています。

Application.WorksheetFunction.

に続いてエクセル関数を書くだけで、マクロプログラムの中でエクセル関数を使えるようになります。

 

上の例では、(1,2)セルから(20,2)セルまで売上データが入っていますので、その合計を(2,5)セルに表示したい場合には、

Range(Cells(2, 5), Cells(2, 5)) = Application.WorksheetFunction.Sum(Range(Cells(1, 2), Cells(20, 2)))

と書けばOKです。

 

もし合計でなく平均値を求めたい場合には、

Range(Cells(2, 5), Cells(2, 5)) = Application.WorksheetFunction.Average(Range(Cells(1, 2), Cells(20, 2)))

と書けばOKです。

これは実に便利な機能ですので、覚えておいて下さい。

 

先ほどのFor~Nextを使って計算した合計と、エクセル関数を使って計算した合計の両方を表示されるプログラムを書くと下記のようになります。

 

そしてこれを実行すると次のようになります。

 

まとめ

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

For~Nextで合計を求めた時には、「マクロって面倒臭いなあ」と思われた方も、エクセル関数も使えるのなら簡単だと思われたのではないでしょうか。

 

確かにその通りなのですが、For~Nextは物流シミュレーションにおいて今後頻繁に使いますので、慣れておくと便利です。

 

第二回の次回は、For~Nextと同じくらい頻繁に使う場合分け(If~Then~Else)について解説していきます。