Excel 中的钟形曲线

钟形曲线是一种统计学中使用的对称图，显示数据在一个给定数据集中的均值或中心值周围的分布情况。它也被称为常规正态分布或高斯曲线。

通过将值直接绘制在图表上形成钟形曲线——因此得名——x轴显示了值在数据集中出现的相对概率，而y轴显示了数据在数据集中出现的可能性。

借助该图，我们可以确定一个给定的数字是否在预期的波动范围内，或者是否需要进一步研究，因为它具有统计学意义。

学习本教程，从零开始在 Excel 中构建正态分布钟形曲线。

用 Excel 制作正态分布钟形曲线的方法

绘制高斯曲线需要两个信息

• 标准测量有时也称为均值。 这样做可以确定曲线的中心，从而定义曲线的位置。
• 测量的标准差 (SD)。 换句话说，它表明曲线应该有多宽，或者数据在正态分布中应该如何分布。例如，上方钟形曲线中的标准差均值表示测试结果在 53 到 85 之间的长度范围。

相反，较低的标准差表示曲线较高，数据分散较少。

分析任何正态分布曲线时，一个有用的经验法则是 68-95-99.7 规则，它指出大约 68% 的数据将位于均值的一个标准差范围内，95% 位于两个标准差范围内，99.7% 位于三个标准差范围内。

现在我们已经理解了基本原理，接下来将从理论转向实践。

开始

以 200 名学生的考试成绩为例，假设你想给他们“按曲线”评分，也就是根据学生与其他学生相比的表现来给分。

步骤 1：计算均值。

如果您最初没有获得标准差和均值，可以通过几个简单的步骤快速计算出来。我们先来处理均值。

您可以使用 AVERAGE 函数获得标准测量值，因为均值是样本或总体数据的平均值。

为了确定数据集中平均考试分数，在任何空白单元格（在此示例中为 F1）中输入以下公式，该单元格应邻近您的实际数据（A 列和 B 列）。

友情提示：您可能需要对公式的输出进行四舍五入。为此，只需按照以下方法将所有内容包装在 ROUND 函数中即可。

步骤 2：查找标准差

还有一个步骤就完成了。幸运的是，Excel 提供了一个独特的功能，可以处理所有计算标准差的繁琐工作。

再次，公式选择了指定单元格范围（B2:B201）中的所有值，然后计算标准差。但是，请记住也要对结果进行四舍五入。

步骤 3：配置曲线的 x 轴参数。

简单来说，图表由许多间隔或步长组成，通过一条线连接以形成平滑的曲线。

在这种情况下，考试分数将由 x 轴值直观表示，y 轴值将表示学生在该次考试中获得该分数的几率。

只要您记得稍后调整比例的水平轴，就可以轻松删除任何不必要的数据。理论上，您可以添加任意多的间隔。确保您选择的范围包含所有三个标准差。

为了设置另一个辅助表，我们从一个（因为学生不可能获得负考试成绩）开始计数，一直到 100。无论是 100 还是 1000，都不重要。

1. 选择图表数据下方的任何空白单元格（例如 E5）并输入“1”，表示第一个间隔。
2. 转到“开始”选项卡。
3. 在“编辑”组的菜单中选择“填充”。
4. 点击“系列”，然后选择“列”。
5. 在“步长值”中输入 1。此设置决定了 Excel 将自动累加的增量，直到达到最后一个间隔。
6. 在“结束值”字段中输入“100”，然后按“确定”。

然后，E 列（E6:E99）中的 99 个单元格将填充从 2 到 99 的值。

步骤 4：确定每个 x 轴值的正态分布值。

对于每个间隔，找到正态分布的值，即表示学生获得特定考试分数概率的 x 轴值。幸运的是，Excel 的 NORM.DIST 函数是处理所有这些计算的强大工具。

在右侧第一个间隔的单元格（F4）中输入此公式（E4）。

这是解码版本，以便您可以进行必要的调整。

为了让其他间隔（E6:E104）的公式易于使用，请锁定标准差 (SD) 和均值。

双击填充柄将公式复制到剩余单元格（F6:F104）。步骤完成。

步骤 5：使用平滑线创建散点图。

钟形曲线的构建阶段终于到来。

