購買単価のばらつきを品目別に出す手順 — Claude for Excelで最安との差額を見る
同じ品目コードの購買単価が仕入先や月でどれだけ違うかを、最安単価・加重平均・最安との差額で出す手順。数式の組み方、数値例、最安を目標にできない場合の見方までを扱います。
同じ品目を複数の仕入先から買っていると、単価は思ったより揃っていません。品目コードごとに最安の単価で買っていたら、いくら安く済んだか。その差額を出せば、価格交渉や発注先の寄せ方を決める材料になります。この集計は、購買明細のブックを読んで数式つきの表を組めるClaude for Excelに向いています。
ただし、差額が大きい品目を見つけるところまでは数字で決まりますが、その差を埋められるかは数字からは分かりません。この記事では、品目別の最安単価と加重平均、最安との差額の出し方、仕入先間と月別の2方向の見方、最安を目標にしてはいけない場合の切り分けを順に書きます。数値は説明用に作った例です。
標準単価との差異(PPV)は購買価格差異をClaude for Excelで品目別に分解する手順で扱っています。ここで見るのは標準単価のない状態で、実績どうしの単価差です。
見る数字は4つだけ
同一品目の単価のばらつきは、次の4つで足ります。
品目ごとに出す指標
最安単価
最小
その期間に実際に払った最も低い単価
最高単価
最大
同じく最も高い単価
加重平均単価
金額÷数量
購買額の合計を数量の合計で割る
最安との差額
金額−最安×数量
最安で全量を買った場合との差
加重平均を使うのは、少量だけ安く買った取引で平均が引っ張られないようにするためです。単価を単純に平均すると、1個だけ買った高い試作品も、1万個買った量産品も同じ重みになります。
最安との差額は次の式です。
最安との差額
= 実際の購買金額 − 最安単価 × 購買数量の合計
= Σ(各取引の単価 − 最安単価) × その取引の数量この金額は「全量を最安の単価で買えていたら浮いた額」で、現実に削れる額の上限です。上限であって見込み額ではない点は、後の節で扱います。
購買明細の形をそろえる
集計に使う列は、購買日、品目コード、仕入先、数量、単価、金額の6つです。金額は =数量×単価 の数式にして、手入力を避けます。
単価のばらつきを出す前に、単位をそろえる必要があります。同じ品目コードで「1枚あたり」と「1箱(100枚)あたり」が混ざっていると、それだけで単価が100倍ずれます。税込と税抜、送料込みと別途、円と外貨も同じです。比べる単価の定義は1つに決めます。
ブックを開いたら、集計の前にClaudeへ次の確認を頼みます。
購買明細の品目コードごとに、単位・税区分・通貨の列に
異なる値が混ざっていないか調べ、混ざっている品目を一覧にしてください。
直さずに、行番号だけ示してください。Claudeの回答にはセル単位の出典が付き、クリックすると該当セルへ移動できます。混在の見落としを減らすには、数字の出どころを自分の目で追えることが役に立ちます。
品目別の集計表を数式で組む
集計表はA列が品目コード、以降の列を次のように置きます。
| 列 | 内容 | 数式の考え方 |
|---|---|---|
| B | 内容購買数量 | 数式の考え方明細の数量を品目コードで合計 |
| C | 内容購買金額 | 数式の考え方明細の金額を品目コードで合計 |
| D | 内容加重平均単価 | 数式の考え方C ÷ B |
| E | 内容最安単価 | 数式の考え方品目コードが一致する行の単価の最小 |
| F | 内容最高単価 | 数式の考え方同じく最大 |
| G | 内容最安基準の金額 | 数式の考え方B × E |
| H | 内容最安との差額 | 数式の考え方C − G |
| I | 内容差額率 | 数式の考え方H ÷ C |
| J | 内容最高と最安の開き | 数式の考え方F ÷ E − 1 |
数式にすると、2行目は例えば次のようになります。
B2: =SUMIFS(購買明細!$D:$D,購買明細!$B:$B,$A2)
C2: =SUMIFS(購買明細!$F:$F,購買明細!$B:$B,$A2)
D2: =IF(B2=0,"",C2/B2)
E2: =MINIFS(購買明細!$E:$E,購買明細!$B:$B,$A2)
F2: =MAXIFS(購買明細!$E:$E,購買明細!$B:$B,$A2)
G2: =B2*E2
H2: =C2-G2
I2: =IF(C2=0,"",H2/C2)
J2: =IF(E2=0,"",F2/E2-1)Claudeに頼むときは、列の定義を文章で渡します。
購買明細を品目コードで集計し、新しいシート「単価ばらつき」に
次の列を数式で作ってください。値の直書きはしないでください。
- 購買数量、購買金額、加重平均単価(金額÷数量)
- 最安単価、最高単価
- 最安との差額 = 購買金額 − 最安単価 × 購買数量
- 差額率 = 差額 ÷ 購買金額
差額の大きい順に並べてください。Claude for Excelは値を書き換えるときに数式の関係を保ちます。既存のデータを上書きしそうなときは、先に警告が出ます。並べ替えやピボットテーブルの編集、条件付き書式も、ブックの中で直接頼めば行われます。
数値例 — 差額は119,800円、7割超が1品目
製造業の部材4品目を想定した例です。数字は説明のために作ったもので、実在の会社のものではありません。
| 品目 | 取引 | 数量 | 金額 |
|---|---|---|---|
| P-01 SUS304板材 | 取引A社1,250円で600枚、B社1,382円で200枚×2、C社1,210円で200枚 | 数量1,200 | 金額1,544,800 |
| P-02ゴムパッキン | 取引A社40円で5,000個、B社46円で3,000個 | 数量8,000 | 金額338,000 |
| P-03梱包箱 | 取引A社95円で2,000個、B社96円で1,000個 | 数量3,000 | 金額286,000 |
| P-04制御基板 | 取引A社3,100円で40枚、B社3,180円で100枚 | 数量140 | 金額442,000 |
ここから集計表を作ると、次のようになります。
| 品目 | 最安 | 最高 | 加重平均 | 最安との差額 | 差額率 | 最高と最安の開き |
|---|---|---|---|---|---|---|
| P-01 | 最安1,210 | 最高1,382 | 加重平均1,287 | 最安との差額92,800 | 差額率6.0% | 最高と最安の開き+14.2% |
| P-02 | 最安40 | 最高46 | 加重平均42 | 最安との差額18,000 | 差額率5.3% | 最高と最安の開き+15.0% |
| P-03 | 最安95 | 最高96 | 加重平均95 | 最安との差額1,000 | 差額率0.3% | 最高と最安の開き+1.1% |
| P-04 | 最安3,100 | 最高3,180 | 加重平均3,157 | 最安との差額8,000 | 差額率1.8% | 最高と最安の開き+2.6% |
| 合計 | 最安 | 最高 | 加重平均 | 最安との差額119,800 | 差額率4.6% | 最高と最安の開き |
購買金額の合計は2,610,800円、最安基準の金額の合計は2,491,000円で、差が119,800円です。このうちP-01が92,800円、約77%を占めます。
見るべきは、開きの大きさと差額の大きさが一致しない点です。ゴムパッキンは最高と最安の開きが15.0%と最大ですが、差額は18,000円です。単価が小さく、買う数量の多くが最安のA社に寄っているためです。一方、制御基板は開きが2.6%でも、1枚あたりの単価が高いので差額が8,000円出ます。開きの率だけで品目を選ぶと、交渉の優先順位を誤ります。優先順位は差額の金額で決め、率は補助に使います。
落とし穴
開きの率だけで並べると、金額の小さい品目に時間を使う
最高と最安の開き(J列)は、単価が低く購買数量が少ない品目でも大きく出ます。並べ替えは差額(H列)で行い、率は「この品目は何%動いているか」を読む列として横に置きます。
仕入先間と月別、2つの方向で見る
最安との差額は、同じ数字を2つの方向から見られます。
差額の出どころを切り分ける
仕入先間のばらつき
同じ時期に、仕入先によって単価が違う場合です。最安の仕入先に寄せられるか、価格を合わせてもらえるかが論点になります。
期間間のばらつき
同じ仕入先でも、月によって単価が動く場合です。値上げの時期や、発注の時期による割高の有無が論点になります。
P-01の取引を日付順に並べると、2つの方向が混ざっていることが分かります。
| 購買月 | 仕入先 | 数量 | 単価 |
|---|---|---|---|
| 4月 | 仕入先A社 | 数量300 | 単価1,250 |
| 5月 | 仕入先C社 | 数量200 | 単価1,210 |
| 6月 | 仕入先A社 | 数量300 | 単価1,250 |
| 8月 | 仕入先B社 | 数量200 | 単価1,382 |
| 10月 | 仕入先B社 | 数量200 | 単価1,382 |
A社は4月も6月も1,250円で、期間では動いていません。差を作っているのは仕入先の違いです。C社の1,210円を基準にすれば、A社分は600枚×40円で24,000円、B社分は400枚×172円で68,800円、合わせて92,800円になります。B社の割高が全体の約74%を占めるので、B社への発注を見直す余地があります。ただし、なぜB社に発注したのかは、数字にはありません。
次のような依頼で、2つの方向を分けた表を作らせられます。
差額が大きい上位3品目について、購買明細を購買日順に並べ、
仕入先ごとの数量・単価・金額を表にしてください。
仕入先を固定したときの単価の動きと、
同じ月に複数の仕入先から買った場合の単価差を、別々に示してください。仕入先ごとの月別単価は、ピボットテーブルで行を購買月、列を仕入先、値を平均単価にすると並びます。ピボットテーブルの編集もClaudeに任せられます。
最安を目標にしてはいけない場合
最安との差額は削減余地の上限です。そのまま見込み額にすると、現場で使えない数字になります。最安の単価が実現できない理由は、おおむね次のいずれかです。
- ロット条件: 最安の単価が大口発注の価格で、いまの発注量では適用されない
- 納期・在庫: 最安の仕入先は納期が長く、欠品の防止に別の仕入先を使っている
- 品質・仕様: 同じ品目コードでも、仕入先によって仕様や検査の条件が違う
- 一回限りの取引: 最安が特価や在庫処分の1回だけで、続けて買える価格ではない
- 送料・諸掛り: 単価に含まれない運賃が、仕入先によって違う
落とし穴
最安単価が1回の少量取引だと、差額が過大になる
最安が数個の試作品や特価品の単価だと、全量にその単価を掛けた差額は実際に狙えない大きな数字になります。最安は、一定の数量以上の取引だけで取り直すと、実現可能な水準に近づきます。
最安を取り直すには、数量の下限を決めます。たとえば、数量が1回あたり50以上の取引だけから最安を出す、といった条件です。MINIFSは条件を追加できるので、集計表の最安単価の数式に「数量が下限以上」の条件を足せます。
E2: =MINIFS(購買明細!$E:$E,購買明細!$B:$B,$A2,購買明細!$D:$D,">="&$M$1)M1に数量の下限を入れておけば、下限を変えたときの差額の変化を、数式を書き換えずに見られます。
最安を目標にする代わりに、加重平均より安い取引だけを基準にする、直近3か月の最安を基準にする、といった置き方もあります。どれを採るかは社内の購買の考え方で決まります。決めた基準は、集計表の見出しに書いておきます。基準が違う表を並べると、翌月に読み違えるためです。
毎回の指示を設定に置く
差額の定義、単位の扱い、数量の下限は、依頼のたびに書くと抜けます。アドインのサイドバーの設定にあるInstructions欄に置くと、Excelでのすべての会話に適用されます。
金額は円単位、桁区切りで表示する。
最安との差額は「購買金額 − 最安単価 × 購買数量」で計算する。
最安単価は数量が下限以上の取引だけから出し、下限はM1セルを参照する。
値は直書きせず、数式で参照する。この設定はExcel専用で、PowerPointやWordの指示とは別に保たれます。
検算で見ておく3点
数式が組めていても、集計が合っているとは限りません。毎回、次の3点を確かめます。
- 購買金額の合計が、購買明細の金額の総合計と一致するか(上の例では2,610,800円)
- 最安との差額の合計が、購買金額の合計から最安基準の金額の合計を引いた値と一致するか(2,610,800 − 2,491,000 = 119,800)
- 最安単価と最高単価が、明細に実在する単価か(集計の条件を取り違えると、明細にない値が出る)
差が出たときは、品目コードの表記ゆれを疑います。「P-01」と「P01」が別の品目として数えられると、最安の相手が見つかりません。ゆれの洗い出しはClaudeに頼めますが、どちらに寄せるかは人が決めます。仕入先の名寄せと集約の考え方はABC分析で仕入先を絞る手順にまとめています。
数字の次に人がやること
単価差が大きい品目が見つかったら、その後の動きは数字の外にあります。
- 差額の大きい上位の品目について、最安の取引の条件(ロット・納期・仕様)を購買担当に聞く
- 条件が合うなら、割高な仕入先への価格交渉の材料にする。合わないなら、その理由を原因欄に残す
- 見直す仕入先の候補が複数あるときは、相見積もりの比較表で条件をそろえて比べる
- 値上げ通知が来ているときは、取引先の値上げ通知を契約・単価表と照合する手順で通知の根拠を確かめる
Claudeに任せてよいのは、集計、並べ替え、単価が動いた月や仕入先の抽出までです。値上げの理由や、最安の仕入先に切り替えられるかは、取引の経緯を知っている人にしか書けません。判断の欄は「要確認」と空欄にしておき、聞き取った内容を人が書き入れます。
使うときの制限
Claude for Excelは、最終的な客先向け成果物を人の確認なしで出すこと、検証なしで監査に使う計算を任せること、機微または規制対象のデータを適切な管理なしに扱うことには向かないとされています。購買単価は取引条件に当たるため、ブックを扱う前に社内の取り扱いルールを確かめます。入出力はバックエンドで受領または生成から30日以内に削除されます。
仕入先から届いた価格表や見積もりファイルは、そのまま開いて作業させません。外部から届いたファイルには、Claudeを操作しようとする隠れた指示が入っていることがあります。内容を自社のブックに値だけ転記してから使います。重要なデータの変更や削除をClaudeが提案したときは確認を求められるので、中身を読んでから承認します。編集を広く頼む前に、信頼できるコピーを作っておく運用も案内されています。
データテーブル(What-If分析のもの)と、マクロ・VBAの操作は使えません。対応するのはExcelのWeb版、Windows版(Microsoft 365契約でビルド16.0.13127.20296以降)、Mac版(バージョン16.46以降、ビルド21011600以降)で、Excel 2016・2019の永続ライセンスやボリュームライセンス版、iPad、Androidでは動きません。利用できるのはPro、Max、Team、Enterpriseの各プランです。
単価のばらつきは、毎月同じ表で追う
1回の分析で終わらせず、同じ表を毎月更新すると、ばらつきの推移が見えてきます。購買明細に月次の行を追記し、集計表の数式の範囲だけを広げれば足ります。差額の合計が前月より増えた場合は、増えた品目をClaudeに抜き出させます。
差額の合計は、仕入先の寄せ方がうまくいくと小さくなります。逆に、特定の品目で最安の取引が減ると、最安単価が上がって差額が見かけ上小さくなることもあります。差額が減ったときは、価格が揃ったのか、最安の基準が動いたのかを、最安単価の列で確かめます。