ExcelでPowerQueryを使って複数のデータソースから収集したデータを結合して出力する方法を説明します。
Excelの関数ではVLOOKUPに相当し、PowerQueryではそれ以上のことが簡単な手順で実現できます。
PowerQueryが対応する結合方法
共通のキーでデータAにデータBを結合する場合、下記の通りの結合方法が選択できます。
- 左外部結合
- データAのすべての行にデータBにあるキーが同じ行を結合して出力します
- VLOOKUPに近い使い方であり、デフォルトの結合方法です。
- 右外部結合
- データBのすべての行にデータAにあるキーが同じ行を結合して出力します
- 完全外部結合
- データAとデータBをすべて出力し、キーが同じ行を結合して出力します
- 内部結合
- データAとデータBに同じキーがある行を結合して出力します
- 左反外部結合
- データAのうちデータBに同じキーがない行を結合して出力します
- 右反外部結合
- データBのうちデータAに同じキーがない行を結合して出力します
結合手順
PowerQueryでは結合をマージと呼びます。
デフォルトの結合方式である 左外部結合 を例に説明します。
用意したサンプルデータ
- user_mail.xls : 氏名とメールアドレスが記載されたExcelファイルです
- user_tel.csv : 氏名と電話番号が記載されたcsvファイルです
両方のファイルに氏名が記載されており、氏名をもとに結合できるデータです。
両方に存在する氏名、片方だけの氏名が存在しています。
手順
まずは過去の記事と同様の手順でファイルを読み込んでいきます。
Excelファイルの読み込み
Excelから をクリックします。
ファイルを選択してOKをクリックします。
読み込む対象のシートを選択して 編集 をクリックします。
先頭の行を見出しとして使用 をクリックします。
先頭行がテーブルの項目名に変換されます。
編集手順が追加されたので、閉じて読み込む をクリックします。
データが読み込まれました。
CSVファイルの読み込み
テキスト/CSVファイルからをクリックします。
対象のファイルを選択します。
プレビューを確認して 編集 をクリックします。
文字コードは自動認識されますが、認識されない場合は元のファイルの値を変更してください。
先頭の行を見出しとして使用 をクリックします。
先頭行がテーブルの項目名に変換されます。
編集手順が追加されたので、閉じて読み込む をクリックします。
データが読み込まれました。
ExcelファイルとCSVファイルを結合
読み込んだ2つのデータを結合していきます。
例ではメールアドレスの表に電話番号の表を結合していきます。
両方のデータに共通する氏名をキーにして結合します。
メールアドレスの表を表示して、クエリタブのマージをクリックします。
マージのプレビューが表示されるので、user_telを結合する表として選択します。
表が読み込まれキーを選択する準備ができました。
結合元のキー、結合先のキーをクリックし、OKをクリックします。
結合の種類のデフォルトは左外部結合です。
例では2件のデータのキーが一致したことが表示されています。
クエリエディターが起動するので、過去の記事と同様の手順で展開します。
展開する列を選択します。
※氏名は重複する列なのでチェック不要ですが、他の結合の場合の例をわかりやすくするために残しています。
テーブルが展開されたので、閉じて読み込む をクリックしてシートに読み込みます。
例では一人一つの電話番号ですが、一人が複数の電話番号を持っている場合は行が追加されて展開されます。
左外部結合なので、電話番号の無い氏名は電話番号の列が空欄になっています。
他の結合方法を選択した場合の例
同じデータで左外部以外の結合方法を選択したときの例を説明します。
結合方法の選択
結合の種類はマージのプレビュー画面で選択できます。
右外部の場合
メールアドレスのない氏名は空欄になります。
完全外部の場合
全ての氏名が表示され欠けた部分が空欄になります。
内部の場合
両方に存在する氏名のみが表示されます。
左反の場合
電話番号の無い氏名のみが表示されます。
右反の場合
メールアドレスの無い氏名のみが表示されます。
中間データを読み込まない方法
最終の出力結果のみが必要な場合、中間のデータをシートに出力しなければ処理を軽量化できます。
読み込んだシートを削除する
シートを右クリックして削除をクリックします。
右側のクエリペインには4行読み込まれました。とあり、データがシートに読み込まれた状態です。
削除をクリックします。
PowerQueryのクエリが含まれるシートを削除すると、選択肢が表示されます。
ロードを無効にする をクリックすると、クエリの接続情報を保持したままにできます。
右側のクエリペインの表示が接続専用。となり、接続情報のみを保持した状態になりました。
クエリの設定を変更する
右側のクエリペインから対象のクエリを右クリックし、読み込み先をクリックします。
接続の作成のみ を選択して読み込みをクリックします。
続行をクリックします。
右側のクエリペインの表示が接続専用。となり、接続情報のみを保持した状態になりました。
クエリの依存関係を図で表示する
例は2つのファイルを読み込むシンプルな結合でしたが、実際の現場では多数のファイルを読み込むことがあります
クエリが複雑化するとどこから何を読み込んでいるか、どのクエリが関係しているかがわからなくなってきます。
クエリの依存関係を図で表示する機能を使って、依存関係を確認します。
クエリエディターを起動します。
表示タブのクエリの依存関係をクリックします。
クエリの依存関係が図で表示されます。
クエリが読み込んでいるファイル、参照先のクエリが矢印で表示されます。
右下のツールを使えば拡大縮小などが行なえます。
