鷹尾祥(アキラ)のExcel BI ブログ

Excelの革新的機能であるパワーピボット/パワークエリ/DAXについて紹介します。

同じ形式のデータを自動で比較する(クエリのマージ)

テーマ

今回は、前月と今月の差分を比べるような履歴の比較です。項目としてレコードの増減、数値、テキストを比較します。技術面のポイントは以下3点です。

1.  比較するための照合列(キー項目)を特定する

2.  結合の種類を完全外部結合(マージ)し、統合リストを作成する

3. レコードの増減に応じて条件列を作る

 

レコードの増減には以下3つのパターンがあるので、それぞれ条件列で対応します。

  • 新規 前回は無かったが、今回はある
  • 削除 前回はあったが、今回は無い
  • 既存 前回も今回もある 

テストデータ

今回使用するデータは以下の2つです。

  • 前回ファイル

    f:id:modernexcel7:20190503171155p:plain

  • 今回ファイル

    f:id:modernexcel7:20190503171229p:plain

手順

  1. 統合リストの用意
    まずは2つのファイルを照合列(番号)で統合します。

    基準とする「今回ファイル」の上にカーソルを置く 

    f:id:modernexcel7:20190503171406p:plain

    「クエリ」メニュー→結合

    f:id:modernexcel7:20190503171440p:plain

    「マージ」設定
     左クエリ  今回ファイル
     右クエリ  前回ファイル
     照合列   番号
     結合の種類 完全外部

    f:id:modernexcel7:20190503171533p:plain

    データプレビューに結合されたテーブルが表示されます。

    f:id:modernexcel7:20190503171710p:plain

    「前回ファイル」右の展開ボタンを押して、Tableデータを展開

    f:id:modernexcel7:20190503172222p:plain

    これで統合テーブルができました。


    「null」はデータが無いという意味なので、以下のようになります。
    3行目 「番号」が「null」が削除レコード
    5行目 「前回ファイル.番号」が「null」の行が新規レコード
    その他  既存レコード

  2. 条件番号の追加
    次に「照合列」として使用した「番号」列を統合し、「統合番号」を作ります。

    列の追加→条件列

    f:id:modernexcel7:20190503172648p:plain

    「条件列の追加」設定
    「番号」がnull の時は「前回.ファイル番号」にします。
    f:id:modernexcel7:20190503175002p:plain

    nullデータのなくなった「統合番号」が追加されました。

    f:id:modernexcel7:20190503175039p:plain

  3. 「変更タイプ」の追加

    続いて「変更タイプ」を追加します。

    列の追加→条件列
    「条件列の追加」設定
    「番号」がnullなら「削除」、「前回ファイル.番号」がnullなら「新規」、それ以外なら「既存」となります。

    f:id:modernexcel7:20190503175057p:plain

    「変更タイプ」が追加されました。

    f:id:modernexcel7:20190503175137p:plain

  4. 「数字差異」の追加(数値の比較)

    次は「数字差異」を比較します。数値の比較は引き算ですが、nullデータと演算を行うと結果がnullになってしまうので、最初に0に変換しておきます。

    「数字」「前回ファイル.数字」列を選択→右クリック→値の置換
    「値の置換」設定

    f:id:modernexcel7:20190503175153p:plain

    nullが0に変換された「数字」と「前回ファイル.数字」

    f:id:modernexcel7:20190503175215p:plain

    次に引き算を行います。

    列の追加→カスタム列
    「カスタム列」設定

    f:id:modernexcel7:20190503175235p:plain

    「数字差異」が計算されました。
    f:id:modernexcel7:20190503175429p:plain

  5. 「データ差異」の追加(テキストの比較)

    まずは数字差異の時と同様に、「データ」と「前回ファイル.テキスト」のnullを変換します。テキストの場合、空白に変換します。

    「データ」と「前回ファイル.テキスト」を選択→右クリック→値の置換
    「値の置換」設定

    f:id:modernexcel7:20190503175447p:plain

    テキストデータのnullが空白に変換されました。
    nullと空白が異なるというのは少し興味深いですね。
    f:id:modernexcel7:20190503175503p:plain

    テキストデータは四則演算ができないので、文字列が何から何に代わったかを
    表示させます。列を選択するときは順番に注意してください。逆にすると、マージの結果が逆転します。

    「前回ファイル.データ」→「データ」の順番で選択
    列の追加→列のマージ

    f:id:modernexcel7:20190503175521p:plain

    「列のマージ」設定
    「区切り記号」には「-カスタムー」で「=>」を入力
    f:id:modernexcel7:20190503175537p:plain

    「データ変化」が追加されました。前回と今回の変化が矢印で表されています。

    f:id:modernexcel7:20190503175550p:plain

    「データ変化」のままでは変更のなかったデータも表示されてしまうので、条件列を追加して変化が無かったときは「変更なし」にします。

    列の追加→条件列
    「条件列の追加」設定

    f:id:modernexcel7:20190503175611p:plain

    「データ差異」が追加されました。変化が無い場合は「差異なし」となります。
    f:id:modernexcel7:20190503175626p:plain

  6. 最後の仕上げ

    不要な列を選択して削除します。残った列は以下4つのみです。
    (もちろん元データを残しておきたい場合は残したままで結構です。)

    f:id:modernexcel7:20190503175646p:plain

    最後に「統合番号」を「番号」にリネームします。
    f:id:modernexcel7:20190503175700p:plain

    「閉じて読み込む」を実行してワークシートテーブルに読み込みます。

    f:id:modernexcel7:20190503175708p:plain