Power Queryで2つのCSVを自動比較！数量差異・単価差異も分析【無料ツール付き】

「2つのCSVファイルを比較して、何があって、何がないのかを確認したい」

「数量や単価が変わった結果、金額にいくら影響したのかを調べたい」

経理・購買・生産管理などの管理部門では、こうしたデータ比較を行う機会が多いのではないでしょうか。

例えば、次のような業務です。

• 前月と当月の在庫データを比較する
• 新旧の部品表（BOM）で追加・削除された部品を確認する
• 購買単価の変更が金額に与える影響を分析する
• SAPなどの基幹システムから出力したCSVデータを照合する
• システム移行前後の数量・単価・金額を比較する

こうした作業をVLOOKUPやXLOOKUPで行うと、数式が複雑になったり、毎回データを貼り替える手間が発生したりします。

そこで今回は、ExcelのPower Query（パワークエリ）を使ったCSV比較ツールを紹介します。

2つのCSVファイルを読み込み、部品の存在有無、数量差、単価差、金額差を一覧化します。さらに、金額差を「数量差異」「単価差異」「追加削除影響」に分解します。

記事で使用するMコードとサンプルCSVは、ZIPファイルにまとめて無料公開します。

初回はMコードをPower Queryに登録する必要がありますが、その後はCSVを差し替えてExcelの「すべて更新」を押すだけで、同じ比較処理を繰り返せます。

今回紹介するCSV比較ツールでできること

今回のツールでは、「データA.csv」と「データB.csv」という2つのCSVファイルを比較します。

両方のCSVには、部品コード・部品名・数量・単価の4項目が含まれているものとします。

このツールでは、次の情報を自動で計算・判定します。

• データAだけに存在する部品
• データBだけに存在する部品
• 両方に存在する部品
• A数量・B数量・数量差
• A単価・B単価・単価差
• A金額・B金額・金額差
• 数量差異（金額への影響）
• 単価差異（金額への影響）
• 部品の追加・削除による金額影響
• 金額差と差異内訳の一致チェック

つまり、単純なデータの一致・不一致だけでなく、金額の増減が何によって発生したのかまで確認できる比較資料を作成します。

データとしてはサンプルとしてこちらのデータを入れてあります。もちろん、加工して必要なデータに置き換えることが可能です。

データA.csv

データB.csv

この2つのCSVには、次の違いがあります。

• P001：数量が10から12に増加
• P002：単価が200から240に上昇
• P003：数量・単価とも変更なし
• P004：データAにしか存在しない
• P005：データBにしか存在しない
• P006：数量と単価の両方が変更

完成イメージ：金額差を数量差異・単価差異に分解する

まずは、ツールで作成する比較結果をご覧ください。

今回は、6種類の部品を比較した例です。

今回のサンプルでは、A金額が4,800円、B金額が6,000円となり、金額差は＋1,200円です。

この金額差を分析すると、数量差異が＋700円、単価差異が−100円、部品の追加・削除による影響が＋600円となっています。

700−100＋600＝1,200円

このように、金額が増減した理由を確認できる点が、今回のツールの大きな特徴です。

なお、上の表は主要列だけを抜粋したものです。実際の比較結果には、数量や単価、存在判定などの列も含まれます。また、合計行はExcel側の集計機能などで表示します。

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

今回使用するサンプルCSVとPower QueryのMコード、比較結果のExcelテンプレートをZIPファイルにまとめました。

Power Query CSV比較ツールの練習セットを無料ダウンロード

ZIPファイルの内容

注意：配布するExcelテンプレートには、比較結果の見本が入っていますが、Power Queryの接続は初期状態では設定されていません。最初にMコードを登録して、比較結果を読み込む設定が必要です。

一度設定して保存すれば、次回からはCSVの差し替えと「すべて更新」で再利用できます。

それでは、実際に操作してみましょう。

手順① ZIPファイルを展開してCSVを準備する

まず、ダウンロードしたZIPファイルを展開します。

その中にある「データA.csv」「データB.csv」を、任意のフォルダに保存してください。

例えば、次のように保存します。

今回のサンプルは、どちらのCSVも次の4項目で構成されています。

• 部品コード
• 部品名
• 数量
• 単価

部品コードを共通キーとして照合し、数量と単価を比較します。

CSVはExcelで編集して保存し直す必要はありません。サンプルファイルをそのまま利用してください。

手順② ExcelでPower Queryの詳細エディターを開く

次に、比較結果を出力するExcelブックを開きます。新しいExcelブックを使って設定しましょう。

Excelの「データ」タブから、次の順番で操作します。

1. 「データの取得」をクリック
2. 「その他のデータソースから」を選択
3. 「空のクエリ」を選択
4. Power Queryエディターで「ホーム」→「詳細エディター」をクリック

詳細エディターを開くと、Mコードを入力する画面が表示されます。

ここに、ZIPファイルに同梱したコードを貼り付けます。

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

ZIPファイルに入っているMコードのテキストファイルを、メモ帳などのテキストエディターで開きます。

