|
大家都知道数据透视表是个强大的工具，尤其在我们进行数据分析、统计的时候，非常方便。但是用久了也会觉得有点遗憾，每次增加数据的时候，数据源不能同步更新，就想下面这个动画演示中的情况，在原有数据源进行修改后，透视表上点一下刷新，汇总结果会发生变化，但是在原有数据源后面增加一行后，刷新也没有什么作用。 数据源肯定是经常增加的，每次都要手动修改数据源还真的是件麻烦事，今天分享两个方法，可以让数据源自动更新，想学的赶紧往下看…… 方法1：插入【表】实现数据源自动更新 先看一下操作演示： 在这个操作中，主要涉及到两个重要的步骤： 步骤1：将数据源转化为【表格】 点击【表格】按钮后出现如下提示： 可以看到数据源区域自动被选中，点击确定完成。 成功创建表格后，在区域的右下角会有一个三角的标记，当我们在下一行录入数据的时候，这个标记会自动扩展： 【表格】这个功能是Excel里非常有用并且智能的一个工具，有想了解详细内容的话可以看下之前发过的教程。 传说Excel中有个“超级表”功能，今天带你揭开神秘的面纱？ 步骤2：使用表格作为透视表的数据源 当我们成功转换表格后，需要使用表格作为透视表的数据源，可以删掉已有的透视表重新添加，也可以通过更改数据源进行修改： 点击更改数据源后，界面如下图： 此时数据源是以区域的形式存在，重新选择区域后，这里会自动变成表名： 直接点击确定，今后再添加数据源后就可以直接刷新了。 以上是通过【表格】这个功能实现的自动更新数据源，下面我们介绍使用“名称”这个功能来实现自动更新数据源的方法。 方法2：利用“名称”实现数据源自动更新 在这个操作中，我们没有使用【表格】，透视表也可以自动调整数据源，其中的奥秘何在？
我们打开数据透视表的数据源设置，可以看到此时既不是区域，也不是表名，就是三个字-数据源。 这个数据源又是个什么东东呢？ 其实这就是一个“名称”。
在公式选项组中，有个定义名称的功能，也可以使用名称管理器查看我们已经定义好的名称。
在定义名称的时候，需要填入两个信息：名称和引用位置。 名称就是给这个引用位置起的名字，例如这里可以叫数据源，也可以起成其他的名字，当然在透视表的数据源中也要对应修改才行。 下面引用位置中用到的公式才是关键部分： =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),3) 通常构造动态区域会用到offset或者indirect这两个函数，今天我们用offset作介绍。 在之前的文章中多次介绍过offset这个函数，一共有五个参数构成，具体含义是： =OFFSET(起始位置，行偏移量，列偏移量，区域高度，区域宽度) 在本例中，我们的数据源是从A1开始，因此第一参数为Sheet1!$A$1，这里是加了工作表的名称和绝对引用； 因为A1这个单元格就是要包含在数据区域中的，因此不需要偏移量，第二参数和第三参数都是0； 数据源只有3列，所以区域宽度是3这也不难理解，因此最后一个参数就是3； 关键在第四个参数的设定，也就是区域高度，为了可以自动确定区域的高度，我们用到了一个计数函数counta，这个函数的功能很简单，就是统计给定区域中有数据的单元格个数。在这个公式里，区域给的是A列，当我们在A列增加数据时，counta就会自动确定出有多少行数据，因此offset得到区域就会自动调整了。 小结：对于新手来说，使用方法1可能比较容易掌握，但是方法2才是重点，这个思路经常会用到一些动态图表，动态数据源的有效性设置等等地方，建议感兴趣的朋友要把offset这个函数好好研究一下了。 |
|
|
