BDAstyle

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

ヒストグラムの作成 with Excel Tips3 [正規分布曲線を重ねて描画する]

6.正規分布曲線を加える手続き

Step 0シチュエーションの設定

家電メーカーY社の商品Aに関する,M県での店頭販売価格調査をもとに作成した度数分布表が以下にあります。

初期データ

ここでは,この度数分布表をもとにヒストグラムを作成し加工を施すことにより,正規分布曲線を重ねて描画するのが目的となります。このアプローチとして,次の2つの作例を提示します。

方法1

長所 方法2と比較して 手続きが簡潔
短所 カーブの表現のアラが気になることも(たとえば作例の場合は山裾。あるいは階級の数が少ない場合などに顕著)

方法2

長所 方法1と比較して よりナチュラルなカーブの描写が可能
短所 x軸ラベルはビンの間にのみ表示可能(この作例の場合。ただし工夫により他の選択も可能)

もっとも,いずれの方法も,一定のスパンおいて期待度数を求め,それらを結んで正規分布曲線を形成させる(この意味では近似線です)といった流れにおいては同じです。以下,前段は方法1「折れ線グラフで描画する方法」,後段は方法2散布図で描画する方法」となります。

作図にあたり参考にしたWebサイト・書籍およびExcelワークブック

[方法1]および[方法2]に関して

[方法1]に関して

[方法2]に関して

1.折れ線グラフで描画

Method1-Step 1ヒストグラムの作成と平均・標準偏差の計算

ヒストグラムを用意します。ここでは項目軸に「階級値」(中心値)を表示しています。

またシートの任意の場所で,平均および標準偏差(下図は分母nで計算)を求めておきます。

Method1-Step 2見出しの作成

度数分布表の近傍に下のような見出しの4つの列を確保しておきます。

※なお便宜上,ここでいう上境界は「階級上限」と同義として,下境界は 「階級上限」-interval(階級幅) に同義として扱います(方法2に同じ)。

上境界までのp,下境界までのp,差,差*n

Method1-Step 3期待度数の計算(1)

「上境界までのp」列を最後の階級を除き埋めていきます。

ここでは,同じ行の「階級上限」の値までの累積(下側)確率を求めます。

※この方法では最初と最後の階級の真ん中で正規分布曲線が切れてしまうので,この作例の両端については誤解を避ける意味合いからフロー分すべての確率を含めることとしています。以下Stepの「最後の階級を除き」といった変則的なアプローチはそうした趣旨からのものですが,フロー分を含めないのであれば表中すべてのセルを当該の式で埋めてもかまいません(この場合Step 7の処理は不要です)。

2010 or later =NORM.DIST(F2,$D$8,$D$9,TRUE)
2007 =NORMDIST(F2,$D$8,$D$9,TRUE)

Method1-Step 4期待度数の計算(2)

「下境界までのp」列を 最初の階級および最後の階級を除いて 埋めていきます。

ここでは 同じ行の「階級上限」-階級幅 の値までの累積(下側)確率を求めます。

2010 or later =NORM.DIST(F3-$D$6,$D$8,$D$9,TRUE)
2007 =NORMDIST(F3-$D$6,$D$8,$D$9,TRUE)

Method1-Step 5期待度数の計算(3)

「差」列にて,「上境界までのp」と「下境界までのp」との差をとります。

Method1-Step 6期待度数の計算(4)

「差*n」列にて, 最初の階級および最後の階級を除いて 差をn倍します(これによりヒストグラムとスケールを合わせます)。

Method1-Step 7期待度数の計算(5)

「差*n」列の 最初の階級および最後の階級部分に関して,前者は「上境界までのp」を,後者は1直前の階級の「上境界までのp」との差をそれぞれn倍します。

最初の階級 =I2*D1
最後の階級 =(1-I12)*D1

Method1-Step 8折れ線の組み込み(1)

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

Method1-Step 9折れ線の組み込み(2)

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

「凡例項目(系列)」の追加ボタンをクリックします。

Method1-Step 10折れ線の組み込み(3)

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

「系列名」および「系列値」について,対応する色の領域を指定します(下図)。

この操作を終えたら,OKボタンをクリックします。

Method1-Step 11折れ線の組み込み(4)

再度「データソースの選択」ダイアログに戻ります。

ここではOKボタンをクリックします。

Method1-Step 12折れ線の組み込み(5)

グラフにあたらしく作成された系列(「差*n」)を選択し,デザインタブ「種類」グループのグラフの種類の変更ボタンをクリックします。

Method1-Step 13折れ線の組み込み(6)

