【原】制作带有“平均线”的Excel动态图表！

哈喽，大家好~这里是略懂一点Excel的小六子~

今天来给大家分享一个带有“平均线”的Excel动态图表。

如下图所示，这是一张平平无奇的销售数据。

下图是自带“数据分析”的Excel动态图表，表中的横线代表“平均线”。

面对纷繁复杂的数据时，一条平均线就能清晰地看见，哪个月的数据低于全年平均水平，哪个月的数据高水平发挥。

下面来看看这种动态图表是如何实现的吧？

第一步：做下拉选择

1.选中J1单元格，点击“数据”选项卡下的“数据验证”。 

2.在“数据验证”窗口下方的“设置”选项里，“允许”选择“序列”，来源选择五个销售区域所在的单元格“=$A$2:$A$6”，点击确定。 

第二步：建立动态更新的辅助数据区域

在J1选择“销售一区域”，然后在B7单元格输入公式：

=OFFSET(B1,MATCH($J$1,$A$2:$A$6,0),0)

输完公式后回车，然后向右填充至G7单元格。

解析

利用OFFSET以“B1”为参考系，偏移的行数是使用MATCH函数获取的$J$1在$A$2:$A$6的位置，偏移列数为0表示不偏移。如图J1的值是“销售一区”，在$A$2:$A$6的位置为1，OFFSET返回的值是以“B1”为参考系，向下偏移一行的引用。这样随着选择区域$J$1的不断变化，B7:G7单元格就能获取到对应区域的销售数据。

设置平均线的数据，在B8单元格输入公式：

=AVERAGE($B$7:$G$7)

输完公式后回车再向右填充。

第三步：创建图表

选择B1:G1和B7:G8区域，点击”插入”选项卡下的”图表”组里的“二维柱形图”。 

在K1单元格输入“=J1&"销售数据"”。 

点击图表标题框，在编辑栏输入=Sheet1!$K$1 ,这样图表标题就和数据验证区域同步更新了。 

点击“图表工具”下方“设计”选项卡下的“更改图表类型”。 

点击“所有图表”选项下的“组合”，将平均值所在的系列2修改成“折线图”。 

最后将“系列1，系列2 ”删除，把辅助数据B7:G8和K1单元格字体修改成白色就完成了。 

写在最后：

这个图标中平均值的位置，看不出明显差距。

我们可以点击纵坐标数据，设置坐标轴格式，将最小值设置成5000。

这时，就能明显看出各月销售数据在平均线的浮动情况了。