Lesson 7-1

# Lesson 7-1

## 関数

### 検索関数

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

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

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

検索語 引用語
A たいへんよくできました
B よくできました
C できています
D がんばりましょう

### セルの検索(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関数の組み合わせ

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 を用いて同じ機能を実現せよ。

### 練習３

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

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

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

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

### 発展練習 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-1.html,v 1.4 2011/01/10 15:48:49 tacha Exp tacha $
ogaito at fpu.ac.jp