概要
在庫管理システムや生産管理において、「ロット引き当て」は非常に重要な業務ロジックです。特に、有効期限が早いものから優先的に出荷する「先入れ先出し（FIFO）」の原則に基づいた引き当て処理は、在庫の品質保持とロス削減に直結します。本記事では、Excel VBAを用いて、複数のロットに分散した在庫から、必要な数量を効率的かつ正確に引き当てるためのアルゴリズムを徹底解説します。単なるプログラミングの学習にとどまらず、実務でそのまま運用できる堅牢なコードの書き方を学びましょう。
詳細解説：ロット引き当てのロジック
ロット引き当てとは、特定の注文に対して、どのロットから何個出荷するかを割り当てる作業です。この処理には、大きく分けて「在庫の並び替え（ソート）」と「順次減算（ループ処理）」の2つのプロセスが必要です。
まず、在庫リストは、有効期限が古い順、あるいはロット番号順に昇順で並んでいる必要があります。この並び替えが不十分だと、古い在庫が残り続け、品質劣化の原因となります。VBAでは、RangeオブジェクトのSortメソッドを使用することで、簡単にこの要件を満たすことができます。
次に、引き当て処理の核心となるのがループ処理です。注文数量（必要数）が、現在見ているロットの在庫数よりも多いのか少ないのかを判定します。
1. 現在のロット在庫 ＞ 必要数：必要数分だけ引き当てを行い、残りの在庫を計算して処理終了。
2. 現在のロット在庫 ≦ 必要数：現在のロットを全量引き当て、残りの必要数を更新して次のロットへ進む。
この条件分岐を、注文数量が0になるまで、あるいは在庫が尽きるまで繰り返すのが、ロット引き当ての基本アルゴリズムです。
サンプルコード
以下に、ロット引き当てを自動化するためのサンプルコードを提示します。このコードは、シート「Stock」のデータを参照し、指定された注文数量を自動的に引き当てる構成です。
Sub ExecuteLotAllocation()
Dim wsStock As Worksheet
Dim orderQty As Long
Dim i As Long
Dim currentStock As Long
Dim allocateQty As Long
' 設定
Set wsStock = ThisWorkbook.Sheets("Stock")
orderQty = 150 ' 注文数（本来はセルから取得）
' 1. 有効期限で昇順ソート（B列：有効期限、C列：在庫数）
wsStock.Range("A1").CurrentRegion.Sort _
Key1:=wsStock.Range("B2"), Order1:=xlAscending, _
Header:=xlYes
' 2. ロット引き当てループ処理
For i = 2 To wsStock.Cells(wsStock.Rows.Count, 1).End(xlUp).Row
currentStock = wsStock.Cells(i, 3).Value
If orderQty <= 0 Then Exit For
If currentStock > 0 Then
If currentStock >= orderQty Then
' 今回のロットで注文が完了する場合
wsStock.Cells(i, 4).Value = orderQty ' D列に引き当て数を記録
orderQty = 0
Else
' 今回のロットを全量使用し、残りを次に回す場合
wsStock.Cells(i, 4).Value = currentStock
orderQty = orderQty - currentStock
End If
End If
Next i
If orderQty > 0 Then
MsgBox "在庫が不足しています。残り：" & orderQty, vbExclamation
Else
MsgBox "引き当てが完了しました。", vbInformation
End If
End Sub
実務アドバイス
実務でこのプログラムを運用する場合、いくつかの注意点があります。
第一に「データの排他制御」です。複数人が同時に同じ在庫ファイルを開いている場合、引き当て処理中に在庫数が書き換わると不整合が発生します。可能であれば、データベース（SQL ServerやAccess）をバックエンドに置くか、せめて共有ブックとしての制限を考慮してください。
第二に「トランザクションの確保」です。万が一、途中でエラーが発生した際に、中途半端に引き当て記録が残ってしまうのは避けなければなりません。VBAで完全にトランザクションを制御するのは難しいですが、処理の直前にシートのコピーを取る、あるいは書き込みの最終確認をユーザーに行わせるなどの工夫が必要です。
第三に「例外処理」です。もし注文数量が0以下であったり、在庫リストが空であったりした場合のケアも重要です。プログラムの冒頭にバリデーションチェックを組み込むことで、バグの発生を未然に防ぐことができます。
まとめ
ロット引き当ては、一見単純なループ処理に見えますが、正確な在庫管理を支える重要な機能です。今回のコードをベースに、自社の業務形態に合わせて「ロット番号の優先順位」や「特定拠点のみの引き当て」といった条件を追加してみてください。VBAによる自動化は、単なる作業の効率化だけでなく、人為的なミスを排除し、在庫という資産の流動性を高めるための強力な武器となります。
今回の練習問題を通じて、配列やループ処理の制御、条件分岐のロジックが身につけば、より複雑な生産計画や在庫最適化のロジックにも挑戦できるはずです。VBA講師として、皆さんの日々の業務が、このコードによって少しでも楽になることを願っています。継続して練習を積み重ね、より高度な業務アプリケーション開発を目指してください。
（執筆者：VBAマスター講師）
