エクセルで在庫管理をマクロで自動化する方法 テンプレート・作り方・注意点を解説

「エクセルの在庫管理表で入出庫のたびに手入力するのが面倒」「マクロを使って自動化したいが、作り方がわからない」。エクセルで在庫管理を行っている企業の担当者にとって、こうした悩みは日常的なものではないでしょうか。

エクセルのマクロ（VBA）を使えば、入出庫データの登録と同時に在庫数を自動更新したり、発注点を下回った商品にアラートを表示したり、棚卸しリストを自動生成したりといった処理を自動化できます。手作業による入力ミスや計算ミスを削減し、在庫管理の精度と効率を大幅に向上させることが可能です。

この記事では、エクセルの在庫管理表にマクロを組み込む具体的な方法、テンプレートの構成、マクロで自動化できる処理の一覧、そして運用時の注意点まで、初心者にもわかりやすく解説します。

確認したいポイント	結論	詳細
マクロで何が自動化できるか？	入出庫登録・在庫更新・アラート	入出庫データの登録と同時に在庫数を自動更新、発注点アラート、棚卸しリスト生成などが自動化できます
テンプレートの構成は？	3シート構成が基本	品目マスタ・入出庫記録・在庫一覧の3シートで構成し、マクロでシート間のデータ連携を自動化します
VBAの知識は必要か？	基礎レベルで十分	変数・条件分岐・ループの基礎があれば実用的な在庫管理マクロを構築できます。テンプレートの流用も可能です
エクセルの限界はどこか？	数千品目を超えると検討が必要	品目数やファイルサイズが大きくなると動作が遅くなるため、規模の拡大時には専用システムへの移行を検討しましょう

この記事でわかること

・エクセルの在庫管理でマクロを使うメリットと関数との違い

・在庫管理テンプレートの構成（3シート構成の設計）

・マクロで自動化できる処理の具体例5選

・在庫管理マクロの作り方（基本手順）

・マクロ運用時の注意点とエクセル在庫管理の限界

＼ 業務効率化・DX推進のご相談はネクストスケールへ ／ ▶ 相談予約はこちら

エクセルの在庫管理にマクロを使うメリット

エクセルの在庫管理表は、関数だけでも作成できますが、マクロを組み込むことで自動化の幅が大きく広がります。ここでは、マクロを使うメリットと関数との違いを解説します。

関数だけの在庫管理表の課題

SUMIFやVLOOKUPなどの関数を使った在庫管理表は、入出庫データが増えるにつれてファイルが重くなり、計算式の連鎖でエラーが発生しやすくなるという課題があります。

また、関数ベースの在庫管理表では、「入出庫を記録すると同時に在庫数を更新する」「発注点を下回ったらアラートを出す」「棚卸しリストを自動生成する」といった動的な処理ができません。データの入力と計算結果の更新がリアルタイムに連動しないため、操作ミスやデータの不整合が起こりやすくなります。

マクロを導入する3つのメリット

メリット1：入出庫と同時に在庫数が自動更新される。マクロを使えば、入出庫データを入力してボタンを押すだけで、品目ごとの在庫数が自動的に計算・更新されます。手動での再計算が不要になり、常に最新の在庫状況を把握できます。

メリット2：ファイルが壊れにくくなる。関数ベースの在庫管理表は、セルの参照関係が複雑になるほど、誤操作で計算式が壊れるリスクが高まります。マクロで処理をプログラム化すれば、入力エリアと計算ロジックを分離でき、ファイルの堅牢性が向上します。

メリット3：帳票出力や他のファイルとの連動が可能。マクロを使えば、在庫データをもとに発注書や棚卸し表を自動生成したり、複数の倉庫の在庫データを1つのファイルに集約したりといった、関数だけでは実現できない処理が可能になります。

在庫管理テンプレートの構成｜3シート設計

マクロを活用した在庫管理表は、品目マスタ・入出庫記録・在庫一覧の3シート構成が基本です。それぞれのシートの役割と設計を解説します。

シート1：品目マスタ

品目マスタは、管理対象となるすべての品目の基本情報を登録するシートです。以下の列を設定します。

