Excel数据处理的完整流程包括:数据输入→数据存储→数据加工→报表输出。
我们使用Excel进行数据处理的目的主要有三个:
I 事物性数据记录;
II 制作报表、图表;
III 利用Excel强大的数据分析功能,进行数据分析。
参照上述数据处理的流程和目的,我们可以将Excel表格分为基础数据表、事务性明细数据表、报表三大类。
I 基础数据表(事务性明细数据记录的数据源)
基础数据表是对需要记录的事物的基本特性的描述,如商品编号、名称、规格、单位、单价、供应商等数据。基础数据表也是确保统计对象唯一性的编码与名称+规格的对照表。
II 事务性明细数据表(数据分析的数据源)
可以理解为保存基础、事务性数据的仓库,是记录数据的清单,是一张由行和列组成的一维表格(首行为字段名、其它行为记录)。表格中的一列数据记录一类信息,一行数据记录一个数据对象的多种信息,所以数据表也可以称为“数据库”。每列的第一行是字段名(变量名),其余列是字段值(变量值)。
“列”在数据库中称为“字段”,字段名称在数据表中不能重复,每个字段只记录同一个含义、同一类型的数据。
“行”是除去首行(标题行)外的一行数据被称为“一条记录”。
III 报表
报表是呈现数据结果的表格,是对事务性明细数据进行加工后形成的表格。报表不仅需要具备直观、易用和容易理解的特点,还需具备美观、得体的特点。
常见的数据分析方式有:排序、分列汇总、筛选、应用公式和函数、插入数据透视表等。
基础数据和事务性数据的规范是数据管理中最为关键的环节,其在很大程序上决定着报表的质量高低以及数据分析的准确性。数据表的数据存储只需要将“事务”完整地记录下来,其在设计理念上也不同于报表。
基础数据和事物性明细数据要确保数据记录和分析的规范性,需要从以下方面进行考虑或改善:
列字段是数据排序、筛选、分类汇总、数据透视表的字段依据。
常见的不规范做法就是将表格的首行或首行在内的区域设置为表格名称, 所以要尽量避免让表格名称占据数据表的首行(用来展示数据分析结果的报表可以)。而表格标题在Excel中可以用其它的方式来表示:用工作簿或工作表的名称来表示,如果需要打印数据源表格,也可以在页面设置中用表格名称来自定义页眉。
另外,列字段不得为空白。
首行列字段使用多行表头和斜线表头同样会破坏数据关系或数据结构。单行表头才能表示数据记录的列字段。
在数据表使用合并单元格会严重破坏数据表的数据结构。
当数据源中有合并单元格,常常会给数据分析带来困扰,或直接出现错误提示,特别是使用数据透视表时。对于数据表本身一些功能的使用也会带来困难,如造成数据表排序、筛选、分类汇总的功能失效。
当然,合并单元格也并不是一无是处,如果使用在呈现数据结果的报表或不需用作数据分析源的表格中,使用合并单元格可以让数据显得更加美观、大方、简洁。
在数据表中人为插入空白列或空白行,会破坏数据区域的连续性,使数据表的排序、筛选的功能失效。或者造成数据透视表的数据源选择出错。
在数据表中,不能用一个字段来保存多种类别数据,也不能使用多个字段来保存同一属性的数据。
如字段名为“销售数量”的列中不能是文本类型,字段名为“日期”的列也不能是文本类型。当然更要避免就某一列的某个单元格单独修改为其它数据类型。
当在单元格中的数据的首尾或中间有空格或不可见字符时,会给数据分析(应用函数或数据透视表)时带来困扰,如同样的数据值,但当有的单元格的尾部有空格、有的没有空格时,会被分析为不同的字段值。
当在单元格中使用“Alt+Enter'组合键进行换行时,字段值只是第一行的数据。
也就是不要对同一数值在不同区域交替使用简写、别称、全称,要统一使用全称、或简写。如字段名为“部门”的列,不要时而是“人事部”,时而又是“人力资源部”或“人力部”。
在称呼事物、应用函数(如vlookup函数)、数据透视表时必须通过对统计或分析的对象通过唯一的编号来避免歧义。
如在公司、学校或者其他组织中,姓名并不能确保唯一,可以使用身份证号、工号、学号等编号。在货物管理中,也需要通过货物编号来代表某一货物名称及其规格,确保一物一码。
数据表作为数据源并不需要在数据表中一边增加记录,一边进行各种小计、合计的操作,这样在后续的分类汇总、数据透视表操作时会出错。数据源表格中只需增加记录、修改记录就可以了。
如Excel中正确的日期数据格式类型为2017-7-31或2017/7/31,而不要采用2017.7.31或20170731等非法的日期格式。
数据字段设置的合理与否对数据分析结果很关键。同时,要避免将字段作为行标题(要用列标题作为字段,首行以外的其它行只是做为字段的值作为记录)。
如有人喜欢按月去记录相同结构的数据,这样在分析数据时会带来极大不便(当然,通过VBA可以将不同工作表中相同结构的数据合并到一起)。很显然进行全年汇总、筛选、排序、远不如在一张工作表中合适。
使用外部链接,不但影响表格的打开速度,而且当工作表移动或者删除时,还容易出现断链,不易于查找和修复。
一般对少量特殊的单元格内容可以使用批注进行特别说明,但是如果在大范围内需要对单元格说明时远不如设置“备注”字段,因为列字段相比批注来说,更方便数据分析。
“25+50”的单元格是无法进行数据的数值类型的运算的。当想让数据内涵更丰富、更有追溯性时可以写成'=25+50'的形式。
多个主题宜规划多个工作表。
Excel适合处理小规模数据(大规模数据宜使用数据库),Word适合用来处理文字,而PPT则侧重于演示汇报数据。
因为进行多步骤处理时,一旦中间某个环节出现失误,无法将数据恢复到最初的状态。
因为大量使用它们会明显降低表格的计算速度。
如不使用Round函数,会出现四舍五入、浮点运算导致的误差。
问题项 | 解决方法 |
每一列中数据格式是否一致 | 用IS类函数检测数据类型,采取刷新、分列、type函数等方法 |
是否存在重复的数据 | 删除、标记重复项 |
数据中是否包含多余的空格或者不可见字符 | 批量替换、利用记事本或Word处理、Trim、Clean、Numbervalue等函数去掉不可见字符、定位条件删除 |
单元格中是否存在换行符 | 查找替换对话框的查找内容中按Alt+10组合键,批量替换换行符,或使用Clean()函数 |
是否存在数据长度不一致 | 用len函数检查数据长度 |
文本型数据是否存在大小不一致的问题 | 利用Upper、Lower、Proper函数转换大小写 |
是否存在不应是空白状态的空白单元格 | 利用Isblank函数等检查是否真的为空白 |
表格中无法插入行或列 | 删除空白列或者空白列、取消合并单元格调整后再插入 |
报表不同于作为数据源的数据表,在报表中可以使用合并单元格、多表头、斜线进行美化。但报表的设计应力求结构合理、层次清晰、重点突出、排版美观、方便阅读和打印。
联系客服