スポンサーリンク
今回はExcelのPowerQueryで、コードと名称などを別々のExcelファイルやシート・・・というか別々のテーブル同士を関連付け結合して引っ張る方法を取り上げる。
データベースというやつの考え方がちょっと本格的に入ってくるので、PowerQueryを学習する人の中でも苦戦する人が多いところかと思う。
私もデータベースについては多分に自己流で学習してるので、あまり正確に分かっているとは言えない。
例題
マスタと一覧
下図は、
左側：商品マスタ
右側：不良品一覧
となっている。
マスタとかいう言葉に馴染みがない人には分かりにくいだろうが、今回の「商品マスタ」について言えば、全ての商品コードと商品名を1:1にしてまとめる用途のものだ。
そして「不良品一覧」は、不良品の商品コードと数量をまとめたものだ。
さて今回の「不良品一覧」には、商品コードがあっても商品名が書かれていない。
それは、商品名は「商品マスタ」というマスタにあるのだから、商品コードを元にマスタから引っ張ってくれば良いということだ。
これは、実際の業務でも本当によくあることで、なんとか「不良品一覧」に、商品マスタから商品名を引っ張ってきたいという場面が多い。
通常のExcelシートでなら、それはVLOOKUP関数とかが定番となるが、今回はそれを、PowerQueryにて実施する方法を扱う。
テーブルの結合
今回のような場合にPowerQueryで、コードに対応する名称をマスタから引っ張ってくるというのは、VLOOKUP関数に当たるような関数をPowerQueryでも使えば良いのか？
そうではなくて、テーブル(クエリ)の結合というものを使う。
テーブルの結合とか言われても、Microsoft Accessなどガチのデータベースというものを扱ったことがない人には、非常に馴染みにくく精神的ハードルが高いものかと思う。
それでも、使う結合処理のパターンは3つ以内というレベルなので、まずは決まった処理だけできるようになれば良いかと思う。
2つのファイルを開く
商品マスタ
ではまず、今回使用する「商品マスタ」Excelファイルをこちらからダウンロードして、自PCのどこかに保存する。そして
→「データの取得」
→「ファイルから」
→「ブックから」
として、同ファイルを開く。
今回のマスタはExcelの「テーブル」形式に変換した状態にしてあり「商品マスタTBL」という名前にしてある。
ナビゲーター画面の左端から、その「商品マスタTBL」をクリックし「データの変換」を押す。
不良品一覧
次に「不良品一覧」Excelファイルをこちらからダウンロードして、自PCのどこかに保存する(「商品マスタ」の保存場所と同じフォルダが良いが、別フォルダでも良い)。
ではPowerQueryに、商品マスタに加えて、この2つ目のファイルを追加する。
2つ目を追加するのは、幾つか方法はあるけど
→「新しいソース」
→「ファイル」
→「Excel」
とすれば良いだろう。
そして「不良品一覧」のExcelファイルを選択し開く。
不良品一覧についても「不良品一覧TBL」という名前のテーブルに変換した状態にしてあるので、ナビゲーター画面の左端から「不良品一覧TBL」をクリックし「OK」を押す。
これで「不良品一覧」もPowerQueryに読み込まれる。
クエリのマージ(結合)
「クエリのマージ」を起動
では、読み込んだ「商品マスタ」「不良品一覧」を相互に関連付ける。
今回は
1)「不良品一覧」の商品コードに対して
2)「商品マスタ」の商品名を結びつける
という処理をするので、順番としては「不良品一覧」がメインのテーブルとなる。
まず、左端の画面に表示されている「商品マスタ」「不良品一覧」のうち、メインとなる「不良品一覧」を選択しておく。
次に
リボン「ホーム」タブ
→「クエリのマージ」
とする。
ここでメニューが分岐するけど、「新規としてクエリをマージ」を選ぶと、既存の「商品マスタ」「不良品一覧」には全く手を加えず新たにテーブル(クエリ)を追加することになる。
今回は「不良品一覧」に上書きで商品名の列を追加する処理をしようと思うので、「クエリのマージ」を分岐メニューから選んでも良い。まあどちらでも良いということ。
照合列(キー)を結びつけ
次に「テーブルと照合列」を2つ選ぶ画面が出てくる。
上の方がメインとなるテーブルなので、ここにメインの「不良品一覧」を選んでおく。
次に下の方がサブとなるテーブルで、ここにサブの「商品マスタ」を選ぶ。
そして照合列というやつを、2つのテーブルから相互に選ばされるのだけど、これが一般にいう「キー」というやつ。
2つのテーブルを関連付けるキーということで、今回の場合は「商品コード」を選ぶ。
商品コードをキーにして、商品マスタから商品名を引っ張ってくるわけなので。
この照合列は複数選択もできるが、そうはならないように1つだけ選択するよう持っていくべきだろう。
キーが複数あるなら、それを結合してでも。
結合の種類
テーブルと照合列を選択したら、「結合の種類」というものを選ぶことになる。
外部結合とか内部結合とか、更には左外部・右外部とか、初めて見たらチンプンカンプンだろう。私も混乱させられる。
なんか6種類もあるけど、本当によく使うのは
- 左外部
- 内部
- 完全外部
くらいで、私はこれら以外まず使わない。
特に「左外部」が7割以上というところなので、まず「左外部」を最優先で覚えると良いかと思う。
各「結合の種類」がどのようなものなのかは、後述する。
サブテーブルのデータ展開
で、この「結合の種類」をどれか選んだら、次はサブとなるテーブルが折りたたまれた状態で右端に表示される。
上図における「商品マスタTBL」がそれで、データが「Table」と表示された状態になっている。
この折りたたまれたデータを展開するため、右端の四角ボタンを押す。
そしてどの項目を展開するか選択する画面になる。
「元の列名をプレフィックスとして使用します」のチェックは、通常は外せば良い。
これのチェックを入れると、項目名が長ったらしくなるので。
そして必要な項目を選択したら「OK」を押せば、「結合の種類」に対応するデータが表示される。
「最初の行」「2番目の行」
さてこの「結合の種類」、カッコ内の説明書きに「最初の行」とか「2番目の行」とかあるけど、実はこれらは一般にいう1行目・2行目という意味ではない。
テーブルを2個選ばされてるけど、それらのうち
「2番目の行」→下の方(2番目に選んだ方)のテーブルのデータ ということだ。 それでは6つある「結合の種類」について、それぞれどう違うのか書いていくが、メインとなる「左外部」「内部」「完全外部」について詳しく述べ、後は軽く流す。 元データについても図を再掲する。 選択肢の一番上にあるだけのことはあり、これが断トツでよく使う。 カッコ内の説明に「最初の行すべて、および2番目の行のうち一致するもの」とある。 今回の場合は、メインの「不良品一覧」テーブルにあった4行のデータは、必ず表示される。 そして「不良品一覧」の商品コード「H500」については「商品マスタ」中に存在していないが、それは「商品名」列がnull表示されるという結果で返されている。 とにかくメインのテーブルのデータは何が何でも残したいということで、最もよく使われる結合方法だろう。 照合の結果、「メインテーブルのキー」と「サブテーブルのキー」が合致したデータだけを表示するものだ。 今回の場合、「不良品一覧」の商品コード「H500」が「商品マスタ」中に存在していないわけだが、それを踏まえて商品コード「H500」の行は表示されない。 マスタに存在しないものは不備データ扱いで表示しないなど、これもそこそこ使う方法だ。 とにかく2つのテーブルのデータを、どっちも全て表示する方法だ。 下図のように、「商品マスタ」「不良品一覧」のどちらか一方にでもデータがありさえすれば、それらが全て表示される。 「左外部」の逆で、サブテーブルにあるデータだけは無条件で全て表示するというもの。 最初から「左外部」でやるよう持っていくべきで、あまり使い所はないかと。 「メインテーブルにあってサブテーブルには無いデータ」だけを抽出するもの、らしい。 マスタ情報の不足など調べるに当たって、そういうことをやる場面もあるにはあるだろう。 無理にこんな「左反」なんて使う必要もないと思うが。 「サブテーブルにあってメインテーブルには無いデータ」だけを抽出する。 スポンサーリンク
いやはや分かりにくい。各「結合の種類」の違い
左外部
「左」という言葉は気にしなくて良いだろう。
つまり「最初の行」＝メインテーブルのデータは、無条件に全て表示するということ。
内部
完全外部
右外部
左反
右反
