リンクユニット

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です。

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

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

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

続きを見る

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

検索方法には検索値と完全に一致する値だけを検索するか、その近似値を含めて検索するかを、論理値(近似値を含めて検索 = 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も時を得て、大きく様変わりしてきました。トラブルを解決しようとするとき、あるいは、何かの操作を探すときなど、 ...

続きを見る

エクセルを学習するなら、お勧めのテキストです。ぜひお手元においてご活用ください。

おすすめ記事と広告

こんな記事も読まれています

受信メールの一部分を印刷したい時はメッセージの編集が便利

受信したメールの一部分を印刷したい時ってありますね。たとえば、レストランや列車の予約をしたら、お知らせのメールが届きます。その受信メールの中 ...

Windows10 Creators Update後に画面中央に[あ][A]が表示されるのを無効にする

Windows10 Creators Updateを実行すると、既定では、IMEの入力モードを切り替える度に画面中央に[あ]または[A]が大 ...

複数フィールドの並べ替えの設定

Accessでは、クエリを利用して指定したフィールドでレコードを昇順や降順に並べ替えることができます。1つのフィールドの並べ替えであれば、デ ...

テーブルやピボットテーブルに変換する時の注意点

Excelのテーブルやピボットテーブルは、データベースとしての管理が簡単にできるようになる、とても便利な機能です。でも、既存の表をテーブルや ...

レポートウィザードでレポートを作成

Accessのテーブルやクエリを基にレポートウィザードを使用してレポートを作成することができます。ウィザードで作成した後、より見やすいレポー ...

-Windows10 & Office2016 Excel2016

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

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