← ↑ →
関数
検索関数
ある範囲から、特定の条件に一致するようなセルを探しだし、
そのセルの値や位置を求めることができる。
このような検索を行うための関数の使い方を学ぶ
シート L7-3 に示す表に基づいて、各学生の成績の評価欄の右にコメントを表示せよ。
| 検索語 |
引用語 |
| A |
たいへんよくできました |
| B |
よくできました |
| C |
できています |
| D |
がんばりましょう |
ある範囲から、特定の条件に一致するようなセルを探しだし、
そのセルの値や位置を求めることができる。
例として、ここでは成績符合を元に言葉としての評価を求めている。
この目的に用いる関数は「検索/行列関数」に分類されている。
検索範囲や検索方法によって次のような関数や方法を利用することになる。
- 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関数の組み合わせ
- MATCH(検査値, 検査範囲, 照合の種類)
検査値が検査範囲の先頭から何番目にあるかを返す。
次の表を「検査範囲」として、検査値に「平均気温」を指定し照合型を 0(厳密一致)を指定すると「2」が返る。
照合の種類として指定できる 1, 0, -1 の意味は以下の通り。
| 1:
| 検査値以下の最大の値の検索(昇順に並べ替えておく必要がある)
|
| 0:
| 厳密一致(並べ替えの必要はない)
|
| -1:
| 検査値以上の最小の値の検索(降順に並べ替えておく必要がある)
|
- INDEX(配列, 行番号, 列番号)
与えられた配列から、指定された行番号、列番号の要素を返す。
縦横座標の順序が数学での順番と逆である事に注意する。
検索に利用できる関数の特徴をまとめると次のようになる。
| 関数名 |
検索方向 |
検索範囲に対する
表示するデータ位置 |
検索型 |
| 厳密 |
近似 |
| VLOOKUP |
縦 |
右 |
○ |
○ |
| HLOOKUP |
横 |
下 |
○ |
○ |
| LOOKUP |
縦 |
左右 |
× |
○ |
| 横 |
上下 |
| INDEX + MATCH |
- |
- |
○ |
○ |
練習 1
以下のような英日対応表がある。
この表を用いて簡易英和辞書を作成せよ。(VLOOKUP を使用する。)
| apple | りんご |
| angle | 角度 |
| bill | 請求書 |
| brother | 兄弟 |
| chalk | チョーク |
| excel | より勝る |
| fish | 魚 |
| Look Up | 調べる |
| mother | 母 |
| Paper | 紙 |
練習2
練習 1 で用いた、英日対応表を行列を入れ替えてコピーし(形式を選択して貼り付け→行列を入れ替える)、
HLOOKUP を用いて同じ機能を実現せよ。
発展練習 1
|
ある食べ放題レストランでは、年齢に基づいた価格設定を行っている。
各年齢に対する価格は表1の通りである。
それぞれの客が年齢を記入するだけで、価格名と価格を表示し、合計額を
表示できるような伝票を関数 LOOKUP を用いて作成せよ。
ただし、1グループの最大人数は5人とする。
|
表1: 年齢別価格表
| 価格名 |
対象年齢 |
価格 |
| 乳児 |
1歳未満 |
0 |
| 幼児 |
6歳未満 |
500 |
| 小学生 |
12歳未満 |
1,000 |
| 中学生 |
15歳未満 |
1,200 |
| 高校生 |
18歳未満 |
1,500 |
| 一般 |
18歳以上 60歳未満 |
2,000 |
| シルバー |
60歳以上 |
1,500 |
|
余裕があれば、年齢欄が空白ならば価格名も空白となるように IF関数を用いて修正せよ。
空白かどうかは「セル番地 = ""」で判別できる。
発展練習 2
|
次の条件を満たすような、納品書を作成せよ。
-
表2に示す商品番号と、発注単位数を入力するだけで、商品名、単価、価格が表示されること
-
最大6品目まで記入できること
-
「合計」のうち消費税相当額(5％)を「内消費税」として表示できること
|
表2: 商品一覧
| 商品番号 |
品名 |
量 |
価格(内税) |
| 040 |
キャベツ |
1玉 |
198 |
| 044 |
レタス |
1玉 |
189 |
| 048 |
なす |
5本 |
170 |
| 079 |
スイートコーン |
1本 |
131 |
| 081 |
キュウリ |
3本 |
141 |
| 082 |
ニンジン |
500g |
150 |
| 084 |
玉ねぎ |
500g |
131 |
|
存在しない商品番号が入力された場合、#N/A と表示されてしまうが、
これを 背景色を赤で「入力エラー」と表示するように設定せよ。
なお、#N/A かどうかは、関数 ISNA を用いて、「ISNA(セル番地)」で判断できる。
← ↑ →
$Id: L7-3.html,v 1.2 2008/12/15 19:47:54 tacha Exp $
ogaito at fpu.ac.jp
