首先看一下数据源:

 

根据分析需求,要制作可以A列、B列、月份选择的动态数据表。

例如某月中某个城市各个组别的销售图表包含柱形图及饼图,柱形图用以突出各个项目之间的对比情况,饼图以用对各个项目在整个数据中的占比情况,完成后效果如下:

 

  某个城市某个组别在各个月的销售图表:

本实例的图表重点在于两个选择项的同时对下拉列表的控制互动。为了便于理解及添加新的应用要求,因此在【名称管理器】中新建名称来解决这类问题。

此次实例中的名称管理器中的新建名称公式如下:

如果觉得有些生涩难懂,可以参考我之前的博文:Excel动态图表应用,与本文类似但应用的名称公式较为简单。

这些名称的主要功能是快速地、动态地赋值给图表的【系列】及【数据】、【标签】等。

名称管理器中的部分名称解释:

(1)按钮_分公司:=图表!$M$3

        将三个分公司按钮分为一组,设置其属性【控制】单元格链接“图表!$M$3”。按钮_下拉列表分类链接至M1。

(2)标签:=CHOOSE(图表!$M$1,CHOOSE(图表!$M$3,组别_北京,组别_上海,组别_广州),月)

       为图表【水平(分类)轴标签】做标识。CHOOSE选择函数是本动态图表的核心部分,“北京、上海、广州”这三个选项会链接至M3单元格,因此可以用M3单元格来控制标签标识。

(3)系列:=CHOOSE(图表!$M$1,系列_月,系列_组别)

       服务于图表的【图例项(系列)】。它与其他几个名称中的函数类似嵌套了CHOOSE函数。标签和系列在图表中的位置【插入】选择图表类型创建图表,然后点击右键【选择数据】。如下图所示:

        当选择编辑时需要输入完整的工作簿路径:

(4)系列_月:=OFFSET(CHOOSE(图表!$M$3,区域_北京,区域_上海,区域_广州),0,图表!$M$2,COUNTA(CHOOSE(图表!$M$3,组别_北京,组别_上海,组别_广州)),1)

       这个函数比较长,这里稍微解释一下,如果对函数构成不了解,可以按F1帮助一下。

Offset函数可以利用偏移量得到新的引用。范围根据选择按钮的链接单元格M3为基础,用CHOOSE函数对其进行选择置顶区域范围。后面的COUNTA函数同样通过按钮链接选择组别,并对其计数。

通过对名称的设置,就能很好的为图表服务,动态的体现数据的意义。当然也可以通过其他函数来实现同样的效果,本文下面有此图表应用附件,如有兴趣欢迎交流。