商品マスターブックと別の商品販売実績ブックの2つからPowerQueryを作成する方法を掲載しています。
以前作成した「商品管理シート｜ExcelのPowerQuery超入門」は同じブックのシートからPowerQueryを作成しました。
今回は別のブックから作成し、その作成元のブックのデータを変更するとPowerQueryに反映されることを確認します。
別のブックを利用し作成できれば、それぞれ別の用途にも使用でき応用範囲が広がります。
例えば1つの商品マスターのデータから、資材・製造・品質・販売などに利用することができます。
作成手順は下記の通りです。
- 商品マスターブックの商品マスターシートを作成します。
- 商品販売実績ブックの販売実績シートを作成します。
- 商品マスターブックからデータの取得を行います。
- ナビゲータから商品マスターシートを選択します。
- 商品販売実績ブックからデータの取得を行います。
- ナビゲータから販売実績シートを選択します。
- 商品マスターと販売実績をマージ（結合）します。
- Power Queryエディターで販売実績のデータを展開します。
- 展開する列の選択をします。
- PowerQueryから閉じて読込みます。
- 商品マスターブックのデータを変更します。
- PowerQueryのシートに反映されることを確認します。
掲載済みの「商品管理シート｜ExcelのPowerQueryを使ったテンプレートの作り方」も参照してください。
PowerQueryの作成手順
2つのブックの作成する
「商品マスター.xlsx」ブックに、「商品マスター」シートを作成し、下の表を作成します。
「商品販売実績.xlsx」ブックに、「販売実績」シートを作成し、下の表を作成します。
商品コードが2つのブックに共通する項目です。
PowerQueryの作成
新規にブックを作成します。
リボンの［データ］～データの取得と変換グループの［データの取得］～［ファイルから］～［ブックから］を選択します。
データの取り込みダイアログボックスが開くので、「商品マスター.xlsx」を選択します。
ナビゲーター ダイアログボックスが開くので、「商品マスター」シートを選択し［読み込み］ボタンをクリックします。
これで商品マスターが、新規に作成したブックに取り込まれました。
同様に「商品販売実績.xlsx」から「販売実績」シートを読み込みます。
これで販売実績が、新規に作成したブックに取り込まれました。
新規ブックの商品マスターシートのデータ上に移動します。
リボンに［クエリ］タブが表示されるので、結合グループの［結合］をクリックします。
マージ ダイアログボックスが開きます。
- 上のコンボボックスから「商品マスター」を選択します。
- 下のコンボボックスから「販売実績」を選択します。
- 商品マスターの「商品コード」をクリックします。すると緑色背景に変わります。
- 販売実績の「商品コード」をクリックします。すると緑色背景に変わります。
- 左外部接合になっているのを確認します。この「左」は上の商品マスターを指します。
これにより商品マスターの全てのデータが表示され、それにマッチした販売実績のデータが表示されます。
これはAccessのリレーションやクエリの接合と同じです。
Power Queryエディターが開き、結合結果が表示されます。
販売実績のデータは「Table」と表示されているので、項目名の右のボタンをクリックします。
これでデータを展開できます。
表示する列を選択します。
「商品コード」は共通のコードなので、チェックを外します。
外さないと2つ表示されます。
リボンの［ホーム］～［閉じて読み込む］～［閉じて読み込む］をクリックします。
これで新規に作成したブックに、PowerQueryで作成した表が表示されます。
商品マスターの全データと、「商品コード」でマッチした販売実績のデータが表示されています。
元のブックとPowerQueryの関係を調べる
商品マスター.xlsxを開き、商品マスターシートのデータを変更し保存します。
ここでは「マウス」を「マウスＳ」に変更しました。
PowerQueryの表に移動します。
リボンの［データ」～クエリと接続グループの［すべて更新］をクリックします。
すると別ブックで変更したデータが反映されます。
このようにPowerQueryを使うと、最新のデータを扱うことができます。
別の2つのExcelブックから作成したPowerQueryの無料ダウンロード
商品コードが商品マスターシートと販売実績シートに共通する項目です。
商品名・単価がが商品マスターのデータです。
日付・数量が販売実績のデータです。
A03のディスプレイは、販売実績にデータが登録されていないので空白になっています。
ダウンロードし使用状況に合わせて、Excelで自由にカスタマイズしご利用ください。
Follow me!
