VLOOKUP関数の「近似値」(条件TRUE)の実際の動作を、徹底検証する

  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る
近似値と完全一致の動作見本 数値

VLOOKUP関数の構文は、次のようになっていますね。

= VLOOKUP (検索値, 検索範囲, 戻り値の列番号, 検索条件)

今日は、この構文の最後の引数、「検索条件」のお話です。

「検索条件」は、「TRUE(近似一致)」または「FALSE(完全一致)」で指定します。

検索条件TRUEの動作とはどんなものか?

エクセルの入門者などでは、「この検索条件を省略すると、予期しない結果を返すことがありますので、必ず『FALSE(完全一致)』を指定してください」と書いてあることがあります。

実際の業務では、この「検索条件」を使いこなすことで、効率化に貢献する場合があります。ただし、実際に「TRUE(近似一致)」を指定したら、どういう動作をするのか?? 、また省略した場合はどう動作するのか? ということについて、正確に知った上で使わないと、期待した通りの結果が得られません。

検索範囲が「昇順」でなければならない理由とは?

また、VLOOKUP関数の仕様上、「検索範囲は昇順に並べかえて下さい」となっていますが、はたして昇順になっていない場合は、どんな動作をするのでしょうか?

この記事では、それを実際に検証してみたいと思います。

※2017/11/30更新 文字列の場合のサンプルが昇順になっていなかった誤りがあり、関連記述を修正しました。ご指摘いただいた読者様ありがとうございました。

「検索条件」を省略した場合の動作にひそむ、落とし穴

Excelの仕様では、この「検索条件」を省略した場合は「TRUE」(近似一致)となる、ということですが、実は、次の二通りの記述で動作が異なります。

  • = VLOOKUP (検索値, 検索範囲, 戻り値の列番号, )  —- 「FALSE(完全一致)」となる(省略していないとみなされる)
  • = VLOOKUP (検索値, 検索範囲, 戻り値の列番号) —- 「TRUE(近似一致)」となる(省略しているとみなされる)

違うのは最後の「, (コンマ)」一個だけです。

「, (コンマ)」があることで、省略していないとみなされ、空白値「ゼロ」があるとみなされます。「ゼロ」は「FALSE」と同義ですので、このような動作になります。

「近似一致」と「完全一致」の動作を詳しく比較する(数値の場合)

次に、両者の動作をくわしく比較し、「近似」とは実際にどんな意味なのかを検証します。まず、数値の場合です。

数値の場合の動作検証用のシート

近似値と完全一致の動作見本 数値

近似一致と完全一致の動作サンプル(数値の場合)

B2, E2セルの数式はそれぞれ、次の通り入力されています。

  • 近似一致: =VLOOKUP(A2,Sheet2!$A$1:$B$3,2,TRUE)
  • 完全一致: =VLOOKUP(D2,Sheet2!$A$1:$B$3,2,FALSE)

つまり、数値に対して近似一致を指定した場合、「検索値を超えない範囲の最大値が戻り値となる」ということかな?? と思われます。

検索範囲が「昇順でない」の場合の動作

では、こんどは、このシートの検索範囲を、上下逆に(つまり降順に)配置してみましょう。

上下逆にした場合

検索範囲を上下逆にした場合の結果

検索範囲を上下逆にした場合の結果です。

「完全一致」の結果はさきほどと全く同じですが、「近似一致」のほうは、なんともいいがたい、不可解な結果が出ます。

興味のある方は、「なぜこんな戻り値になるのだろう?? どういう検索の仕方をしているのかな・・・」と考えてみるのも良いですが、これは簡単に理解できる結果ではありません。

VLOOKUP関数は、検索範囲が昇順(小さい順)にきちんと並んでいないと、不可解な、意味のない結果を返す、ということがわかります。

参考: どうやら「二分探索(バイナリサーチ)」と呼ばれるアルゴリズムで検索しているようです。単純に「上から順にみている」といったものでは全くありません。 →Wikipedia 「二分探索」

VLOOKUPで、数値データを「近似一致」で検索した場合の動作、結論。

結論としては、

検索範囲を正確に昇順に整列した条件のもとでのみ、検索値を超えない範囲の最大値を返す。

検索範囲が昇順でない場合は、確実な動作が見込めません。

正確に動作させるには、念のため、検索範囲の方を昇順で並べ替えておくとよいでしょう。

また、「検索値を超えない範囲の最大値」を戻り値とするため、実際上は「切り捨て」と同様の意味になります。もしこれを「四捨五入」にしたい場合には、

=VLOOKUP(ROUND(A2),Sheet2!$A$1:$B$3,2,TRUE)

のように、あらかじめ検索値を四捨五入しておくとよいでしょう。

「近似一致」と「完全一致」の動作を詳しく比較する(文字列の場合)

次に、文字列で検索する場合を見てみましょう。

文字列でVLOOKUP

近似一致と完全一致の動作サンプル(文字列の場合)

文字列の場合は、近似一致の結果は、前方一致のようです。

ただし、

「検索値→範囲」 ではなく、

「範囲の各値→検索値」の前方一致

ですね。

  • 「1こ」→N/A
  • 「1こめの次」→1
  • 「1こめ半だ」→1.5

複数が一致する場合はできるだけ多くの文字が一致するものが返っているようです。

「1こめ半」→1も1.5も一致するが1.5が返っている

「2こめちょい」→2のみ一致するため2が返っている

「2こめとちょい」→2も2.3も一致するが2.3が返っている

この項は、2度改訂しております。経緯を簡単に記し、ご参考に供させていただきます。

初稿→サンプルに「2こめとちょい」がない状態→結論は現在と同じでした。

