ワザ箱発注点の計算を自動化する方法|エクセル関数の手順と表計算の限界
エクセルで発注点の計算を自動化するなら、1行1品目の表を作り、発注点の列に =B2*C2+D2(平均使用量×リードタイム+安全在庫)、判定の列に =IF(E2<=F2,"要発注","OK")(E列=現在庫、F列=発注点)の2つの数式を入れるのが最短です。これだけで「いま頼むべき品目」が自動で出ます。数式を組んだり保守したりしたくない場合は、数字を入力するだけで発注点と推奨発注量が出る無料ツールでも代用できます。
数式を組まずに発注点を出したい方へ。1日平均使用量・リードタイム・安全在庫・現在庫を入力すると、発注点と推奨発注量を自動計算し、要発注の品目を抽出します(無料・登録不要・データは端末内のみ)。
発注点・安全在庫計算ツールを開く →発注点(はっちゅうてん)の考え方が分かっても、品目ごとに電卓を叩いて現在庫と見比べるのは骨が折れます。「この商品はもう頼むべきか」を毎回手計算していると、確認漏れで欠品したり、逆に頼みすぎたりが起きがちです。そこで多くの現場がまず手を伸ばすのがエクセル(表計算ソフト)での自動化です。式を一度組んでおけば、数字を入れ替えるだけで発注点も要否も自動で出せます。
この記事では、エクセルで発注点の計算を自動化する具体的な組み方を、実際に使える関数例とともに順を追って紹介します。そのうえで、品目が増えたときに表計算そのものがぶつかる限界と、その先の選択肢まで整理します。発注点という言葉の意味・決め方の手順・定量発注と定期発注の違いは「発注点とは?どう決める?」で扱っているので、ここでは「自動化」に絞って進めます。
自動化の前にそろえる3つの数字
自動化の土台になる式はシンプルです。
発注点 = 1日あたりの平均使用量 × リードタイム + 安全在庫
「平均使用量 × リードタイム」で入荷までに使う見込み量を出し、そこに需要のブレを吸収する安全在庫を足します。在庫がこの発注点まで減ったら発注する、という運用です。式そのものは掛け算と足し算だけなので、自動化がうまくいくかどうかは「式を組めるか」より右辺に入れる3つの数字で決まります。数字の決め方そのものは「発注点とは?どう決める?」と「安全在庫はどれくらい持つべき?」にまとめているので、ここでは表計算に載せるときにつまずきやすい点だけを挙げておきます。
- 平均使用量とリードタイムは単位をそろえる — 平均は実績を対象期間の日数で割って出しますが、このとき割る日数の数え方とリードタイムの数え方をそろえる必要があります。リードタイムを暦日で数えるなら平均も暦日で、営業日で数えるなら平均も営業日で割ります。片方だけ営業日にすると、同じ実績から出した発注点が実態からずれます。
- リードタイムに「発注できるまでの待ち時間」を含める — 仕入先の締めが週1回なら、在庫が発注点を割ったと気づいてから実際に発注できるまで数日待つことがあります。この待ち時間を入れずに短く見積もると、計算上は足りていても現実には欠品します。仕入先ごと・品目ごとに、発注日と入荷日の記録から拾い直すのが確実です。
- 季節で動く品目は平均を分けて持つ — 使用量が繁忙期と閑散期で大きく変わる品目は、通年の平均1本で持つと両方の時期に外れます。期間ごとに平均を分けて持ち、切り替え時期に入れ替えるほうが精度が上がります。
たとえば割り箸を1日平均120膳使い、リードタイムが4日、安全在庫を200膳と決めたなら、発注点は 120 × 4 + 200 = 680膳です。ここで仕入先を締めが週1回の先に替えて実質リードタイムが7日になると、発注点は1,040膳に上がります。数字が変われば発注点も変わる——これが、発注点を電卓ではなく数式で持っておきたい理由です。あとは、①1行1品目の表に数字を集める ②発注点を数式で自動計算する ③要否の判定と発注量まで自動化する、の3段階を表計算に載せていきます。
エクセルで発注点を組む(表レイアウトと式)
まずは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を足せば、要発注の強調や抽出まで自動化できます。
一方で、元データの更新は手作業として残り、品目が増えるほど範囲の広げ忘れや属人化・互換性の問題が重くなります。数式の保守が新しい手間になってきたら、入力だけで同じ計算をこなせる発注点・安全在庫計算ツールのような専用ツールに任せるのが素直な選択です。自動化のゴールは「凝った式を組むこと」ではなく、「発注のタイミングを見逃さないこと」だと考えると、道具の切り替えどきも判断しやすくなります。