Excelで正規分布を計算・グラフ化する方法｜NORM.DIST関数の使い方を図解

実務・ツール系

　Excelで正規分布を扱いたいとき、やりたいことは大きく2つに分かれます。「〜以下になる確率を出したい」と「釣り鐘型のグラフを描きたい」です。この記事では、NORM.DIST関数での確率計算からグラフ作成までを、なぜその手順になるのかの理由つきで解説します。必要なのは平均と標準偏差の2つだけです。

　なお「そもそも正規分布ってどんな分布？」という段階の方は、先に 正規分布とは？68-95-99.7ルール・偏差値・標準化までやさしく解説 を読んでおくと、この記事の内容がすっと入ってくるはずです。

1. 準備：必要なのは平均と標準偏差の2つだけ

　正規分布の計算に必要なデータは、たった2つです。平均と標準偏差。この2つさえ分かっていれば、Excelは分布全体を再現できます。逆に言えば、この2つが決まった時点で山の形はもう決まっているということですね。

手元に生データがある場合は、次の2つの関数で先に出しておきましょう。

求めるもの関数入力例
平均AVERAGE=AVERAGE(A2:A101)
標準偏差STDEV.S=STDEV.S(A2:A101)

標準偏差の関数には STDEV.S と STDEV.P の2種類があります。手元のデータが「調べたい対象全体」ではなく「そこから抜き出した一部」なら STDEV.S を使ってください。 実務で扱うデータはほぼ後者です。100個を測って製造ライン全体の品質を判断する、というような場面ですね。この使い分けは 分散と標準偏差の記事 で詳しく扱っています。

📌 この記事で使う例
ある充填ラインの製品重量を題材にします。
　・平均：500 g
　・標準偏差：2 g
　・規格：497 g 以上 503 g 以下

2. NORM.DIST関数で「〜以下になる確率」を出す

引数の意味

正規分布の確率を出す関数が NORM.DIST です。書き方はこうです。

=NORM.DIST(x, 平均, 標準偏差, 関数形式)

4つの引数がそれぞれ何を指しているかを整理します。

引数入れるもの今回の例
x知りたい境界の値498
平均分布の中心500
標準偏差ばらつきの大きさ2
関数形式TRUE または FALSETRUE

では「重量が498g以下になる確率」を出してみましょう。

=NORM.DIST(498, 500, 2, TRUE)
→ 0.1587

約15.9%です。1,000個作れば約159個が498g以下になる、という意味になります。

TRUE と FALSE の違い

ここが最初のつまずきどころです。 4番目の引数を切り替えると、返ってくる数字の意味がまるで変わります。

指定返ってくるもの使う場面
TRUEx以下になる確率（曲線の左側の面積）確率を知りたいとき
FALSEその点の確率密度（曲線の高さ）グラフを描くとき

TRUEは面積、FALSEは高さ。まずはこの違いだけ押さえておいてください。

図1：4番目の引数で返ってくるものが変わる。TRUEは「x以下の面積（＝確率）」、FALSEは「その点の高さ（＝確率密度）」

　なぜこう分かれているかというと、正規分布のような連続的なデータでは「ちょうど498gになる確率」を考えられないからです。498.0000…gにぴったり一致する確率は、どこまでも小数を刻んでいくと限りなくゼロに近づいてしまいます。だから確率は必ず「範囲」で考え、その範囲の面積として求めます。TRUEが返すのはこの面積です。

⚠️ よくある間違い
確率が欲しいのに FALSE を入れてしまうと、0.12 のような一見それらしい数字が返ってきます。エラーにならないので気づきにくく、私も一度やらかしました。「確率＝TRUE」だけは指が覚えるまで意識してください。

例題：規格の下限を下回る確率

規格の下限は497gでした。これを下回る確率を出します。

=NORM.DIST(497, 500, 2, TRUE)
→ 0.0668

約6.7%。この分だけ規格外の軽い製品が出る計算になります。

3. 「〜以上」と「〜〜の範囲」の確率を出す

