ABC分析で仕入先を絞る手順 — Claude for Excelで支出をパレート集計

仕入先が数十社を超えると、全社と同じ熱量で価格交渉をする余裕はなくなります。年間の支出を大きい順に並べ、累積の構成比で区切るABC分析（パレート分析）にすると、交渉に工数を割く先が数字で絞れます。Claude for Excelなら、購買明細の集計、並べ替え、累積比の数式づくりまでをブックの中で進められます。

一方で、結果を左右するのは集計の前段です。「株式会社A商事」と「(株)A商事」が別の仕入先として数えられると、ランクそのものがずれます。この記事では、表記ゆれの名寄せから検算までを、Claudeに任せる部分と人が決める部分に分けて手順にしました。使う数字は説明用に作った架空の購買データです。

ABC分析で決まるのは「どこから交渉するか」の順番

ABC分析は、仕入先を年間支出の大きい順に並べ、支出の累積構成比が低いうちに入る先をA、中ほどをB、残りをCとする整理です。多くの場合、少数の仕入先に支出が偏るため、Aに入る先を押さえるだけで交渉の効果が大きくなります。

次の表は、架空の10社の年間支出を並べた結果です。合計は5,550万円です。

上位3社で支出の7割を占めています。ここまでが交渉のテーブルに乗せる先で、残りの7社は取引条件の標準化や取引先の整理を考える対象です。

表の3位の行に注目してください。C化学は累積が70.6%で、70%の線をまたぎます。またぐ行をどちらの区分に入れるかは、数式の書き方で決まります。この記事では「その行を足す前の累積が閾値未満ならその区分」とし、またぐ行を上位の区分に含める置き方にしています。

手順1: 購買明細を整え、仕入先の表記ゆれを先に潰す

材料は、1行1取引の購買明細です。列は最低でも日付、仕入先、品目、金額（税抜か税込のどちらかに統一）があれば足ります。会計システムや購買システムから出力したCSVをExcelに開いておきます。ファイルの取り込み方はClaude Excel読み込みと作成にまとまっています。

最初にやるのは集計ではなく、仕入先名の名寄せです。次のような揺れが、同じ会社を別の行に割ります。

• 株式会社の位置と表記: 「株式会社A商事」「(株)A商事」「A商事(株)」
• 全角と半角: 「A商事」と「Ａ商事」
• 前後や途中の空白、拠点名の付記: 「A商事 大阪支店」
• 屋号の略称や旧社名

Claudeには、まず明細の仕入先名を一意の一覧にして、件数と金額を添えるよう頼みます。

「購買明細」シートの仕入先列から、重複を除いた一覧を新しいシート「名寄せ」に作ってください。
列は「明細での表記」「件数」「金額合計」「正規名（空欄）」にしてください。
そのうえで、同じ会社の表記ゆれとみられる組を、理由つきで別の列に挙げてください。
正規名の列にはまだ何も入れないでください。

正規名を空欄で止めるのが要点です。Claudeが挙げた候補には、同一会社でないものが混じります。「A商事」と「A商事サービス」のように名前が近い別法人は、人が取引の実態で見分けます。候補を見て、正規名の列を人が確定させます。

確定したら、明細の側に正規名の列を足します。

購買明細のE列に「正規仕入先」を追加してください。
名寄せシートの「明細での表記」で引き当て、見つからない行は「未登録」と表示してください。

引き当ての数式は、たとえば次の形になります（例です）。

=XLOOKUP(B2, 名寄せ!A:A, 名寄せ!D:D, "未登録")

これで、E列に「未登録」が1件も残っていないことが、次の集計に進む条件になります。

手順2: 正規名で年間支出を集計して並べる

新しいシート「ABC」に、正規名の一意の一覧を作り、B列に年間支出を出します。集計は値の貼り付けではなく、SUMIFSの数式で持たせるよう頼むと、明細を直したときに追従します。

シート「ABC」のA列に正規仕入先の一覧、B列に年間支出を作ってください。
B列は購買明細の金額を正規仕入先で合計するSUMIFSで、値の貼り付けにはしないでください。
作ったあと、B列の降順に並べ替えてください。

並べ替えはClaude for Excelが直接行える操作の1つです。ソート、フィルター、ピボットテーブルの編集、条件付き書式、データ検証のドロップダウンは、そのまま頼めます。

並べ替えは、次の手順の累積列を入れる前に済ませておきます。累積の数式は上の行から足し上げる作りなので、並びを変えたあとに数式を足すほうが安全です。

手順3: 累積構成比でA・B・Cを付ける

並べ替えたあとの表の右に、構成比、累積構成比、区分の3列を足します。閾値は数式に直接書かず、別のセルに置いておくと、あとから動かせます。

ABCシートに次の列を数式で追加してください。
C列: 構成比（B列 ÷ B列の合計）
D列: 累積構成比（先頭行から当該行までの合計 ÷ 全体の合計）
E列: 区分。「その行を足す前の累積構成比」がG2未満ならA、G3未満ならB、それ以外はC
G2に0.7、G3に0.9を入力してください。

仕上がる数式は、行2を例にすると次のようになります（例です）。

C2: =B2/SUM($B$2:$B$11)
D2: =SUM($B$2:B2)/SUM($B$2:$B$11)
E2: =IF(D2-C2<$G$2,"A",IF(D2-C2<$G$3,"B","C"))

D2-C2 が「その行を足す前の累積構成比」にあたります。この置き方なら、閾値をまたぐ行は上位の区分に入ります。冒頭の表で、70.6%に達するC化学がAに入るのはこのためです。

