Excel(エクセル)基本講座：VLOOKUP関数の使い方

はじめに

• VLOOKUP関数のポイント
  • リスト範囲の左端列を検索して指定列位置の値を返します。
  • 左端列が昇順に並んでいるときは検索の型でTRUE(もしくは省略してもよい)を使用できますが、そうでない場合はFALSEとして完全一致で検索します。
  • 検索値が見つからない場合はエラー値#N/Aが返されます。(#N/Aエラーを表示しない方法はエラー処理をご覧ください)

　ブイルックアップ
=VLOOKUP(検索値,範囲,列位置,検索の型)
　　　検索の型：「TRUE」もしくは[省略]（「1」と入力してもOK)
　　　　　　　　検索値が見つからない場合に、検索値未満で最も大きい値が使用されます。
　　　　　　　　(注)検索範囲の値は昇順に並んでおく必要があります。
　　　検索の型：「FALSE」(「0」と入力してもOK)
　　　　　　　　検索値と完全に一致する値だけが検索され、見つからない場合はエラー値 #N/A が返されます。
　　　　　　　　文字列の検索に適します。

[A]　検索値が文字列の場合　(検索の型：FALSE)
　　　　(注)検索値が文字列の場合で昇順に並んでいる時は検索の型を[TRUE]でOKです。
　　　　　　ただし、昇順の並べ替えで基準は[ふりがな]を使用してはいけません。日本語の場合には要注意です。

• 【問題】
  • 商品一覧表（H3:J6)があります。
    見積書の品番を入力すると商品名と単価が自動的に入力されるようにC4:D6セルに数式を入れなさい。

  • 	B	C	D	E	F	G	H	I	J
    2		見積書					商品一覧表
    3	品番	商品名	単価	数量	小計		品番	商品名	単価
    4	B01	ラジオ	5,000	1	5,000		A01	テレビ	50,000
    5	C01	ビデオデッキ	20,000	2	40,000		B01	ラジオ	5,000
    6	A01	テレビ	50,000	3	150,000		C01	ビデオデッキ	20,000
    7				合計	195,000

• 【解答例】
  • C4セルの数式は
    =VLOOKUP(B4,$H$4:$J$6,2,FALSE)　または　=VLOOKUP(B4,$H$4:$J$6,2,0)となります。
  • 「B4セルの『B01』を範囲『H4:J6』の左端の列から探し、完全に一致した値が有ったら2列目の値を返す」という意味になります。
    • 検索値：品番の入力されているB4セルになります。
    • 範囲：商品一覧のデータ部分H4:J6を絶対参照で指定します。
      （範囲を絶対参照にするのはC4セルの数式をC5,C6セルにコピーする時値が変化しないようにするためです。）
    • 列位置：範囲の2列目ですので「2」とします。
    • 検索の型：文字の検索や固有品番などの検索では[FALSE] にします。[FALSE]のかわりに[0]でもOK。

  • 	B	C	D	E	F	G	H	I	J
    2		見積書					商品一覧表
    3	品番	商品名	単価	数量	小計		品番	商品名	単価
    4	B01	=VLOOKUP(B4,$H$4:$J$6,2,FALSE)	=VLOOKUP(B4,$H$4:$J$6,3,FALSE)	1	5,000		A01	テレビ	50,000
    5	C01	=VLOOKUP(B5,$H$4:$J$6,2,FALSE)	=VLOOKUP(B5,$H$4:$J$6,3,FALSE)	2	40,000		B01	ラジオ	5,000
    6	A01	=VLOOKUP(B6,$H$4:$J$6,2,FALSE)	=VLOOKUP(B6,$H$4:$J$6,3,FALSE)	3	150,000		C01	ビデオデッキ	20,000
    7				合計	195,000

