VLOOKUP関数を使うならテーブルの構造化参照も利用するのがおすすめ。

テーブルの構造化参照を使う

VLOOKUP関数にはテーブルの構造化参照を使って楽をしよう。

VLOOKUP関数でテーブルの構造化参照を使う

2019EXCEL関数 VLOOKUP関数06回

VLOOKUP関数の引数で構造化参照を使う

VLOOKUP関数を便利に使う方法として、VLOOKUP関数の 3番目の引数にCOLUMN関数を使うという方法を紹介しました。 そうすると数式のコピーをする時に、列指定が自動で変更されるのでとても便利です。
けれども さらに上級のテクニックとしてテーブルの構造化参照を使うという方法があります。

それによって テーブルの 見出し名で 列番号を指定することが可能になります。
(サンプルファイルは、こちらから Excel(エクセル)2019 06回サンプルデータ


COLUMN関数で列番号を指定する

  1. 前回(動画講義5回)は、VLOOKUP関数の 3番目の引数にCOLUMN関数を使う方法を紹介しました。列番号を指定する際に行を固定して横に数式をコピーできるようにしてあります。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  2. 参照範囲としている単価表内の B 2セルを指定したのは、重要度の高い参照表ならば、安易に削除されたりはしないだろうと判断からです。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  3. 数式は、=VLOOKUP($G3,tanka,COLUMN(B$2),0) となります。
    もし種別を指定するのに、機種の列である B 列を指定することに 戸惑いがあるならば 、C列 でも良いのですが、 C列のセル指定の場合には戻り値が3となるため-1してやる必要があります。数式はこのようになります。 =VLOOKUP($G3,tanka,COLUMN(C$3)-1,0)
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  4. 単価表には、テーブル設定がしてあり、「tanka」というテーブル名が付いています。
    VLOOKUP 関数とCOLUMN関数の組み合わせ

テーブルの見出し名を英文字に変更する

  1. テーブルの見出し名が英文字だと関数を入力する際に、頭文字を入力するだけで、オートコンプリートでヒントが表示されます。Excel2019では日本語でもオートコンプリートができるようになっています。しかしIMEを切り替える手間を考えるとやはり英文字をおすすめします。
    VLOOKUP 関数とCOLUMN関数の組み合わせ

引数にテーブルの構造化参照

  1. VLOOKUP関数の第3引数にCOLUMN関数を使ってますが、そのカラム関数の引数にテーブルの名前を入力します。最初の2~3文字入力で、該当する関数やテーブルなどがヒントとして表示されますので、ここではテーブルのtankaを選択してやります。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  2. 左大括弧を入力すると、今度は、テーブルの見出し列が選択できるようにヒントが表示されます。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  3. 第3引数は、このままでは、戻り値が3になるので、COLUMN(tanka[TYPE])-1と入力します。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  4. 数式を隣のセルにコピーすると、自動的にCOLUMN(tanka[Price])-1に変わりました。
    VLOOKUP 関数とCOLUMN関数の組み合わせ

IFERROR関数でエラー対応

  1. IFERROR関数を数式の前につけて、エラー対応しておきます。IFERROR関数の書式は、IFERROR(値, エラーの場合の値)です。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  2. 隣のセルにもコピーします。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  3. 次は、ドロップダウンリストの入力規則に対してテーブルを使っていきます。
    VLOOKUP 関数とCOLUMN関数の組み合わせ

現在の入力規則を消去してテーブルを指定

  1. 既存の入力規則を消去したら、入力値の種類は、いつものようにリストを、元の値には、INDIRECT関数を入力します。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  2. 数式は、=INDIRECT("tanka[ProductID]")
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  3. OKで確定すると、リストが完成します。見かけは前と変わりませんが、テーブルの構造化参照で指定しています。tanka[ProductID]
    VLOOKUP 関数とCOLUMN関数の組み合わせ

VLOOKUP関数の第1引数を構造化参照で

  1. 今度は、VLOOKUP関数の数式の第1引数:参照値を$G3からテーブルの構造化参照で指定することにします。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  2. 集計表の範囲を選択して、テーブルに設定します。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  3. テーブルになったので、$G3の部分を変更します。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  4. [@ と入力すると、入力ヒントが表示されます。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  5. となりの機種を表示したいので、@機種
    VLOOKUP 関数とCOLUMN関数の組み合わせ

構造化参照にした数式をコピー

  1. 数式は、
    =IFERROR(VLOOKUP([@機種],tanka,COLUMN(tanka[TYPE])-1,0),"")
    それを隣のセルにコピーします。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  2. コピーすると、当然、参照する行が移動して、
    @機種@種別に変わってしまうので、@機種に直してやる必要があります。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  3. 数式を直してやると、完成です。別シートにコピーしても数式は崩れません。元の集計表をコピーして新規シートに貼り付けてみましょう。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  4. 貼り付けた表の数式もテーブルの構造化参照なので、崩れません。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  5. 今度は、単価表の方に、製造メーカーを表示するための新規の列を追加します。
    VLOOKUP 関数とCOLUMN関数の組み合わせ
  6. 列を追加しても数式をコピーして、参照値を@機種にしてやればOKです。
    VLOOKUP 関数とCOLUMN関数の組み合わせ