Lesson 6-3

関数

検索関数

ある範囲から、特定の条件に一致するようなセルを探しだし、 そのセルの値や位置を求めることができる。 このような検索を行うための関数の使い方を学ぶ

シート L6-3 に示す表に基づいて、各学生の成績の評価欄の右にコメントを表示せよ。

セルの検索(LOOKUP 関数群)

ある範囲から、特定の条件に一致するようなセルを探しだし、 そのセルの値や位置を求めることができる。 例として、ここでは成績符合を元に言葉としての評価を求めている。 この目的に用いる関数は「検索/行列関数」に分類されている。 検索範囲や検索方法によって次のような関数や方法を利用することになる。

• VLOOKUP(検査値, 範囲, 列番号, 検索型)

  ある領域中(範囲)の『左端列』中で、検索する値(検査値)を探索し、 見つかった行中の特定の列(列番号)の値を返す。 たとえば、練習問題2の表2全体を範囲に指定し「商品番号」から 「品名」を得る場合には列番号として「2」を、 「価格」を得る場合には列番号として「4」を指定する。

  
  検索する列が左端となり、表示する列を含むように範囲を選択する

  検索型は論理値であり、検査値と 一致するものが見つからなかった場合の振る舞いを指定する。 省略された場合は TRUE と見なされる。

  • TRUE(1) :「近似値」を用いた探索結果を返す。
  • FALSE(0):「#N/A」(Not Available)エラーを返す。

  近似値とは「検索値未満の最大値」である。 このため近似値を用いた探索を行う場合には、検索する領域の左端列が 昇順である必要があるので、あらかじめ整列しておく。 (厳密一致で探索を行う場合は整列しておく必要はない。)

  英字の大文字小文字は区別されないので注意する。

  整列していなくても正しい結果が得られる場合もあるようであるが、 エクセル自身のヘルプでも整列するようにという指示が書かれているので、整列しておくべきと考える。

• HLOOKUP(検査値, 範囲, 行番号, 検索型)

  VLOOKUPが垂直方向(vertical)に検索を行ったのに対して、 HLOOKUPは水平方向(horizontal)に検索を行う。

• LOOKUP(検査値,検査範囲,対応範囲)
  

  検査範囲から検査値と一致するようなセルを探しだし、 対応範囲の同じ行(列)にある値を抽出する。 引数として『検査値,検査範囲,対応範囲』、『検査値,配列』を選択する画面では 前者を選択する。

  VLOOKUP(HLOOKUP)では、 検索する範囲の右(下)側に存在するものしか表示できなかったが LOOKUPでは、検査範囲、 対応範囲を別個に指定できるため左(上)側にあるものを指定することも可能である。

  しかしながら、VLOOKUP(HLOOKUP)では厳密探索、近似探索が選択できたが LOOKUPでは近似探索のみが可能である。 したがって、検査範囲はあらかじめ昇順で整列されている必要がある。

  
• INDEXおよびMATCH関数の組み合わせ
   
  
  1. MATCH(検査値, 検査範囲, 照合型)

     検査値が検査範囲の先頭から何番目にあるかを返す。 表1で検査値に「価格」を、「先頭1行」を検査範囲として指定し照合型を 0(厳密一致)を指定すると「4」が返る。 照合型として指定できる 1, 0, -1 の意味は以下の通り。

     1:	検査値以下の最大の値の検索(昇順に並べ替えておく必要がある)
     0:	厳密一致(並べ替えの必要はない)
     -1:	検査値以上の最小の値の検索(降順に並べ替えておく必要がある)

      
     
  2. INDEX(配列, 行番号, 列番号)

     与えられた配列から、指定された行番号、列番号の要素を返す。 縦横座標の順序が数学での順番と逆である事に注意する。

• LOOKUP ウィザード

  INDEX関数を簡便に利用するためのインターフェース。 「ツール」→「ウィザード」→「LOOKUP」で起動する。 メニューにない場合は「ツール」→「アドイン」から『LOOKUP ウィザード』を追加すると利用できるようになる。

  ただし、得られる結果は、MATCH関数の検査値がセル参照ではないので、セルを書き直しても変わらない。 また、照合型は「厳密一致」のみである。

検索に利用できる関数の特徴をまとめると次のようになる。

練習 1

以下のような英日対応表がある。 この表を用いて簡易英和辞書を作成せよ。(VLOOKUP を使用する。)

練習2

練習 1 で用いた、英日対応表を行列を入れ替えてコピーし(形式を選択して貼り付け→行列を入れ替える)、 HLOOKUP を用いて同じ機能を実現せよ。

発展練習 1

余裕があれば、年齢欄が空白ならば価格名も空白となるように IF関数を用いて修正せよ。 空白かどうかは「セル番地 = ""」で判別できる。

発展練習 2

存在しない商品番号が入力された場合、#N/A と表示されてしまうが、 これを 背景色を赤で「入力エラー」と表示するように設定せよ。 なお、#N/A かどうかは、関数 ISNA を用いて、「ISNA(セル番地)」で判断できる。