　NORM.DIST が返すのは常に「x以下」の確率です。それ以外を知りたいときは、少し工夫します。といっても足し算引き算だけなので身構えなくて大丈夫です。

図2：「以上」は全体（1）から引く、「範囲」は大きいほうの面積から小さいほうの面積を引く

「〜以上」は 1 から引く

確率の合計は必ず1（100%）です。ですから「503g以上」は、全体から「503g以下」を引けば求まります。

=1 - NORM.DIST(503, 500, 2, TRUE)
→ 1 - 0.9332 = 0.0668

約6.7%です。平均から左右に同じ距離だけ離れているので、さきほどの「497g以下」と同じ値になりました。正規分布が左右対称であることの確認にもなっていますね。

「範囲」は引き算する

「497gから503gの範囲に収まる確率」、つまり規格内に入る確率です。これは大きいほうの面積から小さいほうの面積を引くと求まります。

=NORM.DIST(503, 500, 2, TRUE) - NORM.DIST(497, 500, 2, TRUE)
→ 0.9332 - 0.0668 = 0.8664

約86.6%が規格内に収まる計算です。裏を返せば13.4%が規格外ということで、実務ならここから工程の見直しを検討することになります。数字にすると、感覚で「まあ大丈夫だろう」と思っていたラインの実態が見えてくるのがおもしろいところです。

4. 計算結果が合っているか確かめる2つの方法

関数は入力を間違えても律儀に数字を返してきます。引数の順番を取り違えていても、それらしい値が出てしまうのが厄介なところです。出した数字を鵜呑みにする前に、次の2つで確かめておくと安心です。

方法1：68-95-99.7ルールで概算と照合する

正規分布には、平均からの距離と確率の関係に有名な目安があります。

範囲収まる割合
平均 ± 1σ約 68%
平均 ± 2σ約 95%
平均 ± 3σ約 99.7%

今回の例で確かめてみましょう。標準偏差は2gなので、497〜503gは平均±3g、つまり±1.5σの範囲です。1σ（68%）と2σ（95%）の間にあるはずですね。計算結果は86.6%でした。ちゃんと68%と95%の間に収まっています。

この照合は暗算でできるので、計算するたびにやる習慣をつけておくと入力ミスにすぐ気づけます。詳しい仕組みは 68-95-99.7ルールの解説 を参照してください。

方法2：標準正規分布表（z表）と突き合わせる

もう一段しっかり確かめるなら、z表を使います。まず値をz得点に変換します。

=STANDARDIZE(498, 500, 2)
→ -1

z = −1 のときの確率をz表で引くと 0.1587。NORM.DIST で出した値と一致しました。

Excelだけで完結させたい場合は NORM.S.DIST でも同じ確認ができます。

=NORM.S.DIST(-1, TRUE)
→ 0.1587

5. 釣り鐘型のグラフを作る

ここからはグラフです。3ステップで作れます。

図3：x列を作る → NORM.DIST(FALSE) で高さを出す → 散布図（平滑線）で描く、の3ステップ

手順1：xの列を作る

　まず横軸になる値を並べます。平均 ± 4σ の範囲を目安にしてください。今回なら 500 ± 8 なので、492 から 508 までです。

なぜ±4σかというと、この範囲の外側には全体の0.01%程度しか存在せず、描いてもほぼ平らな線にしかならないからです。狭すぎると裾が切れた不自然な形になり、広すぎると山が潰れて見えます。

刻み幅は0.5gにします。492, 492.5, 493 …と入力するとA列に33行できます。

💡 刻み幅の決め方
おおよそ30〜50点になるように設定すると、なめらかで扱いやすいグラフになります。点が少なすぎるとカクカクし、多すぎても見た目は変わりません。

手順2：NORM.DIST を FALSE で入れる

B列に、各xに対応する高さを出します。

=NORM.DIST(A2, $E$1, $E$2, FALSE)

