Lecture/InfoPrac2004/9th/kenskau のバックアップ(No.1)

バックアップ一覧
差分を表示
現在との差分を表示
現在との差分 - Visual を表示
ソースを表示
Lecture/InfoPrac2004/9th/kenskau へ行く。
- 1 (2004-12-15 (水) 01:11:44)

出席状況の計算

ここからは前回に利用した、いくつかの関数を使って成績を分析します。

▲ ▼

個人の出席状況

では、個人が何回出席しているかを計算しましょう。 O1セルに「出席回数」と入力してください。出席回数は、各回の「○」の数を数えればわかります。COUNTIF関数を使って、計算します。

COUNTIF（指定された範囲のセルのうち、検索条件に一致するセルの個数を返す）

書式 : COUNTIF(範囲, 検索条件)
引数 : 範囲 : 個数を求めるセルの範囲

引数 : 検索条件 : 個数を求めるセルの検索条件

検索条件には、次のような比較演算子を使います。

演算子	式	内容	例	結果
=（等号）	A=B	AとBが等しい	1=2	FALSE
<>（不等号）	A<>B	AとBが等しくない	3<>4	TRUE
>（〜より大きい）	A>B	AがBより大きい	5>6	FALSE
<（〜より小さい）	A<B	AがBより小さい	7<8	TRUE
>=（〜以上）	A>=B	AがB以上である	9>=10	FALSE
<=（〜以上	A<=B	AがB以下である	11<=11	TRUE

ここで、出席とは「セルの値が○であること」ですから、検索条件は「=○」となります。「=」は数字の比較だけでなく、文字の比較にも使うことができます。

O2セルをクリックしてアクティブにします。
「関数の挿入」ボタンをクリックします。
#ref(): File not found: "excel04.png" at page "Lecture/InfoPrac2004/9th/kenskau"
利用する関数を選びます。
利用したい関数の分類を選んだあと、関数名を選びます。「関数の分類」から「すべて表示」か「統計」を選び、「関数名」から「COUNTIF」を選びます。「OK」ボタンをクリックします。
#ref(): File not found: "excel06.png" at page "Lecture/InfoPrac2004/9th/kenskau"
次に、関数に与える引数を設定します。
1. 「範囲」の入力欄の中を削除します。
2. C2〜L2のセルをマウスをドラッグして範囲指定します。
3. 「検索条件」に「"=○"」と入力します。
4. 「OK」ボタンをクリックします。
  #ref(): File not found: "excel07.png" at page "Lecture/InfoPrac2004/9th/kenskau"
O2セルに計算式が設定されます。

計算できたら、O2セルの計算式をO3〜O51セルまでコピーしてください。

▲ ▼

各回の出席状況

次に、各回の出席の状況を求めます。出席者数と出席率を計算します。まず、B53セルに「出席者数」、B54セルに「出席率」と入力してください。

次に、C53セルに第1回の出席者数の計算を入力します。先ほどと同じように、COUNTIF関数を使って計算してください。計算する範囲は、第1回の出席分（C2〜C51セル）になります。計算できたら、C53セルの計算式をD53〜L53セルにコピーしてください。

次に、各回の出席率を計算します。出席率は次のような計算になります。

出席率（パーセンテージ） = 出席者数（人） ÷ 受講者数（人）

出席者数は計算できたので、受講者数がわかれば、出席率は計算できます。今回のデータでは50人いることがわかっていますが、受講者数がかわっても計算できるように、COUNTA関数を使って、名前のセルの数から、人数を求めます。

COUNTA（空白でないセルの個数を計算する）
- 書式 : COUNTA(数値1, 数値2, ...)
- 引数 : 数値1, 数値2, ... ：個数を計算するセルの範囲

注意: COUNT関数は、「数字が入力されているセル」の数を計算するものなので、文字が入力されているセルの数を数えることはできません。

ここでは、「関数の入力」ボタンを使わずに、直接関数を入力します。

C54セルをクリックして、アクティブにします。
「=C53/COUNTA(B2:B51)」と数式を入力します。
Enterキーを押すと、計算式が入力されます。

計算できたら、C54セルの表示形式をパーセンテージにして、小数点以下の桁数を「1」にしてください。

#ref(): File not found: "excel08.png" at page "Lecture/InfoPrac2004/9th/kenskau"

▲ ▼

絶対参照

