VLOOKUPで構造化参照を利用して楽する

動画解説
 

動画解説

VLOOKUPで構造化参照を利用して楽する

動画版「Excelスキルアップ仕事術」です。
PowerQueryのシリーズの途中ですが、VLOOKUP関数を使えるのかという話をする前に、VLOOKUP関数とテーブルはとても相性がいいという例を紹介します。

さらに、VLOOKUP関数もテーブル内で使えば、メンテナンスフリーになるし、セル参照で相対参照や絶対参照なども考慮しなくてよくなります。 まさに、VLOOKUP関数とテーブルは最強のコンビです。
それをPowerQueryは超えられるのかという話は次回になります。

VLOOKUPで構造化参照を利用して楽する

テーブルでは構造化参照 Power query ではリレーションシップ

通常の Excelのテーブルで VLOOKUP を使う方法と Power query でリレーションシップを使うバイト2通りご紹介します。

  1. Power Queryを使ってフォルダと接続して、複数のファイルを取得しましたが、ファイルには商品の ID だけで商品名とか価格などが記入されていませんでした。

    テーブルの構造化参照の使い方1
  2. この場合、通常はExcel上でVLOOKUP を使う方法が考えられます。
    もちろん Power query でファイルからデータを取得する前に、パワークエリの機能であるリレーションシップを使うということも考えられますが、それは次回で説明します。

    テーブルの構造化参照の使い方2
  3. 左側の表はID を VLOOKUP で参照したい表です。分かりやすくするために右側にコード表を張り付けてテーブルにしておきます。

    テーブルの構造化参照の使い方3
  4. 範囲を選択して Control + D でテーブルが設定できます。先頭行をテーブルの見出しとして使用します。

    テーブルの構造化参照の使い方4
  5. テーブルには自動でテーブル1, テーブル2のような名前がつきますが、ここではテーブル名を「code」という名前に変更しておきます。

    テーブルの構造化参照の使い方5

使用する表をテーブルにしておく

  1. データを入力する方の左側の表もテーブルに設定しておきます。名前を「uriage」にしておきました。

    テーブルの構造化参照の使い方6
  2. 表引きするためにuriage表に列を2つ追加しました。

    テーブルの構造化参照の使い方7
  3. D2セルに”=VLOOKUP(” と入力し、隣のC2セルをクリックすると、そこには通常でしたらセルのアドレスC2が入りますが、テーブルの場合は違います。
    テーブルにしてあるので、=VLOOKUP([@コード] と入りました。
    これは構造化参照されたときの呼び名になったわけです。
    @の意味はこの行という意味です。コードは列見出しです。

    テーブルの構造化参照の使い方8
  4. 続いてどこを探すかということで、検索する対象の範囲を指定します。この場合テーブル名を入力するとインテリセンスが働いてテーブル名として”code”というテーブル名が表示されます。

    テーブルの構造化参照の使い方9
  5. 次に検索する列番号は左から2番目、検索方法はフォルスの完全一致を指定します。
    列番号にColumn関数を使うと数式を変更せずにすみます。

    テーブルの構造化参照の使い方10

テーブルにおける構造化参照と数式のコピー

  1. 隣の単価を参照する列の数式は列番号を3と修正してやります。

    テーブルの構造化参照の使い方11
  2. 下方向への数式のコピーはダブルクリックでコピーすることができます。

    テーブルの構造化参照の使い方12
  3. テーブルで構造化参照の場合は、別シートに参照先の”code”があっても、シートの指定は必要ありません。数式は同じシートでも別シートでも変わらないということです。

  4. また、列を追加して金額を出す場合の数式も簡単です。
    =[@単価]*[@個数] となります。

    テーブルの構造化参照の使い方13
  5. 集計行が必要な場合は、テーブルからテーブルスタイルグループにある集計行にチェックを入れます。

    テーブルの構造化参照の使い方14
  6. 表に集計行が追加されて金額の列に、サブトータル関数を使った合計売上金額が自動的に入力されています。

    テーブルの構造化参照の使い方15
  7. 隣の個数の欄には数式は自動で入りませんから、何か売れたかを集計したい場合はSUMサム関数で数式を入力してやります。

    テーブルの構造化参照の使い方16
  8. SUM 関数の括弧の中に左括弧を入力すると、このテーブルの列名がすべて表示されるので、どの列を集計するかを選んでやります。

    テーブルの構造化参照の使い方17
  9. 列を選んで数式を閉じます。"=SUM([個数])"
    数式も非常にシンプルになります。

    テーブルの構造化参照の使い方18
  10. もしにデータの個数を追加したい場合、集計行のチェックを外してやって新たなデータを追加してください。

    テーブルの構造化参照の使い方19
  11. データを追加してから再び集計行を表示したいという時は、テーブルスタイルのオプションから集計表にチェックを入れてやると、今度は式を入れなくても前回と同様の集計行が入ります。

    テーブルの構造化参照の使い方20