1.2　数据标准化——整理Excel数据

在准备计算和分析的数据时应关注真正的数据，不需要进行过多装饰。整理Excel数据的目的包括完成数据格式的最简化和标准化，并保证数据的完整性和正确性。需要注意的事项如下。

● 要使用纯粹的二维表格式，不要将单元格合并，只保留数据和列标题即可。特殊情况下数据表也可以只包含数据，但对每一列的数据应有明确的解释。

● 注意空白单元格。在数据处理过程中，没有数据（空值）和0是两个不同的概念。如果必须有数据，可以使用一个约定的默认值，比如，数值类的数据常使用0作为默认值。

● 不要使用组合数据，如金额则直接使用数据（如“199”），不需要包含单位（如“199元”）。需要明确数据单位时，可以在列标题中标注，如“金额（元）”。

● 数据表的每一列应该使用相同的数据类型，如数值、文本、日期等，并约定固定的格式，如保留多少位小数。

● 一张数据表只能有一个主题，不要将过多的数据组合在一张表中。需要时可以通过适当的数据冗余关联多个表格的数据，比如通过货号、客户代码、销售渠道关联服装信息、客户信息和销售情况等数据。

下面将分别讨论相关主题。

1.2.1　二维表

二维表是最常见的数据统计形式，而Excel表单（Sheet）就是典型的二维表。图1-2显示了Excel表单的数据区域。

图1-2

在Excel表单中，列索引使用字母，行索引使用数字，定位单元格时则使用列索引和行索引的组合，图1-2中选中的单元格是第一列第一行，位置为“A1”。此外，单元格内容可能是数据，也可能是公式，我们可以设置其显示格式，所以，单元格显示的内容和输入的实际内容可能不一致。图中“单元格内容”所指向的“编辑栏”显示的就是单元格的实际内容。

将数据整理为标准的“二维表”格式时，还需要删除标题、取消单元格合并，整理后的表格格式如图1-3所示。

图1-3

取消单元格合并时，我们修改了“库存”的列标题结构，不再将库存分为主标题、子标题，而是将多个尺寸类型分别作为库存数据的列标题。

在实际工作中，我们可能还习惯对分类进行单元格合并，如图1-4所示，这种形式的合并单元格同样需要取消。

图1-4

整理后，表格中所有列的行数量相同，所有行的列数量也相同，这就是标准的二维表数据结构。

一一问答

一一问：这种纯粹的二维表似乎不太美观？

答：从某些角度来看是这样的，但这里需要重申，我们关注的是数据，标准化的数据结构是数据计算和分析的前提条件。在完成数据计算和分析工作后，我们可以通过精心设计的报表和图形展示结果。

一一问：我还收到过如图1-5所示的数据格式，可不可以将行和列交换呢？

图1-5

答：是可以的。在数据处理中，行和列交换称为数据的旋转。在Excel中可以先选中数据并复制，然后在粘贴数据的位置点击鼠标右键，在弹出菜单中选择“选择性粘贴”命令，在“选择性粘贴”对话框中选择“转置”并点击“确定”按钮，如图1-6所示。

图1-6

通过“转置”就可以完成数据的旋转操作，结果如图1-7所示。

图1-7

1.2.2　数据完整性与正确性

获取数据后还需要对数据的完整性和正确性有一个初步的判断。相关的注意事项包括以下几个方面。

● 缺失的数据。对于明显不应该缺少的数据，如服装的库存数据，我们应该核实数据，如果确实没有数据，可以使用约定的默认值，如0。

● 错误的数据。有些数据过大或过小都可能是不合理的，如服装的价格为负数就是不对的。

● 重复的数据。比如，相同货号的服装信息出现多次，要核实是货号错误还是确实需要冗余数据。

想要在Excel中处理缺失的数据，首先可以查找表中的空白单元格。通过Excel菜单栏的“开始”选项卡中的“查找和选择”→“定位条件”打开“定位条件”对话框，然后选择“空值”，如图1-8所示。

图1-8

点击“确定”按钮后，Excel会自动选中数据区域中的所有空白单元格，如图1-9所示。

图1-9

选中空白单元格以后还需要进行观察，如果确认使用默认值0，可以在编辑栏中输入“0”，然后按下Ctrl+Enter组合键进行确认。这样，所有选中的单元格数据都会修改为0，如图1-10所示。

图1-10

如果只需要检查某一列或多列数据中的空白单元格，可以选中这些列，然后打开“定位条件”对话框并选择“空值”，如图1-11所示。

图1-11

