テスト成績表の作成 Part 2

Part 1 > Part 2

Part 1に引き続き、学校のテスト成績表の作成方法についての秘訣を説明します(^^)
これからご紹介するのは、ちょっと難しいかもしれません。

	A	B	C	D	E	F	G	H	I	J	K
1	生徒番号	名前	性別	国語	数学	英語	理科	社会	合計	平均点	評価
2	0201	伊藤　武	男	39	58	86	50	70	303	60.6
3	0202	井上　勝	男	60	29	88	78	80	335	67.0
4	0203	榎本　幸治	男	70	86	100	92	96	444	88.8
5	0204	太田　博	男	58	65	44	40	56	263	52.6
6	0205	萱島　真	男	88	98	75	68	50	379	75.8
7	0206	木下　宍道	男	60	63	39	55	65	282	56.4
8	0207	久部　明弘	男	98	96	78	88	82	442	88.4
9	0221	綾野　亜希	女	60	58	77	82	80	357	71.4
10	0222	江本　綾子	女	74	38	55	77	40	284	56.8
11	0223	岡田　優利子	女	95	89	99	90	98	471	94.2
12	0224	加藤　祐美	女	78	45	66	62	48	299	59.8
13	0225	河野　恵美子	女	45	80	48	52	60	285	57.0
14	0226	斎藤　美恵	女	90	82	68	95	92	427	85.4
15	0227	篠原　信子	女	48	77	42	50	78	295	59.0
16	クラス平均			68.8	68.9	68.9	69.9	71.1	347.6
17	男子平均
18	女子平均
19	クラス最高			98	98	100	95	98	471
20	男子最高
21	女子最高

性別ごとの平均点を求めたい場合は？
性別ごとの最高点を求めたい場合は？
成績ごとに評価を付け加えたい場合は？

性別ごとの平均点を求めたい場合は？

