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

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

How-to

偏差値表

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

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

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

事務しごとにおいて偏差値表が役に立つのは、管理方法の改善やあらたな施策検討のための判断材料として、集団やグループの構成・時期あるいは単位などの違いを超えて対象を客観的に比較することが必要とされるような場面などがあげられるかと思います。

以下、エクセルによる偏差値の求め方です。ここでは一連の手続きを Excel 2010 で追っています。これは Excel 2007, Excel 2013 および Excel 2016 でも変わりません。

元データ

晴花

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 部員を対象として偏差値を計算することが目的となります。したがって、標準偏差を計算する対象も、全ての RS 部員…つまり母集団と考えます。これが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)をアクティブにしてから、数式バー関数の挿入ボタンをクリックします。すると下のように関数の挿入ダイアログが表示されます。

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

晴花

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

2016.7.12

.
このページの先頭へ