はじめに
Excelには様々な統計関数が用意されていますが、NORM.INV関数（および以前のNORMINV関数）は、正規分布（ガウス分布）の逆関数を計算する際に非常に役立ちます。具体的には、「ある確率以下の値は何か？」という問いに答えることができます。例えば、テストの点数分布が正規分布に従う場合に、「上位20%に入るためには何点以上必要か？」といった問題を解決できます。この記事では、NORM.INV関数の基本的な使い方から、画像にある例のような応用までを詳しく解説します。
正規分布とは？
正規分布は、平均値を中心として左右対称の釣鐘型の分布です。平均値と標準偏差という2つのパラメータによって完全に決定されます。
- 平均値 (μ): 分布の中心を表します。
- 標準偏差 (σ): 分布の広がり具合を表します。標準偏差が大きいほど、分布は広くなります。
NORM.INV関数とは？
NORM.INV関数は、指定された確率に対応する正規分布の値を返します。これは、NORM.DIST関数の逆の操作を行います。以前のExcelバージョンではNORMINV関数が使用されていましたが、現在はNORM.INVが推奨されています。
基本的な構文
Excel
各引数の意味は以下の通りです。
- 確率（必須）: 求めたい確率を指定します。0から1の間の数値を指定します。
- 平均（必須）: 正規分布の平均値を指定します。
- 標準偏差（必須）: 正規分布の標準偏差を指定します。
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
画像例の解説
画像では、以下の設定でNORM.INV関数を使用しています。
- 累積確率: セルA2に0.2（20%）が入力されています。これは、「上位20%」を求めるために、下位80%（1-0.2=0.8）に対応する値を求めることを意味します。ただし、NORM.INVは累積確率（下から数えた確率）を受け取るため、ここでは0.2を使用しています。
- 平均 (μ): セルB2に70が入力されています。これは、テストの平均点が70点であることを意味します。
- 標準偏差 (σ): セルC2に10が入力されています。これは、テストの標準偏差が10点であることを意味します。
- 数式: セルD2には以下の数式が入力されています。
Excel
この数式を分解して解説します。
▶ 下位の境界値の式（D2）を、上の練習ツールで試す ／ 上位の境界値の式（E2）を、上の練習ツールで試す
数式の動作
この数式は、「平均70点、標準偏差10点の正規分布において、下位20%の境界値」を計算します。つまり、この値より小さい得点を取った人は下位20%に入ることを意味します。逆に言えば、この値以上の得点を取った人は上位80%に入ります。画像では、上位20%に入る得点を求めたいので、下位80%の境界値を求めるために、1-0.2 = 0.8 を確率として使用する必要があります。しかし、この画像では直接0.2を使用しているため、計算結果は下位20%の境界値となっています。正しくは、上位20%を求める場合は、以下のように計算します。
Excel
この場合、1-0.2 = 0.8 が確率として使用され、結果は約78.41となります。つまり、約78.41点以上取れば上位20%に入ることになります。
Excelサンプルデータのダウンロード
上記画像のエクセルサンプルデータを、以下のリンクからダウンロードし、練習用として活用ください。
例：身長の分布
例えば、ある地域の男性の身長が平均170cm、標準偏差5cmの正規分布に従うとします。このとき、上位10%に入るためには何cm以上必要かは、=NORM.INV(1-0.1,170,5) または =NORM.INV(0.9,170,5) で計算できます。
NORM.INV／NORMINV関数の注意点
- 確率は0より大きく1より小さい数値でなければなりません。
- 標準偏差は0より大きい数値でなければなりません。
- 引数に数値以外の値を指定すると、エラー値 #VALUE! が返されます。
古い関数NORMINVについて
以前のバージョンのExcelでは、NORMINVという関数が使用されていましたが、これは互換性のために残されているだけで、最新のExcelではNORM.INVを使用することが推奨されています。NORMINVとNORM.INVの機能は基本的に同じです。
まとめ
NORM.INV関数は、正規分布における上位または下位のパーセンテージの境界値を計算する際に非常に役立つ関数です。テストの分析、品質管理、金融など、様々な分野で活用できます。特に、上位パーセンテージを求める場合は、1-確率 を使用することを覚えておきましょう。この関数を理解し、適切に活用することで、データ分析の幅を広げることができるでしょう。
この解説で、NORM.INV／NORMINV関数について、より深く理解できたかと思います。この情報を活用し、Excelでのデータ分析をより効率的に行いましょう。
確率分布・統計検定に関するExcel関数の全体像は、こちらのまとめで整理しています。
正規分布の確率を求めるときは、NORM.DIST関数の使い方もあわせてご覧ください。
コメント