Excel公式的常见应用（二）——动态图表

拜托拜托拜托！动态图表超酷的好不好！！

看看下面的动态图表，一点就能更新欸，但这还不是最酷的：

最好看的还是这个！干净又高效：

       是不是已经开始感兴趣了？

OK，first，我们先看看怎么根据数据来创建一个图表吧：

       框住你需要做图表的数据，如上图就是B1：G2，接着直接在“插入”选项卡下的图表中找到各类图表进行选择即可，还可以设计多种样式，美观又方便！

       “这也太简单了吧！”

       那我们现在已经知道图表如何插入，接下来就是要让它变成一个可以自动更新的动态图表！事实上，在完成动态图表的制作还是需要很多相关知识的，接下来会一一讲到。

第一，需要在表格下方（其他位置也可以）创建一个可以变化的数据区域：

       选中一个单元格，点击“数据”选项卡下的数据验证，这一步是为了什么呢？

       为了在这个单元格做一个“下拉列表”（自己乱叫的），在这个单元格中只能输入特定的值。

       打开数据验证：

       在验证条件中的“允许”列表下选择“序列”，当你选择一个序列时，这个单元格输入的数据就只能在该序列中选择： 

       在本题中，我们就选择姓名那一列，B2：B23（不用选B1），然后当你点击这个单元格时，就会出现一个小箭头，点一下就是上面动图中的“下拉列表”啦~

       第二，有了名字，还需要有它对应的模拟考的成绩，这里我们需要用到一个查找函数，其实也很常见，有VLOOKUP和HLOOKUP两种，大家记不记得“水平的”的英文是H开头的（horizontal），“垂直的”的英文是V开头的（vertical）？所以这两个函数的查找方式不同，VLOOKUP查找的就是这种标题一列一列的表格。（HLOOKUP函数和VLOOKUP的道理是一样的，我就不写了~）

       VLOOKUP函数有4个参数：Lookup_value（需要在数据表首列进行搜索的值，可以是数值、引用或字符串），Table_array（需要在其中搜索数据的文字、数字或逻辑值表，table就是表格的意思嘛，所以记住这里填数据表），Col_index_num（应返回其中匹配值的table_array中的列序号），Range_lookup（逻辑值：若只需要大致匹配，输入TRUE或省略；若要查找精确匹配，用FALSE）。那本题中这四个参数该怎么填呢？

       我们需要在上面的数据表中查找B25那一格的“姓名”对应的各门成绩，所以第一个参数是需要查找的值，即B25，第二个参数是数据表，也就是B2：G23。

       这里写B2：G23是有原因的，首先从第二列开始是因为这个查找函数中，第一个参数“需要被查找的值”默认为第一列，所以要保证它在数据表第一列，本题中即“姓名”在第一列。接着为什么要到G23呢？其实这个空的公式写B2：C23也是可以的，但为了便于我们D25到G25的数据可以一键填充，所以就先规定一整个表了，同时记得绝对引用，上图中还没有更改。

接着第三个参数，它代表数据表中你查找到的那个单元格，对应的那一行的第几个，是你需要的数据，比如这题，填的是2：说明我要查找的是引用的数据表第二列的数据，即“模拟考1”，而如果填“3”查找的就是第三列的数据，即“模拟考2”

       但还有一个问题！如果是写在D25的公式，其他参数都不用变，第三个参数却要从2变成3，每次都要手动纠正，若是我要直接一键填充，有没有什么办法呢？

当然是有办法的啦！！

       第一第二个参数记得先加绝对引用，为了后面的一键填充做准备，然后第三个参数中，我们引用一个函数COLUMN（），这个函数是用来返回括号里单元格的列号的，如B25就是B列，即第二列，若是括号里没有参数，那返回的就是输入公式的这个单元格的列号 ，这里刚好等于2，如果你选择的是C列的单元格，那就要减1。

       随着向右一键填充，Column函数括号里的单元格也右移，于是列号也加一，就不需要手动调整了！

       最后一步就简单啦，选数据区域后插入图表，B1：G1+B25：G25，不同区域同时选择可以按住CTRL ，最后如一开始的第一步中讲的步骤插入图表即可！这就是一个动态的图表啦~