Ctrl＋Aで全文を選択し、Ctrl＋Cでコピーしてください。

続いて、Power Queryの詳細エディターに戻り、表示されている既存のコードを置き換えて貼り付けます。

今回のMコードには、次の処理がまとめて組み込まれています。

• CSVファイルの読み込み
• データ型の設定
• 部品コードの空欄・重複チェック
• 完全外部結合によるデータの照合
• Aのみ・Bのみ・両方ありの判定
• 計算用の数量・単価のnullを0に補完
• A金額・B金額・金額差の計算
• 数量差異・単価差異・追加削除影響の計算
• 金額差と差異内訳の一致チェック
• 比較結果の一覧表作成

すべての処理を画面上で一つずつ設定する必要はありません。

今回は、あらかじめ作成したMコードを貼り付けることで、比較処理をまとめて設定します。

手順④ CSVの保存場所を設定する

Mコードを貼り付けたら、コードの先頭付近にあるCSVファイルの保存場所を確認してください。

今回のコードでは、次の2行でCSVの場所を指定しています。

CSVを別のフォルダに保存した場合は、実際の保存先に変更します。

例えば、DドライブのCSV比較フォルダに保存した場合は、次のようになります。

変更後、「完了」をクリックします。

初回実行時には、ファイルへのアクセスに関する確認画面などが表示されることがあります。内容を確認して設定してください。

エラーが表示される場合は、まずファイル名と保存場所が正しいか確認しましょう。

手順⑤ 比較結果をExcelに出力する

Power Queryエディターに比較結果が表示されたら、正常に処理できています。

「ホーム」→「閉じて読み込む」を選択し、Excelに比較結果を出力します。

出力される比較表には、次の項目が含まれます。

• 部品コード・部品名
• 存在判定
• A数量・B数量・数量差
• A単価・B単価・単価差
• A金額・B金額・金額差
• 数量差異・単価差異・追加削除影響
• 差額チェック

Excelに出力したら、必要に応じて数値に桁区切りを設定したり、差異の大きい順に並べ替えたりすると、比較資料として使いやすくなります。

また、集計行やピボットテーブルを利用すれば、数量差異・単価差異・追加削除影響の合計を表示できます。

最後に、Power Queryのクエリを含むExcelブックを「CSV比較ツール.xlsx」などの名前で保存してください。

この保存操作によって、設定したPower Queryの処理を次回以降も利用できるようになります。

手順⑥ 次回からはCSVを差し替えて「すべて更新」するだけ

初回設定が完了すれば、2回目以降の操作は簡単です。

1. 新しいデータA.csvとデータB.csvを用意する
2. 前回と同じフォルダに、同じファイル名で保存する
3. 初回に設定した「CSV比較ツール.xlsx」を開く
4. 「データ」→「すべて更新」をクリックする
5. 比較結果が更新されたことを確認する

Power QueryがCSVを読み直し、同じ比較・差異分析処理を再実行します。

毎回CSVをExcelにコピーしたり、VLOOKUPの数式を作成したりする必要はありません。

ただし、CSVの列名・保存場所・データ型などが変わった場合は、設定の見直しが必要です。

また、ここでいう「自動比較」とは、Excelの更新操作によって比較処理が自動実行されるという意味です。CSVを保存しただけでExcelが自動更新されるわけではありません。

Power Queryの比較処理はどのような仕組みなのか？

ここまでは、Mコードを貼り付ける方法で比較ツールを作成しました。

ここからは、今回の処理で使っているPower Queryの考え方を簡単に紹介します。

仕組みを理解しておくと、自社データへの応用もしやすくなります。

① 完全外部結合（フルジョイン）で2つのデータを照合する

今回のツールでは、部品コードをキーにして2つのCSVデータを結合しています。

使用しているのは、完全外部結合（Full Outer Join）です。

完全外部結合は、両方に存在する部品だけでなく、データAにしかない部品、データBにしかない部品も取得できる結合方法です。

例えば、片方にしかない部品を探す場合、左外部結合だけでは見落としてしまうことがあります。

部品の追加・削除も含めて比較したい場合は、完全外部結合が便利です。

② 数量・単価のnullを0に置き換える

完全外部結合では、片方に存在しない部品のデータがnullになります。

例えば、データAにしかない部品は、データB側の数量や単価がnullになります。

このままでは金額を計算しづらいため、今回のツールでは計算用のnullを0に置き換えています。

ただし、nullと0は同じ意味ではありません。

数量が0の部品と、データ自体が存在しない部品を区別するため、先に存在判定を行っています。

また、元CSVに数量や単価の入力漏れがある場合は、そのまま0として扱うと誤った結果になる可能性があります。

今回のサンプルコードでは、元データに空欄がある場合はエラーを出す考え方を取り入れています。

③ 数量差異・単価差異を計算する

今回のツールでは、まず次の式で金額を計算します。

金額＝数量×単価

そのうえで、金額差を数量差異と単価差異に分解します。

