Excelで差異分析を行う方法:ステップ・バイ・ステップ

Excelで差異分析を行うには、各項目について予算と実績の数値を並べて配置します。実績から予算を差し引いて金額ベースの差異を算出します。その差異を予算で割ることで、パーセンテージを算出します。次に、各項目に「有利」または「不利」のフラグを立て、大きな乖離の原因を説明します。
このガイドでは、数式、ワークシートのレイアウト、および具体的な計算例を詳しく解説します。また、結果をグラフ化する方法や、収益の乖離を価格と数量に分解する方法についても説明します。Excelの手順は、2026年11月10日に確認したMicrosoftのサポートページに基づいています。
差異分析が示すもの
差異とは、計画したものと実際に起こったことの間の乖離です。差異分析では、それらの乖離を項目ごとにリストアップし、重要なものについて説明します。
ほとんどのチームは、実績を以下の3つの基準のいずれかと比較します:
- 予算。 年度の開始時に設定された計画。
- 予測。 年度中に設定された更新済みの計画。
- 前年同期。 先月、前四半期、または前年の同時期。
このタスク向けのMicrosoft独自のCopilotスキルは、その役割をうまく説明しています。その差異分析スキルは「実績を予算、予測、または前年同期と比較し、価格、数量、構成、レート、またはタイミングによる違いを説明します」としています。
出力は通常、表といくつかの記述メモで構成されます。表はすべての乖離を示し、メモはリーダーが対応すべき3つか4つの重要な項目について説明します。
基本の数式
主に3つの数式を使用します。予算がB列、実績がC列にあると仮定します。
| 指標 | 数式 | わかること |
|---|---|---|
| 金額差異 | =C2-B2 | 実績が予算から通貨単位でどれだけ離れているか |
| パーセンテージ差異 | =IFERROR((C2-B2)/ABS(B2),"") | 計画に対して乖離がどれほど大きいか |
| ステータス | =IF(D2=0,"計画通り",IF(D2>0,"計画を上回る","計画を下回る")) | 項目がどちらの方向に動いたか |
ABS関数は、予算がマイナスの場合でも符号を正しく保ちます。IFERRORは、予算がゼロの場合にエラーを表示する代わりにセルを空白にします。
方向は、必ずしも「良し悪し」と同じではありません。収益が計画を上回るのは良いニュースですが、コストが計画を上回るのは悪いニュースです。次のセクションでは、その処理方法を説明します。
ワークシートのセットアップ方法
整理されたレイアウトにすることで、数式のコピーが容易になります。1項目につき1行を使用し、以下の列を作成します:
- 項目。 収益、売上原価、給与、マーケティングなど。
- タイプ。 Excelがどちらの方向が良いかを判断できるように「収益」または「コスト」と入力します。
- 予算。 その期間の計画額。
- 実績。 会計データからエクスポートした金額。
- 差異。 実績マイナス予算。
- 差異 %。 差異を予算で割ったもの。
- 有利または不利。 タイプと方向に基づいたラベル。
- 説明。 重要な項目についての短いメモ。
予算と実績は、同じ期間、同じ勘定科目マッピングで管理してください。差異分析の精度は、この一致度合いに左右されます。予算で給与が2つの勘定科目に分かれている場合は、実績でも同様に分けてください。
Ctrl+Tで表をExcelテーブルとして書式設定します。これにより、新しい行を追加した際に数式が自動的に適用されます。
Excelで差異を計算する方法
レイアウトが整えば、手順は簡単です。上記の列が2行目から始まり、タイプがB列、予算がC列、実績がD列にあると仮定します。
- E2に、金額差異を算出する =D2-C2 を入力します。
- F2に、=IFERROR((D2-C2)/ABS(C2),"") を入力し、パーセンテージ形式に設定します。
- ステータス列に、以下に示す有利・不利の判定式を入力します。
- 数式をすべての行にコピーします。
- 合計行を追加し、パーセンテージを合算するのではなく、合計値から差異を計算します。
ステータス列の2行目に使用する判定式は以下の通りです:
=IF(E2=0,"計画通り",IF(B2="収益",IF(E2>0,"有利","不利"),IF(E2<0,"有利","不利")))
この式はまずタイプ列を読み取ります。収益の場合、プラスの差異は「有利」です。コストの場合、計画より支出が少なかったことを意味するマイナスの差異が「有利」となります。
合計行のパーセンテージには注意が必要です。各行のパーセンテージを合計すると誤った答えになります。代わりに、合計差異を合計予算で割ってください。
重要な差異を特定する方法
すべての乖離にメモが必要なわけではありません。多くのチームは、「5%以上かつ2,000ドル以上」といった重要性のルールを設定しています。両方のテストをクリアした行にのみ説明を加えます。
シンプルな数式でこれらをマークできます。「重要」列を追加し、2行目に =AND(ABS(F2)>0.05,ABS(E2)>2000) と入力します。重要な行にはTRUEと表示されます。
色を使うと、表を素早くスキャンできるようになります。Excelの条件付き書式でアイコンセットを追加できます。Microsoftによれば、アイコンセットは「しきい値によって区切られた3〜5つのカテゴリにデータを注釈付けし、分類する」ことができます。
追加するには、差異 % 列を選択します。次に、ホーム > 条件付き書式 > アイコンセット に進みます。Microsoftは、3つの矢印セットにおいて「緑の上向き矢印はより高い値を表す」と述べています。コストの行については、上昇が警告として表示されるよう、アイコンの順序を逆にしてください。
要因を説明する方法
数字はどこに乖離があるかを示します。要因(ドライバー)はなぜそうなったかを説明します。Microsoftのスキル説明では、5つの一般的な要因として、価格、数量、構成、レート、タイミングを挙げています。
価格。 計画とは異なる価格で販売または購入した。価格差異 = (実績価格 − 予算価格) × 実績数量。
数量。 異なる数量を販売または使用した。数量差異 = (実績数量 − 予算数量) × 予算価格。
構成(ミックス)。 製品、地域、または顧客のシェアが変化した。数量が維持されていても、利益率の低いアイテムへのシフトは利益を下げます。
レート。 時給やサプライヤー手数料などのコスト単価が変化した。
タイミング。 期間をまたいで資金が移動した。3月の請求書が4月に支払われた場合、両方の月で乖離として現れます。
価格と数量を合わせることで、収益の乖離全体を説明できます。計算の正しさを確認できるため、このチェックは毎回行う価値があります。
差異分析の例
架空の会社「Cedar and Pine Coffee」の計算例を紹介します。財務担当者が第3四半期の実績を予算と比較します。
| 項目 | タイプ | 予算 | 実績 | 差異 | 差異 % | ステータス |
|---|---|---|---|---|---|---|
| 収益 | 収益 | $250,000 | $236,500 | −$13,500 | −5.4% | 不利 |
| 売上原価 | コスト | $100,000 | $97,200 | −$2,800 | −2.8% | 有利 |
| 給与 | コスト | $60,000 | $64,800 | $4,800 | 8.0% | 不利 |
| マーケティング | コスト | $20,000 | $26,000 | $6,000 | 30.0% | 不利 |
| 賃料 | コスト | $15,000 | $15,000 | $0 | 0.0% | 計画通り |
| 営業利益 | $55,000 | $33,500 | −$21,500 | −39.1% | 不利 |
合計の整合性が取れています。収益で13,500ドルのマイナス、売上原価で2,800ドルの節約、給与で4,800ドルの超過、マーケティングで6,000ドルの超過が発生しました。これらを合わせると−21,500ドルとなり、利益の乖離と一致します。
「5%以上かつ2,000ドル以上」という重要性のルールに該当するのは、収益、給与、マーケティングの3項目です。売上原価の変動は2.8%に留まったため、メモは不要です。
収益の乖離を詳しく見る必要があります。予算では50,000ユニットを5.00ドルで販売すると想定していました。実際には43,000ユニットを5.50ドルで販売しました。
- 数量差異。 (43,000 − 50,000) × $5.00 = −$35,000。
- 価格差異。 ($5.50 − $5.00) × 43,000 = +$21,500。
- 合計。 −$35,000 + $21,500 = −$13,500。これは収益項目の差異と一致します。
つまり、値上げは維持できましたが、その分数量が減少したことになります。リーダーへのメモにはまさにその通りに記載します。マーケティング費用は計画より30%多く支出したにもかかわらず、販売数量は減少しました。これは、どのキャンペーンが効果的だったのかという疑問を投げかけます。
給与についても個別のチェックが必要です。4,800ドルの乖離は、賃金上昇などのレート要因か、残業などの数量要因の可能性があります。財務担当者が運用マネージャーに確認したところ、2つの店舗で週末シフトを追加したことがわかりました。これにより、昇給ではなく営業時間に紐づく数量の乖離であることが判明しました。
リーダー向けの最終的な要約は1行にまとめられます。「営業利益は予算を21,500ドル下回りました。値上げによる数量減が利益増を上回り、マーケティング費用と週末シフトの追加が計画を超過したことが主な要因です。」
差異メモの書き方
表は差異分析の半分に過ぎません。リーダーはメモを読むため、各メモは「何が動いたか」「なぜ動いたか」「次に何が起こるか」の3つの問いに答える必要があります。
良いメモはシンプルなパターンに従います:
- 何が。 「マーケティング費用が予算を6,000ドル超過し、計画を30.0%上回りました。」
- なぜ。 「2つの有料キャンペーンが予定より長く実施されました。」
- 次に。 「10月3日に1つのキャンペーンを停止しており、第4四半期は計画通りに着地する見込みです。」
各メモは2〜3文に留めてください。読者が数字と照らし合わせやすいよう、表と同じ項目の順序で記載します。
一回限りの乖離とトレンドを区別してください。一度限りの修理費用は、毎月発生する賃金上昇とは異なります。継続的な乖離は予測に影響するため、明確にマークしてください。
メモは各項目の責任者と一緒に作成してください。財務チームは数字を把握していますが、予算の責任者はその理由を知っています。各責任者への短い確認を行うことで、推測が確かな説明に変わります。
最後に、一番上に1行の要約を追加します。上記の例であれば、「第3四半期の営業利益は、主に数量の減少とマーケティング支出の増加により、予算を21,500ドル下回りました」となります。
タイミングは言葉選びと同じくらい重要です。ほとんどのチームは、帳簿が閉まった後、毎月差異分析を行います。毎月のリズムを作ることで、まだ対策を講じる時間があるうちに問題を把握できます。
四半期ごとのレビューはより深く行います。価格と数量の分解、再予測、そして予算自体がまだ妥当かどうかの見直しを追加します。年度末のレビューでは、当初の計画に対する通年の結果を振り返ります。
毎回同じテンプレートを使用してください。列やルールを固定することで、毎月の差異分析にかかる時間が短縮され、期間をまたいだトレンドが見えやすくなります。
差異をグラフ化する方法
グラフは、リーダーが予算から実績に至るまでの道のりを理解するのに役立ちます。最も適しているのはウォーターフォール図(滝グラフ)です。
Microsoftは、ウォーターフォール図は「値が加算または減算される際の累計を表示する」と述べています。また、「ウォーターフォール図はブリッジチャートとも呼ばれる」と付け加えています。
作成するには、予算利益から実績利益までのステップをリストアップします。55,000ドルから始め、各項目が利益に与えた影響を加算していき、33,500ドルで終わります。データを選択し、挿入 > ウォーターフォール図の挿入 > ウォーターフォール を選択します。
最初と最後のバーを合計として設定します。Microsoftのページでは、開始と終了の列は「多くの場合、水平軸から始まる」のに対し、中間のバーは浮いていると説明されています。増加と減少は色分けされるため、最大の要因が際立ちます。
月ごとの追跡には、差異 % のシンプルな縦棒グラフも有効です。この表を中心とした月次レポート全体については、予算対実績レポートの作成方法に関するガイドで解説しています。
AIで差異分析を行う
表さえあれば、数式の入力は数分で終わります。時間がかかるのは、勘定科目のマッピング、要因の特定、そしてメモの作成です。AIツールを使えば、エクスポートしたデータからこれらのステップをドラフトできます。
Powerdrill Bloomを使用した3つのステップによる方法は以下の通りです。
ステップ 1:予算と実績をアップロードする
会計ツールや計画ツールから予算と実績をエクスポートします。AI予算分析ツールは、「会計やFP&AからのExcel、CSV、またはPDFのエクスポート」を受け付けます。
価格と数量の分解を行いたい場合は、数量と単価のデータも含めてください。両方のファイルで勘定科目名を統一しておきます。
ステップ 2:自然言語で差異と要因を尋ねる
必要な出力を説明します。例:「第3四半期の実績を項目ごとに予算と比較してください。5%かつ2,000ドルを超える項目にフラグを立て、収益の乖離を価格と数量に分解してください。」
ツールページには、表示形式の一つとして「重要性のしきい値を適用した項目別の差異」が挙げられています。また、「当初予算、年初来実績、修正後の年度末予測」を含む再予測ワークブックも作成可能です。
ステップ 3:数値を確認しレポートをエクスポートする
共有する前に、合計値を自分のシートと比較してください。その後、結果をエクスポートします。予算分析ページには、「重要な項目がハイライトされ、要因が添付された差異ダッシュボード」がリストされています。
継続的なモニタリングには、AI財務ダッシュボードビルダーがP&L、キャッシュ、運転資本、予測をカバーしています。前四半期の予算と実績を使って、Powerdrill Bloomを試してみてください。
避けるべき一般的な間違い
初めての差異分析では、ほぼ必ずと言っていいほどいくつかのエラーが発生します。
- 符号の混同。 差異を「実績マイナス予算」にするか一度決めたら、すべての場所で統一してください。
- すべてのプラスの乖離を「良い」と呼ぶ。 数値がプラスであっても、コストの増加は「不利」です。
- パーセンテージを合算する。 合計のパーセンテージは、合計金額から再計算してください。
- 異なる期間を比較する。 年初来の実績を月次予算と比較すると、誤った乖離が生じます。
- タイミングを無視する。 請求書の遅れにより、2ヶ月間で逆方向の2つの乖離が生じることがあります。
- すべてを説明しようとする。 小さな乖離に関するメモは、重要なものを隠してしまいます。
- 数値を手入力する。 入力ミスによる誤った乖離を防ぐため、実績データはエクスポートから貼り付けるかリンクさせてください。
- 合計チェックをスキップする。 各項目の差異の合計は、全体の差異と一致するはずです。一致しない場合は、行が欠落しているか、二重にマッピングされています。
よくある質問
Excelでの差異の数式は何ですか?
予算差異には、=C2-B2 のように「実績マイナス予算」を使用します。パーセンテージには =IFERROR((C2-B2)/ABS(B2),"") を使用し、セルの書式をパーセンテージに設定します。
差異が「有利」かどうかはどうすればわかりますか?
項目のタイプによります。収益の場合、実績が予算を上回れば「有利」です。コストの場合、実績が予算を下回れば「有利」です。タイプ列とIF関数を使えば、各行にラベルを付けることができます。
「重要な差異(マテリアル・バリアンス)」とは何ですか?
説明が必要なほど大きな乖離のことです。チームごとに「5%以上かつ2,000ドル以上」といった独自のルールを設定します。両方のテストをクリアした行には記述メモを添えます。
差異分析に最適なグラフは何ですか?
ウォーターフォール図(ブリッジチャートとも呼ばれる)は、各項目が予算から実績へと合計をどのように動かしたかを示します。Excelでは 挿入 > ウォーターフォール図の挿入 にあります。
ExcelのCopilotで差異分析はできますか?
Microsoftは、ExcelのCopilot向けに差異分析スキルをリストアップしています。実績を予算、予測、または前年同期と比較し、レビュー用の説明文をドラフトします。これにはCopilotライセンスが必要です。
出典: Microsoft Support, Use built-in skills with Copilot in Excel · Microsoft Support, Create a waterfall chart · Microsoft Support, Use conditional formatting to highlight information in Excel.