法政大学国際文化学部
情報リテラシーI,II
担当 重定 如彦
2008年7月15日
第25回 表計算ソフト(その5)
1. データベースとしての表計算ソフト
表計算ソフトは数字を計算するだけでなく、データベースとしても使うことができます。データベースは、様々なデータを数多く入力し整理する為のソフトで、入力したデータの中から特定の性質を持つデータを検索したり、データの順番を数字の大きい順に並べ替えたりすることができます。例えば、成績表などはデータベースの典型的な例です。前に作成した成績表を開いて下さい。もしGドライブにファイルがない人は、私のホームページのseiseki.xlsxからダウンロードして開いて下さい。
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
名前 |
出席点 |
レポート |
試験 |
合計 |
合計2 |
成績 |
|
2 |
法政 太郎 |
80 |
30 |
50 |
160 |
53.3 |
C |
|
3 |
市ヶ谷 花子 |
70 |
50 |
70 |
190 |
63.3 |
C |
|
4 |
多摩 次郎 |
20 |
10 |
|
30 |
10.0 |
D |
|
5 |
小金井 三郎 |
90 |
80 |
90 |
260 |
86.7 |
A |
|
6 |
国際 学 |
60 |
60 |
100 |
220 |
73.3 |
B |
|
7 |
平均点 |
64 |
46 |
77.5 |
172 |
57.3 |
|
|
8 |
最高点 |
90 |
80 |
100 |
260 |
86.7 |
|
|
9 |
最低点 |
20 |
10 |
50 |
30 |
10.0 |
|
|
10 |
人数 |
5 |
5 |
4 |
|
|
|
|
11 |
|
|
|
|
|
|
|
|
·
データの検索
単に特定の文字を検索するにはWordと同様に、ホームタブの「編集」グループの「検索と選択」内の「検索」を行えばOKですが、Excelでは「80以上の数字が入ったデータを検索」のように、条件付きの検索を行うことも可能です。条件付きの検索を行うには「フォーム」という機能を使いますが、Excel2007でフォーム機能を使用するには以下の設定が必要です。「Ofiiceボタン」のメニューの「Excelのオプション」を選択する。次にパネルの左の「ユーザ設定」をクリックし、右のコマンドの選択のメニューから「リボンにないコマンド」を選択する。下の一覧の中から「フォーム」を選択し、「追加」ボタンをクリックしてから「OK」をクリックする。この操作を行うことにより、タイトルバーの左に「フォーム」のボタンが出現します。次に、検索したい範囲を選択状態にしてから「フォーム」ボタンをクリックするとデータの検索を行う為のパネルが表示されまず。ここでは例として、
A1:E6の範囲を選択状態にしてからこの操作を行って下さい。このパネルには選択した範囲のデータのうちの一つが以下のように表示されます。
表示中のデータの番号(左)と データの個数(右) (注:検索条件設定時は Criteria と表示される) 検索条件の設定ボタン データの表示切り替えボタン データの項目名 データの内容
右上の「1/5」の部分が、現在表示しているデータの番号と、データの個数を表示しており、「前を検索ボタン」と「次を検索ボタン」で検索条件に一致する(前または)次のデータを表示することができます。データの項目名の所に表示される項目名は、選択範囲の一番上の行の文字が使用されます。検索時には選択範囲の一番上の行のデータは項目名に使用され、検索するデータには含まれない点に注意して下さい。
このパネルを表示した直後は、特に何の検索条件も設定されていませんので、2つの検索ボタンをクリックして全てのデータを順番に表示することができます。
「検索条件」ボタンを押すと右上の表示が「Criteria」となり、検索条件を設定する画面になるので、各項目に、検索条件の式を入力します。例えば、出席点が50点以上、試験が70点以上の人物を検索するには、出席点の部分に >=50 試験の部分に >=70 を入力します。なお、何も記述しなかった欄の項目は検索のときに無視されます。検索条件の式の入力が完了したら、「フォーム」ボタンを押して、2つの検索ボタンをクリックすると今入力した検索条件を満たすデータのみがパネルに表示されるようになります。
文字の場合は、以下の「?」と「*」というワイルドカードと呼ばれる特別な文字を使って検索することが出来ます(注:?や*は半角で記述する必要があります)
「?」は任意の1文字を表します。例えば、検索条件に ?郎 と記述すると、最初の1文字目が任意の文字、2文字目が「郎」という文字列を検索します。なお、この場合、3文字以上の文字であっても2文字目が「郎」であればその時点で一致するとみなします。従って 三郎太 のような3文字以上の文字列も ?郎 で検索できます。
「*」の場合は、その部分が任意の文字列の代わりになります。例えば、検索条件に
*郎 と記述すると、文字列の途中(何文字目でも良い)に「郎」である文字列を検索します。「?」と「*」の違いは、「?」は必ずそこに何らかの文字が1文字ある必要がありますが、「*」はそこに文字が何文字(0文字も可)あってもかまわないなという点です。検索条件の名前の部分に *郎 と記述し(他の項目の条件は空白にして下さい)名前に「郎」が入っているデータの検索を行って下さい。
注:繰り返しますが、*や?は必ず半角文字で入力して下さい。また、*や?そのものを検索したい場合は、「〜?」や「〜*」のように、〜(チルダ)記号を最初に記述します。
練習問題:A12:A18に順に 「項目名 A B AB ABC ABB BB」 をそれぞれ入力し、検索条件に ?B を入力した場合、どの文字が検索されるかを考えなさい。また、 *B と入力して検索した場合、どの文字が検索されるかを考えなさい。
答えがわかった人は、実際に検索を行い答えが正しいことを確認しなさい。
·
並べ替え(ソート)
特定の項目の数字の順番の並べ替え(ソート)を行うには、まず、並べ替えたい部分をすべて選択状態にして下さい。次に、アクティブセルを(TABキーやENTERキーを使って)並べ替えたい項目の列に移動し、ホームタブの「編集」グループの「並べ替えとフィルタ」内の「昇り順で並べ替え」ボタンを押すと、アクティブセルがある列の数字を使って、選択範囲のデータがすべて小さい順に並べ替えられます。例えば、A2:G6を選択状態にし、TABキーを一回押してB2のセルをアクティブにし、「昇り順で並び替え」ボタンを押せば、成績のデータが出席点の小さい順に並び替えられます。また、大きい順に並べ替えるには、「降順で並び替え」を選んで下さい。(注:データタブの「並べ替えとフィルタ」グループ内のボタンを使って同様の操作を行うことができます)
並べ替えは選択されている部分がすべて並べ替えられます。従って、並べ替えを行う際には並べ替えたいデータの部分をすべて選択状態にしてから行って下さい。関係のない部分を選択して並べ替え操作を行ったり、必要な部分を選択せずに並べ替えを行うと、意図した結果にならないことがあります。例えばB2:C6の範囲を選択して、並べ替えの操作を行うと、出席点とレポート以外の成績の部分は並べ替えられません。(注:この場合、合計や成績の部分の数値も変化し、並べ替えられたように見えますが、これはBの出席点とCのレポートの内容が並べ替えで変化したので再計算が行われただけで、実際には並び替えられていません)。また、並べ替えの条件を複数設定して並べ替えを行うこともできます。これを行うには、並べ替えたい部分を選択状態にして、ホームタブの「編集」グループの「並べ替えとフィルタ」内の「ユーザ設定の並べ替え」を実行します。ここでは並べ替えを行うキー(並べ替えの条件のこと)を複数設定できます。まず「レベルの追加」ボタンをクリックしてキーを追加して下さい。次に、最優先されるキーに出席点、値、降順、2番目に優先されるキーにレポート、値、降順を選んで「OK」ボタンをクリックすると、まず出席点で並べ替えが行われ、もし出席点の等しいデータが複数あった場合は、その中でレポートの点数順に並べ替えが行われます。試しに、小金井三郎の出席点を60に変更し、最優先されるキーを「出席点」、2番目に優先されるキーを「レポート」にして並べ替え操作を行ってみて下さい。次に、2番目に優先されるキーを「試験」にして並べ替え操作を行い、並べ替えの結果が変わることを確かめて下さい。
·
オートフィルタ(抽出)
表の中で条件に一致するものだけを画面に表示することもできます。この機能は抽出とも呼ばれます。抽出を行うには、抽出を行いたい部分を選択状態にし、ホームタブの「編集」グループの「並べ替えとフィルタ」内の「フィルタ」を実行します。すると、各列に▼マークが示されるので、それをクリックするとメニューが表示されます。ここで、「昇順」や「降順」を選ぶとソートが行われます。また、数値フィルタメニューの中からフィルタ(抽出)を行う条件を選ぶことができます。「トップテン」、「平均より上」、「平均より下」以外の項目を選択すると、条件を設定するパネルが表示されるので、例えば左に「50」を入力し、右に「以上」を選択することで、その列の50点以上のデータのみが表示されるようにすることができます。また、下段のテキストボックスに条件を入力することで、2つ目の条件を設定することも可能です。この場合、「AND」をチェックすると両方の条件を共に満たす場合、「OR」をチェックするとどちらか一方の条件を見たす場合を指定することになります。「トップテン」を選択すると、その列の上位何人までを表示するかを設定することができます。なお、表示されなくなったデータは表から消えてしまったわけではありません。もう一度「フィルタ」ボタンをクリックすると再び全てのデータが表示されるようになります。また、印刷時には抽出されたデータだけが印刷されます。
オートフィルタは指定した範囲の下にもデータ入力されていればその部分もフィルタの対象にしてしまします。この表のように、平均点より下の行をフィルタの対象にしたくない場合は、間に空白の行を作れば、その空白行より下のデータはフィルタの対象となりません。先ほどの成績表でオートフィルタを試す場合は、平均点より下の行のデータを一つ下にずらしてから、A1:G6を選択して操作を行って下さい。
2. 応用例2 〜成績表をさらに凝ってみる〜
成績表の例の場合、100点満点として計算された合計2の点数では、「出席点」と「レポート数」と「試験」の点数の割合が同じでしたが、一般的には出席点より試験の点数のほうが重視されることが多いようです。また、A、B、C、Dの境目がそれぞれ80点、70点、50点でしたが、これを変えたい場合はどうすればよいでしょうか?例えばAとBの境目を変更したい場合は、Gの列の計算式の80という数字を変更すればOKです。しかし、この境目の値をいろいろと何回も変えて調整したい場合、毎回式の内容を変更するのは非常に大変です。そこで次の例では、これらの変更したい値を特定のセルの中に記述し、そのセルの中身を書き換えるだけで簡単に変更できるような表を作ることにします。
まず、点数の割合を変えてみましょう。例えば出席点とレポート点と試験の点数の重みを1:2:4(レポートの点数を2倍、試験の点数を4倍にする)にするにはどうすればよいでしょうか?これを行う為に表の11の行に重みのデータを入力し、B11のセルに「出席点重み」、C12のセルに「レポート重み」、D12のセルに「試験重み」という名前を付けて下さい(セルに名前を付けるには、メニューの「挿入」→「名前」→「定義」です)。また、E11のセルには重みの合計を計算しておいて下さい。
次に、重みを元に合計点を計算しなおしてみましょう。最初の式では単純にそれぞれの点数の合計を計算していましたが、今度はそれぞれの点数に重みの掛け算したものを足します。従ってE2に入る合計の式は以下のようになります。
=B2*出席点重み+C2*レポート重み+D2*試験重み
これをE3:E6にコピーすれば全員分の新しい合計点を計算することができます。
次に、合計2の部分にはこの合計点を100点満点に換算する式を書く必要があります。
最高点は
100*出席点重み+100*レポート重み+100*試験重み
=100*(出席点重み+レポート重み+試験重み)
です。このうち括弧の中の値はすでにE11のセルに計算済みです。従ってF2は
=E2/E11
で計算することができます。このままこれをコピーするとE11が相対参照されているためコピーするとE11がずれてコピーされてしまい計算が間違ってしまうので、E11のセルに「重み合計」という名前をつけて絶対参照で参照することにします。
=E2/重み合計
という式をF2に記述してそれを5人分コピーすれば完成です。これで、重みの横の3つの数字を変化させるだけで、出席点、レポート、試験をどれだけ重視するかを自由自在に変更することができるようになりました。
次はA,B,C,Dの境目の点数を変更してみましょう。表のA12:D13に以下のようにデータを書き足して、B13のセルにAの評価の最低点、C13のセルにBの評価の最低点、D13のセルにCの評価の最低点を書くことにします。
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
名前 |
出席点 |
レポート |
試験 |
合計 |
合計2 |
成績 |
|
2 |
法政 太郎 |
80 |
30 |
50 |
340 |
48.6 |
D |
|
3 |
市ヶ谷 花子 |
70 |
50 |
70 |
450 |
64.3 |
C |
|
4 |
多摩 次郎 |
20 |
10 |
|
40 |
5.7 |
D |
|
5 |
小金井 三郎 |
90 |
80 |
90 |
610 |
87.1 |
A |
|
6 |
国際 学 |
60 |
60 |
100 |
580 |
82.9 |
A |
|
7 |
平均点 |
64 |
46 |
77.5 |
466 |
57.3 |
|
|
8 |
最高点 |
90 |
80 |
100 |
610 |
86.7 |
|
|
9 |
最低点 |
20 |
10 |
50 |
40 |
10.0 |
|
|
10 |
人数 |
5 |
5 |
4 |
|
|
|
|
11 |
重み |
1 |
2 |
4 |
7 |
|
|
|
12 |
|
A |
B |
C |
|
|
|
|
13 |
最低点 |
85 |
60 |
45 |
|
|
|
|
14 |
|
|
|
|
|
|
|
|
そして、B13のセルに「A最低点」、C13のセルに「B最低点」、C14のセルに「C最低点」と名前を付けて下さい。
後はGの列の成績の式の80、70、50の数字をそれぞれ以下のように「A最低点」、「B最低点」、「C最低点」に置き換えてやれば完成です。
=IF(F2>=A最低点,“A”, IF(F2>=B最低点,“B”,IF(F2>=C最低点,“C”,“D”)))
完成した表の重みや最低点の部分をいろいろ変えてみて、表の値が変わることを確かめてみて下さい。このように、式の中で頻繁に変更したい部分がある場合は、それを式の中に直接記述するのではなく、表の中にそれらの数字を記述するセルを用意し、式の中でそのセルを絶対参照すると後から変更を簡単に行うことができます。
3. エラー表記と対処法
Excelでは、計算式が間違っていたりした場合、セルの中にエラーを表示します。以下に主なエラーの表記とその対処法を表にします。
エラー名 |
意味 |
対処法 |
##### |
数字を表示する幅が足りない |
セルの横幅を広げる |
#VALUE! |
例えば文字と数字を足し算しようとした場合など、計算する値が不正 |
計算するセルの中身を確認し、 計算できるように変更する。 |
#DIV/0! |
式の中で0で割り算を行った |
0で割らないようにセルの中身を変更する。 |
#NAME? |
存在しない関数の名前を使用した |
関数の名前の綴りをチェックする |
エラーが発生した場合は、そのセルをクリックすると左に ! ボタンが表示されます。このボタンをクリックするとエラーに関するメニューが表示されるので、どうすればよいかわからない場合は、「このエラーに関するヘルプ」を選択してヘルプを参照して下さい。
4. 期末テスト
情報リテラシーIは7月23日(水)の5時限、IIは7月24日(木)の4時限に期末テストを行う予定です。掲示板に発表されていると思いますので各自確認して下さい。私の授業では、説明の都合上シラバスに記述されているIとIIの内容一部入れ替えて行いましたが、試験範囲はシラバスと同じで、以下のようになっているので注意して下さい。また、再履修の方で片方の授業しか取っていない方は、もう片方のプリントを見て試験範囲の内容を勉強して下さい。括弧の中はプリントの回数の番号です。
·
情報リテラシーI
Ø
コンピュータ・ハードウェア概論(1)
Ø
コンピュータ・ソフトウェア概論(2)
Ø
ファイルシステム概論(4,5)
Ø
キーボードと日本語入力(2,3)
Ø
電子メール(6,7)
Ø
ワープロ(11,13,15)
·
情報リテラシーII
Ø
データ表現と情報構造(3の文字コードの部分、10のデータに関する部分)
Ø
表計算ソフトウェア(17,19,21,23,25)
Ø
プレゼンテーション(22,24,26)
Ø
インターネット概論(6のインターネットの説明の部分、8、9、10)
Ø HTMLプログラミング(12,14,16,18,20)
また、試験に関しては以下の点に注意して下さい。
·
資料の持ち込みは不可です。
·
主にプリントを勉強すれば大丈夫ですが、4人の先生で試験問題を作成しているので、多少授業で説明しなかったことが出題される可能性もあります。そのような問題に関しては考慮しますので、それほど心配しないで下さい。
·
例年、軽い気持ちで不正行為を行う人がいますが、決してそのような行為は行わないで下さい。見つかった場合は、他の科目を含めた単位の取り消しや停学など非常に重い処分が下されることになります。また、先輩に頼まれるなど、代理で他人の試験を受けるという行為は、代理を頼んだ人も頼まれた人も処分の対象となります。そのような行為も決して行わないで下さい。
5. 学生による授業評価アンケートについて
他の授業でも既に行っていると思いますが、この授業でも授業評価アンケートを行いますので協力をお願いします。なお、授業評価アンケート以外で、直接授業に関する意見や感想があれば私のメールアドレスまでお願いします。
出席、課題のメールは ta080017@mail.edu.i.hosei.ac.jp までお願いします。
質問のメールなどは、 sigesada@hosei.ac.jp までお願いします。
授業の資料の最新版は http://www.edu.i.hosei.ac.jp/~sigesada/ にあります。