【エクセル】成績表を点数別に自動で５段階評価する方法。複数条件の対処方法

学校のテストなどの成績表を点数に応じて、”１～５”の５段階評価したり”A～E”の５段階評価することがあります。

データ量が少なければ点数を見ながら評価することが可能ですが、人数が多い時はExcel(エクセル)の関数を使うと自動で評価できるので便利です。

今回は、下の成績表から平均点が『40点以下であれば”１”』『50点以下であれば”2”』『65点以下であれば”3”』『80点以下であれば”4”』『80点より上であれば”5”』と５段階で自動で評価する方法を3つ紹介します。

評価する方法は、①IFS関数を用いた方法、➁IF関数を用いる方法、③VLOOKUP関数を用いる方法の３つを紹介します。

下の見出しで関数などの概要を紹介するので、お好みの方法をまずは検討してから利用してください。

成績表を５段階評価する３つの方法

①IFS関数(イフス)、➁IF関数(イフ)、③VLOOKUP関数(ブイルックアップ)のうち、どれか１つの関数を用いることで５段階評価することが可能です。

関数の引数の設定のしやすさが異なるので、私は①→➁→③の順番でおすすめです。

３つの手法を簡単に紹介するので、どの方法が自分で使いやすいか検討してみてください。

関数の詳しい使い方は後の見出しで紹介しているので、この見出しはざ～～っと見て、使いやすそうや手法を選択してください。

私

IFS関数を用いた方法

IFS関数(イフス)は、Excel2016以降のバージョンであれば利用出来る超便利関数です。

IF関数の『IF』と、複数の『S』からも分かるとおり、IF関数を複数の条件で設定できるので、今回のような５段階評価に最も適した関数と言えます。

⇩利用する関数

=IFS(F3<=40,1,F3<=50,2,F3<=65,3,F3<=80,4,F3>80,5)

IF関数を用いた方法

ExcelのバージョンがExcel2016より古い方は１つ目に紹介したIFS関数が利用出来ないので、IF関数を用いた方法を利用しましょう。

IF関数の中にIF関数を複数用いて、５段階評価を実現する方法です。

=IF(F3<=40,"1",IF(F3<=50,"2",IF(F3<=65,"3",IF(F3<=80,"4","5"))))

VLOOKUP関数を用いた方法

VLOOKUP関数のちょっとした応用テクニックを利用することでも５段階評価が可能です。

VLOOKUP関数の4つ目の引数は、”FALSE”を利用することが一般的ですが、”TRUE”を利用します。

=VLOOKUP(F3,$J$2:$K$7,2,TRUE)

VLOOKUP関数を使った方法は、関数の引数を設定しやすいですが、VLOOKUP関数を利用するために評価用の１列を追加する必要があります。また、評価用の列を昇順で並べ替え必要があります。

成績表の元データを準備

まずは成績表をエクセルに纏めましょう。

成績をまとめたら、５段階評価したい点数を平均値にするのであれば、以下のようにAVERAGE関数で平均値を算出します。

続いて、成績表の近くに５段階評価の評価基準を入力します。今回は、平均点が『40点以下であれば”１”』『50点以下であれば”2”』『65点以下であれば”3”』『80点以下であれば”4”』『80点より上であれば”5”』の５段階評価を設定します。

IFS関数を用いて成績表を評価する方法

５段階評価する１つ目の方法は、IFS関数(イフス)を用いた方法です。

ただし、IFS関数はExcel2016、Excel2019、Excel2021、Office365は利用出来ますが、一部のExcel2016やそれ以前のバージョンでは使うことが出来ません。

IF関数の『IF』と複数の『S』の単語のとおり、IFS関数は、1つまたは複数の条件が満たされているかどうかを判定し、最初に条件を満たす場合の値を返します。

IF関数を複数条件で利用できる進化版のIF関数ということです。

私

IFS関数とは

IFS関数の構文は以下です。引数「論理式」と引数「真の場合(返す値)」を１セットとして、計１２７セットの複数条件を設定できます。

