1.2　良好的建表格习惯

1.2.1　Excel中的两种表格

根据所处的Excel数据处理的流程环节不同以及表格的性质不同，可以将Excel表格分为数据表与统计报表两大类型。

·第一种表——数据表

数据表是用来录入明细数据的，还是以产品销售情况为例，在销售产品时，我们需要记录产品的销售日期、销售数量、销售员等一系列数据，通过记录的数据则还可以进行销售金额、折扣及交易金额的计算。

如图1-39所示的“销售记录汇总”表格就是一张数据表，表格中按销售日期，详细地记录了各产品的实际销售金额和销售数量。

因此清单型数据表在设计时要保证表格框架设计规范、数据格式规范、数据完整不缺失，因为此表格将直接影响后期的数据统计与汇总，具体要求如下。

（1）结构合理，主要字段排在前面，以方便查找和引用数据；

（2）有列标题，列标题名不重复，列标题为非数字；

（3）不能有合并单元格，多行标题；

（4）同一类数据要在同一工作表中，不要分表保存；

（5）各记录间不能有空行和空列，不能有小计、合计行；

（6）同一列数据为同一数据类型，且要保证各列数据格式的规范性；

（7）无冗余数据，无缺失数据。

·第二种表——汇总表

汇总表是通过对数据表的分析而得到的最终统计表，例如针对如图1-39所示的“销售记录汇总”工作表，可以汇总每日的交易总金额，也可以汇总每个系列、每个产品的总交易金额。

图1-40所示的报表是用来分析汇总各系列产品销售数量的报表，是利用数据表“销售记录汇总”得到的统计报表。而图1-41所示的报表，是按日期统计各系列产品交易金额的报表。

图1-40　各系列产品销量汇总

图1-41　按日期分析交易金额

汇总表是用来体现数据背后的含义和价值的表格，是得出最终分析结果或结论的表格，通常也是BOSS需要查看的对象。汇总表可以利用函数计算得到，也可以通过“数据透视表”功能创建得到的，具体要求如下。

（1）分析目的明确、观点突出；

（2）如果是定期提供的报表，则使用的公式要有良好的可扩展性；

（3）注意保护工作表。

1.2.2　不规范的表单是问题的制造者

需要明白的是，Excel并不只是一个可以用于分享和打印的图表工具，更是一个可以用于记录数据，以及分析计算数据的工具。想要更有效地利用Excel完成数据计算与统计，就需要了解Excel的使用方法，要真正把表格创建正确了。

·不同属性数据一列呈现

如图1-42所示的表格，是工作人员制作的各部门员工培训成绩统计表，从图中不难看到，成绩表做得简洁、整齐，非常便于理解和查看。

但是当我们求平均成绩时，输入了正确的公式后，得到的结果却是错误值（如图1-43所示）。这是因为姓名和成绩被输入在了同一个单元格中，Excel并不能分析和计算这些数据。

就本例中的培训成绩统计表，正确的建法如下。

将“组别”单独作为一列显示，无论是求平均值还是按组别求平均值都能实现，如图1-44所示。或者“组别”顺序统计时混乱，Excel也有办法做出判断，按组别求平均值，如图1-45所示（计算函数要改为AVERAGEIF函数，此函数用于先判断条件，再对满足条件的数据组求平均值，在后面的相关章节中会详细介绍）。

由此可见，设计表格时，要考虑数据的性质和种类，不同属性的数据不应放在同一列中。

·数据类型不规范

接下来再看图1-46所示的表格，同一类型的数据都放在同一列中，符合上面的要求，那么这张表是正确的吗？

现在根据单价和销量，输入公式来计算销售金额，返回的结果是错误值，如图1-47所示。你发现造成错误结果的原因了吗？

那是因为在“销售单价”列中，输入的单价是带有单位的，那么该单元格的值就被识别成文本，只有将单位“元”去掉，公式才能返回正确结果，如图1-48所示。

因为“销售单价”列中使用了统一的“元”单位，要实现一次性删除，不必手动逐一删除，只需要借助数据“分列”这项功能即可实现。在第3章中还会对分列功能进行详细介绍。

1.2.3　数据库使用单层表头

如图1-49所示的表格中，添加了一个醒目的标题，并且列标识区域对分列数据进行了细致化分。

这张表看起来并没有什么问题，而实际上，当要筛选和汇总数据时，就会遇到阻碍。当我们选中任意单元格，在“数据”选项卡的“排序和筛选”组中单击“筛选”按钮时，并不能在列标识上添加自动筛选按钮，而只是在A1单元格上右下角添加了自动筛选按钮（如图1-50所示）。显然要想进行自由筛选查看数据肯定不能实现，出现这种情况是什么原因导致的呢？

Excel在对数据进行识别时，会将一个连续数据区域（包括空白工作表）的首行辨别为标题行，标题行表明了每列数据的属性和类型，是对数据进行筛选、排序等操作的依据（本例中的标题行是第三行，第一行与第二行属于附加说明信息）。

