BDAstyle

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

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

イントロダクション

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

  • 確率
    • 信頼係数
    • P
    • 有意水準 など
  • パーセント点
    • 信頼限界
    • 検定統計量
    • 棄却限界値 など
  • 確率とパーセント点
    • 棄却域と有意水準
    • 受容域
    • 信頼区間と信頼係数 など

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

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

仕様

曲線・曲線下の領域・横軸上の直線とその境界の各要素を,以下の4つのパターンで描画します。パターンの切り替えについては,オプションボタンの1クリックで完結する仕組みをつくります。

横軸の上の直線とその境界の描画については,チェックボックスの1クリックで表示・非表示が切り替え可能な仕組みを組み込みます。

工程

シートの準備

Step 1見出しの配置

シートにあらかじめ次の見出しを用意しておきます。このとき,セル番地は厳密である必要がありますが,表記については必ずしも下記に従う必要はありません。

scrollable

Step 2x の配置

以下この作例では,標準正規分布での場合を例に処理を進めるものとします。

運用時,このシートは「下側x」「上側x」に,塗りつぶしたい領域の境界となるxの値(この例ではすなわちzと同義)を指定して使用することとなります。

とまれ,シートをつくるうえで便宜的にもこれらの値が欲しいので,ここではそれぞれ-1, 1としておきたいと思います。

"x"列(グラフに表示するxの範囲)を作成します。ここではたとえば±3標準偏差をカバーしたく考えたとして,初期値に-3を指定しています。

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

[セルA8]=TRUNC(A7+0.01,3)

Step 3density の計算とオプションボタンの配置

