用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 , 再激活图表,如此交替,图表就动起来了,实时变化了 。
【在Excel中滚动显示动态图表 excel如何滚动截长图】 不足之处:还缺一个暂停暂停,以便于在运行过程中随时暂停查看图表 。