清理数据的十大方法

重要:  本文是由机器翻译的,请参阅免责声明。请在 此处 中查找本文的英文版本以便参考。

拼写错误的单词、难以去除的尾随空格、不需要的前缀、不正确的大小写和非打印字符给人一种不好的第一印象。导致数据混乱的因素还不止这些。请准备好。通过 Microsoft Excel 对工作表进行大扫除的时候到了。

你并不一定始终可控制从数据库、文本文件或网页等外部数据源导入的数据格式和类型。通常需要先清理数据,才能分析数据。幸运的是,Excel 提供许多功能,可帮助用户获取所需精确格式的数据。有时任务非常简单,Excel 具有执行此任务的特定功能。例如,可轻松使用拼写检查清理包含批注或说明的列中拼写错误的单词。或者如果想要删除重复行,可使用“删除重复项”对话框快速执行此操作。

在其他情况下,可能需要使用公式将导入的值转换为新值来操作一列或多列。例如,如果想要删除尾随空格,可创建新列来清理数据,方法是:使用公式,向下填充新列,将新列的公式转换为值,然后删除原始列。

清理数据的基本步骤如下所示:

  1. 从外部数据源导入数据。

  2. 在单独的工作簿中创建原始数据的备份副本。

  3. 确保数据采用行和列的表格格式:每一列中的数据类似、所有列和行可见并且该区域中无空白行。为获得最佳效果,请使用 Excel 表格。

  4. 先执行不需要列操作的任务,例如拼写检查或使用“查找和替换”对话框。

  5. 接下来,执行需要列操作的任务。列操作的常规步骤有:

    1. 在需要清理的原始列 (A) 旁插入新列 (B)。

    2. 在新列 (B) 的顶部添加将转换数据的公式。

    3. 在新列 (B) 中向下填充公式。在 Excel 表中,会使用向下填充的值自动创建计算列。

    4. 选择新列 (B),将其复制,然后作为值粘贴到新列 (B) 中。

    5. 删除原始列 (A),这会将新列从 B 转换为 A。

若要定期清理相同的数据源,请考虑录制宏或编写代码,自动执行整个过程。第三方提供商部分中列有许多第三方提供商编写的外部加载项,如果没有时间或资源独立自动执行此过程,可考虑使用这些外部加载项。

详细信息

说明

连接(导入)数据概述

介绍将外部数据导入 Office Excel 的所有方式。

在工作表单元格中自动填充数据

介绍如何使用“填充”命令。

创建或删除 Excel 表格

添加或删除 Excel 表格行和列

在 Excel 表格中使用计算列

介绍如何创建 Excel 表格以及添加或删除列或计算列。

创建宏

介绍使用宏自动执行重复任务的若干方法。

使用拼写检查不仅可查找拼写错误的单词,还可查找使用不一致的值(如产品或公司名称),只需将这些值添加到自定义词典即可。

详细信息

说明

检查拼写和语法

介绍如何更正工作表中拼写错误的单词。

使用自定义词典向拼写检查中添加单词

介绍如何使用自定义词典。

导入数据时,重复行是一个常见问题。最好先筛选唯一值,确认结果是所需结果,然后再删除重复值。

详细信息

说明

筛选唯一值或删除重复值

介绍两个密切相关的过程:如何筛选唯一行以及如何删除重复行。

可能需要删除常见的前导字符串(例如后跟冒号和空格的标签)或后缀(例如已过时或不必要的字符串结尾处的附加说明短语)。若要执行此操作,可查找文本的实例,然后将其替换为无文本或其他文本。

详细信息

说明

检查单元格中是否包含文本 (不区分大小写)

检查如果单元格中包含文本 (区分大小写)

介绍如何使用“查找”命令和几个函数来查找文本。

删除文本中的字符

介绍如何使用“替换”命令和几个函数来删除文本。

查找或替换工作表上的文本和数字

查找和替换

介绍如何使用“查找”和“替换”对话框。

FIND、FINDB

SEARCH、SEARCHB

REPLACE、REPLACEB

SUBSTITUTE

LEFT、LEFTB

RIGHT、RIGHTB

LEN、LENB

MID、MIDB

这些是可用来执行各种字符串操作任务的函数,如查找和替换字符串内的子字符串、提取部分字符串或确定字符串的长度。

有时文本格式混乱,尤其是文本大小写方面。使用三种 Case 函数中的一种或多种,可将文本转换为小写字母(如电子邮件地址)、大写字母(如产品代码)或首字母大写(如姓名或书名)。

详细信息

说明

更改文本大小写

介绍如何使用三种 Case 函数。

LOWER

将文本字符串中的所有大写字母转换为小写字母。

PROPER

将文本字符串的首字母以及文字中任何非字母字符之后的任何其他字母转换成大写。 将其余字母转换为小写。

UPPER

将文本转换为大写字母。