第2稿→「2こめ」の下に「2ことちょい」を追加したところ、動作が不可解となったため、いったん「文字列では使えない」という結論に変えましたが

第3稿(現在)→読者様のご指摘により、「2ことちょい」は、昇順だと「2こめ」より前なのに、誤って後に置いていたことから「昇順で並べ替え」のルールに反して、動作が不可解になっていたことが判明。「2ことちょい」→「2こめとちょい」に直したところ、やはり昇順になっていれば前方一致となりました。

VLOOKUPで、文字列データを「近似一致」で検索した場合の動作、結論。

結論としては、

文字列に対してVLOOKUPを使用した場合、近似値で検索すると「前方一致」となる。

ただし、検索値→範囲の各値の前方一致ではなく、範囲の各値→検索値 の前方一致となる

※文字列の場合も、範囲が昇順に並べ替えられていない場合は、まったく意味のない結果を返します。

近似一致で適用できない場合は、他の関数と併用しよう

以上のような条件にあてはまるようなケースでは、VLOOKUP関数の「近似一致」を使うことができます。ぶぶん

しかし、特に文字列による検索の場合などは、いま検証したような動作では使えない場合がとても多いと思います。

そのような場合には、LOOKUP関数、MATCH関数、INDEX関数、OFFSET関数などと組み合わせて、あらかじめ検索値または検索範囲を変化させ、「完全一致」のVLOOKUP関数に落とし込むことで、思い通りの検索を行うことができます。

このような例については、いずれ別の記事で書いていきたいと思います。

 

キュリオステーション志木店
  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る

志木駅前でエクセル講座を開講しています

パソコン教室・キュリオステーション志木店では、東武東上線・志木駅前でエクセル講座を開講しています。

「Excel基礎コース」では、一般事務レベルのエクセルスキルを学べます。

そして、「Excel応用コース」「Excel活用コース」と、よりハイレベルの講座もご用意。

もちろんMOS資格対策講座も開講中。MOS 2010/2013/2016全バージョンにいちはやく対応し、本番試験も受験できます。

よくある「ビデオ授業」や「一斉授業」では聞けない、「ちょっとしたコツ」も満載です!

ちょっとでも興味を持っていただいた方は、お気軽に無料体験レッスンのご予約をどうぞ!

エクセルコースのページを見る

SNSでもご購読できます。

コメント

  1. コメントそしてご指摘ありがとうございます。
    まったく初歩的な確認ミスでお恥ずかしい限りでございます。文字列の場合のサンプルを再確認したところ、11/30で「2こめとちょい」としたところが「2ことちょい」となっており、昇順に並べ替えした場合は「2こめ」よりれ上にあるべきでした。
    この結果、「2ことちょい」の部分が不可解な結果となり、全体として「文字列の場合は使えない」という結論となっておりましたが、
    「2こめとちょい」に変えたところ、11/30改訂版のように「前方一致」の結果となりました。
    VLOOKUP関数は、一般に「範囲を昇順に並べ替える必要がある」ということは常識の範囲なのですが、初めて学ぶ方もそうでない方も含めて、「では、昇順でなければ一体どうなってしまうのか」を見たことがない方が多数いらっしゃいます。
    本記事は、そういった方むけに、「実際にやってみる」ことによって、並べ替えの必要性を理解しよう、という主旨で書いてみたものです。
    はからずも、執筆側のミスにより、ますますその必要性がはっきりした・・・という展開でございました。
    今回、この趣旨が分かりにくい文脈になっていることも修整させていただきました。

    ご指摘ありがとうございました。また検索で見かけられた際には、当ブログをよろしくお願いいたします。

  2. 事務員(雑用係) より:

    GoogleでVLOOKUP,TRUEを検索したところ、こちらのサイトが1番にヒットしたので立ち寄りました。
    VLOOKUPの動作に誤解があるようでしたのでコメントを残しておきますね。

    記事中でVLOOKUPの返り値がおかしくみえるところは、検索キーがきちんと昇順になっていないせいで、理解し難い挙動になっています。
    たとえば、
    『最も不可解なのは「2ことちょい」(近似一致)→1.5』
    とありますが、これは検索対象(A列)が昇順(アイウエオ順)になっていないからです。
    「2こ“め”」と「2こ“と”ちょい」を比べると、タ行の“と”の方がマ行の“め”より先ですよね。
    「1こめ半」(1.5) < 「2ことちょい」 < 「2こめ」(2) なのでここは1.5が返ります。
    他の所についても
    •「2こ」→1.5なのも同様に 「1こめ半」(1.5) < 「2こ」 < 「2こめ」(2) です。
    •「1こ」→N/Aになるのは、 「1こ」<「1こめ」 で検索にヒットしないからですね。

    検索値と検索対象を昇順で並べてみると理解しやすくなると思います。
    なお、「2ことちょい」は辞書順でややこしい位置に来るので「2こめとちょい」に改めました。
    カンマ区切りで見にくいでしょうから、ExcelにCSV形式で読み込ませてみてください。

    検索対象, 値
    1こめ, 1
    1こめ半, 1.5
    2こめ, 2
    2こめとちょい, 2.3
    2こめ半, 2.5
    3こめ, 3
    検索値, TRUEで検索, FALSEで検索
    (1こめ), #N/A, #N/A
    (1こめ半), #N/A, #N/A
    1こ, #N/A, #N/A
    1こめ, 1, 1
    1こめの次,1 , #N/A
    1こめ半, 1.5, 1.5
    1こめ半だ, 1.5, #N/A
    2こ, 1.5, #N/A
    2こめ, 2, 2
    2こめとちょい, 2.3, 2.3
    2こめ半, 2.5, 2.5
    2こめ半だ, 2.5, #N/A
    3こめ, 3, 3

コメントを残す

*

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください