原価率をエクセルで計算する式と関数｜売価の逆算・全体の原価率・一覧表の作り方

飲食店のメニュー、小売の商品、製造業の製品、卸の取扱品。どの業種でも、値付けを見直すときや仕入れ値が上がったときに「この商品の原価率は何%か」を確かめる場面があります。電卓で1つずつ計算しているうちは問題になりませんが、商品が数十品目になると、エクセルの一覧表で一度に見たくなります。

この記事は、原価率をエクセルで計算する式と関数に絞ります。業種ごとの原価率の目安や、段取り替え・不良ロスなど原価に入れ忘れやすい費用は製造業の原価率の目安と管理方法で、原価をどの単位（顧客・案件・商品・部門）で見るかは利益が残らない会社の数字管理で扱っています。

原価率の式は「=原価÷売価」で、表示形式をパーセントにします

原価率は、売価のうち原価が占める割合です。売価から原価を引いた残りの割合が粗利率で、原価率と粗利率を足すと必ず100%になります。

• 計算式：原価率＝原価÷売価、粗利率＝1−原価率
• 計算例：原価350円・売価1,000円なら、原価率は350÷1,000＝35%、粗利率は65%

100は掛けず、セルの表示形式でパーセントにします

B列に原価、C列に売価を入れた表なら、D2セルに =B2/C2 と入れます。結果は0.35と表示されるので、D列を選んでホームタブの「%」（パーセントスタイル）を押します。Windowsなら Ctrl＋Shift＋5（%のキー）でも同じです。小数点以下を出したいときは、同じ場所の「小数点以下の表示桁数を増やす」で1桁ずつ増やします。出典：Microsoft サポート「Excel のキーボード ショートカット」

よくあるのが =B2/C2*100 と書いてからパーセント表示にしてしまう例で、35%が3500%と表示されます。100を掛けた「35」という数字は、あとで売価を逆算したり、条件付き書式で目標と比べたりするときにも100で割り戻す必要が出てきます。セルの中身は0.35のまま持ち、見た目だけをパーセントにするのが扱いやすい形です。

売価が空の行は、IFで空欄にして #DIV/0! を防ぎます

一覧表を下までコピーすると、売価がまだ決まっていない行や空行で #DIV/0!（0で割ったエラー）が出ます。その列を合計した瞬間に、合計もエラーになります。売価が空または0のときは空欄を返すように書きます。

=IF(C2=0,"",B2/C2)
  C2が空欄のときも0と同じ扱いになり、空欄を返します

IFERROR(B2/C2,"") でもエラーは消えますが、売価の列に文字が混ざったときのエラーまで一緒に隠れてしまいます。原因が「売価が未入力」だけと分かっているなら、IFでその条件だけを書いておくほうが、入力の間違いに気付けます。

原価率の一覧表で使う式は6つです

A列に商品名、B列に原価、C列に売価（どちらも税抜）を入れます。計算列はD〜L列を使い、計算列と重ならないN1セルに目標原価率、N2セルに目標粗利率を置きます。販売数や区分まで管理する場合も列がぶつからない配置です。

$N$1のように$を付けるのは、式を下の行へコピーしても目標原価率のセルがずれないようにするためです。G列は販売数、H列は原価額、I列は売上額、J列は区分に使うため、逆算した売価はK列とL列に置きます。原価と売価は税込と税抜を混ぜず、列見出しにも「税抜」と書いておくと入力時に迷いません。

売価の逆算は「原価÷目標原価率」を10円単位で切り上げます

原価が上がったときに「原価率30%に戻すには売価をいくらにすればよいか」を出すのが逆算です。原価350円、目標原価率30%なら、350÷0.3＝1,166.66…円です。端数のままでは値札に書けないので、ROUNDUPで切り上げます。

=ROUNDUP(B2/$N$1,-1)
  B2＝原価350円、N1＝30% → 1,170円
  1,170円で売ったときの原価率は 350÷1,170＝29.9%

ROUNDUPの2つ目の引数を-1にすると10円単位、-2にすると100円単位で切り上げます。四捨五入のROUNDを使うと、端数が切り下がった商品だけ目標の原価率をわずかに上回ります。目標を必ず守りたいなら切り上げにします。出典：Microsoft サポート「ROUNDUP 関数」

粗利率を目標にする場合、「原価×(1＋粗利率)」で売価を出すのは間違いです。原価350円に粗利率65%を乗せるつもりで350×1.65＝577.5円にすると、実際の粗利率は（577.5−350）÷577.5＝39.4%にしかなりません。粗利率は売価に対する割合なので、正しくは 350÷(1−0.65)＝1,000円 です。エクセルでは =ROUNDUP(B2/(1-$N$2),-1) と書きます。

複数商品の原価率は、平均せずに合計同士で割ります

店全体や会社全体の原価率を出すとき、D列の原価率をAVERAGEで平均すると、実際とかけ離れた数字になります。売れる数が商品ごとに違うのに、比率の平均では全商品を同じ重さで数えてしまうからです。次の2商品で比べます（説明用の架空の例です）。

