法政大学市ヶ谷基礎科目

情報処理演習II

担当　重定　如彦

2003年10月22日

 

第5回　表計算ソフト（その５）

1.      データベースとしての表計算ソフト

表計算ソフトは数字を計算するだけでなく、データベースとしても使うことができます。データベースは、同じ種類のデータを数多く入力し整理する為のソフトで、入力したデータの中から特定の性質を持つデータを検索したり、データの順番を数字の大きい順に並べ替えたりすることができます。例えば、成績表などはデータベースの典型的な例です。前に作成した成績表を開いて下さい。もしGドライブにファイルがない人は、私のホームページのseiseki.xlsからダウンロードして開いて下さい。

 

Ａ

Ｂ

Ｃ

Ｄ

Ｅ

Ｆ

Ｇ

Ｈ

１

名前

出席点

レポート

試験

合計

合計２

成績

 

２

法政　太郎

80

30

50

160

53.3

C

 

３

市ヶ谷　花子

70

50

70

190

63.3

C

 

４

多摩　次郎

20

10

 

30

10.0

D

 

５

小金井　三郎

90

80

90

260

86.7

A

 

６

国際　学

60

60

100

220

73.3

B

 

７

平均点

64

46

77.5

172

57.3

 

 

８

最高点

90

80

100

260

86.7

 

 

９

最低点

20

10

50

30

10.0

 

 

１０

人数

5

5

4

 

 

 

 

１１

 

 

 

 

 

 

 

 

·          データの検索

単に特定の文字を検索するにはWordと同様に、メニューの「編集」→「検索」を行えばＯＫですが、Excelでは「８０以上の数字が入ったデータを検索」のように、条件つきの検索を行うことも可能です。検索を行うには、検索したい範囲を選択状態にし、メニューの「データ」→「フォーム」を実行します。するとデータの検索を行う為のパネルが表示されまず。ここでは例として、Ａ１：Ｅ６の範囲を選択状態にしてからこの操作を行って下さい。このパネルには選択した範囲のデータのうちの一つが以下のように表示されます。

表示中のデータの番号（左）と

データの個数（右）

（注：検索条件設定時は

Criteria　と表示される）

 
 

検索条件の設定ボタン
 

データの表示切り替えボタン
 

データの項目名
 

データの内容
 

右上の「１／５」の部分が、現在表示しているデータの番号と、データの個数を表示しており、「前を検索ボタン」と「次を検索ボタン」で検索条件に一致する（前または）次のデータを表示することができます。データの項目名の所に表示される項目名は、選択範囲の一番上の行の文字が使用されます。検索時には選択範囲の一番上の行のデータは項目名に使用され、検索するデータには含まれない点に注意して下さい。

このパネルを表示した直後は、特になんの検索条件も設定されていませんので、２つの検索ボタンをクリックして全てのデータを順番に表示することができます。

「検索条件」ボタンを押すと右上の表示が「Criteria」となり、検索条件を設定する画面になるので、各項目に、検索条件の式を入力します。例えば、出席点が５０点以上、試験が７０点以上の人物を検索するには、出席点の部分に　>=50　試験の部分に　>=70　を入力します。なお、何も記述しなかった欄の項目は検索のときに無視されます。検索条件の式の入力が完了したら、「フォーム」ボタンを押して、２つの検索ボタンをクリックすると今入力した検索条件を満たすデータのみがパネルに表示されるようになります。

文字の場合は、以下の「？」と「＊」というワイルドカードと呼ばれる特別な文字を使って検索することが出来ます（注：？や＊は半角で記述する必要があります）

「？」は任意の１文字を表します。例えば、検索条件に　　　？郎　　　と記述すると、最初の１文字目が任意の文字、２文字目が「郎」という文字列を検索します。なお、この場合、３文字以上の文字であっても２文字目が「郎」であればその時点で一致するとみなします。従って　三郎太　のような３文字以上の文字列も　　？郎　　で検索できます。

「＊」の場合は、その部分が任意の文字列の代わりになります。例えば、検索条件に

　　＊郎　　　と記述すると、文字列の途中（何文字目でも良い）に「郎」である文字列を検索します。「？」と「＊」の違いは、「？」は必ずそこに何らかの文字が１文字ある必要がありますが、「＊」はそこに文字が何文字（０文字も可）あってもかまわないなという点です。検索条件の名前の部分に　　＊郎　　　と記述し（他の項目の条件は空白にして下さい）名前に「郎」が入っているデータの検索を行って下さい。