数量差異＝（B数量−A数量）×A単価

単価差異＝（B単価−A単価）×B数量

例えば、ある部品の数量が4個から6個、単価が250円から200円に変わった場合を考えます。

A金額＝4×250＝1,000円

B金額＝6×200＝1,200円

金額差＝＋200円

この差を分解すると、次のようになります。

• 数量差異：（6−4）×250＝＋500円
• 単価差異：（200−250）×6＝−300円

数量が増えたことで500円増加し、単価が下がったことで300円減少しています。

合計すると＋200円となり、金額差と一致します。

なお、数量差異・単価差異には複数の算定方法があります。今回のツールでは、数量差異をA単価基準、単価差異をB数量基準で計算しています。

④ 部品の追加・削除による影響は別に計算する

片方にしか存在しない部品については、比較対象となる数量や単価がありません。

そのため、今回のツールでは「追加削除影響」という項目を設けています。

• データAにしかない部品：−A金額
• データBにしかない部品：＋B金額
• 両方にある部品：0

これにより、既存部品の数量・単価変化と、部品構成の変化を区別できます。

最終的には次の関係になります。

金額差＝数量差異＋単価差異＋追加削除影響

今回のサンプルでは、700−100＋600＝1,200円となります。

実務で重要なのは、Mコードよりも「比較ルールの設計」

今回紹介したツールは、比較的シンプルな部品データを対象としています。

しかし、実際の業務では、次のような問題が発生することがあります。

• 同じ部品コードが複数行存在する
• 工場コードと部品コードを組み合わせないと一意にならない
• CSVごとに列名やデータ形式が異なる
• 同じ部品でも数量の単位が異なる
• 単価の通貨や価格単位が異なる
• 複数の明細を集計してから比較する必要がある
• 部品名やマスタ情報の変更も確認したい
• 元のCSVと比較結果の合計金額が一致しない

例えば、同じ部品コードが複数行存在する場合、単純にマージすると行数が増え、金額が重複してしまうことがあります。

その場合は、事前に部品ごとに集計するか、複数項目を組み合わせて比較キーを作る必要があります。

また、数量は合計できても、単価はそのまま合計できません。比較する目的によっては、金額を集計したうえで加重平均単価を計算することもあります。

さらに、数量差異・単価差異の算定ルールについても、会社の管理会計ルールに合わせる必要があります。

そのため、Power Queryによる業務改善で大切なのは、Mコードを書くことだけではなく、データの構造を理解し、適切な比較方法を設計することだと考えています。

Power Queryによる業務改善コンサルティングについて

今回のサンプルのような比較処理は、Power Queryを使えばExcelの標準機能で実現できます。

ただし、実務ではCSVの形式や比較ルールが会社ごとに異なるため、汎用的なコードをそのまま使えない場合もあります。

業務改善ラボでは、Excel・Power Queryを活用した業務改善について、次のようなテーマでのご相談を想定しています。

• 現在のExcel・CSV比較業務の課題整理
• データ照合のキーや比較条件の検討
• Power Queryを使った業務の効率化・標準化
• 数量差異・単価差異などの分析ルールの整理
• SAPなどから出力するデータの加工・照合方法の検討
• 複数CSVの統合や定期レポート作成の効率化
• 担当者が変わっても運用できる仕組みづくり
• Power Queryの活用方法や社内運用に関するアドバイス

例えば、「毎月数時間かけているCSV比較作業を効率化したい」「Power Queryを導入したが、正しく比較できているかわからない」「データの構造が複雑で、どのように設計すべきか相談したい」といったテーマです。

単にツールを作成するだけではなく、業務の目的・比較ルール・データの構造を整理し、継続的に運用できる改善方法を考えることを重視しています。

「自社の業務でもPower Queryが使えるか知りたい」という段階でも、お気軽にご相談ください。

Power Query・Excel業務改善について相談する

※ ご相談の際は、機密情報を含む実データではなく、まずはデータの項目名、件数、比較したい内容、現在の作業方法などをお知らせください。

まとめ：Power QueryでCSV比較と差額分析を効率化しよう

今回は、Power Queryを使って2つのCSVファイルを比較し、数量差異・単価差異・追加削除影響を分析するツールを紹介しました。

今回のポイントは、次の5つです。

• CSVとMコードをまとめた無料サンプルをダウンロードできる
• Mコードを詳細エディターに貼り付けて比較処理を設定できる
• 完全外部結合によって、片方にしか存在しない部品も比較できる
• 金額差を数量差異・単価差異・追加削除影響に分解できる
• 初回設定後はCSVを差し替えて「すべて更新」で再利用できる

毎月のように繰り返しているCSV比較業務では、一度比較処理を作っておくことで、作業時間や転記ミスを減らせる可能性があります。

そして、実際の業務でPower Queryを活用するには、データの構造や比較ルールを理解することも大切です。

まずは無料のサンプルデータを使って、Power Queryによる比較と差額分析を試してみてください。

ABOUT ME

まっすー

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

BLOG：https://massu-keiei.com