软件下载 点击激活 软件下载

Excel之数据管理规范

规范的数据源是数据透视表进行多角度汇总、分析和呈现数据的前提条件。数据源不规范会导致数据透视表创建失败、汇总统计出错、无法自动组合等问题。本章将重点介绍原始数据的管理规范及在日常工作中对不规范数据源的整理技巧。

 

数据管理规范

工作中的数据来源纷繁芜杂,没有规范的原始数据,会对后期创建和使用数据透视表带来重重障碍。磨刀不误砍柴工,要得到规范的数据源,需要先了解以下数据管理规范。

(1)Excel 工作簿名称中不能包含非法字符。

(2)数据源不能包含空白的数据行或数据列。

(3)数据源的列字段名称不能重复。

(4)数据源不能包含合并单元格。

(5)数据源不能包含多层表头,有且仅有一行标题行。

(6)数据源不能包含对数据分类汇总的小计行或总计行。

(7)数据源的数据格式要统一和规范。

(8)能在一个工作表中放置的数据源不要拆分到多个工作表中。

(9)能在一个工作簿中放置的数据源不要拆分到多个工作簿中。

01
Excel工作簿名称中不能包含非法字符

工作簿名称中不能包含非法字符, 如“/:*?”<>|”等,并未禁止使用字符“[”或“]”,但是创建数据透视表的工作簿名称中如果包含字符“[”或“]”,会导致无法创建数据透视表。提示“数据源引用无效”,如图1所示。

Excel之数据管理规范

图1 数据源引用无效

02
数据源不能包含空白数据行或数据列

数据透视表默认将连续非空列(行)的数据作为数据源,所以创建数据透视表时只需选择数据源的任意一个单元格。如果数据源包含空列或空行,会导致创建数据透视表时默认选择的数据区域不能包含全部数据,如图2所示。

Excel之数据管理规范

图2 空行(列)导致数据透视表默认数据源不完整

数据透视表不允许字段名为空,所以引用带有空列的数据源创建数据透视表时会提示字段名无效,导致创建失败,如图3所示。

Excel之数据管理规范

图3 提示字段名无效

03
数据源字段名称不能重复

当数据源的列字段名称重复时,创建的数据透视表会自动在字段名称后加上数字以区分多个字段,这样的数据透视表字段列表可读性较差,在进行统计汇总时容易造成字段拖放混乱,所以列字段名称应使其不重复且能直观反映该列数据代表的含义。

04
数据源不能包含合并单元格

合并单元格只有左上角的单元格含有数据信息,当数据源含有合并单元格时,可能导致数据透视表无法返回预期统计结果。

 

05
数据源不能包含多层表头

无论数据源有多少行表头,只有一行能够作为数据透视表的字段。多行表头也可能会因为合并单元格,导致字段名为空或字段名重复,如图4所示。

Excel之数据管理规范

图4 合并单元格导致创建数据透视表时字段名无效

06
数据源不能包含小计行或总计行

有些ERP 系统导出的数据源含有分类汇总的小计行或总计行。当数据源包含小计行或总计行时, 会导致数据透视表在统计时重复计算,从而返回错误结果。

 

07
数据源的数据格式要统一和规范

当数据源中的数据格式不规范时,会导致数据透视表在统计与汇总时出错。如文本数字不能正常参与计算导致汇总时出错,不规范日期进行组合时不能自动分组,从而大大降低工作效率。

 

08
数据源不要拆分到多个工作表中

数据源分处于多个工作表时,需要使用多重合并计算区域、SQL 语句或VBA 代码创建多个工作表的数据透视表,且可能给后期数据的添加、更新和文件的传递带来诸多不便。

 

09
数据源不要拆分到多个工作簿中

当数据源分处于多个工作簿时,不利于数据透视表的更新和传递。

 

对不规范数据的整理技巧

对合并单元格的处理之拆分合并单元格

图5所示为某公司销售清单。其中“产品类别”字段包含合并单元格,需要将合并单元格取消合并,并批量填充相对应的类别信息,具体操作步骤如下。

Excel之数据管理规范

图5 合并单元格拆分并填充

01
步骤一
Excel之数据管理规范

选中A 列单元格区域,依次单击【开始】选项卡→【合并后居中】按钮,如图6所示。

Excel之数据管理规范

图6 拆分合并单元格

02
步骤二
Excel之数据管理规范

按<Ctrl+G> 组合键或<F5> 功能键,弹出【定位】对话框,单击【定位条件】按钮。在弹出的【定位条件】对话框中选择【空值】,单击【确定】按钮,如图7所示。

Excel之数据管理规范

图7 定位空值

03
步骤三
Excel之数据管理规范

在编辑框中输入公式“=A2”,按<Ctrl+Enter> 组合键,如图8所示。

Excel之数据管理规范

图8 批量填充单元格

04
步骤四
Excel之数据管理规范

选中A 列数据区域,按<Ctrl+C> 组合键复制该列,在A 列数据区域上右击,在弹出的快捷菜单中单击【粘贴选项】下的【值】按钮,如图9所示。

Excel之数据管理规范

图9 选择性粘贴为值

快速提取文件夹内的文件名

微信扫一扫加关注,Office自学网官方微信公众号,

专注分享软件安装和视频教程,让你一号在手,办公无忧!

给TA打赏
共{{data.count}}人
人已打赏
办公技巧

Excel基本功:公式中的单元格引用方式

2022-9-22 10:11:59

办公技巧

Excel条件格式的两个使用技巧!

2022-9-23 10:42:24

文章版权声明 1、本网站名称:office自学网
2、本站永久网址:https://www.officezxw.com/
3、本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长QQ:1241926466进行删除处理。
4、本站一切资源不代表本站立场,并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报
6、本站资源大多存储在云盘,如发现链接失效,请联系我们我们会第一时间更新。

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索