注：繰り返しますが、＊や？は必ず半角文字で入力して下さい。また、＊や？そのものを検索したい場合は、「〜？」や「〜＊」のように、〜（チルダ）記号を最初に記述します。

練習問題：Ａ１２：Ａ１８に順に　「項目名 Ａ Ｂ ＡＢ ＡＢＣ ＡＢＢ ＢＢ」　をそれぞれ入力し、検索条件に　？Ｂ　を入力した場合、どの文字が検索されるかを考えなさい。また、　＊Ｂ　と入力して検索した場合、どの文字が検索されるかを考えなさい。

答えがわかった人は、実際に検索を行い答えが正しいことを確認しなさい。

·          並べ替え（ソート）

特定の項目の数字の順番の並べ替え（ソート）を行うには、まず、並べ替えたい部分をすべて選択状態にして下さい。次に、アクティブセルを（TABキーやENTERキーを使って）並べ替えたい項目の列に移動し、ツールバーの「昇り順で並べ替え」ボタンを押すと、アクティブセルがある列の数字を使って、選択範囲のデータがすべて並べ替えられます。

例えば、Ａ２：Ｇ６を選択状態にし、ＴＡＢキーを一回押してＢ２のセルをアクティブにし、「昇り順で並び替え」ボタンを押せば、成績のデータが出席点の小さい順に並び替えられます。また、大きい順に並べ替えるには、「降順で並び替え」ボタンを押してください。

並べ替えは選択されている部分がすべて並べ替えられます。従って、並べ替えを行う際には並べ替えたいデータの部分をすべて選択状態にしてから行って下さい。関係のない部分を選択して並べ替え操作を行ったり、必要な部分を選択せずに並べ替えを行うと、意図した結果にならないことがあります。例えばＢ２：Ｃ６の範囲を選択して、並べ替えの操作を行うと、出席点以外の成績の部分は並べ替えられません。（注：この場合、合計や成績の部分の数値も変化し、並べ替えられたように見えますが、これはＢの出席点の内容が並べ替えで変化したので再計算が行われただけで、実際には並び替えられていません）

また、並べ替えの条件を複数設定して並べ替えを行うこともできます。これを行うには、並べ替えたい部分を選択状態にして、メニューの「データ」→「並べ替え」を実行します。ここでは並べ替えを行うキーを複数設定できます。例えば最優先されるキーに出席点、２番目に優先されるキーにレポートを選んで並べ替えを行った場合は、まず出席点で並べ替えが行われ、もし出席点の等しいデータが複数あった場合は、その中でレポートの点数順に並べ替えが行われます。試しに、小金井三郎の出席点を６０に変更し、最優先されるキーを「出席点」、２番目に優先されるキーを「レポート」にして並べ替え操作を行ってみて下さい。次に、２番目に優先されるキーを「試験」にして並べ替え操作を行い、並べ替えの結果が変わることを確かめて下さい。

·          オートフィルタ（抽出）

表の中で条件に一致するものだけを画面に表示することもできます。この機能は抽出とも呼ばれます。抽出を行うには、抽出を行いたい部分を選択状態にし、メニューの「データ」→「フィルタ」→「オートフィルタ」を実行します。すると、各列に▼マークが示されるので、それをクリックするとメニューが表示されます。ここで「トップテン」を選択すると、その列の上位何人までを表示するかを設定することができます。「オプション」を選択すると、条件を設定するパネルが表示されるので、例えば左に「５０」、右に「以上」を入力することで、その列の５０点以上のデータのみが表示されるようにすることができます。なお、表示されなくなったデータは表から消えてしまったわけではありません。もう一度メニューから「オートフィルタ]を実行すると再び全てのデータが表示されるようになります。また、印刷時には抽出されたデータだけが印刷されます。

オートフィルタは指定した範囲の下にもデータがあればその部分もフィルタの対象にしてしまします。この表のように、平均点より下の行をフィルタの対象にしたくない場合は、間に空白の行を作れば、その空白行より下のデータはフィルタの対象となりません。先ほどの成績表でオートフィルタを試す場合は、平均点より下の行のデータを一つ下にずらしてから、Ａ１：Ｇ６を選択して操作を行って下さい。

2.      応用例２　〜成績表をさらに凝ってみる〜

