用Excel制作的动态图表,当选择不同的类别时,数据源就动态的变化起来,则对应的图表也会跟随变动。在此基础上,如果实现了对类别的自动选择,则可以循环、滚动显示图表,这就是Excel版的可视化报表。
一、数据源
数据源格式如下图,每一行为各地的GDP及三产业数据,A列作为辅助区域(由C列和B列合并而成)。
二、制作选择器
以地区作为筛选字段。
1、插入数据透视表
选择数据源A:G列,插入数据透视表到新工作表的P1单元格。将[地区]字段拖拽到[行区域]。再隐藏数据透视表的字段标题、禁用行和列的总计。
2、设置选择器
在菜单栏[开发工具]的[插入]命令中选择[列表框]控件,在单元格L8画出一个列表框。
用鼠标右键单击列表框,在弹出的快捷菜单单击[设置控件格式(F)],进入[设置对象格式]对话框,将[数据源区域]设置为数据透视表所在的区域P1:P32,将[单元格链接]设置为存放选择结果的单元格N2,单击[确定]按钮。
三、数据辅助区域
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单元格。
再删除[垂直(值)轴],[网格线]设置为无线条,添加[数据标签]并设置为靠上显示。
选中图表标题,输入公式:=动态图表5!$A$3。即将A3单元格内容作为图表标题。
2、插入圆环图(各产业占比)
选中B4:B6数据区域,插入[圆环图],[水平(分类)轴标签]选择A4:A6数据区域。
再删除[图例],添加[数据标签]并勾选类别名称和百分比,将圆环大小设置为50%,选中图表标题,输入公式:=动态图表5!$B$3。即将B3单元格内容作为图表标题。
再将K4:K6数据区域也插入圆环图,并设置图表标题。
3、图表排版
将三个图表拖拽到一起、调整大小,并进行适当排版。
小技巧:在拖拽或拉动图表时,按住ALT键,图表可自动锚定到Excel单元格,这样排版看起来很整齐。
五、控制程序(VBA)
通过VBA编写代码循环改变区域,实现对数据的动态引用。
1、参数区域
N5:N7作为参数区域,通过更改参数,可以设置区域之间的切换时间、循环显示的次数。
2、VBA代码
新建kanban模块,键入以下代码。
3、制作按钮
在菜单栏[开发工具]的[插入]命令中选择[按钮]控件,在N8单元格插入一个按钮,命名为[滚动显示],并指定宏为kanban。
4、最终效果
六、说明
动态图表的核心就是数据源一定要动态变化起来,不管是在工作表中变化,还是通过VBA给图表的数据源赋值,总之一定要变化起来。
在做的过程中,通过控件、函数让数据源动起来后,图表会跟着动态变化。可是通过VBA让数据源变动起来后,图表并没有及时变化。通过网上搜资料,发现有一种方法可以解决,就是在VBA代码中加入DoEvents,再激活图表,如此交替,图表就动起来了,实时变化了。
不足之处:还缺一个暂停暂停,以便于在运行过程中随时暂停查看图表。
评论 (0)