BDAstyle

ビジネスデータ分析ツールの作成 with Excel

分布曲線のグラフ―確率とパーセント点のビジュアライゼーション with Excel

1.イントロダクション

正規分布,標準正規分布,t分布,χ2分布,F分布曲線のグラフに

を,シートの再計算にともなうグラフの更新機能を維持したままに図示する手続きです(下図例)。

散布図と面グラフによる複合グラフをベースとし,これに必要によってデータ系列を重ねて要素を描画する流れをとります。以下,その具体的手続です。

2.工程

1.平滑線散布図・面グラフによる曲線と曲線下面積の描画

Step 1分布曲線の描画(1)

確率変数, 確率密度関数(2つ分)の値をあらわす見出しを任意の名称で用意します(ここでは順に"x","S_density","A_area"としています)。確率密度関数の1つ目は散布図に,2つ目は面グラフに利用します。共用を避けるのは面積の描画・再描画が容易な環境を保つ趣旨からです。

Step 2分布曲線の描画(2)

以下この作例では,標準正規分布曲線を扱うものとします。

したがって,ここで確率変数xの値の初期値を-3にて例示するものとします(目的にかなう値を適宜指定します)。

ここでは-3≦x≦3の区間でグラフを作成したいので,小数の任意の有効桁に合わて"x"列を作成します(ここでは1/100刻みとします。ただし演算誤差への対処から小数点第3位以下の情報を落としています)。

[セルA3]=TRUNC(A2+0.01,3)

Step 3分布曲線の描画(3)

以下の関数をもとにxに対応する確率密度関数の値を埋めていきます("S_density"列。なおここでは標準正規分布曲線を選択したので"NORM.S.DIST"を用います)。

正規分布 NORM.DIST(x, mean, sd, 関数形式)
標準正規分布 NORM.S.DIST(x, 関数形式)
t分布 T.DIST(x, df, 関数形式)
χ2分布 CHISQ.DIST(x, df, 関数形式)
F分布 F.DIST(x, df1, df2, 関数形式)

この場合,引数「関数形式」はいずれの場合であれ"FALSE"を指定します。

[セルB2]=NORM.S.DIST(A2,FALSE)

Step 4分布曲線の描画(4)

左の列の値を転記します("A_area"列)。

[セルC2]=B2

Step 5曲線下面積の描画(1)

"x"列 および"S_density"列のデータ範囲を選択して,挿入タブ「グラフ」グループの散布図ボタンをクリックし,散布図(平滑線)を選択します。

Step 6曲線下面積の描画(2)

デザインタブ「データ」グループのデータの選択ボタンをクリックし,「データソースの選択」ダイアログ左側「凡例項目」の追加ボタンをクリックします。

Step 7曲線下面積の描画(3)

「系列の編集」ダイアログの「系列名」「系列Xの値」「系列Yの値」をそれぞれ下図のような対応(囲み彩色3領域)で指定します。

ふたたび「データソースの選択」ダイアログに戻ったらOKを返します。

Step 8曲線下面積の描画(4)

書式タブ「現在の選択範囲」グループから"A_area"系列を選択し,選択対象の書式設定ボタンをクリックします(対象の書式設定ダイアログの呼び出しはその他の方法でも。以下に同じ)。

Step 9曲線下面積の描画(5)

「データ系列の書式設定」ダイアログが表示されます。

「使用する軸」を2に変更します。

Step 10曲線下面積の描画(6)

レイアウトタブ「軸」グループのボタンをクリックし,2横軸既定の軸を設定とたどって第2横軸を作成します(面グラフ用)。

Step 11曲線下面積の描画(7)

デザインタブ「種類」グループのグラフの種類の変更ボタンをクリックします。

Step 12曲線下面積の描画(8)

「グラフの種類の変更」ダイアログの「面」カテゴリからを選択します。

Step 13曲線下面積の描画(9)

2軸 縦(値)軸を選択し選択対象の書式設定ボタンをクリックします。

「軸の書式設定」ダイアログ(「軸のオプション」カテゴリ)の「横軸との交点」を自動に変更します。

Step 14曲線下面積の描画(10)

2軸 横(項目)軸を選択し選択対象の書式設定ボタンをクリックします。

「軸の書式設定」ダイアログ(「軸のオプション」カテゴリ)の「ラベルの間隔」を「間隔の単位」に切り替え,この値を Step 2で設定した刻みの逆数 に変更します(この例では1/100100)。

