今回は、ExcelのPower Queryのマージ結合で表をつなぐ方法を紹介します。
Power Queryのマージ結合とは
Excelで複数の表を扱うとき、商品コードや社員番号、顧客IDなどをもとに別の表から情報を取り込みたい場面があります。ワークシート関数で検索する方法もありますが、定期的に同じ処理をするならPower
Queryのマージ結合が便利です。
Excel Power Query
マージ結合を使うと、共通する列をキーにして2つのテーブルをつなげられます。売上一覧に商品マスターのカテゴリを追加する、社員一覧に部署マスターの部署名を加える、問い合わせ一覧に顧客情報を結合する、といった使い方ができます。
Power Queryで設定しておけば、元データを差し替えて更新するだけで同じ結合処理を再利用できます。毎月の集計や定期レポートで、手作業のコピーや関数入力を減らしたいときに向いています。
結合前にキー列を確認する
マージ結合では、2つの表に共通するキー列が必要です。キー列がそろっていないと、結合結果に空欄が出たり、想定より行が増えたりします。
- 商品コード
- 社員番号
- 顧客ID
- 部署コード
- 注文番号
キー列は、見た目だけでなくデータ型もそろえます。片方が数値、片方が文字列になっていると、同じ値に見えても一致しないことがあります。先頭ゼロが必要なコードは文字列として扱います。
余分な空白を取り除く
キー列の前後に空白があると、同じコードでも別の値として扱われることがあります。Power Queryで結合する前に、トリミングで余分な空白を削除します。
CSVや外部システムから取り込んだデータでは、見えない空白や表記ゆれが混ざることがあります。結合結果が合わないときは、キー列の空白、全角半角、データ型を確認します。
マージ結合の基本手順
Power Queryでは、結合したい2つのクエリを用意し、マージ操作でキー列を指定します。結合後は、必要な列だけを展開します。
- 結合したい表をPower Queryに読み込む
- 各クエリの列名とデータ型を整える
- 基準にするクエリを開く
- 「クエリのマージ」を選ぶ
- 結合相手のクエリとキー列を指定する
- 結合の種類を選ぶ
- 展開ボタンで必要な列だけを追加する
売上一覧に商品マスターを結合する場合は、売上一覧を基準にし、商品コードで商品マスターを結合します。展開時には、商品名やカテゴリなど必要な列だけを選びます。すべての列を展開すると、不要な情報が増えて後の処理が分かりにくくなります。
列名の重複に注意する
展開した列名が既存の列名と重なる場合、列名に接頭辞が付くことがあります。後で見やすいように、展開後の列名を整理します。
「商品マスター.カテゴリ」のような名前でも意味は分かりますが、最終的な出力では「カテゴリ」に変更したほうが使いやすい場合があります。複数のマスターを結合する場合は、どの情報か分かる列名にします。
結合の種類を使い分ける
Power Queryのマージ結合には、複数の結合種類があります。実務でよく使うのは、基準表の行を残して相手表の情報を追加する左外部結合です。
- 左外部結合
- 内部結合
- 完全外部結合
- 左反結合
- 右外部結合
売上一覧を基準に商品情報を追加するなら、左外部結合が分かりやすい方法です。商品マスターに存在しないコードが売上一覧にあった場合、追加列が空欄になります。これにより、未登録コードを発見できます。
未一致データを確認する
結合後に空欄が出る場合、キーが一致していない可能性があります。未一致の行を抽出し、元データのコードが正しいか、マスターに登録漏れがないかを確認します。
未一致をただ空欄のままにすると、集計時に分類なしのデータが増えます。確認用クエリを作り、未一致だけを一覧にすると管理しやすくなります。
結合後の整形
マージ結合で列を追加した後は、不要な列を削除し、列名やデータ型を整えます。結合前のキー列と展開後の列が重複している場合も確認します。
- 不要な展開列を削除する
- 列名を分かりやすくする
- データ型を設定する
- 未一致の空欄を確認する
- 集計に使う列だけを残す
Power Queryのステップは後から見直せるため、処理の順番も大切です。キー列の整形、結合、展開、列名変更、型設定のように、流れが分かる順番にします。
更新しやすい運用にする
マージ結合を定期的に使う場合は、元データとマスター表の更新ルールを決めます。売上一覧だけ更新して商品マスターが古いままだと、未一致が増えることがあります。
- マスター表の更新担当を決める
- キー列の形式を固定する
- 未一致の確認手順を作る
- 取込用ファイル名をそろえる
- 不要な列を増やさない
マージ結合は便利ですが、キー列の品質に結果が左右されます。マスター表の重複や空白がないか、更新のたびに確認します。
結合結果が正しいかどうかは、元データとマスターの整い方に依存します。Power
Queryの設定だけでなく、データ管理のルールも合わせて整えることが重要です。
結合前後の件数を確認する
マージ結合では、結合後の行数が想定どおりか確認します。左外部結合なら、基本的には基準にした表の行数が保たれます。結合後に行数が増えている場合は、結合相手のマスター表でキーが重複している可能性があります。
集計に使う前に、元の明細件数、結合後の件数、未一致の件数を確認する流れを作ると安全です。マスター側の重複は見落としやすいため、キー列の重複チェックを定期的に行います。結合処理を自動化しても、件数確認は省かないほうが結果を信頼しやすくなります。
まとめ
ExcelのPower Queryのマージ結合を使うと、共通キーをもとに複数の表をつなげられます。売上一覧に商品マスターを追加する、社員一覧に部署名を加えるなど、定期的なデータ整形に向いています。
結合前には、キー列のデータ型、空白、表記ゆれを確認します。結合後は、必要な列だけを展開し、未一致データを確認します。
Excel Power Query
マージ結合は、検索関数の代わりとしてだけでなく、更新しやすいデータ加工の仕組みとして使えます。マスター表とキー列を整え、確認用の流れを作っておくと安定して運用できます。