安全在庫と発注点をClaudeにExcelで計算させ検算する手順
過去12か月の出荷実績から、安全在庫と発注点を求めるExcelの式をClaude for Excelに組ませる手順です。サービスレベルの前提を先に決め、手計算で検算するところまで示します。
安全在庫と発注点は、過去12か月の出荷実績があれば、Excelの標準関数だけで計算できます。Claude for Excelには、その式の組み立てと、計算結果の見直しを任せられます。ただし「どの欠品確率まで許すか」というサービスレベルは人が決める前提で、Claudeに推測させると結果が静かにずれます。
この記事では、出荷明細から月次の需要を集計し、安全在庫と発注点を出し、手計算で検算するところまでを順に示します。
安全在庫と発注点は何で決まるか
発注点は、在庫がこの数量まで減ったら発注する、という基準値です。リードタイム(発注から入荷まで)の間に出ていく需要の見込みに、安全在庫を足して求めます。安全在庫は、需要のぶれで欠品しないための上乗せ分です。
教科書的な式は次の2本です。
安全在庫 = 安全係数z × 月次需要の標準偏差 × √(リードタイム月数)
発注点 = 月平均需要 × リードタイム月数 + 安全在庫安全係数zは、サービスレベル(補充期間中に欠品しない確率)から決まります。95%なら約1.645、99%なら約2.326です。
この式には前提があります。需要が正規分布で近似できること、リードタイムが固定とみなせること、需要とリードタイムが独立なこと。この前提が崩れる品目では、式の結果をそのまま使えません。後半の「使えない品目」で扱います。
Claude for Excelの役割は、式の下書きと見直しです。ブックの質問にはセル単位の根拠が付き、値を変えても数式の関係は保たれるので、パラメータを動かして発注点の変化を見る作業に向いています。一方、サービスレベルの値とリードタイムの実測は、ブックの外にある判断材料です。
前提 — 使える環境とブックの用意
Claude for Excelは、Pro・Max・Team・Enterpriseの各プランで使えます。動作するのは、Excel on the web、Microsoft 365版のExcel on Windows(ビルド16.0.13127.20296以降)、Excel on Mac(バージョン16.46以降、ビルド21011600以降)です。
Excel 2016・2019の永続ライセンス版、iPad、Androidでは動きません。これらの環境で在庫管理をしている職場は、ブックをweb版で開く方法を先に検討します。
ブックは次の3シート構成にします。
| シート | 中身 |
|---|---|
| 出荷明細 | 中身日付、品目コード、出荷数(過去12か月分) |
| 月次需要 | 中身品目ごとの月別出荷数、平均、標準偏差 |
| 設定 | 中身サービスレベル、安全係数z、品目別リードタイム |
最初に、元のブックを複製して作業用のコピーを作ります。公式の注意でも、広く編集させる前に信頼できるコピーで始めることが挙がっています。取引先から受け取ったファイルをそのまま開くと、隠れた指示文に操作を乗っ取られる危険があるため、出荷データは社内システムから出したものに限ります。
ステップ1: 月次の需要を集計する
出荷明細から品目別・月別の出荷数を作ります。Claudeへの依頼は、列の意味と期待する結果を具体的に書きます。
「出荷明細」シートのA列が出荷日、B列が品目コード、C列が出荷数です。
「月次需要」シートのA列に品目コード、1行目のC〜N列に月初日(12か月分)
があります。各品目の月別出荷数をSUMIFSで集計してください。
出荷実績が無い月は0にしてください。返ってくる式は、例えば次のような形になります(例示であり、Claudeの出力そのものではありません)。
=SUMIFS(出荷明細!$C:$C, 出荷明細!$B:$B, $A2,
出荷明細!$A:$A, ">="&C$1,
出荷明細!$A:$A, "<"&EDATE(C$1,1))見る点は2つあります。月の境界が「月初以上、翌月初未満」になっているか。日付が文字列で入っていて、条件に一致しない行がないか。後者は、出荷明細の件数合計と、月次需要の総合計を比べると分かります。
=SUM(出荷明細!C:C) - SUM(月次需要!C2:N200)差が0でなければ、集計から漏れた行があります。関数の書き方そのものはClaude Excel関数の書き方に整理があります。
ステップ2: 平均・標準偏差から安全在庫と発注点を出す
月次需要が揃ったら、品目ごとに平均と標準偏差を求め、設定シートの値と組み合わせます。
月平均 =AVERAGE(C2:N2)
標準偏差 =STDEV.S(C2:N2)
安全係数z =NORM.S.INV(設定!$B$1)
安全在庫 =ROUNDUP(設定!$B$2*P2*SQRT(R2/30),0)
発注点 =ROUNDUP(O2*R2/30 + 設定!$B$2*P2*SQRT(R2/30),0)O列が月平均、P列が標準偏差、R列がリードタイム日数、設定のB1がサービスレベル、B2が安全係数zです。リードタイムを30で割って月数に直しています。
STDEV.Sは標本標準偏差で、数値以外のセルを無視します。12か月分の実績は標本として扱うので、母集団用のSTDEV.Pではなくこちらが自然です。
Claudeへは「この列構成で、安全在庫と発注点の式を入れてください。リードタイムは日数、需要は月次なので月数に換算してください」と頼みます。単位の換算は取り違えやすい箇所です。依頼文に、需要の単位とリードタイムの単位を両方書いておきます。
ステップ3: サービスレベルの前提を確認する
安全在庫の大きさを最も左右するのは、サービスレベルの選び方です。Claudeは、ブックにサービスレベルが書かれていなければ、一般的な値を仮置きすることがあります。仮置きされた値で作った表は、見た目が整っているぶん、そのまま通りやすいので注意します。
主なサービスレベルと安全係数の対応は次のとおりです。
| サービスレベル | 安全係数z | 月平均400・標準偏差60・リードタイム15日の安全在庫 |
|---|---|---|
| 90% | 安全係数z約1.282 | 月平均400・標準偏差60・リードタイム15日の安全在庫約54個 |
| 95% | 安全係数z約1.645 | 月平均400・標準偏差60・リードタイム15日の安全在庫約70個 |
| 99% | 安全係数z約2.326 | 月平均400・標準偏差60・リードタイム15日の安全在庫約99個 |
95%から99%に上げると、安全在庫は4割ほど増えます。在庫金額に直したときの差を、品目の単価と掛けて見ておくと、設定の根拠が説明しやすくなります。
品目をすべて同じ値にする必要はありません。欠品の損失が大きい品目や代替の効かない品目は高く、代替が利く低単価品は低くする運用もあります。その場合は、設定シートに品目別のサービスレベル列を作り、式の参照先を品目ごとに変えます。
依頼文の前提には、次のように書きます。
サービスレベルは設定!B1の値を使い、勝手に変えないでください。
B1が空欄のときは、式を入れずに私に確認してください。ステップ4: 手計算で検算する
出てきた数字は、必ず1品目を手で計算して照合します。Claudeの計算は、式として構造を見直せるぶん追いやすいのですが、重要な計算は別の方法で確かめるのが基本です。精度の考え方はClaude数式・計算の精度と検算が必要な理由で扱っています。
検算の例です。月平均400個、標準偏差60個、リードタイム15日(0.5か月)、サービスレベル95%の品目で考えます。
√0.5 = 0.7071
60 × 0.7071 = 42.43
安全在庫 = 1.645 × 42.43 = 69.79 → 70個
リードタイム中の需要 = 400 × 0.5 = 200個
発注点 = 200 + 69.79 = 269.79 → 270個シートの結果が270なら一致です。1品目が合えば式の構造は正しいと分かり、残りの品目は同じ式のコピーなので、参照のずれがないかだけを見ます。
ずれたときの調べ方は3つに絞れます。リードタイムの日数と月数を取り違えていないか。標準偏差が12か月でなく途中までの範囲を参照していないか。サービスレベルの値が0.95でなく95になっていないか。
さらに、次のように頼むと、Claudeが各列の参照を一つずつ説明します。
発注点の列(S列)の式について、各参照セルが何を指すかを一覧にして
ください。月数への換算が正しいかも確認してください。リードタイムがぶれる品目は式を足す
仕入先の入荷日数が毎回違う品目では、需要のばらつきだけで安全在庫を出すと不足しがちです。リードタイムの標準偏差も加えた式が使われます。
安全在庫 = z × √( LT月数 × 需要の標準偏差^2 + 月平均需要^2 × LT月数の標準偏差^2 )先ほどの品目に、リードタイムの標準偏差が5日(約0.167か月)という条件を足して計算します。
LT月数 × 需要の分散 = 0.5 × 60^2 = 1,800
月平均^2 × LT分散 = 400^2 × 0.167^2 = 160,000 × 0.0278 = 4,444
合計の平方根 = √6,244 = 79.0
安全在庫 = 1.645 × 79.0 = 130個固定リードタイムの70個から、130個へ増えます。入荷日数のぶれが、需要のぶれより大きく効いている例です。
この式を使うには、入荷実績(発注日と入荷日)の列がブックに要ります。Claudeへは「発注日と入荷日から実際のリードタイム日数を出し、品目別の平均と標準偏差を求めてください」と頼み、そのうえで上の式に差し替えます。入荷実績が数件しかない品目は標準偏差が不安定なので、仕入先ごとの傾向で代用するかどうかを人が決めます。
月次と週次、どちらの粒度で集計するか
リードタイムが数日から2週間程度の品目では、月次の標準偏差を月数換算するより、週次で集計したほうが実態に近づきます。12か月分は約52週のデータになり、標準偏差の推定も安定します。
週次で組むときは、式の月数を週数に置き換えます。リードタイムが10日なら、10÷7で約1.43週です。依頼文で「需要は週次、リードタイムは日数。週数への換算は7で割る」と単位を明示すれば、換算の取り違えを防げます。
月次と週次の両方で計算して結果が大きく違う品目は、需要が特定の週に偏っている可能性があります。その偏りの原因(販促、月末の駆け込み)は、出荷データだけでは分からないため、営業側への確認に回します。
式が使えない品目を見分ける
教科書式が成り立たない品目では、計算結果をそのまま発注に使えません。出荷実績を見て、次に当てはまるものは別扱いにします。
- 季節変動が大きい品目: 12か月の標準偏差が、需要の季節性まで含んでしまい、閑散期に過大、繁忙期に過少になります。月別の平均で補正するか、期間を区切ります。
- 欠品していた期間のある品目: 在庫がなくて出せなかった月の出荷数は、需要より小さくなります。実績をそのまま使うと需要を低く見積もります。
- 新製品や仕様変更直後の品目: 12か月に満たない実績で標準偏差を出すと、ばらつきが安定しません。
- 出荷が月に数回しかない品目: 0の月が多く、正規分布で近似しにくくなります。
- リードタイムが大きく変動する品目: 固定とみなす前提が崩れます。実測の入荷日数のばらつきを別に見ます。
Claudeに「実績から外れ値や欠品期間を探して」と頼むこともできますが、欠品の有無は出荷データだけでは分かりません。倉庫の在庫推移や欠品の記録と照らして、人が判断します。
使えない機能と、データの扱い
Claude for Excelは、データテーブルとマクロ・VBA操作に対応していません。感度分析をデータテーブルで組んでいる職場では、その部分は手作業のままになります。VBAはClaude CodeでExcel VBAマクロを書く方法の流れで別に扱います。
公式の制限事項には、監査が重要な計算を検証なしで使うことや、機密性・規制対象の高いデータを統制なしで扱うことが、推奨されない用途として挙がっています。発注点の計算は社内の運用値ですが、単価や取引先が含まれるブックは、社内のデータ取り扱いルールと照合してから使います。
会話履歴はブラウザ内に保存され、Anthropicのサーバーには保存されません。入出力はバックエンドで受領または生成から30日以内に削除されます。
毎回同じ条件で頼むためのInstructions
アドインのSettingsにあるInstructionsに、数値の書式や職場の前提を書いておくと、すべての会話に反映されます。この設定はExcelだけに効き、PowerPointやWordとは別です。
- 在庫の数量は整数で、切り上げ(ROUNDUP)で表示する
- 需要は月次、リードタイムは日数で持ち、式の中で月数に換算する
- サービスレベルは設定シートのB1を参照し、値を勝手に変更しない
- 式を追加したら、参照しているセルの意味を一覧で示す在庫の棚卸差異を分析する手順は実地棚卸と帳簿在庫の棚卸差異をClaudeで分析する手順にあります。発注点を決めたあとの帳簿と実物の突合は、そちらの手順が受けます。
まとめ
安全在庫と発注点の計算は、式そのものよりも前提の置き方で結果が変わります。サービスレベルとリードタイムを人が決めてブックに書き、Claudeにはその値を参照する式を組ませる分担にすると、仮置きの値が紛れ込みません。1品目の手計算が一致するか、これが運用に出す前の最低限の関門です。