次に、入力した数式をD54〜L54にコピーすることにしましょう。

ここで、D54セルを見てみると、「104.7%」になっていますが、出席率が100%を超えることはありえません。コピーされた計算式を見てみると、「=D53/COUNTA(C2:E51)」となっていています。コピー元のC54の数式が相対参照だったので、コピーしたときにCOUNTA関数の計算範囲がずれてしまいました。

このような計算式をコピーしても参照先のセルが変えたくない場合は、セルの番地を基準にした絶対的な位置を使った参照のしかたである、「絶対参照」を使います。絶対参照を使うには、動かしたくないセル番地の行番号や列番号の前に「$」をつけます。通常の参照のしかたである「相対参照」とまとめると次のようになります。

参照のしかた	例
相対参照	A1
絶対参照（セルを固定）	$A$1
絶対参照（行を固定）	A$1
絶対参照（列を固定）	$A1

ここでは、セルか列を固定すればよいので、「COUNTA($B$2:$B$51)」を使うことにします。F4キーを使うと、押すたびに参照のしかたを切り替えることができます。

B2→$B$2→B$2→$B2→B2（もとに戻る）

C54セルをクリックします。
数式バーの計算式「=C53/COUNTA(B2:B51)」の「B2」をクリックします。
F4キーを何回か押して、「$B$2」か「B$2」に変更します。
「B51」をクリックして、F4キーを何回か押し「$B$51」か「B$51」に変更します。
#ref(): File not found: "excel09.png" at page "Lecture/InfoPrac2004/9th/kenskau"

できたら、入力した数式をD54〜L54にコピーしてください。

▲ ▼

個人点数の計算

課題、テスト、出席回数の３つの評価を使って、個人の点数を計算します。 P1セルに「点数」と入力してください。

▲ ▼

四捨五入

評価の配分は、「課題4割、テスト4割、出席2割」とします。割合はパーセンテージであらわせば、「4割」は「40%」のことです。課題とテストは100点満点で計算していますので、出席回数（最大10回）を10倍すれば課題とテストと釣り合いが取れます。したがって、次のような計算式で点数を求めることができます。

点数 = 課題の点×課題の割合 + テストの点×テストの割合 + 出席回数×10×出席の割合

すると、P2セルの内容は「M2*0.4+N2*0.4+O2*10*0.2」となりますが、このままでは小数点以下の数字がでてしまいます。そこで小数点以下を四捨五入することにします。それには、ROUND関数を使います。

ROUND（指定された桁で、四捨五入する）
- 書式 : ROUND(数値, 桁数)
- 引数 : 数値 : 四捨五入する数値
- 引数 : 桁数 : 四捨五入する桁数
  - 0か省略なら、最も近い整数
  - 正の整数なら、指定された小数点以下の桁になるように四捨五入
  - 負の整数なら、指定された整数部分の桁（1の位が0, 10の位が1）で四捨五入

P2セルをクリックしてアクティブにします。
「関数の挿入」ボタンをクリックします。
利用する関数を選びます。
利用したい関数の分類を選んだあと、関数名を選びます。「関数の分類」から「すべて表示」か「数学/三角」を選び、「関数名」から「ROUND」を選びます。「OK」ボタンをクリックします。
次に、関数に与える引数を設定します。
1. 「数値」に「M2*0.4+N2*0.4+O2*10*0.2」と入力します。
2. 「桁数」に「0」と入力します。
3. 「OK」ボタンをクリックします。
P2セルに計算式が設定されます。

▲ ▼

条件判定

たいていの授業では、成績をつける上での条件があります（あるものなのです）。たとえば、次のようなものです。

小テストの点数が○点以下の場合は、期末テストは△点以上ないとダメ
○回以上欠席したら、期末のテストは受けられない

ここでは「5回以上欠席したものは点数は0とする」という条件をつけることにしましょう。 関数のネストを使えば、点数を求めることができます。先ほどのROUND関数と組み合わせて、条件判定のIF関数を使いましょう。

IF（指定された条件（論理式）が合っているか（真）違っているか（偽）を判断し、真と偽のそれぞれの場合の結果を返す）
- 書式 : IF(論理式, 真の場合, 偽の場合)
- 引数 : 論理式 : 評価する条件（比較演算子を使う）
- 引数 : 真の場合 : 論理式の結果が真(TRUE)の場合に返される値
- 引数 : 偽の場合 : 論理式の結果が偽(FALSE)の場合に返される値

