適材適所

WindowsやPowerShellやネットワーク、IBMなどのシステム系の話など気になったことも載せているブログです。

【Excel Power Query入門】2つの表を結合する

需要はあるのに、Excelだと面倒だった複数表の結合。

これまで関数やVBAで頑張っていた人も多いのでは?

はい!!私もそうでした。

そう、Power Queryを知るまではね。

ということ複数表の結合もでPower Queryを使えば、簡単にできてしまいます。

複数の表の連結も簡単ッ!

1つのブックに2つの表があるとして、この表を連結してみたいと思います。

キーは番号です。

SheetA

f:id:shinmai_papa:20200915150942p:plain

SheetB

f:id:shinmai_papa:20200915150945p:plain

データに意味はありません。

まず、全く関係のない、別新規ブックを開きます。

そして、新規ブックでデータの取得を行います。

「データ」タブ→「データの取得」→「ファイルから」→「ブックから」と辿っていきます。

f:id:shinmai_papa:20200915150949p:plain

ナビゲーター画面が出るので、「複数のアイテムの選択」にチェックをいれ、結合したいシートにチェックをいれます。

今回はSheetAとSheetBの2つをチェックします。

f:id:shinmai_papa:20200915150953p:plain

そして、「データの変換」をクリックします。

f:id:shinmai_papa:20200915150958p:plain

データの読み込みが完了すると左ペインのクエリ欄に2つのクエリが表示されていることが確認できます。

f:id:shinmai_papa:20200915151011p:plain

ではこの2つのクエリを結合してみましょう。

Power Queryでは表を結合することを「マージ」と呼んでいます。

結合の意味ですね。

エディターのホームタブの「クエリのマージ」→「新規としてクエリをマージ」を選択します。

f:id:shinmai_papa:20200915151002p:plain

するとマージのウィザードが出てきます。

f:id:shinmai_papa:20200915151006p:plain

結合するシートを選択します。

そして、キーとなる列をクリックします。

ここでポイントとなるのが、「結合の種類」です。

データベースをかじったことがある人ならすんなり理解できますが、普通の人に難解な言葉が並んでいます。

各選択肢が何を意味するかは、後述すとして、先に進みましょう。

今回の例では、SheetBの行全てに対して、SheetAを結合したいので、「右外部」を選択します。

OKを押すと

f:id:shinmai_papa:20200915151018p:plain

新しいクエリが完成しました。

f:id:shinmai_papa:20200915151021p:plainの列が意味不明なので、右側の展開アイコンをクリックします。

すると、どの項目を展開するか選択することができます。

f:id:shinmai_papa:20200915151015p:plain

この例では「番号」は不要なのでチェックを外します。

OKを押すと

f:id:shinmai_papa:20200915151026p:plain

2つの表が番号をキーとして結合され、いい感じに名言一覧が完成しましたッ!!

番号と名前はSheetAのレコードで、そこに番号が同じSheetBのレコードがくっついた形です。

結合の種類について

結合の種類について見てみます。

結合の種類は全部で6種類あります。

左外部

f:id:shinmai_papa:20200915151128p:plain

左外部は、「最初の行すべて、および2番目の行のうち一致するもの」を結合します。

ここで言う「最初」とは、結合対象の最初に指定したシートのことです。

f:id:shinmai_papa:20200915151145p:plain

「最初の行すべて、および2番目の行のうち一致するもの」なので、SheetAは全てに対して、2番目のシートを結合する、という意味ですね。

試しに先ほどのシートを左外部で連結してみると・・・

f:id:shinmai_papa:20200915151149p:plain

さっきと同じですね・・・。

つまりさっきのは左外部でも右外部でも同じことになったんですね。

外部結合の外部とは、結合対象のレコードが存在しない場合、Nullのレコードを増やして連結することです。

例えば、AというテーブルとBというテーブルを結合したいとします。

Aテーブルはキーが1~3のレコードがあります。

Bテーブルにはキーが1と2のレコードしかありません。

AとBを連結するときに、Aテーブルのレコードが消滅してしまっては都合が悪く、

Aテーブルのレコード全て残した状態で連結したいとします。

基準となるAテーブルのレコードをBテーブルと連結する場合、外部結合だと、レコードが足りないBテーブルに空のレコードを追加して連結します。

f:id:shinmai_papa:20200915151154p:plain

後述する内部結合との違いは、「勝手にレコードを作って連結する」ということです。

実際に例を見てみましょう。

outer_A.xlsx

f:id:shinmai_papa:20200915151157p:plain

outer_B.xlsx

f:id:shinmai_papa:20200915151210p:plain

Aには3レコードありますが、Bには2レコードしかありません。

外部結合(ここでは左外部)を使えば、Aブックのレコードを消すことなく、連結することができます。

f:id:shinmai_papa:20200915151204p:plain

番号3はのセリフは、nullになりました。

空白と同義と考えて差支えありません。

注目してもらいたいのは、左側の番号と名前、つまりブックAのレコードはそのまま残り、

