【Googleスプレッドシート】在庫管理シートの構築テンプレート!入出庫データの基本構造

在庫管理をGoogleスプレッドシートで始めたいと考えている方も多いでしょう。手作業でエクセルや紙で管理していると、入力ミスや集計漏れが発生しやすいものです。この記事では、入出庫データを記録して在庫数を自動計算するテンプレートの基本構造を解説します。シートの設計から関数の設定まで、実践的な手順を紹介しますので、すぐに活用できます。

在庫管理シートをテンプレート化するメリット

在庫管理をスプレッドシートで行う最大の利点は、リアルタイムで複数人が同時編集できることです。また、関数を使えば手計算の手間を省けます。テンプレートとして一度作っておけば、新しい商品を追加するたびに数式を書き直す必要がありません。入出庫データを正しい構造で蓄積すれば、後から分析や発注予測にも応用できます。この基本構造を押さえておくことで、どんな規模の在庫管理にも柔軟に対応できます。

テンプレートの全体構成と必要なシート

在庫管理シートは最低限3つのシートで構成します。「入出庫データ」シートで日々のトランザクションを記録し、「在庫一覧」シートで現在庫を確認し、「商品マスタ」シートで商品情報を管理します。それぞれのシートを関連付けることで、一元管理が可能です。以下に各シートの役割と列構成を説明します。

入出庫データシート

このシートがすべての基本です。以下の列を用意します。

• A列: 日付(入出庫が発生した日)
• B列: 商品コード(各商品を一意に識別するID)
• C列: 入庫数(入荷や製造による増加数、数値のみ)
• D列: 出庫数(出荷や消費による減少数、数値のみ)
• E列: 備考(任意のメモ)

入庫と出庫は別々の列にすることで、後から合計を計算しやすくなります。1行につき1つのトランザクションを記録します。

商品マスタシート

商品の基本情報を管理します。最低限、商品コードと商品名を用意します。必要に応じて分類や単価、発注点などを追加します。

• A列: 商品コード
• B列: 商品名
• C列: 分類(任意)
• D列: 単価(任意)

在庫一覧シート

ここで現在庫を表示します。商品マスタの商品コードを基に、入出庫データからSUMIFS関数で入庫合計と出庫合計を計算し、差額を在庫数とします。

• A列: 商品コード
• B列: 商品名(VLOOKUPやINDEX/MATCHで商品マスタから取得)
• C列: 入庫合計(=SUMIFS(入出庫データ!C:C, 入出庫データ!B:B, A2))
• D列: 出庫合計(=SUMIFS(入出庫データ!D:D, 入出庫データ!B:B, A2))
• E列: 在庫数(=C2-D2)

入出庫データの記録と在庫計算の手順

実際にテンプレートを構築する手順を説明します。新規スプレッドシートを作成し、上記の3シートを準備してください。

1. シートを作成する
   シート名を「商品マスタ」「入出庫データ」「在庫一覧」に変更します。必要に応じて色分けすると見やすくなります。
2. 商品マスタにデータを入力する
   商品コードと商品名を入力します。商品コードは重複しないように注意してください。例:P001、P002など。
3. 入出庫データシートの見出し行を設定する
   1行目に日付、商品コード、入庫数、出庫数、備考と入力します。日付列は表示形式を日付に設定します。
4. 在庫一覧シートで商品コードをリストアップする
   商品マスタの商品コードを=商品マスタ!A2:Aで参照するか、直接入力します。一意のコードを並べます。
5. 商品名を自動表示する
   在庫一覧のB2セルに次の関数を入力します:=VLOOKUP(A2,商品マスタ!A:B,2,FALSE)。これで商品コードに対応する商品名が表示されます。
6. 入庫合計を計算する
   C2セルに=SUMIFS(入出庫データ!C:C,入出庫データ!B:B,A2)と入力します。これでA2の商品コードに一致する入庫数の合計が求まります。
7. 出庫合計を計算する
   D2セルに=SUMIFS(入出庫データ!D:D,入出庫データ!B:B,A2)と入力します。
8. 在庫数を計算する
   E2セルに=C2-D2と入力します。これで現在庫が算出されます。
9. 数式を下方にコピーする
   在庫一覧の2行目から数式を必要な行までオートフィルでコピーします。商品コードの行数だけ数式が適用されます。

データの入力規則でエラーを防ぐ

商品コードの入力を正確にするために、入出庫データのB列にドロップダウンリストを設定します。商品マスタの商品コードを範囲に指定すれば、選択式で入力できます。これにより存在しないコードの入力を防止します。

1. 入力規則を設定する列を選択する
   入出庫データシートのB列全体(B:B)を選択します。
2. 「データ」メニューから「データの入力規則」を開く
   「基準」で「リストを範囲で指定」を選び、「範囲」に商品マスタ!A2:Aと入力します。
3. 「無効なデータを拒否する」にチェックを入れる
   これでリストにないコードを入力できなくなります。

注意点とよくある失敗例

商品コードの重複を許してしまう

商品マスタで同じコードを複数登録すると、VLOOKUPやSUMIFSが正しく動作しません。商品コード列に重複を防ぐ入力規則を設定するか、UNIQUE関数を使用して一覧を作成すると良いでしょう。

日付の形式が統一されていない

日付列に文字列や異なる形式が混在すると、後で並べ替えや抽出が困難になります。列全体に日付の表示形式を適用しましょう。また、今日の日付を自動入力したい場合は、TODAY関数を使うと便利です。

在庫数がマイナスになる場合の対処

理論上は出庫が入庫を上回るとマイナスになりますが、現実的にはミスや未入荷の可能性があります。条件付き書式を使って在庫数がマイナスのセルを赤く塗るなど、視覚的に注意喚起すると良いでしょう。

大量データでのパフォーマンス低下

入出庫データが数千行を超えると、SUMIFSの計算が遅くなることがあります。その場合は、QUERY関数やピボットテーブルを利用して集計する方法も検討してください。また、データを定期的にアーカイブするのも一手です。

SUMIFSとQUERYの比較

項目	SUMIFS関数	QUERY関数
記述の簡潔さ	複数の条件を指定しやすい	SQLライクで柔軟だがやや複雑
パフォーマンス	データが多いと遅くなることがある	集計処理が高速で大規模データに強い
動的な範囲	範囲を固定する必要がある	範囲を指定して自動拡張できる
学習コスト	低い	中程度(SQLの知識が必要)

まとめ

この記事では、Googleスプレッドシートを使った在庫管理シートの基本構造を解説しました。入出庫データシート、商品マスタ、在庫一覧の3シート構成で、SUMIFS関数を利用して在庫数を自動計算する方法を紹介しました。このテンプレートを応用すれば、発注点の通知や在庫回転率の分析も可能です。まずは簡単なデータで動作を確認し、自社の運用に合わせて列や関数を追加してみてください。データの入力規則や条件付き書式を組み合わせることで、より実践的な在庫管理ツールに成長します。