Power Queryで2つのデータの共通項目を関連付け（マージ）して取り込む

Power Queryでは、2つのデータの共通項目を関連付けして、1つのテーブルを作成することができます。

Excel2013以降であれば、テーブル間でリレーションシップを設定して1つのレポートを作成できるようになっていますが、Power Queryでも共通項目を関連付けして、同じように1つのレポートにすることができます。

上記と同じデータを使って解説します。

VLOOKUP関数のようですが、データが頻繁に変更されたり、ビッグデータの場合はPower Queryで作成すると更新が楽です。

参考Power Queryの概要については、Microsoftのページを参照してください。

Power Query の概要 - Excel（Microsoft）

データの確認

1つのブック（商品販売一覧.xlsx）に［商品」シートと、［販売］シートがあります。

ここでは、テーブルの設定はしていません。

2つのデータには、［商品ID］という共通項目があります。

上記2つのデータをPower Queryエディターに読み込んで、［販売］シートのデータに［商品名」と［定価］の列を追加します。

新規ブックでファイルを取り込む

Power Queryを使って、2つのシートを取り込みます。

まず、新規ブックを起動します。

［データ］タブの［データの取得と変換］グループにある［データの取得］から［ファイルから］-［ブックから］をクリックします。

［データの取り込み］ダイアログボックスが表示されますので、ファイルを選択して［インポート］ボタンをクリックします。

［ナビゲーター］ウィンドウが開きます。

［複数のアイテムの選択］のチェックボックスをオンにします。

［商品］と［販売］のチェックボックスをオンにして、［読み込み］ボタンをクリックします。

目的のデータが2つのブックに分かれている場合は、それぞれを読み込んでください。

Excel2016でPower Queryを使用する場合

Excel2016（永続ライセンス）の場合は、［データ］タブの［取得と変換］グループにある［新しいクエリ］から［ファイルから］-［ブックから］を選択します。

Excel2010/2013でPower Queryを使用する場合

Excel2010とExcel2013では、Power Queryアドインをインストールして使用します。

以下は、Excel2013にPower Queryアドインをインストールしています。

［POWER QUERY］タブの［外部データの取り込み］グループにある［ファイルから］-［Excelから］を選択します。

参考ご使用中のOfficeのバージョンを確認する方法は、以下の記事で解説しています。

インポートの確認

［クエリと接続］作業ウィンドウが表示され、2個のクエリが表示されます。

参考もし、［クエリと接続］作業ウィンドウを閉じてしまった場合は、［データ］タブの［クエリと接続］グループにある［クエリと接続］ボタンをクリックしてください。

Power Queryエディターでクエリのマージ

［販売］クエリをダブルクリックします。

Power Queryエディターが起動します。

［ホーム］タブの［結合］グループにある［クエリのマージ］から［新規としてクエリをマージ］をクリックします。

［マージ］ウィンドウが表示されます。

下段のプルダウンメニューから［商品］を選択します。

共通項目である［商品ID］をそれぞれのテーブルでクリックします。これで関連付けができます。

結合の種類は、［完全外部（両方の行すべて）］を選択して［OK］ボタンをクリックします。

既定は［左外部（最初の行すべて、および2番目の行のうち一致するもの）］になっていますが、ここでのサンプルの場合は既定のままでも構いません。

以下のような［Marge1］クエリが表示されます。

結合の種類について

結合の種類は、以下の6個の選択肢があります。

• 左外部（最初の行すべて、および2番目の行のうち一致するもの）・・・①
• 右外部（2番目の行すべて、および最初の行のうち一致するもの）・・・②
• 完全外部（両方の行すべて）・・・③
• 内部（一致する行のみ）・・・④
• 左反（最初の行のみ）・・・⑤
• 右反（2番目の行のみ）・・・⑥

左とはマージウィンドウの上のテーブルで、右は下のテーブルのことを指します。

テーブルにデータの無いセルがある場合に、結合の種類でどういう表示にするかを考える必要があります。

目的のクエリにならなかった場合は、上記の記事を参考にして［結合の種類］の選択肢を変更してみてください。

［Marge1］を選択して、［適用したステップ］の［ソース］の歯車マークをクリックすると、再度、［マージ］ウィンドウを開くことができます。

［結合の種類］の選択肢を変更します。

Margeクエリで項目の選択

［Marge1］クエリで取り込む項目を選択します。

［商品］の右側のボタンをクリックして、表示したい項目のチェックボックスをオンにします。

ここでは、［商品名］と［定価］をオンにします。

［元の列名をプレフィックスとして使用します］のチェックボックスはオフにします。［OK］ボタンをクリックします。

以下のように［商品名］と［定価」が表示されます。

参考［元の列名をプレフィックスとして使用します］のチェックボックスをオンにすると、以下のように列名にクエリ名と項目名が表示されます。

ドラッグで列を移動

列見出しをドラッグして、移動します。ここでは、「商品ID」の右に［商品名］と［定価］を並べます。

データの取り込み

データを取り込みます。

［ホーム］タブの［閉じて読み込む］の文字の部分をクリックすると、メニューが表示されるので［閉じて次に読み込む...］をクリックします。

参考［閉じて読み込む」を選択すると、新しいシートに読み込まれます。

［データのインポート］ダイアログボックスが表示されますので、［既存のワークシート］を選択して［OK］ボタンをクリックします。

セル位置も確認してください。

［Sheet1］に以下のようにデータが読み込まれます。

自動的にテーブルが設定されます。

テーブルについては、以下の記事で解説しています。

また、このテーブルを基にピボットテーブルを作成することもできます。

［デザイン］タブの［ツール」グループにある［ピボットテーブルで集計］をクリックします。

［データのインポート］ダイアログボックスからすぐにピボットテーブルを作成することもできます。

参考Power Queryについて学習するなら、以下のテキストがお勧めです。

データの更新をテーブルへ反映させる

データ元のワークシートでデータが変更された場合は、データを更新して最新のデータを反映させることができます。

［デザイン］タブの［外部のテーブルデータ］グループにある［更新］から［更新］をクリックします。

ブック内で複数の外部データとの接続がある場合、［すべて更新］をクリックすると、ブック内のすべての外部データの接続が更新されます。特定のデータのみ更新したい場合は、［更新］をクリックしてください。

ポップヒントで分かりますが、ショートカットキーは以下のとおりです。

• すべて更新［Ctrl］+［Alt］+［F5］
• 更新［Alt］+［F5］

または、［データ］タブの［クエリと接続］グループにある［すべて更新］から［更新］をクリックします。

関連Power Queryエディターを使うとデータベースの整形を簡単に行うことができます。

また、指定したフォルダー内の複数ファイルのデータを1つのテーブルに結合することもできます。

Power Queryを使って、同一ブック内の複数シートを結合することもできます。

Power Queryエディターを起動するショートカットキーは、［Alt］+［F12］です。慣れたらPower Queryエディターを直接開いて操作してみてください。