リンクユニット

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関数

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

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

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

続きを見る

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

おすすめ記事と広告

こんな記事もあります

データベースのプロパティから個人情報を削除

Accessのプロパティには、作成者、管理者、会社名などの個人情報を入力して登録しておくことができます。[プロパティ]は一般に[属性]と呼ば ...

パスワードを入力してフォームを開くようにする

Accessでパスワードを入力して、パスワードが合えばフォームを開くようにする方法です。 注意ここでは、Accessのオプションでウィンドウ ...

透かしの挿入

Wordで下のような透かし文字や透かし画像を挿入する方法です。 [デザイン]タブの[透かし] 透かしを挿入したい文書を開いて、[デザイン]タ ...

表を使った名簿の作成は差し込み印刷の[名簿]で

Wordの差し込み印刷の中に[名簿]という機能があります。レイアウトした表の中にフィールドを差し込むと名簿を一気に作成することができます。 ...

Accessのテーブルをインポート

Accessで作成したデータベースが複数あり、1つのデータベースに他のデータベースのテーブルをインポートする方法です。 Aのデータベースに、 ...

-Windows10 & Office2016 Excel2016

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

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