excel报表自动化-动态图表

制作excel周报、月报，本文仅展示周报制作说明。

目的：动态展示各事业部各业务中心的业绩指标与效率指标。以下是本文最终实现的报表：

一、数据说明

共11个事业部82个业务中心2925个小区的数据。效率、业绩等指标都已提前核算完毕，都放入数据源工作表中。数据源工作表部分数据如下（具体数据已隐藏）：

二、制作动态表格

动态表格如下，业务中心列和指标都是动态变化的，数据也随之改变。

1.业务中心列：用indeirct+subtotal+row等函数，将数据源中的事业部业务中心都引用到A列某行（A列24行）。

2.效率指标与业绩指标：建”参数“工作表，将需要动态展示的指标，在这个工作表中列出。根据这个表中的指标，插入窗体控件。根据窗体控件，引用”参数“中的指标，需要用到indirect或者index函数。

3.根据业务中心和指标，填充数据：需要用到index+match函数，注意如果指标为空数据也要设置为空。

三、制作动态图

1.制作动态图时，需要用到名称管理器。新建三个名称“业务中心”，“效率指标“、“业绩指标”，引用位置是offset+subtotal的函数组合。例如业务中心是“=OFFSET(周报!$A$25,0,0,SUBTOTAL(103,数据源!$A:$A)-1,1)”，表格显示多少数据，图就跟着显示多少数据的变化。

2.定义完名称后，开始制作图。本文插入组合图，选择柱状图和折线图的组合，效率指标用折线图且在次坐标轴上显示。

3.对图进行设计：图右键-选择数据-分别设置图例项和水平轴项。图例项、水平轴项的值是我们定义完的“效率”、“业绩”、”业务中心名称，比如 =周报!效率指标。

经过上面一系列操作，动态图表就完成了。之后也可添加业绩合计值，效率均值。