Lesson 7-1

関数

検索関数

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

はじめに、このファイルから必要なシートを Lesson 5 で用いた book にコピーしなさい。 (ない場合は L7.xlsx に含まれる成績データのシートを用いよ。)

Lesson 5-2 で用いた成績表について、シート L7-1 に示す表に基づいて、 各学生の成績の評価欄の右にコメントを表示せよ。

セルの検索(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(検査値, 検査範囲, 照合の種類)

     検査値が検査範囲の先頭から何番目にあるかを返す。 次の表を「検査範囲」として、検査値に「平均気温」を指定し照合型を 0(厳密一致)を指定すると「2」が返る。

     日付

     平均気温

     平均湿度

     照合の種類として指定できる 1, 0, -1 の意味は以下の通り。

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

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

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

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

練習 1

以下のような英日対応表がある。 この表を用いて、指定のセルに英単語を入れると対応する日本語を表示する 簡易英和辞書を作成せよ。(VLOOKUP を使用する。)

練習2

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

練習３

Lesson 5-3で用いた国勢調査の表を元に 都道府県番号(文字列であることに注意すること)を入力すると、 対応する「都道府県名」、「平成17年の人口」、「人口増減率」、「面積」、「人口密度」を 表示するような表を作成せよ。

元データを次の順で並び替え、結果がどうなるか確認せよ。

1. 都道府県番号の昇順でソートされている場合
2. 人口密度の降順でソートされている場合
3. 都道府県番号の降順でソートされている場合

また、番号欄に 48 を入力し、上記の3つの場合でどうなるかを確認せよ。

発展練習 1

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

発展練習 2

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

なお、入力されたデータをチェックしてエラーとする戦略と対称的な手法として、 入力できるデータをあらかじめ制限するという方法も考えられる。 そのためには、「データの入力規則」を用いる。