:::info 所谓动态数据透视表,是指当源数据条数或其他任何内容发生变化时,只需要刷新一下透视表,就可以更新当前数据透视表了… ::: 动态数据透视表.gif
    那么怎么做呢?核心是什么?有哪些需要注意的点呢?

    1. =OFFSET(明细表!$A$1,0,0,COUNTA(明细表!$A:$A),COUNTA(明细表!$1:$1))
    1. offset

    offset的参数(起始位置,位移的行数,位移的列数,选取的行高,选取的列宽)
    也就是以a1为基础,向下向右平移n个单元格,选中n行n列
    Excel 动态数据透视表 - 图2
    一张明细表的列数一般是不变的,一般情况下只会是追加行数,
    但是,为了防止列数也发生变化的这种情况,也需要把列数看做是一个变动的数

    1. counta

    那么,offset的后两个参数其实是把固定的数据替换成counta,counta是用来计算某一个区域内的非空单元格,也就是说,如果A列有十个非空单元格,明细表有十个表头字段
    那么offset(a1,0,0,counta(a:a),counta(1:1))就是取a1-j10这块区域

    1. 公式

    这个嵌套公式要发挥发的作用,需要在公式-定义名称创建
    image.png
    然后就可以愉快的使用了~

    1. 注意事项
    • 本质上是利用offset的最后两个参数来取值,也就是说A列如果有多个空值,就会影响透视表的取数范围

    image.png

    • 除了行的影响,列的影响也非常大,直接关系到是否能刷新出来数据

    image.png
    可以看下上面这张图,原本表格的字段是6个,但是由于offset的最后一个参数也是counta,也就是说counta(1;1)返回的值就是8
    二由于数据透视表本身的限制,是不允许源数据的第一行有空值的,也就是表头不能为空
    image.png

    另外有个小提示,如果一个工作簿里有非常多透视表,想要一次性全部刷新,可以选中任意一个数据透视表,庵后选择全部刷新
    image.png
    动态数据透视表.xlsx