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

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

How-to

正規確率プロット

確率プロットは,データが正規分布他の理論分布にしたがっているかどうか目視により確認する目的において利用されるグラフです。

とりわけ,ここではデータの正規性を期待した場合の「正規確率プロット」(以下,単に「Q-Qプロット」)をつくっていきます。もしこの期待が満たされるのであれば,グラフにプロットされるマーカーは一定の勾配をもつ直線状に描かれることになります。

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

元データ

晴花

1

元のデータとその分布図です。ぶ厚い事典の中ほどに位置する 特定の 1 ページをめがけて,事典の最初から指を滑らせながらぴったり止めようと 1000 回試行した結果です(ページ数を用紙の枚数に換算)。

この観測値は離散分布ですが,ここでは便宜上連続分布とみなして以下手順を追います。


元データ・正規確率プロット

元データの分布(ヒストグラム+正規分布曲線)

ここにいう「Q-Qプロット」について

晴花

2

その前に…目的はたがわずといえども,グラフとしてのQ-Qプロットには横軸・縦軸が異なるさまざまな書式のものがあります(cf. Google画像検索の結果)。

前提として,ここでは 多くのユーザーに利用されている統計処理系「R」に準じた形式をとっていこうと思います

Step 1 のデータを「R」をとおして出力したプレーンなQ-Qプロットは,下図のようになります。すなわちここでも,横軸には標準正規分布の分位点を,縦軸には観測値をとって,観測値に対する 理論分布(ここでは正規分布)における期待値との関係を見ていくことにします。

説明のみ

Rによる正規確率プロット―横軸:標準正規分布のパーセンタイル, 縦軸:観測値

晴花

3

見出しを 2 つ用意します。

…中身は後述しますが,的を射たものであれば何だって OK です。とりあえず,ここでは「%順位」「Normsinv(%順位)」とします。

晴花

4

あ…えと,このへんでざっくりと,このあとのアウトラインを説明させてもらいますNe。

先の方針に倣って,ここでいう「Normsinv(%順位)」列は散布図の横軸に,「観測値」列は縦軸に適用する予定です。

説明のみ

晴花

5

「Normsinv(%順位)」(期待値)列は,「%順位」(パーセンタイル順位)列をもとに,「%順位」列は「観測値」列をもとに計算します。

以上を転じれば,次のようなプロセスとなります。

  1. 「観測値」群各セルの値を 群内でパーセンタイル順位に変換
  2. パーセンタイル順位をもとに,標準正規分布の上での期待値をはかる
説明のみ

パーセンタイル順位に置き換えてみる

晴花

6

では,具体的に「%順位」列からつくっていきます。ここでは何をやるかというと,観測値を昇順で相対的に順位づけしていきます。

見出し直下のセルに下式を入力し,これを表の最下行までコピーします。

  • =((RANK.EQ(C2, $C$2:$C$1001, 1)+COUNTIF($C$2:C2, C2)-1)-1/2)/$A$2

scrollable

晴花

7

上の長ったらしい式の説明です。

話を単純にするため,とりあえず下図の 青緑色のアンダーライン 線部の式を「順位」といったカンジに置き換えて………(順位-1/2)/ nという式にしてしまいますNe。

説明のみ

[数式の構造] =(順位-0.5)/n

晴花

8

ここでは,先に掲げた「『R』に準ずる」という趣旨から,上の(順位-1/2)/n 式で順位づけをおこなうものとしています。ただし,そうした縛りのない場所では下のような諸式が利用されることもあります( i : 順位)。


1( i - 0.375) / ( n + 0.25)
2( i - 0.3) / ( n + 0.4)
3 i / ( n + 1)
4 i / n

[表の出所] 確率プロットとQ-Qプロット; url:www.originlab.jp/doc/Origin-Help/ProbPlot-QQPlot#Score_Methods ―"OriginLab" を一部改変(リンク切れ)

また,中身がブラックボックスで問題なければ エクセルのPercentRank系関数も利用できるかと思いますが,Q-Qプロットが主観に左右される視覚的な判断をおこなうものである以上,上記の手法含めどれがすぐれているのか………といった判断はあまりなじまないようには感じます。

