VLOOKUP関数(1) - セル範囲検索 [Excel関数リファレンス]

[書式] VLOOKUP(<検索したい値>,<対象範囲>,<列番号>,[検索方法])

 

[機能] 1.[検索方法]に TRUE を指定(もしくは省略)した場合

        <検索したい値> 以下の最大値を <対象範囲> の1列目からさがし、

                                           その行の <列番号> 目のセルの値を返します。

        <対象範囲> は、必ず1列目が昇順で並べ替えられていなければなりません。

 

      2.[検索方法]に FALSE を指定した場合

        <検索したい値> を <対象範囲> の1列目からさがし、その行の <列番号> 目のセルの値を返します。

        <対象範囲>の並び順は任意です。

 

[引数] <検索したい値> 検索したい値やセルを指定します。
<対象範囲> 検索対象となる範囲を指定します。この範囲の1列目が検索対象となります。
<列番号> <検索範囲> の何列目を返したいかを指定します。「1」を指定すると、検索された値そのものが返ります。
[検索方法] TRUE 、FALSE のいずれかを指定します。省略は TRUE とみなされます。

                                                            

 

●●● 忘れられた過去 ●●●

 

VLOOKUP関数はその昔、4番目の引数が存在していませんでした。

ちょっと意外かもしれませんが、本当の話、

90年代前半、まだMS-DOSから

 C>win

なんてやってWindowsを起動いた、素敵な時代の話です。

 

当時のVLOOKUP関数は TRUE の用法のみ、つまり、範囲検索専門の関数でした。

ですから、

  「検索」

というよりむしろ、

  「値のレンジによって異なるデータを取り出す」

という、いかにも「表計算」らしい関数だったわけです。

 

そんな歴史から察するに、VLOOKUP関数は[検索方法]の TRUE と FALSE で、

きっちり区別して考えた方が理解しやすいかもしれません。

 

ということで、今回は、まずその本流ともいえる TRUE での用法について見て

いきましょう。

 

 

●●● IF関数の拡張版 ・・・・のような役割 ●●●

 

さて、こんなケースには、どのように対処しますか?

所得金額から対応する控除額を求めたい、そんなケースです。 

配偶者特別控除.jpg 

そう、条件によって値を変えるという面では、IF関数を利用する方法もありますね。

でも10種類以上の条件判断。

IF関数の入れ子は7個まで・・・

ちょっと、、、つらすぎます。

 

というわけで、VLOOKUP関数の出番となるわけです。

上記の要件をささっとExcelで実現すると、次のようになります。

セルB1の値を変えることで、それに応じた控除額がB3に計算されます。

  VLOOKUP関数_1.jpg 

ここでは、セルB3に

  =VLOOKUP(B1,D2:E12,2,TRUE)

と入力しています。この計算式は、

  「セルD2~E12の範囲の1列目から、セルB1が該当する範囲の行を見つけ、

                                  その2列目の値を返しなさい」

という意味になります。

 求めたい値が<対象範囲>の2列目にあるので、<列番号>は

  2

となります。

 

 

 

●●● ポイントは3つ ●●●

 

TRUE の用法のVLOOKUP関数のポイントは3つです。

 

1.<対象範囲>の1列目が必ず昇順に並んでいること

[検索方法]に TRUE を指定する用法では、<対象範囲>の1列目が、必ず昇順に並んでいなければ

なりません。この点が、FALSE の用法と大きく違うところなので、注意が必要です。

 

2.1列目の値を、「検索したい値以下の最大値」に相当するように正しく指定する

たとえば、

  「450,000円から499,999円」

というレンジに対して、1列目の値をどのように設定するかというのは、ちょっと悩むところかもしれません。

そんな時には、「検索したい値以下の最大値」ということを考えながら<対象範囲>の表を作成しましょう。

 

また完成後、計算元の値に境界値となる

  449999、450000、499999、500000

などを入力して、計算結果が正しく得られるか確認するのも、大切なことです。

(こんなテスト方法を、ちょっとむずかしい言葉で、「限界値分析」と言います) 

 

3.エラー値を避けるには、「0」のケースを指定しておく

「検索したい値以下の最大値」が<対象範囲>の1列目に存在しないと、エラー値

  #N/A

が計算結果として返ります。

これを避けるには、多くの場合、「0」のケースを指定することで解決します。(マイナス値を扱う場合は別です) 

 

さて、VLOOKUP関数の FALSE での用法は、また次回!!