Step 15曲線下面積の描画(11)

横(値)軸を選択し選択対象の書式設定ボタンをクリックします。

「軸の書式設定」ダイアログ(「軸のオプション」カテゴリ)の「最小値」「最大値」を Step 2でおこなった設定 と同様のものに設定します。「目盛間隔」については任意の値を設定します。

Step 16曲線下面積の描画(12)

2軸 横(項目)軸を選択し選択対象の書式設定ボタンをクリックします。

「軸の書式設定」ダイアログ(「軸のオプション」カテゴリ)の「目盛の種類」「補助目盛の種類」「軸ラベル」をすべて なし に設定してこの軸を不可視とします。

Step 17曲線下面積の描画(13)

2軸 縦(値)軸を選択し選択対象の書式設定ボタンをクリックします。

「軸の書式設定」ダイアログ(「軸のオプション」カテゴリ)の「最小値」「最大値」「目盛間隔」を 縦(値)軸(第1縦軸)と同様のそれ に変更します。

つづけて,「目盛の種類」「補助目盛の種類」「軸ラベル」をすべて なし に設定します(この軸を不可視に)。

Step 18曲線下面積の描画(14)

さらにつづけて,「線の色」カテゴリの「線の色」をなし に変更します。

Step 19曲線下面積の描画(15)

縦(値)軸を選択し選択対象の書式設定ボタンをクリックします。

「軸の書式設定」ダイアログ(「軸のオプション」カテゴリ)の「目盛の種類」を交差にしたうえで,「軸ラベル」 を軸の下/左または下端/左端に設定します。

「軸の下/左」と「下端/左端」の違いは次のとおりです。

Step 20基本形の完成

細部の書式設定を必要によっておこない,分布曲線下が彩色された グラフの基本形が完成です。

Step 21必要な部分の面積を図示するには

この状態で"A_area"列の任意の領域,たとえば-1.96≦x≦1.96(下図赤囲み部分)を削除(Delete)すると,

彩色部分の中央が抜け,

-3≦x<-1.96,1.96<x≦3(先の図の赤囲み以外の部分)を削除すると,彩色部分の両端が抜けます。

2.パーセント点のプロット

Step 22パーセント点用の x, density

たとえば 横軸(x)上1.64の位置にドット(…散布図のマーカー)を打ちたいとして,シート上の空いている場所に,確率密度関数の値を0にしたxdensityのペアをつくります。

Step 23散布図の追加(1)

グラフを選択した状態からデザインタブ「データ」グループのデータの選択ボタンをクリックします。

加えて「データソースの選択」ダイアログの左側「凡例項目」の追加ボタンをクリックし「系列の編集」ダイアログを表示させます。

このダイアログの「系列名」「系列Xの値」「系列Yの値」について,それぞれ下図のような対応でデータ範囲を指定します(ここでは系列名を"S_pp"としますが任意の名で可)。

Step 24散布図の追加(2)

系列"S_pp"を選択します。

つづいてデザインタブ「種類」グループのグラフの種類の変更ボタンをクリックします。

Step 25散布図の追加(3)

「グラフの種類の変更」ダイアログの「散布図」カテゴリから散布図(マーカーのみ)を選択します。

Step 261点ドットの描画・完成

x=1.64位置にマーカーが表示されます。

これを適宜整形(大きさ・形状・色など)すると下図のようなグラフになります。彩色部分・パーセント点いずれも更新が可能です。

Step 272点ドットの描画|2点ドットを結ぶ直線の描画

系列"S_pp"x, dを追加し,グラフの範囲を再設定することで2点の描画も可能です。

このとき,系列"S_pp"散布図(直線とマーカー)に変更すると,

ドット間で直線が描画できます。

Step 282点ドットの描画|2点ドットからグラフの端に離れる直線の描画

また,"S_pp"系列を2つ用意し,xの一方をそれぞれグラフの表示範囲外にとることで,ドットから左端ないしは右端にむかって直線を引くことができます。

Step 29t分布, χ2分布, F分布での適用

以下,標準正規分布以外のt, χ2, Fの各分布で面積やパーセント点を描画した場合の例です。

Step 30領域の重なる面積の描画

確率密度関数列をもう1つ用意し面グラフを追加することで(Step 4・6・7・8・9・11・12の処理),領域の重なる面積についても図示することができます(下図に例示:散布図4枚[3点ドット], 面グラフ2枚, 透過処理)。

その他の参照