晴花

9

さて,つづいては 先ほど端折った下図の 青緑色のアンダーライン 線部の式です。

順位づけをおこなうRank.Eq関数は,同値の場合同順位で処理します(欠番が発生します)。こちらも,先に掲げた「『R』に準ずる」趣旨のもとでは意に沿わないので,CountIf関数で前方にある同値の存在を調べ,それ(ら)を見つけた場合は より後の(ワークシートで言えば下方の)値を下位とする処置を施します(この場合欠番が発生しません)。

説明のみ

[RANK.EQのみ]同順位→同値→欠番あり [RANK.EQとCOUNTIFの組み合わせ]同値→先の方を上位に→欠番なし

その下側確率なら,z分布はどの値を返す?

晴花

10

「Normsinv(%順位)」列に次の式を入力し,表の最下行までコピーします。

  • =NORM.S.INV(D2)

個々の観測値の観測値群におけるパーセンタイル順位を理論分布とする正規分布においてあてはめてみて,正規分布を前提としたときに期待される分位点をはかります。

「散布図」にプロットして あてはまりを判断する

晴花

11

ここ以降はグラフ化の手続きです。

と,いうことで,散布図をつくるのに必要な「Normsinv(%順位)」および「観測値」列をいちどに選択して手続きを進めたいところですが,エクセルのグラフまわりの仕様上,より左側にある列が横軸へと割り振られることから,最初の想定のもとではここも対処が必要となってきます。

具体的には,1 列のみを選択した場合は縦軸に振られることから,このふるまいを利用して まず「観測値」列のみの散布図を作成します。作業としては,「観測値」列のデータ範囲を選択し…リボンの挿入タブグラフグループにある散布図(X, Y)またはバブルチャートの挿入ボタン散布図ボタン …とクリックします。

2010

[挿入]タブ[散布図]ボタン[散布図(マーカーのみ)]ボタン

「観測値」列のみを選択して…

「散布図」を挿入

晴花

12

作成されたグラフをアクティブにしておきます。

その上で,リボンのデザインタブデータグループにあるデータの選択ボタンをクリックします。

scrollable

晴花

13

データソースの選択ダイアログが表示されます。

左方,凡例項目編集ボタンをクリックします。

晴花

14

系列の編集ダイアログが表示されます。

系列 X の値に,ここでいう「Normsinv(%順位)」列のデータ範囲を指定(見出しは除く)してOKを返します。

…これによりデータソースの選択ダイアログに戻ったら,ふたたびOKを返します。

scrollable

「観測値」系列にXの値を追加

晴花

15

縦軸ラベルがこの位置にあるのは…この場合やっぱりスッキリしないので,これをグラフの左側に寄せ替えます(好みにより)。

第 1 縦軸をアクティブにした状態から,ダブルクリックなどで書式設定ウィンドウを呼び出し,軸のオプションカテゴリにあるラベルラベルの位置下端/左端に変更します。

scrollable

縦軸ラベル位置の変更

晴花

16

とりわけ n がある程度の大きさをもつとき,(正規分布の性質上50パーセンタイル[横軸0]周辺は必然的に密度が濃くなるゆえ)マーカーが彩色されていると視認性に欠けるので,「R」に倣いデフォルトの塗り色を抜いた方が better なような気もします(判断により)。

この作例でのグラフの系列"観測値"をアクティブにした状態から,(もし書式設定ウィンドウを閉じてしまっていたら再び呼び出し)系列のオプションマーカーカテゴリにある塗りつぶし塗りつぶしなしに変更します。

2010

「R」に準ずるならマーカーの形状 および 大きさも変更します(マーカーのオプション)

scrollable

マーカーの塗り色を削除

晴花

17

任意にその他の書式設定を施します。

とりわけ,

  • 横・縦両軸の境界値に関する最小値および最大値の設定
  • プロットエリアの縦横比の調整(正方に)

は最低限やっておいた方が better な項目だとは思います。

晴花

18

Q-Qプロットの完成です。

FINISHED

Q-Qプロット

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

LastUpdate

2018.2.25

.
このページの先頭へ