Excelで偏差値を求めるユーザー定義関数「DEVIATION_VALUE」

この記事では、Excelで偏差値を求めるためのユーザー定義関数「DEVIATION_VALUE」を紹介します。この関数を使用することで、試験の点数などを比較する際に便利な偏差値を簡単に計算できます。

ただし偏差値はExcelの標準関数でも求めることができます。ユーザー定義関数を使用するケースは、あなたが頻繁に偏差値計算をする場合にしていただくことをおすすめします。

一般の偏差値計算：「(対象値 − 平均値) × 10 / 標準偏差 + 50」

平均値はAVERAGE関数で求めることができます。標準偏差はSTDEVP関数で求めることができます。

DEVIATION_VALUE関数の概要

DEVIATION_VALUE関数は、指定された範囲内の数値の平均値と母集団全体に関する標準偏差（StDevP）を求め、その結果をもとに偏差値を計算します。偏差値は、個々の数値が平均値からどれだけ離れているかを示す指標であり、試験の点数などを比較する際によく用いられます。

ユーザー定義関数のコード

Function DEVIATION_VALUE(rng As Range, target As Double) As Double
    ' 平均値を求める
    Dim avg As Double
    avg = Application.WorksheetFunction.Average(rng)
    
    ' 母集団全体に関する標準偏差（StDevP）を求める
    Dim stdev As Double
    stdev = Application.WorksheetFunction.StDevP(rng)
    
    ' 偏差値を計算する
    ' 偏差値 = (対象値 - 平均値) / 標準偏差 × 10 + 50
    DEVIATION_VALUE = (target - avg) / stdev * 10 + 50
End Function

関数の使い方

この関数をExcelのVBAエディターから「標準モジュール」に追加すると、Excelのシート上で偏差値を計算することができます。使い方は以下の通りです。

    =DEVIATION_VALUE(範囲, 対象値)

範囲は、偏差値を計算する対象となる数値が格納されたセル範囲を指定します。対象値は、偏差値を求めたい個々の数値を指定します。

使用例

例えば、以下のような定期試験の点数が記録されたExcelシートがあるとします。

    A      B
    1   数学
    2   80
    3   75
    4   68
    5   90
    6   78

このデータを使って、セルC2に「=DEVIATION_VALUE(A2:A6, B2)」と入力することで、偏差値を計算できます。同様に、C3～C6にそれぞれの偏差値を計算する式を入力すれば、すべての点数の偏差値を求めることができます。

A      B      C
1   数学          偏差値
2   80          52.51
3   75          45.53
4   68          35.77
5   90          66.47
6   78          49.72

上記の結果から、各生徒の数学の点数に対する偏差値が求められました。これにより、生徒たちの成績を比較しやすくなります。

まとめ

この記事では、Excelで偏差値を求めるためのユーザー定義関数「DEVIATION_VALUE」を紹介しました。この関数を利用することで、試験の点数などを簡単かつ正確に比較することができます。ぜひ、日々の業務や学習においてお役立てください。