成績処理ファイル作成　EXCELの超基本を押さえる！※全文無料

定期考査と平常点から学期成績・学年末成績・５段階評定を計算するEXCELファイルの作成を紹介します。

目的は次の２つです。

①　処理を自動化しておくことで、楽でミスなく作業を行う。
②　関数に慣れ、EXCELの作成を自力でできるようになる。

今回使用する関数は次の５つです。

=ROUND　  指定した桁数で四捨五入します。
=AVERAGE　平均を求めます。
=RANK.EQ　ランキングをつけます。
=if　　　　  条件分岐を行えます。
=COUNTIF　条件にあったものの個数を数えます。

1．レイアウトを決める。

まず、EXCEL全体のレイアウトを決めます。
入力するところと、自動で計算されるところを区別します。

入力する項目
番号、氏名、定期考査素点（１定から５定）、平常点（１から３学期）
自動計算される項目
学期評価、学年末評価、５段階評価

2．評価を自動計算する。

規定として
１定40％＋２定40％＋１学期平常点20％　⇒　１学期評価
３定40％＋４定40％＋２学期平常点20％　⇒　２学期評価
５定80％＋３学期平常点20％　　　　　　⇒　３学期評価
１学期、２学期、３学期評価の相加平均　⇒　学年末評価
という設定で作成していきます。

1学期評価のセル（K2）に
=ROUND(C2*0.4+D2*0.4+H2,0)
と打ち込みます。
C2のセルの値の0.4倍とD2のセルの値の0.4倍とH2のセルの値を足し合わせます。
=ROUND(数値,0) で数値の小数第一位を四捨五入して整数の値にできます。

関数を下までコピーします。
セルの右下にカーソルを合わせ＋マークになったら、ダブルクリックをします。
すると、下まで関数がコピーされます。
一番下までコピーが張り付いているか確認しましょう。

２学期、３学期も同じように作成します。
学年末は、１学期、２学期、３学期の平均です。
=AVERAGE(平均を取る範囲) を使用します。

3．評定を自動計算する

規定として、5を7％、4を24％、3を38％、2を24％、1を7％程度とします。

評定の付け方は次の手順で行います。
①　学年末評価で順位をつける。
②　上位7％を5にする。
③　上位7％＋24％＝31％を4にする。
④　上位7％＋24％＋38％＝69％を3にする。
⑤　上位7％＋24％＋38％＋24％＝93％を2にする。
⑥　残りを1にする。

3－1．学年末評価で順位をつける

=RANK.EQ（順位を調べたい数値，順位を計算する範囲）を使います。
順位を計算する範囲は列で指定するとズレを気にすることがなく楽です。
順位のセル（O2）に
=RANK.EQ(N2,N:N)
と打ち込み、下へコピーします。

②から⑥のために、実際に何位までを5にすればよいかを計算します。
割合を入力し、割合の累計を計算します。

3－2．何位までをどの評定にするかを計算します。

クラスの人数×割合累計×0.01を四捨五入することで求められます。
四捨五入には先程も使った
=ROUND(数値,0)
を使用します。

3－3．評定を計算する。

それでは評定を計算します。評定の計算には
=if（条件，条件を満たすとき，条件を満たさないとき）
を使用します。

if関数を使うときのコツは、
「これこれなら～！そうじゃなかったら～」と唱えながらつくることです。

複雑な処理を行う関数を作るときのコツは、簡単なものを作る。そこから徐々に発展させることです。

まずは、「3位までなら5、そうじゃなかったら”5じゃないよ！”と表示」するようにします。
評定のセル（P2)に
=if(O2<=3,5,"5じゃないよ")
と入力します。

次に、”5じゃないよ”のところを「15位までなら4、そうじゃなかったら”5,4じゃないよ”と表示」にします。
つまり、if(O2<=15,4,"5,4じゃないよ") を先程のif(O2<=3,5,"5じゃないよ") の”5じゃないよ”の部分に当てはめます。
よって、評定のセル（P2）に
=if(O2<=3,5,if(O2<=15,4,"5,4じゃないよ"))
と入力します。
これで「3位までなら5、そうじゃなかったら（ここで一呼吸おいて）、15位までなら4、そうじゃなかったら”5,4じゃないよ”と表示」となります。

同じ要領で if 関数の中に if 関数を入れ込んでいき、評定をつけます。
評定のセル（P2)に
=IF(O2<=3,5,IF(O2<=15,4,IF(O2<=34,3,IF(O2<=46,2,1))))
と入力します。

これでとりあえずは、評定をつける関数が出来上がりました。
ここからさらに改良していきます。
簡単なところから徐々に発展させていくのです。

3－4．関数を改良する

評定の割合や、クラスの人数が変化しても関数を変更しなくていいようにします。
=IF(O2<=3,5,IF(O2<=15,4,IF(O2<=34,3,IF(O2<=46,2,1))))

3－2で計算した結果を関数の中に引用します。
=IF(O2<=U3,5,IF(O2<=U4,4,IF(O2<=U5,3,IF(O2<=U6,2,1))))

この関数を下にコピーすると

参照のセルがズレてしまいます。
=IF(O11<=U12,5,IF(O11<=U13,4,IF(O11<=U14,3,IF(O11<=U15,2,1))))

こうならないために、絶対参照を行います。

絶対参照は、関数をコピーしても参照する場所を動かさないという指示です。
関数をコピーしたのに合わせて、参照場所をズラすのを相対参照といいます。

列を動かしたくない場合は、文字の前に$をつけます（例：$U3）。
行を動かしたくない場合は、数字の前に$をつけます（例：U$3）。
列・行ともに動かしたくない場合は、文字・数両方の前に$をつけます（例：$U$3）

今回はセルをひとつに固定してしまいたいので、文字・数両方の前に$をつけます。
=IF(O2<=$U$3,5,IF(O2<=$U$4,4,IF(O2<=$U$5,3,IF(O2<=$U$6,2,1))))
補足：コピーは列を動かさずに行うので、列の固定は不要（U$3）ですが、セルを完全に動かさないというイメージを持つために、両方共固定しました。

これで、下にコピーしても参照場所は変わりません。

3－5．実際に付いた評定の個数を数える

最後に、実際に付いた評定の個数を数えます。

今回の評定の計算方法ですと、評定の切り替わり目で同点の生徒がいた場合、想定より多くの生徒に同じ評定をつけてしまうことがあります。
ですので、実際に何人が5で、何人が4なのか、調べておきましょう。

=COUNTIF（範囲，検索条件）を使います。

実個数を数えるセル（V3)に
=COUNTIF(P:P,R3)
と入力します。
P列の中で、R3（数値の5）がいくつあるかを数えることができます。

実割合を計算するセル（W3)に
=ROUND((V3/$U$2)*100,0)
と入力します。
実個数（V3）をクラスの人数（U2)で割って割合を計算しています。
ROUND関数で四捨五入もしました。

この計算結果を見ながら、評定の微調整を行います。
今回の方法では、順位に対して機械的に評定をつけていますので、1点差で5と4の違いがあるなどします。
実際の評定の切り替わり目をよく観察し、公正な評定をすることを心がけましょう。

以上で、評価・評定を計算する成績処理EXCELファイルの作成が完成しました。

※　本文は以上です。「記事を購入」での応援を歓迎します。応援いただけましたら今回作成したEXCELファイルをプレゼントいたします。