【Excel】VLOOKUP関数で「TRUE」を使って近似値検索を行うパターンの動きを確認する

こんにちは、新人エンジニアの田口です。

今回は具体的な例を用いてVLOOKUP関数の活用場面を見ていきます。

主に以下の内容について解説します。

・VLOOKUP関数を使った検索において、「TRUE」の使用が必要なパターン

説明にあたって、以下の事柄について知識があることを前提とします。

・VLOOKUP関数の基本的な書式

・引数など関数に関わる用語

・相対参照、絶対参照の違い

本文中でわからないことがあった場合は以下の記事をご覧ください。

【Excel】関数入門｜数値と文字列の違いや、代表的な関数について解説

【Excel】大きな表からデータを転記・検索する！VLOOKUP関数の使い方

【Excel(一般)】コピーで参照がずれてしまう時の対処砲 － 絶対参照と相対参照について

最も近い値を検索する「TRUE」で、複数の○○以上○○未満の分岐処理を行う

VLOOKUP関数の最後の引数である「検索方法」では、完全一致検索を行う「FALSE」を選択するのが一般的です。

しかし、今回紹介するように、場合によっては「TRUE」を使った検索が必要になる時があります。

商品の重量に応じた送料を計算する

それでは実際に見ていきます。

今回の作業の目的は、重量によって変動する送料を表示することです。

「送料表」シートにある算出方法に従い、「売り上げ明細」シートの送料欄に記載します。

条件を整理する

今回の作業は、値に応じて送料を変動させる条件分岐です。

以下のように表現できます。

・「0」以上「30」未満の時は送料2000

・「30」以上「60」未満の時は送料2500

・「60」以上「100」未満の時は送料3000

条件分岐と聞くと真っ先に思いつくのがIF関数ですが、IF関数で書こうとすると入れ子構造になって数式が複雑になってしまいます。

例：IF関数で入力した場合

=IF(D3<送料表!$D$4,送料表!$E$4,IF(D3<送料表!$D$5,送料表!$E$5,IF(D3<送料表!$D$6,送料表!$E$6,"発送不可")))

こんな関数は誰も使いたくありません。

上記のように、「～以上～未満」という条件が続く場合はVLOOKUP関数を使うことで解決できます。ただし、今回は検索の型を「TRUE」(近似値検索)にして使います。

TRUE　近似値検索とは

ここで近似値検索について確認しておきます。Excelにおいて、TRUEを用いた近似値検索とは、

①完全一致する場合はその値を返し、

②一致する値がなかった場合に

③正確に昇順で整列された表のもとで

④範囲で指定した列のうち一番左の列を基準として、

⑤指定した検索値より小さいデータのうち、

⑤最も近い値(近似値)の行の値を取得する

機能のことを言います。

少々複雑なので、説明するより見た方が早いと思うので、実際に今回の例で動きを見てみましょう。

数式化する

「売り上げ明細」シートに以下の数式を入力します。

=VLOOKUP(D3,送料表!$B$4:$E$6,4,TRUE)

すると、送料表30～60の値として指定されている2500が表示されます。

本来、今回の検索値である50という値は送料表には含まれていません。

ですが、TRUE検索によって、一番左の列のうち、50より小さい値の中で最も近い30の行の送料が参照されました。

コピーしても正常に表示されます。

なぜこうなるかについては詳細なアルゴリズムが私もわかっていないので説明を省きます。

こういうものだと理解してください。

文字列をTRUE検索した場合

今回の例では数値を検索しました。

では文字列の場合はどうでしょうか。

「田中」でTRUE検索をすれば「田中太郎」が返されるように見えますが・・・

結論から先に言うと、基本的には使えません。

ワイルドカード「＊」を使えば検索値の文字列を含む文字列を検索できたりもしますが、

近似値検索と部分一致検索は別物なので、文字列の操作には向かないと考えていいかもしれません。

その他の活用例

VLOOKUP関数の様々な使い方について解説しています。

【Excel】VLOOKUP関数で、検索値に対応する二つの値を表示させる方法

【Excel】IF関数の代わりにVLOOKUP関数を使った条件分岐のやり方。