最近一段时间,透视表的文章发的很少,看到好多留言,发觉你对透视表陌生得就像一个外行。卢子针对疑问一个个来解答。
1.计算每个行业年薪的最大值、最小值、平均值
创建透视表,将行业类型拉到行,年薪拉3次到值。
右键,值汇总依据依次改成最大值、最小值、平均值。
2.金额更改汇总依据为求和后变成0
这种是原来的金额就是文本格式,导致求和为0。
针对这种,直接对金额进行分列就可以将格式转换成数字,从而可以求和。
3.日期无法组合
大多数情况下都是因为是文本格式导致,跟案例2一样,分列转换格式。还有一种就是区域选太大,包含空单元格,区域改成实际有内容的区域就行。就不做动画演示。
4.自动统计
插入表格获取动态数据源+VBA自动刷新,这个早期有文章,如果不懂直接点文章进去即可:透视表除了能自动统计,还能……
5.透视表按照数据源的顺序排序
透视表默认是按升序排序,而不是按数据源的顺序排序。
这种常用的解决办法就是将行业类别删除重复值后,自定义排序。不过卢子习惯用辅助列MATCH,获取原来的排位。
=MATCH(B2,B:B,0)
更改数据源后,将排位也拉到行,再点设计,报表布局,以表格形式显示,不显示分类汇总。
当然,这里如果辅助列公式略作改动,操作会更简单,就是将排位和行业类型连接起来。
=MATCH(B2,B:B,0)&B2
不过为了防止排序出错,公式再略作改进,前面的数字用TEXT处理成统一位数。
=TEXT(MATCH(B2,B:B,0),"000")&B2
这样直接将辅助列拉到行就行,就无需多余的操作。
6.统计某个区间的个数
如年薪20-30,用一个辅助列判断区间。
=IF(AND(D2>=20,D2<=30),"20-30","")
如果是多个区间的,可以建立一个对应表,VLOOKUP对应值。
=VLOOKUP(D2,$H$2:$I$4,2)
再将区间分别拉到行、值。
以上就是昨天的全部透视表疑问解答。
推荐:你为什么不用透视表?
关于透视表还有什么疑问?
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)
联系客服