Microsoft Excelでベルカーブ(正規分布グラフ)を作成する方法

ベルカーブ(正規分布グラフ)は、データの分布状況を視覚的に把握できるパフォーマンス分析用のグラフです。多くの企業では従業員の業績評価に活用されており、学校でも生徒の成績分析などに利用されています。特定の人物の各段階におけるパフォーマンスや、特定の時期におけるチーム全体の状況を分析したい場合にも役立ちます。

この記事では、Microsoft Excelでベルカーブを作成する具体的な手順を詳しく解説します。

Excelでベルカーブを作成する基本手順

ここでは、ある科目の10名の生徒の点数(100点満点)をサンプルデータとして使用して説明します。

1. データを昇順に並べ替える

まず、データ全体を昇順に並べ替えます。点数の列を選択し、ツールバーの「並べ替えとフィルター」ボタンをクリックして「小さい順に並べ替え」を選択しましょう。すると、「選択範囲を拡張する」と「現在選択している範囲を並べ替える」の2つの選択肢を含むポップアップウィンドウが表示されます。ここでは前者を選びます。生徒名とセットのまま点数が昇順に並べ替えられます。

2. 平均値を求める

ベルカーブの作成には、平均値・標準偏差・正規分布の3つの値が必要です。まず平均値を計算します。任意のセルに以下の数式を入力し、「Enter」キーを押してください。

=AVERAGE(B2:B11)

ここではセルB13に計算結果を表示しています。平均を求める範囲の先頭はセルB2、最後はセルB11です。実際のデータ範囲に合わせて、正しいセル番地を指定するように注意してください。

このサンプルデータの平均値は82です。

3. 標準偏差を求める

次に、以下の数式で標準偏差を計算します。

=STDEV.P(B2:B11)

平均値と同様に任意のセルに入力でき、ここではセルB14に結果を表示しています。B2が先頭のデータ、B11が最後のデータを指すため、こちらもセル番地を自分の表に合わせて正しく入力してください。

このサンプルデータの標準偏差は6.09918です。つまり、大多数の生徒は「82 − 6.09918」から「82 + 6.09918」までの範囲内に収まることを意味します。

4. 正規分布を求める

続いて、生徒ごとの正規分布値を計算します。正規分布の値は各生徒別に求める必要があるため、C列に書き出していきます。セルC2を選択し、以下の数式を入力して「Enter」キーを押しましょう。

=NORM.DIST(B2,$B$13,$B$14,FALSE)

この数式の「$」マークは、該当するセル参照を固定することを意味します。また、「FALSE」は「確率質量分布」を指定しています。ベルカーブは単調に増加し続ける曲線ではないため、FALSEを選択します。「TRUE」は「累積分布関数」を意味し、値が増加し続けるタイプのグラフを作成する場合に使用します。

5. 数式をすべてのセルにコピーする

選択したセル(C2)の右下隅にカーソルを合わせ、最終行(セルC11)までドラッグします。これで数式がすべてのセルに自動的にコピーされます。

6. グラフを挿入する

データの準備が完了したら、いよいよベルカーブを挿入します。まず点数の列と正規分布の列を選択し、「挿入 > 推奨グラフ」をクリックします。

7. 散布図(平滑線)を選択する

「すべてのグラフ」タブを開き、「XY (散布図) > 平滑線付き散布図」を選択します。正規分布のグラフを選んで「OK」ボタンをクリックしましょう。

8. ベルカーブの完成

以上でベルカーブが完成です。

ベルカーブのカスタマイズ方法

ベルカーブには多彩なカスタマイズオプションが用意されています。X軸やY軸をクリックすれば、それぞれの最小値と最大値を設定できます。ここでは参考として、X軸の値を変更する手順を紹介します。

1. X軸を選択します。
2. 右側のパネルから「軸のオプション」メニューを開き、「境界」セクションで最小値と最大値を設定します。

さらに、グラフ要素の表示・非表示も自由に切り替えられます。グラフを選択して「+」アイコンをクリックすると複数のオプションが表示されるので、必要な項目だけを選択または解除してください。

X軸の間隔を揃えて美しい釣鐘型に仕上げるコツ

上記の手順で作成したグラフを見ると、ベルカーブの形状が完全な釣鐘型になっていないことに気づくでしょう。これは、X軸の値(生徒の点数)の間隔が均等ではないことが原因です。そこで、X軸の値を等間隔に配置したデータでもう一度同じ手順を実行してみましょう。

そうすることで、左右対称の理想的な形状のベルカーブが作成できます。この記事では、X軸の値が等間隔の場合と等間隔ではない場合の両方のパターンについて解説しました。

これで、あなたもMicrosoft Excelでベルカーブを作成できるようになりました。

あわせて読みたい: Excelでガントチャートを作成する方法