よねさんのWordとExcelの小部屋Excel(エクセル)基本講座の総目次Excel(エクセル)関数一覧表|表引き関数(検索・行列の関数・VLOOKUP関数)
よねさんのWordとExcelの小部屋

Excel(エクセル)基本講座:表引き関数(検索・行列の関数・VLOOKUP関数)

スポンサードリンク


列方向の検索(VLOOKUP関数) 行方向の検索(HLOOKUP関数)
昇順データの検索(LOOKUP関数) 何番目に位置するかを検索(MATCH関数)
行位置と列位置を指定して検索(INDEX関数) セル,セル範囲名を計算式で参照(INDIRECT関数)
指定された行/列数だけシフトする(OFFSET関数) セルの参照を文字列で返す(ADDRESS関数)
VLOOKUP関数とMATCH関数の複合 値のリストから指定した値を取出す(CHOOSE関数)
リンクの設定(HYPERLINK関数) 行番号・列番号を返す(ROW関数・COLUMN関数)
セルのデータ型を調べる(TYPE関数)

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

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

【解答例1】
  B C D E F G H I J
2 見積書 商品一覧表
3 品番 商品名 単価 数量 小計 品番 商品名 単価
4 B01 =VLOOKUP(B4,$H$4:$J$6,2,FALSE) =VLOOKUP(B4,$H$4:$J$6,3,FALSE) 1  5,000 A01 テレビ 50,000
5 C01 =VLOOKUP(B5,$H$4:$J$6,2,FALSE) =VLOOKUP(B5,$H$4:$J$6,3,FALSE) 2  40,000 B01 ラジオ 5,000
6 A01 =VLOOKUP(B6,$H$4:$J$6,2,FALSE) =VLOOKUP(B6,$H$4:$J$6,3,FALSE) 3 150,000 C01 ビデオデッキ 20,000
7 合計 195,000

エラー処理    topへ


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


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

  1. セル範囲 H4:J6 に『商品一覧表』と名前を定義します。
    ポイント:検索列が一番左になるようにセル範囲を選択して、名前を定義します。)
    (A) メニューバーからの方法 (B) 名前ボックスを使う方法
    1. セル範囲 H4:J6 をドラッグして選択します。
    2. メニューバーの【挿入】→【名前】→【定義】を選択します。
    3. 【名前の定義】ダイアログの名前に『商品一覧表』と入力し、【OK】ボタンをクリックします。
    4. これで、セル範囲に名前が定義されます。
    vlookup11
    1. セル範囲 H4:J6 をドラッグして選択します。
    2. 【名前ボックス】に『商品一覧表』と入力し、 【Enter】 キーを押します。
    3. これで、セル範囲に名前が定義されます。
    vlookup12
  2. C4セルの数式は =VLOOKUP(B4,商品一覧表,2,FALSE)  D4セルの数式は =VLOOKUP(B4,商品一覧表,3,FALSE) とします。
  3. C4:D4セルを選択し、オートフィルでC6:D6まで数式をコピーして完成です。
  B C D E F G H I J
