Excelで正規分布のベル曲線を作成する方法

このチュートリアルでは、Excelのすべてのバージョンで正規分布のベル曲線を作成する方法を示します：2007、2010、2013 、2016、2019。

統計では、ベル曲線（標準正規分布またはガウス曲線とも呼ばれます）は、データがクラスター化する傾向を示す対称グラフです。特定のデータセットの中心値または平均の周り。

y軸はデータセットで発生する特定の値の相対確率を表し、x軸はグラフ上に値自体をプロットして釣鐘型を作成します。曲線、したがって名前。

グラフは、特定の値が予想される変動の一部であるか、統計的に有意であるかを分析するのに役立ちます。したがって、より詳細に調べる必要があります。

Excelには提供できる組み込みのソリューションがないため、自分でプロットする必要があります。そのため、数回クリックするだけで高度なExcelグラフを作成できるツールであるChartCreatorアドインを開発しました。

このステップでは-ステップバイステップのチュートリアルでは、Excelで正規分布のベルカーブをゼロから作成する方法を学習します。

Toガウス曲線をプロットするには、次の2つのことを知っておく必要があります。

• 平均（標準測定とも呼ばれます）。これにより、曲線の中心が決まります。これにより、曲線の位置が決まります。
• 測定値の標準偏差（SD）。これは、正規分布でのデータの広がり、または平易な英語では、曲線の幅を定義します。たとえば、上記のベルカーブでは、平均の1つの標準偏差は、53から85の試験スコア間の範囲を表します。

SDが低いほど、カーブは高くなり、低くなります。データは分散され、その逆も同様です。

正規分布曲線に適用できる68-95-99.7ルールについて言及する価値があります。つまり、データの約68％が配置されます。平均から1SD以内、2 SD以内で95％、3 SD以内で99.7％。

これで、本質を知っているので、理論から実践に移りましょう。

はじめに

説明のために、200人の学生のテストスコアがあり、それらを「曲線上で評価したい」と仮定します。 、」は、生徒の成績がクラスの他のメンバーとの相対的な成績に基づくことを意味します。

ステップ＃1：平均を見つける。

通常、平均が与えられます。とSD値は最初からですが、そうでない場合は、いくつかの簡単な手順でこれらの値を簡単に計算できます。最初に平均に取り組みましょう。

平均はデータのサンプルまたは母集団の平均値を示すため、AVERAGE関数を使用して標準の測定値を見つけることができます。

次の式を入力します実際のデータ（列AおよびB）の横にある空のセル（この例ではF1）に入力して、データセット内の試験スコアの平均を計算します。

簡単なメモ：多くの場合、数式の出力を切り上げる必要があります。これを行うには、次のようにROUND関数でラップします。

ステップ2：標準偏差を見つけます。

1つダウン、残り1つ。幸い、Excelには、標準偏差を見つけるという汚い作業をすべて行う特別な機能があります。

ここでも、数式は指定されたセル範囲（B2：B201）からすべての値を選択し、その標準偏差を計算します。切り上げることを忘れないでください。出力も同様です。

ステップ＃3：曲線のx軸値を設定します。

基本的に、グラフは膨大な数の間隔を構成します（滑らかな曲線を作成するために線で結合されたステップと考えてください。

この場合、x軸の値は特定の試験のスコアを示すために使用され、y軸の値は生徒が試験でそのスコアを取得する確率を示します。

技術的には、必要な数の間隔を含めることができます。横軸の目盛りを変更することで、後で冗長データを簡単に消去できます。 3つの標準偏差を組み込む範囲を選択するようにしてください。

カウントを1から始めて（学生が負の試験スコアを取得する方法はないため）、最後まで進みましょう。 150（150か1500かは関係ありません）で別のヘルパーテーブルを設定します。

