Power QueryをVLOOKUPの代わりに使う方法｜売上と商品マスターを結合

Excelの売上データに商品名やカテゴリー、単価などを追加するとき、VLOOKUPやXLOOKUPを使っている方は多いのではないでしょうか。少量のデータをその場で参照するだけなら、検索関数は分かりやすく便利です。

一方、毎月同じ売上表を更新し、商品マスターから複数の列を追加する業務では、数式のコピー漏れや参照範囲のずれ、未登録コードの見落としが起こりやすくなります。このような繰り返し作業は、Power Queryの「クエリのマージ」に置き換えると、結合条件と整形手順を保存して再実行できます。

この記事では、売上データの商品番号をキーに商品マスターを左外部結合し、商品名、商品カテゴリー、カラーなどを追加する方法を解説します。単に結合するだけでなく、結合前後の行数、未一致コード、マスター側の重複キーまで確認できる実務向けの手順にしています。

今回の記事で分かること

• Power QueryをVLOOKUPの代わりに使うと効果的な場面
• 売上データと商品マスターを左外部結合する操作手順
• 結合できない原因と、未一致・重複・行数の検算方法

先に結論：繰り返すマスター結合はPower Queryが向いている

Power Queryが置き換えるのは、VLOOKUPという関数そのものではありません。置き換えるのは「売上データを受け取り、検索式を追加し、下までコピーし、エラーを確認する」という繰り返し作業です。

まず、VLOOKUPとPower Queryの役割の違いを確認します。次の図では、左側がセルごとに値を検索する方法、右側が結合ルールを処理手順として保存する方法です。

このように、少量データをその場で参照し、シート上で数式と結果を確認したい場合はVLOOKUPやXLOOKUPが便利です。一方、毎月の定型更新、複数列の追加、未一致や重複の監査まで含めたい場合は、Power Queryの方が処理を管理しやすくなります。

今回使う売上データと商品マスター

本記事では、フォルダー内にあるExcel教材を例に使います。売上データは2018-Sales-Data.xlsx、商品マスターはProduct-Lookup.xlsxです。両方のファイルに商品番号があるため、この列を結合キーにできます。

商品マスターの商品番号は24件すべて一意で、重複はありませんでした。売上データには21種類の商品番号があり、商品マスターとの未一致は0件です。また、売上の受注数量合計は45,554でした。これらの数値を結合後の検算基準にします。

次の図では、売上と商品マスターがどのように1つの表へ変わるかを示しています。左側の2つの入力と、右側の結合後データを見比べてください。

売上データの商品番号318には、商品マスターからレーサーBタイプ、競技用スクーター、レッドなどの属性を追加できます。同じように商品番号321や323にも商品情報が付き、カテゴリー別売上やカラー別構成など、元の売上表だけではできなかった集計が可能になります。

VLOOKUPでマスターを参照するときに起きやすい問題

VLOOKUPは、指定した検索値を表の左端列から探し、指定した列番号の値を返します。例えば商品番号から商品名を取得するなら、次のような式を売上表へ入れます。

=VLOOKUP([@商品番号],商品マスター!$A$2:$H$25,4,FALSE)

この方法は分かりやすい反面、定型業務では次の管理が必要です。

• 売上データの最終行まで数式が入っているか確認する
• 商品マスターの行や列が増えたときに参照範囲を見直す
• 商品名、カテゴリー、カラーなど、取得列ごとに式を追加する
• #N/Aが「マスター未登録」なのか「キー形式の不一致」なのか調べる
• 値貼り付けを行う場合、更新後に同じ操作を繰り返す

Excelテーブルの構造化参照やXLOOKUPを使えば、コピー漏れや列番号の弱点は軽減できます。そのため「検索関数は古いから、すべてPower Queryに変更する」という考え方は適切ではありません。重要なのは、セル計算として残す方がよい作業と、更新可能なデータ処理にした方がよい作業を分けることです。

Power Queryで売上と商品マスターを結合する6ステップ

操作は、読み込みから検算までの6ステップです。次の図では、上段を左から右へ進み、その後、下段を右から左へ進みます。本文の手順1〜6と図内番号を対応させています。

結合ボタンを押して終わりではありません。安定した運用にするには、結合前のキー確認と、結合後の件数確認までを1つの手順として保存することが大切です。

手順1：売上データをPower Queryへ読み込む

Excelのデータタブからデータの取得を選び、ファイルから、Excelブックからの順に進みます。2018-Sales-Data.xlsxを選択し、ナビゲーターで対象シートまたはテーブルを選びます。

すぐにワークシートへ読み込まず、データの変換を選んでPower Queryエディターを開きます。ここで、商品番号が数値型、販売日が日付型、受注数量が整数型になっているか確認します。

商品番号に先頭ゼロが必要な運用では、数値型にするとゼロが失われます。その場合は、売上側とマスター側の両方をテキスト型に統一してください。結合キーは、見た目ではなくデータ型も一致している必要があります。

