关于数据透视表,是大家学习Excel绕不开的一个关键内容,
我经常因为数据透视表被大家提问,遇到的各种疑难问题,这一期我们一起好好来盘点一下,当然还有可能没有讲到的大家也可以给我留言.
1
日期/数值格式问题
下方是一份数据源,当我插入数据透视表,想要根据时间进行数据汇总的时候,发现内容并不会数据不会进行汇总计算,时间也不会进行年,月,季度之类的分组显示:
我很早之前讲过一个问题,当你在单元格中输入"文本",文本会靠单元格的左侧,数值格式日期格式会靠单元格的右侧,这是一个基本规律,无外如是.
所以当你第一次插入数据透视表的时候,你应该先观察一下,你的表格数据格式,通常从系统导出来的数据都是文本格式,如下所示:
在默认的非设置居中会左对齐的情况下都靠单元格的左侧,所以所有内容都是文本格式.文本是无法进行计算的.
这个时候,你应该做的是把时间和数值类型的列都分别进行一次分列处理.
这样就可以把日期转化成日期格式,文本数值转化成数值格式.
你再插入数据透视表,就可以得到以下的结果:
在当前的OFFICE中如上图所示,日期格式会发生自动的组合,直接变成月份年份之类的分组显示,
在WPS当中并不会发生自动的分组,所以如果你是WPS可以选择A列然后右键选择组合,把时间字段根据需求进行选择调取.
2
月份排序问题
大家在用数据透视表的时候,如果和月份有关系,你是不是遇到过下面这样的情况:
10,11,12月份排在最前面?
如果遇到这种情况,或者你没有如下边这样设置过,你一定要打开自己的Excel表格,做一个简单的设置,以后这种问题就不会出现了.
选择文件>选项>高级>编辑自定义列表
打开
自己手动在输入序列那,输入:
1月,2月,3月......一直到12月.如下如所示:
然后再选择行标签,进行一个升序排列就好了,
包括以后在插入数据透视表也不会出现10月排在最前面.
包括大家或许也遇到过,切片器10月排在前面,你只需要按照这个方法设置过,以后就不会出现这些问题.
3
透视之前先做超级表
很多同学反映过一个问题,为什么自己的表格数据更新之后,透视表不会更新,这个是一个常理,或者以后大家一定要这样做.
在做透视表之前,原始数据表格一定要CTRL+T变成超级表.
一定要提前变成超级表.
如上图,我想分析出每一个产品每一个负责人他们的销售额情况.
之前的超级表数据内只有一年的数据,现在又有一份新的数据直接可以复制粘贴到超级表的下方,超级表会自动的延伸超级表范围.
而数据透视表之前是用超级表插入的,所以超级表更新之后,你再选择数据透视表刷新一次,新的数据就会被并入进行汇总计算:
4
年份月份切片器
如下图,是刚才完成的透视表分析,当你在这个情况下想要插入和年份季度有关的切片器的时候,你会发现没有这些选项:
首先,我们应该把透视表做一个复制粘贴到一个空白区域,
然后把日期字段放进透视表的行所在的位置,组合出来年份季度月份等等.
当你用一个透视表组合出来了年份月份季度之类的字段之后,你再选择透视表,才可以插入选择年份季度之类的切片器选项.
和时间相关的切片器都极容易出现下面这样的灰色部分,
按住CTRL选择切片器,右键设置,选择隐藏没有数据的项目就好了.
5
筛选表格串动
下面这幅图看好了,当你选择切片器的时候,你有没有发现,你的表格左右的串动?
这个问题的产生是因为:
当你进行切片器的选择的时候,由于筛选的数值长短不一样,透视表的宽度会根据数值的长度发生变化.
如果你经常使用透视表,会觉得很烦的相信我.
当然这个也不会影响功能,只是单纯的不舒服...
所以,如果你愿意,可以选择透视表,右键设置,把自动调节栏宽取消掉.
6
透视表的字段计算
如下图,在原始表格中一列销售额,有一列成本.
销售额-成本=毛利
在这个简易环境下,你可以直接先插入透视表
产品放在行,销售额和成本放在值的位置.
选择透视表>数据透视表分析中找到:
字段项目和集.
输入公式如下:
毛利就可以直接计算出来了.
再延伸一步,如果你要算毛利率:
输入公式如下就可以了:
7
添加数据模型?
在插入数据透视表的时候,你是否发现:
在插入菜单中有一个叫做:
将此数据添加到数据模型
需要特别明确一点:
如果你插入到数据模型中,数据透视表的字段计算,就无法进行了.
所以通常情况下都不会选择:
放进数据模型.
除非你会有以下的需求:
我想根据数据源分析:
每一个负责人负责的地址有多少个?
在默认的计数情况下,例如:
陈博2654次,指的是陈博在原始表格中有2654次的销售次数.
而并非他所负责的地址有2654个,
比如表格中可能会存存在湖南1000次,湖北1000次,福建654次.
其实去重的结果他所负责就是就是3个地点.
所以,你可以选择非重复计数,
非重复计数,也只能是在数据模型的情况下才可以被使用.
在没有添加到数据模型的时候,是无法使用的.
所以大家一定要区分清楚.
8更换透视表数据源
如下图,你或许遇到过这种情况,明明数据源当中就没有空白单元格,
但是当你看透视表的时候却出现了一个空白位置.
如果你完全是按照透视表的操作规程去做的,不会出现这样的情况.
遇到这个问题,首先你要检查,是否你的表格变成了超级表.
如下图,数据源为超级表.
并且表格名称叫做:表1
然后你再选择到当前数据透视表,
选择数据透视表分析>更改数据源
你会发现,可能你选择的范围并非是超级表范围,而是选择的数据源表格的整个A:F列.
A:F当然就会在超出超级表的范围中包含很多空白的位置.
选择数据透视表,之后更改数据源,把数据源更换成超级表格的名称:
表1
就可以了.
以上!
更新不易.
如果喜欢老徐的内容,记得给老徐留下一个赞!!
谢谢大家的观看和支持!
