年に一度、売上の合計を見て今年もなんとか回ったと息をつく。ところが顧客ごとに分けてみると、売上の半分近くが1社から来ていた。受託の仕事を続けていると、こうした偏りに後から気づくことがあります。

ABC分析とは、顧客を売上の大きい順に並べ、累積の構成比でA・B・Cの3つのグループに分ける分析方法です。結論を先に書きます。売上は請求1件を1行で記録し、QUERY 関数で顧客別に合計して売上順に並べ、累積構成比の70%と90%で区切ってください。この記事では、Googleスプレッドシートで作る2つのシートの設計と、そのままコピーできる数式を書きます。

この記事の要点

  • 「売上」シートに請求1件を1行で記録し、集計期間に入る行だけに印を付ける
  • 「ABC」シートで QUERY を使い、顧客別の売上と粗利を売上順に並べて、累積構成比からA・B・Cを判定する
  • 売上の順位だけで判断せず、粗利率と上位1社・上位3社の比率もあわせて見る
売上台帳からABC分析までの流れを示した図。売上シートに請求1件を1行で記録し、QUERYで顧客別に売上順で合計し、累積構成比を出して70%と90%でA・B・Cに分け、粗利率で見直してから上位1社と上位3社の比率で依存度を確かめる流れが確認できる

この記事の表と数式をそのまま入れたGoogleスプレッドシートを用意しました。顧客ABC分析テンプレートをコピーするを開くと「コピーを作成」の画面になるので、自分のドライブに保存してから使ってください。「使い方」「売上」「ABC」「顧客」の4シート構成で、QUERY による顧客別の集計、累積構成比からのランク判定、上位1社・3社の比率、Aランクの行と粗利率20%未満を色付けする書式まで設定済みです。

受託の顧客ABC分析で分かること

ABC分析をすると、売上を支えている顧客と、その顧客にどれだけ頼っているかが数字で見えます。受託は1件の金額が大きく、顧客の数が少ないため、1社の発注が止まったときの影響が大きいからです。

分かることは次の3つです。

  • どの顧客が売上の大半を占めているか(Aランクの顧客)
  • 上位1社・上位3社に売上がどれだけ偏っているか(依存度)
  • 売上は大きいのに粗利が残っていない顧客はどこか

3つ目は売上だけを並べても分かりません。この記事のシートでは、外注費を引いた粗利も同時に集計します。案件ごとの粗利の出し方は「エクセルで粗利管理表を作る」で詳しく書いています。

売上シートの6列の設計

1行が請求1件です。同じ顧客に毎月請求していれば、月ごとに1行ずつ増えます。シート名は「売上」にします(後の数式で参照します)。

列見出し入力例種類
A請求日2026/09/30手入力
B顧客名甲社プルダウン
C案件名甲社 コーポレートサイト手入力
D売上(税抜)800000手入力
E外注費(税抜)320000手入力(外注がなければ0)
F集計対象1数式

請求管理表をすでに使っているなら、その行をそのまま貼り付けて構いません。請求の記録の作り方は「請求管理のスプレッドシートテンプレート」にまとめています。

B列の顧客名は手で打たず、プルダウンで選びます。「データ」メニューの「データの入力規則」で条件を「プルダウン(範囲内)」にし、顧客の一覧を範囲に指定します(出典:Google ドキュメント エディタ ヘルプ「セル内にプルダウン リストを作成する」、2026年10月18日確認)。「甲社」と「甲社 」のように1文字でも違うと、別の顧客として集計されるためです。

E列の外注費は、外注がない案件でも0を入れます。空欄のままだと、後の QUERY で粗利が計算されない行が出ます。外注費の記録は「発注管理表をスプレッドシートで作る」の表から写すと漏れません。

集計期間を決める数式

F2 には次の数式を入れて、下までコピーします。

=IF(A2="",0,IF(AND(A2>=ABC!$B$1,A2<=ABC!$B$2),1,0))