エラー処理(#N/Aを表示しない)　　　　topへ

• 検索値が検索する範囲にないときはエラー#N/Aが表示されます。
  #N/Aが表示されないようにするにはいろいろな方法があります。

• ISNA関数やISERROR関数で処理する

  • VLOOKUP関数がエラーとなるか否かを調べてIF関数で対処します。
  • エラーの表示を無くするには　IF関数とISNA関数を使います。
    C4セルの例：=IF(ISNA(VLOOKUP(B4,$H$4:$J$6,2,FALSE)),"",VLOOKUP(B4,$H$4:$J$6,2,FALSE))
  • ISERROR関数でもOKです。
    =IF(ISERROR(VLOOKUP(B4,$H$4:$J$6,2,FALSE)),"",VLOOKUP(B4,$H$4:$J$6,2,FALSE))

• COUNTIF関数で処理する

  • 検索値が検索範囲にあるか否かをCOUNTIF関数で調べてIF関数で対処します。
  • =IF(COUNTIF($H$4:$H$6,B7)=0,"",VLOOKUP(B7,$H$4:$J$6,2,FALSE))
    なお、Excelでは1以上の数値はTrueと解釈されるので、以下のように書くこともできます。
    =IF(COUNTIF($H$4:$H$6,B7),VLOOKUP(B7,$H$4:$J$6,2,FALSE),"")

(Step Up)共通の数式に変更する

• C4セルの数式を　=VLOOKUP($B4,$H$4:$J$6,COLUMN(B1),FALSE)　とするとひとつの式で済みます。
  検索値を列固定の複合参照にし、列位置をCOLUMN関数で与えています。
  (D4セルへフィルハンドルをドラッグしてD4セルへコピーし、そのまま、フィルハンドルをC6:D6までドラッグしてコピーします。)
• (説明)
  C4セルの列位置「2」を、D4セルでは「3」になるように細工をします。
  COLUMN関数を使うと、右方向へコピーして値が1増加するので「2」を返す「COLUMN(B1)」とすれば良さそうです。
  この場合重要なのは「B」=「2」なのでCOLUMN(B100)でも良く、「B1」としたのは見た目だけ？のことです。

検索範囲に名前を定義する場合　　　　topへ

1. セル範囲 H4:J6 に『商品一覧表』と名前を定義します。
   （ポイント：検索列が一番左になるようにセル範囲を選択して、名前を定義します。）
   

   (A) メニューバーからの方法	(B)　名前ボックスを使う方法
   セル範囲 H4:J6 をドラッグして選択します。 メニューバーの［挿入]→[名前]→[定義]を選択します。 「名前の定義」ダイアログの名前に『商品一覧表』と入力し、[OK]ボタンをクリックします。 これで、セル範囲に名前が定義されます。	セル範囲 H4:J6 をドラッグして選択します。 「名前ボックス」に『商品一覧表』と入力し、　[Enter] キーを押します。 これで、セル範囲に名前が定義されます。

2. C4セルの数式は　=VLOOKUP(B4,商品一覧表,2,FALSE)　　D4セルの数式は　=VLOOKUP(B4,商品一覧表,3,FALSE)　とします。
   • 列位置をCOLUMN関数で置き換え、C4セル=VLOOKUP(B4,商品一覧表,COLUMN(B1),FALSE)とすれば、D4セルへコピーすればOK。
3. C4：D4セルを選択し、オートフィルでC6:D6まで数式をコピーして完成です。

• 	B	C	D	E	F	G	H	I	J
  2		見積書					商品一覧表
  3	品番	商品名	単価	数量	小計		品番	商品名	単価
  4	B01	=VLOOKUP(B4,商品一覧表,2,FALSE)	=VLOOKUP(B4,商品一覧表,3,FALSE)	1	5,000		A01	テレビ	50,000
  5	C01	=VLOOKUP(B5,商品一覧表,2,FALSE)	=VLOOKUP(B5,商品一覧表,3,FALSE)	2	40,000		B01	ラジオ	5,000
  6	A01	=VLOOKUP(B6,商品一覧表,2,FALSE)	=VLOOKUP(B6,商品一覧表,3,FALSE)	3	150,000		C01	ビデオデッキ	20,000
  7				合計	195,000

  　(上の表はExcelへコピーすると #NAME?　エラーとなります。　セル範囲 H4:J6 に『商品一覧表』と名前を定義するとエラーが解消されます。)

[B]　検索値が数値範囲の場合　(検索の型：TRUE)　　　　　topへ
　　　　(注)検索値が昇順に並んでいる必要があります。
　　　　　　検索値が数値の範囲ではなく、その値に対応する時は[FALSE]とします。

• 【問題2】成績の評価を付けたいと思います。右の評価一覧表（G4:J6)にしたがって、D4:E6セルに数式を入力しなさい。
  • なお、G4セルは0以上、G5セルは40以上、G6セルは70以上を表現しています。

  • 	B	C	D	E	F	G	H	I	J
    2							評価一覧表
    3	名前	得点	評価A	評価B		得点		評価A	評価B
    4	今田	50	△	良		0	以上	×	不可
    5	河野	20	×	不可		40	以上	△	良
    6	山田	70	○	優		70	以上	○	優

