エクセルはデータ解析・管理を行うツールとして非常に機能が高く、上手く使いこなせると業務を大幅に効率化できるため、その扱いに慣れておくといいです。
ただ機能が充実しているあまり初心者にとっては処理方法がよくわからないことも多いといえます。
例えばエクセルにて点数に応じて3段階にランク分け(3段階評価)を行うにはどう処理すればいいのか理解していますか。
ここではエクセルにてあらかじめ決めた評価基準に基づき、点数に応じた3段階評価(ABC評価)を行う方法について解説していきます。
【Excel】エクセルで3段階評価(ABC評価)を行う方法【絶対評価】
それでは以下のサンプルデータを用いてエクセルにて点数に応じた3段階評価(ABC評価)を絶対評価にて行う方法について確認していきます。ここでの絶対評価は点数に応じて評価を一意に決めていきます。
サンプルデータではB列に点数が入力されています。各点数に応じてC列に3段階評価の結果(A,B,C)を表示するのがゴールです。
まずは、3段階の評価基準を決めていきます。(便宜上、E,F列に定義していますが、エクセルの任意の場所に定義できます。)
評価A,B,Cそれぞれに点数の範囲を決めます。上図の例では
・40~69点をB評価
・70~100点をA評価
となるよう定義しています。
ここで、セルの上から評価の低い順(基準点の低い順)に記載することに注意してください。理由は後程説明します。
次に、B列の点数に応じてC列に3段階評価を表示するために
を使用します。
VLOOKUP関数は、指定した値を指定した範囲で検索し、検索がヒットした行の任意の列の値を返す処理を行います。
評価を表示したいセル(ここではC2)にVLOOKUP関数を入力します。
上図の関数を解説します。
・「検索値」には、点数が表示されているセル(B2)を指定します。
・「範囲」には、評価基準が定義されているセルの範囲($E$2:$F$4)を指定します。ここで、$記号は範囲の固定を意味し、関数を別のセルにコピーした場合でも常にE2からF4までの範囲を指定します。
・「列番号」には、評価の列番号(2)を指定します。列番号は上記範囲の左端の列(E列)より数えて2列目(F列)が評価の列であるため、2を指定しています。
・「検索方法」には、近似一致(TRUE)を指定します。近似一致は検索値の近似値を含めて検索します。検索値がヒットしない場合には、検索値未満の最も大きい値を返します。(例:検索値が65であれば、検索値未満の最も大きい値である40を返す)
ENTERボタンにて確定させます。点数に応じた評価が表示されます。
最後にオートフィル機能(関数を入力したセルの右下にカーソルを合わせると出てくる十字をドラッグ&ドロップ)にて各行の点数に応じた評価が表示できます。
【Excel】エクセルで3段階評価(ABC評価)を行う方法【相対評価】
次に、以下のサンプルデータを用いてエクセルにて点数に応じた3段階評価(ABC評価)を相対評価にて行う方法について確認していきます。ここでの相対評価は点数を参考にその割合(百分位)に応じて評価を決めていきます。
サンプルデータではB列に点数が入力されています。各点数に応じてD列に3段階評価の結果(A,B,C)を表示するのがゴールです。中間処理としてC列に百分位を計算します。
まずは、3段階の評価基準を決めていきます。(便宜上、F,G列に定義していますが、エクセルの任意の場所に定義できます。)
相対評価では百分位を使用します。百分位は全データを百等分した際の相対的位置をみるのに用いる数値です。
評価A,B,Cそれぞれに百分位で表した割合の範囲を決めます。上図の例では
・0.5~0.799…をB評価(中位30%)
・0.8~1をA評価(上位20%)
とするよう定義しています。
ここで、セルの上から評価の低い順(百分位の範囲の低い順)に記載することに注意してください。理由は絶対評価の場合と同様に、VLOOKUP関数の近似一致を利用するためです。
次に、C列に各点数の百分位を計算します。
点数に応じた百分位を表示するために
を使用します。PERCENTRANK.INCは、1位を「1」、2位以下を「0~1の間の少数」で返す処理を行う関数です。
百分位を表示したいセル(ここではC2)にPERCENTRANK.INC関数を入力します。
上図の関数を解説します。
「配列」には、点数全体の範囲($B$2:$B$8)を指定します。ここでも$記号にて範囲を固定します。
「x」には、百分位を計算する対象の数値が表示されたセル(B2)を指定します。
ENTERボタンにて確定させます。点数に応じた百分位が表示されます。オートフィル機能にて各行の点数に応じた百分位が表示できます。
次に、C列の百分位に応じてD列に3段階評価(ABC評価)を表示するために
を使用します。
評価を表示したいセル(ここではD2)にVLOOKUP関数を入力します。
上図の関数を解説します。
・「検索値」には、百分位が表示されているセル(C2)を指定します。
・「範囲」には、評価基準が定義されているセルの範囲($F$2:$G$4)を指定します。
・「列番号」には、評価の列番号(2)を指定します。列番号は上記範囲の左端の列(F列)より数えて2列目(G列)が評価の列であるため、2を指定しています。
・「検索方法」には、近似一致(TRUE)を指定します。
ENTERボタンにて確定させます。点数(百分位)に応じた3段階評価(ABC評価)が表示されます。
最後にオートフィル機能にて各行の点数に応じた評価が表示できます。
エクセルでabc評価の平均を計算する方法【3段階評価】
上のように絶対評価や相対評価により3段階評価(ABC評価)をつけることができますが、この結果を用いて全体の平均を計算する方法について確認していきます。
サンプルデータではC列にabc評価が入力されています。C10セルに評価の全体の平均を表示するのがゴールです。
ここで、評価はA,B,Cの文字列で表示されており、そのままでは平均値を計算できません。
そのため
・Bを2
・Cを1
のように評価をいったん数字に置き換えます。
数字への置き換えには
を使用します。
IF関数は、指定した論理式(条件)を満たすかどうか判定し、満たす場合には真(TRUE)、満たさない場合には偽(FALSE)を返す処理を行う関数です。
RUE/FALSEとして返す値には、任意の値や関数を設定でき、IF関数を複数重ねて使用することもできます。
IF関数の構文はこちらです。
実際の例を見てみます。
評価を表示したいセル(ここでは欄外のD2)にIF関数を入力します。
上図の関数を解説します。
1つ目のIFの「論理式」には、abc評価が表示されているセルの値がAであるという条件(C2=”A”)を指定します。ここで、「A」は文字列であるためダブルコーテーション「”」で囲みます。
1つ目のIFの「値が真の場合」には、評価Aを数字に置き換えた3を指定します。
1つ目のIFの「値が偽の場合」には、2つ目のIFを指定します。
2つ目のIFの「論理式」には、abc評価が表示されているセルの値がBであるという条件(C2=”B”)を指定します。
2つ目のIFの「値が真の場合」には、評価Bを数字に置き換えた2を指定します。
2つ目のIFの「値が偽の場合」には、評価Cを数字に置き換えた1を指定します。これは、「セルの値がBの判定で偽、つまり、セルの値はAでもBでもなくC」と判断できるためです。
ENTERボタンにて確定させます。abc評価を数字に置き換えた値がD列に表示されます。
最後にオートフィル機能(関数を入力したセルの右下にカーソルを合わせると出てくる十字をドラッグ&ドロップ)にて各行の値が表示できます。
次に、D列の数字に置き換えた評価の全体の平均を計算します。
以下のように平均値を表示したいセル(ここではD10)に
を入力します。
AVERAGE関数の「数値1」には、平均値を求める範囲(D2:D8)を指定します。
ENTERボタンにて確定させます。数字に置き換えた評価の全体の平均が表示されます。
次に、計算した数字に置き換えた平均値をabc評価に再度置き換えます。
先ほどの変換ルール(A=3、B=2、C=1)と異なり、平均値は少数となっています。
そのため、平均値での置き換えのルールを新たに決めます。
ここでは
・1.5~2.49…の場合は評価B
・2.5以上の場合は評価A
とします。
abc評価(3段階評価)への置き換えにはIF関数を使用します。
abc評価の平均値を表示したいセル(ここではC10)にIF関数を入力します。
上図の関数を解説します。
1つ目のIFの「論理式」には、数字の平均値が2.5以上であるという条件(D10>=2.5)を指定します。「>=」は〇〇以上を表します。
1つ目のIFの「値が真の場合」には、該当する評価(”A”)を指定します。ここで、「A」は文字列であるためダブルコーテーション「”」で囲みます。
1つ目のIFの「値が偽の場合」には、2つ目のIFを指定します。
2つ目のIFの「論理式」には、数字の平均値が1.5以上であるという条件(D10>=1.5)を指定します。
2つ目のIFの「値が真の場合」には、該当する評価(”B”)を指定します。
2つ目のIFの「値が偽の場合」には、該当する評価(”C”)を指定します。これは、「セルの値が1.5以上の判定で偽、つまり、セルの値は1.5未満」と判断できるためです。
ENTERボタンにて確定させます。abc評価(3段階評価)の平均が表示されます。
まとめ エクセルで3段階評価(ABC評価)の平均を計算する【ランク分け】
ここでは、エクセルにてあらかじめ決めた判定基準に基づき、点数に応じた3段階評価(ABC評価)を行いその平均を計算する方法について解説しました。
基本的には評価基準を定義し、絶対評価ではVLOOKUP関数、相対評価ではPERCENTRANK.INC関数とVLOOKUP関数を使うことで3段階評価(ABC評価)を行うことができます。
エクセルでのさまざまな処理に慣れ、日々の用務を効率化させていきましょう。
コメント