成績表の例の場合、１００点満点として計算された合計２の点数では、「出席点」と「レポート数」と「試験」の点数の割合が同じでしたが、一般的には出席点より試験の点数のほうが重視されることが多いようです。また、Ａ、Ｂ、Ｃ、Ｄの境目がそれぞれ８０点、７０点、５０点でしたが、これを変えたい場合はどうすればよいでしょうか？例えばＡとＢの境目を変更したい場合は、Ｇの列の計算式の８０という数字を変更すればＯＫです。しかし、この境目の値をいろいろと何回も変えて調整したい場合、毎回式の内容を変更するのは非常に大変です。そこで次の例では、これらの変更したい値を特定のセルの中に記述し、そのセルの中身を書き換えるだけで簡単に変更できるような表を作ることにします。

まず、点数の割合を変えてみましょう。例えば出席点とレポート点と試験の点数の重みを１：２：４（レポートの点数を２倍、試験の点数を４倍にする）にするにはどうすればよいでしょうか？これを行う為に表の１１の行に重みのデータを入力し、Ｂ１１のセルに「出席点重み」、Ｃ１２のセルに「レポート重み」、Ｄ１２のセルに「試験重み」という名前を付けて下さい（セルに名前を付けるには、メニューの「挿入」→「名前」→「定義」です）。また、Ｅ１１のセルには重みの合計を計算しておいて下さい。

次に、重みを元に合計点を計算しなおしてみましょう。最初の式では単純にそれぞれの点数の合計を計算していましたが、今度はそれぞれの点数に重みの掛け算したものを足します。従ってＥ２に入る合計の式は以下のようになります。

　＝Ｂ２＊出席点重み＋Ｃ２＊レポート重み＋Ｄ２＊試験重み

これをＥ３：Ｅ６にコピーすれば全員分の新しい合計点を計算することができます。

次に、合計２の部分にはこの合計点を１００点満点に換算する式を書く必要があります。

 

最高点は

　　　　１００＊出席点重み＋１００＊レポート重み＋１００＊試験重み

＝１００＊（出席点重み＋レポート重み＋試験重み）

です。このうち括弧の中の値はすでにＥ１１のセルに計算済みです。従ってＦ２は

　＝Ｅ２／Ｅ１１

　で計算することができます。このままこれをコピーするとＥ１１が相対参照されているためコピーするとＥ１１がずれてコピーされてしまい計算が間違ってしまうので、Ｅ１１のセルに「重み合計」という名前をつけて絶対参照で参照することにします。

　　　＝Ｅ２／重み合計

　という式をＦ２に記述してそれを５人分コピーすれば完成です。これで、重みの横の３つの数字を変化させるだけで、出席点、レポート、試験をどれだけ重視するかを自由自在に変更することができるようになりました。

　次はＡ，Ｂ，Ｃ，Ｄの境目の点数を変更してみましょう。表のＡ１２：Ｄ１３に以下のようにデータを書き足して、Ｂ１３のセルにＡの評価の最低点、Ｃ１３のセルにＢの評価の最低点、Ｄ１３のセルにＣの評価の最低点を書くことにします。

 

Ａ

Ｂ

Ｃ

Ｄ

Ｅ

Ｆ

Ｇ

Ｈ

１

名前

出席点

レポート

試験

合計

合計２

成績

 

２

法政　太郎

80

30

50

340

48.6

D

 

３

市ヶ谷　花子

70

50

70

450

64.3

C

 

４

多摩　次郎

20

10

 

40

5.7

D

 

５

小金井　三郎

90

80

90

610

87.1

A

 

６

国際　学

60

60

100

580

82.9

A

 

７

平均点

64

46

77.5

466

57.3

 

 

８

最高点

90

80

100

610

86.7

 

 

９

最低点

20

10

50

40

10.0

 

 

１０

人数

5

5

4

 

 

 

 

１１

重み

1

2

4

7

 

 

 

１２

 

Ａ

Ｂ

Ｃ

 

 

 

 

１３

最低点

85

60

45

 

 

 

 

１４

 

 

 

 

 

 

 

 

そして、Ｂ１３のセルに「Ａ最低点」、Ｃ１３のセルに「Ｂ最低点」、Ｃ１４のセルに「Ｃ最低点」と名前を付けて下さい。

　後はＧの列の成績の式の８０、７０、５０の数字をそれぞれ以下のように「Ａ最低点」、「Ｂ最低点」、「Ｃ最低点」に置き換えてやれば完成です。