1. 可以选择辅助表（E5:F104）中的 x 和 y 轴值以显示任何值。
2. 导航到“插入”菜单。
3. 然后选择“插入气泡图或散点图 (X, Y)”。
4. 在“带平滑线的散点图”下选择它。

步骤 6：放置标签表

您的钟形曲线技术上已经完成了。然而，由于没有任何描述信息，它不容易阅读。

现在我们为每个标准差值（低于或高于均值）添加了标签（您也可以使用它们来显示 z 分数），让我们将其添加到正态分布中，使其看起来更具信息量。

为此，请创建另一个表，如下所示。

在 X 值（I5）列中复制均值（F1）值到其对应的单元格旁边。

接下来，使用此简单公式在单元格 I4 中计算标准差。均值下方的值。

简而言之，该公式从均值中减去先前标准差值的总和。要将公式复制到最后两个单元格（I2:I3），请向上拖动填充柄。

应用镜像公式。

• 对均值以外的标准差并重复此过程。
• 将相同的公式应用于剩余的两个单元格（I7:I8）。

一旦数据标记位于您想要的位置，就用零填充 Y 轴值标签（J2:J8）。

步骤 7：用标签数据填充图表

现在添加所有准备好的数据。通过右键单击图表区域来选择“选择数据”。

在出现的对话框中选择“添加”。

选择辅助表的正确单元格范围（“X 轴系列”为 I2:I8，“Y 轴系列”为 J2:J8）后，点击“确定”。

步骤 8：修改标签系列的图表类型。

必须更改新添加系列的图表类型，以便数据标记显示为点。右键单击图表区域并选择“更改图表类型”。

然后创建组合图。

1. 选择“组合”选项卡。
2. 将“系列 2”的“图表类型”设置为“散点图”。

注意：确保“系列 1”保持为“带平滑线的散点图”。创建组合图时，Excel 有时会更改它。通过取消选中图表类型旁边的框，确保“系列 1”未移至次坐标轴。

3. 选择“确定”。

步骤 9：不同地缩放水平轴。

调整水平轴的比例，将图表置于钟形曲线的中心。通过右键单击水平轴从菜单中选择“设置坐标轴格式”。

在任务窗格显示后，执行以下操作。

• 点击“坐标轴选项”选项卡。
• 在“最小值”处输入“15”。
• 在“最大值”处输入“125”。

虽然您有标准差范围，但您可以根据需要调整轴的比例范围。但是，要显示曲线的“尾部”，请将边界值设置得比所有第三个标准差稍远一些。

步骤 10：放置自定义数据标签并遵循。

在最后确定图表时，请记住包含自定义数据标签。右键单击代表“系列 2”的点，然后选择“添加数据标签”。

之后，在数据标记上方放置您之前配置的标签，并替换默认标签。

1. 右键单击任何“系列 2”数据标签。
2. 选择“设置数据标签格式”。
3. 在任务窗格中，选择“标签选项”选项卡。
4. 勾选“X 值”旁边的框。
5. 清空“Y 值”框。
6. 在“标签位置”下选择“靠上”。

此外，您现在可以通过右键单击网格线并选择删除来消除它们。

步骤 11：如果需要，更改数据标记的颜色。

最后，您可以重新着色这些点，以帮助它们匹配您的图表样式。

1. 右键单击“系列 2”数据标签以进行编辑。
2. 选择“填充”选项。
3. 从显示的选项中选择您的颜色。

同时删除这些点周围的边框。

1. 再次右键单击相同的数据标记，选择“边框”。
2. 选择“无边框”。

步骤 12：如果需要，添加垂直线。

作为最后的修改，您可以在图表中添加垂直线，以突出显示 SD 值。

1. 选择图表区域，以确保线条直接插入到图表中。
2. 选择“插入”选项卡。
3. 从菜单中选择“形状”。
4. 选择“线条”。

要绘制每条精确的垂直线，从每个点到它们与钟形曲线相遇的位置，请按住“SHIFT”键并拖动鼠标。

当您更改图表标题时，您的增强型钟形曲线，显示重要的分布数据，就已经准备就绪。

就是这样。通过这些简单的步骤，您可以从任何数据集中绘制出带有正态分布的钟形曲线！