検索

キーワード


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

  • 公開日:2020-10-08 23:23:17
  • 最終更新日:2021-01-25 12:10:38
【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が表示されます。

売上明細シートにVLOOKUP関数を使った数式を入力


VLOOKUPによって参照された行の説明

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

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


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

売上明細シートでドラッグ&コピーした結果


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

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


文字列をTRUE検索した場合

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

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

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


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

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

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


その他の活用例

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

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

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


【著者】

田口

元建築・リフォーム営業のエンジニア。現在はDXの需要に合わせてネットワークやクラウド、セキュリティといったインフラ方面の勉強中。

よく読まれている記事
【Excel】箱ひげ図の見方と作成方法について

【Excel】箱ひげ図の見方と作成方法について

こんにちは。新人エンジニアの前山です。Excel グラフの作り方 ではグラフの作成方法とレイアウトの編集について基本的な事項を解説しました。本記事では、Excelで作成できる箱ひげ図の見方とを作成方法についての解説を行います。箱ひげ図とは箱ひげ図とは、データのバラツキ、どの部分に集中しているかなどを「箱」と「ひげ」を用いてわかりやすく表したものとなります。大量のデータを扱う場合、平均とのみを活用す

【Excel】複数条件で判定|AND関数,OR関数を使った条件分岐|IF関数

【Excel】複数条件で判定|AND関数,OR関数を使った条件分岐|IF関数

AND関数、OR関数を使って、IF関数で複数の条件を指定するこんにちは、新人エンジニアの田口です。今回は具体的な例を用いてIF関数の活用場面を見ていきます。主に以下の内容について解説します。・IF関数で部分一致、完全一致の条件指定を行う・OR関数・AND関数説明にあたって、以下の事柄について知識があることを前提とします。・IF関数の基本的な書式・引数など関数に関わる用語本文中でわからないことがあっ

【Excel】グラフの作り方、レイアウトの編集方法

【Excel】グラフの作り方、レイアウトの編集方法

こんにちは。新人エンジニアの前山です。本記事では、Excelでのグラフの作り方とタイトルや目盛線、凡例といったよく使用する機能の解説を行います。データテーブルや近似曲線はあまり使用頻度は高くありませんが、ここぞというところで使用すれば、グラフの品質を1段階上げることができます。グラフの作り方1.グラフにしたい表の選択 2.挿入>おすすめグラフのクリック 3.作成するグラフ種類の選択今回は例として集

【Excel】日付処理の活用例 - 前日、翌日の求め方

【Excel】日付処理の活用例 - 前日、翌日の求め方

こんにちは。新人エンジニアの前山です。前回の記事 ではExcelでの日付の取り扱いと基本的な使い方を記載しました。本記事ではその応用として、任意の日付の前日、翌日日付の求め方と関数の紹介をしたいと思います。関連記事: 【Excel】日付処理の活用例 - 指定した日付までの日数の求め方 【Excel】日付処理の活用例 - 月初、月末の求め方前日、翌日の計算方法前日、翌日日付には主に3つの求め方があり

【Excel】日付処理で使用する関数、シリアル値について

【Excel】日付処理で使用する関数、シリアル値について

こんにちは。新人エンジニアの前山です。本記事ではExcelで使用される日付関連の情報をご紹介します。Excel内では日付はシリアル値という数値で管理されており、仕組みを知れば、柔軟な日付計算が可能になります。また、日付には様々な関数が用意されており、翌日前日、期間、月初、月末、営業日などが求められます。日付についての理解を深めれば日々の業務がぐっと楽になります。シリアル値とはシリアル値とは、Exc