Excelで製造間接費差異を計算・グラフ化する方法｜シュラッター図とPower Queryで月次分析を効率化
製造間接費の予算差異や操業度差異について、計算方法は理解できても、実際の業務でExcelをどのように作ればよいか悩むことはありませんか？
例えば、次のようなケースです。
- 毎月、製造間接費の予算差異と操業度差異を計算している
- 計算結果だけでなく、シュラッター図を使って差異を説明したい
- Excelの計算表を作って、数値を変えたときの影響を確認したい
- 複数部門のデータを毎月手作業で集計している
- Power Queryを使って月次の差異分析を自動化したい
この記事では、固定製造間接費だけを対象とした簡易モデルを使い、Excelでの差異分析表の作成方法から、シュラッター図による可視化、Power Queryを利用した月次集計の効率化まで解説します。
練習用Excelファイルも用意していますので、実際に操作しながら確認してみてください。
なお、予算差異や操業度差異が発生する仕組みについて詳しく知りたい方は、関連記事「固定製造間接費差異とは？予算差異・操業度差異の計算方法」を先にご覧ください。
今回作成するExcel差異分析ツール
今回のExcelでは、次の4つの数値を入力すると、製造間接費差異を自動計算できるようにします。
| 入力項目 | 数値 |
|---|---|
| 固定製造間接費予算 | 3,000,000円 |
| 基準操業度 | 1,000時間 |
| 実際発生額 | 3,200,000円 |
| 実際生産量に対応する標準時間 | 800時間 |
これらのデータを入力すると、次の結果が得られます。
| 計算項目 | 結果 |
|---|---|
| 予定配賦率 | 3,000円／時間 |
| 予定配賦額 | 2,400,000円 |
| 予算差異 | 200,000円（不利） |
| 操業度差異 | 600,000円（不利） |
| 差異合計 | 800,000円（不利） |
さらに、これらの差異をシュラッター図で可視化します。
無料サンプルExcelをダウンロード
今回の操作を試せるように、サンプルExcelファイルを作成しました。
ファイル名：製造間接費差異分析_Excel_PowerQuery練習用.xlsx
Excelには、次のシートが入っています。
- 使い方：操作方法と注意点
- 差異分析：数値入力と差異計算
- シュラッター図：固定費の予算線・実際発生額・配賦額を可視化
- 予算データ：月別・部門別の予算と基準操業度
- 実績データ：月別・部門別の実際発生額
- 標準時間データ：月別・部門別の標準時間
まずはExcelの計算表を確認し、その後Power Queryを使って月別・部門別のデータを集計してみましょう。
手順① Excelで入力表と計算式を作る
最初に、ExcelのA列に項目名、B列に数値や計算式を入力します。
サンプルファイルでは、次の配置にしています。
この表では、プラスを不利差異、マイナスを有利差異として表示しています。
差額チェックのB12が0になれば、「予算差異＋操業度差異」と「実際発生額－予定配賦額」が一致していることを確認できます。
入力セルと計算セルを色分けする
実務で使用するExcelは、入力するセルと数式が入っているセルを区別しておくことをおすすめします。
今回のサンプルでは、B2～B5を薄い緑、B7～B12を薄い青にしています。
また、予算差異と操業度差異がプラスになった場合は、赤文字で表示するように設定すると、不利差異を確認しやすくなります。
手順② Excelでシュラッター図を作成する
続いて、製造間接費差異をシュラッター図で可視化します。
シュラッター図は、操業度と製造間接費の関係を表し、差異がどこで発生しているかを視覚的に確認するための図です。
一般的には固定費と変動費を含むモデルでも使用しますが、今回は固定製造間接費だけを対象とする簡易モデルです。
グラフ用のデータを作る
サンプルファイルの「シュラッター図」シートには、次のようなデータを用意しています。
| 操業時間 | 固定費予算 | 実際発生額 | 予定配賦額 |
|---|---|---|---|
| 0 | 3,000,000 | 3,200,000 | 0 |
| 800 | 3,000,000 | 3,200,000 | 2,400,000 |
| 1,000 | 3,000,000 | 3,200,000 | 3,000,000 |
固定費予算と実際発生額は、操業時間にかかわらず一定なので水平線になります。
一方、予定配賦額は「予定配賦率×操業時間」で計算するため、原点から右上がりの線になります。
Excelでは、このデータを選択して「挿入」からグラフを作成します。操業時間を数値軸として正確に表示したい場合は、散布図（直線）を使用する方法がおすすめです。
グラフで確認する2つの差異
操業時間800時間の位置に注目してください。
実際発生額320万円と固定費予算300万円の差が、予算差異20万円です。
固定費予算300万円と予定配賦額240万円の差が、操業度差異60万円です。
つまり、80万円の不利差異は次のように分解できます。
予算差異20万円＋操業度差異60万円＝差異合計80万円
今回の例では、実際作業時間と実際生産量に対応する標準時間がともに800時間であると仮定しており、能率差異は発生しません。
このようにグラフ化すると、費用の超過による差異と操業度の低下による差異を区別して説明しやすくなります。
手順③ Excelの数値を変更して差異の動きを確認する
計算表を作成したら、入力値を変えてみましょう。
例えば、実際発生額を320万円から300万円に変更すると、予算差異は0円になります。しかし、標準時間が800時間のままなら操業度差異60万円は残ります。
反対に、標準時間を800時間から1,000時間に変更すると、予定配賦額は300万円になり、操業度差異は0円になります。ただし、実際発生額が320万円のままなら予算差異20万円は残ります。
こうして入力値を変えてみると、予算差異と操業度差異が、それぞれ異なる要因で発生していることを理解できます。
手順④ Power Queryで月次データの集計を自動化する
ここまでは、1つの部門・1か月分の数値をExcelに直接入力しました。
しかし、実際の会社では、複数の部門・工場について毎月差異分析を行うことがあります。
その場合、Power Queryを利用すると、予算データ、実績データ、標準時間データをまとめて処理できます。
今回のサンプルファイルでは、次の3つのExcelテーブルを用意しています。
予算データ
年月・部門コード・固定費予算・基準操業度を管理します。
実績データ
年月・部門コード・実際発生額を管理します。
標準時間データ
年月・部門コード・実際生産量に対応する標準時間を管理します。
これらをPower Queryで結合すると、部門別・月別の製造間接費差異分析表を作成できます。
手順⑧ 分析ダッシュボードでシュラッター図を確認する
今回の練習用Excelには、計算結果を視覚的に確認するための「分析ダッシュボード」シートを用意しています。
シュラッター図を使い、固定製造間接費の予算差異と操業度差異を一目で確認できる仕組みです。
年月と部門をプルダウンで選択する
「分析ダッシュボード」シートを開き、次のセルから表示対象を選択してください。
- C4セル：対象月を選択
- F4セル：対象部門を選択
例えば、2026年1月・製造1課を選択すると、予算差異20万円、操業度差異60万円、差異合計80万円が表示されます。
対象月や部門を変更すると、それぞれのデータに応じてKPIやグラフが切り替わります。
シュラッター図に表示される3本の線
今回のダッシュボードでは、次の3種類の線を使用しています。
| 線の色 | 表示内容 | 特徴 |
|---|---|---|
| 緑 | 固定費予算 | 水平な直線 |
| 赤 | 実際発生額 | 水平な直線 |
| 青 | 予定配賦額 | 原点から右上がりの直線 |
グラフの横軸は操業度（時間）、縦軸は製造間接費（万円）です。
青い予定配賦額の線は、原点と基準操業度・固定費予算の座標を結ぶ直線です。
今回の2026年1月・製造1課では、原点（0時間・0万円）と、基準操業度（1,000時間・300万円）を結びます。
したがって、標準時間800時間の位置では、予定配賦額が240万円となります。
緑の固定費予算300万円と、赤の実際発生額320万円との差が予算差異20万円です。
また、標準時間800時間における緑の固定費予算300万円と、青の予定配賦額240万円との差が操業度差異60万円です。
なお、実際発生額は本来、実際の操業度と金額の組み合わせを示す点として表現できますが、今回のダッシュボードでは予算線との比較を容易にするため、赤色の水平線として表示しています。
手順⑨ KPIと分析コメントから差異の原因を考える
分析ダッシュボードには、シュラッター図に加えて、3つの差異を示すKPIが表示されます。
- 予算差異：実際発生額と固定費予算の差
- 操業度差異：固定費予算と予定配賦額の差
- 差異合計：予算差異と操業度差異の合計
また、選択した月・部門に応じて、差異の内訳や経営上の確認ポイントを説明するコメントも表示されます。
例えば、操業度差異の金額が予算差異より大きい場合は、受注数量・生産計画・設備負荷などを優先して確認する考え方が示されます。
反対に、予算差異の金額が大きい場合は、修繕費・外部委託費・間接労務費などの費目別分析を進めることが考えられます。
ただし、ダッシュボードのコメントは、差異の金額を基にした確認の目安です。実際の原因を自動的に特定するものではありません。
経営報告では、差異の金額だけでなく、「なぜ発生したのか」「今後どう対応するか」を説明することが重要です。
手順⑩ 翌月以降のデータを更新する
一度Power Queryを設定すれば、翌月以降も同じ処理を利用できます。
例えば、次のような流れです。
- 「予算データ」「実績データ」「標準時間データ」の各テーブルを更新する
- 「データ」→「すべて更新」をクリックする
- Power Queryの計算結果を確認する
- 差額チェックや未結合データの有無を確認する
- 必要に応じて「期待結果」シートへ計算結果を反映する
- 分析ダッシュボードで対象月・部門を切り替える
- 差異が大きい項目について原因を確認する
なお、今回の配布ファイルでは、Power Queryの結果と分析ダッシュボードの参照先が完全に自動連携しているわけではありません。
そのため、Power Queryを更新しただけではダッシュボードに最新結果が反映されない場合があります。
実務で継続利用する場合は、クエリの出力先とダッシュボードの参照先を統一し、更新処理を自動化すると、さらに便利になります。
まとめ：Power Queryとシュラッター図で原価差異分析を効率化する
今回は、Power QueryのMコードを使って、製造間接費差異を自動計算する方法を紹介しました。
3つのExcelテーブルを年月・部門コードで結合し、予定配賦率・予定配賦額・予算差異・操業度差異を計算する仕組みです。
さらに、分析ダッシュボードを使用することで、月別・部門別の差異をシュラッター図で視覚的に確認できます。
特に、経営報告では次の3点が重要です。
- 予算差異と操業度差異を分けて分析する
- グラフを使って差異の構造を視覚的に説明する
- 差異の発生原因を把握し、具体的な改善活動につなげる
Power Queryによる集計の自動化と、Excelによるデータ可視化を組み合わせれば、毎月の原価差異分析業務を効率化できます。
重要なのは、集計作業に時間を使うのではなく、差異の原因分析と改善策の検討に時間を使える仕組みを作ることです。
原価計算の知識とExcel・Power Queryを組み合わせて、より実務に役立つ管理資料を作成していきましょう。
