Excel PowerQuery 検索列を作る~テーブル結合~

Microsoft Excel

スポンサーリンク

今回は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つのテーブルのデータを、どっちも全て表示する方法だ。

下図のように、「商品マスタ」「不良品一覧」のどちらか一方にでもデータがありさえすれば、それらが全て表示される。

右外部

「左外部」の逆で、サブテーブルにあるデータだけは無条件で全て表示するというもの。

最初から「左外部」でやるよう持っていくべきで、あまり使い所はないかと。

左反

「メインテーブルにあってサブテーブルには無いデータ」だけを抽出するもの、らしい。

マスタ情報の不足など調べるに当たって、そういうことをやる場面もあるにはあるだろう。

無理にこんな「左反」なんて使う必要もないと思うが。

右反

「サブテーブルにあってメインテーブルには無いデータ」だけを抽出する。

スポンサーリンク