テーブルの構造化参照を使う
VLOOKUP関数にはテーブルの構造化参照を使って楽をしよう。
2019EXCEL関数 VLOOKUP関数06回
VLOOKUP関数の引数で構造化参照を使う
VLOOKUP関数を便利に使う方法として、VLOOKUP関数の 3番目の引数にCOLUMN関数を使うという方法を紹介しました。
そうすると数式のコピーをする時に、列指定が自動で変更されるのでとても便利です。
けれども さらに上級のテクニックとしてテーブルの構造化参照を使うという方法があります。
それによって テーブルの 見出し名で 列番号を指定することが可能になります。
（サンプルファイルは、こちらから Excel（エクセル）2019 06回サンプルデータ）
COLUMN関数で列番号を指定する
- 前回（動画講義5回）は、VLOOKUP関数の 3番目の引数にCOLUMN関数を使う方法を紹介しました。列番号を指定する際に行を固定して横に数式をコピーできるようにしてあります。
- 参照範囲としている単価表内の B 2セルを指定したのは、重要度の高い参照表ならば、安易に削除されたりはしないだろうと判断からです。
- 数式は、=VLOOKUP($G3,tanka,COLUMN(B$2),0) となります。
もし種別を指定するのに、機種の列である B 列を指定することに 戸惑いがあるならば 、C列 でも良いのですが、 C列のセル指定の場合には戻り値が3となるため-1してやる必要があります。数式はこのようになります。 =VLOOKUP($G3,tanka,COLUMN(C$3)-1,0)
- 単価表には、テーブル設定がしてあり、「tanka」というテーブル名が付いています。
テーブルの見出し名を英文字に変更する
- テーブルの見出し名が英文字だと関数を入力する際に、頭文字を入力するだけで、オートコンプリートでヒントが表示されます。Excel2019では日本語でもオートコンプリートができるようになっています。しかしIMEを切り替える手間を考えるとやはり英文字をおすすめします。
引数にテーブルの構造化参照
- VLOOKUP関数の第3引数にCOLUMN関数を使ってますが、そのカラム関数の引数にテーブルの名前を入力します。最初の2~3文字入力で、該当する関数やテーブルなどがヒントとして表示されますので、ここではテーブルのtankaを選択してやります。
- 左大括弧を入力すると、今度は、テーブルの見出し列が選択できるようにヒントが表示されます。
- 第3引数は、このままでは、戻り値が3になるので、COLUMN(tanka[TYPE])-1と入力します。
- 数式を隣のセルにコピーすると、自動的にCOLUMN(tanka[Price])-1に変わりました。
IFERROR関数でエラー対応
- IFERROR関数を数式の前につけて、エラー対応しておきます。IFERROR関数の書式は、IFERROR（値, エラーの場合の値）です。
- 隣のセルにもコピーします。
- 次は、ドロップダウンリストの入力規則に対してテーブルを使っていきます。
現在の入力規則を消去してテーブルを指定
- 既存の入力規則を消去したら、入力値の種類は、いつものようにリストを、元の値には、INDIRECT関数を入力します。
- 数式は、=INDIRECT("tanka[ProductID]")
- OKで確定すると、リストが完成します。見かけは前と変わりませんが、テーブルの構造化参照で指定しています。tanka[ProductID]
VLOOKUP関数の第1引数を構造化参照で
- 今度は、VLOOKUP関数の数式の第1引数：参照値を＄G3からテーブルの構造化参照で指定することにします。
- 集計表の範囲を選択して、テーブルに設定します。
- テーブルになったので、＄G3の部分を変更します。
- [@ と入力すると、入力ヒントが表示されます。
- となりの機種を表示したいので、＠機種
構造化参照にした数式をコピー
- 数式は、
=IFERROR(VLOOKUP([@機種],tanka,COLUMN(tanka[TYPE])-1,0),"")
それを隣のセルにコピーします。
- コピーすると、当然、参照する行が移動して、
＠機種が＠種別に変わってしまうので、＠機種に直してやる必要があります。
- 数式を直してやると、完成です。別シートにコピーしても数式は崩れません。元の集計表をコピーして新規シートに貼り付けてみましょう。
- 貼り付けた表の数式もテーブルの構造化参照なので、崩れません。
- 今度は、単価表の方に、製造メーカーを表示するための新規の列を追加します。
- 列を追加しても数式をコピーして、参照値を＠機種にしてやればOKです。