コラム ／ 2026年11月1日

取引先ごとの採算の出し方｜儲かっている取引先・儲かっていない取引先をExcelで分ける

「売上はあるのに、利益が残らない」。その原因は、会社全体の数字だけを見ていても分かりません。取引先ごとに分けてみると、売上は大きいのに、ほとんど儲かっていない相手が混ざっていることがよくあります。この記事では、取引先ごとの採算をExcelで出す手順をまとめます。

会社全体の粗利率は「平均」にすぎない

次の表は、従業員20人・年商約3億円の架空の会社の例です。会社全体の粗利率は27.2%ですが、取引先ごとに見ると、17.9%から40.0%まで大きく違います。

会社・数字はすべて架空の例です。

一番売上の大きいa社の粗利率が、一番低い。こういうことは珍しくありません。大口の取引先ほど値引きを求められやすく、長く続くうちに条件が見直されないままになりがちだからです。

用意するデータ

• 売上の明細：日付・取引先・商品（または案件）・金額。請求書や販売管理のソフトから出せることが多いです
• 原価：商品（または案件）ごとの仕入れ・外注・材料の金額。案件ごとに分からない場合は、商品ごとの原価率でもかまいません
• 期間：まずは直近1年分。季節で売れ方が変わる会社は、1年分で見るのがおすすめです

Excelでの手順

1. 1
   明細を1つの表に

   日付・取引先・商品・売上

2. 2
   原価を付ける

   商品ごとの原価を引いてくる

3. 3
   取引先ごとに集計

   ピボットテーブル

4. 4
   粗利率を出す

   粗利 ÷ 売上

5. 5
   4つに分ける

   売上の大きさ × 粗利率

1. 売上の明細を1つの表にまとめる

1行に1件の売上を並べ、「日付・取引先・商品・売上」の列を作ります。取引先の名前の書き方（「株式会社」の有無など）は、ここでそろえておきます。

2. 1行ごとに原価を付ける

「原価」の列を足し、商品ごとの原価（または 売上 × 原価率）を入れます。商品ごとの原価の表を別に作っておき、XLOOKUP（またはVLOOKUP）で引くと楽です。

3. 取引先ごとに集計する

表全体を選んで「挿入 → ピボットテーブル」を選び、行に「取引先」、値に「売上」と「原価」を入れます。SUMIFS関数で集計してもかまいません。

4. 粗利と粗利率を出す

粗利 ＝ 売上 − 原価、粗利率 ＝ 粗利 ÷ 売上 で計算し、粗利率の低い順に並べます。

5. 4つの区分に分ける

売上の大きさと、粗利率が会社の平均より高いか低いかで、取引先を4つに分けます（次の章）。

4つの区分と、それぞれの打ち手

右下の「売上が大きいが、粗利率が低い」取引先は、少しの見直しでも金額が大きく動きます。上の例でa社の価格を3%見直せば、お客さんの数が変わらない場合、粗利は年約234万円増えます。値上げの効き目の計算は、値上げしたら客は何%減っても大丈夫？で詳しくまとめています。

もう一歩進めるなら：手間も加味する

粗利だけでは見えないコストもあります。たとえば次のようなものです。

• 小口の注文が多く、配送や事務の手間がかかる
• 値引きや返品が多い
• 支払いが遅く、お金が長く寝ている

手間をざっくり出して粗利から引くと、より実態に近い採算が見えます。最初から正確にする必要はなく、差が大きい取引先が分かれば十分です。

気をつけること

• 原価の付け方をそろえる：取引先ごとに付け方が違うと、比べられません
• 数字だけで取引をやめない：採算が低くても、ほかの注文の入口になっている、関係が長い、などの事情があります。数字は、話し合いの材料として使います
• 法律に関わる判断は専門家に：取引先との価格の交渉には、下請けの取引に関するルールなどが関わることがあります

まとめ

会社全体の粗利率は平均にすぎません。売上の明細と原価を1つの表にまとめ、取引先ごとに集計して、4つの区分に分ける。それだけで、どこから手を付ければ利益が残るかが見えてきます。

← コラム一覧へ