在图1-11中，点击“确定”按钮后会选中D列和E列这两列数据中的空白单元格。同样地，在编辑栏中输入“0”并按下Ctrl+Enter组合键进行确认。这样，两列数据中空白单元格的数据会修改为0。

需要删除重复数据时，可以通过Excel菜单栏的“数据”选项卡中的“删除重复值”命令进行操作。对服装来说，货号可以作为唯一标识的数据。如图1-12所示，这里选择“货号”列作为删除重复数据的依据。

图1-12

在示例中，货号为“a22002”的记录有两条，点击“确定”按钮后会删除其中一条。

一一问答

一一问：我还没看见哪些数据重复了，能不能不删除重复数据，只将它们标记出来？

答：当然可以。我们还以“货号”为例，首先选中“货号”列，然后选择Excel的“开始”选项卡中的“条件格式”→“突出显示单元格规则”→“重复值”命令，在“重复值”对话框中选择“重复”值并设置自己喜欢的颜色，如图1-13所示。

图1-13

点击“确定”按钮后，重复的“货号”数据会显示为指定的颜色，如图1-14所示。

图1-14

标记重复的数据后，可以根据实际情况整理。如果货号错误就修改货号数据；如果重复数据则删除多余的数据，只保留一条记录。

一一问：可不可以将相同的数据排列在一起？

答：通过排序就可以完成这项工作，稍后将讨论相关内容。

1.2.3　拆分数据——分列与公式

在Excel表单中，由于包含单位的组合数据无法直接进行计算，因此需要删除定价、库存和销量数据中的单位，只保留数值部分。

我们可以使用不同的方法来提取数据的一部分。如果数据的长度是固定的，如“定价”列的数值部分都是3位数字，单位都是“元”，这样的数据可以通过“分列”功能来操作。

首先选中“库存S”列，通过点击鼠标右键菜单“插入”命令来添加一列，如图1-15所示。

图1-15

接下来选中“定价”列，然后选择Excel菜单栏的“数据”选项卡中的“分列”命令来打开文本分列向导。

第1步，选择“固定宽度”并点击“下一步”按钮，如图1-16所示。

图1-16

第2步，在“数据预览”对话框中通过鼠标拖拽将分割线移动到“元”字前，然后点击“下一步”按钮，如图1-17所示。

图1-17

第3步，可以根据实际情况指定列的数据格式，如图1-18所示。

图1-18

点击“完成”按钮完成分列，操作结果如图1-19所示。可以看到，“定价”列的数据已拆分为数值和单位两列数据。

图1-19

接下来还应该检查“定价”列的数据，没有问题后可删除单位列，这样就完成了“定价”列数据的提取工作，如图1-20所示。

图1-20

一一问答

一一问：我尝试对“库存S”列的数据进行分列操作，结果如图1-21所示，似乎有些数据不能成功拆分？

图1-21

答：的确是这样的。当数据长度不一致时，分列操作的结果可能无法令人满意。这里我们需要取消操作，将数据恢复到图1-20所示的内容。

一一问：有没有更合适的方法来提取数值呢？

答：可以使用公式。针对图1-20中的数据，首先在“库存M”前添加一列，然后在新的E2单元格中输入如下公式并按下回车键。

=IF(RIGHT(D2,1)="件",MID(D2,1,LEN(D2)-1),D2)

接下来选中E2单元格，并将鼠标光标移动到单元格右下角（小方块的位置），当鼠标光标变成“十”字时双击或按住鼠标左键向下拖拽，这样就可以将公式扩展到E列的其他单元格。提取的“库存S”列数据的结果如图1-22所示。

图1-22

一一问：这个公式看起来挺复杂的，可以详细说明一下吗？

答：该公式使用了4个函数，分别是LEN()、MID()、RIGHT()和IF()，下面分别介绍。

● LEN()函数可以返回字符数量，如LEN(D2)就是获取D2单元格内容的字符数量。

● MID(参数一，参数二，参数三)函数用于提取文本的部分内容，其中，参数一指定从哪里提取文本；参数二指定从第几个字符提取；参数三指定提取多少个字符。如MID("abcdefg",3,2)返回cd。

● RIGHT(参数一，参数二)函数会从文本右侧截取内容，其中，参数一指定从哪里截取文本；参数二指定截取多少个字符。如RIGHT("abcdefg",3)返回efg。

● IF(参数一，参数二，参数三)函数会根据条件返回内容，其中，参数一指定判断条件，当条件成立时返回参数二的值，条件不成立时返回参数三的值。在本示例中，IF()函数的3个参数如图1-23所示。

