在Excel中,Vlookup、Lookup函数几乎可以搞定所有的查找难题,但它们无法对数据透视表进行查找。
而GETPIVOTDATA函数是大多数人最熟悉又陌生的函数,很少人会用这个函数。
GETPIVOTDATA函数专为数据透视表而生,能快速抓取透视表中的数据,超级简单实用。
下面,一起来学习吧!
1、GETPIVOTDATA函数
1)取消勾选「GETPIVOTDATA函数」
若直接输入公式计算,会出现如图情况:
因为没有将GETPIVOTDATA函数关掉。
只需要点击数据透视表的任意单元格,选择「数据透视表分析」——「数据透视表」——「选项」,取消勾选「GETPIVOTDATA函数」,再输入需要计算的公式就可以了。
2)快速抓取透视表的数据
GETPIVOTDATA 函数能快速抓取透视表的数据,那如何操作呢?
重新勾选上GETPIVOTDATA 函数,在F5单元格中输入=,点击透视表,找到苏州第1季度警告标的值,最后按回车。
但你会发现,直接往下拉,显示的值都是一样的。
因为,还需要最后一步,更改公式。
原公式:
=GETPIVOTDATA(“金额”,透视表!$A$4,”订购日期”,1,”所属区域”,”苏州”,”产品类别”,”警告标”)
更改后:
=GETPIVOTDATA(“金额”,透视表!$A$4,”订购日期”,D5,”所属区域”,C5,”产品类别”,E5)
按回车,双击右下角即可。
2、计算字段
我需要计算产品的利润,直接利用公式添加发现是行不通的,那要怎么添加呢?
金额和成本是单独的两列,可进行计算字段来添加利润列。
1)添加利润
操作方法:
点击「数据透视表分析」——「字段、项目和集」——「计算字段」。
在「名称」栏,输入「利润」,在「公式」栏输入公式,=(金额-成本)/金额
注:选中金额或成本,必须要双击才能添加成功。
2)设置百分比
将利润列的数值设置为百分比格式,选中数值,点击「右键」——「设置单元格格式」——「百分比」。
3)删除错误值
选中错误值,「右键」——「数据透视表选项」——「打印」——「格式」,勾选「对于错误值,显示」。
3、计算项
仔细观察,你会发现预算额和实际发生额是在同一列的。
如果按照计算字段的方法来添加,你会发现字符栏选项下面,没有实际发生额和预算额字段。
因此,需要通过计算项来添加差额列。
1)删除行总计
点击「设计」——「总计」——「仅对列启用」。
2)添加差额
选中预算额,点击「数据透视表分析」——「字段、项目和集」——「计算字段」。
在「名称」处输入「差额」,在「公式」输入“=实际发生额-预算额”,最后点击「添加」,即可。
注:选中实际发生额或预算额,必须要双击才能添加成功。
3)如何删除差额
选中「差额」,点击「数据透视表分析」——「字段、项目和集」——「计算项」。
在「名称」处,选择「差额」,最后点击「删除」,即可。
微信扫一扫加关注,Office自学网官方微信公众号,
专注分享软件安装和视频教程,让你一号在手,办公无忧!