請求日が、ABCシートで指定した開始日から終了日までに入っていれば1、それ以外は0になります。TRUE・FALSE ではなく1と0にしているのは、QUERY が列の型を判定するときに文字と真偽値が混ざるのを避けるためです。

ABCシートで顧客別に集計する数式

ABCシートは、売上シートから顧客別の合計を作り、売上の大きい順に並べるシートです。「ABC」というシートを作り、上の部分に集計期間を置きます。

  • A1:「開始日」、B1:2025/10/01
  • A2:「終了日」、B2:2026/09/30

期間は直近12か月にしておくと、季節による偏りが出にくくなります。

QUERYで顧客別の売上と粗利を並べる

A4 に次の数式を入れます。A4からC列に、見出しと顧客別の集計結果が並びます。

=QUERY(売上!A2:F,"select B, sum(D), sum(D)-sum(E) where F = 1 and B is not null group by B order by sum(D) desc label B '顧客名', sum(D) '売上', sum(D)-sum(E) '粗利'",0)

QUERY は、データの範囲に対して Google Visualization API のクエリ言語で集計や並べ替えを行う関数です。最後の0は、データに見出し行がないことを指定しています(出典:Google ドキュメント エディタ ヘルプ「QUERY 関数」、2026年10月18日確認)。

この数式の中では、次の4つを行っています。

  1. where で集計対象が1の行だけに絞る
  2. group by で顧客ごとに売上と外注費を合計する
  3. order by で売上の合計が大きい順に並べる
  4. label で見出しを「顧客名・売上・粗利」に付け替える

order by には、sum(D) のような集計の結果も指定できます(出典:Google for Developers「Query Language Reference」、2026年10月18日確認)。

累積構成比とランクの数式

D4からH4に「構成比・累積構成比・ランク・粗利率・粗利構成比」と見出しを入れます。5行目に次の数式を入れ、200行目あたりまでコピーします。

列見出し数式(5行目)表示形式
D構成比=IF(A5="","",B5/SUM($B$5:$B))パーセント
E累積構成比=IF(A5="","",SUM($B$5:B5)/SUM($B$5:$B))パーセント
Fランク=IF(A5="","",IFS(E5-D5<0.7,"A",E5-D5<0.9,"B",TRUE,"C"))そのまま
G粗利率=IF(A5="","",IFERROR(C5/B5,""))パーセント
H粗利構成比=IF(A5="","",C5/SUM($C$5:$C))パーセント

IFS は、複数の条件を順に調べて、最初に満たした条件の値を返す関数です(出典:Google ドキュメント エディタ ヘルプ「IFS」、2026年10月18日確認)。

ランクの判定では、累積構成比から自分の構成比を引いた値、つまりその顧客を足す前の累積を見ています。こうすると、1社で70%をまたぐ顧客もAに入ります。区切りの70%と90%は決まりではありません。顧客の数が少ないうちは、まずこの値で試してください。

依存度を1か所で見る

ABCシートの右上に、偏りを示す2つの数字を置きます。

  • D1:「上位1社の比率」、E1:=IFERROR(D5,"")
  • D2:「上位3社の比率」、E2:=IFERROR(E7,"")

E1とE2は、パーセントの表示形式にします。月に一度この2つを見るだけで、1社への偏りが強まっていないかを確かめられます。

計算例で確かめる

直近12か月の売上が1,000万円(税抜)、顧客が8社の例で確かめます。数字は説明のための例です。

顧客売上粗利構成比累積構成比ランク粗利率粗利構成比
甲社4,800,000円864,000円48.0%48.0%A18.0%27.1%
乙社2,400,000円1,080,000円24.0%72.0%A45.0%33.9%
丙社1,200,000円480,000円12.0%84.0%B40.0%15.1%
丁社600,000円300,000円6.0%90.0%B50.0%9.4%
戊社400,000円160,000円4.0%94.0%C40.0%5.0%
己社300,000円150,000円3.0%97.0%C50.0%4.7%
庚社200,000円100,000円2.0%99.0%C50.0%3.1%
辛社100,000円50,000円1.0%100.0%C50.0%1.6%

