Excelによる、Q-Q Plotのつくり方。
以下，ExcelによるQ-Qプロットの作り方です。ここでは一連の手続きを Excel 2016 で追っています。一部ボタンの配置や名称などが異なる箇所がありますが(この場合，可能であれば当該箇所に明記します)，手続きそのものは，「永続ライセンス版」にいうところの Excel 2019, Excel 2013 あるいは Excel 2010 そして，「Microsoft365版」のExcel (本頁更新時点のver.2007)とも基本的には同じです。
元データ
ここにいう「Q-Qプロット」について
前提として，ここでは多くのユーザーに利用されている統計処理系「R」に準じた形式をとっていこうと思います。
1 のデータを「R」を通して出力したプレーンなQ-Qプロット［qqnorm()関数による］は，下の図のとおりです(※グレーの囲み文字のみ除く)。すなわちここで描画するQ-Qプロットも同様，横軸には標準正規分布の分位点を，縦軸には観測値をとって，観測値に対する理論分布(ここでは正規分布)上の期待値とを照らしていきたいと思います。
中身は後述しますが，識別できれば何であれかまいません。とりあえず，ここでは %順位 Normsinv(%順位) とします。
パーセンタイル順位に置き換えてみる
- CELL D2 =((RANK.EQ(C2,\$C\$2:\$C\$1001,1)+COUNTIF(\$C\$2:C2,C2)-1)-1/2)/\$A\$2
下の図の 線部分は，ざっくり言えば，1 から順に2，3 といった感じに一般にいう並びの順位を計算する要素です。そこでさらに話を単純にするため，式の中の 線部分を「順位」という語で置き換えれば (順位$-1/2$)/$n$ といった式が出来上がります。
このページの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$また，中身がブラックボックスで問題なければ Excel固有の PercentRank 系関数も利用できるかと思いますが，Q-Qプロットが主観に依存する視覚的な判断をおこなうものである以上，上記の手法含めどれが優れているのか といった議論は馴染みがたいように感じます。
Excelの組み込み関数「Rank.Eq」は，同値の場合同順位で処理します(=欠番が発生します)。こちらも，先に掲げた「Rに準じる」趣旨のもとでは意に沿わないので， CountIf 関数で前方にある同値の存在を調べ，それ(ら)を見つけた場合，より後の(ワークシートで言えば下方の)値を下位とする旨の処置を咬ませます(=欠番が発生しません)。
その下側確率なら，z分布はどの値を返す？
- CELL E2 =NORM.S.INV(D2)
- CELL E2 =NORM.S.INV(D2:D1001)
「散布図」にプロットして あてはまりを判断する
ということで，ここらで仕様に沿うようなかたち つまり「観測値」列と「Normsinv(%順位)」列の位置関係を解決しておくとその後がスムーズです。具体的には，E列を 切り取り (下図上段)，C列に 切り取ったセルの挿入 (下図下段)をして対処したいと思います。
「Normsinv(%順位)」および「観測値」列を選択後，リボンの 挿入 タブ グラフ グループにある 散布図(X, Y)またはバブルチャートの挿入 ボタン 散布図 ボタン とクリックします 。
DIFFERENT VERSIONS2010: ［挿入］タブ ［散布図］ボタン ［散布図(マーカーのみ)］ボタン
第1縦軸 をアクティブにした状態から，ダブルクリックなどで書式設定ウィンドウを呼び出し， 軸のオプション グループ・ ラベル の ラベルの位置 を 下端/左端 に変更します。
この場合，この作例でのグラフの系列 "系列1" (平たく言えば “グラフ上のマーカー群”)をアクティブにした状態から，(もし書式設定ウィンドウを閉じてしまっていたら再び呼び出し) 系列のオプション マーカー グーループの中の 塗りつぶし を 塗りつぶしなし に変更します。
DIFFERENT VERSIONS2010: Rに準じるならマーカーの形状 および 大きさも変更します(マーカーのオプション)
- 横・縦両軸の 境界値 に関する 最小値 および 最大値 の設定
- プロットエリアの縦横比の調整(正方に)
RELATED
HITORIMARKETING.NET BDASTYLE.NET CONTENTS- 元データ
- ここにいう「Q-Qプロット」について
- パーセンタイル順位に置き換えてみる
- その下側確率なら，z分布はどの値を返す？
- 「散布図」にプロットして あてはまりを判断する
- [web]SSRI(2015u)
- 柴田(1981), pp.239-242.
- ひとりマーケティングのためのデータ分析
- hitorimarketing.net
- hawcas
- ご返答いたしかねますこと
- 「操作手順のここが分からないから教えてほしい」といったユーザーの技量に由来するご質問
- 「あのグラフを自分の持っているデータでつくってほしい」といった代行のご依頼
- その他筆者が対処しえないと判断した事項