1.2 走出数据源表制作误区
在本书中，将数据分析中合适的基础数据表称为“数据源表”，而对于什么是“数据源表”?“数据源表”又来自何处？由于每个用户所处的情况各不相同，“数据源表”可能出自于ERP系统，可能出自财务软件，也可能完全是手工输入的数据。
因为很少有人讨论“数据源表”的管理，所以很多用户并不明白“数据源表”的重要性。这一认知很容易导致数据资料的混乱，比如对应一项数据指标往往有若干个表格，真正做分析时，又没有一个能用。所以，就会不停地做重复的工作，使加班成为常态。
本节将介绍“数据源表”的制作及管理的一些基本理念，介绍一些用户常犯的错误，带领大家走出“数据源表”的制作误区。
1.2.1 清理电脑中的4大类表格
每个用户的电脑中都有大量的 Excel 文件，这些文件的来源不同，用户也不同，而管理这些 Excel 文件，是一件令人头疼的事情。其实，电脑中那么多的表格，我们大致可以分为4大类，即原始数据表、数据源表、计算分析表和结果报告表。这4大类表格及其逻辑使用过程，也可以用下图来表示。
原始数据表：他人提供，或自行编制的原始数据，表格格式应尽量接近于分析所需数据源表的要求，一般需加工后才能用于数据分析。
数据源表：他人提供，或自行编制的用于数据分析的基础数据表，对于表格的格式有一定的要求，但这类表格一般不对外报送。
计算分析表：自行编制的分析用工作底稿，一般不对外报送。
结果报告表：对外提供的分析结果报表，格式无要求。
这四大类的表格，地位会根据其所处的位置而变化。例如他人提供的表格，对他人而言就是结果报告表，而对于报告使用者来说，可能是数据源表，也可能仅仅是原始数据表。
即使对于同一个用户的同一份表格，也可能因为不同的工作内容而处在不同的位置，比如A工作中的数据源表，在B工作中就有可能仅仅是原始数据表。
要制作出一份设计合理的表格，首先需要界定这份表格在他人电脑中的身份地位，因为对于原始数据表、数据源表、结果报告表的格式要求是不一样的。
下面，通过一个案例来理解一下表格的分类及编制要求。
1.忙碌的销售助理
某企业从事化妆品的生产与销售，在全国各地有数十家直营专柜，分别由6位业务员负责。业务员每天上午10点之前都会提供前一天各人分管专柜的销售日报表给公司的专柜销售助理，日报表的统一格式如下图所示。
当专柜销售助理收到日报表后，将文件名统一为“业务员的名字-日期”的格式，例如2016年7月1日收到业务员A上报的日报表，就将文件名改为业务员A-2016.7.1，然后存放在硬盘中，按业务员的姓名、年、月设置的文件夹下，例如业务员A在2016年7月1日～7月5日上报的文件全部在“商场销售日报表/业务员A/2016年/7月”这个文件夹下。
销售助理如此分类，觉得表格格式统一，文件整理得井井有条。可是，当真正要用时，却会发现如此分类会大大增加工作量。
这位销售助理的真实情况是，经常遇到加班作表，分析专柜数据的情况。而加班的原因，总是抱怨老板的要求多变，分析的要求不一致。例如，这个月要看区域的销售情况，下个月要根据业务员分析，有时候要看总的销售额变动，有时候又要分析促销的情况，当遇到这些要求时，销售助理每次都要重新整理表格，导致加班。
2.理解4大类表格
为了找出发生这种问题的关键，首先要理解4大类的表格。
如果读者以前并没有接触过数据分析工作，仅通过前面的例子，也很难找出表格体系存在的问题。其实，这个例子是一个很典型的“数据源表”缺失的例子。
我们首先看一下业务员提供的销售日报表，因为格式是统一规定的，所以业务员每天上报这样的表格并没有问题。
然后再看销售助理的文件管理方式，文件统一格式命令，按业务员的姓名、年、月设置文件夹来存放，虽然不是最佳方案，也不能说有大错。
关键的问题是，管理阶层要看的资料，并不是每个业务员提供的单个销售日报表，而是要看汇总的分析资料，如果只是根据已有的资料简单汇总，并不能满足管理层的需要。
在上文中，我们看到在销售助理电脑中的一份份销售日报表，对于提供者业务员来说是“结果报告表”，但是对于销售助理来说，仅仅是其从事专柜销售分析工作的“原始数据表”，并没有可以直接用于分析的“数据源表”。她工作的实质是每次接到新的分析任务后，根据“原始数据表”临时组织数据表用于当次的分析，再据以编制“结果报告表”，其工作流程图如下。
例如，要分析重百专柜2014年和2015年按产品大类的销售分析，助理就会做出如下的表格。
为了做上图中的表格，需要整理如下图所示的销售额数据表，如果数据过多，甚至需要用计算器及手工计算填列数据。
如果下次的分析要求变了，改为分析新世纪专柜2014年与2015年参加促销的产品销售额比较情况，就会发现上次整理的销售额数据已经完全无用，需要再重新组织含促销金额的数据表，如下图所示。
根据以上的分析，我们可以得知，这位销售助理的工作效率比较低，经常加班的原因是没有一个可通用于管理层对于专柜各项数据分析要求的“数据源表”。
知道了问题的源头，接下来就看看应该怎么解决这一类问题。
一个理想的数据源表应该只有一张工作表，而不是本例中的多个文件夹下的多个工作簿。当然，这只是一种理想的状态，在实际工作中，我们可以用两种方法来达到这种理想的状态：
通过合理地组织原始数据表，使其达到或接近“数据源表”的要求。
对“原始数据表”及时汇总加工，自行打造“数据源表”。
以上两种解决方法，一般需要结合使用，而本例中则需要先改变原始数据表的格式，使其达到数据源表的要求，然后再对原始数据表加以汇总。
1.2.2 汇总数据到一个工作表
假设每位业务专员提供的表格都符合销售助理分析所需的数据源表的要求，但这些表格分散在多层文件夹下的多个工作簿中，而每个工作簿中又有多个工作表，如下图所示。此时需要汇总这么多工作簿中的数据不是一件容易的事情，无论是使用复制粘贴的方法或者是函数、透视表、VBA都不行。
对于普通的Excel用户而言，不管用什么方法都不容易把数据汇总到一个工作表，这并不是 Excel 的功能缺失，而是因为问题出在原始数据的组织上，原始数据人为地放在多个工作簿中，将简单的问题复杂化了。下面，我们就来解决这个问题，把数据汇总。
1.从多个工作表到一个工作表
在汇总数据时，如果销售助理在表格中增加一个字段“商场”，那么，每个业务员每天只需要报送一张工作表，而非之前在一个工作簿中存放若干个工作表。增加“商场”字段后的“商场销售日报表”如下图所示。
2.从多个工作簿到一个工作表
如果再增加一个“日期”字段，业务员报送的资料将从“商场销售日报表”变成“商场销售日报表汇总”，规定将2016年7月的这一个月的数据放在同一个工作表中，那么“商场销售日报表/业务员 A/2016年/7月”这个文件夹中，将只剩下一个文件，文件中只有一个工作表。增加“日期”字段后的“商场销售日报表汇总”如下图所示。
3.从多个文件夹到一个工作表
现在，我们还可以继续整合工作表，比如将每个业务员一年的数据放在一个工作表中，再增加一个“业务员”字段列表，然后整合需要分析的年、月数据，这样可以将原来多层文件夹下多个工作簿中的数据存放在一张工作表中。增加“业务员”字段后的“商场销售日报表汇总”如下图所示。
1.2.3 补齐分析必需字段
经过以上的调整，每个业务员每年只需要提供一张表格就可以了，但是，这张表格离数据源表的要求还有距离。回顾一下销售助理的困惑，是因为老板的要求多变，每一次分析的要求都可能不一样。例如，这个月要看区域的销售情况，下个月要根据业务员分析，有时候要看总的销售额变动，有时候又要分析促销的情况。
此时，对应管理层的分析要求，已经制作完成的表格中，有了“业务员”“销售额”“促销”等资料。但是“销售区域”的资料，如南区、北区等并没有在表格中有所体现。所以，解决方法是可以在表格中增加“区域”字段，完成后如下图所示。
在增加字段时，除了老板的要求之外，还需要有一定的前瞻性。例如，现在并没有提出按“省份”分析的要求，但是考虑到组织架构，今后完全可能会提出，所以在准备数据源表时，可以把“省份”字段加上去。增加省份字段后的效果如下图所示，而用户也可以根据自己公司的情况，设置其他字段。
1.2.4 规范字段设置
对于下面的这张表格，也许你认为已经具备了管理层分析所要求的所有字段元素，但即使如此表达，还存在很多问题，例如“销售方式”栏。
分析一张表格的设置是否合理，首先要看这张表格的身份地位，看其属于4大类表格中的哪一种。而上表中“销售方式”部分如果作为“结果报告表”，则不能简单地评论其是否规范，也许报表的使用者就喜欢用这种格式，但如果是将上表作为数据源表的一部分，就会导致如下诸多缺陷：
字段记录缺失，在销售方式栏下有很多空格；
有两层字段名称，而第2层字段名称（正常销售、公司促销、商场促销）本身就是第1层字段（销售方式）下的属性。
如果没有第2个问题的存在，而只需要处理第1个字段记录缺失的问题，最常用的方法是将窗格部分全部用其他符号填充，如“○”，操作方法如下。
步骤1 选中L5:N14单元格区域。
步骤2 按下“F5”键调出“定位”对话框，单击“定位条件”按钮，在弹出的“定位条件”对话框中选中“空值”选项，然后单击“确定”按钮。
步骤3 输入符号“○”，然后按下“Ctrl+Enter”组合键，即可快速填充所有空值。
但是，这个例子并不只是字段记录缺失的问题，而是把字段的属性值作为字段名，最佳的解决方案应该是将属性值放到该放的位置上。例如，将“销售方式”作为字段，原2层字段名称则作为“销售方式”字段的属性，修改后的商场销售日报表汇总如下图所示。
1.2.5 去除多余的表格名称
对于要向外报送的表格，很多用户习惯为表格设置一个表格名称和制表日期，以便于表格使用者了解其阅读表格的主题，如下图所示。
但是，如果表格的用途是“数据源表”，那么表格的名称不仅没有必要，还会给数据分析带来麻烦。例如，我们使用 SQL 语句查询其他工作表的数据时，Excel 会默认工作表中的第1行为表头，但是在本例中，表头则是在第3行。处理这一类问题的方法很简单，直接把表格名称和日期行删除即可，让工作表的第1行为表头，效果如下图所示。
1.2.6 使用单层表头
作为向外报送的“结果报告表”，主要的特点是把问题表述清楚，使报表使用者能够接受，而表格的形式并不是关键。但是作为“数据源表”，因为Excel默认工作表中的第1行为表头，那么如果是制作“数据源表”，则应该使用单层表头。
在本例中，不仅存在双层表格，还用到了合并单元格，如果取消合并单元格设置，效果如下图所示。
当取消合并单元格后，第二排的若干个单元格的内容为空白。这是因为合并单元格的值仅存在于合并区域中的某一个单元格，而系统默认为左上角的单元格。在这个例子中，I1和 J1单元格之前是合并在一起的，但取消合并之后，只有 I1单元格有内容。
如果不对多层表格加以处理，Excel默认使用第1行为表头，会造成在J列的字段名称缺失，且使I列使用“单价”作为字段名，这并不符合这个数据源表的实际情况，因为I列实际需要的字段名应该为“零售价”。
处理的方法是，使用单层表头，无论是多层表头或是斜线表头，全部取消，修改后的“数据源表”如下图所示。
1.2.7 禁用合并单元格
为什么要在“数据源表”中禁用合并单元格？原因有很多，但是最重要的一点在于，合并区域中部分单元格取值为空，例如下图中，合并后仅保留区域中左上角单元格中的数据。
在本例中，G 列存在合并单元格，我们需要取消合并单元格，并在空格中填充数据，操作步骤如下。
步骤1 选取G2:G13单元格区域。
步骤2 单击“开始”选项卡“对齐方式”组中的“合并后居中”按钮，取消选择区域的合并单元格设置。
步骤3 按下“F5”键打开“定位”对话框，单击“定位条件”按钮，打开“定位条件”对话框，选中“空值”选项，然后单击“确定”按钮。此时当前单元格为G3单元格。
步骤4 输入“=G2”，代表当前单元格取其上一格的值。
步骤5 按下“Ctrl+Enter”组合键即可。
进行以上操作后，G列原有合并单元格的地方会变成公式填充，如下图M列所示。而作为数据源表，应该将有公式的单元格全部数值化，否则如果进行排序之类的操作，数据将会出错。
1.2.8 删除多余的合计行
有的用户在制作表格时，习惯添加“合计行”，认为可以清楚地展现总计数据。可是，在数据源表中，并没有必要保留“合计行”。因为如果有合计行，在数据更新，或者插入行后，可能需要更新合计行的数据，在使用数据透视表等分析工具时，还会因“合计行”的存在造成分析结果错误。
既然了解了多余“合计行”的弊端，就需要删除“合计行”来消除弊端，删除合计行之后的数据源表如下图所示。
1.2.9 数据源表编制中的其他常见问题
在编制数据源表时，还有一些常见的其他问题，我们接下来一一为你分析解答。1.取消空行空列
在表格设计时，很多人都喜欢使用空行和空列，以达到划分区域的目的，这是“数据源表”编制的大忌。如下图所示，如果表格中间没有空行隔断，将鼠标放在任意单元格中，然后按下“Ctrl+A”组合键就可以选定全部数据区域，但是有了空行隔断之后，就只能选择部分数据区域。
如果数据源表中已经存在整行的空白，可以使用下面的方法快速删除，操作步骤如下。
步骤1 选择数据源表数据区域的第一列。
步骤2 按下“F5”键打开“定位”对话框，单击“定位条件”按钮，打开“定位条件”对话框，然后选中“空值”选项，单击“确定”按钮退出。
步骤3 使用鼠标右键单击已经选中的空行，在弹出的快捷菜单中选择“删除”命令，在弹出的“删除”对话框中选择“整行”选项，然后单击“确定”按钮即可删除所选空行。
提示：删除空列的方法与删除空行的方法相似，所不同的是第1步选中的是数据区域表头所在的行，而第3步在“删除”对话框中应该选择“整列”选项。
2.一格一属性
Excel在处理表格数据时，有其自身的规则，而标准规范是一个单元格记录一个属性。下图中左侧的数据单元格中既有数量，又有不同的单位，这显然不符合 Excel 的标准。作为数据源表，我们需要将不符合标准的数据进行处理，如下图所示为处理后规范的表格样式。
3.使用正确的数值格式
在数据源表中，如果涉及日期的输入，一定要选择 Excel 认可的日期格式，下图中列出了几种常见的错误日期格式。
日期是一种特殊的数值，使用了正确的日期格式，Excel就可以直接对日期值进行计算，如果日期格式错误，则不能进行计算。
提示：因为每个人电脑中的默认日期格式可能有所不同，这与操作系统的版本和语言有关，如果需要更改默认设置，可以在“控制面板”的“区域和语言”中修改默认格式。