规范Excel数据透视表的数据源,看这一篇就够了! | 您所在的位置:网站首页 › 创建数据透视表之前要排序吗 › 规范Excel数据透视表的数据源,看这一篇就够了! |
规范Excel数据透视表的数据源,看这一篇就够了! 数据透视表的威力虽然无比强大,但使用前提是数据源要规范,否则会给后期创建和使用数据透视表带来层层阻碍,甚至无法创建数据透视表。 很多新人由于不懂得如何规范数据源,而对阻挡在数据透视表的大门外,此文帮助大家了解规范数据源的几点要求,以及如何修正不规范的数据源! 1 不能包含多层表头,或记录中多次插入标题行 2 数据记录中不能带空行(正确姿势) 3 原始记录不能和计算行混杂 4 数据源中的文本型数字要转换为数值 5 数据源中不能包含重复记录 6 不要输入不规范日期 7 不要包含合并单元格 8 数值和单位不能放在一个单元格 9 列字段不要重复,名称要唯一 10 能放在一个工作表里的数据不要分散放到多个工作表中 下面把这些规则结合案例展开,便于大家记忆和理解。 1 不能包含多层表头,或记录中多次插入标题行 很多单位喜欢用多层表头,比如工资表,表格的第一行和第二行都是表头信息,这类报表在创建数据透视表之前需要将双层表头合并为一行。 另外还有一种情况是,数据行之间穿插多个标题行...... 看看下面这个gif演示,为了让报表查看过程中能随时看到标题行,并且打印时可以每页带上标题行,我见过很多人这么干...... 其实要想随时查看到顶端标题行,冻结窗格即可,点击如下gif动态演示效果 2 数据记录中不能带空行 下面这个案例,连续的销售记录被空行分隔开。 这样的报表无法直接利用Excel的分类汇总功能和数据透视表汇总! 给出批量删除多余空行的办法,我的操作演示效果(下图gif动态演示) 3 原始记录不能和计算行混杂 工作中的原始记录和计算行混杂,也是很多人常犯错误之一 这样导致报表无法批量利用数据透视表汇总,而且当数据源更新时,工作强度很大,还极易出错! 这种情况下,最快捷的办法就是选中全表筛选A列小计,然后批量删除。 4 数据源中的文本型数字要转换为数值 工作中很多系统导出的数据都是文本型数字,这样的数据源会导致数据透视表默认进行计数统计,而不是求和统计,后期处理很麻烦。 其实只要掌握一定技巧,可以快捷的修复这类数据源 下面这个操作就是利用选择性粘贴将文本数字批量转换为真正的数值。+alt+v> 点击下图gif可查看动态演示过程 5 数据源中不能包含重复记录 当数据源中包含很多重复值时,我们需要先批量删除重复项,再进行数据透视。 当判定重复的条件不止一个时,手动删除起来非常麻烦,用如下方法,可以几秒内完成多个条件的重复项判定并批量删除重复数据,一劳永逸! 点击下图gif可查看动态演示过程 6 不要输入不规范日期 不规范的日期数据给工作带来很多困扰,比如无法正确排序,无法提取正确的年月日信息等...... 利用下面的技巧,可以帮你批量修复不规范的日期格式 7 不要包含合并单元格 工作中带合并单元格的报表随处可见,如下: 这样不但使数据难以直接用数据透视表汇总,连函数计算都受限! 下面给出智能填充合并单元格的正确姿势: 我的操作演示效果(下图gif动态演示) 8 数值和单位不能放在一个单元格 下图所示C列,既包括数值又包括单位,导致Excel无法直接求和 处理这个问题的正确姿势,用函数提取对应的信息,分列单独放置 D2=--LEFT(C2,2*LEN(C2)-LENB(C2)) E2=SUBSTITUTE(C2,D2,) 9列字段不要重复,名称要唯一 这个不用截图表述了,直接文字即可讲明白。 当表格中多列数据使用同一个名称时,会造成数据透视表的字段混淆,后期无法分辨数据属性,所以各列字段名称要保持唯一,不要重复。 10 能放在一个工作表里的数据不要分散放到多个工作表中 如下图,本来连续的同类数据被分散在多个工作表,每个日期单放一个工作表 这样到了月底,本来很简单的问题月汇总也变得非常麻烦,甚至需要动用vba代码。 其实这些数据应该放置在一个工作表中,直接数据透视即可瞬间搞定! 如果你用的是Excel 2016版,可以利用下面的方法多表合并,很简单,不出一分钟就OK! 利用追加查询,合并多表数据 最后关闭并上载 这样,位于不同工作表中的数据,瞬间已经合并完成啦~ 好啦,这些内容仅仅是数据透视表系列教程的第二篇,讲解了规范的数据源的要求,以及如何修正不规范的数据源,让大家能顺利的创建数据透视表!此系列持续更新中,明天见~ 如果你觉得有用,就分享给朋友们看看吧~ 干货每天都有,如果你想每天第一时间收到, 如下操作,两秒搞定: 新朋友:点页面右上角三个点→查看公众号→关注 老朋友:点页面右上角三个点→查看公众号→置顶公众号 更多的Excel应用技巧,可以点击文尾的“阅读原文”,获取更多教程。 今天就到这里,大家记得收藏此教程,明天见~ 李 锐 微软全球最有价值专家MVP 新浪微博Excel垂直领域第一签约自媒体 百度名家,百度阅读认证作者 每日分享职场办公技巧原创教程 高效工作,快乐生活! 微博 @Excel_函数与公式 微信公众号(ExcelLiRui) 长按识别二维码,关注并置顶公众号 每天都可以收到最新的Excel干货教程 ▼点击“阅读原文”,获取更多教程。 |
CopyRight 2018-2019 实验室设备网 版权所有 |