=IFS(論理式1, 真の場合1, 論理式2, 真の場合2,論理式3, 真の場合3, ..., ,論理式127, 真の場合127）

IFS関数のイメージ図は下のとおりです。１つ目の条件で「40以下かどうか」、２つ目の条件で「50以下かどうか」と条件を設定していき、５段階評価の「１～５」の値を返すように引数を設定していきます。

IFS関数で成績表を５段階評価する手順

それでは、IFS関数を用いて５段階評価してみましょう。

手順１．IFS関数を挿入して第１条件を設定

評価したいセルにIFS関数を挿入して、第１条件の引数である”40点以下であれば１”を設定します。

=IFS(F3<=40,1,

手順2．IFS関数を挿入して第2条件を設定

評価したいセルにIFS関数を挿入して、第2条件の引数である”50点以下であれば2”を設定します。

=IFS(F3<=40,1,F3<=50,2,

手順３．IFS関数を挿入して第３、４、５条件を設定

同様の手順で、第３～５条件の引数で、”65点以下であれば3”と”80点以下であれば4”と”80点より大きければ5”を設定し、ENTERキーで関数を確定します。

=IFS(F3<=40,1,F3<=50,2,F3<=65,3,F3<=80,4,F3>80,5

手順４．IFS関数をコピペ

IFS関数のセルに評価”１”が入力されました。このセルを残りのセルにコピペします。

手順５．５段階評価が完成

下の図のように、”１～5”の5段階評価が完成します。

IF関数やIFS関数の複数条件の設定方法は以下のリンク記事でも紹介していますので、興味がある方はぜひ参考にしてください。

IF関数を用いて成績表を評価する方法

５段階評価する2つ目の方法は、IF関数(イフ)を用いた方法です。

Excelのバージョンが古くIFS関数を使えない方は、こちらの方法を使ってみて下さい。

IF関数の複数条件を紹介

IF関数を複数の条件で使う方法を紹介します。

IF関数の構文は下のとおりですが、３つ目の引数[値が偽の場合]にIF関数を挿入して条件を設定することで、IFS関数のように複数の条件を設定することが出来ます。

=IF(論理式,[値が真の場合],[値が偽の場合])

下の表のように、IF関数を用いて、テスト結果を”〇””△””×”に３つに評価することが出来ます。

IF関数で成績表を５段階評価する手順

それでは、IF関数を用いて５段階評価してみましょう。

手順１．IF関数を挿入して第１条件を設定

評価したいセルにIF関数を挿入して、第１条件の引数である”40点以下であれば１”を設定します。３つ目の引数[値が偽の場合]は次の見出しで作成します。

=IF(F3<=40,"1",

手順2．２つ目のIF関数を挿入して第2条件を設定

１つ目のIF関数の３つ目の引数[値が偽の場合]に、IF関数を挿入して第2条件の引数である”50点以下であれば2”を設定します。

=IF(F3<=40,"1",IF(F3<=50,"2",

手順3．3つ目のIF関数を挿入して第3条件を設定

2つ目のIF関数の３つ目の引数[値が偽の場合]に、IF関数を挿入して第3条件の引数である”65点以下であれば3”を設定します。

=IF(F3<=40,"1",IF(F3<=50,"2",IF(F3<=65,"3",

手順4．4つ目のIF関数を挿入して第4条件を設定

3つ目のIF関数の３つ目の引数[値が偽の場合]に、IF関数を挿入して第4条件の引数である”80点以下であれば4”を設定します。

=IF(F3<=40,"1",IF(F3<=50,"2",IF(F3<=65,"3",IF(F3<=80,"4",

手順５．4つ目のIF関数に[値が偽の場合]を設定

4つ目のIF関数の３つ目の引数[値が偽の場合]に、”80点より大きければ5”を設定します。IF関数を４つ挿入しているので、”）”を４つ挿入して関数をENTERで確定させます。

=IF(F3<=40,"1",IF(F3<=50,"2",IF(F3<=65,"3",IF(F3<=80,"4","5"))))

手順６．IF関数をコピペ

IF関数のセルに評価”１”が入力されました。このセルを残りのセルにコピペします。

手順７．５段階評価が完成

下の図のように、”１～5”の5段階評価が完成します。

IF関数の複数条件の設定方法は以下のリンク記事でも紹介していますので、興味がある方はぜひ参考にしてください。

VLOOKUP関数を用いて成績表を評価する方法

５段階評価する3つ目の方法は、VLOOKUP関数(ブイルックアップ)を用いた方法です。

VLOOKUP関数を用いた方法を用いるには、第４引数の「検索方法」を「FALSE」という完全一致を使うのではなく、「TRUE」という近似値を返す引数を利用します。

VLOOKUP関数の『TRUE』の使い方を紹介

VLOOKUP関数は表から条件に合った値を縦方向に検索する関数で、構文は以下のとおりです。４つの引数を指定する必要があります。

＝VLOOKUP(検索値、検索範囲、列番号、[検索方法]）

4つ目の引数[検索方法]を「FALSE」を指定した場合、「検索値」と完全一致する値が検索され、「検索値」が見つからない場合は、エラー値「#N/A」が表示されます。

一方、「TRUE」を指定した場合、「検索値」が見つからない場合には、「検索値」未満で最も大きい近似値が表示されます。

「TRUE」を使った実例を１つ紹介します。

下の商品リストから、商品番号『２２』の商品名を抽出するために、G４セルに、『=VLOOKUP(F4,B4:D8,2,TRUE)』を挿入します。

商品番号『22』が表内に存在しないので、「TRUE」を指定した場合は、『22』未満で最も大きな値『18』が採用されます。そのため、商品番号『18』の商品名『ヘアワックス』が結果として返されます。

VLOOKUP関数の基本的な使い方は、以下のリンク記事を参考にしてください。

VLOOKUP関数で成績表を５段階評価する手順

それでは、IF関数を用いて５段階評価してみましょう。

手順１．VLOOKUP関数用のキー列を追加

下の図のように、５段階評価の基準の左隣りの列に、VLOOKUP関数用のキー列を挿入します。

平均点が『40点以下であれば”0.01”』『50点以下であれば”40+0.01”』『65点以下であれば”50+0.01”』『80点以下であれば”65+0.01”』『80点より上であれば”80+0.01”』の足し算の結果を入力します。

また評価用キー列の数値の並びは昇順になるように並べ替えします。

手順２．平均点を昇順で並べ替え

評価したいセルに以下のVLOOKUP関数を挿入します。４つ目の引数「検索方法」は「TRUE」を指定します。

=VLOOKUP(F3,$J$2:$K$7,2,TRUE)

手順３．VLOOKUP関数をコピペ

VLOOKUP関数のセルに評価”１”が入力されました。このセルを残りのセルにコピペします。

手順４．５段階評価が完成

下の図のように、”１～5”の5段階評価が完成します。

成績表をアルファベットで評価することも可能

今回は、成績表の平均点に応じて”１～５”の５段階評価する方法を紹介しました。もちろん、引数の設定を変えることで、３段階評価や４段階評価や”A～E”のアルファベットの評価も可能です。

方法は、IFS関数、IF関数、VLOOKUP関数を用いた３つの手法を紹介しました。利用しやすい方法をご自身で検討してみて下さい。