VLOOKUP や XLOOKUP などの関数を使っていて、次のような悩みを抱えたことは無いでしょうか？
- データが大量になって 動作が重い
- データを追加するたびに 関数式を追加するのが面倒
- 関数式の コピーミス でデータがおかしくなった
- 別ファイル のデータを参照すると不安定になる
VLOOKUP などの関数式は小規模のデータや、一時的な活用であれば非常に便利ですが、ルーチンワークで大量のデータを処理するのにはあまり向きません。
データ処理に特化した Power Query なら、テーブル結合のこのような課題は一挙に解決が可能です。
- 大量のデータ でも全て自動でデータを結合
- 自動なので ミスはゼロ
- 別ファイルのデータとの 連携もスムーズ
皆さんもPower Query のテーブル結合機能を使ってデータ処理業務の効率を上げていきましょう。
Power Queryにおける「テーブル結合」とは何か
結合は「横に広げる」操作
「テーブル結合」はテーブルのデータを横につないで広げる操作です。
VLOOKUPのように、別テーブルのデータを検索キーで取得します。
例えば、社員番号 だけがテーブルに入っていて、別のテーブルに社員のデータ があった場合、社員の氏名や所属情報を取得してそのテーブルの列に加えるという操作になります。
「結合」または「マージ」と呼ぶ
実は Power Query のメニューリボンには「結合」というボタンがありません。
Power Query では「結合」のことを「マージ」とも呼ぶので覚えておきましょう。
一方でクエリウィンドウでクエリを右クリックすると「結合」というメニューが出てきます。少し混乱してしまいますね。
押さえておくべき基礎知識
結合に必要な「キー列」とは
データを結合するには「キー列」が必要です。例えば前の項にも出てきた「社員番号」などを「キー列」とすることができます。
社員の活動などの情報を記録する場合、「社員番号」以外に「氏名」や「所属」などの情報もセットで記録するのは無駄が多いです。
何故ならこれらの情報は「社員番号」が分かれば必ず一意に定まる情報なので、情報が冗長 になってしまうためです。
例えば 社員の勤怠の記録 をデータで残す場合、勤怠時間のデータと「社員番号」だけを記録し、「氏名」や「所属」などの情報は別に保管しておく方が情報が スッキリ整理 ができます。
データを分割整理するメリット
スッキリ整理ができるという感覚的なメリットのみならず、以下のメリットがあります。
- 「所属」が変更になった場合、全てのデータを修正する必要が無い
- 分散したデータに 誤りが混入する可能性を回避 できる
- 全体の データ量が節約 できる
このようにデータを分けて整理することを、「データの正規化」 と呼びます。
データ型と表記ゆれ
データを結合する場合、「キー」の値が同一 のデータが結合されます。
しかし、人間の目で見て 「同じ」 に見える値でもコンピューターで処理すると 「違う」 値として扱われるケースがあります。
データ型の違い
例えば “123” という値があった場合でも、文字列 で数字が3文字並んだ値である場合と、数値 の値である場合とがあります。
文字列 や 数値 というのをデータ型といってこの型が異なると値は全く異なったものになるので注意が必要です。
全角／半角
例えば同じ数字の 「A」 という文字を表現する場合でも、全角と半角で異なるという問題があります。
全角と半角の違いから結合ができないケースがありますので、データの作成時には注意が必要です。
スペース
例えば氏名の値の場合でも、「山田太郎」 と 「山田 太郎」 とで姓と名の間のスペースの有無で違いがあります。
これもルールを統一しておかないと、データが結合できないというトラブルが発生する可能性があります。
結合の種類を理解する
続いてはテーブル結合の種類です。テーブル結合にはいくつか種類がありますので、ここで理解しておきましょう。
これらの種類は似ているようで全く違う結果になる場合がありますので、ここで正しい知識を得ておきましょう。
内部結合（Inner Join）
２つのテーブルを結合する場合に、両方のテーブルに同じ 「キー」値 がある場合のみを出力する結合です。
必ず 「キー」値 が一致する場合や、 「キー」値 が一致しないデータは不要である場合にはこの 内部結合 を使います。
外部結合（Left / Right Outer Join）
例えば「注文」のデータと「店舗」データを結合する場合、片方の「注文」のデータは大切なので消えてしまっては困ります。
万一片方の「店舗」のデータが無くても「注文」のデータは出力されるようにするのが外部結合です。
このように 重要なデータが片方にある ようなケースでは外部結合を使います。
一般的には大切なデータから順に「注文」データ、「店舗」データと左から並べて表現するので、これを 「左外部結合」 と呼びます。
Power Query のデータの結合もデフォルトが 「左外部結合」 になっています。
完全外部結合
「左外部結合」 は片方のデータは必ず出力されますが、もう片方のデータは 「キー」値 が一致するものが無ければ出力されません。
しかし、どちらのデータも大切なので出力したいというケースがあります。
このケースで使うのが 「完全外部結合」 です。
クロス結合
最後のご紹介する 「クロス結合」 は 「キー」値 は不要の結合です。
これは２つのテーブルの各行の組み合わせを全て出力するという結合です。
この結合を使うのは注意が必要です。何故なら大量のデータ同士を結合すると 一気にデータ量が爆増 してコンピュータがフリーズしかねないためです。
例えば 10,000 行 ある２つのテーブルをクロス結合すると、一気に 10,0000 x 10,000 = 100,000,000 行 のデータが生成されてしまいます。
では、この 「クロス結合」 はどういうケースで使えば良いのでしょうか？
例えば一つの注文のデータに対して 「注文元」「受注担当者」「受注日」「納期」 という4つの項目がついたテーブルを作成したいような場合に使います。
片方は注文のデータ、もう片方は単純に 「注文元」「受注担当者」「受注日」「納期」と４行しかないデータを準備して、これをクロス結合すると上記のデータを作成することができます。
このように片方のデータは少ない行数で定型のデータテーブルを生成するのには 「クロス結合」 を活用します。
Power Queryでの結合操作手順
ではいよいよ Power Query でテーブル結合をやってみましょう。
サンプルファイルを例に操作手順を解説しますので、ご自分でも操作して確かめて頂けますのでよろしければダウンロードしてみて下さい。
結合対象となるクエリの準備
まず結合する２つのテーブル（クエリ）が必要です。Excel ファイルのシート上に作成した「テーブル」からクエリを作成するとか、別のExcel ファイルから読み込むなどをして、クエリを２つ作成します。
サンプルファイルでは同じファイル内に２つの「テーブル」が作成されている状態です。
以下の操作をして、それぞれのテーブルからクエリを作成します。
- テーブル内のどれかのセルを選択
- メニューから 「データ」＞「テーブルまたは範囲から」 をクリック
- Power Query エディター が開くのでメニューから 「ホーム」＞「閉じて読み込む」のプルダウン＞「閉じて次に読み込む」
- データのインポート ウィンドウが開くので 「接続の作成のみ」 を選択して 「OK」ボタン を押す
- サイドウィンドウが開きテーブル名と同じ クエリが作成される
クエリの結合（Merge）の操作
ここまでで 「勤怠データ」 と 「社員データ」 という２つのクエリが作成されていると思います。
「勤怠データ」 には社員番号はありますが、氏名や所属の情報がありません。これを 「社員データ」 のテーブルと結合することで全ての情報が入ったデータを作成することにします。
ここまでの解説の通りテーブルの結合には 「キー」 が必要ですが、このケースでは 「社員番号」が「キー」 になります。
出力するデータは情報が追加されるものの基本的には 「勤怠」 のデータになるので、「勤怠データ」 のクエリに 「社員データ」 を結合するようにデータを処理していきます。
次のように操作をします。
マージウィンドウでOKボタンを押すと Power Query エディターウィンドウに戻ります。
続いて展開する列を選択します。
結合の「キー」列は複数でもOK
ここまでの説明ではテーブルを結合するための「キー」列は1つだけでした。しかし、「キー」列は複数設定することも可能です。
例えばスーパーなどで同じ商品でも 日によって価格を変えている ような場合は単純に 商品IDだけでは 価格は定まりません。
このような場合は商品IDと日付と共に価格を記録したテーブルを作成しておけば正しい価格を取得することができます。
複数の「キー」列を指定する場合は１つ目のテーブルと２つ目のテーブルの「キー」列の選択を同じ順番で行う必要がありますので、注意して下さい。
あいまい結合の使いどころ
あいまい結合とは何か
テーブル結合は通常では キー値が完全に一致 している場合にのみ結合処理されます。
しかし、例えば 「山田商事株式会社」 と 「山田商事（株）」 と担当者の入力の仕方の違いがあるデータも同じとして扱いたいケースもあると思います。
このようなケースでもテーブル結合ができるのが 「あいまい結合」 です。
通常の結合では図のように 完全に一致 する行だけが結合されます。
あいまい結合の使い方
あいまい結合の使い方は簡単です。図のように結合の設定ウィンドゥで 「あいまい一致を使用してマージを実行する」 にチェックを入れるだけで使えます。
「あいまい一致」 オプションを選択して結合した場合は図のようになります。
しかし、よく見ると１行だけ結合できていないのが分かると思います。これは 類似の度合い が一定レベル以下で、Power Query の処理で 一致するとは判定されなかった 結果です。
「山田商事株式会社」と「山田商事」を比較すると文字数が４文字と８文字で２倍になっていて、これが原因で一致と判定されなかったと思われます。
このケースでも結合させるためには図のように 「類似性のしき値」 を 0 ～ 1 の間の数値で設定します。
適当に値を入れると図のように一致する行数が表示されるので、この値を調整することで全ての行を一致させて結合することが可能です。
しきい値に 0.5 を入力して結合すると図のように全ての行が結合されました。
実務で使う際の注意点
このあいまい結合は一度上手く動いたとしても、この先に新たに追加されたデータに対して上手く動くという保証はありません。
条件が変わって今まで正しく結合されていたデータが別のデータと結合されるという可能性があるからです。
ですから一度だけデータを再整備する目的で使うなど、利用する場合は注意が必要です。
やはり元のデータを正しく作るというのが基本原則です。
例えば データの入力規制 を設定してリストから選択するような仕様にするなどで問題となるデータの混入を回避します。
実務で失敗しない結合設計の考え方
「テーブル結合」はなるべく後で行う
テーブル結合はクエリ作成の終盤に行うのが鉄則です。
結合するそれぞれのクエリをしっかり整えてから結合します。
その理由は次のようなものになります。
不完全なクエリ同士を結合すると修正が大変
結合キーの揺らぎの修正やデータ重複の削除などの処理をしておかないと、結合により増えたデータを修正することになり手間ば倍増してしまいます。
クエリ再利用の価値が低下
テーブルを結合した後でデータの修正をした場合、その修正は結合後のデータのみで有効です。
テーブル（クエリ）は他のデータと結合して再利用するというケースもありますが、テーブルに修正が適用できていないと結合後に再びデータの修正が必要となってしまいます。
これではクエリの再利用の価値が低下してしまいます。
パフォーマンスの低下
テーブル結合後はデータ量が増大します。増大後にデータを修正すると当然処理負荷が高くなり結果的にPCの動作が重くなってしまうなどパフォーマンス低下の問題を引き起こしてしまいます。
クエリのメンテナンス性が低下
データの修正やテーブルの結合などの処理の順序がバラバラだったり、前後していたりすると、後でクエリの修正が必要になった際に修正すべき箇所が分からなくなるなどの問題が発生する可能性があります。
まとめ
VLOOKUP などの関数は増え続けるデータと使おうとすると作業負担が大きかったり、作業ミスのリスクが増大したり、問題が多くなります。
やはり大量データは Power Query で処理すべきです。
テーブル結合も Power Query で自動的に処理することで作業負担や作業ミスのリスクの問題も解消できます。
この記事で紹介したように Power Query によるテーブルの結合はほとんど マウス操作のみで完了 してしまい、関数入力よりむしろ簡単かもしれません。
このような非常に有用な Power Query のテーブル結合、皆さんも是非この記事で得た知識で挑戦してみて下さい。
