在庫管理表の作り方と実務運用─ミスを減らし、欠品と滞留を防ぐ

中小企業や小規模店舗では、専用の在庫管理システムを導入する予算や人員が限られているケースも多く見られます。そんな中で多くの現場で利用されているのが「エクセルを活用した在庫管理」です。エクセルは導入コストがかからず、誰でも使いやすいという利点があります。本記事では「在庫管理エクセル」をテーマに、基本的な方法からメリット・注意点、効率的に運用するための工夫まで解説していきます。

在庫管理表とは？目的・効果・導入判断

在庫管理表は「いつ・どこで・何が・いくつ動き、今いくつ残っているか」を一元的に可視化する台帳です。

目的は

①欠品・売り逃しの防止

②滞留（死に筋）の早期発見

③仕入れ・発注の精度向上

④棚卸時間の短縮。

特に中小企業では、Excelやスプレッドシートで標準化するだけでも効果は大きく、属人化や二重入力が減ります。導入判断は「SKU数」「拠点数」「ロット管理の有無」から。SKUが数百未満、拠点1～2であればExcel運用で十分スケールします。

在庫管理表の基本設計（スキーマと台帳の分離）

必須カラムと意味

まずは「商品マスター」と「入出庫履歴」を分離します。商品マスターの必須カラムは、

①商品コード（ユニークID）
②商品名
③標準単価
④カテゴリ
⑤保管場所
⑥リードタイム
⑦安全在庫

入出庫履歴は、①日付②種類（入庫/出庫/調整）③商品コード④数量⑤伝票/理由⑥担当者⑦備考。ポイントは「履歴は削らない」で監査性を担保すること。

残高は履歴から再計算できるため、残高表は派生ビュー（計算結果）として扱います。

入出庫と残高をつなぐ計算式

残高は「期首在庫＋入庫合計－出庫合計」。

Excelなら `SUMIFS` で商品コードごとに集計し、残高表に `=初期在庫 + 入庫合計 – 出庫合計` を設定。

発注点は「発注点＝安全在庫＋（平均需要×リードタイム）」、安全在庫は「需要の標準偏差×サービス係数×√リードタイム」が実務的です。

条件付き書式で「残高＜発注点」を赤表示すると、発注優先度が一目で分かります。

金額残高は「残高×単価」で算出し、滞留金額の把握にも活用します。

ロット・賞味期限・シリアルの管理

食品・化粧品・医療品・家電等は「同一SKUでもロット/期限/シリアルが異なる」ため、履歴に追加カラムを設けます。

推奨は、

①ロットNo/シリアル

②製造日/入荷日

③賞味期限/保証期限

④保管場所

出庫は「先入先出（FIFO）」が基本で、ピボットや `SORT` で期限が近い順に並べて引当。棚卸時はロット単位で数量一致を確認し、差異は「調整」種別で履歴登録することで追跡可能になります。

棚卸・差異調整の設計

棚卸手順は

①棚卸日を決め入出庫を一時停止

②棚卸票を自動生成（拠点×棚×SKU順）

③実数入力

④差異集計

⑤原因分析・調整仕訳

Excelでは「棚卸用ビュー」を作り、在庫残高からSKU順に数量のみ入力できる表を出力。差異は「実棚－帳簿」で算出し、履歴に「調整（原因：破損/紛失/数え間違い等）」で登録。翌営業日から入出庫再開、棚卸差異の金額影響は月次報告に反映します。

運用ルール（入力頻度・権限・チェック体制）

実務安定の鍵はルール化です。

①入力頻度：入出庫は当日入力（締め時間を設定）、発注点監視は毎日/毎週定例

②権限：マスター編集は管理者のみ、履歴入力は担当者、差異承認は別権限

③チェック：週次で「在庫ゼロなのに出庫」「マイナス在庫」「異常ロット」を抽出、月次で棚卸差異率をKPI管理

④バックアップ：版管理（YYYYMMDD付きファイル名）と変更履歴を残す

⑤教育：品番規則・入出庫定義・調整数の扱いを文書化して新任者でも即運用可能に。

Excel/スプレッドシート実装手順と自動化の勘所

実装は次の順で進めると安全です。

①マスター/履歴/残高/ダッシュボードの4シート作成

②`SUMIFS`・`VLOOKUP/XLOOKUP` で残高・金額・発注点を自動化

③データ入力規則（種別：入庫/出庫、商品コード：マスター参照）でミス防止

④条件付き書式で「発注要」「滞留（90日未出庫）」「ロット期限近い」を可視化

⑤ピボットで回転率（売上原価÷平均在庫）とABC分析をダッシュボード化

⑥共有はOneDrive/Googleで閲覧・編集権限を分離し、同時編集の競合を回避します。

在庫リスクの見える化（KPIとアラート設計）

KPIは下記のように設定するのがいいかと思います。

①在庫回転率（月次/カテゴリ別）

②欠品率（欠品日÷営業日）

③滞留在庫比率（90日未出庫の金額/在庫総額）

④棚卸差異率

⑤発注遵守率（発注点下回りから発注までの遅延日数）

Excelでは、信号色：緑=健全、黄=注意、赤=要対応

リストアップ：上位10SKUの欠品/滞留・期日アラート：期限30日以内のロットを太字赤

などを実装。朝会でダッシュボードを開くだけで、当日の優先タスクが決まる状態を目指します。