毎月の予実差異分析で、SAPなど基幹システムからCSVを出力し、Excelにコピー＆ペーストしていませんか？

予算データと実績データを突き合わせ、売上差異を数量・単価・為替の要因に分解する作業は、製品数が多くなるほど時間がかかります。

こうした定型作業の効率化に役立つのが、ExcelのPower Query（パワークエリ）です。

Power Queryを使えば、CSVファイルの取り込み、データの結合、差異計算を一連の処理として登録できます。初回設定後は、同じ形式のCSVを差し替え、Excelの「すべて更新」で再集計できます。

この記事では、無料配布するサンプルファイルを使い、3製品の予算・実績・為替データから予実差異分析表を作成する方法を解説します。

Contents
  1. Power Queryで予実差異分析を自動化すると何が変わる？
  2. 2. サンプルファイルをダウンロード
  3. 3. 今回使用する3製品のサンプルデータ
    1. 予算データ（budget.csv）
    2. 実績データ（actual.csv）
    3. 為替データ（fx.csv）
  4. 4. Power QueryをExcelに登録する手順
    1. 手順① ZIPファイルを展開する
    2. 手順② Excelで空のクエリを作成する
    3. 手順③ Mコードを貼り付ける
    4. 手順④ CSVフォルダの保存先を変更する
    5. 手順⑤ クエリをExcelに読み込む
  5. 5. Power Queryで実行している処理の仕組み
    1. ① 3種類のCSVを読み込む
    2. ② 製品コードで予算と実績を結合する
    3. ③ 為替レートを結合する
    4. ④ 円換算売上高を計算する
    5. ⑤ 数量・単価・為替差異を計算する
  6. 6. Power Queryの計算結果を確認する
  7. 7. 翌月からの更新方法
  8. 8. Power Queryで予実差異分析を行う際の注意点
    1. ① 新製品や販売終了品が結果から除外される
    2. ② 製品コードの重複に注意する
    3. ③ CSVの列名を変更しない
    4. ④ 数値と文字列のデータ型に注意する
    5. ⑤ 為替レートの定義を統一する
    6. ⑥ CSV更新後は元データとの照合を行う
  9. 9. 実務向けにPower Queryを発展させる方法
  10. 10. よくある質問
    1. Q. Power Queryを使うのにVBAは必要ですか？
    2. Q. CSVの保存場所を変更しても使えますか？
    3. Q. Excelを開くだけで自動更新されますか？
    4. Q. Power Queryで計算した結果をピボットテーブルにできますか？
    5. Q. 今回のサンプルはそのまま実務で使えますか？
  11. 11. まとめ：Power Queryで毎月の予実差異分析を効率化しよう

Power Queryで予実差異分析を自動化すると何が変わる？

通常のExcel作業では、毎月次のような処理が発生します。

  1. 予算データをExcelに貼り付ける
  2. 実績データをExcelに貼り付ける
  3. 製品コードをキーにしてデータを突き合わせる
  4. 為替レートを設定する
  5. 数量差異・単価差異・為替差異を計算する
  6. 合計金額と差異の整合性を確認する

Power Queryでは、これらのうちデータの取り込み・結合・計算を自動化できます。

作業通常のExcel作業Power Query導入後
CSV取り込み毎月コピー＆ペースト更新操作で再取り込み
データ突合XLOOKUPなどで処理登録した結合処理を再実行
差異計算Excel数式で計算クエリ内で計算
翌月の更新貼り付け・数式確認CSV差し替え後に「すべて更新」

特に、毎月同じ形式のデータを扱う経理・管理会計業務では、Power Queryを使うメリットがあります。

2. サンプルファイルをダウンロード

今回使用するサンプルには、Excelファイル、3種類のCSV、Power QueryのMコード、導入手順書が含まれています。

Power Query予実差異分析・導入キット

Excel＋予算CSV＋実績CSV＋為替CSV＋Mコード

無料サンプルをダウンロード

※Power Query接続は未登録です。この記事の手順で初回設定を行ってください。

ZIPファイルを展開すると、次のファイルが入っています。

