VLOOKUP関数(3) - セル範囲検索<エラー表示の処理> [Excel関数リファレンス]

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

 

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

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

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

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

 

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

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

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

 

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

                                                            

 

●●● 表を変えるとエラーが消える! ●●●

 

前回は、IF関数とISBLANK関数を利用して、ブランク時のエラー処理をしました。

 

ところが、もっと安直な別の方法もあります。

計算式ではなく、学生名簿の表の方をひと工夫するやり方です。 

まれにケガをするかも知れないチカラワザですが、、、

 

VLOOKUP関数FALSE_3.jpg

 

このワークシート、VLOOKUP関数だけで処理しているのに、エラーが消えてますね。

 

このシートでは、名簿の最後に、「0」という学生番号の行を付け加えています。同時に、

計算式の<対象範囲>も1行増えて、「$E$4:$H$24」となっています。

 

また、画面では見えませんが

  F24、G24、H24

のセルに「'」(シングルコーテーション)が入力してあります。

(これを入れないと、#N/Aエラーが表示されていたところに「0」が表示されてしまいます)

 

これで、学生番号が入力されていないところのエラーを消すことができるのです。

 

さて、タネあかしです。エラーが消えた理屈、わかりますか?

 

VLOOKUP関数は、1番目の引数である<検索した値>を、数値変数として扱っています。(推測です)

また、Excelでは、空のセルを数値として扱う場合、内部で「0」に変換されます。

このため、VLOOKUP関数で空のセルを検索すると、<対象範囲>から「0」を探しにいくようなのです。

これを逆手にとって、<対象範囲>に「0」の項目を作っておけば、その行が検索されてエラーが避け

られるという仕組みです。

 

次に、F24、G24、H24 に「'」(シングルコーテーション)を入力した理由です。

セルの先頭の「'」は、

  「これに続く値は文字列ですよ」

ということを明示的に表す特別な記号なのです。

たとえばセルに

  '7

と入力すると、それは数値ではなく、文字列であるとみなされるのです。

 

つまり、F24、G24、H24 に

  '

と入力したのは、これらのセルは「特にデータはないけど文字列だよ」ということを表しています。

これによって、VLOOKUPの計算結果に「0」が表示されるのを避けているのです。

 

ちょっと、深掘りしすぎたでしょうか。。。 

でも、シングルコーテーションを使った文字列入力まで一緒に覚えられる、お得感のある解説だと・・・

 

勝手に思っているのですが ^_^;) 

 

 

 

●●● 表の行数が変化する時には ●●●

 

さて、少し細かくなりすぎたので、話題をサクっと変えます。

 

今回、「学生名簿」の表に1行付け加えたわけですが、このように行を加えるたびに計算式を直して

いたのでは大変ですね。

 

そんな時、つまり <対象範囲> の表に逐次データが追加されていくような場合には、

<対象範囲> に列全体を指定してしまうというチカラワザがあります。

 

つまり、

B4の計算式を

  =VLOOKUP(A4,E:H,3,FALSE)

C4の計算式を

  =VLOOKUP(A4,E:H,4,FALSE)

としてしまうわけです。

 

ただし、実際には関係のない行まで検索対象になってしまうので、その点は注意が必要です。

たとえばこんな感じ。

VLOOKUP関数FALSE_4.jpg

 

セルA11に「<学生名簿>」と入力すると、1行目が検索され、セルG1、H1 が計算結果となってしまいます。

(セルG1、H1 が空なので、ゼロに変換されて「0」と表示されています)

 

 

今回はVLOOKUP関数の、一般にはあまり触れられていない点にまで言及してみました。

ほんとうは、文字列と数値の違いによって生じる問題などもあるのですが、VLOOKUP関数は、

ひとまず、ここまで!!