excel 制作平均值控制线图案例解析

下图左侧A2:D10单元格区域为数据源，C列和D列、第3行和第10行为辅助作图区域。当B列"分数"数据发生变动时，C列和D列中的数据也随之发生变化。

右侧为根据数据源制作的动态图表，系列"分数"用蓝色柱子展示，系列"高于平均值"用灰色柱子展示，系列"平均值"用红色虚线展示。从图表中可以很方便地看出哪些人的分数高于平均值。

前期准备

要让C列"平均值"、D列"高于平均值"数据随B列"分数"中的数据同步变化，需要先在C列和D列设置函数公式。选中C3单元格，输入公式：=AVERAGE($B$4:$B$9)，可返回$B$4:$B$9单元格区域所有数值的平均值84，将公式向下填充至C10单元格。（C3、C10单元格中的辅助数据是为了使图表中系列"平均值"虚线左右两端超出系列"分数"和"高于平均值"柱子，起到美化图表的作用。）

选中D4单元格，输入公式：=IF(B4>C4,B4,0)，公式的含义是：如果B4单元格中的数值大于C4，则在D4单元格返回B4的数值，否则在D4单元格显示"0"值。最后，将公式向下填充至D9单元格。

操作步骤

１、鼠标选中A2:D2单元格区域，按住ctrl键加选A4:D9单元格区域，单击［插入］选项卡［图表］功能组右下角的对话框启动器按钮，弹出［插入图表］对话框。

2、在［插入图表］对话框中，切换到［所有图表］选项卡，单击左侧列表最下面的［组合］，在右侧将系列"分数"的图表类型设为"簇状柱形图"；将系列"平均值"的图表类型设为"折线图"（同时勾选"次坐标轴"选框）；将系列"高于平均值"的图表类型设为"簇状柱形图"，最后单击［确定］按钮。

3、将系列"高于平均值"与系列"分数"的柱子重叠。

双击系列"高于平均值"，在工作表右侧弹出［设置数据系列格式］对话框，在［系列选项］下将［系列重叠］设为"100％"，可使系列"高于平均值"柱子覆盖在"分数"柱子之上。

4、统一主、次纵坐标轴的最大最小值。

双击主要纵坐标轴，在工作表右侧弹出的［设置坐标轴格式］对话框中，在［坐标轴选项］下将［边界］中的［最小值］设为"0"，［最大值］设为"100"。双击次要纵坐标轴，进行同样的设置。

５、调整系列"平均值"的数据范围。

• 选中图表，在［设计］选项卡［数据］功能组中单击［选择数据］按钮。

• 在弹出的［选择数据源］对话框中，选中［图例项（系列）］下的［平均值］，单击［编辑］按钮。在弹出的［编辑数据系列］对话框中，将［系列值］编辑框中默认的"=动态突显高于平均值的数据!$C$4:$C$9"更改为"=动态突显高于平均值的数据!$C$3:$C$10"，将C3和C10两个单元格内容包含进去，单击［确定］按钮。

?

"动态突显高于平均值的数据"为单元格所在工作表的名称

• 返回［选择数据源］对话框，单击［水平（分类）轴标签］下的［编辑］按钮，弹出［轴标签］对话框。在对话框中将［轴标签区域］编辑框中默认的"=动态突显高于平均值的数据!$A$4:$A$9"更改为"=动态突显高于平均值的数据!$A$3:$A$10"，将A3和A10两个单元格包含进去。最后，单击［确定］按钮。

• 返回［选择数据源］对话框，直接单击［确定］按钮。

６、对系列"平均值"折线进行设置，使其显示为红色虚线。

双击系列"平均值"，在工作表右侧弹出［设置数据系列格式］对话框，单击［填充与线条］图标，将［线条］设为"实线"，［颜色］设为［标准红色］，［短划线类型］设为［短划线］。

7、设置次要横坐标轴。

• 鼠标单击图表，在图表右侧出现浮动按钮，点击最上面的"＋"按钮，在弹出的［图表元素］列表中勾选［坐标轴］选项，单击［坐标轴］右侧的三角形按钮，在级联菜单中勾选［次要横坐标轴］。此时，在图表标题下方出现了次要横坐标轴。

• 鼠标双击次要横坐标轴，在工作表右侧弹出的［设置坐标轴格式］对话框中，将［坐标轴选项］下的［坐标轴位置］设为"在刻度线上"。此时，系列"平均值"折线紧贴纵坐标轴显示。

?

?

• 选中次要横坐标轴，在［开始］选项卡［字体］功能组中将字体颜色设为背景色，可将次要横坐标轴进行隐藏，使图表更加美观。（次要横坐标轴不能直接删除，否则折线图又会回到原来的位置。）

8、在"平均值"红色虚线右侧添加数据标签。

单击选中系列"平均值"，再次单击"平均值"虚线最右侧的数据点将其单独选中，为该数据点添加数据标签，标签位置设为"右"。此时，添加的数据标签与次要纵坐标轴发生重叠，可单击选中次要纵坐标轴，按delete键将次要纵坐标轴删除。

9、最后，根据实际需要对图表大小、位置、网格线、图例等细节进行调整，最终效果如下图。

动态效果测试

当数据源中的数据发生变动时，图表中系列"平均值"虚线的位置会随之发生变化。如当把"虚竹"的"分数"改为"100"时，图表中"平均值"虚线的位置同时发生改变，上升到"85"的位置。