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

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

How-to

正規確率プロット

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

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

以下,エクセルによる Q-Qプロット の作り方です。ここでは一連の手続きを,本頁更新時点で最新の「Office365版」 Excel (ver.1810)で追っています。一部ボタンの配置や名称などが異なる箇所がありますが(この場合,可能であれば当該箇所に明記します),手続きそのものは,「永続ライセンス版」にいうところの Excel 2019, Excel 2016, Excel 2013 あるいは Excel 2010 でも基本的には同じです。

元データ

晴花

1

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

下図下段でこのデータの分布をヒストグラムにより可視化し,正規分布曲線(赤線)と重ねてみると,正規分布にしたがっていることがわかります。

このページでは,同様のことをヒストグラムでなく,Q-Qプロットでおこないます。以下,その手続きです。


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

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

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

晴花

2

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

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

Step 1 のデータを「R」をとおして出力したプレーンなQ-Qプロット[qqnorm()関数による]は,注として加えたグレーの囲み文字を除けば下の図のとおりです。すなわちここで描画するQ-Qプロットも同様,横軸には標準正規分布の分位点を,縦軸には観測値をとって,観測値に対する 理論分布(ここでは正規分布)上の期待値とを照らしていきたいと思います。

説明のみ

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

晴花

3

シートに見出しを2つ用意します。

中身は後述しますが,識別できれば何であれかまいません。とりあえず,ここでは「%順位」「Normsinv(%順位)」とします。

晴花

4

えと,このへんでざっくりと,このあとのアウトラインに触れさせてもらいます。

グラフを描くために必要なのは,「観測値」列,および「Normsinv(%順位)」列の2つで足るのですが,後者をダイレクトに求めると計算式が長くなってしまうので,何をやっているかを明確にする意味でも過程を2つに分割したいと思います。

すなわち「観測値」から「%順位」(パーセンタイル順位)を求め,「%順位」から「Normsinv(%順位)」(標準正規分布の上での期待値)を求めるといった流れですすめたいと思います。

説明のみ

観測値→相対化して順位付け→理論分布にあてはめる

晴花

5

また先の方針に倣って,「Normsinv(%順位)」列は散布図の横軸に,「観測値」列は同じく縦軸に充てる予定です。

説明のみ

観測値:縦軸, Normsinv(%順位):横軸

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

晴花

6

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

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

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

scrollable

[セルD2]=((RANK.EQ(C2, $C$2:$C$1001, 1)+COUNTIF($C$2:C2, C2)-1)-1/2)/$A$2

晴花

7

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

下の図の 青緑色のアンダーライン 線部分は,ざっくり言えば,1 から順に2,3…といった感じに一般にいう並びの順位を計算するものです。そこでさらに話を単純にするため,式の中の 青緑色のアンダーライン 線部分を「順位」という語で置き換えれば(順位-1/2)/ nといった式ができあがります。

説明のみ

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

晴花

8

このページのQ-Qプロットの描画法では,「『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)

[セルE2]=NORM.S.INV(D2)

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

晴花

11

ここからグラフを描画します。

早速,散布図をつくるのに必要な「観測値」および「Normsinv(%順位)」列をいちどに選択して手続きを進めたいところですが,ここでエクセルのグラフまわりの仕様として,シートの上でより左側にある列が横軸へと割り振られるといったことに注意する必要があります。何らの準備なくそのままグラフを描きすすめると,当然,当初思い描いたものとは体裁を異にしてしまいます。

ということで,ここらで仕様に沿うようなかたちつまり「観測値」列と「Normsinv(%順位)」列の位置関係を解決しておくとその後がスムーズです。具体的には,E列を切り取り(下図上段),C列に切り取ったセルの挿入(下図下段)をして対処したいと思います。

「Normsinv(%順位)」列を切り取って…

「観測値」列の上で挿入

晴花

12

「Normsinv(%順位)」および「観測値」列を選択後,リボンの挿入タブグラフグループにある散布図(X, Y)またはバブルチャートの挿入ボタン散布図ボタンとクリックします。

この操作でグラフ(散布図)がシートの上に出力されます。

2010

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

scrollable

「Normsinv(%順位)」および「観測値」列をソースとして散布図を作成する

晴花

13

あ。下の図縦軸のラベルがこの位置に据えられましたね。Q-Q プロット的にはどこかハマりがわるいようにも思うので,これをグラフの左側に寄せ替えたいと思います(好みにより)。

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

scrollable

縦軸ラベル位置の変更

晴花

14

とりわけ n がある程度の大きさをもってくると,(正規分布の性質上50パーセンタイル[横軸0]周辺は必然的に密度が濃くなるゆえ)彩色ずみのマーカーではややもすると1本の線のようにも見えてきます。これはいささかのっぺりすぎる印象なので,「R」に倣いデフォルトの塗り色を抜いた方が,“密度” を表現するうえでもbetterなような気もします(判断により)。

この場合,この作例でのグラフの系列"系列1"(平たく言えば “グラフ上のマーカー群”)をアクティブにした状態から,(もし書式設定ウィンドウを閉じてしまっていたら再び呼び出し)系列のオプションマーカーグーループの中の塗りつぶし塗りつぶしなしに変更します。

2010

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

scrollable

マーカーの塗り色を削除

晴花

15

任意にその他の書式設定を重ねます。

とりわけ,

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

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

晴花

16

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

FINISHED

Q-Qプロット

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

LastUpdate

2018.12.8

.
このページの先頭へ