VLOOKUPで構造化参照を利用して楽する
動画解説
VLOOKUPで構造化参照を利用して楽する
動画版「Excelスキルアップ仕事術」です。
PowerQueryのシリーズの途中ですが、VLOOKUP関数を使えるのかという話をする前に、VLOOKUP関数とテーブルはとても相性がいいという例を紹介します。
さらに、VLOOKUP関数もテーブル内で使えば、メンテナンスフリーになるし、セル参照で相対参照や絶対参照なども考慮しなくてよくなります。
まさに、VLOOKUP関数とテーブルは最強のコンビです。
それをPowerQueryは超えられるのかという話は次回になります。
VLOOKUPで構造化参照を利用して楽する
通常の Excelのテーブルで VLOOKUP を使う方法と Power query でリレーションシップを使うバイト2通りご紹介します。
-
Power Queryを使ってフォルダと接続して、複数のファイルを取得しましたが、ファイルには商品の ID だけで商品名とか価格などが記入されていませんでした。
-
この場合、通常はExcel上でVLOOKUP を使う方法が考えられます。
もちろん Power query でファイルからデータを取得する前に、パワークエリの機能であるリレーションシップを使うということも考えられますが、それは次回で説明します。
-
左側の表はID を VLOOKUP で参照したい表です。分かりやすくするために右側にコード表を張り付けてテーブルにしておきます。
-
範囲を選択して Control + D でテーブルが設定できます。先頭行をテーブルの見出しとして使用します。
-
テーブルには自動でテーブル1, テーブル2のような名前がつきますが、ここではテーブル名を「code」という名前に変更しておきます。
使用する表をテーブルにしておく
-
データを入力する方の左側の表もテーブルに設定しておきます。名前を「uriage」にしておきました。
-
表引きするためにuriage表に列を２つ追加しました。
-
D2セルに”＝VLOOKUP（” と入力し、隣のC2セルをクリックすると、そこには通常でしたらセルのアドレスC2が入りますが、テーブルの場合は違います。
テーブルにしてあるので、＝VLOOKUP（[@コード] と入りました。
これは構造化参照されたときの呼び名になったわけです。
@の意味はこの行という意味です。コードは列見出しです。
-
続いてどこを探すかということで、検索する対象の範囲を指定します。この場合テーブル名を入力するとインテリセンスが働いてテーブル名として”code”というテーブル名が表示されます。
-
次に検索する列番号は左から2番目、検索方法はフォルスの完全一致を指定します。
列番号にColumn関数を使うと数式を変更せずにすみます。
テーブルにおける構造化参照と数式のコピー
-
隣の単価を参照する列の数式は列番号を3と修正してやります。
-
下方向への数式のコピーはダブルクリックでコピーすることができます。
-
テーブルで構造化参照の場合は、別シートに参照先の”code”があっても、シートの指定は必要ありません。数式は同じシートでも別シートでも変わらないということです。
-
また、列を追加して金額を出す場合の数式も簡単です。
＝[＠単価]*[＠個数] となります。
-
表に集計行が追加されて金額の列に、サブトータル関数を使った合計売上金額が自動的に入力されています。
-
隣の個数の欄には数式は自動で入りませんから、何か売れたかを集計したい場合はSUMサム関数で数式を入力してやります。
-
SUM 関数の括弧の中に左括弧を入力すると、このテーブルの列名がすべて表示されるので、どの列を集計するかを選んでやります。
-
列を選んで数式を閉じます。"=SUM([個数])"
数式も非常にシンプルになります。
-
もしにデータの個数を追加したい場合、集計行のチェックを外してやって新たなデータを追加してください。
-
データを追加してから再び集計行を表示したいという時は、テーブルスタイルのオプションから集計表にチェックを入れてやると、今度は式を入れなくても前回と同様の集計行が入ります。
集計行が必要な場合は、テーブルからテーブルスタイルグループにある集計行にチェックを入れます。
