利益率分析表で「何がわかるか」—取引先別に見るべき指標を整理
取引先別の利益率分析表は、ひと言でいえば「売上が大きいのに儲からない相手」をあぶり出すための表です。20代の会社員だと、まず任されるのは集計やレポート作成。そこで役立つのが、売上だけでは見えない“質”の比較です。売上ランキングは誰でも作れますが、利益率まで踏み込めると一気に評価されやすくなります。
この分析表で分かることは主に次の3つです。
- どの取引先が会社の利益を支えているか（粗利額の貢献度）
- 売上の割に利益が薄い取引先はどこか（利益率の低さ）
- 改善の打ち手を優先すべき取引先はどこか（粗利額×利益率のバランス）
ここでいう「見るべき指標」は、難しいKPIを並べる必要はありません。まずは王道の3点セットでOKです。
① 売上（規模感）
売上は取引の大きさを表す指標です。ただし注意点は、売上が大きい＝良い取引先とは限らないこと。値引きが多い、特急対応が多い、返品が多いなど、コスト要因が隠れているケースがあります。
② 粗利（儲けの“金額”）
粗利（売上−原価）は「会社に残る額」。最終的に会社を潤すのは率より“額”なので、まず粗利額で貢献度を見ます。粗利額が大きい取引先は、多少利益率が低くても重要顧客であることがあります。
③ 利益率（儲けの“効率”）
利益率（粗利÷売上）は「効率の指標」。同じ売上でも、利益率が高いほど少ないリソースで儲かっている可能性が高いです。特に、売上は大きいのに利益率が低い取引先は、値上げ交渉・原価改善・契約条件の見直しなど、改善余地が埋まっていることが多いです。
実務で使いやすい見方としては、取引先をざっくり4象限で捉えると判断が速くなります。
- 売上大×利益率高：最優良。関係維持と深耕が最優先
- 売上大×利益率低：危険ゾーン。条件見直しの最優先候補
- 売上小×利益率高：伸びしろ枠。提案強化で育てる
- 売上小×利益率低：優先度低。対応コスト次第で整理も検討
この章の結論はシンプルで、利益率分析表は「売上の大きさ」と「儲けの額」と「儲けの効率」を取引先別に並べ、次に手を打つ順番を決めるための道具です。次章では、そのために必要なデータ項目（売上・原価・粗利）と、Excelに入れる前の整形ルールを整理します。
作成前の準備—必要なデータ項目（売上・原価・粗利）と整形ルール
利益率分析表は、計算式やピボットを組む前の「データの形」で9割決まります。ここでつまずくと、あとから数字が合わない・集計できない・取引先が分裂する（表記ゆれ）などの事故が起きがち。先に必要項目と整形ルールを押さえておけば、3章以降がスムーズになります。
まず必要なデータ項目（最低限）
- 取引先名（または取引先コード）：集計の軸。可能ならコード推奨（表記ゆれ対策）
- 売上：請求金額ベースにするのか、出荷ベースにするのかを先に統一
- 原価：仕入原価・製造原価など、社内で使う定義に合わせる
- 日付（売上計上日）：月別・四半期別に切れるように必須
粗利は「売上−原価」でExcel側で作れますが、原価データが別システムにある場合は、売上データと突合できるキー（伝票番号、受注番号、案件IDなど）も準備しておくと後工程が楽です。
「1行＝1取引」ルールで作る
分析用データの鉄則は、1行に1つの取引（または明細）を入れること。たとえば「A社：4月合計」「B社：4月合計」みたいに最初から集計した表を作ると、あとで切り口（商品別、担当者別、月別）を変えたくなったときに詰みます。
おすすめの列構成はこの形です。
| 日付 | 取引先コード | 取引先名 | 売上 | 原価 | （任意）商品 | （任意）担当 |
|---|
整形ルール①：取引先名の表記ゆれを潰す
「(株)」と「株式会社」「全角スペースあり」などの表記ゆれは、ピボットで別会社扱いになります。対策はシンプルで、可能なら取引先コードを軸にすること。コードがない場合は、Excelの置換や正規化で最低限そろえます。
- 前後の空白を削除（TRIM相当）
- 全角/半角の混在を減らす（記号・スペース）
- 「株式会社」「(株)」などをどちらかに統一
整形ルール②：売上・原価は「数値」で持つ
見た目が「1,200,000円」でも、文字列だと計算や集計が壊れます。通貨記号や単位（円、¥）は列に入れない、カンマは表示形式で付ける、が基本です。マイナス（返品・値引き）が混じる可能性もあるので、符号のルールも統一しておきましょう。
整形ルール③：空白・0・エラーの扱いを決める
利益率は「粗利÷売上」なので、売上が0だと割り算エラーになります。実務では「売上0の行は除外する」「利益率は空欄にする」など、先に方針を決めておくと後で揉めません。また、原価が未計上の行がある場合は、未確定データが混じる月は分析対象外にするなどの線引きも必要です。
整形ルール④：データ範囲は“テーブル化”前提で用意する
行や列の途中に空白行を挟まない、見出しは1行に固定、余計な結合セルは使わない。ここまで揃えておくと、次章で計算式を下まで自動でコピーでき、ピボットでも新しい行が勝手に取り込まれます。
準備のゴールは、「取引先」「売上」「原価」「日付」が揃い、1行1取引で、数値が数値になっている状態。ここまで整うと、3章の粗利・利益率は計算式で一瞬です。
Excelで土台を作る—計算式で粗利・利益率を自動算出する手順
2章で整形した「1行＝1取引」データができたら、次は粗利と利益率をExcelで自動計算できる土台を作ります。ここでのポイントは、手計算や電卓を捨てて、増え続けるデータでも壊れない形にすること。4章のピボットに渡す前の“下ごしらえ”です。
STEP1：元データを「テーブル」にする（自動で式が伸びる）
データ範囲内のどこかをクリックして、Ctrl + T（Macは⌘ + T）でテーブル化します。チェックボックスの「先頭行をテーブルの見出しとして使用する」をONにしてOK。
- 行が増えてもテーブルが自動で広がる
- 計算列（粗利・利益率）の式が新しい行にも自動適用される
- ピボットの参照先として安定する（範囲ズレ事故が減る）
STEP2：粗利列を作る（売上−原価）
テーブルの右側に列を追加し、見出しを粗利にします。粗利のセルに次の式を入れます。
=@売上-@原価テーブルを使っていれば列名で扱えるので、行がどれだけ増えても壊れにくいです（表示は環境により =[@売上]-[@原価] になります）。粗利はこの後の集計（取引先別の貢献度）でそのまま使うので、まずここを確実に。
STEP3：利益率列を作る（粗利÷売上）
次に列を追加して見出しを利益率にします。利益率は売上が0のとき割り算エラーになるので、最初からエラー回避を入れておくのが実務向きです。
=IFERROR(@粗利/@売上,"")これで、売上が0（または空欄）だった行は空欄になり、集計表が「#DIV/0!」だらけになる事故を防げます。利益率の列は、表示形式をパーセンテージ（%）に変更しておきましょう（ホーム → 数値 → %）。小数点は1桁 or 2桁が見やすいです。
STEP4：最低限のチェック項目（数字が合ってるかを秒で確認）
ピボットに進む前に、土台の数字がズレていないかだけ確認します。おすすめは次の3つです。
- 粗利が明らかにおかしい行（売上より原価が大きくてマイナス、桁が違う等）がないか
- 利益率が100%超になっていないか（原価の入力ミスや符号ミスの可能性）
- 集計の整合：テーブル下で 売上合計−原価合計＝粗利合計 になっているか
この段階で「粗利は合ってるのに利益率がおかしい」場合、利益率列が文字列扱いだったり、売上列に文字（円やカンマ崩れ）が混ざっていることが多いです。2章のルール通り、売上・原価が数値になっているかも再確認しておくと安心です。
STEP5：列の順番を“集計しやすい形”に整える
最後に、4章のピボット集計がやりやすい列順にしておきます。おすすめは以下です。
| 日付 | 取引先コード | 取引先名 | 売上 | 原価 | 粗利 | 利益率 |
|---|
ここまでできれば、土台は完成です。次章ではこのテーブルをそのまま使い、ピボットテーブルで取引先別の売上・粗利・利益率を一発で可視化します。
取引先別に集計する—ピボットテーブルで利益率を一発可視化
3章までで「1行＝1取引」のテーブルに、粗利・利益率まで入った状態になりました。ここからはピボットテーブルの出番です。目的はシンプルで、取引先別に「売上」「粗利」「利益率」を並べ、儲かっている相手／危ない相手を一瞬で見える化すること。手作業でSUMIFを組むより速く、切り口変更にも強いのがピボットのメリットです。
STEP1：ピボットテーブルを作成する
元データ（テーブル）のどこかをクリック → 挿入 → ピボットテーブル。データ範囲はテーブル名で自動指定されるはずです。配置先は「新規ワークシート」を選ぶと、事故りにくくおすすめです。
STEP2：行に「取引先」、値に「売上」「粗利」を入れる
まずは王道の集計から固めます。
- 行（Rows）：取引先名（できれば取引先コードも）
- 値（Values）：売上（集計方法＝合計）
- 値（Values）：粗利（集計方法＝合計）
ここまでで「どの取引先が粗利額で貢献しているか」が見えます。次に、肝心の利益率を“正しい形”で出します。
STEP3：利益率は「平均」ではなく“粗利÷売上”で出す（重要）
元データに利益率列があると、ピボットにそのまま入れたくなりますが注意。ピボットで利益率を平均すると、取引金額の大小を無視した平均になり、実態とズレやすいです（小口の高利益率が過大評価される）。
おすすめは、ピボット内で計算フィールド（Calculated Field）を作り、合計粗利 ÷ 合計売上の形で利益率を出す方法です。
- ピボット内をクリック
- ピボットテーブル分析 → フィールド、アイテム、セット → 計算フィールド
- 名前：利益率（集計）
- 数式：
=粗利/売上
追加できたら表示形式をパーセンテージ（%）に変更します（値フィールドの設定 → 表示形式）。これで取引先別に“正しい利益率”が並びます。
STEP4：並べ替えとフィルターで「見る順番」を作る
可視化は、並べ替えで8割決まります。たとえば次の運用が実務向きです。
- 粗利（合計）の降順：会社の利益を支えている取引先が上に来る
- 利益率（集計）の昇順：危険ゾーン（薄利）が炙り出せる
加えて、ピボットのフィルターに日付を入れるか、日付を右クリックしてグループ化（月/四半期）しておくと、「今月だけ」「上期だけ」の比較が一発でできます。
ここまでできれば、取引先別の売上・粗利・利益率が同じ表に揃い、“売上は大きいのに儲からない相手”が見える状態になります。次章では、このピボットをさらに読みやすくするために、条件付き書式やグラフで「稼げる取引先」を見抜く仕上げをしていきます。
分析・改善につなげる—条件付き書式／グラフで「稼げる取引先」を見抜く方法
4章で、取引先別に「売上・粗利・利益率（粗利÷売上）」が並ぶピボットができました。ここからのゴールは、表を“眺める”から一歩進んで、優先順位が即決できる状態にすることです。具体的には、危険ゾーン（薄利）と優良ゾーン（高利益）を色と形で強調し、打ち手（値上げ・条件見直し・深耕）に繋げます。
① 条件付き書式で「危ない取引先」を自動で浮かせる
おすすめは、ピボットの利益率（集計）と粗利（合計）に条件付き書式を入れる方法です。ポイントは「色を付けること」より、見る人が迷わないルールを作ること。
- 利益率（集計）：低いほど赤、高いほど緑（カラースケール）
- 粗利（合計）：大きいほど濃く（データバー）
操作は、該当列を選択 → ホーム → 条件付き書式。カラースケールは「赤黄緑」などでOKですが、実務で効くのはしきい値です。たとえば、社内の基準があるなら次のように固定します。
- 利益率が10%未満：赤（条件見直し候補）
- 利益率が20%以上：緑（深耕候補）
これで、表を開いた瞬間に「今、上司に突っ込まれるのはここ」「伸ばすならここ」が視覚で伝わります。なお、ピボットに条件付き書式をかけるときは、ルールの適用先を“選択したセルだけ”ではなく“このフィールドのすべてのセル”にしておくと、並べ替えや更新で崩れにくいです。
② “売上が大きいのに薄利”を狙い撃ちする（上から潰す）
改善インパクトが大きいのは、だいたい売上が大きいのに利益率が低い取引先です。ピボットで次の運用にすると、打ち手の順番が作れます。
- 売上（合計）で降順に並べ替え
- その上位（例：上位10社）だけに絞る
- その中で利益率（集計）が赤い取引先を優先して確認
改善の仮説はシンプルでOKです。「値引きが常態化してないか」「特急・個別対応が多くて工数が膨らんでないか」「仕入条件が古いままでは？」など、利益率を下げる“要因”の棚卸しをすると、次のアクションが具体化します。
③ グラフは「散布図」が最強（2軸で優先順位が一発）
表は正確ですが、意思決定はグラフの方が速いです。取引先別分析で強いのは散布図。横軸に売上、縦軸に利益率（集計）を置くと、1章で紹介した“4象限”がそのまま見える化できます。
- 右上（売上大×利益率高）：最優良。関係維持・深耕
- 右下（売上大×利益率低）：最優先の改善ゾーン。値上げ/条件見直し
- 左上（売上小×利益率高）：伸びしろ。提案を厚く
- 左下（売上小×利益率低）：優先度低。対応コスト次第で整理
散布図を作るときは、ピボット結果を別範囲にコピーして（値貼り付け）、挿入 → 散布図でOK。可能ならデータラベルで取引先名を表示すると、「この点はどこ？」が消えます。
条件付き書式で“赤い会社”を浮かせ、散布図で“右下”を特定できれば、分析は完成です。あとは、その取引先に対して「値上げ交渉」「原価の見直し」「契約条件の変更」など、利益率が上がる一手を提案できる状態になります。ここまで作れると、単なる集計担当ではなく「改善まで持っていける人」になれます。
コメント