Excel文本公式：弯曲数据的19种深层方法

更好的数据可以让您做出更好的决策吗？

也许是的， Excel文字公式 您可以有效地分析数据。

您的企业每天都会生成数百万行数据，并且在大多数情况下，数据不是以结构化方式进行的，即使世界上最好的技术也无法为您提供现成的数据来支持您的决策过程。

我认为这就是我们如此严重依赖MS Excel的原因 简单到复杂的分析.

这是一个教程，可以帮助您了解各种excel文本公式以及如何有效地使用它们来系统地分析数据。

 

Excel文本公式的列表以及示例：

 

1）CONCATENATE：

连接两个或多个文本字符串的公式，最大不超过255

句法： CONCATENATE（文本1，[文本2]，…)
Text1:  您要添加的第一个文本
Text2:  第二，第三，第四，依此类推，最多255

 

Example＃1： 在下面的示例中，我在A列中使用了名字，在B列中使用了名字，我只想将这两个名称加在一起，以使我的数据在分析时看起来更好。

为此，我们可以编写一个简单的公式，如下所示。

= CONCATENATE（A2，”.”,B2)

在我上面的公式中，A1&B2是我的数据单元&“。”代码用于添加单个句点（点）以首先区分& second name.

与此类似，您也可以添加任何类型的符号，例如– * etc.

 

范例＃2 因为我们都有第一 &“ C”列中的第二个名称，现在我们想通过在末尾添加“ @ xyz.com”将它们转换为电子邮件ID。

这是一个公式：

= CONCATENATE（C2，”@xyz.com”) = [电子邮件 protected]

我选择了C2列，因为这是我们添加公式以首先合并的地方& second names.

“ @ xyz.com”：它是电子邮件扩展名，与任何公司电子邮件ID都非常相似，例如@ hp.com，@ oracle.com等。

请注意， 您可以使用连接公式添加任何类型的文本，但是这里要注意的是，它应该用这样的代码括起来 “您的文字”.

 

范例3： 现在，我们在“ D”列中具有电子邮件ID，我们希望向所有人发送电子邮件，但是这里的挑战是您需要并排复制粘贴所有电子邮件以进行大规模交流。

实际上，您可以使用以下公式快速创建邮件列表。这个公式有两个步骤

 

Step1: 在“ E2”列中一起添加第一和第二电子邮件ID

= CONCATENATE（D2，”;”,D3)

第2步 ： 第二个公式，用于将E2中的文本字符串与D4连接起来，以便当我们向下拖动到结尾时，您将自动创建电子邮件列表。

 =CONCATENATE(E2,”;”,D4)

E2：这是我首先添加的范围& Second email id’s

D4：是我要添加到邮件列表中的下一个电子邮件ID

注意： 请不要选择D3作为第二个公式的范围，因为它将两次添加第二个电子邮件ID。

对于您的邮件列表，只需转到数据的最后一行，然后在Outlook中进行复制粘贴即可。

另请阅读： 4种MS Excel替代品来分析数据

 

2）完全（）

这是一个比较两个字符串并告诉您两个字符串是否相同的公式，如果相同，则返回“ True”，否则返回“ False”。

句法：  EXACT(text1, text2)

请注意： 确切区分大小写，因此在比较时要小心

 

例子＃4  在此示例中，我只是比较推销员姓名以检查来自两个不同来源的数据准确性。

这是一个公式：

= EXACT（A2，B2）

如上所述，我只是简单地查看A列中的推销员姓名是否与B列中的确切行匹配。

在C2单元格中，我有一个“ false”，因为两个名称不相同；在C4单元格中，我又有一个false，但是这个名称是相同的，但是名字的首字母是小写的“ Russel.dana”。

 

3) 找（ )

将返回搜索字符串的第一个出现位置的公式。

句法： FIND（find_text，interior_text，[start_num]）

find_text： 您要寻找的文字字串

inside_text： 目标文字，基本上就是您要搜索的文字

[start_num]： 您可以从要检查的地方指定起点，这是一个可选值

 

范例＃5 在本练习中，我们有一个如下的员工姓名，您需要找出员工姓氏的起始位置。

=查找（“.”,”Barry.Chee”) = 6

I’我基本上在上面的文本中查找一个句点，因为这是您可以看到员工姓氏起始位置的唯一点。

请注意， 找&在大多数情况下，搜索功能看起来相同，但事实并非如此。您 可以参考查找与搜索 进一步了解。

 

4）左（）

从给定的文本字符串中提取一定数量的字母的公式。

句法： LEFT（文字，[num_chars]）

文本： your actual text

[num_chars]）： 我们可以指定要提取的字符数

 

