エクセルで使える成績管理表（成績表）の無料テンプレートです。出席番号・名前・5教科のテストの点数を入れると、合計・平均・順位が自動で計算されます。表の上には、教科ごとの平均点・最高点・最低点が出て、平均点より低い点には色が付きます。
学級やクラブのテストの点数管理のほか、教科の名前を書きかえれば、自分の定期テストの記録にも使えます。この記事では、テンプレートの使い方と、中に入っている式の仕組みを、実際の Excel の画面で説明します。
成績管理表テンプレートの中身
シートは「成績管理表」の1枚です。3行目が見出し、4〜6行目が教科ごとの平均点・最高点・最低点、7〜26行目の20行が1人ずつの点数です。薄い黄色のセルが、手で入れる所です。
| 場所 | 項目 | 入力・式 |
|---|---|---|
| A・B 列 | 番号・名前 | 手で入力 |
| C〜G 列 | 国語・数学・英語・理科・社会 | 点数を手で入力 |
| H 列 | 合計 | =IF(COUNT(C7:G7)=0,"",SUM(C7:G7)) |
| I 列 | 平均 | =IF(COUNT(C7:G7)=0,"",AVERAGE(C7:G7)) |
| J 列 | 順位 | =IF(H7="","",RANK(H7,$H$7:$H$26)) |
| 4行目 | 平均点 | =IF(COUNT(C$7:C$26)=0,"",AVERAGE(C$7:C$26)) |
| 5行目 | 最高点 | =IF(COUNT(C$7:C$26)=0,"",MAX(C$7:C$26)) |
| 6行目 | 最低点 | =IF(COUNT(C$7:C$26)=0,"",MIN(C$7:C$26)) |
| C7:G26 | 平均点より低い点の色 | 条件付き書式 =AND(ISNUMBER(C7),C7<C$4) |
成績管理表テンプレートの使い方
- 7〜16行目の A〜G 列に入っている入力例を消します（H〜J 列と 4〜6行目は式なので消しません）。
- A 列に番号、B 列に名前を入れます。
- C〜G 列に5教科の点数を入れます。1人分を入れるたびに、合計・平均・順位が出ます。
- 4〜6行目で、教科ごとの平均点・最高点・最低点を確かめます。色が付いた点は、その教科の平均点より低い点です。
入力例は 10人分です。合計がいちばん高いのは石川の 450 点（平均 90.0）で 1位、いちばん低いのは上田の 313 点で 10位です。
1. 合計・平均を出す（SUM・AVERAGE）
合計は SUM 関数、平均は AVERAGE 関数です。H7 と I7 には次の式が入っていて、26行目まで同じ形でコピーしてあります。
=IF(COUNT(C7:G7)=0,"",SUM(C7:G7))=IF(COUNT(C7:G7)=0,"",AVERAGE(C7:G7))1行目の青木は、78 ＋ 65 ＋ 82 ＋ 70 ＋ 88 で合計 383 点、平均は 383 ÷ 5 で 76.6 です。COUNT 関数は数字が入ったセルの数を数える関数で、点数がまだ1つも入っていない行は、IF 関数で合計と平均を空欄にしています。空の行に 0 や #DIV/0! が出ないようにするためです。
AVERAGE 関数は空のセルを数えません。欠席などで点数が空の教科がある人は、点数が入っている教科だけの平均になります（AVERAGE 関数の使い方）。
2. 順位を付ける（RANK）
順位は、合計が大きい順です。J7 には次の式が入っています。
=IF(H7="","",RANK(H7,$H$7:$H$26))「H7 の合計が、H7:H26 の中で大きいほうから何番目か」を出す式です。範囲は、下へコピーしてもずれないように $ で固定しています。入力例では、遠藤と加藤の合計がどちらも 418 点なので、2人とも 3位になりました。同じ順位が2人いるときは次の順位が1つ飛ぶので、その次の青木（383 点）は 5位です。
RANK 関数は、どのバージョンの Excel でも使えます。Excel 2010 以降には同じ結果になる RANK.EQ 関数もあり、=RANK.EQ(H7,$H$7:$H$26) と書きかえても順位は変わりません。順位の付け方のくわしい説明は、順位を付ける方法にあります。
3. 教科ごとの平均点・最高点・最低点と、平均点より低い点の色
4〜6行目は、教科ごとの列を縦に集計しています。国語の平均点（C4）の式は次のとおりです。
=IF(COUNT(C$7:C$26)=0,"",AVERAGE(C$7:C$26))入力例の国語は、平均点が 76.3、最高点が 95、最低点が 55 です。最高点は MAX 関数、最低点は MIN 関数で、AVERAGE のところを書きかえた同じ形の式です。合計の列（H 列）にも同じ式が入っていて、合計の平均は 377.7 点、最高は 450 点、最低は 313 点です。
点数のセル（C7:G26）には、条件付き書式で =AND(ISNUMBER(C7),C7<C$4) という条件を入れています。「数字が入っていて、その列の 4行目の平均点より小さい」ときに、薄い赤の色を付けます。国語なら、76.3 より低い 55・67・73・61・70 の 5 人に色が付きます（平均以上かどうかを判定する方法）。
【実機で検証】入力例の結果を、別の計算と照らす
| 項目 | テンプレートの結果 | 確かめ |
|---|---|---|
| 青木の合計・平均 | 383 点・76.6 | 78 ＋ 65 ＋ 82 ＋ 70 ＋ 88 ＝ 383、383 ÷ 5 |
| 国語の平均点（C4） | 76.3 | 10人の国語の合計 763 ÷ 10 |
| 数学・英語・理科・社会の平均点 | 73.6・75.0・75.5・77.3 | 合計 736・750・755・773 を 10 で割る |
| 合計の平均（H4） | 377.7 | 10人の合計を足した 3,777 ÷ 10 |
| 1位 | 石川（450 点） | 合計の最高点（H5）と同じ |
| 同じ順位 | 遠藤・加藤が 3位 | どちらも 418 点。次の青木は 5位 |
次に、17行目に 11人目（中村・5教科とも 80 点）を足して、変わり方を確かめました。
| 項目 | 10人のとき | 11人目を足したあと | 確かめ |
|---|---|---|---|
| 中村の合計・順位 | — | 400 点・5位 | 450・434・418・418 の次 |
| 青木の順位 | 5位 | 6位 | 383 点は 400 点の次 |
| 国語の平均点 | 76.3 | 76.6 | （763 ＋ 80）÷ 11 ＝ 76.63… |
| 合計の平均 | 377.7 | 379.7 | （3,777 ＋ 400）÷ 11 ＝ 379.72… |
印刷するとき・自分用に変えるとき
- 印刷: 用紙は横向きで、1ページに収まる設定にしてあります。
- 教科を変える: 3行目の見出し（国語〜社会）を書きかえます。式は教科の名前を見ていないので、そのまま動きます。
- 自分の点数の記録に使う: 名前の列に「1学期 中間」「1学期 期末」のようにテストの名前を入れると、テストごとの合計と平均の記録になります。順位の列は、合計が高かったテストの順になります。
- 21人以上にしたいとき: 表の途中の行（たとえば 20行目）をコピーして、その下に「コピーしたセルの挿入」で行を足します。平均点や順位の範囲も自動で広がります。
よくある質問
点数が高い順に並べかえられますか？
7行目から下の、点数が入っている行（入力例なら A7:J16）を選び、「データ」タブの「並べ替え」で、合計の列を「大きい順」にします。4〜6行目の平均点などの行は選ばないようにします。
偏差値も出したいです。
偏差値は、平均点と標準偏差から計算します。式と手順は、偏差値を求める方法で説明しています。このテンプレートの右に列を足して使えます。
テストを受けていない人はどうなりますか？
5教科とも空の行は、合計・平均・順位が空欄になり、平均点や順位の計算にも入りません。0 と入れると 0 点として計算されるので、受けていない教科は空のままにします。