ファイル内容
PQ_予実差異分析_導入用.xlsxExcel分析用ファイル
budget.csv予算数量・予算単価
actual.csv実績数量・実績単価
fx.csv予算・実績為替レート
PQ_予実差異分析.mPower Queryの処理コード
README.txt初回設定の手順

Excelファイルを開くだけではPower Queryは動作しません。初回のみ、Mコードを登録する必要があります。

3. 今回使用する3製品のサンプルデータ

今回はA001・A002・A003の3製品について、米ドル建ての売上を円換算して分析します。

予算データ（budget.csv）

製品予算数量予算単価USD
A0011,000100
A002500200
A00380075

実績データ（actual.csv）

製品実績数量実績単価USD
A001900110
A002550210
A00376073

為替データ（fx.csv）

今回は3製品とも、予算レート140円/ドル、実績レート150円/ドルを使用します。

実際のCSVには製品ごとに為替レートが登録されています。

4. Power QueryをExcelに登録する手順

ここからは、Windows版Excelを想定して説明します。Excelのバージョンによってメニュー名が多少異なる場合があります。

手順① ZIPファイルを展開する

ダウンロードしたZIPを展開し、budget.csv、actual.csv、fx.csvを同じフォルダに保存します。

例えば、次のフォルダを使用します。

フォルダ名は変更しても構いません。ただし、後でPower QueryのMコードに保存場所を指定する必要があります。

手順② Excelで空のクエリを作成する

  1. 「PQ_予実差異分析_導入用.xlsx」を開く
  2. Excelの「データ」タブを選択する
  3. 「データの取得」から「その他のデータソース」を開く
  4. 「空のクエリ」を選択する
  5. Power Queryエディターが開いたら「詳細エディター」を選択する

メニューに「空のクエリ」が見つからない場合は、Excelのバージョンによって表示位置が異なる可能性があります。

手順③ Mコードを貼り付ける

ダウンロードした「PQ_予実差異分析.m」をテキストエディターで開き、内容をすべてコピーします。

Power Queryの詳細エディターに表示されているコードを置き換えてください。

手順④ CSVフォルダの保存先を変更する

Mコードの先頭付近に、次の設定があります。

この部分を、自分のパソコンでCSVを保存したフォルダに合わせて変更してください。

配布コードではバックスラッシュ(または\マーク)が2つ続く表記になっていますが、Power QueryのM言語ではバックスラッシュを1つで記述できます。例えば、次のように変更できます。

末尾のバックスラッシュを残してください。

なお、フォルダの保存場所を変更した場合は、その都度この設定も変更する必要があります。

手順⑤ クエリをExcelに読み込む

  1. 詳細エディターで「完了」をクリックする
  2. Power Queryのプレビューに3製品の分析結果が表示されることを確認する
  3. 「ホーム」タブから「閉じて読み込む」を選択する
  4. 分析結果がExcelのワークシートに出力されることを確認する

必要に応じて「閉じて次に読み込む」を選び、出力先を新しいワークシートに指定してください。

これで初回設定は完了です。

5. Power Queryで実行している処理の仕組み

今回のMコードは、主に次の処理を行っています。

① 3種類のCSVを読み込む

budget.csv、actual.csv、fx.csvを読み込み、列名とデータ型を設定します。

数量・単価・為替レートは数値型に変換します。

② 製品コードで予算と実績を結合する

予算データと実績データを「製品」列で結合します。

今回のコードでは、予算または実績のどちらか一方にしか存在しない製品も一度は結合結果に含めるため、完全外部結合を使用しています。

ただし、その後の処理で必要な値が欠けている行は除外します。新製品や販売終了品の例外処理については後述します。

③ 為替レートを結合する

製品コードを使って、fx.csvの予算レートと実績レートを結合します。

④ 円換算売上高を計算する

予算売上と実績売上は、次の式で計算します。

予算売上＝予算数量×予算単価USD×予算レート

実績売上＝実績数量×実績単価USD×実績レート

⑤ 数量・単価・為替差異を計算する