クラスには、男子と女子がいますね（共学の場合ですけど(^^;）
性別ごとに平均点を求めたい場合は、データベース関数のひとつ、DAVERAGE関数を使います。

しかし、DAVERAGE関数を使う前に、やっておくべきことがあります。
Criteriaの部分を作らなければ前に進まないのです(ｰｰ;)
Criteriaというのは、どのデータを求めるか、ということですね。

まず、空いているセルに下の表のようにデータ入力（仮にM列以降の部分に）してください。

	M	N
1	性別	性別
2	男	女

男子の国語の平均点を求めましょう

はじめに、男子の国語の平均点を求めましょう。
D17にカーソル移動させて、「関数貼り付け」ボタンを押してください。

上の図のように、「関数の分類(C)」には、「データベース」、
「関数名(N)」には、「DAVERAGE」を選択して、OKボタンを押してください。

まず、データベースとなる部分、C1からH15までの範囲をマウスで選択して、F４キーを1度押してください。
（絶対参照にするために必要です！…絶対参照と相対参照についての説明は→こちら）

フィールド欄は、国語を求めますので、国語の見出しセル、D1を指定します。

Criteria部分は、男子のデータを求めるので、先ほど作成した表のM1からM2までを範囲選択して、F4キーを1度押してください。

最後に、OKボタンを押します。すると、67.6点であることが求められましたね？
小数第1位まで表示するためには、書式設定ツールバーのやのボタンで調整してくださいね。

数式の構造をわかりやすく説明すると・・・
=DAVERAGE($C$1:$H$15, D1, $M$1:$M$2)

C1からH15までのデータベースを固定して参照（絶対参照）し、フィールド部分はD1（つまり、国語のフィールド）を指定し、CriteriaはM１からM2までの範囲（男子の性別）を固定して参照するように平均値を求めてください。

ということになります。

その後、他の科目の男子の平均点を求めるように、マウスで数式コピーしてくださいね。

女子の国語の平均点を求めましょう

次に、女子の国語の平均点も求めてみましょう。
D18にカーソル移動させて、先ほどと同様にDAVERAGE関数を使ってみてください。

ただし、Criteriaの部分は女子なので、N１からN2までを範囲選択して、F4キーを1回押してください。

ちなみに、数式は次のようになるはずです。
=DAVERAGE($C$1:$H$15, D1, $N$1:$N$2)

数式の結果は70と求められましたね？
そして、値を小数点第1位まで表示するようにしておいてください。

その後、同様に他の科目の女子の最高点を求めるために数式コピーしてくださいね。

▲Page TOP

性別ごとの最高点を求めたい場合は？

性別ごとに最高点を求めたい場合は、データベース関数のひとつ、DMAX関数を使います。

男子の国語の最高点を求めましょう

はじめに、男子の国語の最高点を求めましょう。
D20にカーソル移動させて、「関数貼り付け」ボタンを押してください。

上の図のように、「関数の分類(C)」には、「データベース」、
「関数名(N)」には、「DMAX」を選択して、OKボタンを押してください。

フィールド欄は、国語を求めますので、国語の見出しセル、D1を指定します。

Criteria部分は、男子のデータを求めるので、先ほど作成した表のM1からM2までを範囲選択して、F4キーを1度押してください。

最後に、OKボタンを押します。すると、98点であることが求められましたね？
(98点をとった久部君はクラスでもトップですからねヾ(^^;）

数式の構造をわかりやすく説明すると・・・
=DMAX($C$1:$H$15, D1, $M$1:$M$2)

C1からH15までのデータベースを固定して参照（絶対参照）し、フィールド部分はD1（つまり、国語のフィールド）を指定し、CriteriaはM１からM2までの範囲（男子の性別）を固定して参照するように最大値を求めてください。

ということになります。

その後、他の科目の男子の最高点を求めるように、マウスで数式コピーしてくださいね。

女子の国語の最高点を求めましょう

次に、女子の国語の最高点も求めてみましょう。
D21にカーソル移動させて、先ほどと同様にDMAX関数を使ってみてください。

ただし、Criteriaの部分は女子なので、N１からN2までを範囲選択して、F4キーを1回押してください。

ちなみに、数式は次のようになるはずです。
=DMAX($C$1:$H$15, D1, $N$1:$N$2)

数式の結果は95と求められましたね？

その後、同様に他の科目の女子の最高点を求めるために数式コピーしてくださいね。

▲Page TOP

成績ごとに評価を付け加えたい場合は？

成績をランク付けするというのは、あまり私としては気が進まないのですが・・・(-_-;)
学校の先生をしていらっしゃる方は、評価をつけなければならない立場だと思いますので・・・
Excelの関数を使って自動的に評価をつける方法を説明します。

まず、評価を求めるためのリストを作成してください。

	M	N
4	点数	評価	→ここにわかりやすいようにタイトルを入力しておきます
5	0	E	0以上20未満の場合はE
6	20	D	20以上40未満の場合はD
7	40	C	40以上60未満の場合はC
8	60	B	60以上80未満の場合はB
9	80	A	80以上の場合はA

その後、VLOOKUP関数を使います(^^)

実は、VLOOKUP関数の使い方は2種類あります。
一致するものを検索、またはその値を満たすものを検索して求める方法があるのです。
前者は「請求書の作成」にてご紹介しています(^_^;)

ここでは、後者の「その値を満たすものをリストから検索して求める」方法を紹介いたします。

まず、K2にカーソルを移動させて、ボタンを押してください。
関数の貼り付け/VLOOKUP
そして、「関数の分類(C)」欄には、「検索/行列」、
「関数名(N)」欄では、一番下の「VLOOKUP」を選択してOKボタンを押してください。

「検索値」は平均点のセル、J2を選択します。
「範囲」は、先ほど作成したM5からN9までの範囲をマウスで選択して、F4キーを1回押します（絶対参照のため）。
「列番号」は、2列目を検索するので「２」と入力します。
「検索の型」は、「その数字を満たすもの（近似値を含めて）」検索するので、「TRUE」と必ず入力して、
最後にOKボタンを押してください。