在庫管理システムをエクセルで自作。決めるのは3つだけ
結論から、いきます。在庫管理で困っているのは「表が無い」ことではありません。「帳簿と棚の数が合わない」ことです。
表の上では10個あるのに、棚には7個しかない。だから結局、棚を見に行く。見に行くなら、表の意味がありません。
そして、数が合わなくなる原因は、関数でもマクロでもなく、記録のしかたにあることがほとんどです。
だから、表を作る前に決めることがあります。3つだけです。今日は、その3つと、合う状態を保つための表の形を置いていきます。
エクセルで在庫管理システムは自作できるのか？
できます。品目がそれほど多くなく、記録する人が限られている会社なら、十分に回ります。
行数の心配も、まず要りません。エクセルのワークシートは1,048,576行まで使えます（Microsoft「Excel の仕様と制限」）。1日に100件記録しても、埋まるまで28年以上かかります。
足りなくなるのは行数ではなく、「記録が続くかどうか」です。
自作する前に決める、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つあります。
① 同時に記録する人が増えたとき
エクセルで複数の人が同じファイルを同時に編集するには、Microsoft 365のサブスクリプションと、OneDriveやSharePoint Onlineへの保存が条件です（Microsoft サポート「Excel ブックを共同編集で同時に共同作業する」）。
社内の共有フォルダに置いたファイルを数人で開く使い方だと、誰かが開いている間は、ほかの人が保存できない場面が出ます。そうなると記録が後回しになり、②の「その場で記録」が崩れます。
② 棚の前で、パソコンを開けないとき
倉庫や現場にパソコンが無い。手が汚れている。だから、あとでまとめて入力する。そして抜けます。
この2つが見えてきたら、表の作り方ではなく「記録する場所」を変える時期です。
スマホやAIを使うと、何が変わるのか？
記録する場所が、パソコンの前から棚の前に移ります。
スマホの1画面で「品目・区分・数量」だけを送って、送ったものがスプレッドシートの履歴に1行ずつ積み上がる形です。上で作った表の形は、そのまま使えます。
先日、これと同じ形で売上の入力の仕組みを作って納めました。在庫ではなく売上ですが、分かったことは同じです。入力の項目を削るほど、記録が続きます。「あれば便利」で足した1項目が、その仕組みを止めます。
入庫のときに届く納品書なら、撮った写真から品目と数量を読み取らせることもできます。ただし読み取りは100%にはなりません。違っていたときに、その場で直せる形にしておくことが前提です。読み取りの限界は手書き伝票をAIで読み取るに書きました。
自作をやめたほうがいいのは、どんな場合か？
4つあります。
- 社内に、表の面倒を見られる人がいない … 作った人がいなくなると、式が壊れても誰も直せません
- ロット番号・使用期限・シリアル番号まで追う必要がある … エクセルでは記録が重くなりすぎます
- 会計や受発注と、在庫を必ず連動させたい … 既製品のほうが確実です
- 今すぐ必要で、試す時間がない … 既製品のほうが早いです
⚠ 1つ目がいちばん多いです。「作れるかどうか」ではなく「続けられるかどうか」で決めてください。
⚠ 既製の在庫管理システムが不要という話ではありません。まずエクセルで回してみると、自社に何の機能が要るのかが分かった状態で選べます。集計の自動化の順番はExcelでやっていた集計を自動化する、最初の一歩にまとめています。
今日やること
紙に3行、書いてください。
① まず数えるのは、どの品目か（10個まで）：__________
② 棚から出すとき、誰がその場で記録するか：__________
③ 数が合わなかったとき、誰が表を直すか：__________この3行が埋まったら、表はもう半分できています。関数はあとから入れられます。
逆に、②が埋まらないなら、まだ作らないほうがいいです。作っても、また合わなくなります。
「うちは数が合わないのが当たり前になっている」と思われたかもしれません。それは、あなたの会社の人がいいかげんだからではありません。記録のしかたが決まっていなかっただけです。決めれば、合うようになります。応援しています！
よくある質問
在庫管理システムはエクセルで自作できますか？
できます。品目がそれほど多くなく、記録する人が限られている会社なら、エクセルで十分に回ります。ワークシートは1,048,576行まで使えるので、行数が先に足りなくなることはまずありません。つまずくのは道具ではなく記録のしかたです。何を数えるか、いつ誰が記録するか、合わなかったとき誰が直すか。この3つを決めてから表を作ると、帳簿と棚の数が合う状態を保ちやすくなります。
エクセルの在庫表は、どう作ればいいですか？
在庫数のセルを毎回書き換える表にしないことです。入庫と出庫を1件ずつ、日付・品目・入出庫の別・数量で下に積んでいく履歴のシートを作り、今の在庫はSUMIFS関数で入庫の合計から出庫の合計を引いて出します。書き換える表だと、数が合わなくなったときに原因を追えません。履歴を積む表なら、いつどの記録で差が出たかをさかのぼれます。棚卸で出た差も、調整の行として積みます。
エクセルの在庫管理は、どこで限界が来ますか？
多いのは2つです。1つ目は、同時に記録する人が増えたとき。エクセルで複数人が同時に編集するには、Microsoft 365のサブスクリプションと、OneDriveやSharePoint Onlineへの保存が条件になります。2つ目は、棚の前でパソコンを開けないとき。記録があとでまとめてになり、数が合わなくなります。この2つが見えてきたら、スマホから記録できる形か、既製の在庫管理システムを検討する時期です。
自作をやめて既製品にしたほうがいいのは、どんな場合ですか？
社内に表の面倒を見られる人がいない場合、ロット番号や使用期限、シリアル番号まで追う必要がある場合、会計や受発注と在庫を必ず連動させたい場合、今すぐ必要で試す時間がない場合です。とくに面倒を見る人がいないケースは多く、作った人が異動や退職をすると、式が壊れても誰も直せなくなります。作れるかどうかではなく、続けられるかどうかで決めてください。
小さな会社の売上アップ・利益改善のご相談を承っています。
お問い合せはこちら