在庫の表をExcelで作ると、たいてい「商品ごとに列を作って、日付ごとに行を足していく」形になり、商品が増えるたびに列を挿入し、月が変わるとシートをコピーすることになります。このファイルは逆で、商品の一覧は「商品マスタ」に1行ずつ、動きは「入出庫」に1行ずつ入れるだけです。現在庫と在庫金額はマスタの右側に自動で出て、発注点を下回った商品は行が赤くなり、「発注リスト」に推奨発注数つきで並びます。
文房具10品目と10月の入出庫25件をサンプルとして入れてあります。開いてすぐ、どう動くかが分かるはずです。消して使ってください。
商品マスタに入れるのは7項目
商品コード、商品名、単位、期首在庫、発注点、発注ロット、仕入単価。黄色の列だけが入力欄で、右側の入庫計・出庫計・調整計・現在庫・状態・推奨発注数・在庫金額は式です。
期首在庫は「この表を使い始めた時点で棚にある数」です。以降は入出庫で動くので、最初に一度だけ数えます。発注点は「発注してから届くまでの間に使う量に、余裕を足したもの」。週に1回まとめて発注していて1日5個使うなら、35個に少し足した数になります。発注ロットは「1回に発注する単位」で、箱売りなら箱の入り数、1個から買えるなら1です。
仕入単価は在庫金額の計算にしか使わないので、空欄でも動きます。
入出庫は上から順に1行ずつ
日付、商品コード、区分、数量、備考。商品コードはセルを選ぶとマスタにあるコードのリストが出て、隣に商品名が自動で入ります。マスタにないコードを打つと「コード未登録」と出て行が黄色になるので、打ち間違いに気づけます。
区分は3つです。入庫は仕入れや納品、出庫は使った・売った、調整は棚卸で実数に合わせるときに差の数を入れます。棚卸で2個足りなければ「調整」で「-2」。サンプルの10月15日のボールペンがこれです。
日付順に並んでいなくても集計は合います。あとから気づいた出庫を下に足しても構いません。
発注リストは印刷してそのまま使える
現在庫が発注点以下になった商品を、マスタの並び順で自動で並べたシートです。商品コード、商品名、現在庫、発注点、発注ロット、推奨発注数、仕入単価、概算金額が出て、下に合計があります。入力するところはありません。
推奨発注数は「発注点の2倍まで戻す量を、発注ロット単位に切り上げたもの」にしています。発注点10でロット10の商品が8個まで減っていれば、20個まで戻すのに12個、ロット単位に切り上げて20個。発注点を「1回の発注で持ちたい量の半分」と考えれば、この計算で概ね過不足なく回ります。合わなければ数を書き換えて発注すればよく、この列はあくまで目安です。
FILTER関数を使わなかった理由
要発注の商品だけを別シートに並べるなら、Microsoft 365のFILTER関数が一番簡単です。ただ、店や事務所のPCにはExcel 2016や2019がまだ多く、FILTERは動きません。このファイルでは、商品マスタの右端に「発注No」という列を置いて、要発注の行に上から1、2、3と番号を振り、発注リストの側はその番号をINDEXとMATCHで引いています。地味な作りですが、配列の式を使わないのでExcel 2010以降でもGoogleスプレッドシートでも同じ結果になります。
正直に書くと、最初はAGGREGATE関数で行番号を配列から拾う式にしていました。手で打てば動くのに、ファイルとして保存したものを開くと空欄になる現象に当たり、原因を追うより番号を振る方式に変えた方が早いと判断しました。
現在庫の集計はSUMIFSで、入出庫シートの商品コードと区分が一致する数量を足しています。入出庫は1000行、マスタは200行まで式を入れてあります。足りなければ行をコピーして増やし、マスタの式の中にある「$B$2:$B$1001」のような範囲を広げてください。
やっていないこと
賞味期限やロット番号の管理、複数の倉庫や店舗をまたいだ在庫、バーコードリーダーでの入力には対応していません。バーコードは、リーダーが読んだコードを商品コードの列にそのまま打ち込む運用なら、このままでも使えます。月ごとの入出庫の集計表も入れていません。入出庫シートにオートフィルターを付けてあるので、月で絞って合計を見る使い方を想定しています。
動作を確かめた範囲
Microsoft 365のExcel（バージョン16）で開き直して再計算し、サンプル10品目の現在庫が手計算と一致すること、要発注が6品目になること、発注リストの並びと概算金額の合計20,800円、在庫金額の合計26,315円が合うこと、未登録のコードを入れたときに「コード未登録」と出ること、エラー値のセルがないことを確認しました。
GoogleスプレッドシートとLibreOfficeでは確認していません。使っている関数はSUMIFS、VLOOKUP、COUNTIF、INDEX、MATCHだけなので、どちらでも動く見込みです。
シフト表と工程表のテンプレートはこちらの一覧にあります。
