在庫管理表の作り方【エクセルでデータ活用システムの構築】

エクセルで在庫管理表を作成できます。VBAを使わず、データ活用しやすいシステムの構築が可能です。

ポイントは次の2点です。

• テーブルを分割して紐づけ、ピボットテーブルで在庫を可視化
  -> 商品や入出庫履歴のように更新頻度の異なるデータを分けられるため、見やすく、管理しやすくなります。
• テーブル参照を使った入力規則を設定
  -> データ入力時に候補から選択でき、また想定外の入力を排除できるため、入力ミスや表記ゆれを最小限にできます。

在庫をエクセルで管理しようとすると、「1つの表にすべての情報を詰め込む」ケースが多いですが、これは非効率です。

• 列が増えると入力ミスや表記ゆれが増える
• 商品や入出庫の履歴を分けにくく、更新しづらい
• データを集計しようにも、どこを参照すればいいかわからない

これを解決するために、マスタ・トランザクション・ピボットの3構成に分けます。データベースの考え方を取り入れることで、見通しの良い在庫管理が可能になります。

在庫管理表を構築するポイントは次の2つです。

• テーブルを分割して、ピボットテーブルで在庫を可視化-> 更新頻度の異なるデータを分離
• テーブル参照を使った入力規則を設定-> プルダウン式で入力ミスを防止

この要素を組み合わせることで、「商品情報を登録 → 入出庫を記録 → 自動で在庫数が反映」という流れが完成します。

在庫管理の基盤となるテーブルは、次の4種類です。

商品一覧とその情報を格納します。在庫管理者によって更新され、最新情報に保たれます。

在庫からの入庫・出庫記録を記入します。入庫（出庫）するたびにデータが追加され、頻繁に書き込みされるテーブルです。

※1 M_商品とT_入出庫履歴のリレーションは「品番」で行いますが、T_入出庫履歴側で商品情報を入力する際は、「品番 + 品名」を使用します。入力するときのプルダウン表示が「品番」のみだと、商品の判別が難しくなるためです。

商品マスタや入出庫履歴のデータ入力時の入力規則として使用されるテーブルです。

エクセルで在庫管理表を作成する手順を解説します。解説のしやすさから、ここでは、1つのシートにすべてのテーブルを配置していますが、それぞれ個別のシートに配置することもできます（実用面ではこちらがオススメ）。データ数が多くなり、テーブルサイズが大きくなる場合は、それぞれ個別のシートに配置した方が使いやすいです。

1. 以下の列を持つテーブルM_商品を作成する。
   • 品番（必須）
   • 品名（必須）
   • 品番||品名（必須）※2
   • 在庫閾値（必須）
   • 保管場所（任意）
   • 金額（任意）
   • 備考（任意）
2. 品番||品名列のセルに=[@品番]&"||"&[@品名]を記入する※2。

1. 以下の列を持つテーブルT_入出庫履歴を作成する。
   • 日付（必須）
   • 品番||品名（必須）
   • 品番（必須）
   • 入庫数（必須）
   • 出庫数（必須）
   • 担当者（任意）
   • 備考（任意）
2. 品番列のセルに=LEFT([@品番||品名], FIND("||", [@品番||品名]) - 1)を記入する。
   • ※ 品番||品名から||を基準に品番を抽出
3. 品番||品名列のセルに入力規則を設定する。
   1. 品番||品名のセルを範囲選択 > データタブ > データの入力規則の設定ボタン
   2. データの入力規則ウィンドウ（設定タブ）で以下を設定
      • 入力値の種類：リスト
      • 空白を無視する：チェックなし
      • ドロップダウンリストから選択する：チェックあり
      • 元の値：=INDIRECT("M_商品[品番||品名]") ※3
4. 入庫数列と出庫数列がすべての行で空欄の場合は、0を記入した行を追加する。
   • ※ すべての行で空欄の列は、ピボットテーブルで「文字列」データ形式の列と判定され、エラーが生じる原因となるため。

1. M_商品、T_入出庫履歴テーブル間を、品番列で紐づける。
   • ※ 品番||品名列でリレーションシップを設定することも可能ですが、品名が修正されることを考慮し、品番のみを使用します
   1. データタブ > データモデルボタン > リレーションシップボタン > 新規作成ボタン
   2. リレーションシップの作成ウィンドウで以下を設定 > OKボタン
      • テーブル：T_入出庫履歴
      • 列（外部）：品番
      • 関連テーブル：M_商品
      • 関連列（プライマリ）：品番
   3. リレーションシップの作成ウィンドウで閉じるボタン
