【Excel VBA】複数の管理データを自動統合して生産計画と在庫予測を一括出力するマクロ

5つのExcelデータをボタン1つで統合！
半年先の在庫推移まで自動予測する生産計画マクロの開発事例

こんにちは！

「毎月5種類のExcelファイルを開いて、商品コードで突合しながら在庫数や発注残を手入力している…」
「入り数やM3情報を一つ一つ確認しながら転記する作業で、丸半日が潰れてしまう…」
「半年先までの在庫推移を計算式で予測したいが、手計算では商品数が多すぎてミスが怖い…」

工業用部材や製造業の在庫管理・生産計画に携わっている方なら、こうした月次業務の負担に心当たりがあるのではないでしょうか。

今回は、工業用部材の生産販売を手がけるメーカー様からのご依頼で、商品マスタ・入り数リスト・月末在庫・発注残・月平均数量の5つのExcelデータを自動統合し、半年先までの各月在庫推移を予測計算して1枚のレポートシートに一括出力するExcel VBAマクロを開発した事例をご紹介します。

VBAマクロの処理実行画面：ボタンを押すだけで5種類のデータ統合から在庫予測までを自動実行

導入前の課題：手作業によるデータ統合と予測計算の限界

クライアント様はこれまで、毎月以下の5つのExcelファイルを手動で開き、商品コードをキーにして1行ずつデータを照合・転記していました。

• 商品コード品名単価データ（商品コード、品名、単価、FOB単価）
• 入り数リスト（商品ごとの入り数、M3情報）
• 月末在庫数量データ（当月末時点の在庫数量）
• 発注残データ（発注番号、商品コード、納期月、入荷予定数量）
• 月平均数量データ（直近4ヶ月の月平均出荷数量）

これらのデータは部門ごとに別々のExcelファイルで管理されており、統合作業には毎回3〜4時間を要していました。さらに、統合後の在庫推移予測は、「当月在庫 + 入荷予定 − 月平均出荷」を商品ごと・月ごとに手計算で積み上げる必要があり、商品数が数百点に及ぶため計算ミスの発生が避けられない状況でした。

自動化ツールの処理フローと主な機能

5つの入力データから統合レポートを出力するまでの処理フロー全体像

商品マスタデータの自動読込とベースシート生成

マクロを実行すると、まず同一フォルダ内から「商品コード品名単価」ファイルを自動検出し、商品コード・品名・単価・FOB単価の4列をベースとなる新規ワークブックのシートにコピーします。このシートが、すべてのデータを統合する基盤となります。

ファイル名はワイルドカード（*商品コード*品名*単価*.xlsx）で検索するため、月次でファイル名が微妙に異なる場合でも柔軟に対応できる設計となっています。

入り数・M3情報の自動紐付け

次に「入り数リスト」ファイルを読み込み、各商品コードに対して入り数とM3（容積）情報を自動転記します。商品コードをキーとしたループ処理により、数百行のデータを瞬時に紐付けます。

手作業ではVLOOKUP関数を組んで確認する作業が必要でしたが、マクロが完全に代行するため、関数の設定ミスや参照ずれが発生しません。

月末在庫数量の自動統合

「月末在庫数量」ファイルから当月末時点の在庫数量を取得し、商品コードをキーに該当セルへ自動記入します。数値は桁区切り書式（#,##0）で整形されるため、出力レポートはそのまま印刷・共有可能な品質で仕上がります。

発注残データの月別自動集計

「発注残」ファイルからは、商品コードと納期月をキーにして、5ヶ月分の入荷予定数量を月別に自動集計します。

具体的には、発注残データの各レコードに含まれる伝票番号の年月部分（yyyymm形式）を判定し、対象月の入荷予定として合算します。同一商品・同一月に複数の発注残がある場合も自動で合算されるため、手作業での見落としや二重計上のリスクが排除されます。

集計された入荷予定数は赤色フォントで表示されるため、出力レポート上でどのセルが実績値で、どのセルが入荷予定値かが一目で判別可能です。

商品ごとの在庫推移と半年先までの予測が一覧で確認可能なレポート

月平均出荷数量の自動転記

「月平均数量」ファイルから直近4ヶ月の月平均出荷数量を取得し、在庫予測計算の基礎データとして転記します。この月平均値が、将来の在庫消化ペースの推定に活用されます。

