在Excel中打造随年份变化的条形图

平淡

分析视频 理清制作思路

从图1所示的视频中可以看出，全国各省每年的GDP数据从大到小通过条形图进行排列，随着年份的变化，其GDP数据也会不断地发生变化，最终形成视频所示的效果。

按图索骥 制作变化条形图

理清制作思路后，我们就来讲述一下具体的操作步骤。这里以某省份各地市2015年到2019年的GDP数据为例，突出显示的是H市的数据。

1整理数据

启动Excel，然后将收集到的数据依次输入到新建文档中。因为在每组条形图中只显示一个年份的数据且从大到小进行排列，所以我们需要将这些数据整理为按升序排列（这样条形图才会变为从上到下降序排列）。

定位到H1单元格，任意输入一个年份（如2017），接着在H2单元格中输入公式“=SMALL（OFFSET（$A$2：$A$10，0，MATCH（$H$1，$B$1：$F$1，0）），ROW（A1））”，下拉填充到H10单元格，这样即可将D2：D10区域中的数据按升序依次填充到H2：H10区域（图2）。

公式解释：以H1单元格中输入的年份为基准，使用OFFSET函数嵌套MATCH函数对D列中（对应2017年的数据）的数据进行引用，然后再使用SMALL函数对引用的数据从小到大进行排序。

继续定位到G2单元格并输入公式“=INDEX（$A$2：$A$10，MATCH（H2，OFFSET（$A$2：$A$10，0，MATCH（$H$1，$B$1：$F$1，0）），0））”，下拉填充到G10單元格（图3）。公式的含义同上，在H列中填充了相应的GDP数据后，再使用INDEX函数在A 2：A10区域中引用与之相对应的城市名称。

再定位到I2单元格并输入公式“=IF（G2="H市"，H2，""）”，下拉填充到I10单元格（图4）。该公式的意思是使用IF函数对G列中的数据进行判断，如果G列中的单元格显示为“H市”，那么就在I列所对应的单元格中显示H市的GDP数据，这样当我们以I列中的数据制作条形图时就只显示H市的数据。

2设置条形图

选中G2：H10区域中的数据，依次点击“插入→图表→条形图”，右击插入的条形图并点击“选择数据”，在打开的窗口中添加“系列2”，将“图表数据区域”设置为“=Sheet1！$G$2：$I$10”，这样就会出现两个系列的条形图。选中“系列2”的条形图（即只有H市的GDP数据），将其填充颜色设置为红色（图5）。

继续在上述的图表区域中右击任意一个条形图并选择“设置数据系列格式”，在右侧的窗格中将“系列重叠”设置为“ 9 8%”、“间隙宽度”设置为“5%”，这样H市的2个系列的数据就会融合在一起（图6）。

因为这里的条形图是通过引用函数填充的数据生成的，而这些数据又是以H1单元格中输入的年份为基准，所以我们只要在H1单元格中输入其他的年份，该条形图也会随之发生变化。

3设置时间控件

在图1所示的视频中，GDP数据会随着年份的变化而变化，而年份的变化可以通过插入控件来实现。依次点击“开发工具→插入→滚动条控件”，用鼠标右键点击该控件并选择“设置控件格式”，在打开的窗口中切换到“控制”，将“最小值”设置为“2015”、“最大值”设置为“2019”、“单元格链接”设置为“$H$1”（图7）。

接着返回图表区域，依次点击“插入→文本框→ 绘制横排文本框”，定位到文本框，在地址栏中输入公式“=Sheet1！$H$1”，表示在文本框中引用H1单元格中的数据。之后当我们点击滚动条时，H1单元格和文本框中的数字就会从2015到2019不断地发生变化，而条形图也会随之变化。同时由于“系列2”只有H市的数据，其醒目的红色条形图就会随着年份的变化不断地发生跳动（图8）。

最后我们使用QQ自带的录屏工具将变化的图表区域录制为视频，并为其添加背景音乐，就可以打造出图1所示效果的视频了。