2. ピボットテーブルを設置する。
   1. 挿入タブ > ピボットテーブルプルダウン > データモデルからボタン
   2. 設置場所を選択 > OKボタン
3. フィールドを設定する。
   • 行：品番、品名（+ 表示させたい項目）
   • 値：入庫数（合計値）、出庫数（合計値）
4. デザインを変更する（任意）。
   • デザインタブ > レポートのレイアウト > 表形式で表示を選択
   • ピボットテーブル分析タブ > +/-ボタンをOFF
5. 在庫数、閾値メジャーを追加する。
   • ※ PowerPivotアドインをONにしておくこと
   1. Power Pivotタブ > 管理ボタンでパワーピボットエディタ起動
   2. 詳細設定タブ > 暗黙のメジャーの表示をON（フィールド設定した「合計値/入庫数」「合計値/出庫数」が下部の計算領域に表示される）
   3. 下部の計算領域に以下を記入
      • （T_入出庫履歴シート内）在庫数:=[合計 / 入庫数]-[合計 / 出庫数]
      • （M_商品シート内）閾値:=SUM([在庫閾値])
      • ※ 記入場所は計算領域内のどのセルでも良い
   4. パワーピボットエディタを終了（ウィンドウ×ボタン）
6. 在庫数とアラートアイコンを表示する。
   1. Power Pivotタブ > KPIプルダウン > 新しいKPI..ボタン
   2. 主要業績評価指標（KPI）ウィンドウで以下を設定 > OKボタン
      • KPIベースフィールド：在庫数
      • 対象の値の定義：メジャー： 閾値
      • 状態の閾値の定義：0.1%, 100%（在庫数が閾値の0.1%未満（≒ 0個）のとき赤、100%未満（0個 ~ 閾値未満）のとき黄、100以上（閾値以上）のとき緑）
      • アイコンのスタイルの選択：好みのアイコンセット
   3. ピボットテーブルのフィールドの値に以下を追加
      • 在庫数
      • 在庫数：状態（アイコン表示されない場合は、一度外して再度追加）
7. ファイルを開いたときの自動更新を設定する
   1. ピボットテーブル中のセルを選択 > 右クリック > ピボットテーブル オプション選択
   2. ピボットテーブル オプションウィンドウのデータタブ > ファイルを開くときにデータを更新にチェック > OKボタン

入力規則のリストとして使用するテーブルを用意し、M_商品やT_入出庫履歴中で使用します。下記の設定方法により、M_商品やT_入出庫履歴に入力規則を設定した列を追加できます。

使用テーブル： ピボットテーブル（在庫ピボット）

1. 「すべて更新」（Ctrl + Alt + F5）をクリック。
   • ピボットテーブルを右クリック > 更新
   • または データタブ > すべて更新ボタン
2. テーブル上の在庫数とアラートを確認。

使用テーブル： T_入出庫履歴

1. 入出庫が発生したら、テーブルに行を追加。
   • テーブルの末端（右下）セルでtab
   • または、既存の行をコピペ
2. 品番以外を記入。
   • ※ 品番は、関数で自動的に記入
   • ※ 在庫ピボットに反映するには、ピボットテーブルのデータ更新が必要

使用テーブル： M_商品

• 商品を新規追加する場合は、テーブルに行を追加。
  1. 行を追加
     • テーブルの末端（右下）セルでtab
     • または、既存の行をコピペ
  2. 品番||品名以外を記入
     • ※ 品番||品名は、関数で自動的に記入
• 既存商品の情報を変更する場合は、セル値の書き換え。
  • 変更内容は、在庫ピボットにも反映される（要ピボットテーブルのデータ更新）

• VBA不要で、エクセルだけで在庫管理システムを構築できる。
• 商品マスタ、入出庫履歴、ピボットの別テーブルで、分割してデータを管理できる。
• 入力規則とテーブル参照でミスや表記ゆれを防止。
• KPIアイコンで在庫アラートを可視化できる。

本記事では、エクセルのデータ活用に役立つ、テーブルを利用した以下のテクニックを活用しています。

• » テーブルを活用した入力値の制限
• » テーブルの分割＆統合