Google検索

Office2019 Excel2019

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

投稿日:2019年11月26日 更新日:

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

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

テーブル間でリレーションシップを設定してピボットテーブルを作成

Excel2013以降は、テーブル間でリレーションシップを設定して1つのレポートを作成できるようになっています。 以下の記事では、[Powe ...

続きを見る

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

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

Power Queryで共通項目を結合して取り込む

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

Power Query の概要 - Excel

データの確認


1つのブック(商品販売一覧.xlsx)に[商品」シートと、[販売]シートがあります。

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

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

商品リスト

販売シート

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

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

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

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

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

[データ]タブの[データの取得]から[ファイルから]-[ブックから]

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

[データの取り込み]ダイアログボックス

[ナビゲーター]ウィンドウが開きます。

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

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

[ナビゲーター]ウィンドウ

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

Excel2016でPower Queryを使用する場合

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

Excel2016[データ]タブの[新しいクエリ]から[ファイルから]-[ブックから]

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

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

Excel2013にPower Queryをインストールして[POWER QUERY]タブを表示

Excel2013にPower QueryアドインをMicrosoftのダウンロードセンターのページからインストールしてみました。OSは、W ...

続きを見る

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

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

Excel2013[POWER QUERY]タブの[ファイルから]-[Excelから]

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

Office(Word、Excel、PowerPointなど)のバージョンを確認する方法

Microsoft Officeも長い時間を経て、大きく様変わりしてきました。 トラブルを解決しようとするとき、あるいは、何かの操作を探すと ...

続きを見る

インポートの確認

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

[クエリと接続]作業ウィンドウ

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

クエリと接続

ブック内のすべてのクエリと接続を表示して管理します。

接続は外部データソースへのリンクです。クエリを使用して、複数のソースからデータを読み込み、形成し、統合します。

[データ]タブの[クエリと接続]グループにある[クエリと接続]

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

[販売]クエリをダブルクリックします。

[販売]クエリをダブルクリック

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

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

このクエリをこのブック内の別のクエリとマージして、新しいクエリを作成します。

[ホーム]タブの[クエリのマージ]の[新規としてクエリをマージ]

[マージ]ウィンドウが表示されます。

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

[マージ]ウィンドウ

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

結合の種類は、[完全外部(両方の行すべて)]を選択して[OK]ボタンをクリックします。

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

[マージ]ウィンドウで設定

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

[Marge1]クエリ

結合の種類について

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

  • 左外部(最初の行すべて、および2番目の行のうち一致するもの)
  • 右外部(2番目の行すべて、および最初の行のうち一致するもの)
  • 完全外部(両方のすべて)
  • 内部(一致する行のみ)
  • 左反(最初の行のみ)
  • 右反(2番目の行のみ)

[結合の種類]の選択肢

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

おすすめ[結合の種類]の選択肢の内容については、以下の記事がよく纏められています。図説されていて、とても分かりやすいです。ぜひご参照ください。

クエリのマージ 結合の種類 - Akira Takao’s blog

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

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

クエリの編集

[結合の種類]の選択肢を変更します。

[マージ]ウィンドウで[結合の種類]の選択肢を変更

Margeクエリで項目の選択

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

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

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

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

Marge1クエリの[商品]の右側のボタンをクリック

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

[商品名]と[定価」が表示されたクエリ

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

[元の列名をプレフィックスとして使用します]のチェックボックスをオンにした時の列名

ドラッグで列を移動

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

列をドラッグで移動

データの取り込み

データを取り込みます。

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

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

[ホーム]タブの[閉じて読み込む]の[閉じて次に読み込む...]

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

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

[データのインポート]ダイアログボックス

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

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

読み込まれたデータ

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

テーブルの便利機能(テーブルに変換して効率アップ)

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

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

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

ピボットテーブルで集計

ピボットテーブルを使用してこのテーブルのデータを集計します。

ピボットテーブルを使用すると、複雑なデータの配列や集計、詳細の確認を簡単に行うことができます。

[デザイン]タブの[ピボットテーブルで集計]

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

[データのインポート]ダイアログボックス

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

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

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

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

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

  • すべて更新[Ctrl]+[Alt]+[F5]
  • 更新[Alt]+[F5]

[デザイン]タブの[外部のテーブルデータ]グループにある[更新]

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

[データ]タブの[クエリと接続]グループにある[すべて更新]

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

先頭行に複数の同じ項目名がある表をPower Queryで分析可能なテーブルに変換

Excel2016から標準で装備されているPower Query(パワークエリ)を使用すると、効率よくデータを加工することができます。 Ex ...

続きを見る

1列の複数行で1レコードになっている表をPower Queryで複数列のテーブルに変換

1列の複数行に1レコードのデータが並んでいる表も、Power Query(パワークエリ)エディターを使って、複数列のテーブルに整形することが ...

続きを見る

Power Queryでデータベースに整形(マトリックス表をテーブルに)

Power Query(パワークエリ)は、Excel2016とExcel2019には、標準でインストールされています。Excel2010とE ...

続きを見る

複数のマトリックス表をPower Queryでピボットテーブルに変換

Power Queryを使って、複数のマトリックス表を結合して1つのピボットテーブルに変換することができます。一度、ピボットテーブルを作成す ...

続きを見る

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

Power Queryでフォルダー内のすべてのファイルのデータを結合

ExcelのPower Queryを使って、指定したフォルダー内の複数ファイルのデータを1つのテーブルに結合することができます。後でそのフォ ...

続きを見る

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

Power Queryで複数シートの結合

Power Queryを使って、同一ブック内の複数シートを結合する方法を紹介します。同じレイアウトの表の場合は、簡単に結合できます。 結合だ ...

続きを見る

Profile

執筆者/はま

おすすめ記事と広告

-Office2019 Excel2019

Copyright © 2001-2019 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2019 AllRights Reserved Powered by AFFINGER4.