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