Excel PowerQueryでVLOOKUP関数のようにする方法

PowerQueryのマージを使うと、簡単にVLookUp関数使った結果と似たようなことができます。

これはVLookUp関数の式です。

VLOOKUP(検索値,範囲,列番号,(検索方法))

この関数は、いくつかデメリットがあります。

  • 検索値は一番左
  • 複数条件はできない
  • 複数列の結果を返すには、列ごとにVLookUp関数を設定する必要がある

参考:VLOOKUP関数の基本

PowerQueryのマージでは、上記のようなデメリットはありません。

目次

PowerQueryのマージの例

PowerQueryのマージを使ってどのようなことをするか、例を使って説明していきます。

ファイル構成

  1. 購入品.xlsx:シート「購入品記録」
  2. カテゴリ.xlsx:シート「コード」
  3. 読み込むファイルは新規ブック

3つのエクセルブックを使う方法です。

PowerQueryのマージを使ってやること

ここでは単純な方法での説明です。

例:シート「購入品記録」のB列とC列の間にシート「コード」のカテゴリを追加したい。

クエリのマージの設定方法

事前に用意するエクセルブックは2つです。

  1. 購入品.xlsx:シート「購入品記録」
  2. カテゴリ.xlsx:シート「コード」

購入品.xlsx

カテゴリ.xlsx

カテゴリを取得する(接続専用)

STEP
データ取得するブックを用意する

新しいブックを開き、任意の名前で保存します。

STEP
カテゴリ.xlsxのシート「コード」のデータを取得します。

タブ[ データ ] > [ データの取得 ]> [ ファイルから ]> [ Excelブックから ] の順でクリックします。

「カテゴリ.xlsx」を選択し、「インポート」をクリックします。

→ ナビゲーターの画面が表示されます。

STEP
シート「コード」を選択して、画面右下の「読み込み先・・・」をクリックします。
STEP
「接続の作成のみ」を選択し、「OK」をクリックします。
画像に alt 属性が指定されていません。ファイル名: powerquery-vlookup-7.png

→ 画面左側:クエリと接続にシート「コード」が接続専用と表示されます。

購入品記録のデータを取得する

STEP
購入品.xlsxのシート「購入品記録」のデータを取得します。

タブ[ データ ] > [ データの取得 ]> [ ファイルから ]> [ Excelブックから ] の順でクリックします。

エクセル・PowerQuery

「購入品.xlsx」を選択し、「インポート」をクリックします。

エクセル・PowerQuery

→ ナビゲーター画面が表示されます。

エクセル・PowerQuery・ナビゲーター
STEP
画面右下の「データの変換」をクリックします。

→ PowerQueryエディターが開きます。

エクセル・PowerQuery
STEP
日付のデータ型を変更します。

データの型は自動的に検出します。日付設定していないデータを取り込んだ場合、このようにシリアル値で表示されます。この場合は、データ型より「日付」を選択します。

エクセル・PowerQuery

「現在のものを置換」をクリックします。

エクセル・PowerQuery

→ 変更後の画面です。

エクセル・PowerQuery

マージする

STEP
タブ[ ホーム ] > [ クエリのマージ ]をクリックします。
エクセル・PowerQuery・マージ

→ マージの画面上部にシート「購入品記録」が表示されます。

エクセル・PowerQuery・マージ
STEP
「コード」を選択します。
エクセル・PowerQuery・マージ
STEP
表をつなげる

「カテゴリCD」と「CD」をクリックします。

エクセル・PowerQuery・マージ
STEP
結合の種類を選択する

「接続の種類」から「左外部(最初の行すべて、および2番目の行のうち一致するもの」を選択します。

エクセル・PowerQuery・マージ

デフォルトは一番上の「左外部(最初の行すべて、および2番目の行のうち一致するもの」です。

→ 一番左にシート「コード」の列が追加されます。

エクセル・PowerQuery・マージ
STEP
列の展開

展開ボタン(赤丸)をクリックし、展開したい項目にチェックを入れて「OK」をクリックします。

エクセル・PowerQuery・マージ

→ シート「1」のデータが右端の列に展開されます。

エクセル・PowerQuery・マージ

マウスでドラックすると列の位置を移動することができます。

エクセル・PowerQuery・マージ
STEP
読み込み

[ ホーム ]>[ 閉じて読み込む ]をクリックします。

結果

読み込み後は、自動でスタイル設定されています。

スタイルを変更したい場合は、データ内をクリックし、[ テーブルデザイン ]>[ テーブルスタイル ] より好みのスタイルを選択します。

右側の下向き三角をクリックするとすべてのスタイルが表示されます。

まとめ・VLookUp関数と比較

PowerQueryのマージを使うと、VLookUp関数のようなことができますが、違いはあります。

VLookUp関数
  • 照合キーは1つ
  • 1対多の結合は対応していない
  • 照合後の列は1列
  • 結合は左外部のみ
クエリ
  • 複合キーも可能
  • 1対多の結合も可能
  • 展開で複数列の取得も可能
  • 結合は左外部の他、右外部・完全外・内部・左反・右反
目次