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

近似値と完全一致の動作見本 数値 Excel(エクセル)
Excel(エクセル)

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

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

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

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

スポンサーリンク

この記事で扱っている内容

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

検索条件TRUEの場合の動作について、一般にはこのように説明されます。

TRUE を指定するか省略した場合、近似値が返されます。 つまり、完全に一致する値が見つからない場合は、検索値未満の最大値が使用されます。

この「TRUE」を使いこなすことで、効率化に貢献する場合があります。ただし、実際に「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)

検索値「以下」の最大値が戻り値となる

数値に対して近似一致を指定した場合、「検索値以下の最大値」が戻り値となります。

※解説書には「検索値未満の最大値」と書いてあるけど?

次のような表現がされている解説書が多いです。

「完全に一致する値が見つからない場合は、検索値未満の最大値が使用されます。」(Officeサポートサイト「HLOOKUP関数」より)

ここに「検索値未満」の言葉がありますが、「完全に一致する値が見つか」った場合は、その一致した値が戻りますので、

通して意味を考えれば、結局「検索値以下の最大値」が正しいのです。

前半の「完全に一致する値が見つからない場合は」を抜かして、単に「検索値未満の最大値」と言うと、これは誤りになります。

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

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

上下逆にした場合

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

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

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

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

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関数に落とし込むことで、思い通りの検索を行うことができます。

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

パソコン教室・キュリオステーション志木店からのお知らせ
レッスンはオンラインで受講できます

パソコン教室・キュリオステーション志木店では、本年よりオンラインでの在宅レッスンを実施しております。
教室の全コースがオンラインで受講可能。実際にインストラクターがご対応いたします。
1時間の無料体験レッスンはいつでも予約できます。詳しくは公式ページをご覧ください。

スポンサーリンク
キュリオステーション志木店運営をフォローする
志木駅前のパソコン教室・キュリオステーション志木店のブログ

コメント

  1. 茂木 浩之 より:

    初めてメールさせていただきます。
    ブログ拝見して参考にさせていただいております。
    vlookupのtureについて実質検索値以下との見解とてもよくわかりました。スッキリしました!
    ありがとうございました。
    ところでテキストにvlookupのtureについては検索値未満と記載されているのですがmatchの検索方法でも1 には検索値以下と記載されているのですが明確な理由があるのでしょうか?
    初めてのメールで質問するのは失礼かと思いましたが機会があればブログでもご説明いただければと思います。
    ブログの更新楽しみにしています。

    • コメントありがとうございます。そして、細かいところまで読んでいただいてありがとうございます。
      (記事中のHLOOKUP関数の解説のところ、リンクが何か変わっており矛盾しておりましたので修正しました。)
      「見つからない場合は検索値未満」と「検索値以下」の両表現が混在しているのは、確かに不可思議なのですが、もしかしたら、もとの解説が英文でそのようになっており、翻訳した表現がそのまま残っているだけなのかもしれないですね。
      HLOOKUP function | Microsoft Support
      if an exact match is not found, the next largest value that is less than lookup_value is returned
      多くの企業で365への転換が進んでまして、時代はXLOOKUPになっていくのかもしれません。XLOOKUP関数の解説には、またなにか違う独特の表現が使われています。そのうちこれも追記しないといけませんね。
      XLOOKUP 関数 | マイクロソフトサポート

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

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

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

    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

タイトルとURLをコピーしました