分享

为啥透视表老有问题?看完这篇终于找到答案了!

 Excel不加班 2021-10-26

每一样东西在使用的过程中都会出现各种各样的问题,出现问题不可怕,只要找到病根,就能解决掉问题。

对于大多数人而言,只要看链接的这篇文章就能解决掉一半问题:90%的女神用了透视表,都会出现这些问题!

剩下的一半问题,看完这篇也几乎能解决。

1.兼容模式闯的祸

都是用Excel2013以上版本创建的数据透视表,怎么两个数据透视表的求和项位置怎么不一样?

左边这种情况是在兼容模式下创建的数据透视表,可以看到兼容模式4个字。

单击“文件”选项卡,可以看到信息这里提示兼容模式,单击“转换”,在弹出的警告对话框,单击“确定”按钮。转换成功后再重新创建数据透视表就能得到右边那种效果。

兼容模式下会显示经典数据透视表布局,也就是可以直接将字段拖过去,而正常模式直接是拉到“数据透视表字段”对话框操作。

除此之外,属于高版本的功能:插入切片器、插入日程表等功能却显示灰色,无法使用,怎么回事?

这种问题同样是工作簿是兼容模式情况下的弊端,在兼容模式下很多功能都会被限制使用。

这里除了可以使用“转换”这个功能,其实还可以通过将工作簿另存为高版本的。

单击“文件”选项卡,再单击“另存为”,选择储存位置,保存类型选择:Excel 工作簿(*.xlsx)。

还有一点需要说明,就是用高版本的新功能制作数据透视表,在低版本是没法看到的,低版本不支持。

2.标题缺失与双行标题都不行


在创建数据透视表时,弹出“数据透视表字段名无效”的警告对话框,这是什么原因?

数据透视表字段名无效,也就是数据源的标题缺失,现在单位没写,只要将单位这个标题写上就行。

就是原始的数据源,里面出现了合并单元格,导致了部分内容没有标题。在需要进行汇总的表格,最好不用使用合并单元格,这样会造成一系列麻烦。

在第3行插入一行,将所有标题都写上。

在创建数据透视表时,区域选择从第3行开始以后的区域。

在完成了统计以后,可以将插入的这一行隐藏起来,这样看起来就跟以前的表格一样,但依然可以继续统计而不影响。

3.真假财务部,看似相同实则不同


创建透视表后,居然出现了两个财务部,而没有合并起来统计,怎么回事呢?

出现这种情况,最大的可能就是在录入数据的时候不小心录入空格,这是很常有的事儿。

返回到数据源工作表,Ctrl+H调出“查找和替换”对话框,在查找内容输入一个空格,单击“全部替换”按钮,这时可以看到一个对话框,提示完成2处替换,再单击“确定”按钮。也就说数据源存在了2个空格,符合我们的猜测。

再重新回到数据透视表工作表,右击选择“刷新”,财务部就已经进行了合并汇总了。

类似还有不同格式的数据,有的是文本格式,有的是常规格式,这样数据透视表统计的时候就会出现2个44030。

针对这种情况,不能直接采用将单元格设置为文本这种方法,可以采用分列功能进行统一格式。

选择区域,切换到“数据”选项卡,单击“分列”,在弹出的“文本分列向导”对话框,保持默认不变,连续单击两次“下一步”按钮。

选择“文本”格式,单击“完成”按钮。

对数据透视表进行刷新,就恢复了正常。

4.数据透视表选项中那些好用却被忽视的功能


01 取消更新时自动调整列宽

设置好的表格,一旦数据源更新,刷新数据透视表,列宽就会自动改变,这样一来前面的列宽设置就没用了,又得重新设置。如何才能让列宽始终保持不变?

右击选择“数据透视表选项”,在弹出来的对话框单击“布局和格式”,取消勾选“更新时自动调整列宽”,再单击“确定”按钮。这样以后不管怎么刷新,数据透视表的列宽都不会再改变。

02 对于错误值或对于空单元格的显示

有的时候数据透视表会出现一些错误值,为了美观,可以在“布局和格式”,勾选“对于错误值,显示”,后面的内容什么都不写,就代表显示空白。

03 设置打印标题

我们还经常需要打印标题,这时可以在“打印”勾选“打印标题”。

5.一个职业一个表格


是某培训班的学员资料,现在要根据职业,把每个职业的人员信息分成多个表格,一个职业一个表格,该如何处理?

像这种分成多个表格的,水平高的都会采用VBA代码,但对于普通人而言,借助数据透视表,也能轻松实现。

STEP 01 创建数据透视表,将职业拖到筛选器,其他字段依次勾选。

STEP 02 单击“设计”选项卡,选择“报表布局”,单击“以表格格式显示”。

STEP 03 选择“分类汇总”,单击“不显示分类汇总”。

STEP 04 单击“分析”选项卡,选择“选项”,单击“显示报表筛选页”,在弹出的“显示报表筛选页”对话框,单击“确定”按钮。

通过上面4步,生成4个表格,每个职业一个明细表。

6.在每个项目后面插入分页符


一个职业一个表格,如果只是为实现打印方便,其实还有另外一种做法,就是在每个项目后面插入分页符。

STEP 01 先将数据透视表的布局略作改变。

STEP 02 单击职业这个单元格,右击选择“字段设置”。

STEP 03 单击“布局和打印”,勾选“每项后面插入分页符”,单击“确定”按钮。

STEP 04 单击职业这个单元格,右击选择“数据透视表选项”。

STEP 05 单击“打印”选项卡,勾选“设置打印标题”,单击“确定”按钮。

STEP 06  借助快捷键Ctrl+P调出打印预览,对打印的页面进行调整,最终分成4页,每一页都包含标题。

7.将数据源中没有的项目显示出来


用日期组合的时候,只显示2个月份,如何在数据透视表显示所有月份?

右击选择“字段设置”,单击“布局和打印”,勾选“显示无数据的项目”,单击“确定”按钮。

这样所有月份就都显示出来,不过多了2个不是月份的,有点碍眼,直接筛选的时候,取消勾选这2个就行。

8.数据透视表之再透视

这是用数据透视表统计出来的结果,现在想统计100、200、300这些数量出现的次数,该如何处理?

我们都知道,数据透视表可以对数据源进行汇总,其实数据透视表还可以对另外的数据透视表进行汇总,也就是数据再透视。

STEP 01 创建数据透视表,选择原始数据透视表不包含总计的区域,将数据透视表的位置放在D3,单击“确定”按钮。

STEP 02 将求和项:数量拖到行字段,行标签拖到值字段,也就是说将原始数据透视的字段位置转换过来。

再对字段进行重命名,效果会更好。

数据再透视,是转换一个思维处理事情,正难则反。

推荐:知道这个透视表不联动功能的人,都已经老了。。。

上文:用了LOOKUP(1,0/,.....)的这个套路,表格好卡好卡,怎么办?

以上那些问题你曾经遇到过哪些,还遇到过哪些新问题?

作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)

    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多