手順2：商品マスターをPower Queryへ読み込む

同じようにProduct-Lookup.xlsxを読み込みます。商品マスターには、商品番号、商品セグメント、商品カテゴリー、商品名、カラー、売価、原価などがあります。

この段階で、商品番号が一意か確認します。マスター側に同じ商品番号が複数行あると、Power Queryの結合では一致した件数分だけ売上行が増える可能性があります。VLOOKUPが先頭の一致値を返す動きとは異なるため、マスターの重複は結合前に解消する必要があります。

確認方法としては、商品番号列を選んでグループ化し、行数を数える方法があります。行数が2以上の商品番号があれば、単純に重複を削除するのではなく、どちらを正しいマスターとして残すかを確認してください。

手順3：両方の結合キーを整える

売上とマスターの商品番号を同じデータ型にします。テキストキーの場合は、変換タブの書式からトリミングを行い、前後の余分な空白を削除します。全角・半角、ハイフンの有無、桁数にも注意が必要です。

例えば、売上側が0018、マスター側が数値の18なら、そのままでは一致しません。どちらが正しい業務コードなのかを決めたうえで、ゼロ埋めまたは型変換を行います。キー整形のルールをPower Queryのステップとして残すと、翌月も同じ条件で処理できます。

手順4：クエリのマージで左外部結合する

売上クエリを開き、ホームタブからクエリのマージを選びます。上側のテーブルには売上データ、下側には商品マスターを指定します。両方の商品番号列をクリックし、結合の種類は左外部を選びます。

左外部結合は、左側に置いた売上データの全行を残し、商品マスターで一致した情報を追加する方法です。マスターに存在しない商品番号があっても、売上行そのものは消えません。未一致の商品属性がnullになるため、マスターの登録漏れを後から確認できます。

複数列をキーにする場合は、両方のテーブルで同じ順番に列を選択します。例えば会社コード + 商品番号で一意になるなら、会社コードを1番目、商品番号を2番目として両方でそろえます。

手順5：商品マスターから必要な列だけ展開する

マージを実行すると、売上クエリの右端に商品マスターというテーブル型の列が追加されます。列見出しの展開ボタンを押し、商品名、商品カテゴリー、商品セグメント、カラーなど、レポートで使う列だけを選びます。

マスターの全列を展開すると、出力が横に広がり、不要な個人情報や管理列まで持ち込む原因になります。今回のような売上分析であれば、商品名、カテゴリー、カラー、売価、原価など、目的に必要な列へ絞る方が保守しやすくなります。

また、元の列名をプレフィックスとして使用しますのチェックを外すと、商品マスター.商品名ではなく商品名として展開できます。ただし、売上側に同名列がある場合は、列名の衝突を避けるためプレフィックスを残してください。

手順6：行数、未一致、受注数量を検算する

結合後は、売上行数が11,055行のままか確認します。さらに、展開した商品名がnullの行をフィルターし、未一致件数を確認します。今回の教材では未一致は0件でした。

最後に、受注数量の合計が結合前と同じ45,554であることを確認します。行数と数量合計が増えている場合は、マスター側の重複キーによって売上行が複製されている可能性があります。反対に行数や数量が減っている場合は、内部結合を選んでいないか、途中で行をフィルターしていないかを確認します。

結合の仕組みと検算ポイント

Power Queryが生成するMコード

画面操作でマージすると、Power Queryは内部でMコードを生成します。クエリ名や列名は環境によって変わりますが、中心となる処理は次の形です。

let
    売上 = SalesData,
    商品マスター = ProductMaster,
    結合 = Table.NestedJoin(
        売上,
        {"商品番号"},
        商品マスター,
        {"商品番号"},
        "商品マスター",
        JoinKind.LeftOuter
    ),
    展開 = Table.ExpandTableColumn(
        結合,
        "商品マスター",
        {"商品名", "商品カテゴリー", "カラー"},
        {"商品名", "商品カテゴリー", "カラー"}
    )
in
    展開

Table.NestedJoinで2つのテーブルを商品番号によって結び、JoinKind.LeftOuterで売上側の全行を残します。その後、Table.ExpandTableColumnで必要な商品属性を通常の列として展開しています。Mコードを最初から手書きする必要はありませんが、結合キーと結合種類を確認するときに読めると便利です。

左外部結合を使う理由と、他の結合との違い

売上に商品情報を付ける場合は、通常、売上を左側に置いた左外部結合を使います。売上は会計・実績の元データであり、マスター未登録を理由に消してはいけないためです。

特に便利なのが左反結合です。通常の左外部結合で完成データを作り、別の参照クエリで左反結合を行えば、「売上には存在するが商品マスターには存在しない商品番号」の監査表を作れます。完成データとエラー監査を分けることで、更新後の確認が簡単になります。

マージ後に必ず確認したい3つのポイント

