HOME TOOLS

2023/7/10

イントロダクション

偏差値は，集団やグループにおける観測値の格付け手法のひとつで，観測値の平均とばらつきから，1単位［1点，1円，1回，etc...］あたりのもつ相対的な重みを考慮する指標です。

READ MORE

偏差値に変換したデータの平均は50，標準偏差は10となります。母集団に正規性を仮定したとして，偏差値表からは次のようなことが分かります。

  • 集団のある要素の偏差値が50よりも大きい場合，それは平均を超えるポジションにある。また50よりも小さい場合は，平均に満たないポジションにある。
  • 集団のある要素の偏差値が60を超える，あるいは40を下回る場合，標準的なポジション(およそ70%の要素が該当する)との対比において，それを特異なポジションとみることもできる。

事務しごとにおいて偏差値表が役に立つのは，管理のしかたやあらたな施策を改善・検討するための判断の材料として，集団の中での個々の要素の相対的な位置を知ることが必要とされるような場面などがあげられるかと思います。

以下，Excelによる偏差値表の作り方です。ここでは一連の手続きをサブスクリプション版Excel(ver.2006)で追っています。一部ボタンの配置や名称などが異なる箇所がありますが，手続きそのものは「永続ライセンス版」にいうところのExcel 2019, 2016, 2013, 2010も基本的に同じです。

なお，下のスイッチで新機能「スピル」に沿った説明に切り替えが可能です(デフォルト:OFF)。

晴花

元データ

元のデータです。部員の別に売上を集計したものです(「金額」列)。

このデータから偏差値を求めます。

元データ・偏差値

zスコアを求める(データの基準化)

セルE1～F1に列見出し基準値(標準得点，zスコアとも)，偏差値を用意します。

ついでにシートの空いているところ(下の図ではセルC27～C28)にでも，元データの平均と標準偏差を計算するための場所を確保しておきます。

[セルC27]平均, [セルC28]標準偏差 と入力

(売上の)平均を求めます。

ここではAverage関数を使って求めます。この場合，式は次のとおりです。

  • CELL D27=AVERAGE(D2:D25)
average関数で平均を計算…[セルD27]=average(D2:D25)

標準偏差を求めます。

ここでは組み込みのStdev.p関数を使ってそれを求めたいと思います。このとき，式は次のとおりです(Stdev.p関数 ―"Office")。

  • CELL D28=STDEV.P(D2:D25)
Stdev.p関数とStdev.s関数

ここでは，リサーチサービス社の全てのRS部員をひっくるめて評価を加えたくあります。そして24名の部員のすべて，すなわち母集団すべての要素にかかわるデータも揃えています。これをしてStdev.p(STandard DEViation based on the entire Population) 関数を用いるものとしています。

他方Stdev.s関数は，対象が標本(全体からいくつかの要素を無作為に抜き出したもの)であってそこから母標準偏差の推定量をとるときに利用します。もっとも，何らの集団で偏差値を求めることが念頭にあるとき，標本を相手にするようなケースは多くはないように思いますが

stdev.p関数で標準偏差の計算…[セルD28]=stdev.p(D2:D25)

次に各個の基準値を計算します。

関数を使う場合，これはStandardizeによって求めることができます。もっともこの関数はいくらかの引数を要求するので，ここではより明示的に関数の挿入ダイアログを使って入力していく方法をとりたいと思います(Standardize関数 ―"Office")。

ユーザーが直接入力が可能な練度にあれば，それによる作業の方が簡便で好ましくはあります。

「基準値」列・見出し直下のセル(ここではセルE2)をアクティブにしてから，数式バーの関数の挿入ボタンをクリックします。

これにより下のように関数の挿入ダイアログがあらわれます。

「関数の挿入」ダイアログ

関数の検索に "standardize" と入力し検索開始ボタンをクリックします。

standardize関数の検索

関数名のいちばん上にSTANDARDIZEが表示されているかと思います。

これを選択してOKを返します。

関数名より「STANDARDIZE」を選択

関数の引数ダイアログのX, 平均, 標準偏差の3カ所について，下のようにシート上の対応する色のセルを指示します。この後，式をコピーすることを考慮にいれて，「平均」と「標準偏差」は絶対参照としておきます。

該当する入力欄をアクティブにしてF4キーを1回押し，セル番地の行と列の前に「$」記号を表示させておきます。

すべてを指示した後，OKボタンをクリックします。

「standardize関数」ダイアログ…[X]D2, [平均]$D$27, [標準偏差]$D$28

元データのいちばん頭の金額に関して，下のように基準値が返ってきました。

つづく人たちについても同様ですのでこの式を残り23人分コピーします。

基準値計算式のコピー(セルE25まで)

関数の引数ダイアログのX, 平均, 標準偏差の3カ所について，下のようにシート上の対応する色のセルを指示し，OKを返します。

「standardize関数」ダイアログ…[X]D2:D25, [平均]D27, [標準偏差]D28

zスコアを偏差値に変換する

ここで偏差値を求めます。具体的には

基準値を10倍して50を足す

ことで求めます。


すなわち「偏差値」列・見出し直下のセル(ここではセルF2)をアクティブにし，

  • CELL F2=E2*10 + 50
  • CELL F2=E2#*10 + 50

と入力します。

入力を終えたら，こちらも残りの23人分，式をコピーします。

[偏差値の計算式]=10*E2+50。これをセルF25までコピー。
[偏差値の計算式]=10*E2+50。これをセルF25までコピー。

偏差値表の完成です。

売上実績偏差値表の完成