半年先までの在庫推移を自動予測計算

統合されたすべてのデータを基に、5ヶ月先までの各月末在庫推移を数式で自動計算します。

計算ロジックは以下の通りです。

1ヶ月分在庫数：月平均出荷数量の4ヶ月平均 ÷ 4 で算出される1ヶ月あたりの標準消化量

各月末の在庫推移予測：「当月末在庫 + 累計入荷予定 − 累計月平均出荷」の数式をセルに動的に挿入。月が進むごとに累計入荷と累計出荷が加算されていく構造となっており、半年先までの在庫推移を各商品について一覧で把握できます。

これらの計算式はExcelの数式として出力ファイルに埋め込まれるため、マクロ実行後もユーザーが任意の入荷数量や出荷見込みに値を変更すれば、在庫推移が即座に再計算されます。

FOB単価ゼロの商品を自動非表示

FOB単価が0の商品（取扱い終了品や参考品目など）は、レポートの可読性を高めるために行ごと自動で非表示にします。これにより、出力レポートには実際に在庫管理が必要な商品のみが表示され、意思決定に必要な情報だけに集中できます。

導入効果：正確で迅速な在庫管理の実現

データ統合時間を3〜4時間から数分に短縮

5種類のExcelファイルを手動で開いて照合・転記していた作業が、ボタンを押すだけのわずか数分で完了するようになりました。月次のデータ統合作業から解放されたことで、在庫分析や発注計画の立案といった戦略的業務に時間を充てられるようになりました。

計算ミス・転記漏れをゼロに

商品コードをキーとしたデータ照合と計算をすべてプログラムが自動処理するため、手作業で発生していた以下のエラーが完全に解消されました。

• 商品コードの照合ミス（別商品のデータを転記してしまう）
• 入荷予定数量の合算漏れ（同一商品の複数発注を見落とす）
• 在庫推移計算式の参照エラー（列やセル範囲のずれ）
• 数値書式の不統一（桁区切りのない大きな数値の読み間違い）

半年先までの在庫リスクを可視化

月次レポートに半年先までの在庫推移予測が自動で含まれるため、「在庫が何月に切れそうか」「過剰在庫になりそうな商品はどれか」を早期に発見し、先手を打った発注判断が可能になりました。

入荷予定が赤字で表示される視覚的な工夫により、実績値と予測値の区別も直感的に把握できます。

属人化の解消と業務継続性の確保

マクロの実行はExcel上のボタンを押すだけのシンプルな操作です。VBAの知識は一切不要であり、担当者が変わっても同じ品質のレポートを安定して出力できます。入力ファイルのファイル名がワイルドカード検索に対応しているため、月ごとにファイル名が変わっても設定変更なしで運用可能です。

開発費用と費用対効果

今回のような「複数Excelデータの自動統合・商品コードによるデータ紐付け・月別入荷集計・在庫推移の予測計算・レポート自動出力」を一貫して行うExcel VBAマクロの開発費用は、およそ20,000円〜30,000円ほどです。

このツールは、月次で発生する以下のコストを大幅に削減します。

• データ統合作業の人件費削減：3〜4時間の手作業を数分に圧縮
• 計算ミスによる手戻りコストの撲滅：誤発注や在庫切れによる損失を防止
• レポート作成の外注コスト削減：社内でワンクリック出力が可能に

導入後わずか1ヶ月で投資回収が見込める、製造業の在庫管理業務に特化した費用対効果の高いソリューションです。

このようなお悩みをお持ちの企業様におすすめです

• 複数のExcelファイルからデータを手動で統合・照合する業務に時間を取られている
• 手計算による在庫予測の精度に不安があり、計算ミスが発生しやすい
• 半年先までの在庫推移を正確に把握し、適切な発注判断を行いたい
• データ統合業務が属人化しており、担当者不在時に業務が滞るリスクがある
• 在庫管理レポートの作成頻度を上げて、経営判断のスピードを高めたい

毎月のデータ統合・在庫予測業務でお困りのことがあれば、どんな小さなことでも構いませんので、ぜひお気軽にご相談ください。
貴社の業務フローに最適なExcel VBA自動化ソリューションをご提案いたします。