Excelでデータを正規分布に変換する2つの簡単な方法

この記事では、Excelでデータを正規分布に変換する方法を詳しく解説します。統計学において、正規分布の概念を理解することは、データセット内の単一または複数のデータがどの位置にあるかを把握するために非常に重要です。正規分布の値を手作業で計算するのは時間がかかり、面倒な作業になります。幸い、Excelにはこの問題を一瞬で解決できる関数が用意されています。その名もNORM.DIST関数です。それでは、記事の続きを読んで理解を深めていきましょう。

正規分布とは？

正規分布は「ガウス分布」とも呼ばれ、標本空間における確率分布の一つです。この分布は平均値に対して左右対称の形状を持ち、平均に近いデータほど多く出現し、平均から遠いデータほど少なくなる性質があります。重要なポイントとして、正規分布では平均が0、標準偏差が1となるように標準化されます。実務において、正規分布はデータ群の中で特定のデータがどれほど有意であるかを理解するのに役立ちます。データセットの正規分布を求める数式は以下の通りです。

ここで、

σ ＝ 標準偏差

x ＝ 標本データ

μ ＝ 平均値

Excelでデータを正規分布に変換する2つの方法

今回使用するデータセットには、ある試験における学生たちの点数（成績）情報が含まれています。各学生の点数をExcelで正規分布に変換することで、誰が特に優れた成績を収めたのかを判定していきます。

方法1：NORM.DIST関数を使って正規分布に変換する

データを正規分布に変換する最も簡単な方法は、ExcelのNORM.DIST関数を使用することです。この関数は、前述の数式に基づいて自動的に正規分布の値を計算してくれます。以下の手順で操作してみましょう。

手順：

• まず、正規分布の値を格納するための列を作成し、平均と標準偏差を保存するための2つのセルを選択します。
• 次に、平均を求めるための以下の数式を入力し、Enterキーを押します。学生たちの点数の平均値が表示されます。

=AVERAGE(C5:C11)

ここでは、AVERAGE関数によって学生の点数の平均値が返されています。

• 続いて、以下の数式を使って標準偏差を計算します。

=STDEV.P(C5:C11)

STDEV.P関数により、このデータの標準偏差が返されます。

• 次に、セルD5に以下の数式を入力します。

=NORM.DIST(C5,$C$13,$C$14,FALSE)

• Enterキーを押すと、セルC5の点数に対応する正規分布の値が表示されます。

• その後、フィルハンドルを使って下のセルまでオートフィルします。

これで、データセット内のすべてのデータをExcelで正規分布に変換できました。

• データを正規分布グラフとして表示したい場合は、範囲C4:D11を選択します。
• 次に、「挿入」タブから「散布図（平滑線）」を選択します。

これにより、データが正規分布グラフとして表示されます。

覚えておきたい重要なポイントとして、正規分布では約68%のデータが「平均 ± 標準偏差」の範囲に、95%のデータが「平均 ± 2×標準偏差」の範囲に、そして99.7%のデータが「平均 ± 3×標準偏差」の範囲に含まれます。つまり、この例では68%の学生が74.57 ± 10.66、すなわち63.91から85.23の間の点数を取っていることになります。同様に、95%や99.7%の範囲も算出できます。

方法2：数学的な数式を使って正規分布に変換する

統計学に精通しており、組み込みのNORM.DIST関数を使いたくない場合は、正規分布の数学的な数式を直接使うことも可能です。以下の手順で進めましょう。

手順：

• まず、正規分布の値を格納するための列を作成し、平均と標準偏差を保存するための2つのセルを選択します。
• 方法1と同じ手順で、平均と標準偏差を計算します。
• 次に、セルD5に以下の数式を入力します。

=EXP(-0.5*((C5-$C$13)/$C$14)^2)/($C$14*SQRT(2*PI()))

この数式では、EXP関数とSQRT関数を使用して、正規分布変換の数学的な数式を再現しています。これにより、データの正規分布の値が返されます。

• Enterキーを押すと、セルC5の点数に対応する正規分布の値が表示されます。

• その後、フィルハンドルを使って下のセルまでオートフィルします。

これで、数学的な数式を使ってデータセット内のすべてのデータを正規分布に変換できました。

• データを正規分布グラフとして表示したい場合は、方法1で紹介した手順を参照してください。

練習用データセット

ここで、この記事で使用したデータセットをご紹介します。ぜひご自身でもこれらの方法を練習してみてください。

まとめ

この記事では、Excelでデータを正規分布に変換する基本的な2つの方法について解説しました。NORM.DIST関数を使う方法と、数学的な数式を直接入力する方法のどちらも、平均と標準偏差を求めるところから始まるという点が共通しています。より良い方法のご提案や、本記事に関するご質問・フィードバックがございましたら、ぜひコメント欄でお聞かせください。今後の記事作りの参考にさせていただきます。その他のご質問については、弊社サイトExcelDemyをご覧ください。