--- title: "エクセルで原価計算する方法｜原価率・売上原価の計算式と崩れない表の設計" url: "https://www.issoh.co.jp/column/details/11238/" published: 2026-03-06 updated: 2026-09-27 categories: ["会計"] publisher: "株式会社一創" --- # エクセルで原価計算する方法｜原価率・売上原価の計算式と崩れない表の設計 エクセルでの原価計算でつまずく箇所は、関数の知識ではなくほぼ2つに集中します。原価率と売上原価の計算式をセルにどう落とすか、そして単価マスタ・入力・集計をどう分けるかです。この2つを外すと、数式は動いているのに数字が合わない表になります。この記事では実際に入力するセル式と、原価計算基準が定める費目の切り方、運用中に表が崩れる原因の切り分け方を、手を動かす順に並べます。 ## まとめ 先に結論を置きます。本文では各項目をセル式と条文で裏づけます。 - **覚える式は3本**：製造原価＝材料費＋労務費＋経費、原価率＝売上原価÷売上高、売上原価＝期首棚卸高＋当期仕入高−期末棚卸高。 - **原価率のセル式に×100は入れない**。`=C2/B2` と入力して表示形式をパーセントにします。式にも100を掛けると100倍になります。 - **費目の切り方は原価計算基準八(一)の形態別分類に合わせる**。社会保険料の会社負担は労務費であって経費ではありません。 - **間接費は予定配賦率で配る**のが原価計算基準三三(二)の原則です。月末に実績総額を按分する運用は原則から外れます。 - **表が崩れる原因は参照ズレ・シート複製・保護の誤解・非原価項目の混入**の4つ。シート保護はMicrosoftが「セキュリティ機能ではない」と明記しており、改ざん防止には使えません。 - **XLOOKUPはExcel 2016と2019では使えません**。共有相手の環境が混在するならVLOOKUPかINDEX＋MATCHで組みます。 ## 原価率・売上原価・製造原価をエクセルで出す計算式 原価計算の土台は3本の式です。ここを正確に組めば、あとは集計の自動化しか残りません。 ### 原価率の計算式と表示形式・ゼロ除算の処理 原価率は売上原価を売上高で割った比率です。エクセルで最も多い間違いが、式に100を掛けたうえでセルの表示形式もパーセントにしてしまい、30%が3000%になるケースです。 ``` 原価率（%） = 売上原価 / 売上高 × 100 セルB2に売上高、C2に売上原価を入れた場合 D2: =C2/B2 ← 式に100は掛けない セルの表示形式を「パーセンテージ」に設定する 粗利率: =1-D2 ``` 売上が未計上の行では売上高が0になり `#DIV/0!` が出ます。エラーのまま放置すると、その列を参照する合計まで巻き込んでエラーになるため、割り算は例外処理ごと書きます。 ``` D2: =IFERROR(C2/B2,"") ← 売上0の行は空白 月次平均を出すときは行ごとの原価率を平均しない 正: =SUM(C2:C13)/SUM(B2:B13) 売上原価の合計 ÷ 売上高の合計 誤: =AVERAGE(D2:D13) 売上規模の違いが無視される ``` 月次の平均原価率を原価率列の単純平均で出す表は珍しくありませんが、売上100万円の月と1,000万円の月を同じ重みで扱うことになり、実態とずれます。合計同士で割ってください。 ### 売上原価の計算式と棚卸資産の3科目 売上原価は仕入額そのものではなく、期首在庫と期末在庫で調整した額です。財務諸表等規則75条も、売上原価を期首棚卸高、当期商品仕入高または当期製品製造原価、期末棚卸高の3科目に区分して掲記すると定めています。表の列もこの3科目に合わせておくと、決算数値との突合が楽になります。 ``` 売上原価 = 期首棚卸高 + 当期仕入高 − 期末棚卸高 B2:期首棚卸高 C2:当期仕入高 D2:期末棚卸高 E2: =B2+C2-D2 翌月の期首棚卸高は前月の期末棚卸高を参照する B3: =D2 ← 手入力すると必ずどこかでずれる ``` 翌月の期首棚卸高を手入力している表は、数か月後に前月末残高との不一致が出ます。前月の期末セルを参照する形にしておけば、この種のずれは発生しません。 ### 製造原価の3要素と原価計算基準八(一)の形態別分類 製造原価を材料費・労務費・経費に分ける根拠は、原価計算基準（企業会計審議会、昭和37年11月8日）八(一)の形態別分類です。基準は労務費に含まれるものを賃金、給料、雑給、従業員賞与手当、退職給与引当金繰入額、福利費（健康保険料負担金等）と例示しています。健康保険料の会社負担分は労務費であり、経費に落とすと労務費が過小になります。 経費は材料費・労務費以外の原価要素で、基準は減価償却費、たな卸減耗費および福利施設負担額、賃借料、修繕料、電力料、旅費交通費等の諸支払経費を挙げています。費目マスタを作るときは、この3分類を第1階層、基準の例示を第2階層にすると、集計軸が決算書の区分とそろいます。人件費をどこまで原価に入れるかで迷う場合は、[原価計算における人件費の扱い方｜原価に含める範囲・賃率の出し方・販管費との線引き](/column/details/11219/)に賃率の出し方まで整理しています。 ### 全部原価と直接原価の使い分け（基準四(三)・三〇） 同じ製品でも、集計する原価の範囲によって数字は変わります。原価計算基準四(三)は原価を全部原価と部分原価に区別し、「最も重要な部分原価は、変動直接費および変動間接費のみを集計した直接原価（変動原価）である」としています。基準三〇は総合原価計算において、変動直接費と変動間接費のみを集計し「固定費を製品に集計しないことができる」と認めたうえで、会計年度末には当該期間に発生した固定費額を期末の仕掛品および製品と当年度の売上品に配賦すると定めています。 実務上は、外部報告用の在庫評価は全部原価、値決めや受注可否の判断は直接原価と使い分けます。エクセルで両方を出すなら、費目マスタに変動費か固定費かのフラグ列を1つ足すだけで足ります。集計側でフラグを条件に加えれば、同じ入力データから2種類の原価が出ます。 ``` 直接原価（変動費のみ） =SUMIFS(原価入力!$F:$F, 原価入力!$B:$B,$A2, 原価入力!$E:$E,"変動") 全部原価（変動費＋固定費） =SUMIFS(原価入力!$F:$F, 原価入力!$B:$B,$A2) ``` ## マスタ・入力・集計の3層で組む原価計算表のシート設計 崩れない原価表は例外なくシートが分かれています。1枚のシートに単価表と実績入力と集計表を同居させると、行の挿入や並べ替えのたびに参照が壊れます。 ### 3層それぞれの列構成と業種別に変える項目 マスタシートは単価や配賦率など「めったに変わらない値」を1か所に集めます。入力シートは日付・コード・費目・数量・金額を1行1明細で積むだけにし、集計や計算式を置きません。集計シートは入力シートをSUMIFSで参照し、手入力のセルを持たせません。この分離ができていれば、入力シートは何行増えても壊れません。 3層の骨格は業種を問わず同じで、変わるのは集計キーとマスタの中身です。 | 業種 | 集計キー | マスタに持つ値 | 差異を見る単位 | | -------- | ------- | ---------- | -------- | | 製造業 | 製品コード | 部品単価・標準使用量 | 製品別・月別 | | 建設・工事業 | 工事番号 | 実行予算・外注単価 | 工事別・出来高 | | 飲食店・食品加工 | メニューコード | 仕入単価・歩留まり率 | メニュー別・日次 | | サービス・受託 | 案件番号 | 担当者別の時間単価 | 案件別・月別 | 人件費が原価の大半を占めるサービス業や受託開発では、時間単価に給与だけでなく会社負担の社会保険料まで含めないと案件粗利が実態より良く出ます。この構造の違いは[IT企業の原価計算｜売上原価の範囲・工数×賃率の手順](/column/details/11173/)で詳しく扱っています。 ### SUMIFSによる製品別・月別の集計式 集計の中心はSUMIFSです。参照範囲は `$F$2:$F$500` のような固定行ではなく列全体で指定します。行数を決め打ちすると、501行目の明細が静かに集計から漏れます。 ``` 集計シート A列に製品コード、1行目に月 B2: =SUMIFS(原価入力!$F:$F, 原価入力!$B:$B, $A2, 原価入力!$D:$D, B$1) 合計対象:金額列 条件1:製品コード 条件2:月 検算セルは集計範囲の外に置く（例: 集計範囲がB2:M50ならB52） B52: =SUM($B$2:$M$50)-SUM(原価入力!$F:$F) ← 0以外なら集計漏れか二重計上 ``` 集計結果の総和と入力シートの総和の差を出すセルを、必ず集計範囲の外側に置いてください。範囲の内側に置くと自分自身を参照して循環参照になります。0以外の値が出た時点で、条件のどれかが実データと一致していません。この1セルがあるかどうかで、異常に気づくまでの時間が変わります。 ### XLOOKUPとVLOOKUPの選択基準（Excel 2019以前の制約） 単価は入力シートに直接打たず、マスタから引きます。XLOOKUPは見つからない場合の戻り値を第4引数で指定でき、列番号ではなく範囲で指定するため列の挿入に強い関数です。ただしMicrosoftは公式ヘルプで「XLOOKUP は Excel 2016 および Excel 2019 では使用できません」と明記しています。対応するのはMicrosoft 365、Excel 2021、Excel 2024です。 ``` Microsoft 365 / Excel 2021以降 =XLOOKUP(B2, 商品マスタ!$A:$A, 商品マスタ!$C:$C, "未登録") Excel 2016 / 2019 と共有する場合 =IFERROR(VLOOKUP(B2, 商品マスタ!$A:$C, 3, FALSE), "未登録") 列の挿入に強くしたい場合 =IFERROR(INDEX(商品マスタ!$C:$C, MATCH(B2, 商品マスタ!$A:$A, 0)), "未登録") ``` 社外や他部署とファイルをやり取りする表では、XLOOKUPを避けるのが安全です。XLOOKUPを含むブックを古い環境で開くと数式が評価されず、原価が空欄や誤った値で表示されます。自分の画面で正しく見えていることは、相手の画面での正しさを保証しません。 ### 間接費の配賦率設定と基準三三(二)の予定配賦率 エクセルの原価表で最も雑になりやすいのが間接費です。月末に間接費の総額を製品ごとの基準値で按分する式をよく見かけますが、原価計算基準三三(二)は「間接費は、原則として予定配賦率をもって各指図書に配賦する」と定めています。基準三三(三)によれば予定配賦率は、一定期間における各部門の間接費予定額を同期間の予定配賦基準で除して算定し、三三(五)はその予定操業度を原則として一年または一会計期間において予期される操業度としています。 ``` 予定配賦率 = 間接費予定額 / 予定配賦基準（予定直接作業時間など） （例）年間間接費予定額 2,400万円 / 予定直接作業時間 20,000時間 = 1,200円/時間 マスタシート H2 に 1200 を置く 配賦額: =E2*$H$2 E2は当月の実際直接作業時間 配賦差異: =実際発生額 - SUM(配賦額列) ``` 予定配賦率を使う利点は、月次で配賦差異が可視化されることです。実績を按分する方式では総額が必ず一致するため、間接費が予算からどれだけ離れたかが表に出てきません。差異が出る形にしておくと、原価表が集計表から管理資料に変わります。 ## 原価管理表が崩れる4つの原因と検証手順 数か月運用した原価表が合わなくなる原因は、ほぼ次の4つのどれかです。原因ごとに確認場所が違うので、順に切り分けます。 ### 行挿入による参照ズレと循環参照の特定手順 最も多いのが範囲指定のずれです。`=SUM(B2:B100)` の下、101行目に明細を足しても合計は変わりません。範囲末尾への行追加は範囲を広げないためです。入力範囲をテーブル（挿入→テーブル、Ctrl＋T）に変換すると、追加行が自動で範囲に入ります。 循環参照は、合計セルを自分自身の範囲に含めたときに起きます。ステータスバーに「循環参照」とセル番地が表示され、数式タブのエラーチェックから循環参照の一覧をたどれます。反復計算の設定を有効にして警告を消す対処は、原価計算では使わないでください。警告が消えるだけで、参照の誤りは残ります。 ### シート複製によるバージョン分裂と一元化 月次でシートやファイルを複製する運用は、原価表を壊す代表的なパターンです。複製した瞬間に単価マスタも複製されるため、翌月に仕入単価を直しても過去シートには反映されず、どのファイルが正しいのか誰も判断できなくなります。 対処は、月ごとにシートを増やすのをやめ、入力シートに年月列を持たせて1つのテーブルに積み上げることです。月次の見え方はピボットテーブルの行と列で作れます。ファイルは1つ、原本は1か所という状態を保てば、単価の更新は常に1か所で済みます。 ### シート保護の限界と共同編集での破損防止 数式セルの破壊を防ぐ手段としてシート保護が使われますが、Microsoftは公式ヘルプで「ワークシートレベルの保護はセキュリティ機能を意図したものではありません」と明記しています。保護できるのは誤操作までで、ブックの保護やファイルのパスワード暗号化とは別の機能です。 実務的な設定順は、まず入力させたいセルだけロックを解除し、そのうえでシート全体を保護します。既定では全セルがロック状態なので、順番を逆にすると誰も入力できない表になります。共同編集では、同じセルへの同時入力より、範囲を選んだまま貼り付けて数式列を上書きする事故のほうが多いため、数式列は保護の対象に含めてください。 ### 非原価項目の混入と費目マスタでの遮断（基準五） エクセルは何でも足せてしまうため、原価に入れてはいけない費用が紛れ込みます。原価計算基準五は非原価項目を列挙しており、支払利息や社債発行費償却などの財務費用、火災・震災・盗難といった偶発的事故による損失、臨時多額の退職手当、法人税や住民税、配当金、役員賞与金が含まれます。 これらが混入すると、その月だけ原価率が跳ね上がり、原因を追う時間が発生します。防ぐには費目マスタに「原価・非原価」の区分列を持たせ、入力シートの費目をデータの入力規則でマスタからの選択に限定します。入力時点で選べないようにするのが、集計後に探すより確実です。 ## 飲食店・食品加工業の原価率を左右する歩留まりと正味単価 飲食店と食品加工業で原価率が計算と合わない最大の要因は歩留まりです。歩留まりは仕入れた食材のうち実際に商品になる割合で、皮むきや骨抜き、下ろしで生じる廃棄を反映しないと、食材原価を必ず過小に見積もります。 そこで使うのが正味単価です。正味単価は、実際に使える量あたりの単価を意味します。 ``` 正味単価 = 仕入単価 / 歩留まり率 （例）仕入単価200円/kg 、歩留まり0.8 → 250円/kg 食材マスタ: A列 食材コード / B列 仕入単価 / C列 歩留まり率 D2: =B2/C2 ← 正味単価 レシピシート: =使用量 * 正味単価 ← 1品の食材原価 メニュー原価率: =IFERROR(食材原価計/売価,"") ``` レシピシートで使う単価は、必ず食材マスタの正味単価を参照させてください。仕入単価が動いたときにマスタの1か所を直せば全メニューの原価率が更新され、値上げ交渉や売価改定の判断に当日中に使えます。レシピ側に単価を直接打ち込んだ表では、単価改定のたびに全メニューを開くことになり、更新が止まった時点から原価率は実態から離れていきます。 ## エクセルの限界と原価管理システムへの移行ライン エクセルの原価計算は、規模の問題ではなく更新頻度と関係者数の問題で限界に達します。 ### 仕様上限より先に壊れる運用上の限界値 Microsoftが公開しているワークシートの上限は1,048,576行×16,384列、1セルに入る文字数は32,767文字です。この行数に到達する前に、実務のほうが先に破綻します。数万行のSUMIFSを何十列も並べれば再計算に時間がかかり、入力のたびに待ち時間が発生します。 限界の判断材料になるのは、行数よりも次の状況です。原価表を触る人が3人を超えて同時更新の調整が必要になったとき、単価改定が月に何度も入りマスタ更新が追いつかなくなったとき、実績の入力から原価が見えるまでに数日かかるようになったときです。いずれもファイル形式では解けない問題で、数式の書き方を工夫しても改善しません。 ### 移行を判断する3つの状況 移行を決めるべきなのは、まず原価の確定が締め後になり、値決めや採算判断に間に合わなくなった場合です。次に、担当者1人しか数式を理解できず、その人が不在の月に原価が出せない場合。3つ目は、販売管理や会計とのデータ突合に毎月まとまった時間を取られている場合です。この3つはいずれもエクセルの機能不足ではなく、単一ファイルで複数人・複数業務をさばく構造そのものに原因があります。 逆に、品目数が限られ、更新するのが1人か2人で、月次で締まれば足りる規模なら、エクセルのまま精度を上げるほうが費用対効果は高くなります。判断に入る段階では[原価管理システムの基本機能と導入前に押さえるべき業務課題の全体像](/column/details/11130/)で必要な機能を整理し、製品を比べる段階では[原価管理システムの比較で見る6つの軸｜タイプ別選定基準](/column/details/16427/)が実務の順序に合います。 ## よくある質問 ### エクセルでの原価率の計算式は？ 式は `=C2/B2`（C2が売上原価、B2が売上高）で、表示形式をパーセンテージにします。間違いが起きるのは分子です。ここに入るのは当期仕入高ではなく、期首棚卸高と期末棚卸高で調整した売上原価です。在庫を多めに仕入れた月に仕入高をそのまま分子へ置くと、原価率だけが跳ね上がって見えます。分子の作り方は本文の売上原価の章を参照してください。 ### 原価計算のエクセル無料テンプレートはありますか？ Microsoftのテンプレートサイトや会計ソフト各社が配布していますが、自社の費目区分と集計キーに合わないまま使うと、後から列を足す作業のほうが重くなります。品目数が少ないうちは、マスタ・入力・集計の3シートを自分で作るほうが早く、崩れたときの原因も特定できます。テンプレートを使う場合は、単価がマスタ参照になっているか、集計が固定行の範囲指定になっていないかの2点を先に確認してください。 ### 正味単価とは何ですか？ 実際に商品になる量あたりの単価で、仕入単価を歩留まり率で割って求めます。歩留まり率そのものは、下処理後の重量を仕入時の重量で割って実測します（廃棄率が2割なら歩留まり率は0.8）。同じ食材でも産地や時期、下処理する担当者で変わるため、マスタには実測値を入れ、季節をまたぐ食材は年に一度計り直してください。仕入単価のまま計算すると、原価率は実態より低く出ます。 ### 個別原価計算をエクセルで行うことはできますか？ できます。入力シートに指図書番号（案件番号）の列を持たせ、SUMIFSの条件をその番号にすれば案件別に集計されます。注意点は間接費で、原価計算基準三三(二)が予定配賦率による配賦を原則としているため、月末に総額を按分する式にしないことです。適用する生産形態や指図書の考え方は[受注生産に携わる担当者が最初に理解する個別原価計算の定義と基本概念](/column/details/11220/)にまとめています。 ### 工事の原価計算表をエクセルで作るときの注意点は？ 工事番号を集計キーにしたうえで、見積金額・実行予算・実際原価・差異を横並びに置く構成にします。最も多い失敗は、見積時の項目区分と実際原価の費目区分がそろっておらず、差異が計算できない状態です。項目定義はマスタで固定し、工期をまたぐ案件は出来高に応じて月次で費用を配分してください。 ## 関連記事 - [経理・管理部門が押さえるべきエクセル原価計算の基本構造と費目分類](/column/details/11195/) - [原価計算における人件費の扱い方｜原価に含める範囲・賃率の出し方・販管費との線引き](/column/details/11219/) - [受注生産に携わる担当者が最初に理解する個別原価計算の定義と基本概念](/column/details/11220/) - [IT企業の原価計算｜売上原価の範囲・工数×賃率の手順・原価率の目安](/column/details/11173/) - [原価管理システムの比較で見る6つの軸｜タイプ別選定基準とランキングの読み方](/column/details/16427/) --- 出典: [エクセルで原価計算する方法｜原価率・売上原価の計算式と崩れない表の設計]()（株式会社一創）