Excel 数据分析 - 使用文本函数清理数据

您从不同来源获得的数据很多都没有准备好进行分析。 在本章中，您将了解如何准备文本形式的数据以供分析。

最初，您需要清理数据。 数据清理包括从文本中删除不需要的字符。 接下来，您需要以进一步分析所需的形式构建数据。 你可以这样做 −

• 使用文本函数查找所需的文本模式。
• 从文本中提取数据值。
• 使用文本函数格式化数据。
• 使用文本函数执行数据操作。

从文本中删除不需要的字符

当您从另一个应用程序导入数据时，它可能包含不可打印的字符和/或多余的空格。 多余的空间可以是 −

• 前导空格，和/或
• 单词之间的额外空格。

如果您对此类数据进行排序或分析，您将得到错误的结果。

考虑以下示例 −

这是您获得的有关产品信息的原始数据，包括产品 ID、产品描述和价格。 字符"|"分隔每行中的字段。

当您将此数据导入 Excel 工作表时，它看起来如下 −

如您所见，整个数据都在一个列中。 您需要构建此数据以执行数据分析。 但是，最初您需要清理数据。

您需要删除数据中可能存在的所有不可打印字符和多余空格。 为此，您可以使用 CLEAN 功能和 TRIM 功能。

• 选择单元格 C3 – C11。
• 键入 =TRIM (CLEAN (B3)) 然后按 CTRL + 输入。

公式填写在单元格 C3 – C11 中。

结果会如下图所示 −

使用文本函数查找所需的文本模式

要构建您的数据，您可能需要进行某些文本模式匹配，您可以根据这些匹配提取数据值。 一些对此有用的文本函数是 −

从文本中提取数据值

您需要从文本中提取所需的数据以构建相同的结构。 例如，在上面的示例中，您需要将数据放在三列中 – ProductID、Product_Description 和 Price。

您可以通过以下方式之一提取数据 −

• 使用将文本转换为列向导提取数据值
• 使用文本函数提取数据值
• 使用快速填充提取数据值

使用将文本转换为列向导提取数据值

如果您的字段是 −

• 由一个字符分隔，或
• 按列对齐，每个字段之间有空格。

在上面的示例中，字段由字符"|"分隔。 因此，您可以使用将文本转换为列 向导。

• 选择数据。

• 将值复制并粘贴到同一位置。 否则，将文本转换为列会将函数而不是数据本身作为输入。

• 选择数据。

• 单击功能区上数据 选项卡下数据工具 组中的文本到列。

步骤 1 − 将文本转换为分栏向导 - 出现第 1 步，共 3 步。

• 选择分隔符。
• 单击"下一步"。

步骤 2 − 将文本转换为分栏向导 - 出现第 2 步，共 3 步。

• 在定界符下，选择其他。

• 在其他旁边的框中，键入字符|

• 点击下一步。

步骤 3 − 将文本转换为分栏向导 - 出现第 3 步，共 3 步。

在此屏幕中，您可以在向导中选择数据的每一列并设置该列的格式。

• 对于 Destination，选择单元格 D3。

• 您可以点击高级，在出现的高级文本导入设置对话框中设置小数点分隔符和千位分隔符。

• 单击完成。

转换为列的数据显示在三个列中 - D、E 和 F。

• 将列标题命名为 ProductID、Product_Description 和 Price。

使用文本函数提取数据值

假设您的数据中的字段既没有用字符分隔，也没有按列对齐，每个字段之间有空格，您可以使用文本函数来提取数据值。 即使在字段被分隔的情况下，您仍然可以使用文本函数来提取数据。

一些对此有用的文本函数是 −

您还可以根据手头的数据组合这些文本函数中的两个或多个，以提取所需的数据值。 例如，使用 LEFT、RIGHT 和 VALUE 函数的组合，或者使用 FIND、LEFT、LEN 和 MID 函数的组合。

在上面的例子中，

• 剩下的所有字符到第一个 | 给名称 ProductID。

• 第二个 | 右边的所有字符都给名称 Price。

• 第一个 | 和第二个 | 之间的所有字符都给出名称 Product_Description。

• 每个 | 前后都有一个空格。

观察这些信息，您可以通过以下步骤提取数据值 −

• 找到第一个 | 的位置 - 第一个 | 位置

• 可以使用FIND函数

• 找到第二个 | 的位置- 第二个 | 位置

• 你可以再次使用 FIND 函数

• 开始到 (First | Position – 2) 个文本字符给 ProductID

• 你可以使用 LEFT 函数

• (第一 | 位置 + 2) 到 (第二 | 位置 - 2) 文本字符给 Product_Description

• 你可以使用 MID 函数

• (第二 | 位置 + 2) 到文本的结束字符给 Price

• 您可以使用 RIGHT 函数

结果会如下图所示 −

您可以观察到价格列中的值是文本值。 要对这些值执行计算，您必须格式化相应的单元格。 您可以查看下面给出的部分以了解格式化文本。

使用快速填充提取数据值

使用 Excel 快速填充 是另一种从文本中提取数据值的方法。 但是，这仅在 Excel 能够在数据中找到模式时才有效。

步骤 1 − 在数据旁边为 ProductID、Product_Description 和 Price 创建三列。

步骤 2 − 从 B3 复制并粘贴 C3、D3 和 E3 的值。

步骤 3 − 选择单元格 C3，然后单击数据 选项卡上数据工具 组中的快速填充。 ProductID 的所有值都已填充。

步骤 4 − 对 Product_Description 和 Price 重复上述步骤。 数据已填充。

用文本函数格式化数据

Excel 有几个内置的文本函数，您可以使用它们来格式化包含文本的数据。 这些包括 −

根据您的需要格式化文本的函数 −

将数字转换和/或格式化为文本的函数 −

将文本转换为数字的函数 −

使用文本函数执行数据操作

您可能必须对数据执行某些文本操作。 例如，如果员工的登录 ID 在组织中更改为新格式，则基于格式更改，可能必须进行文本替换。

以下文本函数可帮助您对包含文本的数据执行文本操作 −