你还在手动更新excel透视表数据源吗_如何动态更新excel透视表数据源

Excel函数 2023-01-08

小伙伴们是否遇到过这样的情况，创建好的数据透视表，由于向数据源中添加了新的内容，而不得不重新修改透视表的数据源？

或者你在创建透视表时，数据源选择整列内容，但这样创建出来的透视表会存在空白数据，影响透视表的美观性，那我们该怎么做，既不用重新选择数据源，又不会影响表格的美观性呢？

这里我们可以用【定义名称】解决这个问题。

依次点击【公式】，【定义名称】，会弹出新建名称界面，名称框内随意输入，这里我们输入data，数据的意思，在引用位置中输入：=OFFSET(数据源!$A$1,,,COUNTA(数据源!$A:$A),COUNTA(数据源!$1:$1))，点击确定按钮，完成输入。

接着我们开始创建数据透视表，与以往创建不同的是，表/区域中，不用选择数据源，直接输入刚才新建的定义名称data即可，拖入相应的字段，完成数据透视表的创建；

可以发现的是，不论是向数据源中添加数据，还是删减数据，直接刷新透视表即可，不用二次修改透视表的数据源！

公式解释：

offset函数一共五个参数（参考系，上下偏移的行数，左右偏移的列数，新引用区域的行数，新引用区域的列数）；

这里我们以A1单元格作为参考系，因为我们要返回的是区域（数据透视表的数据源），所以2/3参数可以不填写；

第四个参数【新引用区域的行数】这里我们用counta(a:a)表示，统计A列非空单元格的数量，也就是数据源是所有的行数；

第五个参数【新引用区域的列数】用counta(1:1)表示，统计第一行非空单元格的数量，也就是数据源所有的列数；因为是用函数表示，当数据源行列内容发生变化时，counta函数就会重新计算，相当于动态更新透视表的数据源。