我们建立数据表的最终目的是便于后期的数据分析和统计，像图1-50所示的表格阻碍了数据的分析，那么它就是一个错误的表格。如果要对数据进行排序和筛选，正确的做法是删除多余的表头，如图1-51所示。

但这并不是表明多层表头毫无用处，当这张表格需要打印，或只提供给他人阅读时，添加表格的标题，把表格的明细分类表达清楚也是很必要的。

1.2.4　对合并单元格说NO

如图1-52所示的表格是某店铺一日的销售数据，记录时，按照产品的类别来输入数据。因此合并了B列中分类名称相同的单元格，将表格组合得简洁美观。

合并单元格是美化表格最常见的操作方式之一，当表格中有一对多的数据关系时，就可以合并单元格。但是不知道大家在合并单元格时，有没有思考为什么会弹出如图1-53所示的提示框？这个提示框中的文字表达的是什么意思？

接下来我们通过数据筛选来解释原因。

选中表格中的任意单元格，在“数据”选项卡的“排序和筛选”组中单击“筛选”按钮，然后按照“分类”字段，筛选出“果干/蜜饯”的记录，只能得到如图1-54所示的一条数据记录。

由图1-52所示的表格可以看到第9行至第15行都是“果干/蜜饯”的数据记录，而并非只有一条符合条件的记录，出现这样的筛选结果正是因为合并单元格所导致的。因为在合并单元格中，只有B9单元格中有数据，而B10：B15单元格区域都为空。

此外，当我们要对数据进行排序时，也会因为合并单元格的大小不同而导致数据无法排序，如图1-55所示，也无法进行数据的分类汇总。

由此可见，合并单元格对表格的破坏性极大，应该慎用此项操作。正确的做法是取消单元格合并，将空白单元格全部填满，这项操作可以通过快捷的办法快速实现，操作步骤详见第3章“3.2.7一次性处理合并单元格并一次性填充”小节。

1.2.5　一列数据一种属性

同属性数据可以简单理解为同类的数据，如“月份”字段下可以分为“一月”“二月”“三月”等，它们是同属性；“加班类型”字段下可分为“工作日加班”“双休日加班”和“节假日加班”，它们是同属性。如果将这些数据分列来处理，则不方便对数据进行分类统计。

同一属性不仅仅要求是范畴的统一，也要求是数据定义的统一。比如“日期”列的数据定义为“1月份”，另一个定义为“一月份”，这样显然会导致数据在进行统计分析时无法找到统一的标识。

如图1-56所示的表格，就是将不同属性的数据保存在同一列中，最后导致无法计算各部门总提成金额。因为“姓名”和“提成金额”是不同属性的数据，所以正确的做法是分列保存，如图1-57所示。

再看图1-58所示的表格，工作人员根据加班性质，保存了员工的加班记录，现在要根据不同的加班性质，创建数据透视表来统计员工各自的总加班时长。

创建数据透视表，分别对“平时加班”和“双休日加班”的总时长进行统计，如图1-59所示。

在报表中，虽然统计出了不同加班性质的总时长，但是行标签字段下的名称并不能正常显示，无法用肉眼得知总时长对应的加班性质，因此正确做法如图1-60所示，将“加班性质”作为一列记录即可得到正确的统计结果。

1.2.6　处理好缺失数据

缺失数据是数据表中常出现的问题，它是指数据集合中某个或某些属性的值不完整，或者文件保存失败造成的数据缺失。如图1-61所示的表格中有很多的空白单元格，这就是缺失数据。

造成数据缺失主要有两个原因，一是人为主观失误，二是机器客观故障。然而不管是哪一种，都应当采取正确的措施补救。

在数据表中，数据缺失的常见表现形式是空值或错误标识符。数据表中有缺失值后，可以通过以下4种方法进行处理。

（1）用一个样本统计量的值代替缺失值，最典型的做法就是使用该变量的样本平均值代替缺失值。

（2）用一个统计模型计算出来的值代替缺失值。常用的模型有回归模型、辨别模型等，不过这需要用到专业数据分析软件才行。

（3）将有缺失值的记录删除，这可能会导致样本量的减少。

（4）将有缺失值的个案保留，即在相应的分析中进行必要的排除。当调查的样本量比较大，缺失的数量又不是很多，且变量之间也不存在高度的相关情况下，采用这种方式处理比较可行。

在数据源表格中，数据属性是首要考虑因素，没有数据也不能留白，否则会影响数据分析的结果。这时，我们可以在数值区域的空白单元格里填充“0”值。

对于图1-61所示的表格，我们可以利用定位功能，一次性选中D列中的所有空值单元格，然后批量输入“0”值，效果如图1-62所示。

1.2.7　字符间不要输入空格或其他字符

在输入表格数据时，很多人会为了使表格更加工整对齐，在输入信息时添加空格。例如在输入姓名时，在两个字的姓名中间添加空格，使其长度与三个字的姓名长度相等，如图1-63所示。

