第六课
- 动态引用数据源区域
- 数据源增加后透视表刷新效果
- 利用插入表格完成动态更新
- Offset 函数应用
- 参数含义:
- 例1,引用某个值:
- 例2,引用固定的区域:
- 利用Offset 函数设置动态引用区域
- 1,思路:
- 2、实操:
- 动态引用透视表结果值
- 试用常用公式引用透视表数据
- 认识GetPivotData 函数
- 开启GetPivotData 函数应用开关
- GetPivotData 函数参数介绍
- 常见GetPivotData函数应用
动态引用数据源区域
说人话就是:我在数据源里增加了新的内容,原来插入的透视表也能同时更新
数据源增加后透视表刷新效果
一句话,就是没变化,透视表框定的区域毕竟是死的
利用插入表格完成动态更新
在有数据清单的情况下,在插入数据透视表前,先插入表格,转成表格的形式
- 步骤:【插入】→【表格】→【创建表】→【插入数据透视表】
2. 要是就喜欢原来的样子,可以找到【表设计】
把【筛选按钮】去掉,样式选择“无”,假装无事发生~
变成这样:看,是不是真假难辨!
再正常插入数据透视表,这时候引用的就是表1了
当然了,想要什么样的奇葩名称还不是随你!
可以在【公式】下的【名称管理器】找到,然后,你懂得~
这时候你在表1里头添加新数据,表格的区域会自动扩展。回到透视表刷新就好~
3. 不要表格这个身份了!
想变为普通单元格就看这里↓:
Offset 函数应用
参数含义:
引用这么一个区域:以某个单元格为准,移~行,移 ~ 列,得到一个新单元格,以它为起点划定一个 ~高 ~宽的区域
例1,引用某个值:
像这种简单的表格,后4位参数我们可以直接用数字代替,来返回指定的一个值,如下图:
- 这里我分别以A1,和耿娇娇(C13)做为参考,都需要绝对引用。可以灵活使用,一般用$A$1。
- 由于行号是向下渐渐变大的,所以:1表示向下移动1行,-1表示向上移动1行。
同理,在第3参数里-4表示向左移动4列 - 那么=OFFSET($C$13,-1,0,1,1)的含义是:
以耿娇娇为起点,向上移动一行,列不变,取高1,宽1的区域,也就是一个单元格嘛!返回的是何娜娜~
例2,引用固定的区域:
- 这次我直接以A1为起点,完全不移动,圈定一个13行,5列的区域,那不就是这整个表了吗
- 就这样写:=OFFSET($ A $1,0,0,13,5)
- 缺点:不能动态引用
利用Offset 函数设置动态引用区域
1,思路:
- 要想动态引用,就得求助一个函数COUNTA
COUNTA(value1,value2…)
用于计算区域中非空单元格的个数 - 那么,不出意外的话,=COUNTA(A:A),计算的结果就是,最后一个值所在的行数。(前提是这个表比较规范哈~)
- 于是,它不就能替代最后的两个参数了吗
2、实操:
一般情况下比较万能的公式:
=OFFSET($ A$ 1,0,0,COUNTA($ A:$A),COUNTA($1:$1))
动态引用透视表结果值
也就是,透视表虽好,但我还是想在旁边呢,提炼一个小表,直接引用透视表里现成的值。
试用常用公式引用透视表数据
SUMIF
VLOOKUP
。。。
那么问题来了,以上引用都是相对位置的引用。只要是位置变动,那数据就会错乱。
(不要说位置怎么会变呢,筛选按钮一按,你再看,你大爷还是你大爷吗)
认识GetPivotData 函数
如图:在透视表外任意空格,输入=,点表中的一个结果值,就会出来这样一个函数(默认的)。回车,引用成功~就是这么简单!
这里我点的是C10,也就是冯文的数量汇总。
开启GetPivotData 函数应用开关
如果没有出现函数,可以这样打开:
1 位置:文件里【选项】→【公式】
2【数据透视表工具】→【数据透视表分析】→最左端【选项】下拉菜单
GetPivotData 函数参数介绍
第一参数:要引用的字段名,必须是文本
第二参数:所在的透视表:给透视表任意一个单元格的位置就行,默认是最上面的第一个字段
第3、4参数:条件1 位于xx字段下属的xx项目
第5、6参数:条件2 位于xx字段下属的xx项目
……
例1:
如图,是销售人员 冯文 在常熟的销售数量↑
例2:根据数据透视表可以,完美引用冯文的销售总数,以及他在南京的销售数量。
但是南京的销售数量,谁知道,没有给啊?
当然是再插入一个透视表,做一个地区汇总,不就有了。
- 总之,凡是透视表里有的,能点到的结果,都能引用过来。没有的就会报错。
- 那么报错怎么办呢:
以“冯文南京汇总”为例,在外面再包一个IFERROR函数:
=IFERROR(GETPIVOTDATA(“数量”,$A$3,“销售人员”,“冯文”,“所属区域”,“南京”),“无数据”)
意为:假设引用“冯文南京汇总”出错,则显示"无数据"
常见GetPivotData函数应用
- 假设我下次不引用南京了,换成苏州,需要它自动引用过来。
- 那好办呀,直接把对应的参数“南京”,替换成这个单元格。这里也就是把最后一个参数,替换成G9单元格。那也就是,直接引用单元格,自动实现动态引用的效果。
- 为了便利呢,还可以在【数据验证】里,添加【序列】。
- 问题:既然参数可以直接引用单元格,那统计项也做个序列,什么金额,成本,数量之类的!慢着!
第一参数必须是一个文本,可不能直接引用单元格~ - 办法:在后面加上&"",就能被转化为文本。
其他参数都可以针对单元格引用 - 可以随意输入一个数字加上&""看看还能不能运算