8日目 10日目

9日目

目標

• エクセルのデータベース機能を理解する (1:検索)

課題

• 課題20検索

課題 20

検索

練習1

練習2

セルの検索(LOOKUP 関数群)

ある範囲から、特定の条件に一致するようなセルを探しだし、 そのセルの値や位置を求めることができる。 例として、ここでは商品番号をもとに、商品名や単価を検索することを考えている。 この目的に用いる関数は「検索/行列関数」に分類されている。 検索範囲や検索方法によって次のような関数や方法を利用することになる。

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

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

  

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

  • 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)

表1をコピーしたあと、対象年齢と価格の間に1列挿入し、各年齢層の「最小値」を 記入する。 「最小値」を用意するのは、あらゆる年齢が表1に含まれているわけではないのに、 任意の年齢に対して価格を決める必要があるため、「近似」探索を行うからである。 (近似探索とは、「検索値未満の最大値」の探索であったことに注意)
「境界年齢」を「価格名」よりも右に設定したため、VLOOKUP 関数は利用できないので、 関数 LOOKUP を利用する。 引数の選択では、「検査値、検査範囲、対応範囲」を選択する。
「価格名」をまず設定する。検査値は「年齢」を 検査範囲には表1の「境界年齢」を、対応範囲には「価格名」を指定する。 あとで、コピーする場合は、セル参照の方法を良く考えること。 うまく、参照方法を指定すると、コピーして、少し修正することで対応できる。
年齢が入力されていないと、価格名が「乳児」となるのはみっともないので、 IF 関数を用いて、年齢欄が空白ならば価格名も空白となるように、修正せよ。 ヒント: 空白かどうかは「セル番地 = ""」で判別できる

解答例(練習2)

商品番号を元に検索する場合、「近似」探索では納品書として役にたたない。 したがって、VLOOKUP 関数を利用する事にする。

注意する点は次の通り

1. 「範囲」を指定する際は検索する領域が「左端」になるようにすること
2. 「列番号」を指定する際は、領域の左から何列目かを数字で指定する
3. 「検索の型」には厳密一致するものを探索するので 0 を指定する。

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

小ネタ

値が 0のセルを隠す

上の納品書では、式の結果が 0 になる部分が表示されていない。 このようにするためには、「ツール」→「オプション」を選択し、 「表示」タブを選んだなかにある、「ウィンドウオプション」中の「ゼロ値」のチェックを外せば良い。