需要はあるのに、Excelだと面倒だった複数表の結合。
これまで関数やVBAで頑張っていた人も多いのでは？
はい！！私もそうでした。
そう、Power Queryを知るまではね。
ということ複数表の結合もでPower Queryを使えば、簡単にできてしまいます。
複数の表の連結も簡単ッ！
１つのブックに２つの表があるとして、この表を連結してみたいと思います。
キーは番号です。
SheetA
SheetB
データに意味はありません。
まず、全く関係のない、別新規ブックを開きます。
そして、新規ブックでデータの取得を行います。
「データ」タブ→「データの取得」→「ファイルから」→「ブックから」と辿っていきます。
ナビゲーター画面が出るので、「複数のアイテムの選択」にチェックをいれ、結合したいシートにチェックをいれます。
今回はSheetAとSheetBの２つをチェックします。
そして、「データの変換」をクリックします。
データの読み込みが完了すると左ペインのクエリ欄に２つのクエリが表示されていることが確認できます。
ではこの２つのクエリを結合してみましょう。
Power Queryでは表を結合することを「マージ」と呼んでいます。
結合の意味ですね。
エディターのホームタブの「クエリのマージ」→「新規としてクエリをマージ」を選択します。
するとマージのウィザードが出てきます。
結合するシートを選択します。
そして、キーとなる列をクリックします。
ここでポイントとなるのが、「結合の種類」です。
データベースをかじったことがある人ならすんなり理解できますが、普通の人に難解な言葉が並んでいます。
各選択肢が何を意味するかは、後述すとして、先に進みましょう。
今回の例では、SheetBの行全てに対して、SheetAを結合したいので、「右外部」を選択します。
ＯＫを押すと
新しいクエリが完成しました。
の列が意味不明なので、右側の展開アイコンをクリックします。
すると、どの項目を展開するか選択することができます。
この例では「番号」は不要なのでチェックを外します。
ＯＫを押すと
２つの表が番号をキーとして結合され、いい感じに名言一覧が完成しましたッ！！
番号と名前はSheetAのレコードで、そこに番号が同じSheetBのレコードがくっついた形です。
結合の種類について
結合の種類について見てみます。
結合の種類は全部で６種類あります。
左外部
左外部は、「最初の行すべて、および2番目の行のうち一致するもの」を結合します。
ここで言う「最初」とは、結合対象の最初に指定したシートのことです。
「最初の行すべて、および2番目の行のうち一致するもの」なので、SheetAは全てに対して、2番目のシートを結合する、という意味ですね。
試しに先ほどのシートを左外部で連結してみると・・・
さっきと同じですね・・・。
つまりさっきのは左外部でも右外部でも同じことになったんですね。
外部結合の外部とは、結合対象のレコードが存在しない場合、Nullのレコードを増やして連結することです。
例えば、AというテーブルとＢというテーブルを結合したいとします。
Aテーブルはキーが１～３のレコードがあります。
Bテーブルにはキーが1と2のレコードしかありません。
AとBを連結するときに、Aテーブルのレコードが消滅してしまっては都合が悪く、
Aテーブルのレコード全て残した状態で連結したいとします。
基準となるAテーブルのレコードをBテーブルと連結する場合、外部結合だと、レコードが足りないBテーブルに空のレコードを追加して連結します。
後述する内部結合との違いは、「勝手にレコードを作って連結する」ということです。
実際に例を見てみましょう。
outer_A.xlsx
outer_B.xlsx
Aには3レコードありますが、Bには2レコードしかありません。
外部結合（ここでは左外部）を使えば、Aブックのレコードを消すことなく、連結することができます。
番号3はのセリフは、nullになりました。
空白と同義と考えて差支えありません。
注目してもらいたいのは、左側の番号と名前、つまりブックAのレコードはそのまま残り、
ブックBのレコードがnullで追加されている点です。
このように外部結合だと、基準となる表のレコードを全て残し、結合相手にレコードが存在しない場合、
空のレコードを作成して連結します。
右外部
右外部は、上述の左外部の２つ目の表を基準にしたバージョンです。
最初と２番めのどちらの表を基準にしたいかで使い分けます。
完全外部
両方の行を全て結合します。
最初と2番目のレコードで結合できるものは結合しますし、結合するものがなくても、相手方をNullとして結合します。
A表と
B表があるとします。
完全外部結合すると・・・
番号1のレコードはBのレコード分作成され、番号2のレコードはAにしかないのでセリフはNullで作成されました。
内部
内部結合は、外部の反対です。
一致するレコードのみが結合されます。
外部結合のシートを内部結合してみると・・・
A表の番号２のレコードは結合相手がいないので、消滅しました。
左反
左反は最初のテーブルにあって、２番目にないレコードを抽出する結合です。
「反」とは反するの意味でしょうか。否定の意味と考えられますが。。。
もうちょっとわかりやすくして欲しかった・・・。
先ほどの例で左反で結合すると
Aには番号２のレコードがありますが、Bにはないので番号２だけが抽出されました。
右反
右反は左反の逆で、最初のテーブルになくて、２番目にあるレコードを抽出する結合です。
Aには番号１、２のレコードがありますが、Bには番号２だけしかないので、レコードは抽出されませんでした。
さきほどは、A、Bの順番で指定しましたが、今度はAとBの指定を逆にしてみます。
左反と同じ状態ですので、同じ結果になりました。
別のブックのデータを結合する
さきほどの例では同じブックにシートがありました。
今度は別のブックにあるシートを結合してみます。
渾身のbakiブックが２つになってしまいました。
シートは先ほどの例と同じです。
baki_A.xlsx
baki_B.xlsx
この二つを番号をキーとして結合を試みます。
新しくExcelブックを開いて、データタブ→データの取得→ファイルから→ブックから
と進んでいきます。
ファイル選択のダイアログが出てきました。
ここで複数ファイルを選択！！
・・・しようとしましたが、それはできませんでした。
インポートをクリックすると、ナビゲーターが出てくるので、データの変換をクリックします。
するといつものようにPower Queryエディターが立ち上がります。
次は別のブックからSheetBを読み込む必要があるので、エディターのホームタブ→新しいクエリ→新しいソース→ファイル→Excelをクリックします。
データの取り込みダイアログが現れるので、今度はbaki_B.xlsxを選択します。
ナビゲーターが現れるのでSheetBを選択します。
すると左ペインに２つめのSheetBクエリが作成されました！！
あとはさっきと同じように「クエリのマージ」をすれば・・・
こうして・・・
こうだーー！！
あっけねぇぇぇ・・・
セリフのところを展開すれば・・・
完成！
複数表にまたがる結合も簡単にできました。
結合しようとしたらデータの型エラーが出た
読み込まれた表の各項目のデータ型は、自動検出のようです。
そのため、数字と文字列が混在している場合は注意が必要です。
読込の際、Power Queryがデータ型を数字として認識してしまうとデータ型のエラーが出ます。
そうすると先に進めなくなってしまいます。
そのときは、データの型を指定してあげることで回避することができます。
終わりに
表の連結は、連結の方法など、データベース的な考え方が求められます。
多少とっつきにくい部分もありますが、そこまで難しいものではないので、ぜひチャレンジしてもらいたいと思います。
もう少しPower Queryちゃんとの戯れは続きます。
ここまでお読みいただきありがとうございました。
