钟形曲线(bell curve)或正态分布图(normal distribution chart)是性能分析图。许多公司使用钟形图来跟踪员工的绩效。同样，该图表也可以在学校中用于跟踪学生的表现。您可以使用此图表来分析特定人员在不同阶段或团队在特定阶段的表现。在本文中，我们将了解如何在Excel中创建钟形曲线。
如何在 Excel 中创建钟形曲线
为了教你在Excel中制作钟形曲线的过程，我以 10 名学生在特定科目中的分数作为样本数据。满分是100分。
1]首先(First)，您必须按升序排列整个数据。为此，选择整列标记，单击工具栏上的“排序过滤器(Sort Filter)”按钮，然后选择“从最小到最大排序(Sort Smallest to Largest)”选项。选择此选项后，屏幕上会出现一个弹出窗口，其中包含“展开选择(Expand the Selection)”和“继续当前选择(Continue with the Current Selection)”两个选项。您必须选择第一个选项，因为它将按升序排列学生的分数以及他们的姓名。
2]要在excel中创建钟形曲线，我们需要三个值，平均值、标准差和正态分布。我们先计算数据的平均值。为此，输入以下公式并按“ Enter ”按钮：
=Average(B2:B10)
您可以在任何单元格中计算平均值，我已经在B13单元格中计算了它。请注意，您必须在平均公式中输入正确的单元格地址。此处，单元格 B2 包含第一个数字，单元格B11包含用于计算平均值的最后一个数字。
我们数据的平均值是 82。
3] 现在，让我们计算数据的标准差。为此，您必须输入以下公式：
=STDEV.P(B2:B11)
像平均值一样，您可以计算任何单元格中的标准差，我已经在B14单元格中计算了它。同样(Again)，在这个公式中，B2 代表 B2 单元格中的数据，B11代表B11单元格中的数据。请(Please)正确输入手机地址。
我们数据的标准差是 6.09918。这意味着大多数学生将位于 82 – 6.09918 或 82 + 6.09918 的范围内。
4] 现在，让我们计算数据的正态分布。正态分布将分别为每个学生计算。因此(Therefore)，我把它写在C列。选择(C. Select)单元格C2（正态分布单元格正下方的单元格）并输入以下公式，然后按“ Enter ”按钮：
=NORM.DIST(B2,$B$13,$B$14,FALSE)
在上面的公式中，$ 符号表示我们已将相应的单元格冻结，FALSE表示“概率质量分布(Probability mass distribution)”。由于钟形曲线不是连续增加的曲线，我们选择了函数FALSE。TRUE函数表示“累积分布函数(Cumulative distribution function)”，用于创建具有递增值的图形。
5]现在，将光标放在所选单元格（C2）的右下角并将其拖动到最后一个单元格（C11）。这会将整个公式复制并粘贴到所有单元格中。
6]我们的数据准备好了。现在，我们必须插入钟形曲线。为此，首先，选择标记和正态分布列，然后转到“Insert > Recommended Charts”。
7] 现在，单击“所有图表(All Charts)”选项卡，然后转到“ XY Scatter > Scatter with Smooth Lines”。在这里，选择“正态分布”(Normal) 图表(Distribution)并单击“确定(OK)”按钮。
8] 钟形曲线已准备就绪。
钟形曲线中有许多自定义选项。您可以通过单击设置 X 和 Y 轴的最小值和最大值。为方便起见，我列出了更改X-axis值的步骤。
- 选择 X 轴。
- 从右侧面板中选择“ Axis Options ”菜单并在“ (Options)Bounds ”部分设置最小值和最大值。
您还可以显示和隐藏不同的图表元素。为此，选择图表并单击“加(Plus)号”图标。在那里，您将获得许多选项。您可以选择和取消选择其中的任何一个或全部。
我们在上面已经看到，钟形曲线的形状并不完美。这是因为X 轴上(X-axis)的值（学生的分数）之间的差异不一样。现在，我们正在创建另一个在X 轴上(X-axis)具有等间距值的钟形图。您必须按照文章中提到的相同步骤进行操作。请参阅下面的屏幕截图。
这是完美的钟形曲线。在本文中，我们解释了 X 轴上等距和不等距值的钟形曲线。
现在，您可以在 MS excel 中创建钟形曲线。
您可能还喜欢(You may also like)：如何在 Excel 中制作甘特图。
How to create a Bell Curve in Excel
A bell curve or normal distribution chart is a performance analyzer graph. Many companies use the bell chart to track the performance of their employees. Similarly, the graph can also be used in schools to track the performance of students. You can use this chart to analyze the performance of a particular person in different phases or a team in a particular phase. In this article, we will see how to create a bell curve in Excel.
How to create a Bell Curve in Excel
To teach you the process of making a bell curve in Excel, I have taken sample data of 10 students’ marks in a particular subject. The marks are out of 100.
1] First, you have to arrange the entire data in ascending order. For this, select the entire column of marks, click on the “Sort Filter” button on the toolbar and select the “Sort Smallest to Largest” option. After selecting this option, a popup window will appear on the screen with two options, “Expand the Selection” and “Continue with the Current Selection.” You have to select the first option as it will arrange the students’ marks in ascending order along with their names.
2] To create a bell curve in excel, we need three values, average, standard deviation, and normal distribution. Let’s calculate the average of the data first. For this, enter the following formula and press the “Enter” button:
=Average(B2:B10)
You can calculate the average in any cell, I have calculated it in B13 cell. Do note that you have to enter the correct cell address in the average formula. Here, cell B2 contains the first number and cell B11 contains the last number for the calculation of average.
The average of our data is 82.
3] Now, let’s calculate the standard deviation of the data. For this, you have to enter the following formula:
=STDEV.P(B2:B11)
Like mean, you can calculate the standard deviation in any cell, I have calculated it in B14 cell. Again, in this formula, B2 represents the data in the B2 cell, and B11 represents the data in the B11 cell. Please enter the cell address correctly.
The standard deviation of our data is 6.09918. This means that most of the students will lie in the range of 82 – 6.09918 or 82 + 6.09918.
4] Now, let’s calculate the normal distribution of the data. The normal distribution is to be calculated for each student separately. Therefore, I have written it in column C. Select the cell C2 (the cell just below the normal distribution cell) and enter the following formula, and press the “Enter” button:
=NORM.DIST(B2,$B$13,$B$14,FALSE)
In the above formula, the $ sign indicates that we have put the respective cell to freeze and FALSE indicates the “Probability mass distribution.” Since the bell curve is not a continuously increasing curve, we have selected the function FALSE. The TRUE function indicates the “Cumulative distribution function” which is used to create graphs with increasing values.
5] Now, place your cursor at the bottom right corner of the selected cell (C2) and drag it to the last cell (C11). This will copy and paste the entire formula to all the cells.
6] Our data is ready. Now, we have to insert the bell curve. For this, first, select the marks and normal distribution columns and go to “Insert > Recommended Charts.”
7] Now, click on the “All Charts” tab and go to “XY Scatter > Scatter with Smooth Lines.” Here, select the “Normal Distribution” chart and click the “OK” button.
8] The bell curve is ready.
There are many customization options in the bell curve. You can set the minimum and the maximum values of X and Y-axes by clicking on them. For your convenience, I have listed the steps to change the values of the X-axis.
- Select the X-axis.
- Select the “Axis Options” menu from the right panel and set minimum and maximum values in the “Bounds” section.
You can also show and hide different chart elements. For this, select the graph and click on the “Plus” icon. There, you will get a number of options. You can select and deselect any or all of them.
We have seen above that the shape of the bell curve is not perfect. This is because the difference between the values on the X-axis (marks of the students) is not the same. Now, we are creating one more bell graph with equally spaced values on the X-axis. You have to follow the same steps mentioned above in the article. See the below screenshot.
This is the perfectly shaped bell curve. In this article, we have explained the bell curve with both equally and unequally spaced values on the X-axis.
Now, you can create a bell curve in MS excel.
You may also like: How to make a Gantt chart in Excel.
