7日目 9日目

8日目

目標

• データの並べ替えができるようになる
• エクセルのデータベース機能を理解する (1:検索)

課題

• 課題19並び替え
• 課題20検索

課題 19

並び替え

以下の表は、フィギュアスケートの大会での審判員の採点表である。 次の方法にしたがって、得点を求め、順位順に並び替えよ。

1. 各審判は10点満点で採点を行う
2. 採点から、最高最低を１つずつ取り除いた残りの平均を得点とする

入力テクニック (フォームの利用)

以前に、データを入力する場合にあらかじめ 範囲指定を行っておく方法を学習したが、 大量のデータを入力する場合、フォームを利用した入力を用いた方が 混乱が少なくてすむ場合が考えられる。 フォームを用いた入力を行う場合には、いずれかのデータセルを選択後、 「データ」→「フォーム」を実行する。 なお、フォームを利用する場合オートフィルは利用できないので、 入力の手間を減らすためにはあらかじめ埋められる範囲の入力はしておいたほうが良い。 フォーム入力ウィンドウの右上にでている数字は「現在編集中のデータ／全データ数」である。 次のデータに移動する際には「次を検索」、前に戻る場合は「前を検索」、最後にデータを追加する際には「新規」を押す。 必要ないデータを削除することもできる。フォーム入力を終える場合には「閉じる」を押す。 なお、エクセルがデータ範囲を正しく推測できない場合は、範囲全体を選択した後、フォームを起動する。

入力フォーム

最高点、最低点、領域

複数の採点官による得点を評価する際に、上位および下位の評価を除いた 中心部分の平均点を用いることがある。 例として本問では「フィギュアスケート」を取り上げた。

エクセルでは、LARGEやSMALL関数を用いることで、 このような作業を行うことができる。 LARGE(SMALL) 関数は、与えられた領域中(引数「範囲」)から n 番目(引数「順位」)に大きな(小さな)値を返す。 なお、すでにやったように最大値、および最小値は MAXやMIN関数でも得られる。

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

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

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

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

並べ替え

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

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

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

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

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

小ネタ

列や行を隠すには

上の並べ替えの例を示した図には、途中の取り扱いで利用した I 列から O 列までが表示されていない。 このように特定の列や行の表示を行わないようにするには、 表示したくない列(行)を選択後、右クリックしたメニューに含まれる「表示しない」を実行する。

隠した領域を再び表示するためには、隠れた領域を含む、より大きな領域を選択後、 「再表示」を実行すれば良い。

課題 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 になる部分が表示されていない。 このようにするためには、「ツール」→「オプション」を選択し、 「表示」タブを選んだなかにある、「ウィンドウオプション」中の「ゼロ値」のチェックを外せば良い。