よねさんのWordとExcelの小部屋Excel(エクセル)基本講座の総目次Excel(エクセル)関数一覧表|VLOOKUP関数の使い方
よねさんのWordとExcelの小部屋

Excel(エクセル)基本講座VLOOKUP関数の使い方


スポンサードリンク


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

はじめに

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

[A] 検索値が文字列の場合 (検索の型:FALSE)
    (注)検索値が文字列の場合で昇順に並んでいる時は検索の型を[TRUE]でOKです。
      ただし、昇順の並べ替えで基準は[ふりがな]を使用してはいけません。日本語の場合には要注意です。

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


(Step Up)共通の数式に変更する

検索範囲に名前を定義する場合    topへ

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

[B] 検索値が数値範囲の場合 (検索の型:TRUE)     topへ
    (注)検索値が昇順に並んでいる必要があります。
      検索値が数値の範囲ではなく、その値に対応する時は[FALSE]とします。

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

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

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

[E] 検索条件が2列(複数)ある場合   topへ

  1. 左端列に検索条件を1つにまとめます(作業列を利用します)。
  2. SUMPRODUCT関数を使う事もできます。この場合B列は不要です。

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

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

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

スポンサードリンク


よねさんのWordとExcelの小部屋Excel(エクセル)基本講座の総目次Excel(エクセル)関数一覧表|VLOOKUP関数の使い方

PageViewCounter
Counter
Since2006/2/27