ヒストグラムの作図Tips 3/7[正規分布曲線を重ねる]
ヒストグラムと正規分布曲線とを照らし合わせたい
Step 0シチュエーションの設定
機械メーカーXはある部品の国外調達を検討しています。その一端としてXは,この部品を所有する国内の協力会社Yから300単位を買い入れて,性能評価をおこないました。
下表・左はそのスコア,同・右はスコアの度数分布表です。
DL
ここでは,この度数分布表をもとにヒストグラムを作成したうえで,さらに正規分布曲線を重ねて描画することを目的とします。成果物としては,得手不得手の異なる次の2つの作例(type)を提示します。
プラス面 |
グラフの構造が単純(第2軸は使用しない) 作図ミスを誘いにくい |
---|---|
マイナス面 | カーブの描写にアラが出やすい(山頂や山すそ。あるいは階級の数が少ない場合などに顕著) |
プラス面 |
より自然なカーブの描写が期待できる 横軸ラベルを境界値で配置できる |
---|---|
マイナス面 | グラフの構造が複雑(第2軸を使用する) |
もっとも,いずれの方法も一定のスパンおいて期待度数を求め,それらを結んで正規分布曲線を形成させる(この意味では近似線です)といった流れにおいては同じです。以下,前段はtype 1「折れ線依存の描画法」,後段はtype 2「散布図依存の描画法」となります。
作図にあたり参考にしたWebページ・書籍およびExcelワークブック
[type1]および[type2]に関して
- [Web]正規分布へのあてはめ ―"統計学自習ノート"(2014.9 閲覧)
[type1]に関して
- 篠塚 充(2011)『Excel2010/2007限定版 データを「見える化」する Excelグラフ大事典』C&R研究所, pp.279-283.
[type2]に関して
- [xls]Histogram With Normal Curve ―"Mike Middleton"(2014.9 閲覧)
工程
折れ線グラフで描画
type1-Step 1ヒストグラムの作成と平均・標準偏差の計算
ヒストグラムをversion 2016以降の新グラフ以外の方法で用意します。ここでは横軸に「階級値」(中心値)を表示しています。
またシートの任意の場所で,A列のデータより平均および標準偏差(下図は分母n-1で計算)を求めておきます。
type1-Step 2見出しの用意
度数分布表,「度数」見出し直右のセルから下のような見出しの4列を確保しておきます。
- 下限までのp
- 上限までのp
- 差
- 差*n
※なおここでいう下限(「階級上限」-「interval」(階級幅))は下境界と同義として,上限は上境界に同義として扱います(type2に同じ)。
type1-Step 3期待度数の計算(1)
「下限までのp」列を最初の階級を除き※埋めていきます。
すなわち,同じ行の「階級上限」-階級幅 の値までの下側(累積)確率を求めます。
※type 1の描画法では仕様上,最初・最後の階級の真ん中で正規分布曲線が切れてしまいます。誤解を招くやもしれないので,この作例の両端についてはフロー分すべての確率を含めることにしたいと思います。以下Stepの「~の階級を除き」といった変則的なアプローチはそうした趣旨からのものですが,フロー分を含めないのであれば表中すべてのセルを当該の式で埋めてもかまいません(この場合Step 7の処理は不要です)。
I3 | =NORM.DIST(F3-$D$7, $D$9, $D$10, TRUE) |
---|
scrollable
type1-Step 4期待度数の計算(2)
「上限までのp」列を 最後の階級を除いて 埋めていきます。
すなわち,同じ行の「階級上限」の値までの下側(累積)確率を求めます。
J2 | =NORM.DIST(F2, $D$9, $D$10, TRUE) |
---|
scrollable
type1-Step 5期待度数の計算(3)
「差」列にて,「上限までのp」と「下限までのp」との差をとります。
K3 | =J3-I3 |
---|
scrollable
type1-Step 6期待度数の計算(4)
「差*n」列にて, 最初の階級および最後の階級を除いて 差を n(:サイズ)倍します(これによりヒストグラムとスケールを合わせます)。
L3 | =K3*$D$2 |
---|
scrollable
type1-Step 7期待度数の計算(5)
「差*n」列の 最初の階級および最後の階級部分について,前者は「上限までのp」を,後者は1と「下限までのp」との差をそれぞれn倍します。
L2 | =J2*D2 |
---|---|
L14 | =(1-I14)*D2 |
scrollable
type1-Step 8折れ線の組み込み(1)
グラフをアクティブにした状態から,デザインタブ「データ」グループのデータの選択ボタンをクリックします。
type1-Step 9折れ線の組み込み(2)
「データソースの選択」ダイアログが表示されます。
「凡例項目(系列)」の追加ボタンをクリックします。
type1-Step 10折れ線の組み込み(3)
「系列の編集」ダイアログが表示されます。
「系列名」および「系列値」について,前者はさしずめ「正規曲線」「密度関数」などの任意の名称をダイレクトに入力し,後者は見出しを除く「差*n」列のデータ範囲を指定します。
この操作を終えたら,OKボタンをクリックします。
type1-Step 11折れ線の組み込み(4)
再度「データソースの選択」ダイアログに戻ります。
そのままOKボタンを返します。
type1-Step 12折れ線の組み込み(5)
Step 10-11でグラフにあたらしく加えた系列(下図にいうオレンジの柱)を選択し
デザインタブ「種類」グループのグラフの種類の変更ボタンをクリックします。
type1-Step 13折れ線の組み込み(6)
「グラフの種類の変更」ダイアログが表示されます。
直前に選択した方の系列(オレンジ色の系列)の「グラフの種類」を折れ線に変更します。
この操作を終えて,OKボタンを返します。
type1-Step 14折れ線の組み込み(7)
グラフより,折れ線の系列を選択(クリック)し
書式タブ「現在の選択範囲」グループの選択対象の書式設定ボタンをクリックします。
type1-Step 15折れ線の組み込み(8)
「データ系列の書式設定」ウインドウが表示されます。
線グループの設定項目スムージングをONにします。
type1-Step 16ヒストグラムの完成
その他必要な書式を整えて,ヒストグラムの完成です。
散布図で描画
type2-Step 1平均・標準偏差の計算と見出しの用意
A列のデータよりあらかじめ平均と標準偏差を求めておき,任意の場所に次のような見出しをもつ3つの列を確保しておきます。
- x
- d(…確率密度関数[ただしxのときのNorm.Dist関数の戻り値])
- d*n*intv.(…d×n×階級の幅)
scrollable
type2-Step 2期待度数の計算(1)
最初の階級の下限(※)の値,ないしはそれを僅かに遡る程度の値を初期値として,これを見出し「x」の直下のセルに入力します。
さらにその真下のセルに,初期値に任意の定数を加える式を用意します(定数について,この作例では 標準偏差の1/100 と定義しています[下図]。これは仮想的に階級幅を縮めて柱を増やすことで,柱を結ぶラインが正規分布曲線により近似できるよう期待する意図があります)。
この式を,最後の階級の上限と等しいか,あるいは僅かに超える値が出現するところまでコピーします(下表のデータだと600行超を消費します)。
J2 | =J2+$D$10/100 |
---|
scrollable
type2-Step 3期待度数の計算(2)
「d」列を埋めます。
x時点で引数[関数形式]に"false"を指定したときの,Norm.Dist関数の戻り値を求めます。
K2 | =NORM.DIST(J2, $D$9, $D$10, FALSE) |
---|
scrollable
type2-Step 4期待度数の計算(3)
「d*n*intv.」列を埋めます。
見出しそのままの構成で数式をつくります(これによりヒストグラムとスケールを合わせます)。
L2 | =K2*$D$2*$D$7 |
---|
scrollable
type2-Step 5散布図で素地を作成(1)
シートが準備できたところで作図のフェーズに移ります。おおきな流れとしては,散布図を作成してから(正規分布曲線),棒グラフを重ねていきます(ヒストグラム)。
はじめに,「x」列 および「d*n*intv.」列の見出しを含むデータ範囲を選択してから,散布図を作成します[散布図(平滑線)]。
type2-Step 6散布図で素地を作成(2)
グラフの横軸について,度数分布表の最初の階級の下限(「階級上限」ー「interval」)を最小値に,最後の階級の上限を最大値に設定します(図は設定前のもの)。
scrollable
type2-Step 7散布図で素地を作成(3)
作図ミスを防ぐ意味から,グラフの縦軸の最大値と最小値をあえて明示的に設定します。
具体的には,最小値には0を指定し,最大値については度数分布表における最大度数から適宜判断して値を定めます(このとき,軸の最大値 > 最大度数)。
type2-Step 8棒グラフの組み込み(1)
棒グラフを組み込みます。
グラフをアクティブにした状態から,デザインタブ「データ」グループのデータの選択ボタンをクリックします。
「データソースの選択」ダイアログが表示されるので,「凡例項目(系列)」の追加ボタンをクリックし
type2-Step 9棒グラフの組み込み(2)
「系列の編集」ダイアログの「系列名」「系列Xの値」「系列Yの値」を埋めていきます。
- 系列名:「ヒストグラム」とダイレクトに入力
- 系列Xの値:「階級上限」列の見出しを除くデータ範囲を指定
- 系列Yの値:「度数」列の見出しを除くデータ範囲を指定
この操作を終えOKボタンをクリックすると「データソースの選択」ダイアログに戻るので,再度OKボタンを返しておきます。
type2-Step 10棒グラフの組み込み(3)
グラフの「ヒストグラム」系列(=あらたに追加した側の系列。下図にいうオレンジの線)を選択し
デザインタブ「種類」グループのグラフの種類の変更ボタンをクリックします。
type2-Step 11棒グラフの組み込み(4)
「グラフの種類の変更」ダイアログが表示されます。
ここで系列「ヒストグラム」の第2軸のチェックボックスをONにしてから(先),「グラフの種類」を集合縦棒に変更します(後)。
これらすべて終えてからOKボタンを返します。
type2-Step 12棒グラフの組み込み(5)
第1縦軸の最大値と最小値と,第2縦軸の最大値と最小値の対応を確認し,これが合っていなかったら第2縦軸側の最大値と最小値を(第1縦軸と同じものに)修正します。
また,第2縦軸の「線」をなしにし
「ラベルの位置」をなしにしてこの軸を不可視とします。
type2-Step 13棒グラフの組み込み(6)
必要であれば目盛間隔を設定します(たとえば下1番目のグラフではデフォルトの状態で目盛間隔が2000となりました。これが好ましくない場合,適宜値を指定します[2番目の図は例として1000に変更するときの状態])。
type2-Step 14ヒストグラムの完成
その他必要な書式を整えて,ヒストグラムの完成です。
Next
その他の参照
このサイトの関連How-toです。
メインサイト「ひとりマーケティングのためのデータ分析」の関連How-toです。