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

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

エクセルで成績表を5段階評価する方法

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

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

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

エクセルで5段階評価したい成績表
エクセルで5段階評価したい成績表

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

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

成績表を5段階評価する3つの方法

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

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

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

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

IFS関数を用いた方法

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

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

利用する関数

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

成績表をIFS関数で5段階評価
成績表をIFS関数で5段階評価

IF関数を用いた方法

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

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

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

成績表をIF関数で5段階評価
成績表をIF関数で5段階評価

VLOOKUP関数を用いた方法

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

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

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

成績表をVLOOKUP関数で5段階評価
成績表をVLOOKUP関数で5段階評価

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

成績表の元データを準備

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

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

成績表にAVERAGE関数を挿入
成績表にAVERAGE関数を挿入

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

成績表に5段階評価の基準を設定
成績表に5段階評価の基準を設定

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

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

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

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

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

IFS関数とは

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

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

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

IFS関数の条件設定のイメージ図
IFS関数の条件設定のイメージ図

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

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

手順1.IFS関数を挿入して第1条件を設定

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

=IFS(F3<=40,1,

IFS関数に第1条件を設定
IFS関数に第1条件を設定

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

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

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

IFS関数に第2条件を設定
IFS関数に第2条件を設定

手順3.IFS関数を挿入して第3、4、5条件を設定

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

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

IFS関数に第3~5条件を設定
IFS関数に第3~5条件を設定

手順4.IFS関数をコピペ

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

IFS関数をコピペ
IFS関数をコピペ

手順5.5段階評価が完成

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

IFS関数で成績表を5段階評価
IFS関数で成績表を5段階評価

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

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

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

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

IF関数の複数条件を紹介

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

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

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

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

IF関数を複数条件で利用する方法
IF関数を複数条件で利用する方法

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

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

手順1.IF関数を挿入して第1条件を設定

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

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

IF関数に第1条件を設定
IF関数に第1条件を設定

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

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

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

2つ目のIF関数に第2条件を設定
2つ目のIF関数に第2条件を設定

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

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

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

3つ目のIF関数に第3条件を設定
3つ目のIF関数に第3条件を設定

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

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

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

4つ目のIF関数に第4条件を設定
4つ目のIF関数に第4条件を設定

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

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

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

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

手順6.IF関数をコピペ

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

IF関数をコピペ
IF関数をコピペ

手順7.5段階評価が完成

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

IF関数で成績表を5段階評価
IF関数で成績表を5段階評価

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

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

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

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

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

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

=VLOOKUP(検索値、検索範囲、列番号、[検索方法])

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

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

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

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

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

VLOOKUP関数で『TRUE』を使った場合
VLOOKUP関数で『TRUE』を使った場合

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

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

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

手順1.VLOOKUP関数用のキー列を追加

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

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

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

VLOOKUP関数用のキー列を挿入
VLOOKUP関数用のキー列を挿入

手順2.平均点を昇順で並べ替え

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

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

VLOOKUP関数と引数[TRUE]で挿入
VLOOKUP関数と引数[TRUE]で挿入

手順3.VLOOKUP関数をコピペ

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

VLOOKUP関数をコピペ
VLOOKUP関数をコピペ

手順4.5段階評価が完成

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

VLOOKUP関数で成績表を5段階評価
VLOOKUP関数で成績表を5段階評価

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

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

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