７． 評定計算
７－１ 評価点と評定
中学・高校の成績は、最終的に「評定」という形で公簿に残されます。自治体に よって異なりますが、５段階評価または１０段階評価で評価されます。中学では生徒が直接受け取る通知表も評定の形ですが、高校では、多くの場合、 通知表には100点法の「評価点」が記載され、それが換算表で１～５、または１～10 の評定に読み替えられます。
評価点から評定へ換算する時、例えば20点刻みのような単純な換算であれば、 数式も比較的単純なものになります。
|
|
=CEILING(値 )
小数点以下を切り上げ、数値を丸めます
=TRUNC(値 )
小数点以下を切り捨て、数値を丸めます
=INT(値 )
小数点以下を切り下げて、数値を整数化します
=ROUND(値, 桁 )
四捨五入して、数値を小数点下指定桁に丸めます
左表の変換式 =IF(検>0, CEILING(検/20), 1)
右表の変換式 =IF(検<100, TRUNC(検/20)+1, 5)
左表の変換：評価点＝0→CEILNG(0/20)=0→最低値１を下回る
右表の変換：評価点＝100→TRUNC(100/20)+1=6→最高値５を上回る
を防ぐためです。
しかし私の勤める群馬県の場合、いくつかの高校で下記のような換算表が採用 されています。他の都道府県でも同じような感じなのではないでしょうか。
| 評価点 | 評定 |
|---|---|
| 85 ～ 100 | ５ |
| 70 ～ 84 | ４ |
| 45 ～ 69 | ３ |
| 30 ～ 39 | ２ |
| 0 ～ 29 | １ |
IF関数を使えばなんとか数式化できますが、こんな感じになります。
（「検」は、検査値が格納されたセル番地とします。）
=IF(検>=85, 5, IF(検>=70, 4, IF(検>=45, 3, IF(検>=30, 2, 1))))
７－２ VLOOKUP関数
セル／数式の値が○○だったら、△△という値を返す、という関数があります。=VLOOKUP(検査値, 範囲, 参照列, 検索型 )
範囲で指定された対応表の最も左側の列から 検査値 を検索し、検査値があった行の 参照列番目の列の値を、関数の戻り値として返します。
また、検索に際して、文字の大文字小文字は区別されません。
範囲の最も左列（検索列）の読み取り方は、 検索型の値がTRUEの場合とFALSE の場合とで大きく異なります。
検索型＝FALSE では、検査値と完全に一致する値がない 場合は
検索型＝(省略またはTRUE) では、 「検査値未満で最も大きな値」の行にヒットし、その行の 参照列番目の列の値が返されます。ただしこのモードでは、 変換表の最左列（検索列）の値はあらかじめ昇順に並べられていなければなりません。
（例） 変換表がセル範囲 A2:B7 にあるとします。
| A | B | |
|---|---|---|
| 1 | 評価点 | 評定 |
| 2 | 0 | 1 |
| 3 | 30 | 2 |
| 4 | 45 | 3 |
| 5 | 70 | 4 |
| 6 | 85 | 5 |
| 7 | 100.1 | #N/A |
C11 に =VLOOKUP(B11, $A$2:$B$7, 2) が記述されています。
C12 に =VLOOKUP(B12, $A$2:$B$7, 2) が記述されています。
C13 に =VLOOKUP(B13, $A$2:$B$7, 2) が記述されています。
C14 に =VLOOKUP(B14, $A$2:$B$7, 2) が記述されています。
C15 に =VLOOKUP(B15, $A$2:$B$7, 2) が記述されています。
C16 に =VLOOKUP(B16, $A$2:$B$7, 2) が記述されています。
| A | B | C | |
|---|---|---|---|
| 10 | 氏名 | 評価点 | 評定 |
| 11 | 生徒氏名１ | 65 | 3 |
| 12 | 生徒氏名２ | 23 | 1 |
| 13 | 生徒氏名３ | 90 | 5 |
| 14 | 生徒氏名４ | 69 | 3 |
| 15 | 生徒氏名５ | -1 | #N/A |
| 16 | 生徒氏名６ | 101 | #N/A |
ところで、上の変換表にはちょっとした落とし穴があります。
例えば、検査値が 29.99999 である場合、30 に達しないため評定２とはなりません。 しかし、列幅が６より小さく指定されているような時、29.9999 は表示上 四捨五入され、見かけ上だけなのですが 30 に見えることがあります。
すると、下表のような現象が起きます。
B25 には、演算結果が30.25になるような数式が記述されています。
B26 には、演算結果が29.9999になるような数式が記述されています。
C25 に =VLOOKUP(B25, $A$2:$B$7, 2) が記述されています。
C26 に =VLOOKUP(B26, $A$2:$B$7, 2) が記述されています。
| A | B | C | |
|---|---|---|---|
| 25 | 生徒氏名15 | 30.25 | 2 |
| 26 | 生徒氏名26 | 30 | 1 |
同様の理由から、100.1以上を
評定計算処理にVLOOKUP関数を利用し、しかも
７－３ 配列定数
VLOOKUP関数の第２引数は「セル範囲」ではなく「範囲」と書かれています。 第２引数は、必ずしもワークシート上のセル範囲でなくてもよいのです。検索表を、「配列定数」という表現形式で記述することが可能です。
配列定数は、全体を ｛ ｝で囲み、改行をセミコロン（；）、 １行の中の列の区切りをカンマ（，）で表現します。 「定数」なので、残念ながら数式やセル参照を書き込むことはできません。
上記の検索表を配列形式で表現すると下記のようになります。
| { 0, 1; 30, 2; 45, 3; 70, 4; 85, 5; 100.1, "#N/A" } |
=VLOOKUP(検索値, {0,1;30,2;45,3;70,4;85,5;100.1,"#N/A"}, 2)
