Power Queryで表引き、リレーションシップ、VLOOKUP
動画解説
Power Queryで表引きとテーブルでVLOOKUP、どちらを使う？
動画版「Excelスキルアップ仕事術」です。
Power query で取得したテーブルには、前回のように ID だけで商品名や価格などが入っていませんでした。
そういう時に Excel では VLOOKUP 関数を使いますが、 Power query で VLOOKUP 関数が使えるのか、あるいは使えなければどうするのか気になりますよね。
今回は、Power QueryでIDやコードをほかの表と関連づけて表引きする方法を紹介します。
これはリレーションシップとよばれる機能です。
わかりにくいのが、クエリのマージ。
表を結合させるときの種類です。
結合の種類は全部で 6 種類あります。
左外部（最初の行のすべて、および2番目の行のうち一致するもの）
右外部（2番目の行すべて、および最初の行のうち一致するもの）
完全外部（両方の行すべて）
内部（一致する行のみ）
左反（最初の行のみ）
右反（2番目の行のみ）
（サンプルファイルは、こちらです。 作業に使う支店のブックが入ったフォルダです。）
Power Queryで表引きVLOOKUP、Power Queryで時短、怖くないパワークエリ
テーブルでは構造化参照 Power query ではリレーションシップ
通常の Excelのテーブルで VLOOKUP を使う方法と Power query でリレーションシップを使う場合の2通りご紹介します。
-
今回はパワークエリを使って複数のブックを読み込むという手順のほかに、読み込んだブックに IDをもとに
通常なら vlookup 関数を使って表引きする操作をパワークエリの方で id から 商品価格や商品名を別に表示するという設定方法について紹介したいと思います。
-
エクセルのブックを複数結合して、さらにそのブックと商品 IDの記入されたコードの表をリレーションシップで関連付けてやります。
パワークエリでVLOOKUP的な使い方をするわけです。
-
まずはデーターの取得からです。複数のブックを結合します。
ファイルからをクリックして、次に、ブックを複数結合したいということでフォルダーを指定してやります。
-
フォルダーの指定です。フォルダパスを入力する画面が表示されます。
-
フォルダのパスをエクスプローラーからコピーして入力するか、参照をクリックしてフォルダを選択してください。
フォルダ内のブックをパワークエリで読み込む
-
するとこれ前回も表示されましたが、フォルダの中身のブックの概要が表示されます。選択したフォルダが間違いのないことを確認できます。
-
それで次はその表 に対してどうしたいかということで、結合、読み込み、データの変換、キャンセルも入れて、4つのボタンから選択します。結合をクリックすると、さらに選択するようになっています。
今回は結合してさらに変換するんですね。ですからデータの結合と変換を選択します。
-
するとファイルの結合ダイアログが表示されます。各ファイルから抽出するオブジェクト選びますと表示されています。最初のシートはこれでよければ OK をクリックします。
-
すると 今度は、パワークエリエディター というエディターが開いて、 表示してくれます。
このファイルについては シートの中に支店名が入っていない代わりにシート名自体に支店名が入っているという ことでした。
-
まず、列見出しを支店名と変えました。
次にソース名のところからおおみや店の後ろの拡張子 .xlsx この部分を削除してしまいましょう。
右クリックから値の置換、あるいは、タブ「変換」をクリックして 値の置換、を選択します。
パワークエリエディター、列にフィルターをかける
-
そして次の列、日付、コード、個数ということで合っています。これは大丈夫です。ただこの日付ですがバラバラですので、ここはフィルターをかけましょう。昇順で並べ替えます。
この読み込みには右側のクエリの設定欄に「テスト1」という名前がつけられたていますが、これではあとで混乱してしまいますから、名前を変更しておきます。「支店よみこみ」という名前に変更しておきます。
-
準備ができましたのでホームの閉じて読み込むからクリックして閉じて次に読み込むを押してください。
-
データのインポートダイアログが表示されました。けれどもまだ商品コードの接続をしていません。それで操作が完了していないので、接続の作成のみにチェックを入れて OK をクリックします。
-
次は、コード表の読み込みです。再び、パワークエリでデータの取得ボタンから、ファイルからということで、ブックからインポートしたいと思います。
-
コードが記載されているブックをインポートします。
-
ナビゲーターという画面が表示されました。これが先ほどのデータとリレーションで接続したいデータです。これはリレーションで使いたいデータですので、単体で読み込むのではなくて、読み込みのボタンから読み込み先を選択してやります。
-
すると先ほどのデータのインポートというダイアログが出てきます。こちらも接続の作成のみにしてやります。
-
支店読み込みというのが、最初のクエリ、今日行った最初のクエリです。
-
その下にあるのがSheet1という名前のクエリ、これはコードです。名前を変更しておきましょう。Sheet1ではさすがになんだかわかりません。
-
中身は商品IDに対応するコードでしたから、コードに変更しておきます。
パワークエリエディター、取得と変換、コード表の読み込み
クエリのマージ、統合の種類を選ぶ
21.支店の方を開きます。
22. クエリのマージをクリックします。
23. マージというダイアログが表示されて上の段には支店読み込みが表示されています。そして、何をマージするかというと下の段では、コードを読見たいのでコードを選択します。
24. ポイントは、メインで使う支店の方は上にしています。表引きするコードの方は下にしています。
25.どこを対応させるのかということで、上ではコードを下では商品IDの列これが対応するので、それぞれを選択しておきます。
26. 統合の種類を選びます。説明では上下に並んでますけれども上の表ことを左、下の表を右と呼んでいます。（意味不明とかで怒らないで、）左外部の方を選びます。
27.これで支店読み込みとコードが結合されました。
28.テーブルとして結合はされましたが、まだ展開されていません。展開するという意味のアイコンをクリックして表示したい列を選びます。
29. 商品名と販売単価を選びます。OKをクリックします。
30. コードに対応する商品名と販売単価が支店読み込みで読み込んだ表に統合されました。
売上の列を追加する
31. 個数と販売単価が表示されている２列を選択した状態で、列の追加タブから、乗算を選択します。
32.乗算という名前の列が追加されました。 金額とでもしておきましょう。他の列も自動で作成された列名は変更しておきます。
列の配置も編ですので場所を入れ替えましょう。返還タブにある、任意の列グループから列の移動を選びます。
33. これで準備は整いましたので、表に書き出しましょう。今まではパワークエリのクエリエディタ内の処理でした。エクセルに読み込ませます。閉じて読み込むをクリックします。
34. するとクエリと接続というダイアログが表示されますから、今まで作業をしていた、支店読み込みを選んでダブルクリックします。
35. 読み込み先をクリックします。
36.データのインポートダイアログが表示されますから、どのように表示したいかを選びます今回はテーブルとして新規ワークシートに出力を選びます。
37.以上で各支店データと商品コードを統合した形で使いたい表を準備することができました。
38. 今回は、フォルダからブックをすべて選択して結合した支店読み込みと名付けたクエリに、コード表を記載したブックからコードを読み込んでコードというクエリ名をつけました。
次に２つのクエリをマージ（結合）させたわけです。
ポイントは、マージダイアログでの、どちらを上にするか、そして種類に何を選ぶかです。
解説にはポイントをわかりやすく説明しました。
よくわからない箇所は、ぜひ動画もご覧になり、確認してみてください。
お疲れさまでした。