以下の関数をもとにxに対応する確率密度関数の値を埋めていきます("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を指定します。

[セルB7]=NORM.S.DIST(A7,FALSE)

開発タブ挿入ボタンをクリックし,フォームコントロールのオプションボタンを選択します。

オプションボタンを見出し「表示選択」の右隣のセルに配置します。なお,ボタンのラベルに関しては,この場合使用に積極的でなければならない理由も見当たらないので,ここでは削除して使いたいと思います。

対象の右クリックを経て,コントロールの書式設定を選択します。

コントルールタブの「リンクするセル」に,セルF4を指定します。

セルC5に配置したオプションボタンを,D, E, F列の同じ行にコピーします。

てきとうに任意のオプションボタンをいくつかクリックしてみて,セルF4の値が追従して変化するかをテストします。

Step 4outer, inner, lower, upper の計算

"outer", "inner", "lower", "upper"列の見出し直下の各セルに,下表の式を入力orコピペ(以下に同じ)します。

C7 =IF(AND($F$4=1, OR(A7<=$C$2, A7>=$C$3)), B7, NA())
D7 =IF(AND($F$4=2, OR(AND(A7>=$C$2, A7<=$C$3))), B7, NA())
E7 =IF(AND($F$4=3, A7<=$C$2), B7, NA())
F7 =IF(AND($F$4=4, A8>=$C$3), B8, NA())

これらの式をこの表の最下行までコピーします。

"outer"のオプションボタンがONのとき,シートは下のような状態となっています。

Step 5オプションボタンの配置

作業の対象を右サイドの表に移します。

見出し「不可視化」の右隣のセルに,先のオプションボタンと同様にしてチェックボックスを配置します。

このとき,「リンクするセル」にはセルJ3を指定します。

チェックボックスを幾度かクリックしてみて,セルJ3の内容がクリックに追従するかを確認します。

Step 6右表側の outer, inner, lower, upper の計算

以下,表に示した式をそれぞれ順に配置します。

I5 =IF(AND(NOT(J3), F4=1), MIN(A:A)-1, NA())
I6 =IF(AND(NOT(J3), F4=1), C2, NA())
I8 =IF(AND(NOT(J3), F4=1), MAX(A:A)+1, NA())
I9 =IF(AND(NOT(J3), F4=1), C3, NA())

I11 =IF(AND(NOT(J3), F4=2), C2, NA())
I12 =IF(AND(NOT(J3), F4=2), C3, NA())

I14 =IF(AND(NOT(J3), F4=3), MIN(A:A)-1, NA())
I15 =IF(AND(NOT(J3), F4=3), C2, NA())

I17 =IF(AND(NOT(J3), F4=4), MAX(A:A)+1, NA())
I18 =IF(AND(NOT(J3), F4=4), C3, NA())

セルJ5に下式を入力し,これを"upper"ブロックまでコピーします。

J5 =IF(ISNUMBER(I5), 0, NA())

直前にコピーした内容について修正を加えます。具体的には,左隣にデータが存在しないセルの内容については不要につき削除します(下図強調箇所)。

これまでの設定に誤りがなければ,「不可視化」チェックボックスをONにしたとき,右サイドの表の内容はすべてN/Aを示します。OFFのときは左サイドの表のオプションボタンで選択したパターンのみ,N/Aが解除されます。

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

Step 7グラフの挿入

左サイドの表の"X"列から"upper"列のデータをすべて選択した状態から,挿入タブ散布図ボタンをクリックし,散布図(平滑線)を選択します。

これにより,シートに下のようなグラフが出力されます。

Step 8曲線とそれの下の領域の描画(1)

任意の系列を選択した状態から「グラフの種類の変更」ダイアログを呼び出して,"density"系列以外をすべて第2軸に振り替えます。

"density"系列以外をすべてに変更します。

グラフ要素を追加から(面グラフ用の)第2横軸を追加しておきます。

Step 9曲線とそれの下の領域の描画(2)

2縦軸の設定画面(軸の書式設定ウインドウ,以下略)を呼び出します。

「横軸との交点」を自動にし…

1縦軸とスケールを合わせます。このときの第1縦軸の状態に照らし,適当となる最小値最大値を指定し(決め)ておきます。

Step 10曲線とそれの下の領域の描画(3)

1縦軸の設定画面に切り替えます。

直前の手順で決めた第2縦軸の最小値最大値と同一の値を据えておきます。

Step 11曲線とそれの下の領域の描画(4)

2横軸の設定画面に切り替えます。

「ラベルの間隔」を「間隔の単位」に切り替え,この値を Step 2で設定した刻みの逆数 に変更します(この例では1/100100)。

Step 12曲線とそれの下の領域の描画(5)

1横軸の設定画面に切り替えます。

グラフに描かれた第2横軸の最小値最大値の値を読みとって(この例では上図にて[-3, 3]と示される=つまりStep2で決めた範囲),これと同一の値を指定します。

Step 13横軸の取捨選択

この時点で第1・第2両横軸が可視であるため,いずれか一方を不可視にする手筈をとりたいと思います。

ただしいずれを選択するかで結果はわずかながら異なっていて,第1横軸を不可視にすれば下図のような,

2横軸を不可視にすれば下図のような状態となります。

このように横軸と実体とを張り付けるか,あるいは分離させるか……といった意味で差を生むことになりますが,このあたりは「読みやすさ」といったものを考えたときの“好み”の問題かと思いますので,その点をふまえたうえで任意のスタイルを選んでいけばOKです。

いずれにしろ,作業そのものはこれへの対応で分岐することになりますので,前者の場合はすぐ次のStepから,後者の場合は Step 15 からの作業となります。

Step 14第2横軸を可視とする場合

2横軸の設定画面に切り替えます。

「軸位置」を目盛に,「目盛の間隔」を刻みの逆数(ここでは100)に指定して…

1横軸の設定画面に切り替えて,「ラベルの位置」をなしにします。

つづいて,「線」を線なしに変更して第1横軸に関係する要素を不可視とします。

……これ以降は Step 16 へジャンプします。

Step 15第1横軸を可視とする場合

2横軸の設定画面に切り替えます。

「軸位置」を目盛に,目盛の種類を「なし」に,さらに「ラベルの位置」をなしにして…

1横軸の設定画面に切り替え,「目盛の種類」を外向きに変更します。

Step 16彩色

オプションボタンでグラフのパターンを切り替えながら,それぞれのパターンについて各系列に対し任意の彩色を施していきます。

ここでは下の一連の図のように彩色を統一しておきたいと思います。

Step 17系列の追加(1)

デザインタブデータの選択ボタンをクリックし,「データソースの選択」ダイアログを呼び出します。

scrollable

左側「凡例項目」の追加ボタンをクリックします。

Step 18系列の追加(2)

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

scrollable

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

Step 19マーカー付き直線散布図へ

直前に作成したあたらしい系列(下図にいう赤色の線)を選択し,「グラフの種類の変更」ダイアログを呼び出します。

当該系列の種類を散布図(直線とマーカー)に変更します。

この系列に関して,マーカーの彩色,あるいは形状サイズなど任意の書式設定を加えます。

Step 20動作のテスト

オプションボタン・チェックボックスの操作に追従するかたちで,シートの内容とグラフが変化することを確認します。

「下側x」「上側x」をグラフの横軸にとった範囲内で(※)てきとうな値に変更し,シートの内容とグラフが追従することを確認します。

※ 裏を返せばこれらを範囲外で指定し,かつ"outer"のパターンとすることで,シート構成の修正を経ることなく一時的に曲線のみの出力とすることが可能です。

Step 21完成

2縦軸を不可視にするなど,残る細かな部分の書式設定をおこなって全工程の完了です。

Step 22Tips

さらに系列を加えるなど重ねてリソースを割けるなら,たとえば下図のように領域の重なる要素を指示するなどといったアレンジも可能かと思います。

その他の参照