Excel Data Analysis 简明教程

Cleaning Data with Text Functions

从不同来源获得的数据可能不适合于分析。在本章中，你将了解如何准备你的数据，即以文本格式用于分析的数据。

最初，你需要清理数据。数据清理包括从文本中移除无用字符。接下来，你需要按你所需的分析格式来组织数据。通过以下步骤可以完成该操作：

  1. 使用文本函数查找必需的文本模式。

  2. 从文本中提取数据值。

  3. 使用文本函数格式化数据。

  4. 使用文本函数执行数据操作。

Removing Unwanted Characters from Text

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

  1. leading spaces, and/or

  2. extra spaces between words.

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

考虑以下示例 −

product data

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

当您将该数据导入 Excel 工作表时，它将显示如下所示 −

import data

正如您所看到的，整个数据都在单列中。在执行数据分析之前，您需要对数据进行结构化处理。但是，您首先需要清洗数据。

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

S.No.

Function & Description

1.

CLEAN 从文本中删除所有不可打印的字符

2.

TRIM Removes spaces from text

  1. 选择单元格 C3 – C11。

  2. 键入 =TRIM(CLEAN(B3))，然后按 Ctrl + Enter。

公式填入单元格 C3 – C11 中。

formula filled

结果将如下所示：

formula filled result

Finding required Text Patterns with the Text Functions

要对数据进行结构化处理，您可能必须执行某些文本模式匹配，以从中提取数据值。一些可用于此目的的文本函数包括：

S.No.

Function & Description

1.

EXACT 检查两个文本值是否相同

2.

FIND 在一个文本值中查找另一个（区分大小写）

3.

SEARCH 在一个文本值中查找另一个（不区分大小写）

Extracting Data Values from Text

您需要从文本中提取所需数据以对其进行结构化处理。在上例中，假设您需要将数据放入三个列中：ProductID、Product_Description 和 Price。

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

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

  2. 使用文本函数提取数据值

  3. 使用快速填充提取数据值

Extracting Data Values with Convert Text to Columns Wizard

如果您的字段符合以下条件，则可以使用 Convert Text to Columns Wizard 将数据值提取到 Excel 列中：

  1. 以字符分隔，或者

  2. 在列中对齐，每个字段之间有空格。

在上例中，字段以字符“|”分隔。因此，可以使用 Convert Text to Columns 向导。

  1. Select the data.

  2. 在相同位置复制并粘贴值。否则， Convert Text to Columns 会将函数而并非数据本身作为输入。

convert text to columns

  1. Select the data.

  2. 在功能区上 Data 标签下的 Data Tools 组中单击 Text to Columns 。

出现 Step 1 −“文本到列向导” - 步骤 1/3。

  1. Select Delimited.

  2. Click Next.

convert text to columns step1

出现 Step 2 −“文本到列向导” - 步骤 2/3。

  1. Under Delimiters, select Other.

  2. 在 Other 旁边的方框中，键入字符 |

  3. Click Next.

convert text to columns step2

出现 Step 3 −“文本到列向导” - 步骤 3/3。

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

  1. 对于 Destination ，选择单元格 D3。

  2. 您可以单击 Advanced ，并在出现的 Advanced Text Import Settings 对话框中设置 Decimal Separator 和 Thousands Separator 。

  3. Click Finish.

convert text to columns step3

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

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

name column headers

Extracting Data Values with Text Functions

假设您数据中的字段既没有用字符分隔，也没有用空格排列在列中，则可以使用文本函数来提取数据值。即使字段已分隔，您仍然可以使用文本函数来提取数据。

可用于此目的的一些文本函数如下：

S.No.

Function & Description

1.

LEFT 从文本值返回最左边的字符

2.

RIGHT 从文本值返回最右边的字符

3.

MID 从文本字符串返回从您指定的位置开始的特定数量的字符

4.

LEN 返回文本字符串中的字符数量

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

在上面示例中，

  1. 直到第一个 | 左边的所有字符提供名称“ProductID”。

  2. 直到第二个 | 右边的所有字符提供名称“Price”。

  3. 介于第一个和第二个 | 之间的所有字符提供名称“Product_Description”。

  4. 每个 | 的前后都有一个空格。

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

  1. 查找第一个 | 的位置 - First | Position 您可以使用 FIND 函数

  2. 查找第二个 | 的位置 - Second | Position 您可以再次使用 FIND 函数

  3. 文本的从 ( First | Position - 2) 个字符开始的部分提供了 First | Position 您可以使用 LEFT 函数

  4. 文本的从 ( Second | Position + 2) 到 ( Second | Position - 2) 个字符提供了 Second | Position 您可以使用 MID 函数

  5. 文本的从 ( Flash Fill + 2) 个字符到结尾的部分提供了 Second | Position 您可以使用 RIGHT 函数

extract data values

结果将如下所示：

extract data values result

您可能会观察到，价格列中的值是文本值。要对这些值执行计算，您必须设置相应单元格的格式。您可以参阅下面提供的部分，了解如何设置文本格式。

Extracting Data Values with Flash Fill

使用 Excel Step 1 是从文本中提取数据值的另一种方式。但是，只有在 Excel 能够在数据中找到模式时，此方法才会起作用。

Step 2 − 在数据旁边创建三列，分别用于 ProductID、Product_Description 和 Price。

create columns

Step 3 − 复制并粘贴 B3 中的 C3、D3 和 E3 的值。

paste values

Flash Fill − 选择单元格 C3，然后单击 Data Tools 选项卡上的 Data 组中的 Step 4 。将填充 ProductID 的所有值。

flash fill

Functions that format the Text as per your need − 对 Product_Description 和 Price 重复上述步骤。将填充数据。

repeat steps

Formatting Data with Text Functions

Excel 具有几个内置文本函数，您可以使用这些函数设置包含文本的数据的格式。其中包括 −

PROPER −

S.No.

Function & Description

1.

LOWER Converts text to lowercase

S.No.

Function & Description

1.

UPPER Converts text to uppercase

2.

@{s16}将文本值中每个单词的第一个字母大写

Functions that convert and/or format the Numbers as Text −

S.No.

Function & Description

1.

DOLLAR 使用 $（美元）货币格式将数字转换为文本

2.

FIXED 使用固定位数的小数点将数字设定为文本格式

3.

TEXT 将数字转换为文本

Functions that convert the Text to Numbers −

S.No.

Function & Description

1.

VALUE 将文本参数转换为数字

Executing Data Operations with the Text Functions

你可能需要在你的数据上执行一些文本操作。例如，如果组织中员工的登录 ID 更改为新格式，基于格式更改，可能必须执行文本替换。

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

S.No.

Function & Description

1.

REPLACE Replaces characters within text

2.

SUBSTITUTE 替换文本字符串中的旧文本为新文本

3.

CONCATENATE 将几个文本项合并成一个文本项

4.

CONCAT 合并来自多个区域和/或字符串的文本，但它不提供分隔符或 IgnoreEmpty 参数。

5.

TEXTJOIN 合并来自多个区域和/或字符串的文本，并在每个要合并的文本值之间包括你指定的定界符。如果定界符是空文本字符串，此函数将有效地连接范围。

6.

REPT 给定次数内重复文本