ここでの条件「5回以上の欠席したものは点数は0」を言い換えれば、次のようになります。

条件：「出席回数が6回以上かどうか」
- 6回以上（真）：「成績を課題、テスト、出席回数から計算する」
- 6回未満（偽）：「0とする」

この条件をIF関数を使うと、次のようになります。

IF(O2>=6, ROUND(M2*0.4+N2*0.4+O2*10*0.2,0), 0)
   -----  --------------------------------  -
   条件  真の場合の結果                    偽の場合の結果

この条件を使って、P2セルを次のように書き換えましょう。

P2セルをクリックして、選択します。
数式バーをクリックして、計算式を編集できる状態にします。
「=IF(O2>6,ROUND(M2*0.4+N2*0.4+O2*10*0.2,0),0)と入力します。
Enterキーを押すと、P2セルに計算式が設定されます。

計算できたら、P2セルの計算式をP3〜P51セルにコピーしてください。

▲ ▼

順位

前回と同じように、成績の順位を求めてみましょう。 Q1セルに「順位」と入力してください。

順位を計算するRANK関数を使います。

RANK（数値が指定された範囲で何番目に位置するかを返す）
- 書式 : RANK(数値, 範囲, 順序)
- 引数 : 数値 : 順位を調べる数値
- 引数 : 範囲 : 調べるセルの範囲
- 引数 : 順序 : 順位の並べ方
  - 0か省略なら、大きいもの順に順位をつける（降順）
  - 0以外の数字なら、小さいもの順に順位をつける（昇順）

先週の内容とこれまでの内容を参考に、必ず絶対参照を使って、 P2〜P51セルに順位を計算する計算式を入力して、点数の順位を高得点から順につけてください。

▲ ▼

課題、テストの結果分析

成績を分析するのに必要な数字は、ほぼ出揃ったので、次は成績の分析をします。 T列に、次の図のように、T1〜T24までのせるに文字を入力してください。

#ref(): File not found: "excel10.png" at page "Lecture/InfoPrac2004/9th/kenskau"

▲ ▼

個数、平均、最大・最小

まず次のような計算をしてください。先週の内容とこれまでの内容を参考にしてください。

U1セルは、受講者数を計算してください。COUNTA関数を使って、範囲はB列を使ってください。
U3セルは、第1回から第10回までの平均の出席率を計算してください。AVERAGE関数を使います。
U5〜U10までは点数についてです。
- U6セルは、点数全体の平均を計算してください。AVERAGE関数を使います。
- U7セルは、点数全体の最高点を計算してください。MAX関数を使います。
- U8セルは、点数全体の最低点を計算してください。MIN関数を使います。
U12〜U17までは課題についてです。
- U13セルは、課題全体の平均を計算してください。
- U14セルは、課題全体の最高点を計算してください。
- U15セルは、課題全体の最低点を計算してください。
U19〜U24まではテストについてです。
- U20セルは、テスト全体の平均を計算してください。
- U21セルは、テスト全体の最高点を計算してください。
- U22セルは、テスト全体の最低点を計算してください。

▲ ▼

中央値

次に、新しい関数を紹介します。 MEDIANという関数です。

MEDIAN（指定された範囲の中央値を返す）
- 書式 : MEDIAN(数値1, 数値2, ...)
- 引数 : 数値1, 数値2, ... ：中央値を計算するセルの範囲

「中央値」とは、順番に並べられた数の列（数列）のちょうど中央に位置する値のことです。たとえば、次のような数列があったとします。

1,2,3,5,8

真ん中の数が中央値になるので、「3」が中央値になります。では、次のような数列ではどうなるでしょうか。

1,2,3,5,8,13,21,34

今度は数値の数が8個で偶数になってしまい、中央の数字がありません。このような場合は、真ん中の2つの数を足して2で割ったものが中央値となります。つまり、「8」と「13」を足して2で割った「10.5」が中央値です。

次のようにして、点数全体の中央値を求めてみましょう。

