PowerQueryを別の2つのExcelブックから作成する方法

商品マスターブックと別の商品販売実績ブックの2つからPowerQueryを作成する方法を掲載しています。

以前作成した「商品管理シート|ExcelのPowerQuery超入門」は同じブックのシートからPowerQueryを作成しました。
今回は別のブックから作成し、その作成元のブックのデータを変更するとPowerQueryに反映されることを確認します。
別のブックを利用し作成できれば、それぞれ別の用途にも使用でき応用範囲が広がります。
例えば1つの商品マスターのデータから、資材・製造・品質・販売などに利用することができます。
作成手順は下記の通りです。

  1. 商品マスターブックの商品マスターシートを作成します。
  2. 商品販売実績ブックの販売実績シートを作成します。
  3. 商品マスターブックからデータの取得を行います。
  4. ナビゲータから商品マスターシートを選択します。
  5. 商品販売実績ブックからデータの取得を行います。
  6. ナビゲータから販売実績シートを選択します。
  7. 商品マスターと販売実績をマージ(結合)します。
  8. Power Queryエディターで販売実績のデータを展開します。
  9. 展開する列の選択をします。
  10. PowerQueryから閉じて読込みます。
  11. 商品マスターブックのデータを変更します。
  12. PowerQueryのシートに反映されることを確認します。

掲載済みの「商品管理シート|ExcelのPowerQueryを使ったテンプレートの作り方」も参照してください。


PowerQueryの作成手順

2つのブックの作成する

「商品マスター.xlsx」ブックに、「商品マスター」シートを作成し、下の表を作成します。

「商品マスター」シート

「商品販売実績.xlsx」ブックに、「販売実績」シートを作成し、下の表を作成します。
商品コードが2つのブックに共通する項目です。

「販売実績」シート

PowerQueryの作成

新規にブックを作成します。
リボンの[データ]~データの取得と変換グループの[データの取得]~[ファイルから]~[ブックから]を選択します。

新規にブックを作成する

データの取り込みダイアログボックスが開くので、「商品マスター.xlsx」を選択します。

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

ナビゲーター ダイアログボックスが開くので、「商品マスター」シートを選択し[読み込み]ボタンをクリックします。

ナビゲーター ダイアログボックス

これで商品マスターが、新規に作成したブックに取り込まれました。

商品マスターがブックに取り込まれた

同様に「商品販売実績.xlsx」から「販売実績」シートを読み込みます。

「販売実績」シートを読み込む

これで販売実績が、新規に作成したブックに取り込まれました。

販売実績がブックに取り込まれた

新規ブックの商品マスターシートのデータ上に移動します。
リボンに[クエリ]タブが表示されるので、結合グループの[結合]をクリックします。

結合グループの[結合]をクリックする

マージ ダイアログボックスが開きます。

  1. 上のコンボボックスから「商品マスター」を選択します。
  2. 下のコンボボックスから「販売実績」を選択します。
  3. 商品マスターの「商品コード」をクリックします。すると緑色背景に変わります。
  4. 販売実績の「商品コード」をクリックします。すると緑色背景に変わります。
  5. 左外部接合になっているのを確認します。この「左」は上の商品マスターを指します。
    これにより商品マスターの全てのデータが表示され、それにマッチした販売実績のデータが表示されます。
    これはAccessのリレーションやクエリの接合と同じです。
マージ ダイアログボックス

Power Queryエディターが開き、結合結果が表示されます。
販売実績のデータは「Table」と表示されているので、項目名の右のボタンをクリックします。
これでデータを展開できます。

Power Queryエディター

表示する列を選択します。
「商品コード」は共通のコードなので、チェックを外します。
外さないと2つ表示されます。

表示する列を選択する

リボンの[ホーム]~[閉じて読み込む]~[閉じて読み込む]をクリックします。

Power Queryエディター

これで新規に作成したブックに、PowerQueryで作成した表が表示されます。
商品マスターの全データと、「商品コード」でマッチした販売実績のデータが表示されています。

PowerQueryで作成した表が表示される

元のブックとPowerQueryの関係を調べる

商品マスター.xlsxを開き、商品マスターシートのデータを変更し保存します。
ここでは「マウス」を「マウスS」に変更しました。

商品マスターシートのデータを変更し保存する

PowerQueryの表に移動します。
リボンの[データ」~クエリと接続グループの[すべて更新]をクリックします。
すると別ブックで変更したデータが反映されます。
このようにPowerQueryを使うと、最新のデータを扱うことができます。

PowerQueryの表に移動する

別の2つのExcelブックから作成したPowerQueryの無料ダウンロード

PowerQueryを別の2つのExcelブックから作成する方法

商品コードが商品マスターシートと販売実績シートに共通する項目です。
商品名・単価がが商品マスターのデータです。
日付・数量が販売実績のデータです。
A03のディスプレイは、販売実績にデータが登録されていないので空白になっています。


PowerQueryブックの無料ダウンロード

商品マスターブックの無料ダウンロード

商品販売実績 ブックの無料ダウンロード

ダウンロードし使用状況に合わせて、Excelで自由にカスタマイズしご利用ください。

Follow me!