Example＃6 在本练习中，我们列出了带有产品代码前缀的产品列表，现在我们只想将产品代码提取到单独的列中，以便可以将其用于数据分析。为此，您可以编写如下的简单公式。

=左（A2,6）

A2：是我的文字

6：我们只想从文本字符串的左侧提取前6位数字

 

左组合& 找formulas:

实际上，您可以通过组合两个或更多公式来进行更复杂的分析，只是为了给出一种味道，这是我的另一个示例。

 

Example＃7 在此示例中，在一列中有雇员编号，后跟雇员姓名，现在您只需要将雇员编号提取到另一列中。

这里的问题是，员工编号没有特定的位数，很少有员工具有3位代码，很少有5位数字，而很少有7位数字。

基本上，我将该公式分为两部分，第一部分是确定要提取的位数，为此，您可以使用如下所示的FIND函数。

第1步：

找（” “,A2)  = 7

在find_text部分中，我添加了打开和关闭的代码来指示我们想要在文本字符串中搜索空格。

A2是我们的原文

使用此公式，您将能够根据空白识别要提取的字母数。

第2步：

左（A2，找（” “,A2))

在这一步中，我添加了左公式&选择A2作为我的原始文本。

如果您清楚地注意到，我已经添加了FIND公式来标识要提取的数字，那么结果将正是我们想要的。

 

5）.LEN（） 

用于标识给定文本字符串中的多个字母的公式。

句法： LEN（文字）

LEN是大多数excel专家广泛使用的公式之一，它非常简单，就像冠军一样。

 

Example＃8  在此示例中，我们在一行中有员工电子邮件ID，您想要查看每个员工电子邮件ID中有多少个字母。

这是一个公式：

LEN（“[电子邮件 protected]”）= 21

注意： Len函数也会计算空格

好的，我上面的例子很简单&直截了当，您什么也做不了。但是，当您将LEN公式与任何其他公式结合使用时，您将拥有更大的优势。

这是我的示例，以说明如何使用Len和Left公式提取特定的文本字符串。

 

Example＃9 我们使用与上面示例相同的员工电子邮件ID，现在我们只想从电子邮件ID中提取员工姓名。

=左（A2，LEN（A2）-8）

A2是我们的文本，在这种情况下，它是员工的电子邮件ID

LEN（A2）：我们基本上是在计算员工电子邮件ID中的位数

-8：这是我们不想在新列中包含的字母数（在所有电子邮件ID中，我们共有一个“ @ xyz.com”，因此当我将这些字母数为8左右时）

因此，基本上，我要指示Len公式计算总位数，然后从中减去8位数字，以便将剩余字母提取到新列中。

 

6）降低（）

将任何文本转换为小写字母的公式。

句法： 下（文字）

 

Example＃10  在此示例中，我们有一个大写的文本，如下所述，现在您要将其转换为小写。为此，您可以像这样使用Lower公式。

=降低（“HELLO JAY GOOD MORNING .. !!”) = 你好杰伊早上好.. !! 

 

7）MID（）

这是一个根据用户要求提取文本字符串中间值的公式。

句法： MID（文字，start_num，num_chars）

文本： 您要从中提取一定数量字母的数据

start_num： 用户输入基于所需文本的开始位置

num_chars： 您要从文本中提取的字符数

 

Example＃11  在我的示例中，我们有一个带有公司名称，产品名称和产品代码的产品列表，您只需要将产品名称提取到其他单元格中。

= MID（A1,8,40）

A1：是我们的数据范围

8：您想要的值的起始位置，在这种情况下，我们有“ Ballon” 6个字母和1个空格=7。因此起始位置为8。

40：我们产品中的字母数，因产品而异，因此我’给出了40个最大限制。

8）道具（）

这是一个公式，可将每个单词的第一个字母转换为大写字母，而其余所有字母均转换为小写字母。

这是一个简单的公式，可以将不正确的文本转换为正确的文本：

= PROPER（“HELLO JAY GOOD MORNING .. !!”）= 你好杰伊早上好.. !!

另请阅读： 高级Vlookup教程

 

9）替换（）

用于替换句子中文本的特定部分的公式。

句法： 替换（old_text，start_num，num_chars，new_text）

old_text： 您的实际文字

start_num： 您要替换的位置或字母

num_chars： 您希望替换多少个字符

new_text： 以及您要添加的下一个文本

 

Example＃13： 我使用了示例11中的相同数据。在这种情况下，我们不需要产品代码，因此我们必须将其替换为无。

= REPLACE（A1,19,3，””) 

 

10）REPT（）

用于根据用户想要的次数重复文本或符号的公式。

仅举一个简单的例子，我们想在一个单元格中重复10次*符号，为此我们可以编写一个公式。

= REPT（“*”,10) = **********