U9セルをクリックしてアクティブにします。
「関数の挿入」ボタンをクリックします。
利用する関数を選びます。
利用したい関数の分類を選んだあと、関数名を選びます。「関数の分類」から「すべて表示」か「統計」を選び、「関数名」から「MEDIAN」を選びます。「OK」ボタンをクリックします。
次に、関数に与える引数を設定します。
1. 「数値1」の入力欄の中を削除します。
2. P2〜P51のセルをマウスをドラッグして範囲指定します。
3. 「数値1」に「P2:P51」と設定されているのを確認できたら、「OK」ボタンをクリックします。

同じようにして、U16に課題全体の中央値を、U23にテスト全体の中央値を計算してください。

▲ ▼

最頻値

また、新しい関数を紹介します。 MODEという関数です。

MODE（指定された範囲でもっとも頻出する値を返す）
- 書式 : MODE(数値1, 数値2, ...)
- 引数 : 数値1, 数値2, ... ：中央値を計算するセルの範囲

「最頻値」とは、数列のなかで、一番よく出てくる数値のことです。たとえば、次のような数列があるとします。

1, 1, 2, 2, 3, 3, 3, 3, 4, 5, 5

このような数列の最頻値は、4つ登場している「3」になります。

次のようにして、点数全体の最頻値を求めてみましょう。

U10セルをクリックしてアクティブにします。
「関数の挿入」ボタンをクリックします。
利用する関数を選びます。
利用したい関数の分類を選んだあと、関数名を選びます。「関数の分類」から「すべて表示」か「統計」を選び、「関数名」から「MODE」を選びます。「OK」ボタンをクリックします。
次に、関数に与える引数を設定します。
1. 「数値1」の入力欄の中を削除します。
2. P2〜P51のセルをマウスをドラッグして範囲指定します。
3. 「数値1」に「P2:P51」と設定されているのを確認できたら、「OK」ボタンをクリックします。

同じようにして、U17に課題全体の最頻値を、U24にテスト全体の最頻値を計算してください。

▲ ▼

個人成績の評価

成績の分析のしかたには、点数だけではなく、点数をもとにした「評価」があります。いわゆる「優・良・可・不可」というものです。次の図のように、W1〜X6のセルに、評価の表を入力してください。

#ref(): File not found: "excel11.png" at page "Lecture/InfoPrac2004/9th/kenskau"

▲ ▼

個人の評価

入力した評価の表を元に、個人の評価を調べてみましょう。つまり「評価の表の中から、個人の点数に最も近い表を探し出す」ということをします。

このように、ある値を使って指定した範囲からデータを取り出すには、VLOOKUP関数を使います。

VLOOKUP（指定された範囲の左端の列の値を検索し、見つかった行と同じ行の値を返す）
- 書式 : VLOOKUP(検索値, 範囲, 列番号, 検索の型)
- 引数 : 検索値：範囲の左端列から検索したい値
- 引数 : 範囲：検索する範囲
- 引数 : 列番号：見つかった場合に返したい値の、範囲の左端から数えた列の数
- 引数 : 検索の型
  - TRUE : 検索値に近い値を検索する
  - FALSE : 検索値と完全に一致するものだけを検索する

検索の型に注目して、説明します。次のような表があるとします。

	A	B	C
1	値	検索範囲(x)	返り値
2	0	0<=x<10	○
3	10	10<=x<20	△
4	20	20<=x<30	□
5	30	30<=x	×

VLOOKUP関数を使って、左端の値から、右端（3列目）の記号を取り出すことを考えます。

「VLOOKUP(20, A1:C4, 3, FALSE)」とすると、「□」が返ってきます。
「VLOOKUP(25, A1:C4, 3, FALSE)」とすると、「#N/A」（エラーの意味）が返ってきます。
「VLOOKUP(20, A1:C4, 3, TRUE)」とすると、「□」が返ってきます。
「VLOOKUP(25, A1:C4, 3, TRUE)」とすると、「□」が返ってきます。
- 検索の型がTRUEのため、検索値25にもっとも近い値を探し出します。
- そのとき、各値の意味が「自分の値以上、下の行の値未満」にかわります。
「VLOOKUP(40, A1:C4, 3, TRUE)」とすると、「×」が返ってきます。
- 一番最後の行では「自分の値以上」という意味になります。

では、まずR1セルに「評価」と入力してください。そして、次のようにして、個人の評価を求めてください。