数量、単価、為替の順に実績値へ置き換える方法で計算します。

  • 数量差異＝（実績数量－予算数量）×予算単価USD×予算レート
  • 単価差異＝（実績単価USD－予算単価USD）×実績数量×予算レート
  • 為替差異＝（実績レート－予算レート）×実績数量×実績単価USD

最後に、売上差異と3つの差異要因の合計が一致するか照合します。

6. Power Queryの計算結果を確認する

サンプルCSVを正しく取り込むと、次の結果になります。

製品予算売上実績売上売上差異
A0011,400万円1,485万円＋85万円
A0021,400万円1,732.5万円＋332.5万円
A003840万円832.2万円▲7.8万円
合計3,640万円4,049.7万円＋409.7万円

差異要因別の合計は、次のとおりです。

差異要因金額
数量差異▲42万円
単価差異＋181.72万円
為替差異＋269.98万円
売上差異合計＋409.7万円

照合差額は0円です。

今回のサンプルでは、販売数量の減少によるマイナス影響がある一方、単価上昇と円安が売上増加に貢献しています。

重要：Power Queryの出力結果には製品別の行が表示されます。上記の合計金額は、各行を合算したものです。必要に応じてExcelのSUM関数やピボットテーブルで合計を表示してください。

7. 翌月からの更新方法

Power Queryを導入する最大のメリットは、翌月以降の更新作業を減らせることです。

今回のサンプルでは、次の手順で更新します。

  1. 翌月の予算データをbudget.csvに反映する
  2. 翌月の実績データをactual.csvに反映する
  3. 為替レートをfx.csvに反映する
  4. CSVファイルを同じ名前・同じ保存場所に保存する
  5. Excelを開き、「データ」タブの「すべて更新」をクリックする
  6. 差異分析結果と照合差額を確認する

CSVの列名や構造が変わらなければ、基本的に同じクエリを再利用できます。

ただし、今回のコードは指定した3つのファイルを読み込む方式です。月別CSVをフォルダに蓄積し、自動で複数ファイルを結合する機能は含まれていません。

8. Power Queryで予実差異分析を行う際の注意点

① 新製品や販売終了品が結果から除外される

今回のサンプルコードは、予算数量・予算単価・実績数量・実績単価・為替レートがそろった製品のみを最終結果に出力します。

そのため、予算に存在しない新製品や、実績に存在しない販売終了品は、最終結果から除外されます。

実務では、これらを単純に除外すると売上差異の集計漏れにつながります。

新製品・販売終了品を別途抽出し、例外リストで管理する仕組みを追加することをおすすめします。

② 製品コードの重複に注意する

今回のサンプルは、各CSVで製品コードが1行ずつ登録されている前提です。

同じ製品コードが複数行存在する場合、結合によって行数が増え、売上が重複計上される可能性があります。

例えば、製品別・月別に分析するなら、結合前に「年月＋製品コード」で集計するなど、キーを統一してください。

③ CSVの列名を変更しない

今回のMコードでは、次の列名を参照しています。

  • 製品
  • 予算数量
  • 予算単価USD
  • 実績数量
  • 実績単価USD
  • 予算レート
  • 実績レート

列名が変わるとエラーになることがあります。ERPから出力するCSVの形式が変わった場合は、Power Queryの設定も見直してください。

④ 数値と文字列のデータ型に注意する

製品コードは文字列、数量・単価・為替レートは数値として扱います。

特に製品コードが「00123」のようにゼロから始まる場合、数値に変換すると先頭のゼロが失われることがあります。

また、CSVの小数点や桁区切りの形式が異なる場合は、数値変換時にエラーが発生する可能性があります。

⑤ 為替レートの定義を統一する

予算レートと実績レートの定義が異なると、為替差異の解釈が難しくなります。

予算レート、月中平均レート、取引日レートなど、社内の管理会計ルールに合わせて設定してください。

なお、今回の為替差異は円換算売上高の予実差異であり、売掛金決済などで発生する会計上の為替差損益とは異なります。

⑥ CSV更新後は元データとの照合を行う