品目コード（一意の識別番号）、品目名、カテゴリ、単位（個・箱・kgなど）、発注点（この数量を下回ったら発注が必要な基準値）、標準発注数量、仕入先名。

品目マスタは、入出庫記録と在庫一覧の両方から参照される「マスターデータ」です。品目コードをキーにして、マクロでデータを紐づけます。

シート2：入出庫記録

入出庫記録は、日々の入庫（仕入れ）と出庫（出荷・使用）を1行ずつ記録するシートです。以下の列を設定します。

日付、品目コード、品目名（マスタから自動参照）、入出庫区分（入庫/出庫）、数量、備考（発注番号や出荷先など）。

品目名は品目コードを入力するとマクロまたはVLOOKUP関数で自動表示されるようにしておくと、入力ミスを防げます。入出庫区分はプルダウンリストで選択式にするのがおすすめです。

シート3：在庫一覧

在庫一覧は、品目ごとの現在の在庫数を一覧表示するシートです。マクロが入出庫記録のデータを集計し、このシートの在庫数を自動更新します。以下の列を設定します。

品目コード、品目名、現在庫数、発注点、発注点との差（現在庫数 − 発注点）、ステータス（正常/要発注/在庫過多）。

ステータス列は、マクロまたは条件付き書式で発注点を下回った品目を赤く表示するように設定しておくと、在庫切れリスクのある品目がひと目でわかります。

＼ 業務効率化・DX推進のご相談はネクストスケールへ ／ ▶ 資料請求はこちら

マクロで自動化できる在庫管理の処理5選

在庫管理のマクロで特に効果が大きい自動化処理を5つ紹介します。

処理1：入出庫登録と在庫数の自動更新

入出庫データを入力して「登録」ボタンを押すと、入出庫記録シートにデータが追加され、在庫一覧シートの該当品目の在庫数が自動更新される処理です。入庫であれば在庫数に加算、出庫であれば減算します。在庫管理マクロの最も基本的な処理です。

処理2：発注点アラートの自動表示

在庫一覧シートの在庫数が発注点を下回った品目に対して、セルの背景色を赤に変更したり、メッセージボックスでアラートを表示したりする処理です。在庫切れを事前に検知し、発注漏れを防ぐことができます。

処理3：棚卸しリストの自動生成

「棚卸し」ボタンを押すと、品目マスタのデータをもとに棚卸し用のリスト（品目名・理論在庫数・実在庫数の記入欄）を自動生成する処理です。棚卸しの度にリストを手作業で作成する手間を省けます。

処理4：入出庫履歴の検索・フィルタリング

特定の品目コードや期間を指定して、入出庫記録を検索・抽出する処理です。「この商品の直近3ヶ月の出庫履歴を見たい」といったニーズに、マクロで瞬時に対応できます。ユーザーフォーム（入力画面）を使えば、操作性もさらに向上します。

処理5：月次在庫レポートの自動出力

月末に「レポート出力」ボタンを押すと、品目ごとの月初在庫・入庫合計・出庫合計・月末在庫をまとめたレポートシートを自動生成する処理です。経営層への報告資料や、仕入れ計画の策定に活用できます。

業務効率化のアイデアをより幅広く検討したい方は、業務効率化アイデア55選の記事もあわせてご確認ください。

在庫管理マクロの作り方｜基本手順

ここでは、エクセルの在庫管理表にマクロを組み込む基本的な手順を解説します。

手順1：開発タブを表示しVBEを開く

マクロを作成するには、Excelの「開発」タブを使います。「開発」タブが表示されていない場合は、「ファイル」→「オプション」→「リボンのユーザー設定」で「開発」にチェックを入れて表示させます。

「開発」タブの「Visual Basic」ボタンをクリックすると、VBE（Visual Basic Editor）が起動します。ここにVBAのコードを記述してマクロを作成します。

手順2：入出庫登録マクロのコードを記述する

VBEの「標準モジュール」にマクロのコードを記述します。入出庫登録マクロの基本的な処理の流れは以下のとおりです。

1. 入力フォーム（またはシート上の入力セル）から品目コード・入出庫区分・数量を取得する