R2セルをクリックしてアクティブにします。
「関数の挿入」ボタンをクリックします。
利用する関数を選びます。
利用したい関数の分類を選んだあと、関数名を選びます。「関数の分類」から「すべて表示」か「検索/行列」を選び、「関数名」から「VLOOKUP」を選びます。「OK」ボタンをクリックします。
#ref(): File not found: "excel12.png" at page "Lecture/InfoPrac2004/9th/kenskau"
次に、関数に与える引数を設定します。
1. 「検索値」の入力欄をクリックし、P2セルをクリックします。
2. 「範囲」の入力欄をクリックし、評価の表W1〜X6のセルを範囲指定します。
3. 「列番号」の入力欄に、「2」（評価の表の2列目）を入力します。
4. 「検索の型」の入力欄に、「TRUE」（近いものを検索する）を入力します。
5. 「OK」ボタンをクリックします。
  #ref(): File not found: "excel16.png" at page "Lecture/InfoPrac2004/9th/kenskau"

入力できたら、R2セルの計算式をR3〜R51までにコピーしましょう。絶対参照を使わないと、コピーするときに計算範囲がずれてしまうので、必ず絶対参照を使ってください。

▲ ▼

評価ごとの人数

個人の評価ができたので、評価ごとの人数を集計します。 T26〜U31セルに、次の図のように入力してください。

#ref(): File not found: "excel13.png" at page "Lecture/InfoPrac2004/9th/kenskau"

COUNTIF関数を使って、 U27〜U31セルに、「秀・優・良・可・不可」のそれぞれの人数を計算してください。

▲ ▼

度数分布

次に、点数ごとの分布の状況を調べてみましょう。 10点刻みの分布（度数分布）を計算します。 U33〜U43セルに、次の図のように入力してください。分布の意味は「10点以上、20点未満」という意味で、最後の行だけ「90点以上、100点以下」という意味になります。

#ref(): File not found: "excel14.png" at page "Lecture/InfoPrac2004/9th/kenskau"

もし、入力中に「10月20日」のように表示されてしまったら、セルを右クリックして「セルの書式設定」を選んでください。「表示形式」から「文字列」を選んでおくと、入力したとおりに表示されます。

入力できたら、COUNTIF関数を使って、U34〜U43のセルに分布を計算しましょう。ヒントは次のとおりです。

U34セルは「=COUNTIF(P2:P51,"<10")」で、U43セルは「=COUNTIF(P2:P51,"> =90")」となります。
「10点以上、20点未満」とは、10点以上の人数から、ある計算をすれば求められます。

▲ ▼

検索用シートの作成

ここまで集計・分析したデータを、検索するためのワークシートを作ります。番号を入力すると、名前と成績の情報が表示されるようにします。

メニューバーから「挿入」→「ワークシート」を選んでください。新しくできたワークシートの名前を右クリックして、「名前の変更」を選んでください。「成績の検索」と入力して、Enterキーを押してください。

▲ ▼

表の作成

次の図を参考にして、自由に表を作成してください。解説すると、塗りつぶしが黄色い部分が入力欄で、青い部分が検索結果を表示するところです。

#ref(): File not found: "excel15.png" at page "Lecture/InfoPrac2004/9th/kenskau"

▲ ▼

検索のしかた

入力された番号をもとに、別のワークシートの表から、番号に対応した値を検索します。このような場合にも、今回紹介したVLOOKUP関数を使います。

VLOOKUP関数を使って、値を検索してください。計算式を考える上でのポイントは次のとおりです。

検索値は、検索シートにある番号の入力欄です。
検索する範囲は、別シート（成績表）の個人の成績の部分です。
関数の引数の設定で、別シートのセルを指定するときには、シートのタブをクリックすればいいだけです。元のシートにもどるのも同じ操作です。
正確な検索をするので、検索の型の値は「FALSE」です。

▲ ▼

空白の処理

ここまでくれば、あと一歩で完成です。

もし番号に何も入力されていないと、名前などの検索結果には「#N/A」というエラー値が表示されてしまいます。この部分を改良しましょう。

「番号に何も入力されていなければ、結果の部分は空白を表示する」という機能を付け加えましょう。次のヒントを参考にして、検索のシートを完成してください。

新たに使う関数はIF関数です。
関数のネストを使います。
Excelで空白とは「""」（ダブルクォーテーション２つ）になります。

ここまでできたら、課題の説明にすすんでください。

KAWANO's PukiWiki Plus! - Lecture/InfoPrac2004/9th/kenskau のバックアップ(No.1)