ここでFALSEを使うのが最大のポイントです。 グラフに必要なのは各点の「高さ」であって、「そこまでの面積」ではありません。TRUEを入れると、右上がりのS字カーブ（累積分布）が描かれてしまいます。釣り鐘型にはなりません。

平均（E1）と標準偏差（E2）はコピーしても動かないよう $ で固定しておくと楽です。あとは下方向にコピーするだけですね。

手順3：散布図（平滑線）で描く

A列とB列を選択して、グラフの種類から散布図（平滑線）を選びます。

折れ線グラフではなく散布図を選ぶ理由は、横軸の扱いが違うからです。折れ線グラフは横軸を「1番目、2番目、3番目…」という順番として扱います。一方の散布図は横軸を数値そのものとして扱います。正規分布は横軸が連続した数値の分布なので、散布図でないと横軸の目盛りが正しくなりません。刻み幅を不均等にした場合は特に差が出ます。

うまくいかないときの3つの確認点

グラフが思った形にならないときは、次の順に確認してください。だいたいこの3つのどれかです。

  1. S字カーブになった → NORM.DIST の4番目がTRUEになっています。FALSEに変えてください
  2. 横軸の数値がおかしい → 折れ線グラフを選んでいます。散布図（平滑線）に変更してください
  3. 山が画面の端に寄っている・平らに見える → xの範囲が平均からずれているか、広すぎます。平均±4σに設定し直してください

6. 逆引き：確率から値を求める（NORM.INV）

ここまでは「値 → 確率」でした。逆に「確率 → 値」を求めたい場面もあります。上位5%に入る境界はいくつか、といった問いですね。

使うのは NORM.INV です。

=NORM.INV(0.95, 500, 2)
→ 503.29

下から95%にあたる値が503.29gだと分かりました。つまり503.29gより重い製品が上位5%です。

実務では、規格値を決めるときにこの逆引きが効いてきます。「不良を1%以内に抑えたいなら、規格をどこに置けばいいか」を数字で出せるからです。感覚で決めていた基準値に根拠を持たせられるようになります。

よくある質問（FAQ）

Q1. NORM.DIST と NORMDIST は違いますか？

計算結果は同じです。NORMDIST（ピリオドなし）はExcel 2007以前の古い関数で、互換性のために残されています。これから書くなら NORM.DIST を使ってください。 古いファイルを開いたときに NORMDIST が出てきても、そのままで動作します。

Q2. NORM.DIST と NORM.S.DIST はどちらを使えばいいですか？

元の単位のまま計算したいなら NORM.DIST、すでにz得点に変換済みなら NORM.S.DIST です。

NORM.S.DIST は平均0・標準偏差1の標準正規分布専用なので、引数に平均と標準偏差を渡しません。手元にg単位やcm単位の生データがあるなら、変換の手間がない NORM.DIST のほうが早いです。

Q3. データが正規分布に従っているか確かめる方法はありますか？

簡易的には、ヒストグラムを描いて釣り鐘型になっているかを目視で確認します。より厳密にやるなら正規性の検定という手法がありますが、まずは形を見るだけでも判断材料になります。

なお、正規分布に従わないデータでも統計解析ができないわけではありません。対数変換で正規分布に近づくデータもありますし、正規分布を前提としない手法もあります。標本と母集団 の考え方を押さえておくと、この判断がしやすくなります。

まとめ

Excelで正規分布を扱うポイントを整理します。

  • 必要なのは平均と標準偏差の2つだけ
  • 確率を出すのは =NORM.DIST(x, 平均, 標準偏差, TRUE)
  • 「以上」は 1から引く、「範囲」は 2つの結果を引き算する
  • 出した数字は 68-95-99.7ルールで概算と照合しておく
  • グラフを描くときは4番目を FALSE にして、散布図（平滑線）で表示する
  • 確率から値を逆引きするときは NORM.INV

TRUEとFALSEの使い分けさえ押さえてしまえば、あとは目的に応じて引き算するだけです。正規分布そのものの考え方をもう少し知りたい方は、正規分布の基礎解説 もあわせてどうぞ。

コメント

タイトルとURLをコピーしました