2. 入出庫記録シートの最終行の次の行にデータを追加する

3. 在庫一覧シートから該当品目の行を検索し、在庫数を更新（入庫なら加算、出庫なら減算）する

4. 在庫数が発注点を下回っていたらアラートを表示する

5. 入力セルをクリアして次の入力に備える

VBAのコードは、テンプレートとして公開されているものをベースに、自社の運用に合わせてカスタマイズするのが効率的です。

手順3：ボタンを配置してマクロを割り当てる

コードの記述が完了したら、シート上に「登録」ボタンを配置し、作成したマクロを割り当てます。「開発」タブ→「挿入」→「フォームコントロール」→「ボタン」を選択し、シート上にドラッグして配置します。ボタンに割り当てるマクロ名を選択すれば完成です。

ボタン名を「入庫登録」「出庫登録」のようにわかりやすく変更しておくと、操作する担当者が迷わずに使えます。

手順4：マクロ有効ブックとして保存する

マクロを含むExcelファイルは、通常の.xlsx形式ではなく、.xlsm形式（Excelマクロ有効ブック）で保存する必要があります。「ファイル」→「名前を付けて保存」で、ファイルの種類を「Excelマクロ有効ブック（*.xlsm）」に変更してから保存してください。

＼ 業務効率化・DX推進のご相談はネクストスケールへ ／ ▶ 相談予約はこちら

在庫管理マクロの注意点とエクセルの限界

マクロを活用した在庫管理は強力ですが、運用上の注意点とエクセルの限界も理解しておく必要があります。

注意点1：マクロの属人化を防ぐ

マクロを作成した担当者しかコードの内容を理解していない場合、担当者の異動や退職でメンテナンスが不能になるリスクがあります。マクロのコードにはコメント（処理の説明）を入れ、操作マニュアルを作成して、複数のメンバーが運用・修正できる体制を整えましょう。

注意点2：バックアップを定期的に取得する

マクロの誤操作やファイルの破損に備えて、定期的にバックアップを取得する習慣をつけましょう。日次でバックアップを取るか、ファイル名に日付を含めて世代管理する方法が実用的です。

注意点3：セキュリティ設定に注意する

マクロはExcelのセキュリティ設定によって実行がブロックされる場合があります。「マクロを有効にする」の確認ダイアログが表示された際に「有効にする」を選択する必要があるため、利用者に事前に周知しておきましょう。

注意点4：エクセル在庫管理の限界を知っておく

エクセルの在庫管理は手軽に始められますが、品目数が数千を超える場合やデータ行が数万行になる場合は、ファイルの動作が遅くなり、管理が困難になります。また、複数人が同時に同じファイルを編集するのが難しい点、リアルタイムのデータ共有に制約がある点も限界のひとつです。

エクセルでの運用に限界を感じたら、クラウド型の在庫管理システムや、ノーコードツールを使ったWebアプリへの移行を検討しましょう。

システム開発の外注を検討している場合は、システム開発の外注とはの記事で、費用相場や外注先の選び方を解説しています。AI活用による業務改善については、ネクストスケールのAIコンサル・AX伴走支援のサービスもご活用ください。

＼ 業務効率化・DX推進のご相談はネクストスケールへ ／ ▶ 資料請求はこちら

まとめ

エクセルの在庫管理にマクロ（VBA）を組み込むことで、入出庫登録と在庫数の自動更新、発注点アラート、棚卸しリストの生成、入出庫履歴の検索、月次レポートの出力といった処理を自動化できます。テンプレートは品目マスタ・入出庫記録・在庫一覧の3シート構成が基本で、マクロでシート間のデータ連携を自動化します。

マクロの作成は、開発タブの表示→VBEでのコード記述→ボタンの配置→xlsm形式での保存という手順で進めます。運用にあたっては、マクロの属人化防止、定期バックアップ、セキュリティ設定への対応が重要です。

エクセルでの在庫管理は、品目数が数千を超えない規模であれば十分に実用的です。まずはテンプレートをベースに基本的な在庫管理表を構築し、業務の成長に合わせてマクロの機能を拡張していくアプローチがおすすめです。