首页 数据透视表正文

offset+counta函数的另类引用。Excel 数据透视表动态引用数据源,新增记录自动添加到透视表区域

Excel 2016数据透视表教程系列
第-9.2-节 定义名称法创建动态数据透视表
上一篇咱用的表格法,这次的动态数据透视表咱用OFFSET+COUNTA组合动态名称引用来实现,跟着小编excel小课堂(ID:excel-xiaoketang 长按复制)来点不一样的体验。

01创建动态引用名称


    在“数据源”工作表中按快捷键<Ctrl+F3>组合键打开“定义名称”对话框,然后在“在当前工作簿中的名称”框中输入“data”,在“引用位置”框中输入“=OFFSET(销量统计表!$A$1,,,COUNTA(销量统计表!$A:$A),COUNTA(销量统计表!$1:$1))”,单击确定按钮。公式的作用说明小编放在文末了!!!



02使用名称创建数据透视表


    单击“销量统计表”工作表中的任意一个单元格,然后选择插入工具栏中的菜单“数据透视表”,在弹出的“创建数据透视表”对话框中,手动输入表/区域内容为“data”,这个内容根据上一步定义的名称来确定,剩下的和创建普通数据透视表一样,点击确定按钮完成创建。此后,就可以通过刷新工具将新增的数据记录同步到数据透视表中了。

03知识扩展


 利用OFFSET函数与COUNTA函数的组合,可以创建一个动态的名称,OFFSET函数可以通过指定的单元格或区域为参照,以给定的偏移量和行、列数得到新的数据区域;COUNTA函数用于计算出列表中非空值的单元格个数。

    =OFFSET(销量统计表!$A$1,,,COUNTA(销量统计表!$A:$A),COUNTA(销量统计表!$1:$1))公式的具体含义是:以数据源A1单元格为基准,在数据区域不发生偏移的情况下重新生成一个38行(COUNTA(销量统计表!$A:$A))、7列(COUNTA(销量统计表!$1:$1))的新的数据区域A1:G38。如果在“销量统计表”工作表的39行新增一条记录,则COUNTA(销量统计表!$A:$A)=39,也就是说新的数据区域将会自动延伸至A1:G39,从而形成动态的单元格区域引用。
    但这个方法要求数据源区域中的首列和首行不能包含空单元格,否则将无法用定义名称取得正确的数据区域。





版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。

评论