部門別損益管理をExcelで始める方法【共通費配賦のルール設計】

「会社全体では黒字。でも、店舗ごと・事業ごとに見たらどうなのか」——この問いに答えられない会社は、実は儲かっている部門が赤字部門を静かに支え続けています。部門別損益管理を始めるハードルは会計ソフトの部門設定ではなく、共通費をどう配るかのルール設計にあります。

この記事では、部門区分と配賦基準の設計の考え方を整理し、試算表データから部門別PLを自動集計するExcelの作り方を解説します。月次損益管理の発展版です。（関連記事：No.17 月次損益管理の作り方）

設計の考え方

部門区分は「責任者がいる単位」で切る

店舗別・事業別・チーム別——区分の正解は組織によりますが、原則は数字に責任を持つ人がいる単位で切ることです。誰も責任を持たない区分の損益は、出しても行動につながりません。最初は3〜5部門の粗い区分で始め、運用が回ってから細分化するのが現実的です。

直課と配賦を区別する

• 直課：部門に直接紐づく収益・費用（部門の売上、専属人員の人件費、店舗家賃など）→そのまま部門に載せる
• 配賦：本社家賃・管理部門人件費・システム利用料など共通費→基準を決めて按分する

配賦基準の定番と使い分け

共通費の性質	適した配賦基準	例
人に比例するもの	人数比・工数比	総務人件費、福利厚生費、システム利用料
場所に比例するもの	面積比	本社家賃、水道光熱費
事業規模に比例するもの	売上高比・粗利比	広告宣伝費（全社分）、本社管理費の残り

基準に唯一の正解はありません。重要なのは一度決めたら毎月同じ基準で続けることと、基準をシートに明文化しておくことです。

Excelでの実装手順

ステップ1：データ構造（仕訳・試算表からの取込み）

会計ソフトに部門コードを入れている場合は部門別試算表をCSVで貼り付けるだけです。部門設定がない場合は、勘定科目×補助科目（または摘要のルール）から「科目・金額・部門・月」の明細を作り、SUMIFSで集計します。

部門別・科目別の集計：
=SUMIFS(明細!金額,明細!部門,B$1,明細!科目区分,$A2,明細!月,対象月)

ステップ2：配賦テーブルで共通費を按分する

配賦テーブル：行=共通費科目、列=部門、値=配賦率
（例：本社家賃→面積比 40%/35%/25%、総務人件費→人数比 50%/30%/20%）
配賦率の検算：=IF(SUM(行の配賦率)<>1,"合計が100%になっていません","OK")
部門への配賦額：=共通費実額*配賦テーブルの該当率

配賦率を数式に埋め込まずテーブルとして独立させるのがポイントです。人員異動や増床のたびにテーブルだけ更新すれば、全月の計算に一貫して反映されます。

ステップ3：貢献利益で見せる

部門別PLは「売上−直課費用＝貢献利益」と「貢献利益−共通費配賦＝部門営業利益」の2段構えにします。配賦後の営業利益が赤字でも、貢献利益が黒字なら「その部門を閉じると共通費の行き場がなくなり全社利益はむしろ悪化する」——この構造が見える形式こそが、部門別損益の本当の価値です。

数値例：閉店判断を誤りかけたケース

項目	A店	B店	C店
売上	3,000万円	2,000万円	1,200万円
直課費用	2,400万円	1,700万円	1,100万円
貢献利益	600万円	300万円	100万円
共通費配賦（売上比）	290万円	190万円	120万円
部門営業利益	310万円	110万円	△20万円

C店は配賦後△20万円の赤字ですが、閉店しても本社共通費600万円は消えません。閉店するとC店が負担していた120万円がA店・B店に乗り、全社利益は貢献利益100万円分だけ悪化します。「配賦後赤字＝即閉店」ではない——この判断を可能にするのが2段構えのPLです。

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

• 最初から精密な配賦を目指さない：配賦の精緻化に時間をかけるより、直課の精度（部門コードの付け漏れ防止）を上げるほうが効果的です。共通費は多少粗くても毎月続くことが優先です。
• 兼務者の人件費は工数のざっくり按分でよい：50%/30%/20%のような自己申告ベースで十分です。ただし比率の見直しは期首など決まったタイミングに限定し、恣意的な変更を避けます。
• 部門間取引（社内売上）のルールを決める：製造→販売のような内部取引がある場合、社内単価と相殺のルールを先に決めないと全社合計が合わなくなります。
• 評価とセットにするなら配賦は慎重に：部門長の評価に使う場合、配賦費用は「管理不能費用」として評価対象から外すのが定石です。貢献利益ベースの評価が納得感を生みます。
• 全社計との突合を毎月行う：部門合計＝全社試算表と一致することを検算列で確認します。差異が出たら部門コードの付け漏れが第一容疑です。（関連記事：No.32 月次監査チェックリスト）

よくある質問

Q. 会計ソフトの部門機能とExcel、どちらでやるべきですか？

入力時の部門付与は会計ソフトで行い、配賦と見せ方（貢献利益の2段構え・前年比較・グラフ）はExcelで行う役割分担が現実的です。ソフトの配賦機能は柔軟性に欠けることが多く、配賦テーブル方式のExcelのほうが説明資料として使いやすくなります。

Q. 部門数が多くて集計が重くなってきたら？

SUMIFSの参照範囲を列全体ではなくテーブル（構造化参照）に変えるだけで大きく改善します。それでも重い場合はピボットテーブルへの移行を検討してください。（関連記事：No.38 ピボットテーブルの経理活用）

部門別損益テンプレートのご案内

部門別試算表の貼り付け→配賦テーブル→貢献利益2段構えの部門別PL・前年同月比較まで自動化した部門別損益Excelテンプレートをご用意しています。配賦基準の設計メモ欄付きで、ルールの属人化を防ぎます。

まとめ

• 部門区分は「数字に責任を持つ人がいる単位」で3〜5部門から始める
• 直課を最優先で正確に。共通費は配賦テーブル（人数比・面積比・売上比）で按分する
• 「貢献利益→配賦後利益」の2段構えが撤退判断の誤りを防ぐ
• 配賦基準は明文化して毎月同じルールで継続。変更は期首に限定する
• 部門合計と全社試算表の一致を毎月検算する