1. グラフデータの下にある空のセル（E4など）を選択し、「1」と入力します。最初の間隔を定義する値。
2. [ホーム]タブに移動します。
3. [編集]グループで、[塗りつぶし]を選択します。
4. [シリーズイン、 」を選択します。
5. 「ステップ値」に「1」と入力します。この値は、Excelが最後の間隔に達するまで自動的に加算される増分を決定します。
6. [停止値]に、最後の間隔を表す値である「150」を入力し、[OK]をクリックします。 ”

奇跡的に、E列に149個のセル（E5： E153）は2から150までの値で埋められています。

注：スクリーンショットに示されているように元のデータセルを非表示にしないでください。非表示にしないと、この手法は機能しません。

ステップ4：すべてのx軸値の正規分布値を計算します。

次に、正規分布値、つまり特定のx軸値で表される特定の試験スコアを取得する学生の確率を見つけます。 —間隔ごとに。幸いなことに、Excelには、これらすべての計算を実行するための主力製品であるNORM.DIST関数があります。

次の式を右のセル（F4）に入力します。最初の間隔（E4）：

これは、それに応じて調整するのに役立つデコードされたバージョンです。

平均値とSD値をロックして、残りの間隔（E5：E153）の式を簡単に実行できるようにします。

次に、塗りつぶしハンドルをダブルクリックして、数式を残りのセルにコピーします（F5：F153）。

ステップ＃5：滑らかな線で散布図を作成します。

最後に、ベルカーブを作成するときが来ました：

1. x軸とy軸の値（E4：F153）を含むヘルパーテーブルの任意の値を選択します。
2. [挿入]タブに移動します。
3. [散布図（X、Y）またはバブルチャートを挿入]ボタンをクリックします。
4. [滑らかな線で散布]を選択します。

ステップ＃6：ラベルテーブルを設定します。

技術的には、ベルカーブがありますが、それを説明するデータがないため、読みにくいでしょう。

平均の上下のすべての標準偏差値を示すラベルを追加して、正規分布をより有益なものにしましょう（代わりに、それらを使用してzスコアを表示することもできます。

そのために、次のようにさらに別のヘルパーテーブルを設定します。

まず、列X値（I5）の対応するセルの横にある平均値（F1）をコピーします。

次に、次の単純な式をセルI4に入力して、平均より下の標準偏差値を計算します。

簡単に言えば、式は平均から先行する標準偏差値の合計を減算します。次に、塗りつぶしハンドルを上にドラッグして、数式を残りの2つのセル（I2：I3）にコピーします。

ミラー式を使用して、平均を超える標準偏差に対して同じプロセスを繰り返します。

同様に、他の2つのセルの数式を実行します（I7 ：I8）。

最後に、必要に応じてy軸ラベル値（J2：J8）にゼロを入力します。水平軸に配置されたデータマーカー。

ステップ＃7：ラベルデータをグラフに挿入します。

次に、準備したすべてのデータを追加します。グラフのプロットを右クリックして、[データの選択]を選択します。

ポップアップするダイアログボックスで、[追加。」

ヘルパーテーブルの各セル範囲を強調表示します。「シリーズX値」の場合はI2：I8、J2 ：J8 for “Series Y values” —そして[OK]をクリックします。

ステップ＃8：チャートタイプを変更します。ラベルシリーズ。

次のステップは、新しく追加されたシリーズのグラフタイプを変更して、データマーカーをドットとして表示することです。これを行うには、グラフプロットを右クリックして、[変更]を選択します。グラフの種類。」

次に、コンボグラフを設計します。

1. に移動します。コンボタブ。
2. シリーズ「Series2」の場合は、「グラフの種類」を「散布図」に変更します。
   • 注：「Series1」が「滑らかな線の散布図」のままであることを確認してください。コンボを作成すると、Excelによって変更される場合があります。また、「Series1」が2次軸にプッシュされていないことを確認してください。グラフの種類の横にあるチェックボックスはマークしないでください。
3. [OK]をクリックします。

ステップ＃9：横軸の目盛りを変更します。

横軸の目盛りを調整して、グラフをベル曲線の中央に配置します。横軸を右クリックして、メニューの[軸の書式設定]。

作業ウィンドウが表示されたら、次の手順を実行します。

• [軸オプション]タブに移動します。
• 最小境界値を「15」に設定します。
• 最大境界値を「125」に設定します。

適切と思われる方法で軸のスケール範囲を微調整できますが、標準偏差の範囲がわかっているため、3番目の標準偏差のそれぞれから少し離れた境界値を設定して、曲線の「テール」を表示します。 。

ステップ＃10：カスタムデータラベルを挿入して配置します。

グラフを磨くときは、必ずカスタムデータラベルを追加します。まず、シリーズ「Series2」を表すドットを右クリックして、[データラベルの追加]を選択します。

次に、置換以前に設定したラベルのデフォルトラベルをデータマーカーの上に配置します。

1. シリーズ「Series2」データラベルを右クリックします。
2. 「フォーマット」を選択します。データラベル。」
3. 作業ウィンドウで、[ラベルオプション]タブに切り替えます。
4. [X値]ボックスをオンにします。
5. [Y値]のチェックを外します。 」ボックス。
6. 「ラベルの位置」で、「上」を選択します。

また、グリッド線を削除できるようになりました（>削除を右クリックします）。

ステップ＃11：データマーカーの色を変更します（オプション）。

最後に、ドットの色を変更して、グラフのスタイルに合わせます。

1. シリーズ「Series2」のデータラベルを右クリックします。
2. クリックします。 [塗りつぶし]ボタン。
3. 表示されるパレットから色を選択します。

また、rドットの周囲の境界線を削除します。

1. 同じデータマーカーをもう一度右クリックして、[アウトライン]を選択します。
2. [アウトラインなし]を選択します。

ステップ＃12：垂直線を追加します（オプション）。

最終調整として、次のことができます。 SD値を強調するために、グラフに垂直線を追加します。

• グラフのプロットを選択します（そうすると、線がグラフに直接挿入されます）。
• 移動[挿入]タブに移動します。
• [形状]ボタンをクリックします。
• [線]を選択します。

[SHIFT]キーを押しながらマウスをドラッグして、各ドットから各線がベルカーブと交わる場所まで完全に垂直な線を描画します。

グラフのタイトルを変更します、そして改善されたベルカーブの準備が整いました。貴重な分布データが表示されます。

これがその方法です。これで、次の簡単な手順に従って、任意のデータセットを選択し、正規分布のベルカーブを作成できます！