2つのデータの差異をExcelのPowerQuery で簡単に探す

2つのエクセルデータでの不一致を探す方法はいくつかあり、簡単なのはAccess(アクセス)を使う方法です。しかし、勤務先でAccessを使える環境ではないことが多いのではないでしょうか。

エクセルを使った方法では、関数を使った方法もあります。

参考:エクセルでVLookUp関数を使った2つのデータの不一致・追加されたデータを探す方法

上記の方法では、データが更新された場合やデータ量が多い場合には、扱いづらくなります。ここでは、簡単にデータの差異を探す方法として「PowerQuery(パワークエリ)」を利用する方法を紹介します。

目次

エクセルデータの不一致の例

例:エクセルブック「カテゴリ」にシート「1」とシート「2」の不一致データがあります。

  • 黄色:シート「2」にあり、シート「1」にないデータ
  • 青色:シート「1」にあり、シート「2」にないデータ

エクセルのPowerQueryで不一致データを探す方法

いくつかの方法がありますが、ここでは新しいエクセルブックに「カテゴリ」のデータを取得する方法です。

1つめのデータを取得する(接続専用)

(1)新しいブックを開き、任意の名前で保存します。

(2)エクセルブック「カテゴリ」のシート「1」のデータを取得します。

タブ[ データ ] > [ データの取得 ]> [ ファイルから ]> [ Excelブックから ] の順でクリックします。

エクセル・PowerQuery

カテゴリ.xlsxを選択し、「インポート」をクリックします。

→ ナビゲーター画面が表示されます。

エクセル・PowerQuery・ナビゲーター

(3)シート「1」を選択します。

エクセル・PowerQuery・ナビゲーター

(4)画面右下の「データの変換」より「読み込み先・・・」をクリックします。

エクセル・PowerQuery・ナビゲーター

(5)「接続の作成のみ」を選択し、「OK」をクリックします。

エクセル・PowerQuery・接続専用

→画面左側:クエリと接続にシート「1」が接続専用と表示されます。

エクセル・PowerQuery・接続専用

2つめのデータを取得する

(1)エクセルブック「カテゴリ」のシート「2」のデータを取得します。

タブ[ データ ] > [ データの取得 ]> [ ファイルから ]> [ Excelブックから ] の順でクリックします。

シート「1」と同様の方法

エクセル・PowerQuery

→ ナビゲーター画面が表示されます。

エクセル・PowerQuery・ナビゲーター

(2)画面右下の「データの変換」をクリックします。

エクセル・PowerQuery

→ PowerQueryエディターが開きます。

エクセル・PowerQuery・PowerQueryエディター

(3)タブ[ ホーム ] > [ クエリのマージ ]をクリックします。

エクセル・PowerQuery・PowerQueryエディター

→ マージの画面上部にシート「2」が表示されます。

(4)「1」を選択します。

エクセル・PowerQuery

→ 読み込み後の画面です。

エクセル・PowerQuery

(5)「接続の種類」から「完全外部(両方のすべて)」を選択します。

エクセル・PowerQuery

(6)シート「2」とシート「1」の「CD」をクリックし、「OK」をクリックします。

「CD」をキーとするため

エクセル・PowerQuery・マージ

→ 一番左にシート「1」の列が追加されます。

エクセル・PowerQuery・マージ

(7)展開ボタン(赤丸)をクリックし、展開したい項目にチェックを入れて「OK」をクリックします。

エクセル・PowerQuery・マージ

→ シート「1」のデータが右端の列に展開されます。

2つのデータの差分

条件列の追加を使って、2つのデータの差分をさがします。

(1)[ 列の追加 ] > [ 条件列 ] をクリックします。

エクセル・PowerQuery

→ 条件列の追加が表示されます。

エクセル・PowerQuery

(2)条件を設定します。

エクセル・PowerQuery

新しい列名はわかれば何でもOK

条件1
  • 列名:CD
  • 演算子:指定の値に等しい
  • 値:null
  • 出力:1になし(わかれば何でもOK)

「列の追加」でもう一つ条件を追加します。

エクセル・PowerQuery
条件2
  • 列名:1.CD
  • 演算子:指定の値に等しい
  • 値:null
  • 出力:2になし(わかれば何でもOK)

それ以外の場合に「一致」(わかれば何でもOK)を設定し、「OK」をクリックします。

→ 設定後の画面です。

エクセル・PowerQuery

(3)[ ホーム ] > [ 閉じて読み込む ] をクリックします。

エクセルのシートにデータを読み込みます。

結果

PowerQueryエディターの画面で設定した結果です。

エクセル・PowerQuery

E列(差異):「1になし」で抽出すると、シート「1」にはないデータが表示されます。

エクセル・PowerQuery

E列(差異):「2になし」で抽出すると、シート「2」にはないデータが表示されます。

エクセル・PowerQuery

E列(差異):「一致」で抽出すると、両方のシートにあるデータを表示します。

エクセル・PowerQuery
目次