【過不足調整マクロ（1/2）】物品移動の調整を効率化してボタンひとつで！（その１：全体像）
当ブログで紹介するコードは筆者が独自に開発して自身の業務のみに使用しているものであり、会社の財産ではありません。また、コードや参考画像等の中で使用している単語や図表なども抽象化したものであり、業務で使用しているものではありません。
当記事ではNoteのシンタックスハイライトの仕様上、コメントアウトされたコードを「//」から始まる形で記述していますが、VBAでは本来「'」によって記述します。コードを準用する場合はご注意ください。
【参考】公式ドキュメント：コード内のコメント
今回から物品などの過不足調整用のマクロについて解説していきます。
年度初めなどに経費削減のため物品を全て新調するのではなく部署間などで循環させて使用するする企業様も多いでしょう。そんなさっさと終わらせたい調整業務の担当になってしまった読者様には、ぜひ役立てていただきたいです。
実際に筆者や筆者の前任担当者は、過不足調整の作業を毎回２時間以上かけて行なっていましたが、マクロ作成後はボタンひとつで終わらせることができるようになり、次のようなメリットがありました。
他部署の過不足の取りまとめの後、調整案の送付が劇的に速くなった。
同時期の他のタスクに時間を割けるようになり、残業が減った。
調整案を何パターンも作ることができ、最も最適化された案が作れるようになった。
第１回の当記事では、解決したい課題とマクロの全体像とについて解説していきます。
解決したい課題
手作業でやる場合の手順
今回の想定課題は、複数の部署間での作業服の過不足調整案の作成の自動化です。一旦、手作業でやっていく手順を見てみましょう。
次の画像のような、Sheet1のB2:I2を見出し行とし、各部署からとりまとめた作業服の過不足情報がレコードとして入っているテーブル1を扱います。
テーブルの内容を見ていくと、５行目で、B課でMサイズの上着が不足していることがわかります。よって、他のレコードでMサイズの上着が余剰しているところをみつけて、それぞれの「対応相手」列に値を入力します。
上記のような作業を繰り返していくと、対応相手があるレコードには全て値が入り、対応相手がない（26cmの靴が不足しているが他課のどこにも余っていなかった、Sサイズのシャツが余っているがどこも必要としていなかった…等）レコードは空欄のままの状態になります。
さらにここから、「不足」で対応相手がなかったレコードには「購入」、「余剰」で対応相手がなかったレコードには「保留」と値を入れていきます。
「不足」で対応相手なし → 「対応相手」列に「購入」
「余剰」で対応相手なし → 「対応相手」列に「保留」
その結果、全てのレコードの「対応相手」列に何らかの値が入り、過不足調整案の作成は完成になります。
目視確認と手作業でやるのは非常に大変
ここまで手作業の手順を説明してきましたが、非常に手間がかかって大変な作業です。挿入しているテーブルの画像は当ブログのために作成したサンプルであり、スクリーンショットですので30行ほどしか写っていませんが、全体で160レコードもあります。実務なら、もっとレコード数が増える可能性は大いにあるでしょう。これを手作業でやっていると、時間がかかるのはもちろんのこと、記述ミスも誘発します。対応相手の存在を見逃して仕舞えば、他の課で余剰があるのに新たに購入することになってしまい、最適な過不足調整ができず、そもそもこの業務の目的を100％達成することができなくなってしまいます。
目視確認・手作業で行うことによるデメリット
レコード数が多いほど、膨大な作業時間が必要
記述ミスを誘発する
対応相手の存在を見逃してしまい、最適な過不足調整ができない
だからこそ、このような作業はマクロによって自動化してしまうべきなのです。そのマクロは次のようなプログラムコードでつくられます。
自動化するために作成するマクロ
マクロの全体像
今回紹介するマクロです。比較的少ないコードでできております。
Option Explicit
Sub Adjustmet()
Dim UniformList As ListObject: Set UniformList = Sheet1.ListObjects(1)
With UniformList
Dim a As Integer
Dim b As Integer
Dim c As Integer
For a = 2 To .ListRows.Count + 1
If .Range(a, 5).Value = "不足" And .Range(a, 7) = Empty Then
For b = 2 To .ListRows.Count + 1
If _
.Range(b, 7).Value = Empty And _
.Range(b, 5).Value = "余剰" And _
.Range(b, 4).Value <> .Range(a, 4).Value And _
.Range(b, 2).Value = .Range(a, 2).Value And _
.Range(b, 3).Value = .Range(a, 3).Value _
Then
.Range(a, 7).Value = .Range(b, 4).Value + "から"
.Range(b, 7).Value = .Range(a, 4).Value + "へ"
.Range(a, 8).Value = a
.Range(b, 8).Value = a
Exit For
End If
Next b
End If
Next a
For c = 2 To .ListRows.Count + 1
If .Range(c, 5).Value = "不足" And .Range(c, 7) = Empty Then
.Range(c, 7).Value = "購入"
ElseIf .Range(c, 5).Value = "余剰" And .Range(c, 7) = Empty Then
.Range(c, 7).Value = "保留"
End If
Next c
End With
End Subプログラムの実行プロセス
上記のコードを実行される順に沿ってみていき、実行プロセスを理解しましょう。
Option Explicit
Sub Adjustmet()
Dim UniformList As ListObject: Set UniformList = Sheet1.ListObjects(1)
//中略
end sub まずは一番上の３行です。「Option Explicit」は、今はエラーを招かないためのおまじないとしてスルーしていただいて構いません。
Sub Adjustmet()でサブプロシージャというの内容の記述が始まります。最後行の「End Sub」までがひとつのマクロの実行単位でとなります。
3行のコードは、リストオブジェクトクラスの「UnifirmLIst」というインスタンスを生成し、Sheet1のテーブル1を代入します。つまりは、「UniformList」という変数で扱うオブジェクトをSheet1のテーブル1に決めたということです。
その次のWithステートメントは、UniformListに一連のステートメントを記述するエリアになります。
With UniformList
// 中略
End WithWithステートメントの中では、後々使うための3つの数値「a」、「b」、「c」の変数宣言をしておきます。
With UniformList
Dim a As Integer
Dim b As Integer
Dim c As Integer
// 中略
End With ここまでこのプログラムで扱うオブジェクトや変数の設定をしてきて準備が完了したので、ここからいよいよ実際にExcelのシート上に影響与える処理を記述していきます。
テーブル1は1行目がタイトル行であり、処理は2行目から開始しますので、変数「a」の初期値を「2」とし、そこからテーブルの最後の行まで「a」が1ずつ増えていくという、変数「a」によるループを作成します。
そのループの中で、a行の5列目の値が「不足」であり、a行の7列目のセルに値が入っていないとき、という条件分岐をつくります。つまり、上の行から順に、作業服が「不足」していて「対応相手」がまだみつかっていないレコードを探す処理を行います。
For a = 2 To .ListRows.Count + 1
If .Range(a, 5).Value = "不足" And .Range(a, 7) = Empty Then
//中略
End If
Next a
上の画像におけるIDが5のブルゾン（L）のように条件に合致するレコードが見つかると、If文の中の処理を実行していきます。
先述の変数「a」によるループとIf分の中で、今度は変数「b」によるループを回し始め、上の行から順に、次の複数条件を満たすレコードを探します。
「対応相手」がまだ見つかっていない ＝空欄
「過不足」が「余剰」である
変数「a」によるループで見つけたレコードとは所属が異なっている
変数「a」によるループで見つけたレコードと作業服の種類が同じである
変数「a」によるループで見つけたレコードと作業服のサイズが同じである
つまりは、他の課で上着（L）が余っているのを探すという処理です。
For a = 2 To .ListRows.Count + 1
If .Range(a, 5).Value = "不足" And .Range(a, 7) = Empty Then
For b = 2 To .ListRows.Count + 1
If _
.Range(b, 7).Value = Empty And _
.Range(b, 5).Value = "余剰" And _
.Range(b, 4).Value <> .Range(a, 4).Value And _
.Range(b, 2).Value = .Range(a, 2).Value And _
.Range(b, 3).Value = .Range(a, 3).Value _
Then
// 中略
End If
Next b
End If
Next aその条件に合致するレコードが見つかったら、上記コードの「中略」の箇所に以下のとおり記述されている処理が行われます。
「不足」のレコードの「対応相手」列には、「b」ループで発見したレコードの「所属名」の値 ＋「から」という文字列を入力する
「余剰」のレコードの「対応相手」列には、「a」ループでで発見したレコードの「所属名」の値＋「へ」という文字列を入力する
また、それぞれのレコードの「パス」列に同じ数字を入力する
For a = 2 To .ListRows.Count + 1
If .Range(a, 5).Value = "不足" And .Range(a, 7) = Empty Then
For b = 2 To .ListRows.Count + 1
If _
.Range(b, 7).Value = Empty And _
.Range(b, 5).Value = "余剰" And _
.Range(b, 4).Value <> .Range(a, 4).Value And _
.Range(b, 2).Value = .Range(a, 2).Value And _
.Range(b, 3).Value = .Range(a, 3).Value _
Then
.Range(a, 7).Value = .Range(b, 4).Value + "から"
.Range(b, 7).Value = .Range(a, 4).Value + "へ"
.Range(a, 8).Value = a
.Range(b, 8).Value = a
Exit For
End If
Next b
End If
Next aここまでの処理結果が次の画像のとおりです。
ちなみに「パス」というのは、手作業の説明の際には何も入力しておらず、このマクロで追加した機能です。「パス」列で同じ数字を持つレコードが、互いに対応相手となっている仕組みです。上の画像では、「9」という値を持っているレコード同士の間で、調整が行われているのがわかります。
そして最後に実行されるのが、次のコードです。
For c = 2 To .ListRows.Count + 1
If .Range(c, 5).Value = "不足" And .Range(c, 7) = Empty Then
.Range(c, 7).Value = "購入"
ElseIf .Range(c, 5).Value = "余剰" And .Range(c, 7) = Empty Then
.Range(c, 7).Value = "保留"
End If
Next c「不足」で対応相手なし → 「対応相手」列に「購入」
「余剰」で対応相手なし → 「対応相手」列に「保留」
これにより全ての処理が終わり、調整案が完成しました！
この処理にかかった時間は、次の画動画のとおり、ボタンひとつで本当に一瞬です！
ちなみに、「購入」のセルは条件付き書式というExcelの機能により色付けしています。
まとめ
以上がこのマクロの全体像と使用方法になりますが当ブログの読者様には、ぜひご自身の業務に合ったプログラムをご自分で実装できるようになっていただきたいと思っています。すぐにでも目の前の業務改善の参考にしたい！という方向けに最初に全体像の記事を執筆しましたが、やはり作り方を学ぶ上では情報が不十分かと思います。
よって次回以降では、このマクロのプログラムを、どのような課題分解や思考順序によって作成したか、詳しく解説していきますので、ぜひご一読ください。
参考文献
公式ドキュメント
書籍
この記事が参加している募集
この記事が気に入ったらサポートをしてみませんか？