Windows 10 & Office 2016 Excel 2016

参照範囲から検索値以下の一番近い値を求める方法(VLOOKUP関数)

投稿日:2017年10月13日 更新日:

通し番号を印刷した展示会の案内状があります。

その案内状をどの企業へ配布したか、また誰が招待したかが分かるように作成した管理表があります。

配布した案内状が少なければ、案内状の番号を目で追って探せますが、管理表が膨大になると探すのも一苦労です。

こういう場合、VLOOKUP関数が役に立ちます。

VLOOKUP関数 検索方法TRUE

検索値のセルへ案内状の番号を入力するだけで、どの企業へ配布した案内状で、かつ誰が配布したのかが分かるようにできます。

その方法を紹介します。

案内状管理台帳

管理表の確認

管理表は下のように作成します。

注意一番左の列が検索対象になります。必ず昇順に並べておく必要があります。

項目行もデータとして認識されてしまうような場合は、データが入力されている行と異なる書式設定をしてみてください。

データと区別されて、並べ替えの対象にはならないと思います。(例えば、文字サイズ変更、太字設定、セルの色を付けるなど)

また、参照先となる表は、テーブルに変換しておくと、データに増減があっても自動的に参照範囲が修正されて便利です。

管理台帳

VLOOKUP関数

セル[G3]とセル[H3]にVLOOKUP関数を使って入力します。

セル[F3]には、数値(案内状の番号)を入力しておいてください。

セル[C2]をアクティブにして、[数式]タブの[検索/行列]をクリックして、[VLOOKUP]をクリックします。

ポップヒントには、以下のように記されています。

VLOOKUP(検索値,範囲,列番号,検索方法)

指定された範囲の1列目で特定の値を検索し、指定した列と同じ行にある値を返します。テーブルは昇順で並べ替えておく必要があります。

[数式]タブの[検索/行列]

「関数の引数」ダイアログボックスが表示されます。

VLOOKUP関数

  • 検索値には、セル[F3]、絶対参照にしたいので、セル[F3]を指定した後、[F4]キーを押します。
  • 範囲は、[A列からD列]までを指定します。ここにも[F4]キーを押して、絶対参照を付けます。セル範囲を指定しても構いません。
  • 列番号は、範囲内の左から3列目の値を表示したいので、[3]と入力します。
  • 検索方法は、[TRUE]と入力します。あるいは、[1](0以外)でもいいですし、空白のままでもOKです。

参考絶対参照については、以下の記事で解説しています。

相対参照と絶対参照と複合参照は[ F4 ]キーで切替

セルの参照方法は、相対参照、絶対参照、複合参照があります。 絶対参照と複合参照では、[$]を挿入する必要があります。 このとき、直接入力して ...

続きを見る

関数の引数ダイアログボックスの検索方法には、以下のように記されています。

検索方法には検索値と完全に一致する値だけを検索するか、その近似値を含めて検索するかを、論理値(近似値を含めて検索 = TRUE または省略、完全一致の値を検索 = FALSE)で指定します。

VLOOKUP関数の引数

[OK]ボタンをクリックして、関数の引数ダイアログボックスを閉じます。

セル[G3]には、配布先会社の名前が表示され、数式バーには以下のように入力されていると思います。

参考VLOOKUP関数で引数に[TRUE]を指定すると、一致するデータがない場合、参照範囲の中の検索値は越えず、その中の一番近い値を返します。

検索方法は、何も指定しない場合は[TRUE]を指定したことになります。

関数の完成

IFERROR関数でエラー回避

このままでは、セル[F3]が空白の場合は、エラーが表示されてしまいます。

エラー表示

これを回避するには、IFERROR関数を使用します。

注意IFERROR関数は、Excel2007以降であれば使用できます。

IF関数でもエラーを回避することができます。IF関数であれば、Excel2003でも使用できます。

IFERROR関数(値,エラーの場合の値)

式がエラーの場合は、エラーの場合の値を返します。エラーでない場合は、式の値自体を返します。

VLOOKUPを入力したセル[G3]をクリックします。

数式バーの[=]の後ろに[i]を入力すると、iで始まる関数の補助ポップが表示されます。

その中から[IFERROR]を選択します。

選択するには、マウスでクリック、もしくは方向キー[↓]を使用します。

そして、[Tab]キーを押します。

数式バーで修正

すると、数式バーは以下のようになります。

IFERROR関数の入力

数式バーのお尻でクリックして、[,"番号未入力"]と入力します。

[Enter]キーで確定します。

これで、セル[F3]の検索値が空白の場合は、[番号未入力]と表示されるようになります。

参考検索値が空白の場合は空白にしたいという場合は、以下のように入力します。

=IFERROR(VLOOKUP($F$3,$A:$D,3,TRUE),"")

IFERROR関数の完成

数式をコピーして列番号を修正(配布担当者)

あとは、セル[H3]の配布担当者ですね。

セル[G3]のフィルハンドルをドラッグして数式をコピーします。

数式のコピー

セル[H3]に数式がコピーされます。

数式のコピー後

セル[H3]に表示したいのは、範囲内の4列目の値なので、VLOOKUP関数の列番号を[4]に変更します。

セル[F3]の番号が空白のままでは、変化がわからないので、セル[F3]の[検索値]に数字を入力してみてください。

列番号の修正

IF関数でエラー回避

もし、検索値のセル[F3]が空白の場合は、空白にしたいのであれば以下のような数式でもOKです。

参考IF関数は、Excel2003でもOKです。

=IF($F$3="","",VLOOKUP($F$3,$A:$D,3,TRUE))

検索値のセル[F3]が空白の場合は[番号未入力]と表示したい場合は、以下のように入力します。

=IF($F$3="","番号未入力",VLOOKUP($F$3,$A:$D,3,TRUE))

IF関数でエラー回避

関連 VLOOKUP関数(TRUE)は、税率の計算などでもよく使用されています。

税率の計算

VLOOKUP関数の完全一致の場合の解説もしています。

VLOOKUP関数(FALSE)

VLOOKUP関数-完全一致

以下は、IFERROR関数に関する記事です。

VLOOKUP関数のエラー回避はIFERROR関数で

Excelでデータを参照する場合、VLOOKUP関数を使います。 下の左の表、セル[C2]からセル[C7]には、VLOOKUP関数が入力され ...

続きを見る

エラーの場合の値を指定できるIFERROR関数

Excel2007からの関数ですが、エラー値を表示せずにエラーの場合の値を指定することができる関数があります。 下の表は、E列で[販売単価] ...

続きを見る

IFERROR関数

Excel2021とMicrosoft 365のExcelでは、[XLOOKUP関数]を使用できます。ここで使用した同じサンプル表を使って解説しています。

とても便利な関数です。

データ検索ならXLOOKUP関数で縦横に検索(IFERROR関数は不要)

Excel2021とMicrosoft 365 のExcelでは、[XLOOKUP(エックスルックアップ)関数]が使用できます。 これは、V ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

created by Rinker
★★★★★ 分かりやすい!Excelの基本はこれでOK♪
Amazonのレビューを見る

Profile

執筆者/はま

-Windows 10 & Office 2016 Excel 2016

Copyright © 2001-2024 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2024 AllRights Reserved Powered by AFFINGER4.