9日目

目標

エクセルのデータベース機能を理解する

課題

課題 19 まずは LOOKUP 関数に挑戦
課題 20 計算と並べ替え
課題 21 フォーム入力、抽出・並べ替え
課題 22 抽出 (応用)

課題 19

まずは LOOKUP関数に挑戦

次のような表を作成し[　？　]に LOOKUP関数をいれよ。なお、表中の空欄部分には乱数発生関数 RAND() を利用して、0～100の間の数値を入れよ。

販売員別売上高(単位千円)
月日	山本	青木	中川	小澤
2003/06/01
2003/06/02
2003/06/03
2003/06/04
2003/06/05
2003/06/06
2003/06/07
2003/06/08
2003/06/09
2003/06/10
2003/06/08	の中川の売り上げは、		？	です。

乱数の発生

関数 RAND()は引数を持たない関数で、{x| 0 ≦ x ≦ 1} な乱数を返す。したがって、例えば a以上、b以下であるような乱数が必要な場合は "=RAND()*(b-a) + a"を用いれば良い。

セルの検索(LOOKUP関数群)

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

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

検索型は論理値であり、検査値と一致するものが見つからなかった場合の振る舞いを指定する。
- TRUE(1) :「近似値」を用いた探索結果を返す。
- FALSE(0):「#N/A」(Not Available)エラーを返す。
近似値を用いた探索を行う場合には、検索する領域の左端列が昇順である必要があるので、あらかじめ整列しておく。(厳密一致で探索を行う場合は整列しておく必要はない。)

英字の大文字小文字は区別されないので注意する。
HLOOKUP(検査値, 範囲, 行番号, 検索型)
VLOOKUPが垂直方向(vertical)に検索を行ったのに対して、 HLOOKUPは水平方向(horizontal)に検索を行う。
(たとえば表2のようなものから検索する場合)
LOOKUP(検査値,検査範囲,対応範囲)

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

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

しかしながら、VLOOKUP(HLOOKUP)では厳密探索、近似探索が選択できたが LOOKUPでは近似探索のみが可能である。したがって、検査範囲はあらかじめ昇順で整列されている必要がある。
LOOKUP(検査値,配列)
関数 LOOKUPのもう一つの呼び出し方法。主に後方互換性もしくは他のアプリケーションとの互換性のために存在する。

検査範囲と対応範囲の両方を含む領域を配列として指定する。指定された領域の左端列(上端行)が検査範囲と、指定された領域の右端列(下端行)が対応範囲とみなされる。

INDEXおよびMATCH関数の組み合わせ

MATCH(検査値, 検査範囲, 照合型)

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

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

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

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

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

表1
商品番号	品名	量	価格
048	ピーマン	150g	98
064	キャベツ	1玉	198
065	キュウリ	3本	148
075	ニンジン	1kg	198
079	オクラ	10本	158

表2
Ａ	Ｂ	Ｃ	Ｄ
優	良	可	不可

表1を用いて納品書を作る例を以下に示す。

適切に表を作成する。本来ならば商品テーブルと納品書は別のシートに作成した方が良いと思われるが、説明のため同一シートに書いてある。
(商品番号欄に値を入れて)引数を設定する。
あとで、コピーした際に場所がずれないように範囲欄は絶対参照とすること。
品名は表位置の左から2列目なので、列番号は「2」である。
単価欄も同様に参照し、価格欄に適切な式を入れる。また、「小計」、「消費税」、「合計」欄にも式を入れる。

LOOKUP関数群で検査値に対応するものがなかった場合に "#N/A" が得られるがこれを「IF関数」を用いて表示しないようにできるので、考えてみよ。

ヒント

空白かどうかは「セル番地 = " "」で判別できる
#N/A かどうかは関数 ISNA を用いて「ISNA(セル番地)」で判断できる。

練習

上記の納品書を完成させ、間違った商品番号が入力された場合には「入力エラー」と表示させるようにせよ。

課題 20

計算と並べ替え

以下はオムニバス方式で6人の教員が採点した30名の成績である。各生徒が獲得した6つの点数について最高点と最低点を除外した 4つの点数の平均を計算し、平均点の高い順に並べ替えよ。

ヒント
最高点や最低点の抽出には関数 LARGE を使え。

	A先生	B先生	C先生	D先生	E先生	F先生
生徒01	75	65	75	85	80	90
生徒02	75	65	85	85	55	80
生徒03	70	85	80	85	85	85
生徒04	80	60	80	85	90	60
生徒05	90	70	75	65	90	60
生徒06	75	65	80	85	75	90
生徒07	80	70	80	85	80	90
生徒08	80	85	85	85	85	60
生徒09	85	85	80	85	95	90
生徒10	85	85	80	85	90	60
生徒11	85	70	85	85	90	60
生徒12	80	85	85	85	90	90
生徒13	75	65	80	65	40	60
生徒14	75	85	85	85	85	60
生徒15	75	85	80	85	60	90
生徒16	70	85	80	85	85	85
生徒17	70	85	85	85	60	86
生徒18	80	85	80	85	65	86
生徒19	75	0	75	0	65	90
生徒20	0	0	75	65	35	90

