正規分布曲線を描く

目的

  • 統計の基本である正規分布の考え方を理解し、グラフで表せるようになる。

使用するファイル

  • 前回のExcelファイルを引き続き使用します。
  • 新しい内容をする際は、下のように、Execl左下の「+」ボタンを押して新しいシートを作ります。

  • また「Sheet 2」という部分をダブルクリックすると、シートの名前を変更できます。新しく作ったシートの名前を「①正規」と入力し、enterを押してください。

正規分布

  • 今作ったシートに、テキストp.156の血圧例を参考に下のような正規分布のグラフを書きます。

  • グラフを書くのに必要なのは、各点の座標です。縦横それぞれの座標が決まれば、その点を繋ぐとグラフになります。では、正規分布に必要な座標は何でしょうか?

  • 血圧の正規分布グラフは、各値が生じる確率を示すものだから、x軸は血圧で、y軸はその値が生じる確率です
  • x軸の血圧の値は、平均のプラスマイナス32とみなし、86~150cmとします。では、高さとなるy軸の確率はどうすれば分かるでしょうか?

  • Excelでは、その値を求めるために norm.dist という関数を使います。これは NormalDistribution (正規分布)の略です。
  • 正規分布のグラフの形は、平均と標準偏差で決まります。この関数に、「ある血圧(x)、平均、標準偏差」を引数として与えると、そのxが生じる確率を返してくれます。

  • 以下では、グラフを描くための準備として、下のようにデータを入力しましょう。
  • 正規分布の形を決めるのに必要なのは、平均と標準偏差です。テキストの例では、平均118、標準偏差は8.67とされています。また、x軸の値をB6セルから縦に86から150まで用意します。

  • 次に「各血圧値に何%の人が該当するか(つまり血圧 x ごとのy座標の高さ)」を求めます。
  • まずは、血圧が86になる確率を求めます。
    norm.distという関数に、「x、平均、標準偏差」の三つを与えると、その x が生じる確率を教えてくれます。
  • C6セルにその確率を表示させるので、C6セルをクリックします。
  • そして、「=norm.dist 」の最初の何文字かを入力すると、下のように関数候補の一覧が出てきます。ここでは、矢印の上下キーで使う関数に合わせ、「Tab」キーで選択します

  • すると、下のように関数の解説が表示されるので、この順番で引数を入力します。なお、数字を入力するのではなく、該当するセルをクリックしてセル番号で指定します。また、各引数の間は半角の「,」(カンマ)で繋ぎます。
     x    : 86が入っているB6セル
     平均  : 該当するセル
     標準偏差: 該当するセル
     関数形式: 今回は関係ないので何も入力しない

  • 最終的に下の数式バーのようになるはずです。なお、ここでは最後のカンマを忘れないように。合っていれば、enterを押します。

  • ちゃんと処理できれば、下のようになるはずです。これは、確率が「5.067の10のマイナス5乗」という意味で、「0.0000506」ということです。つまり「血圧は約0.005%の確率で86になる」ということです。

  • では続いて、87から150まで、それぞれの確率を求めます。
    ただし、C6セルをC7セルにコピーペーストしたり、オートフィルを使おうとすると、下のようなエラーになります。

  • なぜエラーになるのか、C7セルをクリックしてみましょう。
  • 原因は、下のように関数の引数が(B7,C3,C4,)となっていることです。本来は、(B7,C2,C3)が正しい式です。これはエクセルが勝手に、「計算式を入れるセルがC6からC7に一つずれたので、関数内の引数の位置も一つずつずれているだろう」と解釈したからです
  • では、この間違った式を修正しましょう。下のように、計算式の各引数の文字とそれが該当するセルにはそれぞれ色が付いています。まず、C3の茶色の枠をC2に動かします。このようなときは、C3セルを縁取っている茶色の枠線をC2セルにドラッグします。同じように、C4の紫の枠をC3セルにドラッグします。正しく修正できれば、Enterキーを押します。

  • すると、下のように正しく計算結果が表示されました。

  • では次に、毎回修正するのは面倒なので、オートフィルでセルがずれない方法を練習します。
  • 下の赤線部分の引数を見てください。先ほどの例では、C7セルの引数に「C2, C3」と書いていましたが、今回は「$C$2, $C$3」と書いてあります。
  • このように、セル番号の行と列の前に「$」(ドルマーク)を付ける書き方を「絶対参照」と呼びます。セル番号を絶対参照で指定すれば、そのアドレスは固定されます。すると、コピーやオートフィルを使っても、Excelがセル番号を勝手に変更することはなく、もとの番号がそのまま使われます。
  • なお手で「$」を入力するのが面倒であれば、数式バーの対象セルをクリックして「F4キー」を押すのでもOKです。
  • さて、C7セルを絶対参照で書いたら、オートフィルで一気に下まで片付けましょう。

  • 全ての確率を求めると、下のようになります。

  • これで、各xy座標が分かったので、これらの点を繋ぐグラフを書きます。これは簡単です。
  • グラフにするデータのどこかのセルをクリックします。そして「挿入」タブの「グラフ」にある「散布図」を選びます。

  • すると、下のように正規分布のグラフができました。左右対称の山形グラフになっているはずです。
  • あとは必要に応じて、見やすく加工しましょう。

練習課題

  • 見やすい資料を作成したら、ファイル名は同じまま、上書き保存してください。

情報科学