エクセル在庫管理表の作り方｜自動集計で資材管理・物品管理のシステム化

そんな疑問を持つ方に向けて、この記事では在庫管理表の種類と選び方から、資材管理・物品管理にも対応した自動集計システムの具体的な作り方まで、順を追って解説します。

在庫管理表のスタイルは、おおまかに「一覧型」「単票型」「移動表型」の3つに大別されます。各スタイルの特徴・メリット・デメリット・向いているケースは下記の通りです。

在庫一覧表（リスト）型 は、 商品名と「現在庫数」だけを並べた 1つの表で管理する形式で、Microsoftのテンプレートでも最も多い形式です。

単票（吊り下げ票）型 は、 1つの商品に対して1つのシートや表を作成し、日付・入庫・出庫・現在庫数を縦に記録する 形式です。

在庫移動表型 は、 1枚のシートに全商品と日付（横軸）を並べる 形式です。

「在庫一覧（マスタ）」と「入出庫履歴」を分離したデータベース形式 を採用することで、計算ミスがなく現在庫数が常に自動集計されるExcel在庫管理システム が構築できます。在庫一覧表（リスト）型のような「見やすい一覧表」と、単票タイプのような「詳細な履歴管理」を両立させた仕組みです。

ここでは、管理項目の定義から数式の設定まで、準備と3つのステップに分けて解説します。

システムを作る前に、「何を管理すべきか」を決める必要があります。

資材管理・商品管理に共通する基本項目は下記の5つです。

• 品番
• 商品名
• カテゴリ
• 保管場所
• 単位

「資材管理」特有の項目として、下記も追加で検討します。

• 規格
• ロット番号
• リードタイム（発注から納品までの日数）
• 最低在庫数（発注点）

管理目的によって必要な項目は異なります。下記の比較表を参考に、各自の運用に合った項目を選んでください。

在庫一覧表（マスタ）と入出庫履歴の2つのテーブルを作ります。

ここでは、Excelのテーブル機能を使います。メリットは、データを追加した際に参照範囲や数式・書式などが自動調整され、手作業でのメンテナンスが不要になるということです。テーブルデザイン タブの「テーブルスタイル」欄からデザインも簡単に変えられます。

数式の自動拡張や書式の継承など、在庫管理を支える「テーブル機能」のメリットは下記で解説しています。

» Excelテーブルの基本と活用法｜通常の表との違い・できることを徹底解説

自動集計の仕組みを作ったら、「見やすさ」と「操作ミスの防止」を高める設定を追加します。具体的なテクニックは下記の3つです。

条件付き書式 は、現在庫数が発注点（最低在庫数）を下回ったとき、セルを自動的に赤色にする設定です。視覚的にすぐ気づけるため、 発注漏れの防止に効果的です。

下記は充足 / 要発注 のどちらかが表示される状態列（数式は前述）が存在する前提での設定になります。

データの入力規則 は、手入力によるミス（表記ゆれや誤字）を防げる機能です。品番や商品名の入力欄に プルダウン（ドロップダウンリスト） を設定できます。

どれほど優れたExcel管理表を作っても、運用ルールが徹底されなければ在庫ズレは防げません 。ここでは、現場全体で共有すべき「入力タイミングの固定」と「定期的な棚卸し」の2つのルールを解説します。

入出庫があったら「その場ですぐ」または「終業前」に 必ず必ず データ入力する、というように入力タイミングを固定することをルール化するのは重要です。「まとめて後で入力する」という運用は、 入力漏れや記憶違いによるミスの温床 となります。「 入出庫と入力はセットで行う 」という意識を現場全体に根付かせることが重要です。

「帳簿上の現在庫数」と「実際の在庫（実在庫）」を定期的に照合する、定期的な棚卸し作業は重要です。

どれだけルールを徹底しても、長期間放置すれば誤差は蓄積します。月次または四半期ごとに棚卸しを実施し、数字のズレをリセットする習慣が、 正確な在庫管理の土台 となります。

Excelには限界があり、 運用していくうちに必ず「データ量」「同時編集」「ヒューマンエラー」という3つの壁に直面 します。無理にExcelを使い続けると、かえって業務効率を下げ、重大なミスを招く恐れがあります。

ここではExcelの3つの限界と、専用システムへの移行を検討すべき具体的なタイミングについて解説します。

その他のExcelの限界や詳細については、下記にまとめました。

» エクセルの限界とは？活/脱Excelの判断基準【向かない業務を徹底解説】

以下の状況に1つでも当てはまるなら、 専用システムへの移行を具体的に検討するタイミング です。

この記事では、次のことについて解説しました。

Excelは「最初の一歩」として最適なツールです。運用が追いつかなくなってきた段階で、専用の在庫管理システムへの移行を視野に入れておきましょう。