商品数が多い在庫管理をExcelで｜商品マスタを分けると崩れにくい

商品が増えると、記録のたびに商品名や単価を打ち直すことになり、表記ゆれで集計が合わなくなります。

解決は、商品の情報（コード・名前・単位・発注点）を「商品マスタ」という1枚に集め、記録にはコードだけを入れる形にすることです。

商品が数百になっても、同じ作りのまま使えます。

■1. 3枚のシートに分けます

商品マスタ：A コード／B 商品名／C 単位／D 発注点

記録：A 日付／B コード／C 数量（入庫はプラス、出庫はマイナス）／D 商品名（式で自動表示）

在庫表：A コード／B 商品名／C 現在庫／D 要発注（式で自動表示）

商品名を打つのは、商品マスタの1回だけにします。

■2. 記録の商品名を自動で出します

記録のD2に次の式を入れ、下の行へコピーします。

=IFERROR(VLOOKUP($B2,商品マスタ!$A:$B,2,FALSE),"未登録")

コードを入れると商品名が出ます。「未登録」と出たら、マスタにないコードを入れた合図です（打ち間違いに気づけます）。

最後の「FALSE」は、コードが完全に同じものだけを探す指定です。省略すると別の商品を拾うことがあるので、必ず付けます。

■3. 在庫表を作ります

A列に、商品マスタのコードを並べます（マスタの行を選んでコピー）。

B2に次の式を入れます。

=VLOOKUP($A2,商品マスタ!$A:$B,2,FALSE)

C2に次の式を入れます。

=SUMIF(記録!$B:$B,$A2,記録!$C:$C)

入力例：商品マスタに A001（ボルト・発注点30）・A002（ナット・発注点30）・A003（ワッシャー・発注点10）の3つを入れます。記録には次の6行を入れます。

10/1 A001 +50／10/3 A001 -10／10/5 A002 +30／10/8 A002 -5／10/9 A003 +20／10/9 A003 -20

結果は、A001が40、A002が25、A003が0になります。

■4. 発注点を下回ったら知らせます

在庫表のD2に、発注点を商品マスタから出します。

=VLOOKUP($A2,商品マスタ!$A:$D,4,FALSE)

E2に次の式を入れ、下へコピーします。

=IF(SIGN(C2-D2)=1,"","発注")

SIGNは、数が正なら1、0なら0、負なら-1を返します。在庫が発注点より多いときだけ空欄、同じか少ないときは「発注」と出ます。大小の記号を使わないので、書き間違えにくくなります。

入力例では、A001（40・発注点30）は空欄、A002（25・発注点30）とA003（0・発注点10）は「発注」と出ます。

■5. 商品を足すとき

商品マスタの末尾に1行足します。在庫表には、そのコードを1行足して式をコピーします。記録の式は直しません。

コードは英数字で統一し、前後にスペースを入れません。全角と半角が混ざると、別の商品として扱われます。

■6. 合わないときの確認

「未登録」と出る：コードの打ち間違い、前後のスペース、全角と半角のちがい。

在庫表の商品名に「#N/A」が出る：マスタにないコードを在庫表に入れています。

数量が文字になっている：左寄せで表示されます。数値に直します。

■7. この方法の限界

数千の商品、複数の置き場所、同時に何人もが書き込む場合は、Excelでは崩れやすくなります。やめ時の目安は別の記事で説明しています。

在庫管理のExcelテンプレートは、ASKOのサービス一覧にご用意しています。