「グラフの種類の変更」ダイアログが表示されたら「折れ線」グループの折れ線を選択します。

この操作を終えたのち,OKボタンをクリックします。

Method1-Step 14散布図の組み込み(7)

グラフの「差*n」系列(=折れ線)を選択し,レイアウトタブ「現在の選択範囲」グループの選択対象の書式設定ボタンをクリックします。

Method1-Step 15散布図の組み込み(8)

「データ系列の書式設定」ダイアログが表示されます。線のスタイルグループのスムージングONにして閉じるボタンをクリックします。

Method1-Step 16ヒストグラムの完成

書式を整えて,ヒストグラムの完成です。

2.散布図で描画

Method2-Step 1平均・標準偏差の計算と見出しの作成

あらかじめ平均と標準偏差を求めておき,任意の場所に次のような見出しをもつ3つの列を確保しておきます。

Method2-Step 2期待度数の計算(1)

最初の階級の下限()の値,ないしはそれを僅かに遡る程度の値を初期値として,これを見出し「x」の直下のセルに入力します。

さらにその直下のセルに,初期値に任意の定数を加える式を用意します(定数について,この作例では標準偏差の 1/100と定義しています[下図]。これは仮想的に階級幅を縮めて柱を増やすことで,柱を結ぶラインが正規分布曲線に近似するよう期待する意図があります)。

この式を,最後の階級の上限と等しいか,あるいは僅かに超える値が出現するところまでコピーします。

Method2-Step 3期待度数の計算(2)

「d」列を埋めます。

x時点で引数[関数形式]に"false"を指定したときの,Norm.Dist関数の戻り値を求めます。

2010 or later =NORM.DIST(J2,$D$8,$D$9,FALSE)
2007 =NORMDIST(J2,$D$8,$D$9,FALSE)

Method2-Step 4期待度数の計算(3)

「d*n*intv.」列を埋めます。

見出しそのままの構成で数式をつくります(これによりヒストグラムとスケールを合わせます)。

=K2*$D$1*$D$6

Method2-Step 5散布図で正規分布曲線を作成(1)

ここからはグラフ作成の工程です。おおきな流れとしては,散布図を作成してから(正規分布曲線),棒グラフを重ねていきます(ヒストグラム)。

はじめに,「x」列 および「d*n*intv.」列を選択してから,散布図を作成します[散布図(平滑線)]。

Method2-Step 6散布図で正規分布曲線を作成(2)

横軸について,最初の階級の下限を最小値に,最後の階級の上限を最大値に設定します(図は設定前のもの)。

Method2-Step 7散布図で正規分布曲線を作成(3)

縦軸について,最小値を0に設定します。最大値に関しては,度数分布表における最大度数から適宜判断して値を定めます(図は設定前のもの)。

Method2-Step 8棒グラフの組み込み(1)

つづいて棒グラフを組み込みます。

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

「データソースの選択」ダイアログが表示されるので,「凡例項目(系列)」の追加ボタンをクリックし…

Method2-Step 9棒グラフの組み込み(2)

「系列の編集」ダイアログの「系列名」「系列Xの値」「系列Yの値」を埋めていきます(系列Xには「階級上限」の方を指定)。

この操作を終えOKボタンをクリックすると「データソースの選択」ダイアログに戻るので,再度OKボタンをクリックします。

Method2-Step 10棒グラフの組み込み(3)

グラフの「度数」系列(=あらたに追加された方の系列)を選択し,レイアウトタブ「現在の選択範囲」グループの選択対象の書式設定ボタンをクリックします。

「データ系列の書式設定」ダイアログが表示されるので,「系列のオプション」カテゴリの「使用する軸」を2に変更して閉じるボタンをクリックします。

Method2-Step 11棒グラフの組み込み(4)

「度数」系列の選択を維持したまま,デザインタブ「種類」グループのグラフの種類の変更ボタンをクリックします。

「グラフの種類の変更」ダイアログが表示されるので,「縦棒」グループから集合縦棒を選択しOKボタンをクリックします。

Method2-Step 12棒グラフの組み込み(5)

2縦軸の最小値と最大値を第1縦軸のそれらと合わせます。

また「目盛の種類」「軸ラベル」をなしにして不可視とします。

Method2-Step 13棒グラフの組み込み(6)

必要であれば目盛間隔を設定します(たとえば下1番目のグラフではデフォルトの状態で目盛間隔が2000 となりました。これが好ましくない場合,適宜を指定します[2番目の図は例として1000に変更するときの状態])。

Method2-Step 14ヒストグラムの完成

その他書式などを調整して,ヒストグラムの完成です。

その他の参照