原価率を平均すると（10%＋60%）÷2＝35%ですが、実際にかかった原価は15,000円、売上は100,000円なので、全体の原価率は15%です。たくさん売れているのは原価率の低い商品Aで、ほとんど売れていない商品Bの60%を同じ重さで数えたぶんだけ、平均は実際より20ポイント高く出ています。逆に、売れ筋の原価率が高い店では、平均のほうが低く出て、利益を甘く見積もることになります。

全体はSUMPRODUCT、区分別はSUMIFSで出します

G列に販売数を入れておけば、原価額や売上額の列を作らなくても、1つのセルで全体の原価率が出ます。SUMPRODUCTは、2つの範囲を行ごとに掛けてから合計する関数です。

全体の原価率
=SUMPRODUCT(B2:B100,G2:G100)/SUMPRODUCT(C2:C100,G2:G100)
  B列＝原価、C列＝売価、G列＝販売数

ランチとディナー、部品と完成品のように区分ごとの原価率を見たいときは、H列に原価額（=B2*G2）、I列に売上額（=C2*G2）、J列に区分を置き、SUMIFSで区分ごとに合計してから割ります。

区分別の原価率（L2に区分名を書いた場合）
=SUMIFS($H:$H,$J:$J,L2)/SUMIFS($I:$I,$J:$J,L2)
  H列＝原価額、I列＝売上額、J列＝区分

SUMIFSは条件に合う行だけを合計する関数です。全体の原価率を出すSUMPRODUCTの式も、区分別の原価率を出すSUMIFSの式も、原価の合計と売上の合計を別々に出してから最後に1回だけ割っています。原価率を行ごとに出してから平均すると、売れていない商品まで同じ重さで数えてしまいます。出典：Microsoft サポート「SUMPRODUCT 関数」／出典：Microsoft サポート「SUMIFS 関数」

目標を超えた商品は、条件付き書式で色を付けます

原価率のD列を選び、ホームタブの「条件付き書式」から「セルの強調表示ルール」「指定の値より大きい」の順に開きます。値の欄には目標原価率のセル（例：$N$1）を指定するか、30% または 0.3 と入力します。「30」と入れると30倍（3000%）より大きいセルだけが対象になり、どの行にも色が付きません。目標をセルで指定しておけば、目標を変えたときに色の付く行も一緒に変わります。

月次の原価率は「仕入÷売上」ではなく売上原価で出します

1か月の実際の原価率は、売上原価（期首在庫＋当月仕入−期末在庫）を当月の売上で割って出します。仕入れを売上で割るだけだと、在庫が増えた月は高く、減った月は低く出ます。商品ごとの売価と原価から計算した原価率とは別の数字なので、両方を並べて見ます。

月次の原価率
=(期首在庫+当月仕入-期末在庫)/当月売上
  例：(800,000+3,000,000-1,100,000)/9,000,000 → 30.0%
  同じ月を 仕入÷売上 で出すと 3,000,000/9,000,000 → 33.3%

月ごとの数字を比べるなら、月末に在庫を数えて売上原価を求めるのが前提です。

一覧表の原価率と月次の原価率の差をエクセルで並べて見る

一覧表に全商品が載り、販売数も実績どおりに入っている場合は、販売数で重みを付けた原価率と、売上原価から出した月次の原価率を比べられます。比較する期間、対象商品、税込・税抜の基準も揃えます。別の比較表で、B2にSUMPRODUCTで出した原価率、C2に月次の原価率を置くなら、D2の式は =C2-B2 です。条件を揃えても、実際の仕入れ単価や在庫評価などの違いで差が残ることがあります。

差を毎月同じ表に残せば、値上げや仕入れ先の見直しの前後で変化を確かめられます。ただし、差が縮んだことだけで原因が解消したとは判断せず、確認した記録と合わせて見ます。

原価の積み上げ自体を見直したい場合、製造業ならエクセルで見積書を自動計算する作り方の単価表がそのまま原価の一覧表の元になります。人の時間が主な原価になる受託型の仕事なら、商品単位ではなく案件単位で見るほうが合っているので、プロジェクト原価管理をエクセルで行う方法を参照してください。

来週は、売上上位10品だけで一覧表を作ります

最初の作業範囲は、先月の売上上位10品でも構いません。商品名・原価・売価・販売数の4列を税抜で書き、この記事の原価率の式とSUMPRODUCTの式を入れます。上位10品の売上が全体の何割を占めるかも、売上額の列を合計して確認できます。

原価率が目標を超えている商品が見つかったら、売価の逆算の列で「いくらに上げれば目標に戻るか」を出し、値上げ・原価の見直し・据え置きのどれにするかを商品ごとに決めます。商品や取引先が増えてファイルの更新が追いつかなくなってきたら、Excelからシステムへ移すタイミングも目安になります。