1.2　用Excel完成常见数据预处理

前面讲了数据预处理的内容，本节开始就不能纸上谈兵了，我们用Excel来实现数据预处理中常见的操作。

1.2.1　文本数值化：文本数字转为数值型数字

在Excel中，数字通常有两种存储形式——数值型和文本型。

■　数值型

这是数字在Excel中规范和正确的存储形式，其表现形式为默认居右对齐。以数值型存储的数字，可以完成各种数学运算，包括但不限于计数、求和、平均值、方差、标准差、最大值、最小值等。

■　文本型

这是数字在Excel中的另一种存储形式，其表现形式为默认居左对齐，因为其本质依然是文本。以文本型存储的数字，只能完成计数，其他数学运算均不能完成，故而存在极大的限制。为了能够进行后续数据分析，必须将其转换为数值型数字。

提示：如何快速判断单元格中的数字是数值型还是文本型呢？一般情况下看它们默认的对齐方式，或观察单元格左上角有无绿色小三角。如有绿色小三角，则此单元格为文本型数值。

1．用分列将文本型数字转换为数值型数字

操作步骤

第一步：选择需要转换的列，如图1-7所示。

第二步：单击“数据”选项卡，找到“分列”功能，如图1-8所示。

第三步：单击“分列”按钮，显示分列对话框，如图1-9所示。

第四步：单击“完成”。

注意：不需要执行任何分列操作，只需要点击“完成”按钮即可，此时可以发现文本型数字已转换为数值型数字；亦可留意右下角，已显示平均值、计数和求和，证明此时已成为数值型数字。

2．用公式将文本型数字转换为数值型数字

操作步骤

第一步：插入新列，作为转换辅助列，如图1-10所示。

第二步：在新列中使用VALUE( )函数，如图1-11所示。

第三步：重复此操作，或双击单元格右下角的控制柄，如图1-12所示。

汇总对比一下两种文本转为数字的方法，如表1-12所示。

1.2.2　日期数值化：文本型日期转为日期型格式

在Excel中，日期通常有两种存储形式——日期型和文本型。

■　日期型

这是日期在Excel中规范和正确的存储形式，其表现形式为默认居右对齐，并以特定的日期格式显示。以日期型存储的日期，可以完成各种操作，包括但不限于两个日期的差、提取星期、按日期的不同维度分组汇总和筛选等。

■　文本型

这是日期在Excel中的另一种存储形式，其表现形式为默认居左对齐，因为其本质依然是文本。以文本型存储的日期，只能完成计数，其他均不能完成，故而存在极大的限制。为了能够进行后续数据分析，必须将其转换为日期型日期。

1．用分列将文本型日期转换为日期型日期

操作步骤

第一步：选择需要转换的列，如图1-13所示。

第二步：单击“数据”选项卡，找到“分列”功能，如图1-14所示。

第三步：单击“分列”按钮，显示分列对话框，如图1-15所示。

第四步：单击“完成”。

注意：不需要执行任何分列操作，只需要点击“完成”按钮即可，此时可以发现文本型日期已转换为日期型日期；亦可留意右下角，已显示平均值、计数和求和，证明此时已成为数值型数字。

特别提醒

此方法的前提是文本型日期依然遵循日期格式的样式来存储，目前支持如下格式的文本型日期通过分列操作转换为日期型：

■　YYYY/MM/DD

■　YYYY-MM-DD

2．用公式将文本型日期转换为日期型日期

Excel同样提供了将文本型日期转换为日期型日期的函数，即DATEVALUE( )函数，使用方式为：DATEVALUE(“2009/01/01”)。两种转换方式的对比如表1-13所示。

1.2.3　用分列实现维度拆分

在Excel中进行维度拆分是一件非常容易和轻松的工作，因为Excel为我们提供了“分列”功能。在很多源系统中地址类信息都是重要信息，包括用户联系地址、商品收货地址等，多数情况下是用单一字段进行存储，如表1-14所示。

此类型存储的地址信息对于数据分析是毫无意义的，因为它将大量有用信息融合进了同一字段，或者叫数据列，而所有分析工具的最小分析维度就是一个数据列。如果用这样的地址信息来分析可乐在不同城市的销量，你会发现无法下手，城市这个重要的分析目标恰恰被融合在一个夹杂很多无用数据的数据列中，数据分析工具无法处理数据列内的信息，就好像装着很多糖果的透明玻璃盒，你能看到却无法从中提炼出价值。

Excel中分列的方式有两种：基于固定位置，基于分隔符。

1．基于固定位置分列

基于固定位置分列，只需指定数据中需要分列的位置即可，支持同时指定多个分列位置，适合格式统一和固定的数据。

应用：提取身份证中的出生年月

第一步：单击“数据”选项卡，找到“分列”功能，如图1-16所示。

第二步：单击“分列”按钮，显示分列对话框，如图1-17所示。

第三步：点击“固定宽度”并单击“下一步”按钮，结果如图1-18所示。

第四步：在标尺上单击创建分列线，拖动分列线到合适的位置并进入“下一步”，如图1-19所示。

第五步：检查分列数据是否正确，无误后单击“完成”按钮，如图1-20所示。

2．基于分隔符分列

基于分隔符分列，需要指定数据中按照哪些字符进行拆分即可，每次分列仅支持一个分隔符，适合格式复杂的数据。

应用：提取地址信息中的省份

第一步：选择待拆分的列，如图1-21所示。

第二步：单击“数据”选项卡，找到“分列”功能，如图1-22所示。

第三步：单击“分列”按钮，显示分列对话框，如图1-23所示。

第四步：点击“分隔符号”并进入“下一步”，如图1-24所示。

第五步：在分隔符中点击“其他”输入“省”并进入“下一步”，如图1-25所示。

第六步：检查分列数据是否正确，无误后单击“完成”按钮。

提取地址中的信息一般只能用分隔符分列，因为省份名称长短不一，无法用固定宽度进行分列。

注意：分列后的数据会覆盖相邻列，请提前插入空白列，否则相邻列的数据会被覆盖；分列后用于分列的字符会被删除，需检查分列后数据是否符合要求。再次强调，是否要做维度拆解，决定于拆解后的多个子维度是否为业务分析所需。

1.2.4　用“查找并删除重复行”处理重复值

在数据分析前必须仔细处理重复值。若源系统数据质量管控不严，极易产生大量重复数据。重复数据不仅占用大量存储空间，拖慢分析速度，甚至会直接导致分析结果出现错误或偏差。假如有10行性别数据，其男女比例是6∶4，但其中有4个男性数据是重复的，故而真实的男女比例应为3∶4。

Excel为重复值处理提供了便捷的工具，即删除重复项。

应用：删除重复数据

第一步：选择要处理的数据区域，如图1-26所示。

第二步：单击“数据”选项卡，找到“删除重复项”功能，如图1-27所示。

第三步：单击“删除重复项”按钮，显示对话框，如图1-28所示。

第四步：勾选用以判断重复的列，如图1-29所示。

第五步：单击“确定”按钮，如图1-30所示。

第六步：Excel提示重复值数量，并自动保留唯一行，无误后单击“确定”按钮，如图1-31所示。

注意：勾选判断重复项的列时，建议尽可能选择完备。一般来说，重复列要求所有列的数据都一样，否则不能作为重复列进行删除处理。