BDAstyle

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

ヒストグラムの作図Tips 3/7[正規分布曲線を重ねる]

ヒストグラムと正規分布曲線とを照らし合わせたい

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

機械メーカーXはある部品の国外調達を検討しています。それにあたってXは,この部品をすでに国内で所有する協力会社Yから300個を買い入れて,その性能について評価をしました。

下表・左はそのスコア,同・右はスコアの度数分布表です。

DL

元データと度数分布表

ここでは,この度数分布表をもとにヒストグラムを作成したうえで,さらに正規分布曲線を重ねて描画することを目的とします。成果物としては,得手不得手の異なる次の2つの作例(type)を提示します。

エクセル ヒストグラム 正規分布曲線 type1

利点 グラフの構造が単純(第2軸は使用しない)
作図ミスを誘いにくい
短所 カーブの描写にアラが出やすい(山頂や山すそ。あるいは階級の数が少ない場合などに顕著)

エクセル ヒストグラム 正規分布曲線 type2

利点 より自然なカーブの描写が期待できる
横軸ラベルを境界値で配置できる
短所 グラフの構造が複雑(第2軸を使用する)

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

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

[type1]および[type2]に関して

[type1]に関して

[type2]に関して

工程

折れ線グラフで描画

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

ヒストグラムを version 2016 の新グラフ以外の方法で用意します。ここでは横軸に「階級値」(中心値)を表示しています。

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

mean,sd

type1-Step 2見出しの用意

度数分布表,「度数」見出し直右のセルから下のような見出しの4列を確保しておきます。

  • 下限までのp
  • 上限までのp
  • 差*n

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

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

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

「下限までのp」列を最初の階級を除き埋めていきます。

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

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

I3 =NORM.DIST(F3-$D$7, $D$9, $D$10, TRUE)

scrollable

「下限までのp」列

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

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

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

J2 =NORM.DIST(F2, $D$9, $D$10, TRUE)

scrollable

「上限までのp」列

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

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

K3 =J3-I3

scrollable

「差」列

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

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

L3 =K3*$D$2

scrollable

「差*n」列

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

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

L2 =J2*D2
L14 =(1-I14)*D2

scrollable

「差*n」列

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ヒストグラムの完成

その他必要な書式を整えて,ヒストグラムの完成です。

histogram with normalcurve type1

散布図で描画

type2-Step 1平均・標準偏差の計算と見出しの用意

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

  • x
  • d(…確率密度関数[ただしxのときのNorm.Dist関数の戻り値]
  • d*n*intv.(…d×n×階級の幅

scrollable

mean,sd,x,d,d*n*intv.

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

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

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

この式を,最後の階級の上限と等しいか,あるいは僅かに超える値が出現するところまでコピーします(下表のデータだと600行超を消費します)。

J2 =J2+$D$10/100

scrollable

「x」列

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

「d」列を埋めます。

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

K2 =NORM.DIST(J2, $D$9, $D$10, FALSE)

scrollable

「d」列

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

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

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

L2 =K2*$D$2*$D$7

scrollable

「d*n*intv.」列

type2-Step 5散布図で素地を作成(1)

シートが準備できたところで作図のフェーズに移ります。おおきな流れとしては,散布図を作成してから(正規分布曲線),棒グラフを重ねていきます(ヒストグラム)。

はじめに,「x」列 および「d*n*intv.」列の見出しを含むデータ範囲を選択してから,散布図を作成します[散布図(平滑線)]。

「x」列「d*n*intv.」列の選択

type2-Step 6散布図で素地を作成(2)

グラフの横軸について,度数分布表の最初の階級の下限(「階級上限」ーinterval)を最小値に,最後の階級の上限を最大値に設定します(図は設定前のもの)。

scrollable

第1横軸min,maxの設定

type2-Step 7散布図で素地を作成(3)

作図ミスを防ぐ意味から,グラフの縦軸の最大値と最小値をあえて明示的に設定します。

具体的には,最小値には0を指定し,最大値については度数分布表における最大度数から適宜判断して値を定めます(このとき,軸の最大値>最大度数)。

第1縦軸min,maxの設定

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縦軸と同じものに)修正します。

Y軸のスケールを合わせる

また,第2縦軸の「線」をなしにし……

「軸の書式設定」ウインドウ

「ラベルの位置」をなしにしてこの軸を不可視とします。

「軸の書式設定」ウインドウ

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

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

目盛間隔:2階級分

「軸の書式設定」ウインドウ

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

その他必要な書式を整えて,ヒストグラムの完成です。

histogram with normalcurve type2

Next

次頁は 区間の幅や数を柔軟に変更可能なテンプレートを作成するための手続きです。

その他の参照