フォームを用いた入力

大量のデータを入力している場合にはフォームを利用した入力を用いた方が便利な場合がある。

フォームを用いた入力を行う場合には、いずれかのデータセルを選択後、「データ」→「フォーム」を実行する。なお、フォームを利用する場合オートフィルは利用できないので、入力の手間を減らすためにはあらかじめ埋められる範囲の入力はしておいたほうが良い。

フォーム入力ウィンドウの右上にでている数字は「現在編集中のデータ／全データ数」である。

次のデータに移動する際には「次を検索」、前に戻る場合は「前を検索」、最後にデータを追加する際には「新規」を押す。必要ないデータを削除することもできる。フォーム入力を終える場合には「閉じる」を押す。

なお、エクセルがデータ範囲を正しく推測できない場合は、範囲全体を選択した後、フォームを起動する。

最高点、最低点、領域

複数の採点官による得点を評価する際に、最高点および最低点を除いた残りの平均点が用いられることがある。 (例. オリンピックの体操競技等)
エクセルを用いてこのような採点を行うようにというのが、課題20である。このためには、エクセルで用意されている LARGEやSMALL関数を用いる。

LARGEおよびSMALL関数は、与えられた領域中(範囲)から n番目(順位)に大きな(小さな)値を返す。なお、最大値および最小値は MAX関数やMIN関数でも得ることができる。

入力した表の右側に「最高」、「2番目」、「3番目」、「4番目」、「5番目」、「最低」の点数を参照する領域を作成し、「最高」は MAX関数を、「2番目」から「4番目」までは LARGE関数を、「5番目」には SMALL関数を「最低」については MIN関数を用いて値を表示するようにせよ。

コピーをして領域を埋めるためには参照方法を適切にする必要があることに注意せよ。 ($ はどこにつけるのが正しいか?)

表が出来上がれば、最高点および最低点を除いた平均値を一番右に表示するように設定せよ。

なお、エクセルには与えられたデータの中から、指定した割合の範囲に含まれるデータを除いて平均を求める TRIMMEAN関数があるので、各自でどのような振る舞いをするのか調べてみよ。

並べ替え

エクセルでデータを並べ替えることを「ソート」と呼ぶ。行でソートする場合は、並べ替えたいデータが入力されているセルを選択した後、「データ」→「並べ替え」を実行する。

ソートする領域をエクセルが正しく見つけられなかった場合は「キャンセル」を押して取りやめ、正しい範囲全体を選択した後、「データ」→「並べ替え」を実行する。

並べ替えの際に優先する順位を選択した後、「OK」を押すとソートが実行される。

エクセルがソートする領域を「正しく推定できる」ような単純なデータ構造であるときは、キーとなる列の一つのセルを選択後、メニューバーにあるを押すことにより容易にソートを実行できる。

なお、ソートする際にはまとめて並べ替えを行いたい列(行)を含めた範囲を指定する必要がある。

課題 21

フォーム入力、抽出・並べ替え

データベース用のフォームを使って15名分の名簿(住所録や電話番号など任意)を作成し、並び替えや抽出を行え。

ヒント

データを入力する時には、セルを選択した状態で「データ」→「フォーム」を選ぶ。並び替えや抽出も「データ」→「並べ替え」、「データ」→「フィルタ」を選んで簡単にできます。

例題

フォームを用いた入力、および並べ替えは課題20を参照のこと。

データの抽出には「フィルタ」を用いる。

オートフィルタ
「データ」→「フィルタ」→「オートフィルタ」を実行すると各列の見出しに▼ボタンが表示され、設定されたことが判る。

見出しの▼ボタンを押すことによりフィルタ条件を選択できる。デフォルト以外のフィルタを設定するために、次の２つが用意されている。
- (オプション)を選択し、条件を入力
- (トップテン)を選択し、上位(下位)の指定件数を入力
フィルタが選択されている列は▼の色が異なる。フィルタを解除するには設定されている列で「（すべて)」を選択する。
フィルタオプション
オートフィルタより詳細な条件設定でデータを抽出できる。
1. 事前準備
  条件を設定するための領域として見出し語と同じ内容を入力したセルを準備する。
  
  条件を列記する。1行に記述された複数条件は and が取られ、異なる行に記述された条件は or となる。
2. 実行
  「データ」→「フィルタ」→「フィルタオプションの設定」
例題

条件の入力。「電話番号がフリーダイアルである」 or 「福井市内」。

フィルタオプションの起動

実行結果。選択範囲内を選んだため、条件に一致しないものが隠されている。

フィルタを解除するには「データ」→「フィルタ」→「すべて表示」を実行する。

課題 22

抽出(応用)

課題20で作成したシートをコピーし、シート名を課題22とする。データベースの抽出機能を用いて以下のレコードを抽出しなさい。

平均点が上から3位までのレコードを抽出せよ
A先生と C先生の成績が 80点以上のレコードを抽出せよ

課題21 の復習であるので、課題21を参照のこと。

tacha@tack.fukui-med.ac.jp
$Id: 09.html,v 1.3 2003/06/24 04:46:44 tacha Exp $