Excel(エクセル)中級編：No.4-3 数式と関数(その3)

エクセルで良く使用される表や配列から値を取出す関数(VLOOKUP関数,HLOOKUP関数)について説明しています。

4.6 VLOOKUP・HLOOKUP

4.6 VLOOKUP・HLOOKUP　　　Topへ

表や配列からデータを検索して取出す。

4.6.1 VLOOKUP関数　（File:Ex_tyu05.xls Sheet1）

書式 VLOOKUP(検索値,範囲,列番号,検索の型)

検索値：範囲の左端の列で検索する値を指定します。検索値には、値、セル参照、または文字列を指定します。
範囲：目的のデータが含まれるテーブルを指定します。セル範囲の参照、または List、Database のような名前を指定します。
列番号：範囲内で目的のデータが入力されている列を、左端からの列数で指定します。
- 列番号が 1 より小さいときは、エラー値#VALUE!が返され、列番号が範囲の列数より大きいときは、エラー値#REF!が返されます。
検索の型
- TRUEを指定するか省略すると、検索値が見つからない場合に、検索値未満で最も大きい値が使用されます。
- FALSEを指定すると、検索値と完全に一致する値だけが検索され、見つからない場合はエラー値 #N/A が返されます。FALSEの代わりに0(ゼロ)を入力することもできます。

(練習)　セル範囲C3:D7にシート「商品コード表」から品名コードをキーにして、品名と単価を検索して取出しなさい。

(解答例)

計算式を入力するC3セルを選択します。
[関数の挿入]ボタンからVLOOKUP関数を選択します。
VLOOKUP関数の引数で検索値は「B3」、範囲は「商品コード表!$A$4:$C$24」、商品名は検索する表の2列目なので「2」、検索は完全一致とするので「FALSE」となります。
データシートでの数式は下図のようになります。

(練習問題1)　ところが・・・コードを入力していない時にはシートにエラーが表示されてしまいます。エラーが表示されないように、C,D,F列の数式を修正しなさい。

(解答例1-A)
『もし、B列のセルが入力されていなかったら(空白であったら)空白とする、そうでなかったら(入力されていたら)検索する。』とした場合。
=IF(B5="","",VLOOKUP(B5,商品コード表!$A$4:$C$24,2,FALSE))
のように書くことができます。

(解答例1-B)
『もし、検索値がなかったら(VLOOKUP関数がエラーとなったら)空白とする、そうでなかったら検索する』とした場合。
=IF(ISNA(VLOOKUP(B5,商品コード表!$A$4:$C$24,2,FALSE)),"",VLOOKUP(B5,商品コード表!$A$4:$C$24,2,FALSE))
と、ISNA関数を使います。他にはISERROR関数なども使えます。

単価の列(D列）も同様に=IF(B5="","",VLOOKUP(B5,商品コード表!$A$4:$C$24,3,FALSE))とします。

金額の部分(F列)は
『単価と数量のどちらかが空白の時は空白、そうでなかったら計算をする』とした場合。
=IF(OR(D5="",E5=""),"",D5*E5)

『単価と数量の値の数が2であれば計算し、そうでなかったら空白を返す』とした場合。
=IF(COUNT(D5:E5)=2,D5*E5,"")

(練習問題2)次の表を完成しなさい。（File:Ex_tyu05.xls Sheet3）
判定1は合計得点を判定表に基づいて判定します。