VLOOKUP関数やINDEX関数(+MATCH関数)でテーブルを参照する数式を作成する


スポンサードリンク


VLOOKUP関数でテーブルを利用する INDEX関数でテーブルを利用する
構造化参照を使った数式の作成方法

はじめに       Topへ

VLOOKUP関数でテーブルを利用する       Topへ

  1. VLOOKUP関数を使って、品番を入力すると商品リストから商品名や単価を取り出す、といった使い方です。
    通常の数式では =VLOOKUP($F4,$B$4:$D$8,MATCH(G$3,$B$3:$D$3,0),FALSE) となります。
    構文は =VLOOKUP(検索値,範囲,列番号,[検索方法]) ですので、列番号を上の数式では MATCH(G$3,$B$3:$D$3,0) としていますが、 2 としてもOKです。
  2. この数式だと、商品コードの9行目にデータが追加されたら=VLOOKUP($F4,$B$4:$D$8,MATCH(G$3,$B$3:$D$3,0),FALSE) のままではエラー #N/A となり、検索ができません。
  3. 数式を =VLOOKUP($F4,$B$4:$D$9,MATCH(G$3,$B$3:$D$3,0),FALSE) と修正する必要があります。
    $B$4:$D$8 → $B$4:$D$9 と変更しています。

テーブルを使う方法

  1. 商品コードのB3:D8セルを選択して、[ホーム]タブの[テーブルとして書式設定]→「オレンジン、テーブルスタイル」を選択して、テーブルに変換します。
  2. テーブル内のセルを1つ選択して、テーブルツールを表示します。
    テーブルツールの[デザイン]タブのテーブル名で「商品コード」とテーブルの名前を変更しました。
  3. G4セルの数式は =VLOOKUP($F4,商品コード,MATCH(G$3,商品コード[#見出し],0),FALSE) となります。
  4. 商品コードにデータが追加されました。すると、テーブルが自動で拡張されます。
    G4セルの数式は 何も修正することなく、正しい答えを返しています。

INDEX関数でテーブルを利用する       Topへ

  1. VLOOKUP関数を使わずに、INDEX関数とMATCH関数に置き換えただけです。
    G3セルの数式は =INDEX(商品コード2,MATCH($F3,商品コード2[品番],0),MATCH(G$2,商品コード2[#見出し],0)) となります。
    INDEX関数の構文は =INDEX(配列,行番号,列番号) となります。
    配列は テーブル名を指定します。 ここの例では テーブル名は 商品コード2 としています。
    行番号は MATCH関数で 「商品コード2」テーブルの「品番」の列で検索しますので、MATCH($F3,商品コード2[品番],0) となります。
    列番号は VLOOKUP関数の時と同じで MATCH(G$2,商品コード2[#見出し],0) とします。
  2. ところが、G3セルのフィルハンドルをドラッグして、H3セルにコピーすると・・・・エラーになってしまいました。
    原因は MATCH($F3,商品コード2[品番],0) のままでないといけないのに、MATCH($F3,商品コード2[商品名],0) に変化しているためです。
    数式でいうところの絶対参照でないといけないところです。
  3. テーブルを参照するときには絶対参照をどう指定するのか・・・列を範囲で指定すればよいようでした。
    つまり、商品コード2[品番] を 商品コード2[[品番]:[品番]] とすることで、コピーしても相対参照みたいに変化せず、絶対参照のような使い方ができました。

構造化参照を使った数式の作成方法       Topへ

  1. 数式バーに数式を入力していく方法で説明します。
    =VLOOKUP($F4, と入力したら、マウスでデータ範囲 B4:D9 をドラッグして選択します。数式バーには テーブル名 商品コード が入力されます。
  2. =VLOOKUP($F4,商品コード,MATCH(G$3, 続きを入力します。
    MATCH関数の検査範囲 B3:D3 をドラッグして選択します。数式に 商品コード[#見出し] と入力されます。
  3. =VLOOKUP($F4,商品コード,MATCH(G$3,商品コード[#見出し],0),FALSE) と、残りの部分を入力して、数式は完成です。

スポンサードリンク


HomeExcel 2016の使い方の目次Excel 2016でのデータの入力|VLOOKUP関数やINDEX関数(+MATCH関数)でテーブルを参照する数式を作成する