第三节 一可赛二——规范化数据处理的偷懒心法
易筋经云：“登正果者，其初基有二：一曰清虚，一曰脱换。能清虚则无障，能脱换则无碍。无碍无障，始可入定出定矣。知乎此，则进道有其基矣。所云清虚者，洗髓是也；脱换者，易筋是也”。本节的目的就是帮助读者在数据管理和表格设计方面从理念和心法上“洗髓、易筋”，克服表格设计的种种陋习，了解各类表格的基本设计规范，掌握高效数据处理的实用技巧，让你在Excel表格应用方面脱胎换骨，无障无碍。
本节是站在理论高度来介绍数据管理和表格设计，读起来可能略显枯燥。但是，强烈建议你认真仔细地阅读完，并且还要多读几遍。因为这些心法都是笔者走了很多弯路后才摸索出的经验。偷懒，首先要练好基本功！掌握了这些数据处理原则，会让你在学习Excel的过程中更快地走上快车道，使用Excel的理念也会脱胎换骨，看表格的眼光会上升一个层级。
那么，如何才能练好偷懒的基本功呢？
一、始终有“根据用途确定表格类型及结构”的意识
不同的用途需要使用不同的表格，不同的表格有不同的设计要求。
我们用Excel进行数据处理的目的主要有三个：存储数据，制作图表以及利用Excel强大的数据分析功能进行数据分析，提炼出报表。如果数据量不是很大，可以在Excel中直接录入数据并保存，再进行加工分析。如果是海量数据，Excel则无法胜任，这不是我们讨论的范畴，暂且略过。因而，Excel数据处理的完整流程包括：数据输入、数据存储、数据加工以及报表输出。
根据所处的Excel数据处理的流程环节以及表格的性质不同，可以将Excel表格分为：清单型、报表型、其他型三大类型。
1.清单型表格
与数据存储环节相对应的是清单型表格。此类表格其实就是一个数据仓库，主要用于存储基础数据，其数据来源可能是直接录入，也可能是外部数据导入。各种台账（见图1-8中的合同台账）、凭证序时簿、业务明细表等都是清单型表格。其使用对象是数据加工者，数据加工的所有基础数据大多来源于清单型表格。因而，清单型表格设计是否合理，格式是否规范，将直接影响到后期的数据加工效率。
图1-8 清单型表格示例
2.报表型表格
与报表输出环节相对应的是报表型表格。数据量较小时，清单型表格与报表型表格合二为一；数据量较大时，就必须用报表型表格来进行汇总数据的信息输出，它反映的信息都是经过加工处理高度浓缩后的。比如各种汇总表、财务报表等（见图1-9），其使用对象就是信息的最终用户。
图1-9 报表型表格示例
3.其他型表格
除前两者之外的表格就是其他型表格。其他型表格主要用于数据的输入、作为参数表或用于数据分析。
在使用Excel编制各种表格时，一定要有区分表格类型的意识。由于不同类型的表格，其结构和格式都不同，因而其使用原则和设计要求也是不同的。如果清单型表格当报表型表格来设计，就会给后期数据分析带来很大的障碍。例如上文中提到的图1-2“逸凡公司合同登记台账”就是因为违背清单型使用原则，使得本可以五至十分钟就完成的工作需要花费一两天才能完成。
各类表格的使用原则和设计要求详见后文“三类表格的具体设计要求”。
二、遵循六大使用原则，从心所欲不逾矩
我们使用Excel表格的目的就是对基础数据进行加工分析后，整理出需要的信息，然后制作出报表，提交给信息的最终使用者。数据量较大时，各环节中最关键、最复杂甚至直接决定最终报表质量的就是数据处理环节。因而，进行Excel表格设计时就要一切以数据加工为中心，始终以“为数据加工服务”为原则。基于此，在Excel表格设计时要遵循以下原则。
1.数据管理原则
在实际工作中，要有良好的数据管理理念，要先根据数据量的大小、表格的用途确定表格的类型，然后再确定表格的整体结构、布局。
➣清单型表格的结构取决于数据的类型和复杂性，一定要结构简单、逻辑清晰、无冗余数据，以方便数据加工为原则。
➣报表型表格以用户需求为原则，在清晰直观地反映信息的基础上，适当考虑报表使用者的习惯。
➣其他型表格要以实用、效率为原则。
在设计表格时要问自己：此表是用于存储数据还是作为报表输出。如果用于存储基础数据，那么数据的格式、表格的结构和格式一定要有利于数据的加工分析，凡是不利于数据加工分析的结构和格式一律要摒弃。如果是报表输出则不必讲究，只要结构清晰、格式美观、直观地反映数据即可。
2.一致性原则
一致性原则要求表格内、表格之间的字段名称、数据类型、表格结构格式等要保持一致。具体来讲有三个基本要求：同物同名称、同表同格式、同列同格式。
同物同名称：狭义的同物同名称也就是说同一对象只能使用同一个名称，同一对象的名称在任何表格、任何人员、任何部门、集团内的任何公司间都要保持一致，以便数据统计和表格间的数据引用。比如图1-2“逸凡公司合同登记台账”中的重庆环宇实业和环宇实业公司实际为同一家公司，但写成前面两种名称，对于Excel来说就是两家不同的公司，在函数求和、分类汇总、数据透视表进行数据加工时，都会按不同的公司进行统计。
广义的同物同名称指对其他具备共性的信息制定统一的命名规则。例如，同类型工作簿应使用统一格式的文件名。以各月的财务报表为例，我们可以将文件名设定为“逸凡公司XXXX年X月财务报表”，而不要出现“逸凡1月报表”“逸凡公司2014年2月财务报表”等之类的情况。工作簿、工作表的名称保持同样的样式，才能在表格翻新时使用查找替换快速修改公式。表格翻新是指本月复制上个月表格，将上月引用的表格修改成本月引用的表格。
【链接】关于表格快速翻新技巧，请详见第二章第三节《坐享其成——报表翻新的偷懒妙招》。
同表同格式：相同的表格其表格结构和格式必须保持一致，以便应用函数统计汇总数据，这样才能大大提高操作效率。比如图1-6“各月费用汇总表”，如果1～12月各月的表格格式不一样，假如某张表格的范围不是A1:B12，那么在使用“=SUM('1月:12月'!B2)”公式统计时就会出错，只有应用常规的公式：
='1月'!B2+'2月'!B2+'3月'!B2+'4月'!B2+'5月'!B2+'6月'!B2+'7月'!B2+'8月'!B2+'9月'!B2+'10月'!B2+'11月'!B2+'12月'!B2。
同列同格式：表格的列相当于数据库中的字段，同一列应保持同一格式，比如不能将某列一些行设成文本、其他行又设成数字或日期等格式。
3.规范性原则
名称规范、格式规范：表格中的各类数据使用规范的格式，数字就使用常规或数值型的格式，而不应使用文本型的格式。日期型数据不能输入“20140106”“2014.1.6”“14.1.6”等不规范的格式，否则在将日期型单元格进行运算时，会影响数据的加工处理。比如，数字使用文本格式，使用SUM函数求和时就把它视为字符串，求和结果为零。日期型数据输入类似“2014.1.6”就无法统计日期相隔天数，在使用数据透视表时无法对日期按月、季、年进行分组。
有些时候，数据的来源不是手工输入，而是从其他系统导入。比如ERP系统、网银，这些系统导出的数据可能并不规范，比如数字是文本格式、数字后有空格、有不可见字符，这时就需要按规范性原则转换成标准的数据，具体方法将在第二章第一节进行介绍。
4.整体性原则
整体性原则要求同一事项数据放在同一工作表，同一类型的工作表放在同一个工作簿，同一类工作簿放置在同一文件夹中。如第三章第三节所举的例子，各公司的报表都放在以月份命名的文件夹下，这样在报表翻新时才能批量修改公式。清单型表格的数据能在同一工作表记录就不要拆分为年或月分表保存，比如本章的“图1-8清单型表格示例”中合同台账如果按年或按月拆分为不同工作表登记，就不方便使用公式或数据透视表进行统计分析。
只有在数据录入时贯彻整体性原则，在统计分析数据、编辑修改公式时才会得心应手，不必为数据的移动、合并、无法批量修改公式而烦恼。
5.可扩展性原则
可扩展性原则主要体现在以下三个方面：
（1）编辑公式要有良好的可扩展性。
我们在编辑公式时应考虑其扩展性，这样才能快速提高数据分析效率。编制公式时，单元格引用时应正确使用相对引用、绝对引用、混合引用，以便使用鼠标拖拉填充柄填充公式。
比如图1-10中的表格，D列、E列的销售收入是根据B列、C列销售数量乘以第二行相应年份的单价，在D6单元格输入公式：=B6*B$2*$E$2。公式中销售数量是相对引用，销售单价是混合引用，折扣率是绝对引用。如果D6单元格公式的引用模式错误，则无法使用拖拉填充柄将公式快速填充复制到D列、E列的其他单元格。
图1-10 使用正确的单元格引用保证公式的扩展性
（2）表格的名称要有良好的可扩展性。
表格名称应规范、有规律，以方便批量修改公式。参见前文“一致性原则”中提到的“同物同名称、同表同格式、同列同格式”，不赘述。
（3）表格的布局要有良好的可扩展性。
需增补数据的表格应使用表格功能或在末行末列预留空白行列。通常在表格的末行末列对前面的行列求和，如果表格可能要在求和行列前添加行列补录数据，那么应该使用表格功能或预留空白行列。
比如：“图1-6各月费用汇总表”需增加“费用11”，就要在合计行前插入一行，此时，B列求和公式不会自动变为=SUM(B2:B12)，还是=SUM(B2:B11)。但是，如果之前就在合计行前预留一空白行第12行，原求和公式就是=SUM(B2:B12)，补录数据时，在原空白行第12行前再插入一空白行，此时公式就会自动变为=SUM(B2:B13)。
Excel2003的列表功能就体现了可扩展性功能，在列表的最末行设置了一个“插入”行，即图1-11中星号③所表示的行。如果直接在该行的单元格中键入数据，列表会自动调整大小以容纳新数据，后面的统计公式也会自动调整所包含的行数。
注意，Excel2010版已将列表更名为表格。与Excel2003中的列表不同，表格不包含用于快速添加新行的特殊行（用*标记）。但是，如果在紧邻列表的空行或空列中键入内容，则列表将自动扩展以将该行或列合并到列表中。如果显示了汇总行，则在列表下面的行中键入时将不会自动扩展列表。
图1-11 Excel2003的列表功能
对清单型表格使用公式或数据透视表进行统计分析时，建议将清单型表格设为列表（表格），这样当添加新记录时公式或透视表可自动将新增的记录包含进去，不必再手动修改公式的引用范围。
6.安全性原则
使用Excel处理数据时一定要注意数据的安全，否则，轻则导致数据丢失，一切都要从头再来；重则造成数据录入错误、分析结果不正确，给公司带来经济损失，也影响自己的职业前途。安全性原则具体要求如下：
（1）数据安全。
①要分发的表格一定要注意保护工作表，仅允许其他用户修改可以修改的单元格。
②如果引用了其他工作簿的数据，在不需要链接时就应断开链接，以免源表格被删除或移动后，造成本表数据丢失。
断开链接的方法：点击【数据】选项卡→编辑链接→在弹出的“编辑链接”对话框点击右侧的“断开链接”按钮。
③养成定期备份数据的习惯。如果你发生过因表格损坏或因误操作而将原表格替换掉，导致辛苦几天做出来的表格眨眼间灰飞烟灭的悲剧，体会过那痛彻心扉的懊悔，你应该知道这习惯是多么的重要和必要。
④临时性操作应在文档备份上进行，以免误操作替换掉原有数据。
（2）信息安全。
除了保证数据安全不丢失之外，还要注意公司和个人信息的安全。不注意保护信息安全，可能会给公司和个人带来无可挽回的损失。
表格在对外发布之前，一定要检查有无敏感数据，有无不能提供的数据未删除。这些数据可能在隐藏的行列中，也可能在隐藏的工作表中。
我们来看一个因未删除隐藏工作表而造成信息泄漏的真实案例：2013年11月，河南某农信社在官方网站发布考生面试成绩公告，考生下载后发现除了公而告之的成绩单，在隐藏的工作表中居然还有一份不一样的成绩单。不管这两份不一样的成绩单是因为“第三方公司的技术性故障”，还是因为某些“你懂的”原因，总之，这是两份成绩单，这是两份不一样的成绩单，这是两份看了会让人“浮想联翩”的成绩单。我们不从法律和道义的角度评判此事，单纯从Excel操作者的角度来讲，将另一份成绩单隐藏了事，然后对外发布，这是对单位数据信息安全极端不负责任的行为，这样的行为是不可饶恕的低级错误。
日常工作中，我们在对外发送表格前应该使用文档检查器进行检查，将隐藏的行、列、表一次删除，避免因疏忽而泄露了公司的重要机密信息。我们以示例文件“表1-7使用文档检查器检查工作簿”为例介绍检查方法（见图1-12）。
图1-12 使用文档检查器检查文档
打开示例文件“表1-7使用文档检查器检查工作簿”→【文件】菜单→信息→检查问题→检查文档，在弹出的“文档检查器”对话框点击“检查”按钮。
检查结果如图1-13所示。
图1-13 文档检查结果
针对检查的结果，可根据情况直接删除或返回修改。
关于信息安全，需要特别提醒的是，透视表是会隐藏明细数据的，即使我们已经将源文件删除。因而，在对外发送带有数据透视表的表格时一定要注意透视表的源文件是否有敏感数据，如果有敏感数据，建议将透视表转换为普通的表格，再对外发送。
比如示例文件“表1-8 透视表也会隐藏数据”是根据示例文件“表1-1 合同登记台账”中的“工作表规范（整理后）”工作表生成的数据透视表（如图1-14），生成后，将源文件“工作表规范（整理后）”工作表删除。
图1-14 数据透视表也会隐藏数据
从表面上看，此表格只是一个反映汇总情况的透视表。但是如果我们双击透视表总计行的E9单元格，工作簿马上像变魔术一样新增了一个表格（如图1-15），表格的内容就是源文件的全部内容。
图1-15 数据透视表所隐藏的数据
另外，在工作中遇到问题，在网上寻求帮助时应该使用模拟数据，至少要删除或修改公司的名字，同时将那些人名、身份证号、账号等敏感信息删除。
三、要熟练掌握三类表格的具体设计要求
1.清单型表格的具体设计要求
（1）以方便后期数据处理为基本原则。
（2）结构合理：主要字段排在前面，以方便阅读和查找引用数据，具体实例还可参见第七章第二节第一点“正确布局让逻辑更合理”。
（3）有列标题，且列标题名不重复，列标题应为非数字。如果列标题重名，在使用数据透视表时会自动将重名的标题更改成另一列标题，给数字使用者带来困扰或误解。
（4）不要使用中国传统的斜线表头。
（5）同一类数据要在同一工作表，不要分表保存。比如图1-2的合同台账，所有的合同应保存在同一工作表，而不是每年或每月分一张工作表保存，这样才有利于统一使用数据透视表或函数公式进行数据分析。
（6）同一列为同一数据类型，且要保证各列数据格式的规范性。
（7）无冗余数据，可通过已有数据计算得出的数据则不必输入到清单型表格。
（8）保证数据的一致性。
（9）清单型表格尽量不要从其他表格引用数据。
（10）各记录间不能有空行空列，不能有小计、合计行，否则无法使用数据透视表或分类汇总功能。
（11）不能有合并单元格、多行标题。在清单型表格中使用合并单元格、多行标题不方便使用数据透视表、筛选等分析工具。在清单型表格中使用合并单元格会带来以下麻烦：
➣不能排序；
➣不能筛选或筛选结果不正确；
➣不方便粘贴数据；
➣使用数据透视表结果不正确；
➣输入公式时，无法正确地选择单元格区域；
➣使用SUMIF、COUNTIF等函数计算时结果不正确。
2.报表型表格的基本设计要求
（1）结构合理、层次清晰、重点突出、排版美观，方便阅读与打印。
（2）用公式统计清单型的数据自动生成或引用其他型表格的数据而生成。
（3）如果是定期提供的报表，则使用的公式要有良好的可扩展性。
（4）注意保护工作表，防止误操作破坏公式与数据。
（5）报表要准确表达信息使用者需要的信息。
（6）表格排版适当考虑使用者的习惯。
3.其他型表格的基本设计要求
（1）数据录入类的表格要方便数据录入或计算。
（2）数据录入类的表格对录入的数据要有校验机制，确保数据正确。
（3）用于分析的其他型表格无论如何设计至少要以不影响源数据为前提。
（4）效用最大化原则：选用最快捷、最高效的方法进行数据加工。
（5）实用性原则：灵活使用辅助单元格，化繁为简。
（6）不要大范围使用数据有效性、条件格式和数组公式。
（7）如果某工作表含有计算量大的公式，可考虑将其移出工作簿单独保存，需要时再打开，以提高整个其他型工作簿的运行速度。
（8）数据加工时注意要勾稽、对比，以检验数据是否完整准确。
（9）数据加工使用的公式要有良好的可扩展性，方便修改。
（10）逻辑清晰、布局合理、适当标注，便于后期阅读、修改。
四、使用Excel表格的其他专业素养
（1）要保证计算过程的自动化。凡是需要通过计算才能得出结果的单元格或链接其他单元格结果的单元格一定要使用公式计算或链接，而不要直接填列计算结果：因为手工填列的数字不会随着源单元格的数字变化而变动，会导致报表的数字或分析结果出现偏差。当表格的数据很重要时，出现这种情况对你职业生涯的影响可能是灾难性的。
（2）某数据已经在本工作表或其他工作表的单元格公式计算出结果，当其他单元格需要此数据时，应该用链接直接引用，而不是另外重新编制公式计算结果：这是因为当公式错误时，需要同时修改这些单元格的公式很可能会出现修改遗漏的情况，用链接直接引用就可保证计算公式的单一性，方便后期维护。
（3）统筹考虑操作对其他单元格、表格的影响。非必要情况下不要删除、移动行列或单元格，以免导致公式引用出错。
（4）同一工作表只放一张表格，至少要避免出现在横向有多张工作表的同时在纵向也有多张工作表：因为表格设计好后，因各种原因可能要删除行或列，如果纵向、横向都有表格，你会发现要删除行或列是非常麻烦的一件事。
（5）做到规范化操作：不要选定整行、整列设置格式；不要通过插入空格来排版；整行整列设置格式既影响表格的美观，也影响表格文件的大小。单元格内插入空格后其内容发生变化，违背了一致性原则，不利于使用工具进行分析。如某列字段为姓名，为了排版美观，部分读者喜欢在类似于“张三”等单字姓名中间插入空格，对于Excel来讲,“张三”不等于“张三”，无法直接使用函数公式进行数据分析。
（6）先录入数据、编辑公式，然后再进行单元格格式设置等排版工作：因为还可能会增补数据，如果一开始就设置格式，就会出现重复劳动，影响工作效率。
（7）数据加工时要多方勾稽对比，看数据是否完整准确。作为财务人员，我们对数据勾稽的重要性应该有充分的认识，就不详述了。
（8）基于整体性原则，如果可能的话尽量将数据整合在同一工作簿；如果不能整合在同一工作簿，宁可使用少量的大型工作簿，也不要使用数量较多的小型工作簿；尽可能地避免工作簿间的链接，对外部工作簿进行链接既影响表格的打开速度，当工作簿移动或删除时还容易出现断链，并且不一定易于查找和修复。
（9）对关闭的工作簿应尽量使用简单的直接单元格引用，这样做可以避免在重新计算任何工作簿时重新计算所有链接的工作簿。
（10）如果某张工作表需要进行大量运算，且其他工作表对它的引用较少，可考虑将其移出本工作簿以免每次重新计算时影响工作簿的整体计算速度。
（11）如果不能避免使用链接的工作簿，最好将它们全部打开而不是关闭，并且表格打开顺序也有讲究：请首先打开要链接到的工作簿，然后再打开包含链接的工作簿。一般来说，从打开的工作簿比从关闭的工作簿中读取链接的速度要快。
（12）尽管使用很多工作表可以简化工作簿的使用，但是通常计算指向其他工作表的引用比计算工作表内的引用速度要慢。
（13）尽管条件格式和数据有效性的功能非常强大，数组公式运算功能也非常强大，但是大量使用它们会明显降低计算速度，除非你愿意忍受蜗牛般的运算速度。否则，不要大范围使用数据有效性、条件格式和数组公式。
（14）数据量大的表格，应将已经计算出结果且不会再更新的单元格的公式计算结果采用选择性粘贴方式转化为数值，以减少计算量。
（15）表格较大或计算量较多时一定要考虑表格的计算效率，尽可能提高表格的计算效率。