可以看到，添加空格后表格美观了许多，但是有没有想过这会给数据分析带来怎样的麻烦。接下来我们用VLOOKUP函数查看任意姓名人员的联系电话，公式却返回了错误值，如图1-64所示。

这时因为在进行数据判断时，“王镁”并不等于“王镁”，VLOOKUP函数查找的“王镁”，在B列中并不存在，因为找不到匹配的值，所以最终返回了错误值。

还有一种字符就是换行符，在输入数据时，可能一不小心，使用了Alt+Enter组合键，就会在单元格中输入换行符。添加了换行符的单元格，会对数据的计算造成阻碍。

例如图1-65所示的表中，B1和B3单元格中存在换行符，所以在进行求和运算时，公式就无法计算。

这些无关的字符看似对表格显示并无影响，却不知会给数据计算带来很大的麻烦，因此在数据输入时要注意这些细节，或者对数据进行整理时要有删除空格或无关字符的意识。

1.2.8　别在数据源表中添加小计

如图1-66所示的数据表，是在一边录入数据，一边对同一分类名称的数据进行汇总。

通过上面的处理，这张表既是销售记录表，也是各个分类的销售金额的统计表。虽然可以看到更多的信息，但是这样的表格违背了数据管理和分析的原则，也混淆了数据表和报表的分类概念。有关数据的分析和统计计算等操作，不应该在数据表中进行，最佳的做法是将汇总结果单独建立在另一张工作表中。因此，这里应将合计行删除，保持数据源表格的完整性。

真正的合计行是使用“分类汇总”功能按钮创建的。创建分类汇总的结果如图1-67所示，与图1-66不同的是这个分类汇总的结果既可以提取、又可以取消，丝毫不会破坏源数据的完整性。原始表格可以接着进行排序、数据透视表的统计等其他任意分析操作。

1.2.9　特殊数据输入时应遵循先设格式后输入的原则

对于特殊格式的数据，可以采用先设格式后输入的原则来达到提升输入效率的目的。例如输入百分比数据、日期数据、小数等，可以先设置单元格的格式，然后以最简易的方式输入，数据可以自动转换为需要的显示格式。

·设置数据自动包含两位小数的格式

在工作表中可以先选中目标单元格区域，进入“设置单元格格式”对话框，设置“数值”显示为包含两位小数，如图1-68所示。

设置后当输入的数据无论是否有小数位或是有几位小数位，都将只显示两位小数，如图1-69所示。

·设置日期自动显示为需要的格式

在工作表中选中目标单元格区域，打开“设置单元格格式”对话框，设置所需要的日期格式，如图1-70所示。

设置完成后以最简易的方式输入日期，按Enter键确认输入时则能显示为需要的格式，如图1-71所示。

1.2.10　利用数据验证控制数据的输入

利用数据验证控制数据的输入，即是在输入数据之前，通过给单元格设置限制条件，限制单元格只能输入什么类型的值、输入什么范围的值。一旦不满足其设置范围，输入的数据就会被阻止输入，还可以提前设置数据出错警告提醒。这种数据验证设置可以有效避免数据的错误输入，提升数据的输入效率。

如图1-72所示的员工加班记录表，即为单元格设置了数据验证，这样就避免了输入错误日期。

如图1-73所示的试用期到期提醒，根据工号的设置特点，在该列设置了数据验证，一旦光标定位到该列任意单元格中，就会弹出输入提示。

有些数据验证可能涉及公式，比如限制输入重复值、空格等，就必须通过公式识别判断输入值的特性，进而返回结果。如图1-74所示，为该列设置数据验证时就使用了判断函数，即判断这个输入中是否包含有空格，如果有就禁止输入。

1.2.11　表格设计的统一意识

整个公司要有一个统一的用表理念，就是在制定表格框架时，要考虑到工作簿的关联性、工作表统计目标的明确性及字段的完整性。公司业务流程越长，每个环节的经手人越多，你遵守表格设计别人不遵守，数据需要共享交流时，就是问题集中爆发的时候。因此，进行Excel表格设计时就要以数据处理为中心，在数据处理时要具备一定的统一意识，保证表格设计的规整性。

一致性原则要求表格内、表格之间的字段名称、数据类型、表格结构格式要保持一致，具体来讲就是有两个基本要求：同物同名称、同表同格式。

同物同名称：也就是说对象只能有一个名称，同一对象的名称在任何表格、任何人员、任何部门里都要保持一致，以便数据引用。如图1-75所示与图1-76所示的表格中，数据保持一致。

同表同格式：相同的表格其格式必须保持相同，以便统计汇总数据。比如想汇总年工资额，所有月份工资表中数据都应保持同样的格式（如图1-77所示），这样才能方便使用函数（如图1-78所示），否则计算公式就需要编辑为“='1月'！T3+'2月'！T3+…+'12月'！T2（比如12月工作表中第一位员工的实发工资在T2单元格中）。