有时文本值包含前导空格、尾随空格或多个嵌入空格字符(Unicode 字符集值 32 和 160),或非打印字符(Unicode 字符集值 0 到 31、127、129、141、143、144 和 157)。执行排序、筛选或搜索操作时,这些字符有时会导致意外结果。例如,在外部数据源中,用户可能会无意添加额外的空格字符,从而导致打字错误,或者从外部源导入的文本数据可能包含嵌入在文本中的非打印字符。由于这些字符不容易引起注意,因此意外结果可能很难理解。若要删除这些不需要的字符,可组合使用 TRIM、CLEAN 和 SUBSTITUTE 函数。

详细信息

说明

介绍如何从 Unicode 字符集中删除所有空格和非打印字符。

CODE

返回文本字符串中第一个字符的数字代码。

CLEAN

从文本中删除 7 位 ASCII 代码中的前 32 个非打印字符(值 0 到 31)。

TRIM

从文本中删除 7 位 ASCII 空格字符(值 32)。

SUBSTITUTE

可使用 SUBSTITUTE 函数,将值较高的 Unicode 字符(值 127、129、141、143、144、157 和 160)替换为 7 位 ASCII 字符(TRIM 和 CLEAN 函数专门用于此类字符)。

主要有两个数字问题可能需要你进行数据清理:无意中将数字导入为文本,以及需要根据你组织的标准更改负号。

详细信息

说明

将存储为文本的数字转换成数字

介绍如何将单元格中设置和存储为文本格式(这会导致计算问题或排序顺序混乱)的数字转换为数字格式。

DOLLAR

将数字转换为文本格式并应用货币符号。

TEXT

将值转换为采用特定数字格式的文本。

FIXED

将数字舍入到指定的小数位数,使用句点和逗号,以十进制数格式对该数进行格式设置,并以文本形式返回结果。

VALUE

将表示数字的文本字符串转换为数字。

由于存在许多不同的日期格式,并且这些格式可能混杂有编号部件代码或其他包含斜杠标记或连字符的字符串,因此日期和时间通常需要进行转换和重新设置格式。

详细信息

说明

更改日期系统、格式或两位数年份表示方式

介绍 Office Excel 中日期系统的工作原理。

转换时间

介绍如何在不同的时间单位之间进行转换。

将以文本格式存储的日期转换为日期

介绍如何将单元格中设置和存储为文本格式(这会导致计算问题或排序顺序混乱)的日期转换为日期格式。

DATE

返回表示特定日期的连续序列号。如果在输入该函数之前单元格格式为“常规”,则结果将使用日期格式。

DATEVALUE

将由文本表示的日期转换为序列号。

TIME

返回特定时间的十进制数字。 如果在输入该函数之前单元格格式为“常规”,则结果将使用日期格式。

TIMEVALUE

返回由文本字符串表示的时间的十进制数字。十进制数字是一个范围在 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 函数、&(与号)运算符和文本分列向导。

合并单元格或拆分合并的单元格

介绍如何使用“合并单元格”、“跨越合并​​”和“合并及居中”命令。

CONCATENATE

将两个或多个文本字符串联接成一个文本字符串。

Office Excel 中的大多数分析和格式设置功能都假设数据存在于单个平面二维表中。有时可能需要将行转换为列、将列转换为行。有时候,数据甚至不是表格格式结构,需要使用一种方法将数据从非表格格式转换为表格格式。

详细信息

说明

TRANSPOSE

将垂直单元格区域作为水平区域返回,反之亦然。

有时,数据库管理员会使用 Office Excel 查找并更正两个或多个表联接时的匹配错误。这可能涉及协调不同工作表中的两个表,例如,查看两个表中的所有记录,或比较两个表并查找不匹配的行。

详细信息

说明

在数据列表中查找值

介绍使用 lookup 函数查找数据的常用方法。

LOOKUP

从单行或单列区域或数组返回值。LOOKUP 函数具有两种语法形式:向量形式和数组形式。

HLOOKUP

在表格的首行或值数组中搜索值,然后返回表格或数组中指定行的所在列中的值。

VLOOKUP

在表格数组的第一列中搜索值,并返回表格数组中另一列所在行中的值。

INDEX

返回表格或区域中的值或值的引用。INDEX 函数有两种形式:数组形式和引用形式。

MATCH

返回符合特定值特定顺序的项在数组中的相对位置。需要项目在区域中的位置而非项目本身时,请使用 MATCH 而非 LOOKUP 函数。

OFFSET

返回对单元格或单元格区域中指定行数和列数的区域的引用。 返回的引用可以是单个单元格或单元格区域。 可以指定要返回的行数和列数。

注意: 机器翻译免责声明:本文是由无人工介入的计算机系统翻译的。Microsoft 提供机器翻译是为了帮助非英语国家/地区用户方便阅读有关 Microsoft 产品、服务和技术的内容。由于机器翻译的原因,本文可能包含词汇、语法或文法方面的错误。

扩展你的技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×