拼写错误的单词、难以去除的尾随空格、不需要的前缀、不正确的大小写和非打印字符给人一种不好的第一印象。 导致数据混乱的因素还不止这些。 请准备好。 通过 Microsoft Excel 对工作表进行大扫除的时候到了。
你并不一定始终可控制从数据库、文本文件或网页等外部数据源导入的数据格式和类型。 通常需要先清理数据，才能分析数据。 幸运的是，Excel 提供许多功能，可帮助用户获取所需精确格式的数据。 有时任务非常简单，Excel 具有执行此任务的特定功能。 例如，可轻松使用拼写检查清理包含批注或说明的列中拼写错误的单词。 或者如果想要删除重复行，可使用“删除重复项”对话框快速执行此操作。
在其他情况下，可能需要使用公式将导入的值转换为新值来操作一列或多列。 例如，如果想要删除尾随空格，可创建新列来清理数据，方法是：使用公式，向下填充新列，将新列的公式转换为值，然后删除原始列。
清理数据的基本步骤如下所示：
-
从外部数据源导入数据。
-
在单独的工作簿中创建原始数据的备份副本。
-
确保数据采用行和列的表格格式：每一列中的数据类似、所有列和行可见并且该区域中无空白行。 为获得最佳效果，请使用 Excel 表格。
-
先执行不需要列操作的任务，例如拼写检查或使用“查找和替换”对话框。
-
接下来，执行需要列操作的任务。 列操作的常规步骤有：
-
在需要清理的原始列 (A) 旁插入新列 (B)。
-
在新列 (B) 的顶部添加将转换数据的公式。
-
在新列 (B) 中向下填充公式。 在 Excel 表中，会使用向下填充的值自动创建计算列。
-
选择新列 (B)，将其复制，然后作为值粘贴到新列 (B) 中。
-
删除原始列 (A)，这会将新列从 B 转换为 A。
-
若要定期清理相同的数据源，请考虑录制宏或编写代码，自动执行整个过程。 第三方提供商部分中列有许多第三方提供商编写的外部加载项，如果没有时间或资源独立自动执行此过程，可考虑使用这些外部加载项。
|
详细信息 |
说明 |
|---|---|
|
介绍如何使用“填充”命令。 |
|
|
介绍如何创建 Excel 表格以及添加或删除列或计算列。 |
|
|
介绍使用宏自动执行重复任务的若干方法。 |
使用拼写检查不仅可查找拼写错误的单词，还可查找使用不一致的值（如产品或公司名称），只需将这些值添加到自定义词典即可。
|
详细信息 |
说明 |
|---|---|
|
介绍如何更正工作表中拼写错误的单词。 |
|
|
介绍如何使用自定义词典。 |
导入数据时，重复行是一个常见问题。 最好先筛选唯一值，确认结果是所需结果，然后再删除重复值。
|
详细信息 |
说明 |
|---|---|
|
介绍两个密切相关的过程：如何筛选唯一行以及如何删除重复行。 |
可能需要删除常见的前导字符串（例如后跟冒号和空格的标签）或后缀（例如已过时或不必要的字符串结尾处的附加说明短语）。 若要执行此操作，可查找文本的实例，然后将其替换为无文本或其他文本。
|
详细信息 |
说明 |
|---|---|
|
介绍如何使用“查找”命令和几个函数来查找文本。 |
|
|
介绍如何使用“替换”命令和几个函数来删除文本。 |
|
|
介绍如何使用“查找”和“替换”对话框。 |
|
|
FIND、FINDB
|
这些是可用来执行各种字符串操作任务的函数，如查找和替换字符串内的子字符串、提取部分字符串或确定字符串的长度。 |
有时文本值包含前导空格、尾随空格或多个嵌入空格字符（Unicode 字符集值 32 和 160），或非打印字符（Unicode 字符集值 0 到 31、127、129、141、143、144 和 157）。 执行排序、筛选或搜索操作时，这些字符有时会导致意外结果。 例如，在外部数据源中，用户可能会无意添加额外的空格字符，从而导致打字错误，或者从外部源导入的文本数据可能包含嵌入在文本中的非打印字符。 由于这些字符不容易引起注意，因此意外结果可能很难理解。 若要删除这些不需要的字符，可组合使用 TRIM、CLEAN 和 SUBSTITUTE 函数。
|
详细信息 |
说明 |
|---|---|
|
返回文本字符串中第一个字符的数字代码。 |
|
|
从文本中删除 7 位 ASCII 代码中的前 32 个非打印字符（值 0 到 31）。 |
|
|
从文本中删除 7 位 ASCII 空格字符（值 32）。 |
|
|
可使用 SUBSTITUTE 函数，将值较高的 Unicode 字符（值 127、129、141、143、144、157 和 160）替换为 7 位 ASCII 字符（TRIM 和 CLEAN 函数专门用于此类字符）。 |
主要有两个数字问题可能需要你进行数据清理：无意中将数字导入为文本，以及需要根据你组织的标准更改负号。
|
详细信息 |
说明 |
|---|---|
|
介绍如何将单元格中设置和存储为文本格式（这会导致计算问题或排序顺序混乱）的数字转换为数字格式。 |
|
|
将数字转换为文本格式并应用货币符号。 |
|
|
将值转换为采用特定数字格式的文本。 |
|
|
将数字舍入到指定的小数位数，使用句点和逗号，以十进制数格式对该数进行格式设置，并以文本形式返回结果。 |
|
|
将表示数字的文本字符串转换为数字。 |
由于存在许多不同的日期格式，并且这些格式可能混杂有编号部件代码或其他包含斜杠标记或连字符的字符串，因此日期和时间通常需要进行转换和重新设置格式。
|
详细信息 |
说明 |
|---|---|
|
介绍 Office Excel 中日期系统的工作原理。 |
|
|
介绍如何在不同的时间单位之间进行转换。 |
|
|
介绍如何将单元格中设置和存储为文本格式（这会导致计算问题或排序顺序混乱）的日期转换为日期格式。 |
|
|
返回表示特定日期的连续序列号。 如果在输入该函数之前单元格格式为“常规”，则结果将使用日期格式。 |
|
|
将由文本表示的日期转换为序列号。 |
|
|
返回特定时间的十进制数字。 如果在输入该函数之前单元格格式为“常规”，则结果将使用日期格式。 |
|
|
返回由文本字符串表示的时间的十进制数字。 十进制数字是一个范围在 0（零）到 0.99999999 之间的值，表示 0:00:00 (12:00:00 AM) 到 23:59:59 (11:59:59 P.M.) 之间的时间。 |
从外部数据源导入数据后的常见任务是将两列或多列合并为一列，或将一列拆分为两列或多列。 例如，可能需要将包含全名的列拆分为名字和姓氏。 或者可能需要将包含地址字段的列拆分为单独的街道、城市、地区和邮政编码列。 反之亦可。 可能需要将名字和姓氏列合并为一个全名列，或者将单独的地址列合并为一列。 其他可能需要合并为一列或拆分为多列的常见值包括产品代码、文件路径和 Internet 协议 (IP) 地址。
|
详细信息 |
说明 |
|---|---|
|
介绍合并两列或多列中的值的典型示例。 |
|
|
介绍如何使用此向导基于各种常用分隔符拆分列。 |
|
|
介绍如何使用 LEFT、MID、RIGHT、SEARCH 和 LEN 函数，将名称列拆分为两列或多列。 |
|
|
介绍如何使用 CONCATENATE 函数、&（与号）运算符和文本分列向导。 |
|
|
介绍如何使用“合并单元格”、“跨越合并”和“合并及居中”命令。 |
|
|
将两个或多个文本字符串联接成一个文本字符串。 |
Office Excel 中的大多数分析和格式设置功能都假设数据存在于单个平面二维表中。 有时可能需要将行转换为列、将列转换为行。 有时候，数据甚至不是表格格式结构，需要使用一种方法将数据从非表格格式转换为表格格式。
|
详细信息 |
说明 |
|---|---|
|
将垂直单元格区域作为水平区域返回，反之亦然。 |
|
有时，数据库管理员会使用 Office Excel 查找并更正两个或多个表联接时的匹配错误。 这可能涉及协调不同工作表中的两个表，例如，查看两个表中的所有记录，或比较两个表并查找不匹配的行。
|
详细信息 |
说明 |
|---|---|
|
介绍使用 lookup 函数查找数据的常用方法。 |
|
|
从单行或单列区域或数组返回值。 LOOKUP 函数具有两种语法形式：向量形式和数组形式。 |
|
|
在表格的首行或值数组中搜索值，然后返回表格或数组中指定行的所在列中的值。 |
|
|
在表格数组的第一列中搜索值，并返回表格数组中另一列所在行中的值。 |
|
|
返回表格或区域中的值或值的引用。 INDEX 函数有两种形式：数组形式和引用形式。 |
|
|
返回符合特定值特定顺序的项在数组中的相对位置。 需要项目在区域中的位置而非项目本身时，请使用 MATCH 而非 LOOKUP 函数。 |
|
|
返回对单元格或单元格区域中指定行数和列数的区域的引用。 返回的引用可以是单个单元格或单元格区域。 可以指定要返回的行数和列数。 |
下面是提供通过各种方式清理数据的产品的第三方提供商的部分列表。
注意: Microsoft 不对第三方产品提供支持。
|
提供商 |
产品 |
|---|---|
|
Add-in Express Ltd. |
|
|
Add-Ins.com |
|
|
AddinTools |
|
|
WinPure |
