1. 主页 > Excel教程 >

excel 制作平均值控制线图实例剖析

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

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

Excel办公技巧:平均值控制线图制作案例解析 

前期准备

要让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单元格。

操作步骤

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

Excel办公技巧:平均值控制线图制作案例解析 

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

Excel办公技巧:平均值控制线图制作案例解析 Excel办公技巧:平均值控制线图制作案例解析 

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

双击系列"高于平均值",在事情表右侧弹出[设置数据系列花样]对话框,在[系列选项]下将[系列重叠]设为"100%",可使系列"高于平均值"柱子笼罩在"分数"柱子之上。

Excel办公技巧:平均值控制线图制作案例解析 

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

双击主要纵坐标轴,在事情表右侧弹出的[设置坐标轴花样]对话框中,在[坐标轴选项]下将[界限]中的[最小值]设为"0",[最大值]设为"100"。双击次要纵坐标轴,举行同样的设置。

Excel办公技巧:平均值控制线图制作案例解析 

5、调整系列"平均值"的数据规模。

选中图表,在[设计]选项卡[数据]功效组中单击[选择数据]按钮。 Excel办公技巧:平均值控制线图制作案例解析  在弹出的[选择数据源]对话框中,选中[图例项(系列)]下的[平均值],单击[编辑]按钮。在弹出的[编辑数据系列]对话框中,将[系列值]编辑框中默认的"=动态突显高于平均值的数据!$C$4:$C$9"更改为"=动态突显高于平均值的数据!$C$3:$C$10",将C3和C10两个单元格内容包罗进去,单击[确定]按钮。 Excel办公技巧:平均值控制线图制作案例解析 

Excel办公技巧:平均值控制线图制作案例解析 

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

返回[选择数据源]对话框,单击[水平(分类)轴标签]下的[编辑]按钮,弹出[轴标签]对话框。在对话框中将[轴标签区域]编辑框中默认的"=动态突显高于平均值的数据!$A$4:$A$9"更改为"=动态突显高于平均值的数据!$A$3:$A$10",将A3和A10两个单元格包罗进去。最后,单击[确定]按钮。 Excel办公技巧:平均值控制线图制作案例解析 Excel办公技巧:平均值控制线图制作案例解析  返回[选择数据源]对话框,直接单击[确定]按钮。 Excel办公技巧:平均值控制线图制作案例解析 

6、对系列"平均值"折线举行设置,使其显示为红色虚线。

双击系列"平均值",在事情表右侧弹出[设置数据系列花样]对话框,单击[填充与线条]图标,将[线条]设为"实线",[颜色]设为[尺度红色],[短划线类型]设为[短划线]。

Excel办公技巧:平均值控制线图制作案例解析 Excel办公技巧:平均值控制线图制作案例解析 

7、设置次要横坐标轴。

鼠标单击图表,在图表右侧泛起浮动按钮,点击最上面的"+"按钮,在弹出的[图表元素]列表中勾选[坐标轴]选项,单击[坐标轴]右侧的三角形按钮,在级联菜单中勾选[次要横坐标轴]。此时,在图表题目下方泛起了次要横坐标轴。 Excel办公技巧:平均值控制线图制作案例解析  鼠标双击次要横坐标轴,在事情表右侧弹出的[设置坐标轴花样]对话框中,将[坐标轴选项]下的[坐标轴位置]设为"在刻度线上"。此时,系列"平均值"折线紧贴纵坐标轴显示。

Excel办公技巧:平均值控制线图制作案例解析 

 

选中次要横坐标轴,在[最先]选项卡[字体]功效组中将字体颜色设为配景色,可将次要横坐标轴举行隐藏,使图表越发雅观。(次要横坐标轴不能直接删除,否则折线图又会回到原来的位置。) Excel办公技巧:平均值控制线图制作案例解析 

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

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

Excel办公技巧:平均值控制线图制作案例解析 Excel办公技巧:平均值控制线图制作案例解析 

9、最后,凭据现实需要对图表巨细、位置、网格线、图例等细节举行调整,最终效果如下图。

Excel办公技巧:平均值控制线图制作案例解析 

动态效果测试

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

Excel办公技巧:平均值控制线图制作案例解析

本文由网上采集发布,不代表我们立场,转载联系作者并注明出处:http://www.xcfljd.com/a/exceljiaoc/598.html

联系我们

在线咨询:点击这里给我发消息

微信号:weixin888

工作日:9:30-18:30,节假日休息