用Excel制作的动态图表,当选择不同的类别时,数据源就动态的变化起来,则对应的图表也会跟随变动。在此基础上,如果实现了对类别的自动选择,则可以循环、滚动显示图表,这就是Excel版的可视化报表。


一、数据源

数据源格式如下图,每一行为各地的GDP及三产业数据,A列作为辅助区域(由C列和B列合并而成)。

excel中插入动态图表(在Excel中滚动显示动态图表)(1)

二、制作选择器

地区作为筛选字段。

1、插入数据透视表

选择数据源A:G列,插入数据透视表到新工作表的P1单元格。将[地区]字段拖拽到[行区域]。再隐藏数据透视表的字段标题禁用行和列的总计

excel中插入动态图表(在Excel中滚动显示动态图表)(2)

2、设置选择器

在菜单栏[开发工具][插入]命令中选择[列表框]控件,在单元格L8画出一个列表框

excel中插入动态图表(在Excel中滚动显示动态图表)(3)

用鼠标右键单击列表框,在弹出的快捷菜单单击[设置控件格式(F)],进入[设置对象格式]对话框,将[数据源区域]设置为数据透视表所在的区域P1:P32,将[单元格链接]设置为存放选择结果的单元格N2,单击[确定]按钮。

excel中插入动态图表(在Excel中滚动显示动态图表)(4)

三、数据辅助区域

excel中插入动态图表(在Excel中滚动显示动态图表)(5)

1、年份

第一行为年份,在K1单元格输入最近的年份后,其他左侧单元格自动依次递减。

在J1单元格输入公式:=K1-1。并将公式复制到B1:I1区域。 在B3单元格输入公式:=B1&"年 各产业占比"。作为圆环图的标题。 在K3单元格输入公式:=K1&"年 各产业占比"。作为圆环图的标题。

2、地区(省份)

根据选择结果(N2单元格),将地区引用到A2单元格。

则在A2单元格输入公式:=INDEX(P1:P32,N2)。 在A3单元格输入公式:=A2&" GDP趋势"。作为折线图的标题。

3、按条件提取数据

B2单元格输入公式:=VLOOKUP(B$1&$A2,各省GDP!$A:$D,4,0),并将公式复制到C2:K2数据区域。将选定区域对应年份的数据提取到辅助区域。

在A4/A5/A6单元格分别输入细分类别。在其右侧单元格输入公式。

在B4单元格输入公式:=VLOOKUP(B$1&$A$2,各省GDP!$A:$G,5,0) 在B5单元格输入公式:=VLOOKUP(B$1&$A$2,各省GDP!$A:$G,6,0) 在B6单元格输入公式:=VLOOKUP(B$1&$A$2,各省GDP!$A:$G,7,0)

再将B4:B6数据区域的公式复制到C4:K6,将选定区域对应年份细分类别的数据提取到辅助区域。

四、制作图表

1、插入折线图(按年趋势图)

选中B2:K2数据区域,插入[带数据标记的折线图][水平(分类)轴标签]选择B1:K1数据区域,[系列名称]选择A2单元格。

excel中插入动态图表(在Excel中滚动显示动态图表)(6)

再删除[垂直(值)轴][网格线]设置为无线条,添加[数据标签]并设置为靠上显示。

excel中插入动态图表(在Excel中滚动显示动态图表)(7)

选中图表标题,输入公式:=动态图表5!$A$3。即将A3单元格内容作为图表标题。

excel中插入动态图表(在Excel中滚动显示动态图表)(8)

2、插入圆环图(各产业占比)

选中B4:B6数据区域,插入[圆环图][水平(分类)轴标签]选择A4:A6数据区域。

excel中插入动态图表(在Excel中滚动显示动态图表)(9)

再删除[图例],添加[数据标签]并勾选类别名称百分比,将圆环大小设置为50%,选中图表标题,输入公式:=动态图表5!$B$3。即将B3单元格内容作为图表标题。

再将K4:K6数据区域也插入圆环图,并设置图表标题。

excel中插入动态图表(在Excel中滚动显示动态图表)(10)

3、图表排版

将三个图表拖拽到一起、调整大小,并进行适当排版。

小技巧:在拖拽或拉动图表时,按住ALT键,图表可自动锚定到Excel单元格,这样排版看起来很整齐。

excel中插入动态图表(在Excel中滚动显示动态图表)(11)

五、控制程序(VBA)

通过VBA编写代码循环改变区域,实现对数据的动态引用。

1、参数区域

N5:N7作为参数区域,通过更改参数,可以设置区域之间的切换时间、循环显示的次数。

excel中插入动态图表(在Excel中滚动显示动态图表)(12)

2、VBA代码

新建kanban模块,键入以下代码。

excel中插入动态图表(在Excel中滚动显示动态图表)(13)

3、制作按钮

在菜单栏[开发工具][插入]命令中选择[按钮]控件,在N8单元格插入一个按钮,命名为[滚动显示],并指定宏为kanban

excel中插入动态图表(在Excel中滚动显示动态图表)(14)

excel中插入动态图表(在Excel中滚动显示动态图表)(15)

4、最终效果

excel中插入动态图表(在Excel中滚动显示动态图表)(16)


六、说明

动态图表的核心就是数据源一定要动态变化起来,不管是在工作表中变化,还是通过VBA给图表的数据源赋值,总之一定要变化起来。

在做的过程中,通过控件、函数让数据源动起来后,图表会跟着动态变化。可是通过VBA让数据源变动起来后,图表并没有及时变化。通过网上搜资料,发现有一种方法可以解决,就是在VBA代码中加入DoEvents,再激活图表,如此交替,图表就动起来了,实时变化了。

不足之处:还缺一个暂停暂停,以便于在运行过程中随时暂停查看图表。

,