ひとりマーケティングのためのデータ分析

手順解説 | Excel(エクセル)でおこなうビジネスデータの分析

How-to

偏差値表

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

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

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

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

以下,エクセルによる偏差値表の作り方です。ここでは一連の手続きをExcel 2016で追っています。Excel 2013,Excel 2010およびExcel 2007についても基本的には同じ流れとなりますが,一部ボタンの配置や名称などが異なる箇所があります(この場合,可能であれば当該箇所に明記します)。

元データ

晴花

1

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

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


元データ・偏差値

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

晴花

2

ではまず,基準値(標準得点,zスコアとも)と偏差値を求めるにあたって,列見出しを加えておきます。ここではE1~F1です(上方のハイライトの部分)。

ついでにシートの空いているところにでも,平均標準偏差を計算するための場所を確保しておきます。ここではC27~C28にそれらの見出しを入力しています(下方のハイライトの部分)。

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

晴花

3

売上の平均を求めます。

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

  • =AVERAGE(D2:D25)

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

晴花

4

売上の標準偏差を求めます。

ここではStdev.p関数[2010以上]またはStdevp関数[2007]を使い求めます。Stdev.p関数を利用したとき,式は下の図のとおりです。Stdevp関数の場合も引数は同じです(Stdev.p関数[2010以上], Stdevp関数[2007] ―"Office")。

  • =STDEV.P(D2:D25)

Stdev.pまたは Stdevp関数]と[Stdev.sまたはStdev関数]

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

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

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

晴花

5

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

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

晴花

6

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

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

scrollable

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

晴花

7

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

standardize関数の検索

晴花

8

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

これを選択してOKボタンをクリックします。

関数名より「STANDARDIZE」を選択

晴花

9

関数の引数ダイアログのX, 平均, 標準偏差の3カ所について,下のようにシート上の対応する色のセルを指定します。この後で式をコピーすることを考慮して,「平均」と「標準偏差」は絶対参照としておきます(絶対参照:該当する入力欄をアクティブにしてF4キーを 1 回押し,セル番地の行と列の前に「$」記号を表示させておきます)。

すべて入力した後,OKボタンをクリックします。

scrollable

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

晴花

10

先頭のデータの金額に関して,下のように基準値が返ってきます。

ということで,この式を残り23人分コピーします。

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

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

晴花

11

続いて偏差値を求めます。偏差値を求めるためには

基準値を10倍して50を足すことが必要です。

具体的には,「偏差値」列・見出し直下のセル(ここではセルF2)をアクティブにし,

  • =10*E2+50

と入力します。

これを入力した後,ここでも残りの23人分,式をコピーします。

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

晴花

12

偏差値表の完成です。

FINISHED

売上実績偏差値表の完成

  • 本頁で使用したデータはすべて架空のものです。また特定の会社等に実在する人物名,および同場所で実際に観測されたデータ群などを根拠にしたものでもありません。
.

LastUpdate

2017.3.7

.
このページの先頭へ