Excel 数据分析 - 使用文本函数清理数据
您从不同来源获得的数据很多都没有准备好进行分析。 在本章中，您将了解如何准备文本形式的数据以供分析。
最初，您需要清理数据。 数据清理包括从文本中删除不需要的字符。 接下来，您需要以进一步分析所需的形式构建数据。 你可以这样做 −
- 使用文本函数查找所需的文本模式。
- 从文本中提取数据值。
- 使用文本函数格式化数据。
- 使用文本函数执行数据操作。
从文本中删除不需要的字符
当您从另一个应用程序导入数据时，它可能包含不可打印的字符和/或多余的空格。 多余的空间可以是 −
- 前导空格，和/或
- 单词之间的额外空格。
如果您对此类数据进行排序或分析，您将得到错误的结果。
考虑以下示例 −
这是您获得的有关产品信息的原始数据，包括产品 ID、产品描述和价格。 字符"|"分隔每行中的字段。
当您将此数据导入 Excel 工作表时，它看起来如下 −
如您所见，整个数据都在一个列中。 您需要构建此数据以执行数据分析。 但是，最初您需要清理数据。
您需要删除数据中可能存在的所有不可打印字符和多余空格。 为此，您可以使用 CLEAN 功能和 TRIM 功能。
| S.No. | 函数 & 说明 |
|---|---|
| 1. | CLEAN 从文本中删除所有不可打印的字符 |
| 2. | TRIM 从文本中删除空格 |
- 选择单元格 C3 – C11。
- 键入 =TRIM (CLEAN (B3)) 然后按 CTRL + 输入。
公式填写在单元格 C3 – C11 中。
结果会如下图所示 −
使用文本函数查找所需的文本模式
要构建您的数据，您可能需要进行某些文本模式匹配，您可以根据这些匹配提取数据值。 一些对此有用的文本函数是 −
| S.No. | 函数 & 说明 |
|---|---|
| 1. | EXACT 检查两个文本值是否相同 |
| 2. | FIND 在另一个文本值中查找一个文本值（区分大小写） |
| 3. | SEARCH 在另一个文本值中查找一个文本值（不区分大小写） |
从文本中提取数据值
您需要从文本中提取所需的数据以构建相同的结构。 例如，在上面的示例中，您需要将数据放在三列中 – ProductID、Product_Description 和 Price。
您可以通过以下方式之一提取数据 −
- 使用将文本转换为列向导提取数据值
- 使用文本函数提取数据值
- 使用快速填充提取数据值
使用将文本转换为列向导提取数据值
如果您的字段是 −
- 由一个字符分隔，或
- 按列对齐，每个字段之间有空格。
在上面的示例中，字段由字符"|"分隔。 因此，您可以使用将文本转换为列 向导。
选择数据。
将值复制并粘贴到同一位置。 否则，将文本转换为列会将函数而不是数据本身作为输入。
选择数据。
单击功能区上数据 选项卡下数据工具 组中的文本到列。
步骤 1 − 将文本转换为分栏向导 - 出现第 1 步，共 3 步。
- 选择分隔符。
- 单击"下一步"。
步骤 2 − 将文本转换为分栏向导 - 出现第 2 步，共 3 步。
在定界符下，选择其他。
在其他旁边的框中，键入字符|
点击下一步。
步骤 3 − 将文本转换为分栏向导 - 出现第 3 步，共 3 步。
在此屏幕中，您可以在向导中选择数据的每一列并设置该列的格式。
对于 Destination，选择单元格 D3。
您可以点击高级，在出现的高级文本导入设置对话框中设置小数点分隔符和千位分隔符。
单击完成。
转换为列的数据显示在三个列中 - D、E 和 F。
- 将列标题命名为 ProductID、Product_Description 和 Price。
使用文本函数提取数据值
假设您的数据中的字段既没有用字符分隔，也没有按列对齐，每个字段之间有空格，您可以使用文本函数来提取数据值。 即使在字段被分隔的情况下，您仍然可以使用文本函数来提取数据。
一些对此有用的文本函数是 −
| S.No. | 函数 & 说明 |
|---|---|
| 1. | LEFT 返回文本值最左边的字符 |
| 2. | RIGHT 返回文本值最右边的字符 |
| 3. | MID 从指定位置开始的文本字符串中返回特定数量的字符 |
| 4. | LEN 返回文本字符串中的字符数 |
您还可以根据手头的数据组合这些文本函数中的两个或多个，以提取所需的数据值。 例如，使用 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 有几个内置的文本函数，您可以使用它们来格式化包含文本的数据。 这些包括 −
根据您的需要格式化文本的函数 −
| S.No. | 函数 & 说明 |
|---|---|
| 1. | LOWER 将文本转换为小写 |
| S.No. | 函数 & 说明 |
|---|---|
| 1. | UPPER 将文本转换为大写 |
| 2. | PROPER 将文本值的每个单词的第一个字母大写 |
将数字转换和/或格式化为文本的函数 −
| S.No. | 函数 & 说明 |
|---|---|
| 1. | DOLLAR 使用 $（美元）货币符号将数字转换为文本 |
| 2. | FIXED 将数字格式化为具有固定小数位数的文本 |
| 3. | TEXT 格式化数字并将其转换为文本 |
将文本转换为数字的函数 −
| S.No. | 函数 & 说明 |
|---|---|
| 1. | VALUE 将文本参数转换为数字 |
使用文本函数执行数据操作
您可能必须对数据执行某些文本操作。 例如，如果员工的登录 ID 在组织中更改为新格式，则基于格式更改，可能必须进行文本替换。
以下文本函数可帮助您对包含文本的数据执行文本操作 −
| S.No. | 函数 & 说明 |
|---|---|
| 1. | REPLACE 替换文本中的字符 |
| 2. | SUBSTITUTE 用新文本替换文本字符串中的旧文本 |
| 3. | CONCATENATE 将多个文本项合并为一个文本项 |
| 4. | CONCAT 组合来自多个范围和/或字符串的文本，但不提供定界符或 IgnoreEmpty 参数。 |
| 5. | TEXTJOIN 组合来自多个范围和/或字符串的文本，并在将组合的每个文本值之间包含一个您指定的分隔符。 如果分隔符是空文本字符串，此函数将有效地连接范围。 |
| 6. | REPT 重复给定次数的文本 |
