どうも、まっすーです。

Excelで大量のデータを扱っていると、VLOOKUPを使う機会は多いと思います。

しかし、

  • VLOOKUPのコピーが面倒
  • 行数が増えると重くなる
  • 数式が壊れる
  • 列番号を変更するとエラーになる

といった悩みを抱えている方も多いのではないでしょうか。

私自身も業務で大量のExcelデータを扱う中で、VLOOKUPを多用していました。XLOOKUPが登場してからはVLOOKUPよりも便利、ということでXLOOKUPを多用するようになりましたが、Power Queryを学ぶと「VLOOKUPやXLOOKUPの代わりに使える」だけでなく、慣れると大変使いやすいです。

今回はPower Query初心者向けに、VLOOKUPやXLOOKUPを置き換える方法を解説します。

Contents
  1. VLOOKUP・XLOOKUPの問題点
    1. 売上データ
    2. 品目マスタ
    3. 品目名別売上
  2. Power Queryなら「結合(Merge)」で対応できる
  3. 手順① データをテーブル化する
  4. 手順② Power Queryへ取り込む
  5. 手順③ クエリを結合する
  6. 手順④ マスタ情報を展開する
  7. Power Queryを使うメリット
    1. ① 数式が不要
    2. ② 更新ボタンだけで再計算できる
    3. ③ 大量データに強い
    4. ④ 複数のマスタを結合できる
  8. VLOOKUPとPower Queryはどちらを使うべき？
    1. VLOOKUP向きの作業
    2. Power Query向きの作業
  9. 初心者が最初につまずくポイント
    1. テーブル名を付け忘れる
    2. 結合キーを間違える
    3. 不要な列まで展開する
  10. 実務で使うならこんな場面
  11. まとめ

VLOOKUP・XLOOKUPの問題点

例えば次のようなデータがあるとします。

売上データ

品目コード数量
A001100
A002200
A003150

品目マスタ

 

品目コード品目名
A001ネジ
A002ボルト
A003ナット

通常はVLOOKUPで品目名を取得します。

品目名別売上

VLOOKUPで取得したデータは下記のとおりです。

品目コード品目名数量
A001ネジ100
A002ボルト200
A003ナット150

 

少ないデータであれば、それほど問題はありません。

ですが、

  • 数万行以上になる
  • 複数シートを参照する
  • マスタが頻繁に更新される

といった場合、Excelが重くなりがちです。

またVLOOKUPは列番号を指定するため、マスタ側に列を追加すると数式を修正しなければならないケースがあります。

毎月同じ作業を繰り返している人ほど、こうした手間が積み重なります。

XLOOKUPはVLOOKUPよりも使いやすくなりましたが、VLOOKUP同様にExcelが重くなる、元データの変更があった際に数式の修正が必要といった問題が発生します。

Power Queryなら「結合(Merge)」で対応できる

Power QueryではVLOOKUPの代わりに「結合(Merge)」を使います。

イメージとしては、

共通のキーを使って2つの表を合体させる

機能です。

Excel関数のVLOOKUPでは1行ずつ検索を行います。

一方、Power Queryではテーブル同士を直接結合するため、データ量が増えても管理しやすくなります。

また、一度作った手順を保存できるため、毎月同じ作業を行う場合に非常に便利です。

手順① データをテーブル化する

まず元データをテーブル化します。

売上データについて対象範囲を選択して、CTRL+Tを押します。

テーブル化しておくことで、行数が増えても自動的に範囲が拡張されます。

例えば今月は1,000行、来月は5,000行になったとしても、Power Query側の設定を変更する必要はありません。

手順② Power Queryへ取り込む

テーブルを選択し、「データ」→「テーブルまたは範囲から」を選択します。

ここでは、

  • 売上データ
  • 品目マスタ

の2つをPower Queryに読み込むイメージです。

手順③ クエリを結合する

Power Query画面で、下記の順に実行していきます。

ここで選ぶ「品目コード」がVLOOKUPでいう検索キーに相当します。

実務では、

  • 品目コード
  • 工場
  • 年月

など複数列をキーにして結合するケースもあります。

手順④ マスタ情報を展開する

結合が完了すると、新しい列が作成されます。

列名の右側にある展開ボタンをクリックして取得したい列を選択します。

これでVLOOKUPと同じ結果が得られました。

Power Queryを使うメリット

① 数式が不要

VLOOKUPを何万行もコピーする必要がありません。

数式のコピー漏れや参照範囲ミスも防げます。

② 更新ボタンだけで再計算できる

データを差し替えた後は、

更新ボタンを

を押すだけです。

毎月同じフォーマットのデータを扱う業務では非常に効果があります。

③ 大量データに強い

数万行〜数十万行のデータでも扱いやすくなります。

ただし、

  • 不要列が多い
  • 結合するデータ量が大きすぎる
  • 展開列が多すぎる

場合はPower Queryでも重くなります。

その場合はPowerShellを使うような選択も考える必要がありますが、まずは不要列を削除してから結合するのがおすすめです。

④ 複数のマスタを結合できる

例えば、

  • 品目マスタ
  • 原価マスタ
  • 在庫マスタ
  • 取引先マスタ

を順番に結合することも可能です。

複雑なVLOOKUPを何本も作る必要がなくなります。

VLOOKUPとPower Queryはどちらを使うべき？

VLOOKUP向きの作業

  • 100行程度の小規模データ
  • 一時的な分析
  • その場限りの集計
  • すぐ終わる作業

Power Query向きの作業

  • 毎月行う業務
  • 数千行以上のデータ
  • マスタ結合が多い業務
  • 自動化したい業務
  • 定型業務を効率化したい場合

私の経験では、「毎月同じことをしているならPower Query化を検討する価値がある」と感じています。

初心者が最初につまずくポイント

テーブル名を付け忘れる

Power Queryではテーブル名が非常に重要です。

後から見返したときに分かりやすい名前を付けましょう。

例

  • SalesData
  • ItemMaster
  • CostMaster

結合キーを間違える

結合キーを間違えると、

  • データが増えすぎる
  • 一致しない
  • 重複が発生する

といった問題が起きます。

まずはキー列が本当に一意になっているか確認しましょう。

不要な列まで展開する

Power Queryが重くなる原因の一つです。

展開時には必要な列だけ選択しましょう。

実務で使うならこんな場面

Power QueryでVLOOKUPを置き換える方法は、実務でも非常に活用できます。

例えば、

  • 売上データに品目名を付ける
  • 原価データに部門情報を付ける
  • 在庫データに分類情報を付ける
  • 複数システムのデータを比較する
  • マスタ情報を一括付与する

といった場面です。

私自身もシステム移行のデータ比較やSAPデータ分析で活用できそうだと感じています。

特に旧システムと新システムの比較では、VLOOKUPよりPower Queryの方が管理しやすいケースが多そうです。

まとめ

Power QueryのMerge機能を使えば、VLOOKUPの多くを置き換えることができます。

初心者の方はまず、

  1. テーブル化する
  2. Power Queryへ取り込む
  3. Mergeで結合する
  4. 展開する

の4ステップを覚えるのがおすすめです。

VLOOKUPは便利な関数ですが、大量データや定型業務では限界を感じることがあります。

Power Queryを覚えることで、Excel業務を大幅に効率化できる可能性があります。

まずは簡単なマスタ結合から試してみてはいかがでしょうか。

ABOUT ME
まっすー
製造業の経理・業務改善を発信する「まっすー」です。原価計算・Excel・Power Query・SAP・Pythonを使って、面倒な仕事をラクにする方法を紹介しています。
BLOG：https://massu-keiei.com