使用文本函数清理数据

---

---

从不同来源获得的数据可能并非以适合分析的形式存在。在本节中，您将了解如何准备以文本形式存在的数据以进行分析。

首先，您需要清理数据。数据清理包括从文本中删除不需要的字符。接下来，您需要将数据结构化为进一步分析所需的格式。您可以通过以下方式做到这一点：

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

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

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

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

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

请考虑以下示例：

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

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

如您所见，所有数据都在一列中。您需要构造此数据以执行数据分析。但是，首先您需要清理数据。

您需要删除数据中可能存在的任何不可打印字符和多余空格。您可以为此目的使用 CLEAN 函数和 TRIM 函数。

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

公式填充到单元格 C3 – C11 中。

结果将如下所示：

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

要构造您的数据，您可能需要进行某些基于文本模式匹配的操作，您可以根据这些模式提取数据值。一些为此目的有用的文本函数是：

从文本中提取数据值

您需要从文本中提取所需的数据才能构造相同的数据。在上面的示例中，假设您需要将数据放置在三列中 - ProductID、Product_Description 和 Price。

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

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

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

如果您要提取的数据字段：

• 由某个字符分隔，或
• 在列中对齐，字段之间有空格。

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

• 选择数据。

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

• 选择数据。

• 在功能区的“数据”选项卡上的“数据工具”组中，单击“分列”。

**步骤 1** - “将文本转换为列”向导 - 步骤 1/3 出现。

• 选择“分隔符号”。
• 单击“下一步”。

**步骤 2** - “将文本转换为列”向导 - 步骤 2/3 出现。

• 在“分隔符”下，选择“其他”。

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

• 单击“下一步”。

**步骤 3** - “将文本转换为列”向导 - 步骤 3/3 出现。

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

• 对于“目标”，选择单元格 D3。

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

• 单击“完成”。

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

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

使用文本函数提取数据值

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

一些为此目的有用的文本函数是：

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

在上面的示例中，

• 第一个 | 左侧的所有字符都构成名称 ProductID。

• 第二个 | 右侧的所有字符都构成名称 Price。

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

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

观察此信息，您可以按照以下步骤提取数据值：

• 查找第一个 | 的位置 - **第一个 | 位置**

  • 您可以使用 FIND 函数

• 查找第二个 | 的位置 - **第二个 | 位置**

  • 您可以再次使用 FIND 函数

• 文本的开始到（**第一个 | 位置** – 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 更改为新格式，则根据格式更改，可能需要进行文本替换。

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