VLOOKUP関数の使い方(データを検索する):Excel関数


スポンサードリンク


更新 2021/11/23

完全一致を検索する(検索の型:FALSE) エラー処理(#N/Aを表示しない)
近似一致を検索する(検索の型:TRUE) 検索範囲に名前を定義する場合
検索範囲にテーブルを利用する方法
検索表を使用しない方法 #N/Aエラー が返されるいろいろなケース
セルのデータ型を調べる VLOOKUP関数で複数条件の検索をする
VLOOKUP関数とMATCH関数の複合 VLOOKUPとMATCHおよびINDIRECTの複合

はじめに:VLOOKUP関数のポイント

 ブイルックアップ
=VLOOKUP(検索値,範囲,列位置,検索の型)
   検索の型:「TRUE」もしくは[省略](「1」と入力してもOK)
        検索値が見つからない場合に、
検索値未満で最も大きい値が使用されます。
        
(注)検索範囲の値は昇順に並んでおく必要があります。
   検索の型:「FALSE」(「0」と入力してもOK)
        
検索値と完全に一致する値だけが検索され、見つからない場合はエラー値 #N/A が返されます。
        文字列の検索に適します。

[A] 完全一致で検索する (検索の型:FALSE)

問題1の解答例

VLOOKUP関数のエラー処理(#N/Aを表示しない)    topへ

IFERROR関数でエラー処理をする(Excel2007以降で使用できます)

IFNA関数でエラー処理をする(Excel2013以降で使用できます)

ISNA関数やISERROR関数でエラー処理をする

COUNTIF関数で処理する

VLOOKUP関数の検索範囲にテーブルを利用する    topへ

VLOOKUP関数の検索範囲に定義した名前を利用する    topへ

  1. 検索範囲に名前を定義して、数式にその名前を使用すると数式が読みやすくなります。
    (数式を見て、すぐにその名前の範囲が参照されているのがわかります。)
    セル範囲 H4:J6 に『商品一覧表』と名前を定義します。
    ポイント:検索列が一番左になるようにセル範囲を選択して、名前を定義します。)
    1. メニューバーからの方法
      1. セル範囲 H4:J6 をドラッグして選択します。
      2. Excel2007以降は、[数式]タブの[名前の定義]を実行します。
        Excel2003以前は、メニューバーの[挿入]→[名前]→[定義]を選択します。
        [名前の定義]コマンド
      3. 「名前の定義」ダイアログの名前に『商品一覧表』と入力し、[OK]ボタンをクリックします。
        これで、セル範囲に名前が定義されます。
        VLOOKUP:新しい名前
    2. 名前ボックスを使う方法
      1. セル範囲 H4:J6 をドラッグして選択します。
      2. 「名前ボックス」に『商品一覧表』と入力し、 [Enter] キーを押します。
        これで、セル範囲に名前が定義されます。
  2. C4セルの数式は =VLOOKUP(B4,商品一覧表,2,FALSE)
    D4セルの数式は =VLOOKUP(B4,商品一覧表,3,FALSE) とします。
  3. C4:D4セルを選択し、オートフィルでC6:D6まで数式をコピーして完成です。
    VLOOKUP関数の説明12

[B] 近似一致を検索する場合 (検索の型:TRUE)     topへ
    (注)検索値が昇順に並んでいる必要があります。近似値(検索値未満の最大値)を範囲の中から探します。
      検索値が数値の範囲ではなく、その値に対応する時は[FALSE]とします。

問題2の解答例-1 

問題2の解答例-2 検索範囲に名前を定義して使う方法

[C] 検索表を使用しない方法  (数式中に検索範囲のデータを書く方法)    topへ

[D] 検索値が見つからず #N/Aエラー が返されるいろいろなケース   topへ

  1. 検索値にスペースが紛れ込んでいないか確認してみます。特に最後にスペースが含まれる場合は気づきにくいです。
    1. [ホーム]タブの[検索と選択]→[置換]を実行します。
      ショートカットキーは[Ctrl]+[H]です。
      VLOOKUP:検索と選択から置換を実行
    2. 検索する文字列:半角または全角スペースを入力します。(画面では見た目の変化はないです)
      置換後の文字列:何も入力しません。
      『半角と全角を区別する』にチェックは入れません。(【オプション】をクリックすると表示されます)
      【すべて検索】ボタンをクリックし、【すべて置換】をクリックすると、一度に置換されます。
      確認しながら置換するときは、【次を検索】と【置換】を使います。
      VLOOKUP:置換の設定方法
  2. 他のソフトなどで作成したデータで空白文字CHAR(160)が紛れ込んでくることがあります。
  3. 表示形式は文字列であるが、データは数値と認識されていることがあります。
    この様な場合は、=TYPE(A1)のようにTYPE関数を使ってセルのデータ形式を確認します。

セルのデータ型を調べる   topへ
 タイプ

=TYPE(データタイプ)
   数値は「1」 、テキストは「2」 、論理値は「4」
   エラー値は「16」 、配列は「64」を返します。

  1. C列にD列のようなそれぞれの値や数式を入力します。
  2. C5,C6はそれぞれの結果「FALSE」「#VALUE!」が表示されます。
  3. C7はTYPE関数の戻り値「64」が表示され、E7は戻り値「64」は数値なので、「1」が返されます。
    B C D E F
    2 データタイプ 入力データ C列の内容 TYPE関数の戻り値 E列の数式
    3 数値 10 ←10 1 ←=TYPE(C3)
    4 テキスト 文字です ←文字です 2 ←=TYPE(C4)
    5 論理値 FALSE ←=A1="moji" 4 ←=TYPE(C5)
    6 エラー #VALUE! ←=B1/B2 16 ←=TYPE(C6)
    7 配列 64 ←=TYPE({1,2;3,4}) 1 ←=TYPE(C7)

[E] VLOOKUP関数で複数条件の検索をする   topへ

左端列に検索条件を1つにまとめます(作業列を利用します)。

SUMPRODUCT関数を使う事もできます。この場合B列は不要です。

Microsoft365をお使いの場合は XLOOKUP関数で対処できます。

VLOOKUP とMATCH の複合   topへ
   縦横の検索表から、一致する値を取り出すのに、VLOOKUP関数とMATCH関数を組み合わせて使う方法です。

解答例

VLOOKUP と MATCH および INDIRECT の複合    topへ
 参照する表が2種類ある場合、切り替えて使いたい時にINDIRECT関数が使えます。
 インダイレクト

=INDIRECT(参照文字列,参照形式)
     参照文字列で入力したセル、セル範囲名を計算式で参照できるようにする。
     参照形式:TRUE または省略した場合、A1形式でのセル参照。
            FALSE R1C1形式でのセル参照。

スポンサードリンク



よねさんのWordとExcelの小部屋 »  エクセル関数一覧表 »  VLOOKUP関数の使い方(データを検索する)

PageViewCounter
Counter
Since2006/2/27