Excel图表学习：创建带有阴影区域的正态曲线图

本文详细介绍如何配置数据并创建带有阴影区域的正态曲线图。

打开一个新工作簿，至少包含有三个工作表，其名称分别为：Control，Data和Reports。

配置Control工作表

Control工作表如下图1所示。

图1

在该工作表中，单元格区域B2:B8的名称分别为：Mean，StdDev，NumRows，Zmin，Zmax，PctClear，PctShade；单元格区域B11:B14的名称分别为：ShadeLeft，ShadeRight，CurveMin，CurveMax。

其中设置部分的数据为：

• Mean：数据的平均值。
• StdDev：数据的标准偏差。
• NumRows：要绘制的数据的行数。
• Zmin：要绘制的最小标准偏差数。
• Zmax：要绘制的最大标准偏差数。
• PctShade：曲线左侧阴影区域的百分比，从.0001%到99.999%。
• PctClear：曲线左侧非阴影区域的百分比，从.0001%到99.999%。

计算部分中的数字由公式返回：

ShadeLeft：=NORM.S.INV(PctClear)*StdDev+Mean

ShadeRight：=NORM.S.INV(PctShade)*StdDev+Mean

CurveMin：=Zmin*StdDev+Mean

CurveMax：=Zmax*StdDev+Mean

配置Data工作表

Data工作表如下图2所示。

图2

列A中是一列连续的序号值，从1至100。

列B返回最小的Z值至最大的Z值：

B2：=Zmin

B3：=(Zmax-Zmin)/(NumRows-1)+B2

列C为每个Z值计算图表的 X（水平）值：

C2：=B2*StdDev+Mean

列D计算图表的Y（垂直）值：

D2：=NORM.DIST(C2,Mean,StdDev,FALSE)

列E计算阴影区域的Y值：

E2：=IF(OR(C2>ShadeRight,C2<ShadeLeft),NA(),D2)

分别复制每列单元格公式至第101行。

然后，将第1行单元格值命名为相应列数据区域名称，例如列C中数据区域C2:C101的名称为“X”。

Reports工作表

该工作表即为放置图表的工作表。

在工作表Data中，选择单元格区域C2:D101，单击功能区“插入”选项卡“图表”组中的“散点图——带平滑线的散点图”，将绘制的图表剪切并复制到工作表Reports中，如下图3所示。

图3

单击选择图表中的曲线，在公式栏中应该看到下面的公式：

=SERIES(,Data!$C$2:$C$101,Data!$D$2:$D$101,1)

将其修改为：

=SERIES(,ChartStudy79.xlsx!X,ChartStudy79.xlsx!Y,1)

仍然选择图表中的曲线，在公式栏中选择SERIES公式并按Ctrl+C复制。然后，单击图表空白处，再单击上方公式栏，按Ctrl+V粘贴刚才的公式，按Enter键确认。

此时，你看到的图表似乎只有一个系列，实际上有两个系列，因为它们彼此重合。选择系列1，在公式栏中将其修改为：

=SERIES(,ChartStudy79.xlsx!X,ChartStudy79.xlsx!Area,2)

接着，单击图表右侧的加号展开菜单，选择“误差线——更多选项”，如下图4所示。

图4

在“设置误差线格式”中，单击“误差线选项”下拉箭头（如下图5所示），选择“系列1 Y误差线”。

图5

在“设置误差线格式”窗格中，将垂直误差线的方向设置为“负偏差”，末端样式为“无线端”，误差量百分比设置为“100%”，如下图6所示。

图6

然后，设置线条为“实线”，并选择颜色（示例中为橙色），将宽度设置为2.75磅，如下图7所示。

图7

设置误差线线宽、系列颜色，并删除图表中的一些元素后，最终结果如下图8所示。

图8

注：本文学习整理自exceluser.com，供有兴趣的朋友参考。