动态图表揭秘：“动”的关键——取数

这一节内容介绍动态交互图表制作的核心内容，就是取数。没看过前两节的朋友可移步《这样用Excel动态交互图表，还要什么前端？》、《动态图表交互揭秘：制作选择器的奥秘》。首先要理解，图表能动起来，是作图的这部分数据在动态变化，因此我们说动态图表，其实是动态数据，数据动起来了，图表自然而然就跟着变化了。

原理

不要破坏原数据，在原数据的基础上建立一个取数区域，取数区域是根据取数函数动态取数的。当然也可以不做辅助的取数区域直接让图表动起来的方法，见文末。

取数函数

vlookup

最常用的函数，这个函数已经写了无数遍，每天工作也用了无数遍，但还是要来啰嗦，因为它实在是太重要了，先来看看函数结构：vlookup（Lookupvalue，Table array，Col index num，Range lookup）
函数参数解释说明如下：Lookup value：要查找的单元格。Table array：从哪个区域/表找。Col index num：选择区域/表的第几列，默认序号是从1开始。Range lookup：0是精确查找，1是模糊查找。下面是使用vlookup函数取数的完整步骤：Step1：做选择器和固定表头。先做一个组合框，组合框选择的是城市，单元格链接到D50单元格，也就是图中标黄的单元格，表头是日期，要取的是日期固定下的不同城市的值。

Step2：取数。先取城市，在D51单元格中输入公式“=VLOOKUP($D$50,$A$1:$M$32,2,0)”。vlookup的第一个参数要查找的单元格，肯定是选择器的结果即D50单元格，第二个参数值在哪找，当然是在原数据中找，即$A$1:$M$32区域，第三个找第几列，城市是第二列，最后一个参数是精确查找。再来回顾一下上节内容制作选择器，选择器制作完选择的结果通常是数字（1/2/3等）或逻辑值（TRUE/False），我们知道使用vlookup函数连接两张表的时候，两张表必然有一个公共字段，所以要在原数据中添加一列序号值，作为公共字段。添加了序号列的原数据如下：

Step3：至此已经完成一个数据的动态变化了，选择器选择不同的城市在D51单元格中就显示相应的城市，因为vlookup函数往右拖Excel没法智能识别出你的用意，所以如果你不嫌麻烦的话，就在每一个固定日期下输入相同的函数，只是列数选择不一样。

一个一个改参数实在有点麻烦，所以我们在固定表头日期的上方做一行辅助行，数字表示该列数据在原数据中的位置。这样vlookup函数就可以向右拖动了。

index

index（Array，Row num，Column num）Index函数有3个参数：Array：要查找的区域。Row num：寻找第几行。Column num：寻找第几列。 Step1：还是做表头和选择器，就不多说了。Step2：用index函数取数，在D52单元格中输入公式“=INDEX(B2:B32,$D$50)”，从B2:B32区域中找第$D$50行的值，注意B2:B32区域只有一列，所以就不填第几列这个参数了，$D$50依旧是组合框选择的值，这个函数可以右拖，是不是比vlookup函数好用许多呢。

index+match

index还可以和match函数结合来取数。Index函数是取一个精确坐标下对应的值，而Match函数是用来确定一个值的坐标，返回指定范围内值所在的序号，结构如下：Match（Lookup value，Lookup array，Match type）Lookup value：是要查找的值Lookup array：是要查找的值所在的区域Match type ：同vlookup一样精确匹配0 Step1：制作选择器，这里用数据有效性来制作。鼠标放在B54单元格中，选择【数据】——【数据验证】，验证条件选择序列，来源是原数据的城市一列，点击确定就得到了B54单元格的数据有效性。

Step2：先来看一下使用match函数的结果，在D53单元格中输入公式“=MATCH(B54,B2:B32,0)”，表示查找B54单元格这个值在B2:B32区域中是第几行，结果返回2，表示是第二行。

Step3：用indext+match函数结合取数。在D54单元格中输入公式“=INDEX(B2:B32,MATCH($B$54,$B$2:$B$32,0))”，表示在B2:B32这一列中取第几行的值，第几行由match函数算出，这个公式做好相对引用绝对引用后可以右拉。这两个函数结合取数的方式用在对一个值直接取数，而不是选择值对应的数字取数。

offset

以指定的引用为参照系，返回新的引用，这个函数有点复杂了，但学会了还是很有用的，该函数结构如下：Offset（reference,rows,cols,height,width），有5个参数，分别表示：Reference：指定参照系起始位置，表示从哪个位置开始引用Rows：相对于起始位置，向下偏移几行Cols：相对于起始位置，向右偏移几列Height：新区域选中几行，Width：新区域选中几列. 同其他几个取数函数一样，先制作选择器，如下图所示，组合框选择的结果在D50单元格中显示，在D55单元格中输入公式“=OFFSET(B1,$D$50,0)”，这个公式的意思是，从B1单元格开始，向下偏移$D$50单元格中显示的行数，向右偏移0行，得到的值即为取出的值。B1单元格是城市那一列字段的最开头的单元格，$D$50是选择器显示的结果，后两个参数不写也是可以的。

offset动态定义名称

接下来放个大招，无需制作取数区域，制作完选择器直接画出动态图表，就要用到offset动态定义名称这个功能来实现了。
在【公式】——【定义名称】中，名称命名为t_data，就是要画图的数，在引用位置处填写公式“=OFFSET(Sheet1!$C$2,Sheet1!$A$5,1,1,11)”，表示从C2单元格开始，向下偏移A5单元格所显示的行数（A5是选择器的单元格链接，这里组合框选择了天津，显示2，就是向下偏移两行），向右偏移1列，选择1行，选择11列（因为整个数据源有11列），这时就选择上了天津市对应的数据。

在做一个名称为b_data的offset偏移函数，引用位置处的公式为“=OFFSET(Sheet1!$C$2,Sheet1!$A$5,0)”，就是将城市名选择上。

对前两行数据插入一个柱形图。

在柱子上右键【选择数据】，在弹出的【选择数据源】对话框中，对【图例项】进行编辑

这一步很重要，将系列名称改为刚刚编辑的动态名称，b_data，系列值改为动态名称，t_data，点击确定。

组合框选择不同的值，图表就跟着动态变化，如下动图所示，还没有另做辅助区域，是个大招，不过也比较烧脑，需要在脑海中构想出offset函数偏移的过程。

Excel动态图表交互系列就到这里了，see you next week~

更多相关文章

随机推荐