规范Excel数据透视表的数据源,看这一篇就够了!

原创作者 | 李锐

微信公众号 | Excel函数与公式(ID:ExcelLiRui)

微信个人号 | (ID:ExcelLiRui520)

规范Excel数据透视表的数据源,

看这一篇就够了!

数据透视表的威力虽然无比强大,但使用前提是数据源要规范,否则会给后期创建和使用数据透视表带来层层阻碍,甚至无法创建数据透视表。

很多新人由于不懂得如何规范数据源,而对阻挡在数据透视表的大门外,此文帮助大家了解规范数据源的几点要求,以及如何修正不规范的数据源!

下面把这些规则结合案例展开,便于大家记忆和理解。

1 不能包含多层表头,或记录中多次插入标题行

很多单位喜欢用多层表头,比如工资表,表格的第一行和第二行都是表头信息,这类报表在创建数据透视表之前需要将双层表头合并为一行。

另外还有一种情况是,数据行之间穿插多个标题行......

看看下面这个gif演示,为了让报表查看过程中能随时看到标题行,并且打印时可以每页带上标题行,我见过很多人这么干......

其实要想随时查看到顶端标题行,冻结窗格即可,点击如下gif动态演示效果

2 数据记录中不能带空行

下面这个案例,连续的销售记录被空行分隔开。

这样的报表无法直接利用Excel的分类汇总功能和数据透视表汇总!

给出批量删除多余空行的办法,我的操作演示效果(下图gif动态演示)

3 原始记录不能和计算行混杂

工作中的原始记录和计算行混杂,也是很多人常犯错误之一

这样导致报表无法批量利用数据透视表汇总,而且当数据源更新时,工作强度很大,还极易出错!

这种情况下,最快捷的办法就是选中全表筛选A列小计,然后批量删除。

4 数据源中的文本型数字要转换为数值

工作中很多系统导出的数据都是文本型数字,这样的数据源会导致数据透视表默认进行计数统计,而不是求和统计,后期处理很麻烦。

其实只要掌握一定技巧,可以快捷的修复这类数据源

下面这个操作就是利用选择性粘贴<Ctrl+Alt+V>将文本数字批量转换为真正的数值。

点击下图gif可查看动态演示过程

5 数据源中不能包含重复记录

当数据源中包含很多重复值时,我们需要先批量删除重复项,再进行数据透视。

当判定重复的条件不止一个时,手动删除起来非常麻烦,用如下方法,可以几秒内完成多个条件的重复项判定并批量删除重复数据,一劳永逸!

点击下图gif可查看动态演示过程

6 不要输入不规范日期

不规范的日期数据给工作带来很多困扰,比如无法正确排序,无法提取正确的年月日信息等......

利用下面的技巧,可以帮你批量修复不规范的日期格式

7 不要包含合并单元格

工作中带合并单元格的报表随处可见,如下:

这样不但使数据难以直接用数据透视表汇总,连函数计算都受限!

下面给出智能填充合并单元格的正确姿势:

我的操作演示效果(下图gif动态演示)

8 数值和单位不能放在一个单元格

下图所示C列,既包括数值又包括单位,导致Excel无法直接求和

处理这个问题的正确姿势,用函数提取对应的信息,分列单独放置

D2=--LEFT(C2,2*LEN(C2)-LENB(C2))

E2=SUBSTITUTE(C2,D2,)

还想看更多精彩?更多实用Excel技能请长按识别下图二维码,里面有各种方向不同的Excel特训营,每一期特训营内容不同,都可以帮你系统提升自己!

Excel特训营老学员,联系小助手(ExcelLiRui03)领取优惠

跟李锐学Excel,高效工作,快乐生活!

一次购买,永久学习,手机、iPad、电脑任意时间听课

长按下图  识别二维码,参加Excel实战特训营

Excel特训营老学员,联系小助手(ExcelLiRui03)领取优惠

长按上图↑识别二维码,查看详情

9 列字段不要重复,名称要唯一

这个不用截图表述了,直接文字即可讲明白。

当表格中多列数据使用同一个名称时,会造成数据透视表的字段混淆,后期无法分辨数据属性,所以各列字段名称要保持唯一,不要重复。

10 能放在一个工作表里的数据不要分散放到多个工作表中

如下图,本来连续的同类数据被分散在多个工作表,每个日期单放一个工作表

这样到了月底,本来很简单的问题月汇总也变得非常麻烦,甚至需要动用vba代码。

其实这些数据应该放置在一个工作表中,直接数据透视即可瞬间搞定!

如果你用的是Excel 2016版,可以利用下面的方法多表合并,很简单,不出一分钟就OK!

利用追加查询,合并多表数据

最后关闭并上载

这样,位于不同工作表中的数据,瞬间已经合并完成啦~

好啦,这些内容仅仅是数据透视表系列教程的第二篇,讲解了规范的数据源的要求,以及如何修正不规范的数据源,让大家能顺利的创建数据透视表!此系列持续更新中,明天见~

如果你觉得有用,就分享给朋友们看看吧~

别忘了帮忙去底部点赞

>>推荐阅读 <<

(点击蓝字可直接跳转)

vlookupsumifcountifsumif丨sumproduct

indexmatchdatediffrequency个税年终奖

点“阅读原文”进入直播间+关注,不再错过精彩!

(0)

相关推荐