• 解答例2-1　

  • 解答例1とほとんど同じですが、数値の範囲での検索になりますので、検索の型を[TRUE]または「1」もしくは[省略]します。
    検索の型が「TRUE」のとき、検索値が見つからない場合に、検索値未満で最も大きい値が使用されます。つまり、○○以上の○○が返されます。
    • 検索の型を省略：=VLOOKUP(C4,$G$4:$J$6,3)
    • 検索の型をTRUE：=VLOOKUP(C4,$G$4:$J$6,3,TRUE)
    • 検索の型を1 　：=VLOOKUP(C4,$G$4:$J$6,3,1)
    • 上の3つは同じ結果になります。
  • この場合、得点欄の値以上の意味になります。得点欄（G列)は昇順に並んでいることが必要です。
    

    	B	C	D	E	F	G	H	I	J
    2							評価一覧表
    3	名前	得点	評価A	評価B		得点		評価A	評価B
    4	今田	50	=VLOOKUP(C4,$G$4:$J$6,3)	=VLOOKUP(C4,$G$4:$J$6,4)		0	以上	×	不可
    5	河野	20	=VLOOKUP(C5,$G$4:$J$6,3)	=VLOOKUP(C5,$G$4:$J$6,4)		40	以上	△	良
    6	山田	70	=VLOOKUP(C6,$G$4:$J$6,3)	=VLOOKUP(C6,$G$4:$J$6,4)		70	以上	○	優

• 解答例2-2　検索範囲に名前を定義して使う方法

  • セル範囲 G4:J6 に『評価一覧』と名前を定義すると、
    • D4セルの数式は =VLOOKUP(C4,評価一覧,3)
    • E4セルの数式は =VLOOKUP(C4,評価一覧,4)
    となります。
  • D4:E4 セルを選択し、オートフィルでD6:E6セルまで数式をコピーして完成です。
  • 名前の定義方法：G4:J6を選択し、挿入→名前→定義　で名前に「評価一覧」と入力する。
  • 別解：D4セル=VLOOKUP(C4,評価一覧,COLUMN(C1))　として、E4セルへコピーしてもOKです。

[C]　検索表を使用しない方法　　(数式中に検索範囲のデータを書く方法)　　　　topへ

• (解答例2-3)　シートに検索表を作成しない方法
  • 数式内に対応する値を並べて記述します。
  • D4セルの数式　=VLOOKUP(C4,{0,"×","不可";40,"△","良";70,"○","優"},2)
  • E4セルの数式　=VLOOKUP(C4,{0,"×","不可";40,"△","良";70,"○","優"},3)
  • D4:E4 セルを選択し、オートフィルでD6:E6セルまで数式をコピーして完成です。

[D]　検索値が見つからず #N/Aエラー が返される場合　　　topへ

• シート上で検索値が検索範囲内の値と同じ値が入力されているように見えているのに、エラーとなるときがあります。

1. 検索値にスペースが紛れ込んでいないか確認してみます。特に最後にスペースが含まれる場合は気づきにくいです。
   • スペースの削除は【編集】→【置換】を使い、半角スペースと全角スペースを取り除きます。
     検索する文字列：半角または全角スペースを入力します。(画面では見た目の変化はないです)
     置換後の文字列：何も入力しません。
     『半角と全角を区別する』にチェックは入れません。(【オプション】をクリックすると表示されます)
   • 【すべて検索】ボタンをクリックし、【すべて置換】をクリックすると、一度に置換されます。
     確認しながら置換するときは、【次を検索】と【置換】を使います。
     
2. 他のソフトなどで作成したデータで空白文字CHAR(160)が紛れ込んでくることがあります。
   • CHAR(160)はTRIM関数やCLEAN関数では取り除くことができないので、SUBSTITUTE関数で取り除きます。その後、形式を選択して貼り付けの「値」でデータを置き換えます。
     • =SUBSTITUTE(A1,CHAR(160),"")
3. 表示形式は文字列であるが、データは数値と認識されていることがあります。
   この様な場合は、=TYPE(A1)のようにTYPE関数を使ってセルのデータ形式を確認します。
   • 数値データを文字列に変更したいときは、【データ】→【区切り位置】のウィザードで「文字列」を指定します。
   • 文字列の数字を数値に変更したい場合は、どこかのセルに数値の「1」を入力しコピー、変更したいセルを選択して形式を選択して貼り付けで「乗算」にチェックを入れて【OK】とします。

   • セルのデータ型を調べる　　　topへ
     　タイプ
     =TYPE(データタイプ)
     　　　数値は「1」 、テキストは「2」 、論理値は「4」
     　　　エラー値は「16」 、配列は「64」を返します。

     1. C列にD列のようなそれぞれの値や数式を入力します。
     2. C5,C6はそれぞれの結果「FALSE」「#VALUE!」が表示されます。
     3. C7はTYPE関数の戻り値「64」が表示され、E7は戻り値「64」は数値なので、「1」が返されます。
        

        	B	C	D	E	F
        2	データタイプ	入力データ	C列の内容	TYPE関数の戻り値	E列の数式
        3	数値	10	←10	1	←=TYPE(C3)
        4	テキスト	文字です	←文字です	2	←=TYPE(C4)
        5	論理値	FALSE	←=A1="moji"	4	←=TYPE(C5)
        6	エラー	#VALUE!	←=B1/B2	16	←=TYPE(C6)
        7	配列	64	←=TYPE({1,2;3,4})	1	←=TYPE(C7)