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

原创虾壳可乐可乐的数据分析之路

这一节内容介绍动态交互图表制作的核心内容，就是取数。没看过前两节的朋友可移步《这样用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列），这时就选择上了天津市对应的数据。