　＝ＩＦ（Ｆ２＞＝Ａ最低点，“Ａ”, ＩＦ（Ｆ２＞＝Ｂ最低点，“Ｂ”，ＩＦ（Ｆ２＞＝Ｃ最低点，“Ｃ”，“Ｄ”）））

完成した表の重みや最低点の部分をいろいろ変えてみて、表の値が変わることを確かめてみて下さい。このように、式の中で頻繁に変更したい部分がある場合は、それを式の中に直接記述するのではなく、表の中にそれらの数字を記述するセルを用意し、式の中でそのセルを絶対参照すると後から変更を簡単に行うことができます。

3.      エラー表記と対処法

Excelでは、計算式が間違っていたりした場合、セルの中にエラーを表示します。以下に主なエラーの表記とその対処法を表にします。

エラー名

意味

対処法

#####

数字を表示する幅が足りない

セルの横幅を広げる

#VALUE!

例えば文字と数字を足し算しようとした場合など、計算する値が不正

計算するセルの中身を確認し、

計算できるように変更する。

#DIV/0!

式の中で０で割り算を行った

０で割らないようにセルの中身を変更する。

#NAME?

存在しない関数の名前を使用した

関数の名前の綴りをチェックする

エラーが発生した場合は、そのセルをクリックすると左に　！　ボタンが表示されます。このボタンをクリックするとエラーに関するメニューが表示されるので、どうすればよいかわからない場合は、「このエラーに関するヘルプ」を選択してヘルプを参照して下さい。

 

4.      課題

以下の作業を行って下さい。締切りは次回の授業までです。

1.         次の表を入力する。

 

 

Ａ

Ｂ

Ｃ

Ｄ

Ｅ

Ｆ

Ｇ

Ｈ

Ｉ

１

名前

チーム

打率

安打

打点

本塁打

三振

犠打

 

２

 

 

 

 

 

 

 

 

 

３

 

 

 

 

 

 

 

 

 

…

 

 

 

 

 

 

 

 

 

１１

 

 

 

 

 

 

 

 

 

2.         ウェブブラウザを使って　http://sports.yahoo.co.jp/baseball/cl/stats/rbatter.html　を見て、本年度のセ・リーグの打率上位１０人分のデータをＡ２：Ｇ１１のセルに入力する。

3.         データの検索を使って、安打数が１６０以上、打点が９０以上の選手をすべて挙げよ。

4.         データの検索を使って、巨人の選手で打点が７０以下の選手をすべて挙げよ。

5.         並べ替え機能を使って、安打数の多い順に並べかえ、上位３名の名前を挙げよ。

6.         並べ替え機能を使って、三振数の少ない順に並べ替えよ。下位（少ない方から順に）３名の名前を挙げよ。

7.         並べ替え機能を使い、最優先されるキーを「犠打」、２番目に優先されるキーを「三振」として多い順に並べ替えよ。また、上位３名の名前を挙げよ。

8.         オートフィルタ機能を使って、本塁打の上位５名だけを表示せよ。

9.         表を更に以下のようにＩ，Ｊ，Ｋの列の部分を付け加え、Ｉ２：Ｉ１１に式を記述して、最多安打数の選手に○を表示するようにせよ。

10.     同様に、Ｊ２：Ｊ１１及び、Ｋ２：Ｋ１１に式を記述して、打点王及び、本塁打王の選手に○を表示するようにせよ

11.     ３〜７は課題のメールの本文に答えを書いて送ること。

12.     作成した表をＧドライブに保存し、添付ファイルで送ること。

 

 

Ａ〜F

Ｇ

Ｈ

Ｉ

Ｊ

Ｋ

Ｌ

１

省略

三振

犠打

最多安打

打点王

本塁打王

 

２

 

 

 

 

 

 

３

 

 

 

 

 

 

…

 

 

 

 

 

 

１１

 

 

 

 

 

 

 

ヒント：９、１０は、まず関数MAXを使い、表のどこかに安打、打点、本塁打の最大値を求める式を記述して下さい。次に、計算で求めた最大値とそれぞれの選手の成績を比較し、等しければ○と表示するような式を記述すればOKです。

 

 

 

 

 

 

出席のメールは　takaaki_311@yahoo.co.jp　までお願いします。

課題や質問のメールなどは、sigesada@edu.i.hosei.ac.jpまでお願いします。

授業の資料の最新版はhttp://www.edu.i.hosei.ac.jp/~sigesada/にあります。