4日目

目標

関数の使い方(復習)
条件分岐(論理関数)
条件付き書式

課題

課題10 成績データの整理(分散,標準偏差)
課題11 偏差値の計算、成績の評価
課題12 順位付けを行う

使用するデータ

以下のデータを、次の操作にしたがって加工して用いよ。(「データの加工」の復習)

新しくシートを作成し、名前を「0507作業用シート」とせよ
Internet Explorer からデータを、領域の左上が D1セルになるようにコピーする
D1:W5 の領域をコピーした後、「形式を選択して貼り付け」機能の「行と列を入れ替える」を用いて、20行5列で、左上が D7 となるようにデータ形式を変更せよ
その後、各列のデータをすべて A1:A100 の領域に順にコピーせよ
新しくシートを作成し、名前を「課題10」とし、その A 列に「05/07作業用シート」の A1:A100 をコピーせよ

73	71	75	71	70	65	68	58	63	64	80	70	71	70	71	58	66	64	66	81
69	57	70	57	64	70	66	75	67	74	53	73	59	60	69	60	64	57	66	66
69	79	77	67	72	68	70	63	62	79	76	67	72	80	78	66	58	73	67	70
69	80	67	70	83	72	72	75	68	81	74	67	67	69	69	58	71	76	80	74
68	77	69	74	70	63	72	77	66	60	77	62	70	63	64	72	78	78	64	81

課題 10

成績データの整理 (分散、標準偏差)

上記の表は、全国の大学2年生から 100名を抽出して行ったテスト(100点満点）の成績である。 C列, D列の 1から5 行を用いて、成績の平均値、分散、標準偏差、母分散、母標準偏差を小数第2位まで求めよ。

ただし、分散、標準偏差とは、上記100人のばらつきの指標であり、母分散、母標準偏差とは上記の100人のデータを用いて、母集団(この場合は全国の大学2年生)に対する推定を行った値である。「関数の貼り付け」時に適切な関数を選択すること。

なお、統計量の定義については参考資料を参照のこと。

解答例

課題 11

偏差値の計算、成績の評価

シート「課題10」を新しいシートにコピーし、シート名を「課題11」とする。各学生の点数の右に列を2列挿入し、B列に偏差値を表示し、 C列に A, B, C, D の成績評価を行え。ただし、点数の評価は IF関数を使用して、80点以上を A, 70点以上を B, 60点以上を C, 60点未満を D とする。さらに、評価 D の欄の書式を条件付き書式を用いて、背景を赤とせよ。また、先頭に1行挿入し、各列のデータの内容を表示せよ。

ヒント

偏差値は標準偏差をσとしたとき、次式で表される。

IF関数は条件を満たす場合は x, 満たさない場合は y とすると

=if(条件, x, y)

となる。x として、文字 B を、y として空白を書く場合は ('"' でくくって)

=if(条件, "B", "")

のようである。条件としてセル A2 が60未満の場合は

=if(A2 < 60, "B", "")

となる。

というわけで、

=if(評価されるセル番地 < 60, "D", if(…))

のような式がはいることになる。

簡単な if 関数の使用例

まず、if 関数を理解するために、60点以上なら「合格」、60点未満ならば「不合格」と表示することを考える。

論理値

if 関数の条件としては論理値(真または偽)を返すものが書ける。式「 A2 < 60 」はその結果が真あるいは偽の2値しか取れないので論理値である。数値が代入された場合 0ならば「偽」として、それ以外の場合は「真」として取り扱われる。

論理式

複数の条件を満たす、満たさないという判断をするには論理関数を用いる。エクセルに含まれている論理関数には次のようなものがある。

AND(条件1, 条件2,....)
30個までの論理式を引数に取り、すべてが真の場合「真」を、いずれか１つでも偽の場合「偽」を返す。
FALSE()
常に「偽」を返す。
NOT (条件)
与えられた論理式が「真」ならば「偽」を、「偽」ならば「真」を返す。
OR(条件1, 条件2,....)
30個までの論理式を引数に取り、いずれか１つでも真の場合「真」を、すべてが偽の場合には「偽」を返す。
TRUE()
常に「真」を返す。

複雑な if 関数の使用例

評価の判定は次のような仮定を踏む。

実際にエクセルで上記の式をいれる場合は、「偽の場合」の入力時に続けて次の IF 関数を入力する。

条件付き書式

セルの内容に基づいて、書式を変更することによって結果の視認性を向上させることができる。そのためには、「書式(O)」の中の「条件付き書式(D)」を用いる。条件付き書式を呼び出すと次のようなウィンドウが表示される。

条件を指定した後、書式をクリックすることにより条件に合致する場合の書式を選択することができる。「追加(A)>>」をクリックすることにより複数の条件を指定することも可能である。

課題 12

順位付けを行う

RANK関数を用いて「課題11」の成績評価の右側に、成績の上位からの順序をつけよ。

ヒント

RANK 関数の引数は、RANK(数値、範囲、順序) である。オートフィルを用いて、すべての欄を埋めるつもりならば、「範囲」を指定する際には絶対参照を用いなければ正しい順序は得られない事に注意すること。また「順序」は 0 の場合は降順、それ以外の場合は昇順となる。

最終結果の例

3日目

表紙

5日目

tacha@tack.fukui-med.ac.jp
$Id: 04.html,v 1.1 2004/05/06 12:53:19 tacha Exp $

73	71	75	71	70	65	68	58	63	64	80	70	71	70	71	58	66	64	66	81
69	57	70	57	64	70	66	75	67	74	53	73	59	60	69	60	64	57	66	66
69	79	77	67	72	68	70	63	62	79	76	67	72	80	78	66	58	73	67	70
69	80	67	70	83	72	72	75	68	81	74	67	67	69	69	58	71	76	80	74
68	77	69	74	70	63	72	77	66	60	77	62	70	63	64	72	78	78	64	81

73	71	75	71	70	65	68	58	63	64	80	70	71	70	71	58	66	64	66	81
69	57	70	57	64	70	66	75	67	74	53	73	59	60	69	60	64	57	66	66
69	79	77	67	72	68	70	63	62	79	76	67	72	80	78	66	58	73	67	70
69	80	67	70	83	72	72	75	68	81	74	67	67	69	69	58	71	76	80	74
68	77	69	74	70	63	72	77	66	60	77	62	70	63	64	72	78	78	64	81

73	71	75	71	70	65	68	58	63	64	80	70	71	70	71	58	66	64	66	81
69	57	70	57	64	70	66	75	67	74	53	73	59	60	69	60	64	57	66	66
69	79	77	67	72	68	70	63	62	79	76	67	72	80	78	66	58	73	67	70
69	80	67	70	83	72	72	75	68	81	74	67	67	69	69	58	71	76	80	74
68	77	69	74	70	63	72	77	66	60	77	62	70	63	64	72	78	78	64	81