エクセルで在庫管理表を作るとき、最初に決めるのは表の見た目ではなく「どこに何を記録するか」です。在庫数を1つのセルに上書きしていく表は作るのが簡単ですが、数が合わなくなったときに原因をたどれません。
この記事では、製造業の現場でエクセルの在庫管理表を作る手順を、シートの分け方、そのまま使える関数、発注点と色の警告、見やすくするコツ、よくある失敗の順に説明します。関数と操作は Microsoft の公式サポートページで確かめた内容です。
すぐ使える表が欲しい人は、製造業の在庫管理をExcelで始める方法 を先に見てください。この記事は、在庫管理表を自分の現場に合わせて作る・直すための手順です。
在庫管理表は3つのシートに分ける
| シート | 何を記録するか | 主な列 |
|---|---|---|
| 品目マスタ | 品目ごとに変わらない情報 | 品目コード、品目名、単位、期首在庫、発注点 |
| 入出庫 | 入庫・出庫の1件ごとの記録 | 日付、品目コード、区分（入庫／出庫）、数量、理由・伝票番号 |
| 在庫一覧 | 品目ごとの現在庫と判定（関数で自動計算） | 品目コード、品目名、単位、期首在庫、入庫計、出庫計、現在庫、発注点、判定 |
人が手で入力するのは「品目マスタ」と「入出庫」だけにします。在庫一覧は関数で計算するので、現在庫を手で書き換えることはありません。
現在庫は次の式で求めます。
現在庫 ＝ 期首在庫 ＋ 入庫の合計 − 出庫の合計
品目マスタの列
- 品目コード: 1品目に1つ。同じコードを2回登録しない（後で説明する入力規則で防げます）
- 品目名・単位: 単位は「枚」「本」「kg」など、出庫で数える単位にそろえる
- 期首在庫: 記録を始めた日、または直近の棚卸で数えた実数
- 発注点: この数を下回ったら発注を検討する数（決め方は後で説明します）
入出庫の列
1行に1件の動きを書きます。入荷したら「入庫」、製造で払い出したら「出庫」の行を足していくだけです。「理由・伝票番号」に納品書番号や製造指示の番号を書いておくと、あとで数が合わないときに追いかけられます。
| 日付 | 品目コード | 区分 | 数量 | 理由・伝票番号 |
|---|---|---|---|---|
| 9/1 | P-001 | 出庫 | 30 | 製造払出 |
| 9/3 | P-001 | 入庫 | 50 | 入荷 No.1021 |
| 9/5 | P-001 | 出庫 | 45 | 製造払出 |
P-001 の期首在庫が120枚なら、現在庫は 120 ＋ 50 − (30 ＋ 45) ＝ 95枚 です。
在庫管理に使うエクセル関数
ここからの式は、シート名が「品目マスタ」「入出庫」「在庫一覧」で、どのシートも1行目が見出し、データが2行目から始まる前提です。入出庫は2,000行目まで、品目マスタは500行目までを範囲にしています。足りなくなったら範囲を広げてください。
在庫一覧の列は、A 品目コード、B 品目名、C 単位、D 期首在庫、E 入庫計、F 出庫計、G 現在庫、H 発注点、I 判定 とします。
入庫・出庫の合計: SUMIFS関数
在庫一覧の E2（入庫計）に入れる式です。
=SUMIFS(入出庫!$D$2:$D$2000,入出庫!$B$2:$B$2000,$A2,入出庫!$C$2:$C$2000,"入庫")
F2（出庫計）は、最後の "入庫" を "出庫" に変えるだけです。
SUMIFS は、最初に「合計する範囲」を書き、そのあとに「条件を調べる範囲」と「条件」を組にして並べます。この式では「品目コードが A2 と同じ」かつ「区分が入庫」の行の数量を足しています。文字の条件は "入庫" のように二重引用符で囲みます。
現在庫と判定
G2: =D2+E2-F2
I2: =IF(G2<=H2,"要確認","")
現在庫が発注点以下になった品目に「要確認」と出ます。式を入れたら、品目の数だけ下の行にコピーします。
品目名を引く: XLOOKUP関数（Excel 2016・2019 は VLOOKUP）
在庫一覧の B〜D 列と H 列は、品目マスタから引いてきます。B2（品目名）の例です。
=XLOOKUP($A2,品目マスタ!$A$2:$A$500,品目マスタ!$B$2:$B$500,"コード未登録")
3つ目の範囲を品目マスタの単位・期首在庫・発注点の列に変えれば、C・D・H 列も同じ形で作れます。4つ目の引数は、コードが見つからなかったときに表示するものです。期首在庫（D列）と発注点（H列）は数の列なので、4つ目の引数を 0 にします。文字のままだと、現在庫の式（=D2+E2-F2）が #VALUE! になります。
入出庫シートにも F 列に「品目名（確認用）」を作り、同じ式で品目名を出しておくと、コードを打ち間違えた行がすぐ分かります。空の行に「コード未登録」と出ないように、=IF(B2="","",XLOOKUP(…)) のように包んでおきます。
XLOOKUP は Excel 2016 と Excel 2019 では使えません。その場合は VLOOKUP を使います。
=IFERROR(VLOOKUP($A2,品目マスタ!$A$2:$B$500,2,FALSE),"コード未登録")
FALSE は「完全に一致するものだけを探す」という指定です。IFERROR は、式がエラーになったときに代わりの値（ここでは「コード未登録」）を表示します。
直近30日の使用量: SUMIFS に日付の条件を足す
発注点を決めるには、1日にどれだけ使っているかが要ります。在庫一覧の J 列に「1日平均（直近30日）」を作り、次の式を入れます。
=SUMIFS(入出庫!$D$2:$D$2000,入出庫!$B$2:$B$2000,$A2,入出庫!$C$2:$C$2000,"出庫",入出庫!$A$2:$A$2000,">"&(TODAY()-30))/30
TODAY は今日の日付を返す関数で、">"&(TODAY()-30) は「今日の30日前より後の日付」という条件です。この式は休日を含めた暦日で割っています。後で使うリードタイムも暦日で数えてください（稼働日で数えるなら、使用量も稼働日で割ります）。
発注点の決め方
発注点は次の式で決めます。
発注点 ＝ 1日あたりの使用量 × リードタイム（日）＋ 安全在庫
品目マスタに「1日使用量」「リードタイム（日）」「安全在庫」の列を足し、発注点の列を式にします。1日使用量を F 列、リードタイムを G 列、安全在庫を H 列に置いた場合はこうなります。
=ROUNDUP(F2*G2+H2,0)
ROUNDUP の2つ目の引数を 0 にすると、小数を切り上げて整数にします。たとえば1日2.5枚使い、発注から入荷まで10日、安全在庫を20枚とすると、2.5 × 10 ＋ 20 ＝ 45枚 が発注点です。
1日使用量は毎日自動で変えるより、月に1回、在庫一覧の J 列を見て人が書き換えるほうが判断がぶれません。安全在庫の決め方と、発注済みでまだ届いていない分の扱いは 発注点の計算方法｜欠品を防ぐ在庫確認と見直し手順 で詳しく説明しています。
発注点を下回ったら色で知らせる（条件付き書式）
「要確認」の文字だけでは見落とすので、行ごと色を付けます。
- 在庫一覧の A2 から I 列の最終行までを選ぶ
- [ホーム] タブの [条件付き書式] > [新しいルール] を選ぶ
- 「数式を使用して、書式設定するセルを決定」を選び、数式を入れる
- [書式] で塗りつぶしの色を選んで OK
入れる数式は次のとおりです。
| 色 | 数式 | 意味 |
|---|---|---|
| 黄 | =AND($A2<>"",$G2<=$H2*1.2) | 発注点まであと2割（割合は品目に合わせて調整） |
| 赤 | =AND($A2<>"",$G2<=$H2) | 発注点以下。発注を検討する |
| 灰など | =AND($A2<>"",$G2<0) | 在庫がマイナス。入庫の記録漏れか入力ミス |
列の前にだけ $ を付けているので、選んだ範囲の行全体に色が付きます。数式は = で始め、結果が TRUE か FALSE になる形にします。
同じセルに複数のルールが当てはまって色がぶつかると、ルールの一覧で上にあるものが優先されます。新しいルールは一覧の先頭に追加されるので、黄 → 赤 → マイナスの順に作ると、マイナス・赤が黄より優先されます。
赤・黄・緑で在庫を見る考え方は、欠品を減らす「バッファ在庫」の考え方 にまとめています。
在庫管理表を見やすくするコツ
入出庫をテーブルにする
入出庫の範囲を選んで [ホーム] タブの [テーブルとして書式設定] を選ぶと、見出しにフィルターが付き、行が縞模様になります。品目や日付で絞り込むのが楽になり、1つのセルに式を入れると列全体に同じ式が入る「集計列」も使えます。
見出しを固定する
行が増えると見出しが画面から消えます。[表示] タブの [ウィンドウ枠の固定] で見出しの行を固定しておきます。
入力するセルと計算するセルの色を分ける
入力する列（日付・品目コード・区分・数量など）は白、式の入った列は薄い灰色、のように分けると、触ってよい場所が一目で分かります。
式の列はシートの保護で守る
入力するセルを選んで [セルの書式設定] の [保護] タブで [ロック] をオフにし、[校閲] タブの [シートの保護] をかけます。ロックしたままの式のセルは変更できなくなります。ただし、シートの保護は誤操作を防ぐためのもので、セキュリティの機能ではありません（Microsoft もそう説明しています）。
列を増やしすぎない
「あとで使うかもしれない」列を足していくと、入力の手間が増えて記録が止まります。最初は上の表の列だけで始め、必要になってから足します。
よくある失敗と防ぎ方
在庫数を上書きしてしまう
在庫一覧の現在庫のセルに、数え直した数を直接書き込むのが一番多い失敗です。式が消え、いつ何が起きたかの記録も残りません。
棚卸で帳簿と実数がずれたら、差の分を入出庫に1行足します。実数が3枚少なければ「出庫・3・棚卸差異」、多ければ「入庫」で記録します。ずれの原因の探し方は 在庫が合わない工場で、まず直す3つのこと を参考にしてください。
何人かで同時に開いて入力できない
エクセルで複数の人が同時に編集する「共同編集」には、条件があります。
- Microsoft 365 のサブスクリプションと、最新のバージョンの Excel を使う
- ファイル形式が .xlsx、.xlsm、.xlsb のいずれか
- ファイルを OneDrive、OneDrive for Business、SharePoint Online に置く（社内に置いた SharePoint のサーバーは対象外）
条件を満たさない置き方だと「ファイルがロックされています」と表示され、誰かが閉じるまで入力できないことがあります。その間に別の人がコピーを作って入力すると、ファイルが分かれて数が合わなくなります。
型番の表記ゆれで集計から漏れる
SUMIFS は品目コードが一致した行だけを足します。たとえば末尾に空白が入った「P-001 」は「P-001」とは別の文字なので、その行は集計から漏れます。手入力を減らすのが一番の対策です。
入出庫の品目コードはリストから選ばせる
- 入出庫の B 列（品目コード）を選ぶ
- [データ] タブの [データの入力規則] を開き、入力値の種類を「リスト」にする
- [ソース] に
=品目マスタ!$A$2:$A$500と入れる - [エラー メッセージ] タブで、リストにない値を入れたときのメッセージを設定する
区分の列も同じ手順で、[ソース] に 入庫,出庫 とコンマで区切って入れれば、2つから選ぶだけになります。
品目マスタのコードを重複させない
品目マスタの A 列に、入力値の種類「ユーザー設定」で次の式を入れます。
=COUNTIF($A$2:$A$500,A2)=1
同じコードがもう一つあると入力できなくなります。COUNTIF は英字の大文字と小文字を区別しないので、「p-001」と「P-001」も重複として止まります。
すでにある表記ゆれを直す
別の列に =TRIM(ASC(B2)) を入れると、全角の文字を半角にし（ASC）、余分な空白を取り除いた（TRIM）コードになります。結果を確かめてから、値として貼り付けて元の列と置き換えます。なお TRIM が取り除くのは通常の空白だけで、Web からコピーした文字に含まれることがある「改行しない空白」は残ります。
エクセルの限界と、システムに移る目安
ここまでの作り方で、品目が数百、入力する人が数人までの在庫管理なら十分に回ります。次のような状態になったら、エクセルで続けるか見直す時期です。
| 状態 | エクセルで起きること |
|---|---|
| 入力する人や拠点が増えた | 共同編集の条件を満たせないと、入力待ちやファイルのコピーが生まれる |
| 発注済み・入荷予定まで見たい | 発注残のシートと式が増え、作った人しか直せない表になる |
| ロット・使用期限で管理したい | 1品目1行の表では、同じ品目の中の違いを表せない |
| 毎朝、表を見て発注を決めている | 判断が担当者の目に頼ったままで、休むと止まる |
| 生産計画もエクセルで組んでいる | 在庫表と計画表を手で突き合わせる作業が増える |
発注の判断を手順にする方法は 在庫・発注の属人化を解消する手順、生産計画をエクセルで続けたときの壁は 生産計画をExcelで管理する方法と限界 で説明しています。
自分で式を組む時間がなく、すぐ使える表が欲しい人は 製造業の在庫管理をExcelで始める方法 を見てください。
エクセルの次の選択肢: AI工場長 becon
becon は、中小メーカーのための工場運営のツールです。在庫・発注の数字をまとめて見られるようにし、発注は提案まで用意します。決めるのは人です。今あるエクセルを捨てずに始められます。
- エクセルのデータを CSV で取り込める: 品目（商品マスタ）・入出庫・受注・発注を CSV で読み込みます。入出庫は「日付・品目コード（SKU）・区分・数量・理由」の形なので、この記事の入出庫シートとほぼ同じ列です。必須の列が足りない、マスタにない品目コード、日付や数量がおかしい、といった誤りが1行でもあると取り込まず、誤りの行を一覧で示します
- 在庫の状態を赤・黄・緑で表示: 在庫バッファ信号で、どの品目から手を打つべきかが分かります。欠品しそうな品目の予測も出します
- 発注は AI が提案し、人が決める: 発注ナビが発注の候補を出し、担当者が画面で確かめて承認します。AI が勝手に発注することはありません
becon の無料の現場診断では、いまのエクセルの在庫表と発注の流れを一緒に書き出し、becon でどこまで置き換えられるかをその場でお答えします。オンラインでも受けられ、資料の準備は要りません。