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

動画解説

動画解説

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

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

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

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

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

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

   

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

   

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

   

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

   

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

   

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

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

   

7. 表引きするためにuriage表に列を２つ追加しました。

   

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

   

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

   

10. 次に検索する列番号は左から2番目、検索方法はフォルスの完全一致を指定します。
    列番号にColumn関数を使うと数式を変更せずにすみます。

    

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

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

    

12. 下方向への数式のコピーはダブルクリックでコピーすることができます。

    

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

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

    

集計行が必要な場合は、テーブルからテーブルスタイルグループにある集計行にチェックを入れます。



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

    

16. 隣の個数の欄には数式は自動で入りませんから、何か売れたかを集計したい場合はSUMサム関数で数式を入力してやります。

    

17. SUM 関数の括弧の中に左括弧を入力すると、このテーブルの列名がすべて表示されるので、どの列を集計するかを選んでやります。

    

18. 列を選んで数式を閉じます。"=SUM([個数])"
    数式も非常にシンプルになります。
    

    

19. もしにデータの個数を追加したい場合、集計行のチェックを外してやって新たなデータを追加してください。

    

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