REPT公式对于根据用户自定义在单元格内创建折线图非常有用

 

11）搜寻（）

用于标识用户定义的文本字符串在文本中的位置的公式。

句法： SEARCH（find_text，within_text，[start_num]）

find_text： 您要查找的文字，字母或符号

inside_text： 您想看看哪个目标文字

[start_num]： 是一个可选值，通过它可以定义起点以执行字符串搜索。

 

范例＃14： 我们在A1栏中列出了员工姓名，您需要找出员工姓氏的起始位置。

A2 =莉莉·梅里特

=搜索（” “,A2) = 7

在我上面的公式中，搜索值是一个空格，因此我使用的是“”代码，而A2是我们的目标文本。

7是上述公式的结果，这意味着A2中的员工姓氏从第7位开始。

另一个变体： 如果文本字符串中有多个空格怎么办？例如，相同的雇员姓名的末尾也有一个雇员ID。

这是通过定义搜索位置的起始编号的公式。

=搜索（” “,E2,搜索（” “,E2)+1)

我添加了另一个搜索公式来标识第一个空格的位置，然后添加了+1来定义第二个空格的起始编号。

例如，根据我的第一个公式，您将获得“ 7”，即您的第一个空格位置，因此，如果我加+1，则在我们的起始数字部分将为8。

因此，我们基本上是在指导excel从文本的第8个位置查找空白。

 

12）。右（）

从文本字符串的右侧提取一定数量的字符的公式。

句法： RIGHT（文本，[num_chars]）

文本： 用户定义的文字

[num_chars]：  您要提取的字母数。

 

范例＃15： 在此示例中，我们在A列中有员工全名，您只需要将员工姓氏提取到B列中。

A2中的文字：Lillie.Merrit

=右（A2,6）= Merrit

A2是我的文字

6是要提取的多个字母。

 

另一个变体：

如果您不知道要提取多少个字母怎么办，我们可以做些什么..？确实是的。

这是一个可以自动识别姓氏并提取到新单元格的公式。

= RIGHT（A2，LEN（A2）-SEARCH（“.”,A2))

为了定义字符数，我在这里使用了两个不同的公式。

LEN函数可识别文本字符串中的字母总数，并具有搜索公式，可根据我自己的定义找出特定的文本位置。

因此，基本上，我们从文本中减去直到“。”或“。”为止的字符总数。

 

13）替换（）

根据用户输入来替换原始文本中文本，符号或数字的某些部分的公式。

句法： SUBSTITUTE（文本，old_text，new_text，[instance_num]）

文字： 您的实际文字

old_text： 您要替换的旧文本

new_text： 根据您的要求提供新文本

[instance_num]： 您可以定义实例编号，例如第一次出现，第二次出现等等。

 

范例＃16： 在此示例中，我们在A列中拥有相同的员工姓名，而您需要用下划线“ _”替换姓氏和名字之间的句点。

A2中的文字：Lillie.Merrit

= SUBSTITUTE（A2，”.”,”_”) = Lillie_Merrit

A2是我们的实际文字

“。”是我们拳头与众不同之处& second name

“_”您想用下划线代替句点

 

另一个变体： 在此示例中，我们没有句号，而是第一，第二之间有间隔&员工代码，您仅需要用下划线替换员工姓名。员工代码应与以前相同。

= SUBSTITUTE（E2，” “,”_”,1)

唯一的变化是，我将实例号添加为1， 这意味着我们要替换第一个实例的文本，而忽略所有其他实例。

 

14）TRIM（）

删除句子中不需要的空格的公式

句法： TRIM（文字）

 

Example＃17：  我们在文本下方有个单词之间的空格，现在您需要从文本中删除不需要的空格。

= TRIM（“您好杰伊早上好.. !!”) = 你好杰伊早上好.. !!

使用上述公式，您实际上可以删除其他空格&每个单词之间仅保留一个空格。

 

15）。上（）

将字母从小写转换为大写的公式

句法： UPPER（文字）

例如，如果您有这样的文字“ hi good morning”，您可以轻松地将其转换为大写形式，如下所示

= UPPER（“嗨，早上好”）=嗨，早上好

 

结论：

Excel文本公式对于任何类型的分析都是很大的交易，我们拥有的数据越复杂，我们对它们的依赖就越多。

越来越多的需求 分析人员学习新技术 分析复杂数据并将其转化为有意义的完整信息以支持决策。

如果你不是’以前使用这些公式，那么您’最有可能通过手动操作来浪费您的时间。

为什么不’从今天开始，您开始在日常工作中使用这些功能吗？

而且，如果您已经使用这些公式进行分析，那么我’我想知道您认为哪种公式更有用..？还是您认为我错过了您最喜欢的公式之一？