Excel数据分析教程

使用文本功能清理数据

← 上一节 下一节 →

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

最初，您需要清理数据.数据清理包括从文本中删除不需要的字符接下来，您需要以所需的形式构建数据以进行进一步分析.您可以通过 :

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

  • 从文本中提取数据值.

  • 使用文本函数格式化数据.

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

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

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

  • 前导空格，和/或

  • 之间的额外空格单词.

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

请考虑以下示例 :

产品数据

这是您拥有的原始数据获得的产品信息包含产品ID，产品描述和价格.字符"&＃x7c"分隔每行中的字段.

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

导入数据

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

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

S.No.功能&描述
1.

CLEAN

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

2.

TRIM

从文本中删除空格

  • 选择单元格C3  -  C11.

  • Type = TRIM(CLEAN(B3))然后按CTRL + 输入.

公式填写在单元格C3  -  C11中.

公式已填充

结果如下所示 :

公式填充结果

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

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

S.No.功能&描述
1.

确认

检查两个文本值是否相同

2.

查找

在另一个中查找一个文本值(区分大小写)

3.

SEARCH

在另一个中查找一个文本值(不区分大小写)

从文本中提取数据值

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

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

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

  • 使用文本函数提取数据值

  • 使用Flash Fill提取数据值

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

如果字段为 : ，则可以使用将文本转换为列向导将数据值提取到Excel列中;

  • 由字符分隔，或

  • 在每个字段之间有空格的列中对齐.

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

  • 选择数据.

  • 在同一个地方复制并粘贴值.否则，将文本转换为列将函数而不是数据本身作为输入.

将文本转换为列

  • 选择数据.

  • 点击数据标签下的数据工具组中的文字到列在功能区上.

第1步 : 将文本转换为列向导 - 显示第1步(共3步).

  • 选择分隔.

  • 单击接下来.

将文本转换为列Step1

第2步 : 将文本转换为列向导 - 显示第2步(共3步).

  • 在分隔符下，选择其他.

  • 在其他旁边的框中，输入字符&＃x7c;

  • 点击下一步.

将文本转换为列Step2

步骤3 : 将文本转换为列向导 - 显示第3步(共3步).

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

  • 对于目的地，选择单元格D3.

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

  • 单击完成.

将文本转换为列Step3

您的数据转换为列出现在三列--D，E和F.

  • 将列标题命名为ProductID，Product_Description和Price.

名称列标题

使用文本提取数据值函数

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

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

S.No.功能&描述
1.

LEFT

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

2.

RIGHT

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

3.

MID

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

4.

LEN

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

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

在上面的示例中，

  • 所有字符留给第一个&＃x7c;给出名称ProductID.

  • 所有字符都在第二个&＃x7c;给出名称Price.

  • 第一个&＃x7c之间的所有字符;和第二个&＃x7c;给出名称Product_Description.

  • 每个&＃x7c;之前和之后都有空格.

观察此信息，您可以使用以下步骤提取数据值 :

  • 找到First&＃x7c的位置; -  第一&＃x7c;职位

    • 您可以使用FIND功能

  • 找到第二个位置&＃x7c; -  第二&＃x7c;职位

    • 您可以再次使用FIND功能

  • 从(第一个&＃x7c;位置  -  2)文本字符给出ProductID

    • 您可以使用左功能

  • (第一个&＃x7c位置 +  2)到(第二个&＃x7c位置  -  2)文字的字符给出Product_Description

    • 您可以使用MID功能

  • (第二个&＃x7c位置 +  2)到文本的结束字符给出价格

    • 你可以使用RIGHT函数

提取数据值

结果如下所示 :

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

使用Flash填充提取数据值

使用Excel Flash Fill 是从文本中提取数据值的另一种方法.但是，仅当Excel能够在数据中找到模式时才有效.

步骤1 : 为数据旁边的ProductID，Product_Description和Price创建三列.

创建列

第2步 : 从B3复制并粘贴C3，D3和E3的值.

粘贴值

第3步 : 选择单元格C3，然后单击数据选项卡上数据工具组中的 Flash Fill . ProductID的所有值都被填充.

Flash Fill

第4步 : 对Product_Description和Price重复上述给定步骤.数据已填充.

重复步骤

使用文本函数格式化数据

Excel有几个内置的文本函数，可用于格式化包含文本的数据.这些包括 :

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

S.No.功能&描述
1.

LOWER

将文本转换为小写

S.No.功能&描述
1.

UPPER

将文字转换为大写

2.

正确

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

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

S.No.功能与优惠描述
1.

DOLLAR

使用$(美元)货币格式将数字转换为文本

2.

固定

将数字格式化为具有固定小数位数的文本

3.

TEXT

格式化一个数字并将其转换为文本

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

S.No.功能&描述
1.

VALUE

将文本参数转换为数字

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

您可能需要执行对您的数据进行某些文本操作.例如，如果Employees的Login-ID在组织中更改为新格式，则基于格式更改，可能必须执行文本替换.

以下文本函数可帮助您在对包含Text : 的数据执行文本操作时;

S.No.功能&描述
1.

REPLACE

替换文字中的字符

2.

SUBSTITUTE

用文本字符串替换旧文本的新文本

3.

CONCATENATE

将多个文本项连接到一个文本项中

4.

CONCAT

组合来自多个范围和/或字符串的文本，但它不提供分隔符或IgnoreEmpty参数.

5.

TEXTJOIN

合并多次运行的文本ges和/或字符串，并包括在将要组合的每个文本值之间指定的分隔符.如果分隔符是空文本字符串，则此函数将有效地连接范围.

6.

REPT

重复给定次数的文本

← 上一节 下一节 →

相关菜鸟教程