飲食店や小売業、製造業など、商品やサービスを販売するすべての事業において、原価率を正確に把握することは収益管理の根幹となる重要な作業です。
「原価率が高すぎて利益が出ない」「商品ごとの収益性を比較したい」「損益計算に使える原価管理シートを作りたい」といった悩みを持つ方は多いのではないでしょうか。
Excelを活用すれば、原価率の計算から粗利益・マージンの算出、商品別の収益性評価まで、一つのシートで一元管理できます。
本記事では原価率の基本計算式からExcelへの入力方法、コスト分析・損益計算への応用、マージン算出の実践テクニックまで、実務で即使えるサンプルデータとイメージ図を交えながら丁寧に解説します。
【この記事のポイント】
・原価率の基本計算式とExcelでの数式入力方法
・粗利益・粗利率（マージン）をセットで自動算出する方法
・商品別の収益性評価と損益計算シートの作り方
・目標原価率から逆算して販売価格を設定する方法
Excelで原価率計算を行うには基本の計算式と粗利益の関係を正しく理解することが重要
原価率の計算をExcelで正確に行うには、まず計算式の意味と粗利益・粗利率との関係を押さえておく必要があります。
原価率とは、売上高に対して原価がどれだけの割合を占めているかを示す指標です。
【原価率の基本計算式】
原価率 ＝ 原価 ÷ 売上高 × 100（%）
【粗利益（売上総利益）の計算式】
粗利益 ＝ 売上高 − 原価
【粗利率（マージン）の計算式】
粗利率 ＝ 粗利益 ÷ 売上高 × 100（%）
※原価率 ＋ 粗利率 ＝ 100%
たとえば、販売価格1,000円の商品の原価が600円であれば、原価率は600 ÷ 1,000 × 100 ＝ 60%、粗利率は40%となります。
原価率と粗利率は必ず合計100%になるという関係を覚えておくと、計算結果の検算にも役立ちます。
以下のサンプルデータを使って解説を進めます。
| A列：商品名 | B列：売上高（円） | C列：原価（円） | D列：粗利益（円） | E列：原価率 | F列：粗利率 |
|---|---|---|---|---|---|
| ランチセットA | 1,000 | 350 | |||
| ディナーコースB | 3,500 | 1,200 | |||
| ドリンクセットC | 500 | 80 | |||
| デザートプレートD | 800 | 320 | |||
| テイクアウト弁当E | 900 | 400 |
1行目がヘッダー、2行目以降に各メニューのデータが並んでいます。
まずD2セルに粗利益を算出する数式を入力します。
【D2セルの数式（粗利益）】
=B2-C2
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 商品名 | 売上高 | 原価 | 粗利益 | 原価率 | 粗利率 |
| 2 | ランチセットA | 1,000 | 350 | 650 | =C2/B2 | |
| 3 | ディナーコースB | 3,500 | 1,200 | 2,300 | 34.3% | |
| 4 | ドリンクセットC | 500 | 80 | 420 | 16.0% | |
| 5 | デザートプレートD | 800 | 320 | 480 | 40.0% |
▲ E2セルに「=C2/B2」と入力しパーセント書式を適用。オートフィルで全行に一括適用する
続いてE2セルに原価率、F2セルに粗利率を入力します。
【E2セルの数式（原価率）】
=C2/B2
【F2セルの数式（粗利率）】
=D2/B2
または
=1-E2
E列・F列を選択して「%」ボタンを押すとパーセント表示になります。
F列に「=1-E2」を使うと、原価率と粗利率の合計が必ず100%になることを数式上で保証できるためミスの発見にも役立ちます。
各数式をオートフィルで下方向にコピーすれば、全メニューの原価率・粗利率が一括で算出されます。
【操作のポイント】
原価率は「=C2/B2」、粗利率は「=1-E2」で算出します。粗利率に「=1-E2」を使うことで原価率との合計が自動的に100%に保たれ、入力ミスのチェックにも活用できます。
商品別の収益性評価をExcelで行ってコスト分析に活用する方法
原価率・粗利率が算出できたら、次は商品ごとの収益性を比較・評価する分析シートへと発展させていきましょう。
原価率だけでなく、売上数量・売上合計・粗利合計を組み合わせることで、どの商品が実際に利益に貢献しているかを多角的に評価できます。
SUMPRODUCT関数で売上加重平均原価率を算出する方法
複数商品の原価率を単純平均すると、売上規模の差が無視されてしまいます。
売上高で重み付けした加重平均原価率を算出することで、実態に即したコスト分析が可能になります。
【売上加重平均原価率の数式】
=SUMPRODUCT(B2:B6,E2:E6)/SUM(B2:B6)
※B2:B6が売上高（重み）、E2:E6が各商品の原価率
この加重平均原価率が全体の収益構造を最も正確に反映した原価率となります。
目標原価率（例：飲食業では30〜35%が目安）と比較することで、商品ミックス全体の改善方向性が見えてきます。
条件付き書式で原価率の高い商品を自動ハイライトする方法
E列の原価率セルを選択した状態で「ホーム」→「条件付き書式」→「新しいルール」を設定すると、原価率が高い商品を自動的に強調表示できます。
たとえば「原価率が40%以上のセルを赤で塗りつぶす」というルールを設定しておけば、コスト管理上の要注意商品が瞬時に識別できます。
【条件付き書式の設定例】
ルール1：セルの値が 0.4以上 → 赤（要改善）
ルール2：セルの値が 0.3以上0.4未満 → 黄（注意）
ルール3：セルの値が 0.3未満 → 緑（良好）
RANK関数で粗利益の高い順にランキングして主力商品を特定する方法
G列に粗利益ランキングを追加することで、どの商品が利益貢献度の高い主力商品かを自動的に順位付けできます。
【G2セルの粗利益ランキング数式】
=RANK(D2,$D$2:$D$6,0)
※第3引数0で粗利益の高い順（降順）でランキング
粗利益ランキングが高い商品は積極的に販売を強化すべき主力商品であり、原価率も低ければ理想的な収益構造といえます。
逆に粗利益ランキングが低く原価率が高い商品は、価格改定・仕入れコスト見直し・メニューからの除外などの検討対象となります。
【操作のポイント】
SUMPRODUCT関数で加重平均原価率を算出し、条件付き書式で要注意商品をハイライト、RANK関数で粗利益ランキングを作成する3点セットで収益性評価シートが完成します。
目標原価率から販売価格を逆算してExcelで価格設定を行う方法
新商品の価格設定や原価改善の際に、目標原価率から逆算して適正な販売価格を算出することは非常に実用的なアプローチです。
「原価率を30%以内に抑えるためには、この原価でいくらで売ればよいか」という問いに、Excelで瞬時に答えを出せます。
【目標原価率から販売価格を逆算する計算式】
販売価格 ＝ 原価 ÷ 目標原価率
Excelでの数式例：=C2/$H$1
（H1セルに目標原価率を入力しておく）
| A（商品名） | C（原価） | H（目標販売価格） | |
|---|---|---|---|
| 1 | 商品名 | 原価 | 目標販売価格 |
| 2 | ランチセットA | 350 | 1,167円 |
| 3 | ディナーコースB | 1,200 | 4,000円 |
| 4 | ドリンクセットC | 80 | 267円 |
目標設定セル
H1を変更するだけで全商品の目標販売価格が自動更新されます
▲ H1に目標原価率（30%）を入力し、H2セルに「=C2/$H$1」で目標販売価格を逆算する
H1セルに目標原価率（例：30%）を入力しておき、H2セルに「=C2/$H$1」を入力してオートフィルで下方向に引っ張ると、全商品の目標販売価格が一括で算出されます。
H1の目標原価率を変更するだけで全行の目標販売価格が瞬時に更新されるため、「30%ではいくらか」「35%ではどうか」というシミュレーションが手軽に行えます。
ROUND関数で目標販売価格を切りのよい金額に整える方法
逆算した目標販売価格が「1,166.7円」のような端数になる場合は、ROUND関数で丸めると実務的な価格設定になります。
【10円単位に四捨五入した目標販売価格の数式】
=ROUND(C2/$H$1,-1)
（-1で10円単位、-2で100円単位に四捨五入）
目標原価率と実際の原価率の差分を算出して改善余地を数値化する方法
I列に「実際の原価率 − 目標原価率」の差分を算出することで、各商品のコスト改善余地を数値で一覧できます。
【原価率の改善余地を算出する数式（I2セルに入力）】
=E2-$H$1
（プラスなら目標超過、マイナスなら目標達成済み）
差分がプラスの商品は原価率が目標を超えており、仕入れ交渉・レシピ見直し・販売価格の引き上げなどの対策が必要な商品として一覧表示されます。
【操作のポイント】
目標販売価格の逆算は「=C2/$H$1」で算出し、H1の目標原価率を変えるだけで全行が自動更新されます。ROUND関数で端数処理し、差分列で改善余地を数値化すると価格改定の判断材料として使えます。
損益計算シートをExcelで作成して原価率を収益管理に組み込む方法
商品別の原価率管理をさらに発展させ、月次の損益計算シート（P&L）に原価率を組み込むことで、事業全体の収益性を俯瞰的に把握できるようになります。
売上合計・原価合計・粗利合計をSUM関数で自動集計する方法
各商品の売上高・原価・粗利益をSUM関数で合計し、全体の原価率・粗利率を算出します。
【合計行の数式（7行目に設定する場合）】
売上合計：=SUM(B2:B6)
原価合計：=SUM(C2:C6)
粗利合計：=SUM(D2:D6)
全体原価率：=C7/B7
全体粗利率：=D7/B7
| A（商品名） | B（売上高） | C（原価） | D（粗利益） | E（原価率） | F（粗利率） | |
|---|---|---|---|---|---|---|
| 2 | ランチセットA | 1,000 | 350 | 650 | 35.0% | 65.0% |
| 3 | ディナーコースB | 3,500 | 1,200 | 2,300 | 34.3% | 65.7% |
| 4 | ドリンクセットC | 500 | 80 | 420 | 16.0% | 84.0% |
| 5 | デザートプレートD | 800 | 320 | 480 | 40.0% | 60.0% |
| 6 | テイクアウト弁当E | 900 | 400 | 500 | 44.4% | 55.6% |
| 7 | 合計・全体 | 6,700 | 2,350 | 4,350 | 35.1% | 64.9% |
▲ 合計行にSUM関数で売上合計・原価合計・粗利合計を集計し、全体の原価率・粗利率を算出する
販売数量を加味した売上シミュレーションシートを作成する方法
各商品の販売数量（個数）を追加列として設け、「売上高 ＝ 単価 × 販売数量」「原価合計 ＝ 単価原価 × 販売数量」の形で売上シミュレーションシートを作ることもできます。
【販売数量を加味した売上高の数式（B2セルに入力する場合）】
=J2*K2
※J2：単価、K2：販売数量
K列の販売数量を変えるだけでB列の売上高、C列の原価、D列の粗利益、E列の原価率がすべて連動して更新されるため、「今月のメニュー別販売数を入力するだけで損益が自動計算される」シートが実現します。
前月比・目標比を並べた月次レポートシートを作成する方法
原価率の当月実績・前月実績・目標値を横並びで表示する月次レポートシートを作成することで、毎月の経営会議でそのまま使える資料が完成します。
IFERROR関数で前月データが未入力の場合のエラーを処理し、条件付き書式で目標比の達成・未達を色分けすると視認性の高いレポートになります。
【操作のポイント】
損益計算シートの合計行はSUM関数で売上・原価・粗利を集計し、全体原価率は「=C7/B7」で算出します。販売数量列を追加して単価と掛け合わせることで売上シミュレーションシートへと発展させられます。
原価率計算でよくあるミスとExcelでの対処法
原価率の計算をExcelで運用していると、いくつかのよくあるミスやエラーに遭遇することがあります。
代表的なミスと対処法を事前に把握しておくことで、実務でのトラブルを最小限に抑えられます。
売上高と原価を混同した場合の計算ミスへの対処法
原価率の計算では「原価 ÷ 売上高」が正しい数式ですが、誤って「売上高 ÷ 原価」と入力してしまうミスが起こりやすいです。
E列の値が1を超えている（100%超）場合は分母と分子が逆になっているサインです。
MAX関数でE列の最大値をチェックし、1を超えていた場合にエラーメッセージを表示するセルを設けておくと気づきやすくなります。
【原価率の異常値チェック数式】
=IF(MAX(E2:E6)>1,”⚠ 原価率が100%を超えている行があります”,”正常”)
売上高が0または空白の場合のDIV/0エラー対処法
| A（商品名） | B（売上高） | C（原価） | E（原価率） | |
|---|---|---|---|---|
| 2 | ランチセットA | 1,000 | 350 | 35.0% |
| 3 | 新メニュー（未販売） | 0 | 200 | （空白） |
| 4 | デザートプレートD | 800 | 320 | 40.0% |
▲ IFERRORで売上高が0の場合のDIV/0エラーを空白表示に置き換える
売上高が0や空白の場合、通常の数式では「#DIV/0!」エラーが発生します。
IFERROR関数でエラーを空白に置き換えることでシート全体の見た目を整えられます。
【DIV/0エラーを回避する数式】
=IFERROR(C2/B2,””)
消費税込みの売上高・原価が混在する場合の注意点と対処法
売上高が税込みで原価が税抜き（または逆）のデータが混在していると、原価率が正確に算出されません。
計算前に売上高・原価の両方を税抜きに統一するか、税込みに統一するかを決めておくことが重要です。
税抜きに統一する場合は「=B2/1.1」で税込み価格から税抜き価格に変換してから計算に使うと正確な原価率が得られます。
【操作のポイント】
IFERROR関数でDIV/0エラーを空白処理し、MAX関数で100%超の異常値チェックを設けることで安全な運用ができます。売上高・原価の税込み・税抜きは計算前に必ず統一しておきましょう。
まとめ：原価率計算をエクセルでコスト分析・収益性評価・損益計算に活かすためのポイント
本記事では、Excelで原価率計算を行う方法として、基本計算式の入力から始まり、粗利益・粗利率のセット算出、商品別収益性評価、目標原価率からの価格逆算、損益計算シートの構築、エラー対処法まで体系的に解説しました。
原価率の基本数式は「=C2/B2」で、粗利率は「=1-E2」と入力することで原価率と常に合計100%になる関係を保てます。
SUMPRODUCT関数による加重平均原価率の算出、条件付き書式による要注意商品のハイライト、RANK関数による粗利益ランキングの3点セットで実践的な収益性評価シートが完成します。
目標原価率からの販売価格逆算は「=C2/$H$1」で算出し、ROUND関数で端数処理することで実務的な価格設定に活用できます。
損益計算シートにはSUM関数で売上・原価・粗利を集計し、販売数量列を追加することで売上シミュレーションシートへと発展させることが可能です。
IFERROR関数でエラー対策、MAX関数で異常値チェックを設けることでシートを長期間安定して運用できる仕組みが整います。
Excelでの原価率管理を日常業務に取り入れ、コスト分析と収益性評価の精度を高めることでより確かな経営判断に役立ててみてください。