反対に、「その行を足した後の累積が閾値以下ならその区分」と書くと、またぐ行は下の区分に落ちます。同じデータでもAの社数が変わるので、どちらで置くかをClaudeへの指示に書き込んでおくのが確実です。

手順4: 品目で見直して、集約の候補を絞る

ABC分析で分かるのは仕入先の大きさまでです。集約交渉の対象は、同じ品目を複数の仕入先から買っているところに現れます。仕入先と品目を交差させたピボットテーブルで確認します。

購買明細から、行を品目、列を正規仕入先、値を金額合計にしたピボットテーブルを新しいシートに作ってください。
さらに右端に「取引先数」の列を足し、金額が入っている仕入先の数を数えてください。
取引先数が2以上の品目を、金額の大きい順に並べてください。

たとえば、梱包テープをD包装から120万円、G工具から35万円、I清掃から12万円で買っているなら、取引先数は3です。数量をまとめて1社に寄せる交渉の材料になります。

ここで区分の見方が分かれます。

• A区分の仕入先: 金額が大きいぶん、単価や支払条件の交渉余地が数字に直結します。同じ品目を他社からも買っているなら、その比較が交渉の根拠になります
• B区分の仕入先: Aとの取引に寄せられる品目がないかを、品目表で確かめます
• C区分の仕入先: 1社あたりの金額は小さく、数が多いのが普通です。取引先を絞るか、発注の窓口を一本化する検討の対象です

これは整理の考え方であり、実際に1社へ寄せられるかは、品質・納期・調達の継続性の条件で変わります。それらの評価は、サプライヤー評価の月次スコアカードのように別の物差しで持っておくと、支出の大きさだけで判断せずに済みます。

検算: 集計の誤りは3つの合計で見つかる

ABCの区分は、元の合計がずれていても何事もなかったように表になります。集計後に、次の3点を数式で確かめます。

1. 明細の金額合計と、ABCシートの年間支出の合計が一致しているか
2. 明細の正規仕入先に「未登録」が残っていないか
3. 構成比の合計が100%になり、累積構成比の最終行が100%になっているか

次の3つを確かめて、結果を各セルに数式で出してください。
・購買明細の金額合計 − ABCシートB列の合計（0になるはず）
・購買明細の正規仕入先列の「未登録」の件数（0になるはず）
・累積構成比の最終行（1になるはず）

1が0にならないとき、原因は大きく3つです。名寄せの表に載っていない仕入先が引き当てられていない、返品・値引きのマイナス行が一部の集計から漏れている、税込と税抜が混ざっている、のいずれかが多いパターンです。差額の出た行を絞り込む作業は、Claudeに頼めます。

税込と税抜が混ざる問題は、金額列の見出しが「請求額」と「税抜金額」のように複数あるときに起きます。集計に使う列を最初に1つへ決め、その列名を指示文に書いておきます。

月次の予実管理など、同じ「数式で組み、人が検算する」型の別の作業は、月次予実対比レポートの差異分析にもあります。支払先ごとの年間合計を条件に当てる流れは、支払調書の対象判定と同じ骨組みです。

毎回の指示は、設定の「Instructions」に持たせる

ABC分析は年に数回、同じ手順で回す作業です。毎回プロンプトに書くより、アドインのサイドバーにあるSettingsのInstructionsに置いたほうが続きます。Instructionsには、書式の決まり（千区切りなど）や、通貨・ロケールの好み、繰り返し使う文脈を書けます。

たとえば次のような内容です。

集計は値の貼り付けではなく数式で作る。
金額は万円単位で表示し、元データは円単位のまま変えない。
集計後は、明細の合計との差額を確認する式を最下部に置く。

Instructionsの設定は、Excelだけに効きます。PowerPointやWordで設定したものとは別です。

任せる範囲と、使う前の確認事項

Claude for Excelは、Pro・Max・Team・Enterpriseのプランで使えます。動作するのは、Excel on the web、Microsoft 365のWindows版（ビルド16.0.13127.20296以降）、Mac版（バージョン16.46以降）です。Excel 2016・2019の買い切り版、iPad、Androidでは動きません。

今回の作業に関わる制限は次のとおりです。

• マクロとVBAの操作、データテーブルはサポート外。集計を毎月マクロで回している職場は、その部分をClaude経由では置き換えられません
• 公式の記載では、最終的な取引先向け成果物を人の確認なしで出すこと、監査に直結する計算を検証なしで使うことは推奨されていません。交渉資料に載せる数字は、検算を通した後のものにします
• 機密性や規制の高いデータは、適切な管理がないまま扱うことが推奨されていません。仕入価格や取引条件を含む明細は、社内の持ち出しルールに照らして使います
• 既存のデータを上書きするときは、Claudeが事前に警告を出す

見落としやすいのは、外部から受け取ったファイルです。仕入先から届いた見積書や納品実績のExcelには、隠された指示が仕込まれていることがあり、データの抽出や破壊的な操作につなげられる場合があると説明されています。仕入先のファイルは、そのまま作業中のブックに貼り込まず、信頼できるコピーを作り、確認してから明細に取り込みます。危険な操作を提案された場合は確認を求められるので、外部ファイルを扱うときは内容を読んで承認します。

まとめ

ABC分析の精度は、集計の数式より先に、仕入先名の名寄せで決まります。候補はClaudeに挙げさせ、正規名の確定と、同一会社かどうかの判断は人が持ちます。累積構成比と区分は数式で組み、閾値を別セルに置けば、境界を動かして区分がどう変わるかをすぐ比べられます。

最後に、明細との合計差額、未登録の件数、累積の最終行の3つが揃ってから、A区分の仕入先と、複数社から買っている品目を交渉の候補として使います。