Power Queryで照合差額が0になっていても、入力データの欠落や重複まで保証されるわけではありません。

実務では、次の確認が必要です。

  • 予算・実績の対象期間が一致しているか
  • CSVの行数や対象製品数に異常がないか
  • 除外された製品がないか
  • SAPなどのERPの売上高と一致しているか
  • 返品・値引き・調整伝票が適切に反映されているか

9. 実務向けにPower Queryを発展させる方法

今回のサンプルは、Power Queryによる予実差異分析の基本形です。

実務では、次のような機能を追加することで、さらに便利になります。

  • 月別CSVをフォルダからまとめて取り込む
  • 製品コード・年月・拠点コードをキーにして結合する
  • 複数通貨の為替レートを自動で対応付ける
  • 新製品や販売終了品を自動判定する
  • マスタに存在しない製品コードを抽出する
  • 製品別・工場別・得意先別の差異をピボットテーブルで分析する
  • SAPなどのERP出力データを定型フォーマットへ変換する

特に、月次決算や予実管理で同じ作業を繰り返している場合は、Power Queryによるデータ加工の自動化を検討する価値があります。

Excel・Power Queryによる業務改善のご相談

今回のサンプルは3製品のシンプルな構成ですが、実際の業務では製品数が多く、複数拠点・複数通貨のデータを扱うこともあります。

業務改善ラボでは、Excel・Power Queryを活用した予実管理や月次集計の効率化について、個別のご相談を受け付けています。

「SAPから出力したCSVを毎月加工している」「予算と実績の突合に時間がかかる」「Excelの集計処理を自動化したい」といった場合は、お問い合わせページからご相談ください。

10. よくある質問

Q. Power Queryを使うのにVBAは必要ですか？

いいえ。今回のサンプルではVBAマクロを使用していません。Power QueryのMコードでデータの取り込み・結合・計算を行います。

Q. CSVの保存場所を変更しても使えますか？

はい。ただし、MコードのFolderPathを新しい保存場所に変更する必要があります。

Q. Excelを開くだけで自動更新されますか？

今回のサンプルでは、基本的に「すべて更新」を実行します。必要に応じてクエリの接続プロパティで、ファイルを開いたときに更新する設定を検討できます。

Q. Power Queryで計算した結果をピボットテーブルにできますか？

はい。Power Queryの結果をExcelのテーブルに読み込めば、そのデータを使ってピボットテーブルを作成できます。

製品別、拠点別、月別などの集計が必要な場合に便利です。

Q. 今回のサンプルはそのまま実務で使えますか？

今回のコードは、3つのCSVに必要な項目がそろっていることを前提とした学習用サンプルです。

新製品・販売終了品・重複キー・返品などの例外処理は十分に実装されていません。実務で利用する際は、社内のデータ構造と管理会計ルールに合わせた調整が必要です。

11. まとめ：Power Queryで毎月の予実差異分析を効率化しよう

今回は、Power Queryを使って予算・実績・為替データを取り込み、売上差異を数量・単価・為替の3要因に分解する方法を紹介しました。

  • 予算・実績・為替の3種類のCSVを取り込む
  • 製品コードでデータを結合する
  • 数量差異・単価差異・為替差異を自動計算する
  • 3製品合計で409.7万円の売上差異を確認する
  • 翌月以降はCSV差し替えと「すべて更新」で再集計する

Power Queryは、毎月同じ形式のデータを扱う業務と相性のよい機能です。

まずは今回のサンプルで、CSVの読み込みから差異計算、更新までを試してみてください。

Power Query予実差異分析・導入キット

3製品のCSVデータ・Excel・Mコード付き

無料サンプルをダウンロード

※本記事の数値は架空のサンプルです。Power Queryの初回登録とExcel上での動作確認は利用者側で行ってください。実務利用に際しては、データの完全性と社内の計算ルールを確認してください。

ABOUT ME
まっすー
製造業の経理・業務改善を発信する「まっすー」です。原価計算・Excel・Power Query・SAP・Pythonを使って、面倒な仕事をラクにする方法を紹介しています。
BLOG：https://massu-keiei.com