图1-23

一一说：看起来还是挺复杂的，我得再研究一下。

答：不着急，弄明白这个公式再继续学习也不迟。接下来还可以尝试提取“库存M”“库存L”和“销量”列的数据。提取结果如图1-24所示。

图1-24

1.2.4　数据类型和显示格式

Excel单元格的格式非常丰富，为数据处理提供了更多的灵活性，但同时也存在一些问题，比如，我们看到的数据和单元格中的实际内容可能不一致。如果单元格内容是公式，那么通过选择或取消选择Excel菜单栏的“公式”选项卡中的“显示公式”命令，就可以在显示公式或显示计算结果之间切换。

如果只需要保留计算结果，那么可以选中公式所在单元格（列、行、区域），复制后按数值粘贴。在前面示例中，我们通过公式提取了库存和销量数据的数值部分。可以通过复制、按数值粘贴的方式保留数据，操作结果如图1-25所示。

图1-25

一一问答

一一问：有些单元格左上角有个绿色的小三角，这是什么意思？

答：这表示单元格中的内容是文本格式。如果单元格内容是需要计算的数值，就必须改变这些数据的格式。

一一问：有时候设置单元格格式并不能将文本修改为数值格式，有没有其他方法能够改变数据的格式呢？

答：的确有这种情况。有时候，在一些应用系统生成的Excel文件中，数值会被设置为文本格式，并且无法通过设置单元格格式进行修改，此时可以使用Windows操作系统的“记事本”程序来过滤格式。首先在Excel表单中全选数据并复制，然后将数据粘贴到“记事本”程序中，此时粘贴的就是没有格式的数据；最后，在Excel中新建一个数据表，并将“记事本”程序中的数据全选、复制并粘贴到新表中，这样就可以得到“常规”格式的数据，如图1-26所示。

需要注意的是，如果数据中包含很长的数值（如身份证号码），或者有前导为0的内容（如电话区号、国民经济行业分类代码）等特殊格式的数据时，在新建的数据表中首先需要将单元格（列）的格式设置为“文本”，然后再粘贴数据。

图1-26

此外，针对日期和时间数据，单元格显示的内容和实际内容也不同。Excel中的日期和时间数据实际上是浮点数，其中，整数部分是日期，表示从1900年1月1日开始的第几天；浮点数部分是时间，表示当天的时间比例。如10.1就表示1900年1月10日2时24分，其中，2时24分就是144分钟，即一天1440分钟的十分之一。

此外，还需要注意数据的小数部分，在Excel中可以通过单元格格式设置显示的小数位数，但参与计算的是单元格的完整数据，可能包含了不同数量的小数位，此时需要注意计算的精度问题。

一一问答

一一问：如何才能真正保留2位小数，而不是通过设置单元格格式显示2位小数呢？

答：这里介绍两种方法。

第一种方法是使用ROUND()函数截取，如ROUND(11.1269,2)返回11.13。通过ROUND()函数截取小数位后可以通过复制、粘贴数值的方法获取包含两位小数的数据。

第二种方法是，如果在单元格格式中已经设置了显示两位小数，那么可以将数据复制到“记事本”程序中，然后再从“记事本”程序中复制数据并粘贴到Excel数据表中，这样也可以得到包含两位小数的数据。

1.2.5　分而治之，按需组合

在实际工作中，数据可能会有不同的分类和来源，如服装信息、客户信息，以及不同渠道的销售数据等。在对全部数据进行统一处理时，首先需要对数据进行合并。一般来说，常用的数据合并方式有3种，分别是垂直合并、水平合并和交叉合并。

对于“列”定义相同的数据应采用垂直合并。比如，线下、网店和直播间的销售数据就可以进行垂直合并，此时，数据结构中列的数量、数据类型和顺序要保持一致，如图1-27所示。

图1-27

水平合并一般用于对数据的扩展，比如，前面示例中的服装信息没有包含颜色和图案信息，如果另外统计了这些信息，就可以将这些数据与服装基本数据进行水平合并。水平合并时，每行数据应有一个关联数据，比如，服装可以使用“货号”数据进行关联，如图1-28所示。

图1-28

交叉合并一般用于不同类型的数据合并，如服装数据、客户数据和销售数据的合并，图1-29显示了服装和销售数据的合并，其中使用“货号”作为关联数据。

图1-29

随着数据量不断增加，无论使用哪一种方式合并数据，如果完全靠手工操作都非常容易出错。所以，对于大量数据的合并操作，使用编程和数据库技术会更加适合，本书后续会详细讨论相关主题。