出席状況の計算ここからは前回に利用した、いくつかの関数を使って成績を分析します。個人の出席状況では、個人が何回出席しているかを計算しましょう。 O1セルに「出席回数」と入力してください。 出席回数は、各回の「○」の数を数えればわかります。COUNTIF関数を使って、計算します。成績の評価
各回の出席状況次に、各回の出席の状況を求めます。出席者数と出席率を計算します。 まず、B53セルに「出席者数」、B54セルに「出席率」と入力してください。 次に、C53セルに第1回の出席者数の計算を入力します。 先ほどと同じように、COUNTIF関数を使って計算してください。計算する範囲は、第1回の出席分(C2〜C51セル)になります。計算できたら、C53セルの計算式をD53〜L53セルにコピーしてください。 次に、各回の出席率を計算します。出席率は次のような計算になります。出席率(パーセンテージ) = 出席者数(人) ÷ 受講者数(人)
出席者数は計算できたので、受講者数がわかれば、出席率は計算できます。
今回のデータでは50人いることがわかっていますが、受講者数がかわっても計算できるように、COUNTA関数を使って、名前のセルの数から、人数を求めます。
#ref(): File not found: "excel08.png" at page "Lecture/InfoPrac2004/9th/seiseki_hyouka" 絶対参照次に、入力した数式をD54〜L54にコピーすることにしましょう。 ここで、D54セルを見てみると、「104.7%」になっていますが、出席率が100%を超えることはありえません。コピーされた計算式を見てみると、「=D53/COUNTA(C2:E51)」となっていています。コピー元のC54の数式が相対参照だったので、コピーしたときにCOUNTA関数の計算範囲がずれてしまいました。 このような計算式をコピーしても参照先のセルが変えたくない場合は、セルの番地を基準にした絶対的な位置を使った参照のしかたである、「絶対参照」を使います。 絶対参照を使うには、動かしたくないセル番地の行番号や列番号の前に「$」をつけます。通常の参照のしかたである「相対参照」とまとめると次のようになります。
B2→$B$2→B$2→$B2→B2(もとに戻る)
個人点数の計算課題、テスト、出席回数の3つの評価を使って、個人の点数を計算します。 P1セルに「点数」と入力してください。四捨五入評価の配分は、「課題4割、テスト4割、出席2割」とします。 割合はパーセンテージであらわせば、「4割」は「40%」のことです。 課題とテストは100点満点で計算していますので、出席回数(最大10回)を10倍すれば課題とテストと釣り合いが取れます。 したがって、次のような計算式で点数を求めることができます。点数 = 課題の点×課題の割合 + テストの点×テストの割合 + 出席回数×10×出席の割合
すると、P2セルの内容は「M2*0.4+N2*0.4+O2*10*0.2」となりますが、このままでは小数点以下の数字がでてしまいます。そこで小数点以下を四捨五入することにします。
それには、ROUND関数を使います。
条件判定たいていの授業では、成績をつける上での条件があります(あるものなのです)。 たとえば、次のようなものです。
IF(O2>=6, ROUND(M2*0.4+N2*0.4+O2*10*0.2,0), 0) ----- -------------------------------- - 条件 真の場合の結果 偽の場合の結果この条件を使って、P2セルを次のように書き換えましょう。
順位前回と同じように、成績の順位を求めてみましょう。 Q1セルに「順位」と入力してください。 順位を計算するRANK関数を使います。
課題、テストの結果分析成績を分析するのに必要な数字は、ほぼ出揃ったので、次は成績の分析をします。 T列に、次の図のように、T1〜T24までのせるに文字を入力してください。#ref(): File not found: "excel10.png" at page "Lecture/InfoPrac2004/9th/seiseki_hyouka" 個数、平均、最大・最小まず次のような計算をしてください。 先週の内容とこれまでの内容を参考にしてください。
中央値次に、新しい関数を紹介します。 MEDIANという関数です。
1,2,3,5,8
真ん中の数が中央値になるので、「3」が中央値になります。では、次のような数列ではどうなるでしょうか。
1,2,3,5,8,13,21,34
今度は数値の数が8個で偶数になってしまい、中央の数字がありません。このような場合は、真ん中の2つの数を足して2で割ったものが中央値となります。つまり、「8」と「13」を足して2で割った「10.5」が中央値です。
次のようにして、点数全体の中央値を求めてみましょう。
最頻値また、新しい関数を紹介します。 MODEという関数です。
1, 1, 2, 2, 3, 3, 3, 3, 4, 5, 5
このような数列の最頻値は、4つ登場している「3」になります。
次のようにして、点数全体の最頻値を求めてみましょう。
個人成績の評価成績の分析のしかたには、点数だけではなく、点数をもとにした「評価」があります。 いわゆる「優・良・可・不可」というものです。 成績の分析のしかたには、点数だけではなく、 いわゆる「秀・優・良・可・不可」のような、 点数をもとにした「評価」があります。 次の図のように、W1〜X6のセルに、評価の表を入力してください。#ref(): File not found: "excel11.png" at page "Lecture/InfoPrac2004/9th/seiseki_hyouka" ![]() 個人の評価個人の評価入力した評価の表を元に、個人の評価を調べてみましょう。 つまり「評価の表の中から、個人の点数に最も近い表を探し出す」ということをします。 つまり「 評価の表の中から、個人の点数に最も近い項目を探し出す 」ということをします。 このように、ある値を使って指定した範囲からデータを取り出すには、VLOOKUP関数を使います。
検索の型に注目して、説明します。 次のような表があるとします。
VLOOKUP関数を使って、左端の値から、右端(3列目)の記号を取り出すことを考えます。
では、まずR1セルに「評価」と入力してください。 そして、次のようにして、個人の評価を求めてください。
評価ごとの人数書き換えできたら、R2セルの計算式をR3〜R37までにコピーしましょう。評価ごとの人数個人の評価ができたので、評価ごとの人数を集計します。 T26〜U31セルに、次の図のように入力してください。#ref(): File not found: "excel13.png" at page "Lecture/InfoPrac2004/9th/seiseki_hyouka" T20〜U25セルに、次の図のように入力してください。![]() COUNTIF関数を使って、 U27〜U31セルに、「秀・優・良・可・不可」のそれぞれの人数を計算してください。 U21〜U25セルに、「秀・優・良・可・不可」のそれぞれの人数を計算してください。度数分布度数分布次に、点数ごとの分布の状況を調べてみましょう。 10点刻みの分布(度数分布)を計算します。 U33〜U43セルに、次の図のように入力してください。 T27〜U37セルに、次の図のように入力してください。 分布の意味は「10点以上、20点未満」という意味で、最後の行だけ「90点以上、100点以下」という意味になります。#ref(): File not found: "excel14.png" at page "Lecture/InfoPrac2004/9th/seiseki_hyouka" ![]() 入力できたら、COUNTIF関数を使って、U34〜U43のセルに分布を計算しましょう。 入力できたら、COUNTIF関数を使って、U28〜U37のセルに分布を計算しましょう。 ヒントは次のとおりです。
検索用シートの作成ここまで集計・分析したデータを、検索するためのワークシートを作ります。 番号を入力すると、名前と成績の情報が表示されるようにします。次へ進んでください。
表の作成次の図を参考にして、自由に表を作成してください。 解説すると、塗りつぶしが黄色い部分が入力欄で、青い部分が検索結果を表示するところです。#ref(): File not found: "excel15.png" at page "Lecture/InfoPrac2004/9th/seiseki_hyouka" 検索のしかた入力された番号をもとに、別のワークシートの表から、番号に対応した値を検索します。 このような場合にも、今回紹介したVLOOKUP関数を使います。 VLOOKUP関数を使って、値を検索してください。計算式を考える上でのポイントは次のとおりです。
空白の処理ここまでくれば、あと一歩で完成です。 もし番号に何も入力されていないと、名前などの検索結果には「#N/A」というエラー値が表示されてしまいます。 この部分を改良しましょう。 「番号に何も入力されていなければ、結果の部分は空白を表示する」という機能を付け加えましょう。次のヒントを参考にして、検索のシートを完成してください。
|