エクセルはデータ解析・管理を行うツールとして非常に機能が高く、上手く使いこなせると業務を大幅に効率化できるため、その扱いに慣れておくといいです。
ただ機能が充実しているあまり初心者にとっては処理方法がよくわからないことも多いといえます。
例えばエクセルにて点数に応じて10段階に絶対評価や相対評価を行うにはどう処理すればいいのか理解していますか。
ここではエクセルにてあらかじめ決めた評価基準に基づき、点数に応じた10段階評価を行う方法について解説していきます。
エクセルにて10段階評価を行う方法【絶対評価】
それでは以下のサンプルデータを用いてエクセルにて点数に応じた10段階評価を絶対評価にて行う方法について確認していきます。ここでの絶対評価は点数に応じて評価を一意に決めていきます。
サンプルデータではB列に点数が入力されています。各点数に応じてC列10段階評価の結果を表示するのがゴールです。
まずは、10段階の評価基準を決めていきます。(便宜上、E,F列に定義していますが、エクセルの任意の場所に定義できます。)
評価1~10それぞれに点数の範囲を決めます。上図の例では、0~9点を評価1、10~19を評価2、20~29を評価3、・・・、90~100点を評価10となるよう定義しています。
ここで、セルの上から評価の低い順(基準点の低い順)に記載することに注意してください。理由は後程説明します。
次に、B列の点数に応じてC列に10段階評価を表示するために、VLOOKUP関数を使用します。
VLOOKUP関数は、指定した値を指定した範囲で検索し、検索がヒットした行の任意の列の値を返す処理を行います。
評価を表示したいセル(ここではC2)に「=VLOOKUP(B2,$E$2:$F$11,2,TRUE)」を入力します。
上図の関数を解説します。
「範囲」には、評価基準が定義されているセルの範囲($E$2:$F$11)を指定します。ここで、$記号は範囲の固定を意味し、関数を別のセルにコピーした場合でも常にE2からF11までの範囲を指定します。「列番号」には、評価の列番号(2)を指定します。列番号は上記範囲の左端の列(E列)より数えて2列目(F列)が評価の列であるため、2を指定しています。「検索方法」には、近似一致(TRUE)を指定します。近似一致は検索値の近似値を含めて検索します。検索値がヒットしない場合には、検索値未満の最も大きい値を返します。(例:検索値が65であれば、検索値未満の最も大きい値である60を返す)
ENTERボタンにて確定させます。点数に応じた評価が表示されます。
最後にオートフィル機能(関数を入力したセルの右下にカーソルを合わせると出てくる十字をドラッグ&ドロップ)にて各行の点数に応じた評価が表示できます。
良く使用する方法なのでこの機会に覚えておくといいです。
エクセルにて10段階評価を行う方法【相対評価】
次に、以下のサンプルデータを用いてエクセルにて点数に応じた10段階評価を相対評価にて行う方法について確認していきます。ここでの相対評価は点数を参考にその割合(百分位)に応じて評価を決めていきます。
サンプルデータではB列に点数が入力されています。各点数に応じてD列に10段階評価の結果を表示するのがゴールです。中間処理としてC列に百分位を計算します。
まずは、10段階の評価基準を決めていきます。(便宜上、F,G列に定義していますが、エクセルの任意の場所に定義できます。)
相対評価では百分位を使用します。百分位は全データを百等分した際の相対的位置をみるのに用いる数値です。
評価1~10それぞれに百分位で表した割合の範囲を決めます。上図の例では、0~0.099…を評価1(下位10%)、0.1~0.199…を評価2(下位10-20%)、・・・、0.9~1を評価10(上位10%)とするよう定義しています。
ここで、セルの上から評価の低い順(百分位の範囲の低い順)に記載することに注意してください。理由は絶対評価の場合と同様に、VLOOKUP関数の近似一致を利用するためです。
次に、C列に各点数の百分位を計算します。点数に応じた百分位を表示するために、PERCENTRANK.INC関数を使用します。PERCENTRANK.INCは、1位を「1」、2位以下を「0~1の間の少数」で返す処理を行う関数です。
百分位を表示したいセル(ここではC2)に「=PERCENTRANK.INC($B$2:$B$9,B2)」を入力します。
上図の関数を解説します。
「x」には、百分位を計算する対象の数値が表示されたセル(B2)を指定します。
ENTERボタンにて確定させます。点数に応じた百分位が表示されます。オートフィル機能(関数を入力したセルの右下にカーソルを合わせると出てくる十字をドラッグ&ドロップ)にて各行の点数に応じた百分位が表示できます。
次に、C列の百分位に応じてD列に10段階評価を表示するために、VLOOKUP関数を使用します。
評価を表示したいセル(ここではD2)に「=VLOOKUP(C2,$F$2:$G$11,2,TRUE)」を入力します。
上図の関数を解説します。
「検索値」には、百分位が表示されているセル(C2)を指定します。
「範囲」には、評価基準が定義されているセルの範囲($F$2:$G$11)を指定します。
「列番号」には、評価の列番号(2)を指定します。列番号は上記範囲の左端の列(F列)より数えて2列目(G列)が評価の列であるため、2を指定しています。
「検索方法」には、近似一致(TRUE)を指定します。
ENTERボタンにて確定させます。点数(百分位)に応じた評価が表示されます。
最後にオートフィル機能(関数を入力したセルの右下にカーソルを合わせると出てくる十字をドラッグ&ドロップ)にて各行の点数に応じた10段階評価が表示できます。
こちらも良く使用する方法なのでこの機会に覚えておくといいです。
まとめ エクセルにて10段階評価を行う方法
ここでは、エクセルにてあらかじめ決めた判定基準に基づき、点数に応じた10段階評価を行う方法について解説しました。
基本的には評価基準を定義し、絶対評価ではVLOOKUP関数、相対評価ではPERCENTRANK.INC関数とVLOOKUP関数を使うことで処理できます。
エクセルでのさまざまな処理に慣れ、日々の用務を効率化させていきましょう。
コメント