1. Home »
2. Excel講座の総目次 »
3. Excel基本講座の目次 »
4. VLOOKUP関数やINDEX関数(+MATCH関数)でテーブルを参照する数式を作成する

更新:2025年5月1日;作成:2017年11月18日

はじめに Topへ

• Excel2007以降でテーブル機能が追加されました。
  テーブル機能と関数とを組み合わせて利用することで、検索範囲などでデータの追加などの変化があった時に自動でその追加された範囲までを認識してくれるようになります。
  つまり、検索範囲などが変化したときは、従来のセル参照ではその範囲を修正する必要があります。テーブルの構造化参照を利用するとその範囲はこのテーブルのこの行やこの列といった風に指定できますので、行数や列などに変化があったも数式で修正する必要がなくなります。

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

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

テーブルを使う方法

• テーブルの作成の仕方は Excelでのテーブル機能の使い方 をご覧ください。

1. 商品コードのB3:D8セルを選択して、[ホーム]タブの[テーブルとして書式設定]→「オレンジン、テーブルスタイル」を選択して、テーブルに変換します。
   
2. テーブル内のセルを1つ選択して、テーブルツールを表示します。
   テーブルツールの[デザイン]タブのテーブル名で「商品コード」とテーブルの名前を変更しました。
   
3. G4セルの数式は =VLOOKUP($F4,商品コード,MATCH(G3,ドル商品コード[#見出し],0),FALSE) となります。
   • =VLOOKUP(検索値,範囲,列番号,[検索方法]) の検索値は $F4 と通常と同じです。
     範囲に テーブル名を指定します。 ここの例ではテーブル名の 商品コード を指定します。
     列番号は 2 でもよいのですが、数式の横へのコピーを考慮してMATCH関数を使っています。
   • MATCH関数の構文は =MATCH(検索値,検査範囲,[照合の種類]) となります。
     検査値は G3ドル と通常と同じです。
     検査範囲は 商品コード[#見出し] と指定します。テーブル 商品コードの見出し行 を指定しています。
     照合の種類は 0 とします。(昇順のデータではないので)
   • ちなみに、H4セルの数式は =VLOOKUP($F4,商品コード,MATCH(H3,ドル商品コード[#見出し],0),FALSE) となります。
     G4セルの数式をフィルハンドルのドラッグで、H4セルへコピーしています。
     そのご、G4:H4セルを選択して、下方向へフィルハンドルをドラッグしてG6:H6セルまでコピーすれば完成です。
     
4. 商品コードにデータが追加されました。すると、テーブルが自動で拡張されます。
   G4セルの数式は 何も修正することなく、正しい答えを返しています。
   

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

• VLOOKUP関数では左端の列に検索する値がないと検索することができません。
  検索するデータリストの列が入れ替わったりして、左端の列以外で検索したいときは INDEX関数を使います。
• INDEX関数でもテーブルを利用することができますので、その使い方を説明します。

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

• テーブル「商品コード2」の 品番の列と商品名の列とを入れ替えました。
  数式の変更をすることなく、以前と同じ数式で対応することができます。
  

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

• VLOOKUP関数を使った数式の作成方法

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

• ちなみに、数式の引数を利用しても構造化参照の形式で数式が作成できます。
  下図は B4:D9セルをドラッグして選択しています。すると、関数の引数の範囲に 商品コード とテーブル名が入力されています。
  
• テーブルの見出しや集計部を指定する方法
  
  項目指定子 参照先
  #すべて テーブル全体(見出し、集計すべて含む)
  #データ データ部分のみ(見出し、集計以外)
  #見出し 見出し部分のみ
  #集計 集計部分のみ
  @ 数式と同じ行のセルのみ
• 数式での使用例:F列には H列の数式が入力されています。
  テーブルの各参照先の文字列や数値が入力されているセルの数をカウントしています。
  
• @ の使い方:数式の入力されている行が対象になります。
  見積書[@単価] は テーブル名が見積書の単価の列のこの行を指します。 よって、下図では4,000のセルになります。
  
• 複数列の指定の仕方
  売上[@[4月]:[7月]] は数式のある行で、4月〜7月の列のデータ を指します。
  売上[[4月]:[7月]] は4月〜7月の列のデータ を指します。
  

スポンサードリンク
(追記) (追記ここまで)

---

(追記) (追記ここまで)

---

Home|Excel講座の総目次|Excel基本講座の目次|VLOOKUP関数やINDEX関数(+MATCH関数)でテーブルを参照する数式を作成する

PageViewCounter
Counter
Since2006/2/27