商品別・得意先別の粗利管理をExcelで行う方法【儲かる商品を見抜く】

「売上は伸びているのに利益が残らない」。この症状の原因は、たいてい商品別・得意先別の粗利がブラックボックスになっていることにあります。全社の粗利率しか見ていないと、儲かる商品の値下げと儲からない商品の拡販という「逆の努力」に気づけません。

この記事では、売上明細データから商品別・得意先別の粗利を自動集計するExcelの設計と、値引きの影響を見える化する方法を解説します。

粗利管理のデータ設計

必要なのは「明細1行に売価と原価」

粗利管理の成否はデータ構造で決まります。販売管理ソフトやレジから出力する売上明細に、次の列が揃っているかを最初に確認してください。

• 日付／得意先／商品コード・商品名／数量／売上単価／売上金額
• 原価単価（または原価金額）／定価（値引き分析をする場合）

原価が明細にない場合は、商品マスタ（商品コード→標準原価）を別シートに持ち、VLOOKUPで補完します。標準原価でもまず十分で、精密な実際原価を追うのは運用が回ってからで構いません。

原価の補完：=数量*VLOOKUP(商品コード,商品マスタ,原価列,FALSE)
粗利：=売上金額-原価金額
粗利率：=IFERROR(粗利/売上金額,"-")

集計の作り方

ステップ1：ピボットテーブルで2軸集計

明細をテーブル化し、ピボットテーブルで「行=商品、値=売上・粗利・粗利率」の商品別集計と、「行=得意先、列=商品分類」のクロス集計を作ります。毎月データを貼り足して更新ボタンを押すだけの運用にできるのがピボットの強みです。（関連記事：No.38 ピボットテーブルの経理活用）

ステップ2：「粗利率×粗利額」のマトリクスで見る

	粗利率 高	粗利率 低
粗利額 大	主力。守るべき商品（安易な値下げ禁止）	量で稼ぐ商品。原価改善・値上げの本命
粗利額 小	育成候補。拡販の余地を探る	整理候補。取扱い継続の意義を問う

粗利率だけで見ると「率は高いが額が小さい商品」を過大評価しがちです。率と額の2軸で散布図（挿入→散布図）にすると、どの商品にテコ入れすべきかが一目で分かります。上位集中度の分析はABC分析と組み合わせてください。（関連記事：No.85 ABC分析のやり方）

ステップ3：値引きの影響を見える化する

値引額：=(定価-売上単価)*数量
値引率：=1-売上単価/定価
得意先別の値引額集計：=SUMIF(明細!得意先,対象先,明細!値引額)

「どの得意先に・どの商品で・いくら値引きしているか」を金額で並べると、現場の感覚と数字のギャップが露わになります。値引きは粗利に100%直撃するため、値引額ランキングは粗利改善の優先順位表そのものです。

数値例：値引き5%の重み

項目	定価販売	5%値引き
売上単価（原価700円の商品）	1,000円	950円
粗利/個	300円	250円
粗利の減少率	—	△16.7%
同じ粗利額に必要な数量	100個	120個

「5%くらい」の値引きは、粗利率30%の商品では粗利の6分の1を吹き飛ばし、取り返すには2割増しの販売数量が必要です。この換算表を営業と共有するだけで、値引きの安売りが目に見えて減る——粗利管理の即効性が最も出るポイントです。

実務の注意点・つまずきポイント

• 商品マスタの原価を放置しない：仕入価格が上がったのにマスタが古いままだと、粗利が過大表示されます。仕入価格改定時にマスタも更新するフローを決めてください。
• 送料・支払手数料も「準原価」として扱う：EC事業では送料・決済手数料・モール手数料を含めた「実質粗利」で見ないと判断を誤ります。明細に手数料列を追加しましょう。
• 会計の売上総利益との整合を確認する：明細ベースの粗利合計と試算表の売上総利益は、棚卸調整等で差が出ます。差異の理由を説明できる状態にしておくと数字の信頼性が保てます。
• 得意先別に見るときは回収条件もセットで：粗利が薄いうえに回収サイトが長い得意先は、資金繰り面でも負担です。（関連記事：No.23 売掛金滞留チェック表の作り方）
• 月次の定例レポートに組み込む：単発の分析で終わらせず、月次報告の1ページに商品別・得意先別のトップ10とワースト5を固定掲載するのが定着のコツです。（関連記事：No.34 月次報告資料の作り方）

よくある質問

Q. サービス業で「原価」がない場合はどうしますか？

担当者の投入時間×時間単価を原価とみなす「工数粗利」で同じ分析ができます。案件別の工数記録があれば、案件別・顧客別の採算がそのまま出ます。時間単価は（人件費＋間接費）÷実働時間で設定するのが一般的です。

Q. 粗利率は何%あればよいのですか？

業種によってまったく異なるため、絶対値より「自社内の商品間比較」と「時系列の変化」を見るのが実用的です。全社の目安が欲しい場合は業種平均との比較を経営分析ダッシュボードで行ってください。（関連記事：No.79 経営分析ダッシュボード）

Q. 明細データが多すぎてファイルが重くなります

明細は月別のシートに分けず、1枚のテーブルに縦に貯めるのが原則です（分析の柔軟性が段違いです）。10万行を超えて重くなってきたら、①明細を年度でファイル分割し集計値だけ引き継ぐ、②VLOOKUPをINDEX+MATCHや一度だけの値貼り付けに変える、③ピボットテーブルのデータモデル（PowerPivot）に載せ替える、の順で対処すると長く使えます。

粗利管理テンプレートのご案内

売上明細の貼り付けから、商品別・得意先別の粗利集計、率×額の4象限散布図、値引きランキング、月次レポート用のトップ10/ワースト5まで自動生成する粗利管理Excelテンプレートをご用意しています。商品マスタの原価更新チェック付きです。

まとめ

• 粗利管理は「明細1行に売価と原価」のデータ構造づくりから始まる
• 粗利率だけでなく「率×額」の2軸4象限で商品を評価する
• 値引き5%は粗利17%減（粗利率30%の場合）。値引額ランキングが改善の優先順位表になる
• 送料・手数料を含めた実質粗利で見ないとECでは判断を誤る
• 月次レポートに固定掲載して習慣化し、ABC分析・売掛金管理と組み合わせる