1章：粗利分析の基本（売上・原価・粗利・粗利率を3分で整理）
「頑張って売ってるのに、なぜか数字が残らない」――その原因は、売上だけを見て判断していることが多いです。商品が“儲かっているか”を最短で見抜くには、粗利（あらり）に注目するのが王道。ここでは、Excelで分析に入る前に必要な基本だけを、3分で整理します。
売上・原価・粗利の関係
- 売上：顧客に販売した金額（数量×販売単価の合計）
- 原価：仕入れや製造にかかった直接コスト（数量×仕入単価 など）
- 粗利：売上から原価を引いた「商品そのものの稼ぐ力」
式で書くとシンプルです。
- 粗利 ＝ 売上 − 原価
例えば、売上が10万円で原価が7万円なら、粗利は3万円。ここまでは直感的ですよね。ポイントは、売上が大きい＝儲かっているとは限らないこと。売上が大きくても、原価がそれ以上に重ければ粗利は残りません。
粗利率（％）が「比較」を可能にする
もう一段、分析を強くするのが粗利率です。粗利は金額なので、売上規模が大きい商品が目立ちがち。でも、儲かる商品を見つけるには、規模に引っ張られずに比較できる指標が必要です。
- 粗利率 ＝ 粗利 ÷ 売上
同じ粗利3万円でも、売上が10万円なら粗利率30％、売上が30万円なら粗利率10％。後者は「たくさん売っているのに薄利」の可能性があります。20代のサラリーマンが仕事で役立てるなら、上司に説明するときも「粗利額」と「粗利率」をセットで出すだけで説得力が段違いです。
Excelで扱うための最低限の定義（ブレない軸を作る）
粗利分析でつまずく典型は、「数字の定義が部署や人で違う」問題です。まずは次のように割り切ると、Excelでも迷いません。
- 売上：値引き後の実売上（請求ベース）
- 原価：仕入原価（または標準原価）。まずはどちらかに統一
- 粗利：売上−原価（販促費や人件費などは一旦入れない）
この記事の前半（2〜3章）では、まず「商品別に儲けの輪郭」を掴むことを目的に、粗利をシンプルに定義して進めます。次の4章で、値引き・返品・販促費といった現場あるあるのノイズを織り込んで精度を上げる流れです。
覚えるのはこの2つだけでOK
最後に、今日このあとExcelで手を動かすために必要なのは次の2式だけです。
- 粗利 ＝ 売上 − 原価
- 粗利率 ＝ 粗利 ÷ 売上
次章では、この2式をそのまま使って、最小構成の「粗利分析シート」をExcelで作ります。余計な項目は足さず、まずは“儲かる商品の当たり”を最短で見つけにいきましょう。
2章：Excelで作る「粗利分析シート」最小構成（必要な項目と表の形）
粗利分析で最初にやるべきは、凝ったダッシュボード作りではなく、集計しやすい「1枚の表」を作ることです。ここが雑だと、ピボットやランキングに進んだ瞬間に崩れます。逆に言えば、最小構成でも“儲かる商品の当たり”は十分見つかります。
最小構成で入れるべき項目（これだけで回る）
おすすめは、1行＝1明細（1商品×1取引）で揃える形です。列は次の7つが基本。
- 日付（例：2026/04/01）
- 商品コード（後で表記ゆれを消せるので必須）
- 商品名（人が見て分かるため）
- 数量（返品などはマイナスで持てる）
- 販売単価（税抜・税込はどちらかに統一）
- 仕入単価（原価単価）（ここも定義を統一）
- 売上・原価・粗利・粗利率（計算列。後述）
「売上」「原価」を最初から入力する運用もありですが、ミスが増えやすいので、可能なら数量×単価で機械的に計算させるのが安全です。
表の形は「Excelのテーブル化」が正解
シートに入力したら、範囲を選んでCtrl + Tでテーブルにします（見出しありにチェック）。テーブル化するメリットは3つ。
- 行を追加しても計算式が自動でコピーされる
- ピボットの集計範囲が伸びても追従しやすい
- 列名で参照できて、後から見ても崩れにくい
つまり、20代の現場担当でも「作った自分しか直せないExcel」になりにくい、ということです。
計算列は4つ。式は“固定”してブレを消す
列を追加して、以下の計算列を作ります。1章で整理した2式を、そのままExcelに落とすだけです。
- 売上：
=数量*販売単価 - 原価：
=数量*仕入単価 - 粗利：
=売上-原価 - 粗利率：
=IFERROR(粗利/売上,0)
IFERRORを入れるのは、売上が0（サンプル提供、0円処理など）のときにエラーで集計が止まるのを防ぐため。粗利率の表示形式はパーセンテージにしておきましょう。
ありがちな落とし穴：表記ゆれ・空欄・税区分
最小構成でも、ここを放置すると精度が落ちます。
- 商品名の表記ゆれ：集計軸は商品名ではなく、基本は商品コードに寄せる
- 単価の空欄：空欄があると売上・原価が0扱いになり、粗利率が暴れる。入力規則やフィルタで早期発見
- 税込・税抜の混在：粗利率が別物になるので、どちらかに統一（レポート前に直すのは地獄）
この章のゴールは「正しい数字を一発で集計できる土台」を作ること。次章では、この粗利分析シートを材料に、ピボットテーブルで商品別に粗利と粗利率を並べ、ランキングで“儲かる商品”をあぶり出す手順に進みます。
3章：儲かる商品を見つける集計術（ピボットテーブル×粗利率ランキング）
2章で作った「1行＝1明細」の粗利分析シートができたら、次は商品別に集計して“儲かる順”に並べるだけです。ここで効くのがピボットテーブル。関数で集計表を手作りすると、商品追加や期間変更のたびにメンテが発生しますが、ピボットならドラッグ＆ドロップで更新が完了します。
手順1：ピボットテーブルで「商品別の粗利」を一気に出す
粗利分析シート（テーブル化した範囲）のどこか1セルを選び、挿入 → ピボットテーブルを作成します。配置は新しいシートがおすすめ（集計用シートが散らからない）。
フィールドは次の形が最短です。
- 行：商品コード（必要なら商品名も下に追加）
- 値：売上（合計）
- 値：粗利（合計）
この時点で、商品ごとの「売上」と「粗利」が横並びになり、粗利額ベースで強い商品が見えます。まずはここで、粗利が大きい順に並べ替え（値の並べ替え）をして、上位商品をざっくり押さえましょう。
手順2：粗利率は「計算列」ではなくピボットの計算フィールドで作る
やりがちなのが、明細にある粗利率（1行ごとの粗利率）をピボットで平均してしまうミス。これは取引ごとの粗利率平均になり、商品全体の実態（売上加重の粗利率）とズレます。
正しいのは、商品別に集計した上での粗利率です。
- 商品別粗利率 ＝ 商品別粗利合計 ÷ 商品別売上合計
ピボットで作るなら、ピボットテーブル分析 → フィールド、アイテム、セット → 計算フィールドから、例えば名前を「粗利率（集計）」にして、数式を次で追加します。
=粗利/売上
表示形式はパーセンテージに。これで「商品別に正しく割り戻した粗利率」が出せます。
手順3：「粗利率ランキング」だけだと危ないので、2軸で絞る
粗利率ランキングは強力ですが、粗利率だけで上位を見ると売上が小さい“尖った商品”が上に来がちです。仕事で使うなら、次の2段構えが鉄板。
- 粗利額で上位（例：上位20商品）を抽出
- その中で粗利率が高い順に並べて「優等生」を特定
ピボット上でのやり方はシンプルで、粗利（合計）で降順ソート → フィルターで「上位10/20」→ その後に粗利率（集計）で降順ソート。これだけで、“売り場の主力なのに薄利”と“売れてて利益率も高い”が分かれます。
手順4：見つけたいのは「勝ち商品」と「改善対象」の2種類
集計結果の見方は、ざっくり次の2パターンに分類すると判断が速いです。
- 勝ち商品：粗利額も粗利率も高い（まず守る・伸ばす対象）
- 改善対象：売上は大きいのに粗利率が低い（価格・仕入・売り方の見直し候補）
特に後者は「頑張ってるのに数字が残らない」の正体になりやすいゾーン。20代のサラリーマンが上司に提案するなら、ピボットを見せながら『売上は稼いでるけど利益率が低い商品がこれです』と指差しできるだけで、会話が一段前に進みます。
ここまでで、ピボット×ランキングで“儲かる商品の当たり”は見つかります。ただし、現場には値引き・返品・販促費といったノイズが必ず混ざるもの。次章ではそれらを加味して、見誤らないための深掘り分析に進めましょう。
4章：見誤らないための深掘り分析（値引き・返品・販促費を加味する）
3章のピボット×粗利率ランキングで「当たり商品」は見つかります。ただし、その粗利はあくまで理想に近い粗利になりがち。現場には、値引き・返品・販促費という“利益を削る要因”が乗ってきます。
ここを無視すると、例えば粗利率が高いのに全然儲からない商品や、逆に薄利に見えるのに実は優秀な商品を取り違えます。勝ち筋を誤認すると、次の打ち手（5章）の精度も落ちるので、ここで一段深掘りします。
①値引き：売上は「定価」ではなく「実売上」で見る
値引きが多い商品は、表面上の単価や粗利率が良く見えても、実態は別物です。2章の定義どおり、売上は値引き後の実売上に寄せるのが鉄則。
明細に次の列を足すと、ズレが減ります。
- 値引き額（クーポン、特売、個別調整など）
- 実売上：
=売上-値引き額 - 粗利（実）：
=実売上-原価 - 粗利率（実）：
=IFERROR(粗利（実）/実売上,0)
ピボットで見る指標も「売上」「粗利」ではなく、実売上・粗利（実）・粗利率（実）に差し替えるだけ。これで「値引きで利益が溶けている主力商品」が炙り出せます。
②返品：数量マイナスだけで終わらせない（返品コストを持つ）
返品を数量マイナスで入れる運用は良いのですが、実務だと返品には追加コストがつきます。例：検品、再梱包、送料、廃棄など。ここまで入れないと、返品が多い商品が過大評価されます。
おすすめは列を2つ足して“見える化”すること。
- 返品フラグ（通常0、返品行は1）
- 返品処理費（返品行だけ金額を入れる。なければ平均でもOK）
そして最終的に見るのは、商品別の「粗利（実）」から返品処理費も引いた指標です。
粗利（補正後）＝ 粗利（実）− 返品処理費
ピボットでは、返品フラグの合計を出せば「返品件数」も同時に見えます。粗利は出ているのに返品が多い商品は、価格ではなく品質・説明・売り方に課題があるケースが多いので、次のアクションが変わります。
③販促費：商品別に“割り振って”初めて判断できる
販促費（広告、ポイント、販促物、EC手数料など）は、粗利分析で最も見落とされがち。でもここを入れると、「儲かってるように見える商品」の順位が平気で入れ替わります。
理想は商品別に実績を持つことですが、難しければルールを決めて配賦するだけでも前進です。
- 売上比で配賦：露出が売上に比例する施策向き
- 数量比で配賦：サンプル同梱・出荷作業など数量起点の費用向き
- 定額で配賦：LP制作費など特定商品に紐づく費用向き
Excelでは、商品別の販促費を別表（商品コード／販促費）で持ち、XLOOKUPで明細に引っ張るのが手堅いです。
=XLOOKUP([@商品コード],販促表[商品コード],販促表[販促費],0)
そして、最終的に見たいのはここ。
- 粗利（最終）＝ 粗利（補正後）− 販促費
- 粗利率（最終）＝ 粗利（最終）÷ 実売上
結論：「粗利」から「粗利（最終）」へ。見る指標を一段大人にする
3章までの粗利分析は“儲けの輪郭”を掴むフェーズ。4章では、値引き・返品・販促費を加味して、見誤らない粗利（最終）に寄せます。
ここまで整えると、上司への報告も強くなります。
- 「粗利率は高いんですが、値引きで実粗利率が落ちてます」
- 「利益は出ているように見えるが、返品コスト込みだと赤字です」
- 「販促費を乗せると、利益を生むのは別の商品でした」
次章では、この“補正後の事実”を材料に、価格改定・仕入れ交渉・売り方改善へ落とし込んでいきます。分析は、打ち手に変換できて初めて儲けに直結します。
5章：分析結果を次の打ち手へ（価格改定・仕入れ交渉・売り方改善の実践）
4章までで、値引き・返品・販促費まで織り込んだ粗利（最終）が見えるようになりました。ここからが本番です。分析は「気づき」で止めると、ただの自己満。数字を“次の一手”に翻訳できた人から、利益が伸びます。
やることはシンプルで、商品を3タイプに分けて、打ち手を固定化します。
- A：勝ち商品（粗利額も粗利率も強い）…守る・伸ばす
- B：売れてるのに薄利（売上大／粗利率低、値引き多い等）…改善して“利益商品”にする
- C：赤字・足を引っ張る（粗利（最終）がマイナス）…撤退 or 条件変更
打ち手①：価格改定は「何％上げると黒字化するか」で提案する
価格の話は感情論になりがちですが、粗利分析を使えばロジックで押せます。ポイントは「値上げする／しない」ではなく、黒字化に必要な最低ラインを出すこと。
例えば、商品別に次を出します。
- 現状の粗利（最終）
- 目標粗利（最終）（例：最低でも粗利率15％、など）
- 不足粗利＝目標粗利−現状粗利
そして不足粗利を売上で割れば、「必要な実売上の上げ幅（≒値上げ余地）」が見えます。上司に出すときは、価格改定案を1つに絞らず、次のように段階案で出すと通りやすいです。
- まずは値引き条件を締めて実質値上げ（クーポン対象から外す等）
- セット化・容量変更で見かけ価格を維持しつつ単価改善
- 最後に定価改定（やるなら理由とタイミングもセット）
打ち手②：仕入れ交渉は「根拠のある改善幅」で話す
薄利商品の改善は、価格だけが手段ではありません。むしろ現場では、仕入単価を数％落とすだけで粗利が跳ねるケースが多いです。
交渉で効くのは「お願いします」ではなく、次の2点を数字で示すこと。
- 対象商品がどれだけ売れているか（数量・売上）
- 単価をいくら下げたいか（例：1個あたり-20円で粗利（最終）が黒字化）
さらに強いのは、代替案を添えることです。
- 発注ロット増で単価を下げる
- 納期条件を緩める代わりに単価を下げる
- 返品率が高いなら、不良負担や返品条件の見直しも交渉する
粗利（最終）まで見ていると、「値引き」ではなく返品コストが原因で赤字になっている商品も見つかります。その場合は値段交渉より、仕様・梱包・説明書きなどの改善のほうが効きます。
打ち手③：売り方改善は「勝ちパターンの横展開」と「ムダ販促の停止」
勝ち商品（A）は、守るだけだともったいない。やるべきは伸ばすための配置です。
- 入口商品（集客）と利益商品（回収）をセットで提案する
- 高粗利商品を同梱・合わせ買い導線の中心に置く
- 返品が多い商品は、説明文・サイズ表記・注意書きの改善を優先する
逆に、販促費を加味した結果「儲かっているように見えた商品」が落ちたなら、打ち手は明確です。ムダな販促を止める／配分を変える。粗利（最終）を見ながら、販促の優先順位をこう入れ替えます。
- Aに寄せる（伸ばせば利益が増える）
- Bは改善できる見込みがある施策だけ残す
- Cは原則ストップ（続けるなら撤退コストとして明確化）
結論：提案資料は「商品×打ち手×期待効果」の1枚に落とす
最後に、職場で刺さる形にまとめます。ピボットの画面を見せるより、1枚の表にするのが最短です。
| 商品 | 分類 | 課題（原因） | 打ち手 | 期待効果 |
|---|---|---|---|---|
| 主力商品A | B | 値引きで粗利率（最終）が低下 | 値引き条件見直し／セット化 | 粗利（最終）+◯万円/月 |
ここまでできれば、あなたの粗利分析は「見つける」から「動かす」に進化します。Excelは作って終わりではなく、会社の利益を増やす会話を作る道具です。
コメント