※記載金額は税抜、パーセントは小数第2位を四捨五入

この例では、次の3つが読み取れます。

  1. Aランクは甲社と乙社の2社で、売上の72%を占めている
  2. 上位1社の比率は48%、上位3社の比率は84%で、甲社への偏りが大きい
  3. 甲社は売上1位でも粗利率が18%しかなく、粗利の構成比では乙社が1位になる

数式を入れたら、この8行を売上シートに仮で入力し、同じ結果になるかを見てください。合わない場合は、請求日が文字として入っていないかを先に疑います。

ランク別に次の打ち手を決める

ABC分析は、顧客に優劣を付けるためではなく、時間の使い方を決めるために使います。ランクごとの見方は次のとおりです。

ランク見ること打ち手の例
A粗利率と、上位1社の比率粗利率が低ければ単価や外注の範囲を見直す
B前年より伸びているか保守や追加開発を提案して、Aに近づける
C単発で終わっていないか納品後の連絡を決めて、次の相談につなげる

Aの顧客は、失うと影響が大きい一方で、粗利が薄いまま続いていることがあります。単価の見直し方は「受託の単価を上げる方法」に書いています。

B・Cの顧客を伸ばすには、過去の取引から次の提案の時期を拾うのが近道です。具体的な仕組みは「リピート受注を増やす方法」にまとめています。上位1社の比率が高すぎると感じたら、新しい顧客を増やす計画も並行して立ててください。チャネルの選び方は「受託の新規開拓の方法」で書いています。

色分けで警告を出す

ランクと粗利率は、条件付き書式で色を付けると見落としません。「表示形式」メニューの「条件付き書式」で、条件に「カスタム数式」を選びます(出典:Google ドキュメント エディタ ヘルプ「Google スプレッドシートで条件付き書式ルールを使用する」、2026年10月18日確認)。

  • 範囲 A5:H200、数式 =$F5="A":Aランクの行を淡い色にする
  • 範囲 G5:G200、数式 =AND($G5<>"",$G5<0.2):粗利率が20%未満のセルを赤にする

20%は一例です。自社の粗利率の目安に合わせて書き換えてください。

ここからは自社製品の紹介です。Pipelia では、受注した案件の売上と発注した外注費から、案件別の粗利率と月次推移を自動で出します。粗利率が20%未満の案件には警告が出ます。

Pipeliaの粗利画面。案件別の粗利率・月次推移と、粗利率20%未満の案件への警告が確認できる

顧客別の偏りを年に数回見るだけなら、この記事のスプレッドシートで十分です。売上と外注費を毎月つないで見たくなったら、登録不要のデモを開いてみてください。

よくある質問

ABC分析はどのくらいの頻度で見直せばよいですか?

受託では、月に一度、請求をまとめるタイミングで見直すのが続けやすい頻度です。集計期間を直近12か月にしておけば、毎月終了日を1か月ずらすだけで更新できます。案件の切れ目が年に数回しかない場合は、四半期ごとでも偏りの変化は十分に追えるでしょう。

顧客が5社以下でもABC分析は意味がありますか?

顧客が少なくても、上位1社・上位3社の比率を見る意味はあります。むしろ顧客が少ないほど1社の発注が止まったときの影響が大きいため、依存度の数字を把握しておく価値があります。ただしランクの区切りは崩れやすいので、5社以下ならA・B・Cの判定より依存度と粗利率を中心に見てください。

売上ではなく粗利でABC分析をしてもよいですか?

粗利で並べる分析も有効です。QUERY の order by を sum(D) から sum(D)-sum(E) に変え、D列からF列の数式で売上の列を粗利の列に置き換えれば、粗利の順で同じ判定ができます。外注比率が案件ごとに大きく違う受託では、売上順と粗利順の両方を見ると、手元に残る顧客がはっきりします。