Second，我们来学一下第二种图表？

       第一，要先插入一个可以下拉的列表，excel中的选项卡中一般没有这个选项，所以需要小小设置一下，步骤如下，很简单哒~

       将开发工具打开后，在选项卡中找到插入，插入“组合框”，如下图：

       然后在表格中画一个框即可！但是这个时候这个框还是动不了的，因为还没有框选范围呢~ 然后按步骤右键点框，点击“设置控件格式”：

       “哎呀又是一个窗口！好麻烦啊啊啊啊！”但是，别气馁！做出来之后可是满满成就感呢~

       第一个空源区域就是你希望这个下拉表单里出现的数值序列，这里我们选择一到十二月（时间）A3：A14（别问为什么绝对引用！它自动的，我没有点噢~），单元格链接呢随便找一个空白的单元格就可以啦，它是用来返回“你选择的这个值在列表中排第几个”的数值的单元格。

       第三个空也不是我填的嗷！它自己自动检测出来的，不用管它。

       现在这个下拉列表的组合框就做好咯~

       下一步呢，就是要创建一个图表并把它和这个组合框以及数据区域产生联系，所以我们先随便选择一部分区域做一个图表，随后再进行设置，比如下面这块区域：

       然后在编辑区域的时候，我们需要用到一个新的公式，所以在此之前，我们还得定义一下单元格名称，把一到十二月定义为“月份”，后面的数据定义为“销售额”（当然啦，名字随意取就行，取名什么都可以滴~），那怎么定义呢？步骤如下：

       如上图首先在随便选中一个空白单元格，点击“公式”选项卡下的“定义名称”，随意取名~然后把引用区域确定好，这里我们需要确定月份区域和销售额区别（在别的情景下就是确定别的图表x轴和y轴的数值），那怎么定义呢？需要用到一个新的函数offset。

OFFSET（Reference，Rows，Cols，Height，Width）：以指定的引用为参照系，通过给定偏移量返回新的引用。

Reference：作为参照系的引用区域，其左上角单元格是偏移量的起始位置。

Rows：相对于引用参照系的左上角单元格，上（下）偏移的行数。

Cols：相对于引用参照系的左上角单元格，左（右）偏移的列数。

Height：新引用区域的行数

Width：新引用区域的列数

这个函数是用来干什么的呢？简而言之就是用一个区域作为参照点，上下移动后的区域范围就是得到的值，新区域的长宽也可以设置。

所以该题中定义名称应该如何设置：

       名称为“月份”（或其他），引用位置：=OFFSET(商品月销售表!$A$2,商品月销售表!$L$2,0,1,1)

       以A2为参照，向下移动L2格，这个L2就是我们上面随便选择的用来表示“下拉列表”组合框中选择的是第几个的单元格，在这里刚好可以用来表示下移第几格，然后左右不用移动，即为0，只需要选择一个单元格，所以最后两个参数高和长都为1。

同理，销售额的定义：

名称为“销售额”（或其他），引用位置：=OFFSET(商品月销售表!$A$2,商品月销售表!$L$2,1,1,5)

别的不变，但后三个参数改为了1，1，5，分别代表：向右平移1格，取高1，宽5的区域。这样就能代表相应月份后面的一串销售额啦~

接着就是设计图表，右键图表，点开“选择数据”，再点击“编辑”：

       接着打开编辑框，将名称和系列值改成之前定义的名称即可~这样就把这张图表和动态数据联系起来啦！

       生成的最后效果就是这样咯~很帅吧：

讲完一轮之后发现还是挺复杂的~但实际操作起来又并不难，图片挺多的，如果能自己操作一遍会很有成就感滴。为了防止忘记，还是需要多熟悉多操作，这也是一项很实用的技能！希望大家可以有所收获。