「エクセルで在庫管理表を作りたいけれど、どの項目を入れればよいかわからない」
「在庫数が合わない、最新版がわからない、担当者によって入力ルールが違う」
このような悩みがある場合、まずは商品マスタ・入出庫履歴・現在庫・発注点を整理した在庫管理表を作ることが重要です。
エクセルは、商品数や入出庫件数がまだ多くない段階では、在庫管理を始めるための有効な手段です。
一方で、入力漏れや更新忘れ、ファイルの上書き、担当者ごとのルール違いがあると、関数を入れていても在庫数は合わなくなります。
この記事では、エクセル在庫管理表の基本項目、作り方、便利な関数、失敗しないための運用ルールを解説します。
さらに、エクセルで対応できる範囲と、在庫情報を取引先に見える化するためにシステム化を検討すべきタイミングも紹介します。
実務ですぐに使える「在庫管理表テンプレート」も無料で配布しています。
まずはテンプレートを使いながら、自社の在庫管理を見える化していきましょう。
※テンプレートの使い方は、本記事の第3章で分かりやすく解説しています。
単なる作り方の解説にとどまらず、現場で起こりがちな「複数人編集によるエラー」や「入力ミス」を防ぐためのプロのテクニックも公開。
この記事を読むだけで、今日からミスなくスムーズな在庫管理がスタートできます。
【この記事でわかること】 |
目次
1. エクセル在庫管理は、まず「在庫数が合う状態」を作ることが重要
エクセルで在庫管理を始める目的は、単に表を作ることではありません。
重要なのは、誰が見ても同じ在庫数を確認でき、必要なタイミングで発注や出庫判断ができる状態を作ることです。
在庫管理がうまくいっていない現場では、次のような問題が起こりがちです。
- 実際の在庫数とエクセル上の在庫数が合わない
- 最新ファイルがどれかわからない
- 商品名や品番の入力ルールが担当者ごとに違う
- 入庫や出庫の記録が後回しになり、更新漏れが発生する
- 発注点を下回っていることに気づかず、欠品が起きる
こうした問題は、関数だけでは解決できません。
管理表の項目設計、入力ルール、更新タイミング、担当者の役割をセットで決める必要があります。
そのため、エクセル在庫管理では「表の作り方」と「運用の仕方」を分けずに考えることが大切です。
2. エクセル在庫管理表に必要な基本項目
在庫管理表を作るときは、まず管理すべき項目を整理しましょう。
項目が不足していると、後から在庫差異の原因を追いづらくなります。
一方で、最初から項目を増やしすぎると入力負担が大きくなり、更新が続かなくなります。
まずは、次の項目を基本として設計するのがおすすめです。
| 項目 | 役割 | 入力・管理のポイント |
| 商品番号・品番 | 商品を一意に識別するための番号 | 重複しない番号を使い、表記ゆれを防ぐ |
| 商品名 | 商品の名称 | 色・サイズ・規格違いがある場合は名称に含める |
| 繰越在庫数・月初在庫数 | 管理開始時点の在庫数 | 棚卸後の確定数を入力する |
| 入庫数 | 仕入れ・返品などで増えた数量 | 入庫が発生した日に記録する |
| 出庫数 | 出荷・販売・使用などで減った数量 | 出庫が発生した日に記録する |
| 現在在庫数 | 現時点で残っている数量 | 繰越在庫＋入庫数−出庫数で算出する |
| 発注点 | 発注が必要になる在庫数 | リードタイムや販売数量を踏まえて設定する |
| ステータス | 発注要否を判断する表示 | 「要発注」「在庫あり」などを自動表示する |
| 棚卸差異 | 実在庫と帳簿在庫の差 | 差異の原因分析に使う |
| 更新日・更新者 | 誰がいつ更新したかの記録 | 複数人運用では必ず残す |
特に重要なのは、商品番号・品番を軸に管理することです。
商品名だけで管理すると、全角・半角、スペースの有無、略称の違いによって集計ミスが起こりやすくなります。
たとえば「A-001」と「Ａ−００１」、「ネジ M4」と「M4ネジ」が別の商品として扱われると、関数で正しく集計できません。
商品番号をマスタ化し、プルダウンで選択できるようにしておくと、入力ミスを減らせます。
3. テンプレート運用を破綻させない3つの鉄則
ここからは、エクセルで在庫管理表を作る手順を解説します。
本記事で配布しているテンプレートは、「商品マスタ」「入出庫履歴」「在庫管理」の3つの考え方で構成されています。
まずはテンプレートをダウンロードし、自社の商品情報に合わせて入力してみてください。
3-1. ステップ1：「商品マスタ」を作成する
▼テンプレートの「商品マスタ・在庫管理」タブ左側が入力エリア
最初に作るのは、在庫管理の基礎となる商品マスタです。
商品マスタには、以下のような情報を入力します。
- 商品番号・品番
- 商品名
- 月初在庫数
- 発注点
商品番号は、在庫管理で最も重要なキーになります。
必ず重複しない番号を設定し、同じ商品を複数の名称で登録しないようにしましょう。
商品名には、現場の担当者が見てすぐ判別できる名称を入れます。
色違い、サイズ違い、容量違い、型番違いがある商品は、商品名だけで区別できるようにしておくと誤出荷や入力ミスを防ぎやすくなります。
月初在庫数には、管理を開始する時点の在庫数を入力します。
棚卸後の数量をもとに設定すると、エクセル上の在庫数と実在庫の差異を抑えやすくなります。
発注点には、「この数量以下になったら発注する」という基準を入力します。
発注点を設定しておくと、現在在庫数が少なくなったときに自動でアラートを出すことができます。
3-2. ステップ2：「入出庫履歴」を入力する
次に、日々の入庫数と出庫数を記録する入出庫履歴を作成します。
入出庫履歴には、以下の項目を用意します。
- 日付
- 商品番号・品番
- 商品名
- 入庫数量
- 出庫数量
入庫や出庫が発生したら、その都度、入出庫履歴に記録します。
ここで大切なのは、現在在庫数を直接書き換えないことです。
在庫数を直接上書きすると、なぜ在庫が増減したのかを後から追えなくなります。
入庫数と出庫数を履歴として残し、現在在庫数は関数で自動計算する形にしましょう。
商品番号は、商品マスタからプルダウンで選択できるようにしておくと便利です。
商品番号を選ぶと商品名が自動表示されるようにしておけば、商品名の入力ミスや表記ゆれを防げます。
商品名のC列（例えばC3セルの場合）には、「=vlookup(B3,’商品マスタ’!A:B,2,FALSE)」という関数を入れてあり、「商品マスタ」の商品番号に対応する商品名が自動で表示されるようになっています。
3-3. ステップ3：「現在在庫数」と「発注ステータス」を確認する
入出庫履歴を入力したら、商品マスタと履歴をもとに現在在庫数を計算します。
基本的な考え方は、次のとおりです。
| 現在在庫数 ＝ 繰越在庫数 ＋ 入庫数の合計 − 出庫数の合計 |
たとえば、月初在庫数が15個、入庫数の合計が0個、出庫数の合計が15個の場合、現在在庫数は0個です。
| 15＋ 0 − 15 ＝ 0 |
さらに、現在在庫数が発注点以下になったときに「要発注」と表示されるように設定しておくと、発注漏れを防ぎやすくなります。
| =IF(A3=””,””,IF(H3<=D3,”要発注”,”在庫あり”)) |
このように、現在在庫数と発注ステータスを自動表示できるようにしておくと、担当者が毎回手作業で計算する必要がなくなります。
4. エクセル在庫管理で使える基本関数・設定
エクセル在庫管理では、複雑な関数を大量に使う必要はありません。
まずは、入力ミスを減らし、入出庫数を正しく集計するための基本的な関数を押さえましょう。
| 関数・設定 | 役割 | 活用例 |
| SUMIF関数 | 条件に一致する数量を合計する | 商品番号ごとの入庫数・出庫数を集計する |
| VLOOKUP関数 | 商品番号に対応する情報を表示する | 商品番号を選ぶと商品名を自動表示する |
| IF関数 | 条件によって表示を切り替える | 在庫数が発注点以下なら「要発注」と表示する |
| IFERROR関数 | エラー表示を見やすく整える | 未入力時の「#N/A」などを非表示にする |
| 条件付き書式 | 条件に応じてセルの色を変える | 発注点以下の商品を赤く表示する |
| データの入力規制 | 入力できる値を制限する | 商品番号をプルダウン選択にする |
4-1. SUMIF関数で入庫数・出庫数を集計する
SUMIF関数を使うと、商品番号が一致する入庫数や出庫数だけを合計できます。
入庫数の合計を出す例は、次のとおりです。
| =SUMIF(‘入出庫履歴’!B:B,A3,’入出庫履歴’!D:D) |
この式では、入出庫履歴のB列にある商品番号がA3と一致する行だけを対象に、D列の入庫数を合計します。
出庫数も同じ考え方で集計できます。
| =SUMIF(‘入出庫履歴’!B:B,A3,’入出庫履歴’!E:E) |
商品数が増えても、商品番号ごとの入庫・出庫を自動で集計できるため、手計算によるミスを防ぎやすくなります。
4-2. VLOOKUP関数で商品名を自動表示する
商品番号を入力したときに商品名を自動表示したい場合は、VLOOKUP関数を使います。
| =VLOOKUP(B3,’商品マスタ’!A:B,2,FALSE) |
この式では、B3に入力された商品番号を商品マスタのA列から探し、対応する商品名をB列から表示します。
商品名を毎回手入力すると、表記ゆれが起こりやすくなります。
商品番号を選ぶだけで商品名が表示される仕組みにしておくと、集計ミスや検索漏れを防ぎやすくなります。
4-3. IF関数と条件付き書式で発注漏れを防ぐ
IF関数を使うと、現在在庫数が発注点以下になった場合に「要発注」と表示できます。
| =IF(A3=””,””,IF(H3<=D3,”要発注”,”在庫あり”)) |
さらに、条件付き書式を使って「要発注」のセルを赤く表示すれば、発注が必要な商品を一目で確認できます。
在庫管理では、数字を正しく計算するだけでなく、対応が必要な商品にすぐ気づけることも重要です。
関数と見た目の工夫を組み合わせることで、確認漏れを減らしましょう。
5. エクセルの関数はAIで作成・確認することもできる
SUMIF関数やVLOOKUP関数に不慣れな場合は、ChatGPTなどの生成AIを使って関数を作成することもできます。
たとえば、次のように依頼すると、在庫管理表で使う関数の候補を出力できます。
| “`text エクセルで在庫管理表を作っています。 「入出庫履歴」シートのB列に商品番号、D列に入庫数、E列に出庫数があります。 「在庫管理」シートのA3にある商品番号と一致する入庫数の合計を出す関数を教えてください。 “` |
このように、シート名、列名、どのセルに商品番号があるかを具体的に伝えると、AIから実務に近い関数を得やすくなります。
ただし、AIが出力した関数は、そのまま使うのではなく、必ず自社の表に合わせて確認しましょう。
シート名や列番号が違うと、正しく計算されない場合があります。
AIを使うときは、以下の情報をセットで伝えるのがおすすめです。
|
たとえば、発注点以下になった商品を「要発注」と表示したい場合は、次のように依頼できます。
“`text |
AIは関数作成の補助には便利ですが、在庫管理そのものを自動化するものではありません。
入力漏れや更新忘れ、ファイルの上書きなどの運用課題は、別途ルールを決めて防ぐ必要があります。
6. エクセル在庫管理でよく起きる失敗と原因
エクセル在庫管理では、表の設計が正しくても、運用が崩れると在庫数が合わなくなります。
ここでは、現場で起こりやすい失敗と原因を整理します。
| よくある失敗 | 主な原因 | 対策 |
| 在庫数が合わない | 入力漏れ、二重入力、更新忘れ | 入出庫の入力タイミングを決める |
| 最新ファイルがわからない | ファイルを複製して個別管理している | 保存場所とファイル名を統一する |
| 関数が壊れる | 計算セルを誤って上書きする | 入力セル以外を保護する |
| 商品名がばらつく | 手入力で商品名を入力している | 商品マスタとプルダウンを使う |
| 担当者ごとに数値が違う | 更新ルールが共有されていない | 担当者・更新頻度・締め時間を決める |
| 棚卸差異の原因が追えない | 履歴や更新者が残っていない | 入力者・更新日・備考欄を残す |
6-1. 入力漏れ・更新忘れ
最も多いのが、入庫や出庫が発生したのにエクセルへ反映されていないケースです。
現場で紙にメモし、あとでまとめて入力する運用では、記録漏れや転記ミスが起こりやすくなります。
特に、出荷作業が忙しい時間帯や、複数人で作業している現場では注意が必要です。
対策として、「入庫・出庫が発生した当日中に入力する」「毎日15時までの入出庫を当日分として締める」など、更新タイミングを明確に決めましょう。
6-2. 二重入力
同じ入出庫を複数回入力してしまうと、エクセル上の在庫数が実在庫とずれてしまいます。
二重入力は、担当者間で入力済みかどうかが共有されていない場合に起こりやすくなります。
注文番号、納品書番号、出荷指示番号などを備考欄に残しておくと、同じ処理を重複して入力していないか確認しやすくなります。
6-3. ファイルの上書き・最新版不明
エクセルファイルをメールでやり取りしたり、担当者ごとにコピーして編集したりすると、どのファイルが最新版かわからなくなります。
「在庫管理表_最新版」「在庫管理表_修正版」「在庫管理表_最終」などのファイルが増えると、誤ったファイルを見て発注判断をしてしまうリスクがあります。
共有フォルダやクラウドストレージを使い、保存場所とファイル名のルールを固定しましょう。
過去ファイルを残す場合は、日付を付けてアーカイブフォルダに移動するなど、最新版と混ざらないようにします。
6-4. 担当者ごとの入力ルールの違い
商品名、品番、数量、日付の入力方法が担当者ごとに違うと、関数で正しく集計できないことがあります。
たとえば、日付を「2026/4/1」と入力する人と「4月1日」と入力する人が混在すると、期間集計がしづらくなります。
品番にハイフンを入れるかどうかが統一されていない場合も、別商品として扱われる可能性があります。
入力形式はできるだけ手入力に頼らず、プルダウン、入力規則、マスタ管理で統一しましょう。
7. 在庫管理表を破綻させない運用ルール
エクセル在庫管理を続けるには、関数よりも運用ルールが重要です。
誰が、いつ、どのタイミングで更新するかを決めておかないと、在庫数はすぐにずれてしまいます。
最低限、以下のルールを決めておきましょう。
| 決めること | ルール例 |
| 更新担当者 | 入庫は購買担当、出庫は出荷担当が入力する |
| 更新タイミング | 入出庫が発生した当日中に入力する |
| 締め時間 | 毎日15時時点の在庫を当日分として確認する |
| 確認担当者 | 毎日終業前に管理者が「要発注」を確認する |
| ファイル保存場所 | 共有フォルダの指定場所に1ファイルだけ置く |
| 入力禁止セル | 関数セルは保護し、入力欄だけ編集可能にする |
| 棚卸ルール | 月1回、実在庫とエクセル在庫を照合する |
7-1.入力する人と確認する人を分ける
入力担当者と確認担当者を分けると、ミスに気づきやすくなります。
たとえば、出荷担当者が出庫数を入力し、管理者が毎日終業前に発注ステータスを確認する運用にすれば、入力と判断の役割が明確になります。
また、すべてを1人に任せると担当者が不在のときに更新が止まり、属人化しやすくなります。
複数人で運用する場合は、代理担当者も決めておくと安心です。
7-2.現在在庫数を直接上書きしない
現在在庫数は、入出庫履歴から自動計算する項目です。
実在庫と合わないからといって、現在在庫数のセルを直接修正すると、差異の原因がわからなくなります。
棚卸で差異が出た場合は、棚卸差異として履歴に残し、調整数を入力する形にしましょう。
たとえば、エクセル上の在庫が100個、実在庫が98個だった場合は、現在在庫数を直接98に書き換えるのではなく、「棚卸差異 -2」として記録します。
こうすることで、いつ、どの商品の差異を調整したのかが残ります。
7-3.月次で棚卸差異を確認する
エクセル管理では、日々の入力が正しくても、実在庫とのズレが少しずつ発生することがあります。
そのため、月1回など定期的に棚卸を行い、エクセル上の在庫数と実在庫を照合しましょう。
差異が出た場合は、単に数値を合わせるだけでなく、原因を確認することが大切です。
- 入力漏れがあったのか
- 出庫数を間違えたのか
- 商品を取り違えたのか
- 破損・廃棄・サンプル使用などの記録が漏れていたのか
原因を把握できれば、次月以降の運用改善につなげられます。
8. エクセルで対応できる範囲と、システム化すべき範囲
エクセル在庫管理は便利ですが、すべての在庫管理に向いているわけではありません。
自社の業務規模や取引先とのやり取りに合わせて、エクセルで続けるべきか、システム化すべきかを判断しましょう。
| 状況 | エクセルで対応しやすい | システム化を検討すべき |
| 商品数 | 商品数が少ない | 商品数・品番数が多い |
| 更新頻度 | 入出庫が少ない | 毎日多くの入出庫がある |
| 利用人数 | 1〜2名で管理している | 複数部署・複数拠点で使う |
| 在庫確認 | 社内だけで確認する | 取引先にも在庫を見せたい |
| 受発注 | 電話・メール中心で件数が少ない | FAX・メール・Web注文が混在している |
| ミスの影響 | 手作業で修正できる範囲 | 欠品・誤出荷・機会損失につながる |
8-1.エクセルで対応しやすいケース
商品数が少なく、在庫の増減も限られている場合は、エクセルでも十分に管理できます。
たとえば、管理対象の商品が数十点程度で、更新担当者も1〜2名に限られている場合は、商品マスタと入出庫履歴を整えるだけでも在庫状況を把握しやすくなります。
また、まず在庫管理を始めたい段階では、エクセルで現状を見える化することに意味があります。
いきなり大きなシステムを導入する前に、どの商品がどれだけ動いているのか、どこで入力ミスが起きているのかを把握することで、次の改善策を考えやすくなります。
8-2.システム化を検討すべきケース
一方で、次のような状況が増えてきた場合は、エクセル管理の限界を考えるタイミングです。
- 複数人が同時に在庫情報を更新する
- 複数拠点の在庫をまとめて確認したい
- 取引先から在庫状況について頻繁に問い合わせがある
- 最新の在庫数がわからず、回答に時間がかかっている
- FAXやメールの注文を手作業で転記している
- 商品数や品番数が多く、目視確認に時間がかかる
- 担当者が変わると運用ルールが引き継がれない
特に、「在庫情報を取引先に見える化したい」場合は、エクセルだけで対応するのが難しくなります。
取引先が必要な在庫情報だけをオンラインで確認できるようにしたい場合は、受発注システムやBtoB ECの導入を検討するとよいでしょう。
| 取引先に在庫情報を見える化したい場合は、システム化も検討を |
エクセル在庫管理は、社内で在庫状況を整理するには便利です。 エクセルファイルを取引先に共有すると、
といった課題が起こりやすくなります。 在庫情報を取引先にわかりやすく見せたい場合は、BtoB受発注システムの活用も選択肢です。 BtoB受発注システム「WONDERCART」では、商品情報や在庫情報をオンラインで管理し、取引先が必要な情報を確認しながら注文できる環境づくりを支援します。
|
9. まとめ：エクセル在庫管理は「表」と「運用ルール」をセットで整える
エクセル在庫管理表は、商品マスタ、入出庫履歴、現在在庫数、発注点を整理することで、在庫状況を見える化できる便利な方法です。
ただし、在庫管理表は作っただけでは機能しません。
入力漏れ、二重入力、更新忘れ、ファイルの上書き、担当者ごとの入力ルールの違いがあると、関数を入れていても在庫数は合わなくなります。
エクセルで在庫管理を始めるときは、次のポイントを押さえましょう。
- 商品番号・品番を軸に商品マスタを作る
- 入庫数・出庫数を履歴として残す
- 現在在庫数は関数で自動計算する
- 発注点を設定し、要発注の商品を見える化する
- 誰が、いつ、どのタイミングで更新するかを決める
- 棚卸差異を記録し、原因を確認する
- 複数人・複数拠点・取引先共有が必要になったらシステム化を検討する
まずは、無料テンプレートを活用して、自社の在庫情報を整理するところから始めてみてください。
※テンプレートの使い方は、本記事の第3章で分かりやすく解説しています。
もし、エクセル管理を続ける中で「在庫数が合わない」「最新版がわからない」「取引先への在庫回答に時間がかかる」といった課題が出てきた場合は、管理方法を見直すサインです。
現在の業務フローを踏まえながら、無理なく受発注業務を効率化する方法を検討してみましょう。
エクセル管理の「その先」について、少しでも不安や課題を感じたら、ぜひお気軽に私たちにご相談ください。
あなたの現場に最適な、「無理のないデジタル化」を全力でサポートいたします。
#在庫管理 #エクセル
コメント