在庫管理システムをエクセルで自作。決めるのは3つだけ
結論から、いきます。在庫管理で困っているのは「表が無い」ことではありません。「帳簿と棚の数が合わない」ことです。
表の上では10個あるのに、棚には7個しかない。だから結局、棚を見に行く。見に行くなら、表の意味がありません。
そして、数が合わなくなる原因は、関数でもマクロでもなく、記録のしかたにあることがほとんどです。
だから、表を作る前に決めることがあります。3つだけです。今日は、その3つと、合う状態を保つための表の形を置いていきます。
エクセルで在庫管理システムは自作できるのか？
できます。品目がそれほど多くなく、記録する人が限られている会社なら、十分に回ります。
行数の心配も、まず要りません。エクセルのワークシートは1,048,576行まで使えます（Microsoft「Excel の仕様と制限」）。1日に100件記録しても、埋まるまで28年以上かかります。
足りなくなるのは行数ではなく、「記録が続くかどうか」です。
自作のメリットは3つです。
- 費用がかからない … 手元のエクセルで始められます
- 自社の呼び方と手順に合わせられる … 品番の付け方も、棚の呼び方も会社ごとに違います
- 要る機能が分かってから、既製品を選べる … 回してみると、何が足りないかがはっきりします
デメリットは、同時に記録する人が増えると回らなくなることと、作った人がいなくなると直せなくなることです。詳しくは、下の「限界」と「やめたほうがいい場合」に書きました。
無料で作るなら、エクセルとスプレッドシートのどちらか？
記録する人が1〜2人ならエクセル、数人で同時に記録するならGoogleスプレッドシートです。
- エクセル … 社内にあれば追加の費用はかかりません。ただし、複数の人が同時に編集するには条件があります（下の「限界」に書きました）
- Googleスプレッドシート … Googleアカウントがあれば無料で使えて、同じ表を複数の人が同時に編集できます。Googleフォームと組み合わせると、スマホから入出庫を送って、表に1行ずつ積むこともできます
このあと書く表の作り方（履歴を積んで、SUMIFSで今の在庫を出す）は、どちらでも同じように動きます。道具より先に、次の3つを決めてください。
自作する前に決める、3つのこと
① 何を数えるか
全部数えようとしないでください。
品目が300あるなら、300を毎日きっちり合わせるのは無理があります。続かなくなって、全部が合わなくなります。
おすすめは、切れたら困るもの・金額が大きいものからです。売上や出荷の多い順に並べて、上から数えていく。並べ方はABC分析のやり方に書きました。
残りは、月に1回まとめて見るくらいで足ります。
② いつ、誰が記録するか
ここがいちばん大事で、いちばん飛ばされます。
「夕方にまとめて入力する」「週末に誰かが伝票から打ち込む」。この形だと、必ず抜けます。抜けた1件が、そのまま「表と棚の差」になります。
⚠ おすすめは「棚から出した人が、その場で記録する」です。あとでまとめて、は続きません。
③ 合わなかったとき、誰が直すか
記録がきちんとしていても、数はズレます。数え間違い、破損、記録し忘れ。ゼロにはなりません。
だから、棚卸をいつやるかと、差が出たときに誰が表を直すかを先に決めておきます。
ここが決まっていないと、差を見つけた人がそれぞれ勝手に数字を書き換えます。そうなると、もう誰も表を信じなくなります。
エクセルの表は、どう作ればいいのか？
在庫数のセルを、毎回書き換える表にしないでください。
よくあるのは、品目の横に「在庫数」の列があって、出すたびにその数字を上書きしていく表です。これだと、数が合わなくなったときに原因を追えません。いつ、どの記録で狂ったのかが、どこにも残っていないからです。
代わりに、入出庫を1件ずつ下に積んでいきます。
「履歴」という名前のシートを作り、1件ずつ下に足していくだけです。
| A：日付 | B：品目 | C：区分 | D：数量 |
|---|---|---|---|
| 9/1 | ボルトM6 | 入庫 | 200 |
| 9/3 | ボルトM6 | 出庫 | 30 |
| 9/5 | ボルトM6 | 出庫 | 45 |
| 9/30 | ボルトM6 | 調整 | -5 |
最後の「調整」は、棚卸で数が合わなかった分です。
今の在庫は、別のシートで計算させます。A列に品目名（A2に「ボルトM6」）を入れ、B2にこの式を1つのセルに続けて入力します。
=SUMIFS(履歴!D:D,履歴!B:B,A2,履歴!C:C,"入庫")-SUMIFS(履歴!D:D,履歴!B:B,A2,履歴!C:C,"出庫")+SUMIFS(履歴!D:D,履歴!B:B,A2,履歴!C:C,"調整")入庫の合計から出庫の合計を引き、調整の合計を足す、という意味です。
上の例なら、200から30と45を引き、調整の−5を足して、今の在庫は120個です。
履歴を積む形にしておくと、数が合わなかったときに1行ずつさかのぼれます。「3日の出庫、本当は40だった」と分かれば、その行を直すだけです。
⚠ 棚卸で出た差も、在庫の数字を書き換えずに「調整」の行として積みます。どれだけズレたかが記録に残るので、ズレやすい品目が見えてきます。
この「履歴」と「品目」の2枚を、そのまま使えるエクセルにしました。登録もメールアドレスも要りません。
履歴に1行ずつ足すと、品目ごとの今の在庫が自動で出ます。品目と区分はプルダウンで選ぶ形にしてあり、下限を切った品目だけが赤く塗られます。古い版のエクセルでも動く式だけで作っています。
在庫管理表を見やすくするには？
入出庫管理表は、作った直後がいちばん見やすくて、半年後がいちばん見づらいです。列が増えるからです。
見やすさは、足すのではなく削って作ります。4つだけ挙げます。
- 列は5つまで。日付・品目・区分・数量・メモ。「担当者」「棚番」「単価」は、使う場面が本当にあるか確かめてから足す
- 1行目を固定する（表示 → ウィンドウ枠の固定）。1,000行を超えると、見出しが見えないだけで読めなくなります
- 色は1色だけ。「在庫が下限を切った品目」だけを塗ります。3色使うと、どれが緊急か分からなくなります
- 品目と区分はプルダウンにする（データ → データの入力規則 → リスト）。「ボルトM6」「ボルト M6」「ﾎﾞﾙﾄM6」が混ざると、SUMIFSは別のものとして数えます
⚠ 最後のひとつが、いちばん多い失敗です。式が悪いのではなく、打ち方がそろっていないだけで数が合わなくなります。
売り切れは、在庫管理の失敗なのか？
先日、こんなことがありました。
三連休の晴れた日に、佐布里池の梅まつりへ行こうとしたんです。駐車場は満車。臨時も満車。シャトル乗り場も満車。「三連休でも空いてるやろ」は、完全に思い込みでした。
ほんなら、と行き先を変えて、日曜限定の「いかの姿焼き」を目当てに、えびせんべいの里へ。あれ、ほんま好きなんですよ。ところが着いたら、人、人、人。「本日分は終了しました」。完売でした。
悔しかったんですけど、帰り道に考えたんです。天気は前の日に分かる。三連休も分かる。日曜限定も決まっている。読める条件がこれだけそろっていて、仕込む量はどこまで攻める設計なんやろ、と。
売り切れは、人気の証明です。でも、機会の放棄でもあります。多めに仕込むのか、希少さを守るのか。どちらを選んでもいい。大事なのは、選んだ結果を記録に残しているかどうかです。
在庫の表でも同じです。欠品した日は、メモの列に「欠品」と書く。それだけで、半年後には「月末と連休の前に切れる」といった波が見えてきます。下限の数は、一度決めたら終わりではありません。読める波に合わせて、変えていくものです。
エクセルの自作は、どこで限界が来るのか？
2つあります。
① 同時に記録する人が増えたとき
エクセルで複数の人が同じファイルを同時に編集するには、Microsoft 365のサブスクリプションと、OneDriveやSharePoint Onlineへの保存が条件です（Microsoft サポート「Excel ブックを共同編集で同時に共同作業する」）。
社内の共有フォルダに置いたファイルを数人で開く使い方だと、誰かが開いている間は、ほかの人が保存できない場面が出ます。そうなると記録が後回しになり、②の「その場で記録」が崩れます。
② 棚の前で、パソコンを開けないとき
倉庫や現場にパソコンが無い。手が汚れている。だから、あとでまとめて入力する。そして抜けます。
この2つが見えてきたら、表の作り方ではなく「記録する場所」を変える時期です。
スマホやAIを使うと、何が変わるのか？
記録する場所が、パソコンの前から棚の前に移ります。
スマホの1画面で「品目・区分・数量」だけを送って、送ったものがスプレッドシートの履歴に1行ずつ積み上がる形です。上で作った表の形は、そのまま使えます。
先日、これと同じ形で売上の入力の仕組みを作って納めました。在庫ではなく売上ですが、分かったことは同じです。入力の項目を削るほど、記録が続きます。「あれば便利」で足した1項目が、その仕組みを止めます。
入庫のときに届く納品書なら、撮った写真から品目と数量を読み取らせることもできます。ただし読み取りは100%にはなりません。違っていたときに、その場で直せる形にしておくことが前提です。読み取りの限界は手書き伝票をAIで読み取るに書きました。
自作をやめたほうがいいのは、どんな場合か？
4つあります。
- 社内に、表の面倒を見られる人がいない … 作った人がいなくなると、式が壊れても誰も直せません
- ロット番号・使用期限・シリアル番号まで追う必要がある … エクセルでは記録が重くなりすぎます
- 会計や受発注と、在庫を必ず連動させたい … 既製品のほうが確実です
- 今すぐ必要で、試す時間がない … 既製品のほうが早いです
⚠ 1つ目がいちばん多いです。「作れるかどうか」ではなく「続けられるかどうか」で決めてください。
⚠ 既製の在庫管理システムが不要という話ではありません。まずエクセルで回してみると、自社に何の機能が要るのかが分かった状態で選べます。集計の自動化の順番はExcelでやっていた集計を自動化する、最初の一歩にまとめています。
今日やること
紙に3行、書いてください。
① まず数えるのは、どの品目か（10個まで）：__________
② 棚から出すとき、誰がその場で記録するか：__________
③ 数が合わなかったとき、誰が表を直すか：__________この3行が埋まったら、表はもう半分できています。関数はあとから入れられます。
逆に、②が埋まらないなら、まだ作らないほうがいいです。作っても、また合わなくなります。
「うちは数が合わないのが当たり前になっている」と思われたかもしれません。それは、あなたの会社の人がいいかげんだからではありません。記録のしかたが決まっていなかっただけです。決めれば、合うようになります。応援しています！
よくある質問
在庫管理システムはエクセルで自作できますか？
できます。品目がそれほど多くなく、記録する人が限られている会社なら、エクセルで十分に回ります。ワークシートは1,048,576行まで使えるので、行数が先に足りなくなることはまずありません。つまずくのは道具ではなく記録のしかたです。何を数えるか、いつ誰が記録するか、合わなかったとき誰が直すか。この3つを決めてから表を作ると、帳簿と棚の数が合う状態を保ちやすくなります。
在庫管理システムは無料で自作できますか？
できます。社内にエクセルがあれば、追加の費用はかかりません。数人で同時に記録したいなら、Googleスプレッドシートが向いています。Googleアカウントがあれば無料で使え、同じ表を複数の人が同時に編集できます。Googleフォームを使えば、スマホから入出庫を送って、スプレッドシートに1行ずつ積むこともできます。どちらを選んでも、表の形と、誰がいつ記録するかの決め方は同じです。
エクセルの在庫表は、どう作ればいいですか？
在庫数のセルを毎回書き換える表にしないことです。入庫と出庫を1件ずつ、日付・品目・入出庫の別・数量で下に積んでいく履歴のシートを作り、今の在庫はSUMIFS関数で入庫の合計から出庫の合計を引いて出します。書き換える表だと、数が合わなくなったときに原因を追えません。履歴を積む表なら、いつどの記録で差が出たかをさかのぼれます。棚卸で出た差も、調整の行として積みます。この形のテンプレート（エクセル・無料・登録不要）を記事内からダウンロードできます。
エクセルの在庫管理は、どこで限界が来ますか？
多いのは2つです。1つ目は、同時に記録する人が増えたとき。エクセルで複数人が同時に編集するには、Microsoft 365のサブスクリプションと、OneDriveやSharePoint Onlineへの保存が条件になります。2つ目は、棚の前でパソコンを開けないとき。記録があとでまとめてになり、数が合わなくなります。この2つが見えてきたら、スマホから記録できる形か、既製の在庫管理システムを検討する時期です。
自作をやめて既製品にしたほうがいいのは、どんな場合ですか？
社内に表の面倒を見られる人がいない場合、ロット番号や使用期限、シリアル番号まで追う必要がある場合、会計や受発注と在庫を必ず連動させたい場合、今すぐ必要で試す時間がない場合です。とくに面倒を見る人がいないケースは多く、作った人が異動や退職をすると、式が壊れても誰も直せなくなります。作れるかどうかではなく、続けられるかどうかで決めてください。
欠品を減らすには、在庫の下限をどう決めればいいですか？
最初は、ふだんの出庫量と仕入れにかかる日数から仮の下限を決めます。そのうえで、欠品した日を記録に残してください。入出庫表のメモ列に欠品と書くだけで構いません。半年分たまると、月末や連休前、取引先の繁忙期など、切れやすい時期が見えてきます。下限は一律にせず、その波に合わせて時期ごとに変えるほうが欠品は減ります。
小さな会社の売上アップ・利益改善のご相談を承っています。
お問い合せはこちら