ワザ箱発注点の計算を自動化するには|エクセル関数の手順と手計算の限界
発注点(はっちゅうてん)の考え方が分かっても、品目ごとに電卓を叩いて現在庫と見比べるのは骨が折れます。「この商品はもう頼むべきか」を毎回手計算していると、確認漏れで欠品したり、逆に頼みすぎたりが起きがちです。そこで多くの現場がまず手を伸ばすのがエクセル(表計算ソフト)での自動化です。式を一度組んでおけば、数字を入れ替えるだけで発注点も要否も自動で出せます。
この記事では、エクセルで発注点の計算を自動化する具体的な組み方を、実際に使える関数例とともに順を追って紹介します。そのうえで、品目が増えたときに表計算そのものがぶつかる限界と、その先の選択肢まで整理します。発注点の定義そのものは「発注点とは?どう決める?」で扱っているので、ここでは「自動化」に絞って進めます。
発注点の式をおさらい
自動化の土台になる式はシンプルです。
発注点 = 1日あたりの平均使用量 × リードタイム + 安全在庫
「平均使用量 × リードタイム」で入荷までに使う見込み量を出し、そこに需要のブレを吸収する安全在庫を足します。在庫がこの発注点まで減ったら発注する、という運用です。安全在庫の決め方は「安全在庫はどれくらい持つべき?」で詳しく扱っています。エクセルでは、この式をそのままセルの数式に落とし込みます。
エクセルで発注点を組む(表レイアウトと式)
まずは1行1品目の表を作ります。ここでは次のように列を割り当て、データは2行目から入れるものとします。
| セル | 列の内容 | 入力する値 / 数式 |
|---|---|---|
| A2 | 品名 | 入力(例: 割り箸) |
| B2 | 1日平均使用量 | 入力(または実績から算出。後述) |
| C2 | リードタイム(日) | 入力(発注から入荷までの日数) |
| D2 | 安全在庫 | 入力(数量) |
| E2 | 現在庫 | 入力(いまの在庫数) |
| F2 | 発注点 | =B2*C2+D2 |
F2に =B2*C2+D2 と入れれば、発注点が自動で出ます。この数式を下の行へコピーすれば、品目が何行あっても同じ計算が効きます。
1日平均使用量(B列)も自動化できます。たとえば直近30日の日次使用量を別の範囲(例: P2:P31)に記録しているなら、=AVERAGE(P2:P31) で平均が出ます。期間合計しか手元にないなら =合計セル/日数 でも構いません。また安全在庫を「◯日分」で持ちたい場合は、安全在庫日数をJ2に入れて =B2*J2 と組めば、平均使用量に連動して安全在庫量が決まります。
「要発注」を自動で判定・抽出する
発注点が出たら、次は「現在庫がそれを下回ったか」の判定です。G列に判定を置きましょう。
- 要否の判定:G2に
=IF(E2<=F2,"要発注","OK")と入れると、現在庫(E2)が発注点(F2)以下の品目に「要発注」と表示されます。 - 要発注の件数:別のセルに
=COUNTIF(G2:G100,"要発注")で、いま何品目が発注待ちかを数えられます。 - 行を色で強調:条件付き書式で、ルールに数式
=$G2="要発注"を指定し、塗りつぶしを赤系にすると、要発注の行がひと目で分かります(列を固定する$G2の書き方がポイントです)。
要発注の品目だけを別表に抜き出したいときは、新しいエクセル(Microsoft 365 / 2021以降)なら =FILTER(A2:A100,G2:G100="要発注") で品名の一覧が自動抽出できます。複数列をまとめて出すなら =FILTER(A2:F100,G2:G100="要発注") のように抽出する範囲を広げます(条件はG列のまま)。FILTERが使えない古いバージョンでは、オートフィルターで「要発注」だけを表示する方法で代替できます。
推奨発注量まで自動化する
「頼むべき」と分かったら、次は「いくつ頼むか」です。補充の目標を「◯日分まで戻す」と決めると、必要量は次の考え方で出せます。
推奨発注量 = 補充目標日数 × 1日平均使用量 + 安全在庫 − 現在庫
補充目標日数を設定セル(例: L1)に置き、発注単位(まとめ買いの倍数)をH2に入れるとします。すると推奨発注量は =CEILING(MAX(0,$L$1*B2+D2-E2),H2) と組めます。MAX(0,…) で「足りている品目はマイナスにせず0」に丸め、CEILING(…,H2) で発注単位の倍数に切り上げます。たとえば必要量が130・発注単位が50なら150になります。
ひとつ注意点があります。切り上げの単位(H2)は0や空のセルにしないでください。単位が0・空のままだとCEILINGはうまく計算できず、エクセルのバージョンによって #DIV/0! などのエラーになったり、本当は要発注なのに0が返って発注量ゼロに見えたりします(どちらも見落としのもとです)。発注単位を入れない品目が混ざるなら、=IF(H2>0,CEILING(MAX(0,$L$1*B2+D2-E2),H2),MAX(0,$L$1*B2+D2-E2)) のように、単位があるときだけ切り上げる形にしておくと安全です。ここまで組めば、数字を入れ替えるだけで「発注点・要否・発注量」が一気に出るシートになります。
エクセル自動化がぶつかる限界
ここまでで、少数の品目ならエクセルで十分に回せます。ただし、品目が増え、運用が続くほど、表計算そのものの限界が見えてきます。
- 元データの更新が手作業のまま残る
- 数式は自動でも、1日平均使用量やリードタイムといった入力値そのものは誰かが実績を見て更新しなければなりません。季節やトレンドで使用量が変わると発注点も古くなり、更新を怠ると自動化しているのに判定がずれます。
- 品目数が増えると破綻しやすい
- 数百品目になると、フィルターと目視での確認、発注書への転記が現実的でなくなります。行を追加したときに数式や条件付き書式・FILTERの範囲を広げ忘れると、一部の品目が判定から静かに漏れます。しかもエラーにならず「OK」と出てしまうので気づきにくいのが厄介です。
- 属人化と版管理のリスク
- 複雑な数式は組んだ本人しか直せなくなりがちで、壊れても
#REF!や誤った値がそのまま通ることがあります。複数人が同じブックを触ると、上書きや版の分裂も起きます。 - 環境による関数の互換性
- FILTERやCEILINGは環境によって使えなかったり、Googleスプレッドシートと挙動が違ったりします。現場のスマホで大きなブックを開きにくい、という運用面の壁もあります。
つまりエクセル自動化は「少数品目・少人数・自分で保守できる」うちは強力ですが、規模が大きくなるほど、式の保守そのものが新しい手間になっていきます。
発注点も要発注の抽出も、入力するだけ。エクセルの数式を組まずに自動化。無料・登録不要。
発注点・安全在庫計算ツールは、1日平均使用量・リードタイム・安全在庫・現在庫・発注単位を入力すると、発注点(平均使用×リードタイム+安全在庫)と推奨発注量を自動で計算します。現在庫が発注点を下回った品目を「要発注」として赤く抽出し、発注リストの印刷やCSV出力にも対応。安全在庫は日数指定・数量指定を切り替えられます。数式の保守も範囲の広げ忘れもなく、入力データはお使いの端末内だけで処理されます。
この記事で組んだ「発注点 → 要否判定 → 推奨発注量」という流れは、そのままツールの画面に対応します。既存のエクセル資産はCSVで往復できるので、これまで作ってきた表を捨てずに移行や併用ができます。まずは数品目で試して、手元のシートと結果を突き合わせてみてください。
まとめ
発注点の自動化は、エクセルで =B2*C2+D2 の発注点、=IF(E2<=F2,"要発注","OK") の判定、CEILING と MAX を使った推奨発注量、というシンプルな数式から始められます。条件付き書式やFILTERを足せば、要発注の強調や抽出まで自動化できます。
一方で、元データの更新は手作業として残り、品目が増えるほど範囲の広げ忘れや属人化・互換性の問題が重くなります。数式の保守が新しい手間になってきたら、入力だけで同じ計算をこなせる専用ツールに任せるのが素直な選択です。自動化のゴールは「凝った式を組むこと」ではなく、「発注のタイミングを見逃さないこと」だと考えると、道具の切り替えどきも判断しやすくなります。