ブックBのレコードがnullで追加されている点です。

このように外部結合だと、基準となる表のレコードを全て残し、結合相手にレコードが存在しない場合、

空のレコードを作成して連結します。

右外部

右外部は、上述の左外部の2つ目の表を基準にしたバージョンです。

最初と2番めのどちらの表を基準にしたいかで使い分けます。

f:id:shinmai_papa:20200915151125p:plain

完全外部

両方の行を全て結合します。

最初と2番目のレコードで結合できるものは結合しますし、結合するものがなくても、相手方をNullとして結合します。

f:id:shinmai_papa:20200915151139p:plain

A表と

f:id:shinmai_papa:20200915151207p:plain

B表があるとします。

f:id:shinmai_papa:20200915151217p:plain

完全外部結合すると・・・

f:id:shinmai_papa:20200915151221p:plain

番号1のレコードはBのレコード分作成され、番号2のレコードはAにしかないのでセリフはNullで作成されました。

内部

内部結合は、外部の反対です。

一致するレコードのみが結合されます。

f:id:shinmai_papa:20200915151135p:plain

外部結合のシートを内部結合してみると・・・

f:id:shinmai_papa:20200915151213p:plain

A表の番号2のレコードは結合相手がいないので、消滅しました。

左反

左反は最初のテーブルにあって、2番目にないレコードを抽出する結合です。

「反」とは反するの意味でしょうか。否定の意味と考えられますが。。。

もうちょっとわかりやすくして欲しかった・・・。

f:id:shinmai_papa:20200915151131p:plain

先ほどの例で左反で結合すると

f:id:shinmai_papa:20200915151224p:plain

Aには番号2のレコードがありますが、Bにはないので番号2だけが抽出されました。

右反

右反は左反の逆で、最初のテーブルになくて、2番目にあるレコードを抽出する結合です。

f:id:shinmai_papa:20200915151142p:plain

Aには番号1、2のレコードがありますが、Bには番号2だけしかないので、レコードは抽出されませんでした。

f:id:shinmai_papa:20200915151227p:plain

さきほどは、A、Bの順番で指定しましたが、今度はAとBの指定を逆にしてみます。

f:id:shinmai_papa:20200915151230p:plain

左反と同じ状態ですので、同じ結果になりました。

別のブックのデータを結合する

さきほどの例では同じブックにシートがありました。

今度は別のブックにあるシートを結合してみます。

渾身のbakiブックが2つになってしまいました。

f:id:shinmai_papa:20200915151030p:plain

シートは先ほどの例と同じです。

baki_A.xlsx

f:id:shinmai_papa:20200915151040p:plain

baki_B.xlsx

f:id:shinmai_papa:20200915151034p:plain

この二つを番号をキーとして結合を試みます。

新しくExcelブックを開いて、データタブ→データの取得→ファイルから→ブックから

と進んでいきます。

ファイル選択のダイアログが出てきました。

ここで複数ファイルを選択!!

・・・しようとしましたが、それはできませんでした。

f:id:shinmai_papa:20200915151050p:plain

インポートをクリックすると、ナビゲーターが出てくるので、データの変換をクリックします。

f:id:shinmai_papa:20200915151044p:plain

するといつものようにPower Queryエディターが立ち上がります。

次は別のブックからSheetBを読み込む必要があるので、エディターのホームタブ→新しいクエリ→新しいソース→ファイル→Excelをクリックします。

f:id:shinmai_papa:20200915151054p:plain

データの取り込みダイアログが現れるので、今度はbaki_B.xlsxを選択します。

ナビゲーターが現れるのでSheetBを選択します。

f:id:shinmai_papa:20200915151057p:plain

すると左ペインに2つめのSheetBクエリが作成されました!!

f:id:shinmai_papa:20200915151109p:plain

あとはさっきと同じように「クエリのマージ」をすれば・・・

こうして・・・

f:id:shinmai_papa:20200915151120p:plain

こうだーー!!

f:id:shinmai_papa:20200915151101p:plain

あっけねぇぇぇ・・・

f:id:shinmai_papa:20200915151104p:plain

セリフのところを展開すれば・・・

f:id:shinmai_papa:20200915151116p:plain

完成!

複数表にまたがる結合も簡単にできました。

結合しようとしたらデータの型エラーが出た

読み込まれた表の各項目のデータ型は、自動検出のようです。

そのため、数字と文字列が混在している場合は注意が必要です。

読込の際、Power Queryがデータ型を数字として認識してしまうとデータ型のエラーが出ます。

そうすると先に進めなくなってしまいます。

そのときは、データの型を指定してあげることで回避することができます。

終わりに

表の連結は、連結の方法など、データベース的な考え方が求められます。

多少とっつきにくい部分もありますが、そこまで難しいものではないので、ぜひチャレンジしてもらいたいと思います。

もう少しPower Queryちゃんとの戯れは続きます。

ここまでお読みいただきありがとうございました。

M式言語で2つの表を結合する

【Power Query M式言語】2つの表を結合する - 適材適所