2 見積書 商品一覧表
3 品番 商品名 単価 数量 小計 品番 商品名 単価
4 B01 =VLOOKUP(B4,商品一覧表,2,FALSE) =VLOOKUP(B4,商品一覧表,3,FALSE) 1  5,000 A01 テレビ 50,000
5 C01 =VLOOKUP(B5,商品一覧表,2,FALSE) =VLOOKUP(B5,商品一覧表,3,FALSE) 2  40,000 B01 ラジオ 5,000
6 A01 =VLOOKUP(B6,商品一覧表,2,FALSE) =VLOOKUP(B6,商品一覧表,3,FALSE) 3 150,000 C01 ビデオデッキ 20,000
7  合計 195,000
 (上の表はExcelへコピーすると #NAME? エラーとなります。 セル範囲 H4:J6 に『商品一覧表』と名前を定義するとエラーが解消されます。)

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

【問題2】成績の評価を付けたいと思います。右の評価一覧表(G4:J6)にしたがって、D4:E6セルに数式を入力しなさい。

【解答例2-1】 


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

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

[D] 検索値が見つからず #N/Aエラー が返される場合   topへ

  1. 検索値にスペースが紛れ込んでいないか確認してみます。特に最後にスペースが含まれる場合は気づきにくいです。
  2. 他のソフトなどで作成したデータで空白文字CHAR(160)が紛れ込んでくることがあります。
  3. 表示形式は文字列であるが、データは数値と認識されていることがあります。
    この様な場合は、=TYPE(A1)のようにTYPE関数を使ってセルのデータ形式を確認します。

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

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

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

【問題】

【解答例1】

【解答例2】検索範囲に名前を定義して使う方法

LOOKUP              topへ
 ルックアップ
=LOOKUP(検査値,検査範囲,対応範囲)  [ベクトル形式]
   対応範囲:1行/1列のみのセル範囲を指定。検査範囲と同じサイズであること。
  
(注)検査範囲の値は昇順に並べておく必要があります。英字の大文字と小文字は区別されません。
    また、日本語文字を並べ替える場合「ふりがなを使用しない」で並べ替えます。

=LOOKUP(検査値,配列) [配列形式]

【問題】成績の評価を付けたいと思います。右の評価一覧表(G4:J6)にしたがって、D4:E6セルに数式を入力しなさい。

【解答例】

【別解】配列形式を使うと、D4セルは以下のように書くこともできます。

(参考) LOOKUP関数のその他の用途(文字列から数値を取り出す方法など)

範囲内で値を検索し、見つかった値が何番目に位置するかを数値で返す。   topへ
 マッチ
=MATCH(検索値,範囲 [,照合の型])
   照合の型:「0」完全に一致する値を検索する
        「1」一致するものがない場合、検索値以下の最大の値を検索する。(データは
昇順に並んでいることが必要)
        「-1」一致するものがない場合、検索値よりも大きい最小の値を検索する。(データは
降順に並んでいることが必要)

[A] MATCH関数で完全に一致する値を検索する

【問題】

【解答例】

(別解) VLOOKUP関数やINDEX関数とMATCH関数を組み合わせて求める方法

MATCH関数で検索値以下の最大値を検索する。

MATCH関数で検索値よりも大きい最小の値を検索する。

範囲の中から、行位置と列位置を指定して値を取り出す  topへ
 インデックス
=INDEX(範囲,行位置,列位置 [,領域番号])
    領域番号は、離れた場所にある複数の範囲を指定する時に使う。

【問題1】INDEX関数とMATCH関数を使って運賃を求めなさい。

【解答1例】

【問題2】大人運賃表と子供運賃表を使って運賃を求めなさい

【解答2例】

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

【問題】VLOOKUP関数とMATCH関数を使って運賃を求めなさい。

【解答例】

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

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

【料金計算の例】

  1. 大人の料金表のE4:H7の範囲に【大人】と名前をつけます。
  2. 大人の料金表の到着地行E3:H3に【大人到着地】と名前をつけます。
  3. 子供の料金表のE11:H14の範囲に【子供】と名前をつけます。
  4. 子供の料金表のタイトル行E10:H10に【子供到着地】と名前をつけます。
  5. C5に数式 =VLOOKUP(C2,INDIRECT(B4),MATCH(C3,INDIRECT(B4&"到着地"),0),FALSE)*C4 を、入力します。

【説明】

(ステップアップ)入力規則を使って入力値を制限する

vlookup10

OFFSET      topへ
 オフセット
=OFFSET(基準,行数,列数 [,高さ,幅]
     基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセル
     またはセル範囲の参照 (オフセット参照) を返します。

【問題】OFFSET関数とMATCH関数を使って運賃を求めなさい。

【解答例】

ADDRESS      topへ
 アドレス
=ADDRESS(行番号,列番号 [,参照の型,参照形式,シート名])
     行番号と列番号で指定したセルの参照を文字列で返します。
     行番号: セル参照に使用する行番号を指定します。
     列番号: セル参照に使用する列番号を指定します。
     参照の型: セル参照の種類を指定します。
      参照の型 結果として返される参照形式
       1 または省略 :絶対参照
       2      :行は絶対参照、列は相対参照
       3      :行は相対参照、列は絶対参照
       4      :相対参照

【問題】ADDRESS関数を使って運賃を求めなさい。

【解答例】

値のリストから指定した値を取出す      topへ
 チューズ
=CHOOSE(インデックス,値1,値2,値3・・・)
インデックスに指定した番号に基づいて、最大 29 個の値の中から 1 つの値だけを選択できます。

【例】

ファイルや別シートのセルへリンクを張る      topへ
 ハイパーリンク
=HYPERLINK(リンク先,別名)
   リンク先:文字列を二重引用符で囲んで指定するか、またはリンクが設定されているセルを文字列として指定する

【例】

ハイパーリンクの挿入を使う方法

【操作手順】同一ファイルの別シートへのリンク方法を例に説明します。

  1. メニューバーの【挿入】→【ハイパーリンク】を選択します。
  2. リンク先:このドキュメント内
    ドキュメントの場所:Sheet2 を選択
    セル参照を入力してください:B2 と入力
    表示文字列:シート2のB2へリンク としました。(何でも良い)
  3. 数式バーには表示文字列に入力した文字が表示されます。

ハイパーリンクの削除

行番号を返す      topへ
 ロウ
=ROW(範囲)  引数として指定された範囲の行番号を返します。
 ロウズ
=ROWS(配列) セル範囲または配列の行数を返します。

列番号を返す      topへ
 コラム
=COLUMN(範囲)  引数として指定された範囲の列番号を返します。
 コラムズ
=COLUMNS(配列) セル範囲または配列の列数を返します。

スポンサードリンク


よねさんのWordとExcelの小部屋Excel(エクセル)基本講座の総目次Excel(エクセル)関数一覧表|表引き関数(検索・行列の関数・VLOOKUP関数)

PageViewCounter
Counter
Since2006/2/27