次の図では、結合結果を正しいと判断するための3つの確認を示しています。左から、行数、未一致、マスター側の重複キーです。

今回の教材では、売上行数は11,055 → 11,055、未一致は0件、商品マスターの重複キーも0件でした。この3点がそろっているため、売上の粒度を変えずに商品属性を追加できたと判断できます。

注意したいのは、左外部結合を選んだからといって、必ず行数が維持されるわけではないことです。商品マスターに同じ商品番号が2行あれば、1つの売上行が2つの商品行に一致し、結合後の行数が増えます。「左外部結合だから安心」ではなく、「マスター側のキーが一意だから行数を維持できる」と理解することが重要です。

結合できないときの原因と対処法

データ型が違う

売上側の商品番号が数値、マスター側がテキストの場合、見た目が同じでも結合できません。両方をテキストまたは整数へ統一します。業務コードに先頭ゼロがあるなら、テキスト型を選びます。

前後に空白や制御文字がある

CSVや基幹システムから出力したコードには、末尾スペースや改行などが混ざることがあります。テキスト型へ変換した後、トリミングとクリーンを適用します。ハイフンや全角スペースを統一する場合は、置換ルールもステップとして残します。

マスター側に重複がある

商品番号が重複していると、結合後の売上行が増えます。商品番号でグループ化して件数を確認し、2件以上のコードを抽出します。単純な重複削除で済ませず、適用開始日や会社コードなど、本来必要な複合キーがないか確認してください。

nullや空欄をキーにしている

キーが空欄の売上行は商品マスターと正しく結び付きません。空欄を削除するのか、未設定として監査対象にするのか、業務ルールを決めます。エラーを消すだけではなく、元データの登録工程へ戻すことも必要です。

結合する列の選択順が違う

複数列結合では、選択した順番も条件の一部です。売上側で会社コード → 商品番号、マスター側で商品番号 → 会社コードと選ぶと一致しません。列見出しに表示される番号が両方で同じ順序か確認します。

VLOOKUP・XLOOKUP・Power Queryの使い分け

VLOOKUPやXLOOKUPは、入力したコードに応じて画面上の値を即座に変えたい場合に向いています。例えば、見積書の商品番号を変更すると単価が変わるような入力フォームでは、検索関数の方が自然です。

Power Queryは、月次売上、受注明細、在庫一覧など、まとまったデータを更新してレポート用テーブルを作る場面に向いています。セルを編集した瞬間に結果が変わるのではなく、すべて更新を実行したタイミングで処理が再実行されます。この違いを理解して選ぶことが大切です。

翌月以降の更新方法

クエリを一度作成した後は、元の売上ファイルを同じ場所・同じ列構成で更新し、Excelのデータタブからすべて更新を実行します。Power Queryは、型変換、キー整形、マージ、列展開を同じ順序で再実行します。

ただし、更新ボタンだけで必ず成功するわけではありません。ファイル名や保存場所、シート名、列名が変わると、参照エラーになる可能性があります。更新後は、最終更新日時、売上行数、未一致件数、受注数量合計を確認するチェック欄をレポートに用意すると、異常に気付きやすくなります。

よくある質問

Power QueryのマージはVLOOKUPより必ず速いですか？

データ量、変換内容、データソース、PC環境によって変わるため、一律には言えません。Power Queryの利点は速度だけでなく、処理手順を保存できること、複数列を一度に展開できること、未一致や重複を監査しやすいことにあります。実際のファイルで更新時間と保守性を比較してください。

マスターにない商品番号は消えますか？

左外部結合なら売上行は残り、商品名などの展開列がnullになります。内部結合を選ぶと未一致行は結果から消えるため、売上実績の欠落につながる可能性があります。通常は左外部結合で残し、別の左反結合クエリで未一致を監査します。

商品マスターを更新したら、結合結果も変わりますか？

Power Queryを更新すると、最新の商品マスターを使って結合が再実行されます。過去時点の商品名やカテゴリーを保持する必要がある場合は、現在のマスターをそのまま結合するのではなく、適用開始日・終了日を持つ履歴マスターなど、別のデータ設計が必要です。

まとめ：Power Queryのマージで検索作業を更新処理に変えよう

Power Queryのマージを使うと、売上データと商品マスターを商品番号で結合し、商品名、カテゴリー、カラーなどをまとめて追加できます。VLOOKUPの検索式を1列ずつ管理する方法から、読み込み、整形、結合、検算を再実行できる処理へ変えられることが大きな利点です。

実務で重要なのは、左外部結合を選ぶことだけではありません。結合前にキーの型・空白・桁数とマスターの重複を確認し、結合後に行数、未一致、数量合計を検算する必要があります。

少量の単発参照や入力フォームではVLOOKUP・XLOOKUPを使い、毎月繰り返す売上集計や複数列のマスター付与ではPower Queryを使う。このように役割を分けると、Excelの計算とデータ処理を無理なく整理できます。