Windows10 & Office2016 Excel2016

Print Friendly, PDF & Email

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

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

通し番号を印刷した展示会の案内状があります。その案内状をどの企業へ配布したか、また誰が招待したかが分かるように作成した管理表があります。
配布した案内状が少なければ、案内状の番号を目で追って探せますが、管理表が膨大になると探すのも一苦労です。こういう場合、VLOOKUP関数が役に立ちます。検索値のセルへ案内状の番号を入力するだけで、どの企業へ配布した案内状で、かつ誰が配布したのかが分かるようにすることができます。その方法を紹介します。
案内状管理台帳

管理表の確認

管理表は下のように作成します。注意一番左の列が検索対象になります。必ず昇順に並べておく必要があります。
管理台帳

VLOOKUP関数

[セルG3]と[セルH3]にVLOOKUP関数を使って入力します。[セルF3]には、数値(案内状の番号)を入力しておいてください。

[セルC2]をアクティブにして、[数式]タブの[検索/行列]をクリックして、[VLOOKUP]をクリックします。ポップヒントには、以下のように記されています。

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

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

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

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

参考絶対参照については、以下のMicrosoftの記事が参考になると思います。
相対参照、絶対参照、複合参照を切り替える - Excel

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

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

VLOOKUP関数の引数

[OK]ボタンをクリックして、関数の引数ダイアログボックスを閉じます。[セルG3]には、配布先会社の名前が表示され、数式バーには以下のように入力されていると思います。
参考VLOOKUP関数で引数に[TRUE]を指定すると、一致するデータがない場合、参照範囲の中の検索値は越えず、その中の一番近い値を返します。検索方法は、何も指定しない場合は[TRUE]を指定したことになります。
関数の完成

IFERROR関数

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

これを回避するには、IFERROR関数を使用します。注意IFERROR関数は、Excel2007以降であれば使用することができます。

IFERROR関数(値,エラーの場合の値)
式がエラーの場合は、エラーの場合の値を返します。エラーでない場合は、式の値自体を返します。

VLOOKUPを入力したセルG3セルをクリックします。数式バーの[=]の後ろに[i]を入力すると、iで始まる関数の補助ポップが表示されます。その中から[IFERROR]を選択します。選択するには、マウスでクリック、もしくは方向キー[↓]を使用します。そして、[Tab]キーを押します。

数式バーで修正

すると、数式バーは下のようになります。
IFERROR関数の入力

数式バーのお尻でクリックして、[,"番号未入力"]と入力します。[Enter]キーで確定します。これで、[セルF3]の検索値が空白の場合は、[番号未入力]と表示されるようになります。
IFERROR関数の完成

数式をコピーして列番号を修正

あとは、[セルH3]の配布担当者ですね。
[セルG3]のフィルハンドルをドラッグして数式をコピーします。
数式のコピー

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

[セルH3]に表示したいのは、範囲内の4列目の値なので、VLOOKUP関数の列番号を[4]に変更します。[セルF3]の番号が空白のままでは、変化がわからないので、[セルF3]の[検索値]に数字を入力してみてください。
列番号の修正

検索値が空白の場合は空白にしたい

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

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

IF関数

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

税率の計算

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

VLOOKUP関数 完全一致

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

VLOOKUP関数のエラー回避

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

続きを見る

エラーの場合の値を指定する

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

続きを見る

IFERROR関数

解決できないことは【teratail】に質問してみましょう。

参考Officeのバージョンによって操作が異なる場合があります。以下の記事で、お使いのOfficeのバージョンを確認する方法を解説しています。

Office(Word、Excel、PowerPointなど)のバージョンを確認する方法

Microsoft Officeも時を得て、大きく様変わりしてきました。トラブルを解決しようとするとき、あるいは、何かの操作を探すときなど、 ...

続きを見る

あなたへのおすすめ

関連記事

予定表からすべての予定を削除するには

Outlookの予定表のスケジュールを1つずつ削除するには、削除したいアイテムで右クリックして、ショートカットメニューから[削除]をクリック ...

名前を定義して関数で使用する

Excelでは、セル範囲に名前を定義しておくと、関数の引数に名前を利用することができます。定義された名前は、[数式]タブの[数式で使用]ボタ ...

図形に画像を挿入するには

図形を挿入して、その図形に画像を挿入する方法です。Word2016で解説しますが、Office2007以降のバージョンや他のアプリケーション ...

ピボットグラフに展開/折りたたみボタン

Excel2016では、ピボットグラフに[フィールド全体の展開ボタン]と[フィールド全体の折りたたみボタン]が追加されました。 フィールド全 ...

ウィザードを使ったコマンドボタンの作成

Accessで起動時にメニューフォームを表示した後、ボタンをクリックして目的のフォームを開くようにしたいという要望はよくあります。コマンドボ ...

-Windows10 & Office2016 Excel2016

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

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