VLOOKUP関数の参照先を構造化参照にする
VLOOKUP関数は他の表を参照して自動でデータが入力できる便利な関数です。
「商品コード」を入力すれば、「商品名」と「単価」が参照入力される
参照先のデータに増減があると、数式の修正が必要
この関数にも欠点があり、参照先のデータに増減があると数式を変更する必要があります。
参照先のデータに増減があるたびに数式を修正するのは大変です、そんな時に参照の方法を「構造化参照」にすれば、参照先のデータに増減があっても数式を修正する必要がありません。
商品コード「A-9」の「コスタリカ」を追加
参照先が「セル番号」になっているので、行の範囲を広げなければならない
VLOOKUP関数の参照する「範囲」を「行番号」と「列番号」で直接指定しているので、「表」が大きくなり参照範囲が変わると数式を変更する必要があります。
参照先の「範囲」をテーブル名にして「構造化参照」にする
VLOOKUP関数の参照する「範囲」を表に名前を付けた「テーブル名」にすることで「構造化参照」となり、参照する「範囲」が増減しても数式を変更する必要がなくなります。
参照先の「表」をテーブル化する
- 参照先の表のどこか1カ所を選択
- 「挿入」タブを選択
- 「テーブル」をクリック
- テーブルの作成ダイアログボックスで「範囲」が正しく選択されているのを確認
- 「先頭行をテーブルの見出しとして使用する」にチェックを入れる
「テーブル名」を変更する
表をテーブル化すると自動で「テーブル１」と作成順に連番で名前が付けられます。
数式を分かり易くするために、テーブル名を変更します。
- テーブル内のどこか１カ所を選択
- リボンに「テーブル デザイン」タブが表示される（※ テーブル内にカーソルがある場合に表示されます）
- テーブル名を「商品マスター」に変更します
VLOOKUP関数の数式を入力する
「 fx 」関数の挿入ボタンを使って数式を入力する
- 数式を入力する「商品名」にカーソルを置く
- 「 fx 」関数の挿入ボタンを押す
- 関数の分類のボックスで「検索／行列」を選択
- 関数名のボックスで、一番下にスクロールして「VLOOKUP」を選択（※ アルファベットの降順）
※ 関数の分類は「すべて表示」で探してもよいが、主なものは分類を覚えておいた方が良い
※ 一度使用すれば「最近使ったもの」に表示されるようになる
数式を入力します
- 検索値にセル「B２」を選択（※ テーブル化しているので「［＠商品コード］」と表示されます）
- 範囲にテーブル名「商品マスター」と入力
- 列番号に「２」と入力
- 検索方法に「FALSE（完全一致）」と入力
数式が入力されました
「商品コード」にコードを入力すれば、「商品名」が表示されます。
VLOOKUP関数の「範囲」がテーブル名「商品マスター」になっているので、商品が増えて「商品マスター」の範囲が広がっても数式を変更する必要がありません。
フィールド名（列）の増減に対応する
データ（縦方向）の増減はVLOOKUP関数の「範囲」で指定しているので、、テーブルが自動で広がる機能を使って「構造化参照」にすることができます。
フィールド（横方向）の増減は「列番号」を「範囲の何列目」かを直接列番号で指定する方法では構造化参照になりません。
VLOOKUP関数の「列番号」をCOLUMN関数で求めることで構造化参照にすることができます。。
「列1」の挿入で、「商品名」の列番号が変わってしまう
COLUMN関数を使って、「列番号」の値を求める
関数の挿入でVLOOKUP関数の「列番号」に、COUNTIF関数をネスト（入れ子）します。
- 「商品名」のセルを選択します
- 関数の挿入をクリックします
- 「列番号」の部分を消去して、カーソルを置きます
- 名前ボックスで「その他の関数」をクリックします
- 関数の挿入ダイアログで関数の分類で「検索／行列」を選択します
- 関数名からCOLUMN関数を選択します
COUNTIF関数の参照を「商品名」のセルに指定します
- COLUMN関数の参照を「商品名」にします
条件に一致する「商品名」のある列番号が、VLOOKUP関数の「列番号」になります
VLOOKUP関数の「列番号」に、COLUMN関数を使うことでフィールド（列）が増減しても数式を変更する必要がなくなります。
参照する「表」が「A列」から始まっていない場合は、COLUMN関数は使えません
VLOOKUP関数の「列番号」に、COLUMN関数を使う方法は数式が簡単で便利ですが、COLUMN関数は「A列からの位置を返す」ので、参照対象の「表」がA列から始まっていない場合は使うことができません。
参照用に「表」を作る場合はA列から始まるように作ることができますが、参照用の「表」が複数ある場合や、既にある「表」を使う場合は他の方法を使う必要があります。
「表」の位置にかかわらず、「A列」が必ず「１」になる
MACH関数を使えば、「表」はどの位置でもよい
MACH関数は「範囲を設定できる」ので、「表」がA列から始まっていなくても正しい値を求めることができます。
MACH関数の数式を入力します
- 検査値に参照範囲に「表」の見出しを選択します
- 検査範囲にテーブル化した「表」を選択します
- 照合の種類を「０」にします
このようにVLOOKUP関数の「列番号」にCOLUMN関数やMACH関数を使うことにより、データ（行方向）だけでなく、フィールド（列方向）の増減にも対応した構造化参照にすることができます。
VLOOKUP関数の、エラーが表示されないようにする
VLOOKUP関数は「検索値」が入力されていないと「＃Ｎ/A」エラーになります。
IFERROR関数を使って、エラーが表示されないようにする
IFERROR関数を使って、エラーが出た場合は「" "」（空白）が表示されるようにします。
- 値に、VLOOKUP関数をネスト（入れ子）にします
- エラーの場合の値に「" "」（空白）を入力します
リスト入力も構造化参照にすることができます
VLOOKUP関数の検索値に「リスト入力」を設定すれば、入力間違いもなくなり簡単に入力することができます。
リスト入力の「範囲」も構造化参照にすることで、データの増減に対応できるようになります。
フィールド別に「名前」を付けます
「選択範囲から作成」の機能を使って、フィールド（項目）別に名前を付けます。
- 参照するテーブル全体を範囲選択します
- 「数式」タブを選択します
- 「範囲から選択」をクリックします
- 選択範囲から名前...ダイアログで「上端行」にチェックを入れます
フィールド（項目）別に名前が付きました
選択範囲から作成...によって、選択範囲のそれぞれのフィールドが「上端行」の内容で名前が付きました。
データを追加しても、「範囲の名前」は変わりません
リスト入力を構造化参照で設定します
データの入力規則の設定で「元の値」を、「項目につけた名前」にすることでデータの増減に対応できる構造化参照になります。
- リスト入力を設定するセルを選択します
- 「データ」タブを選択します
- 「データの入力規則」をクリックします
- データの入力規則ダイアログで、入力値の種類を「リスト」にします
- 元の値で「商品コードの範囲」を範囲選択します（※ ダイアログボックスには「商品コード」と表示されます）
入力規則を他のセルにコピーします
テーブル化していても、入力規則は自動でコピーされないので手作業でコピーします。
- 入力規則を設定したセルを選択して右クリックします
- ダイアログボックスで「コピー」をクリックします
- 「ホーム」タブを選択します
- 「貼り付け」をクリックします
- 「形式を選択して貼り付け」をクリックします
- 形式を選択して貼り付けダイアログで「入力規則」を選択します
「商品コード」のフィールド全てに入力規則がコピーされました
表に「テーブル化」や「選択範囲に名前を付ける」の機能を使って「範囲に名前を付ける」ことにより、VLOOKUP関数の参照範囲に「範囲に付けた名前」が使えるようになり構造化参照にすることができます。
実務ではVLOOKUP関数の参照範囲のデータの増減は頻繁に発生します。
構造化参照にしておくことで、気付かないうちに参照範囲のデータが増減していてエラーになってしまうことを未然に防ぐことができます。
