excel函数:汉字转全拼_30天中30个Excel函数：18

excel函数:汉字转全拼

Yesterday, in the 30XL30D challenge, we identified errors with the ERROR.TYPE function, and saw that it could help with Excel troubleshooting. For day 18 in the challenge, we'll examine the SEARCH function. It looks for a character, or characters, within a text string, and tells you where it was found. We'll see how to handle any errors that it returns.

昨天，在30XL30D挑战中，我们使用ERROR.TYPE函数识别了错误，并发现它可以帮助解决Excel故障。在挑战的第18天，我们将检查SEARCH函数。它在文本字符串中查找一个或多个字符，并告诉您在哪里找到它。我们将看到如何处理它返回的任何错误。

NOTE: Get all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).

注意：在一个易于使用的单一参考文件中获取所有30个Function内容-30天电子书工具包中的30个Excel Functions ($ 10)。

So, let's take a look at the SEARCH information and examples, and if you have other tips or examples, please share them in the comments.

因此，让我们看一下SEARCH信息和示例，如果还有其他提示或示例，请在评论中分享。

功能18：搜索 (Function 18: SEARCH)

The SEARCH function looks for a text string, within another text string, and returns its position, if found. It is not case sensitive.

SEARCH函数在另一个文本字符串中寻找一个文本字符串，并返回其位置(如果找到)。它不区分大小写。

您如何使用SEARCH？ (How Could You Use SEARCH?)

The SEARCH function looks for a text string, within another text string, and it can:

SEARCH函数在另一个文本字符串中寻找一个文本字符串，它可以：

Find a text string within another text string – not case sensitive
在另一个文本字符串中查找一个文本字符串–不区分大小写
Use wildcards in the search
在搜索中使用通配符
Specify a start number within search text
在搜索文字中指定一个起始号码

搜索语法 (SEARCH Syntax)

The SEARCH function has the following syntax:

SEARCH函数具有以下语法：

- find_text is the text that you're looking for.
  find_text是您要查找的文本。
- within_text is the string that you're searching in.
  inside_text是您要搜索的字符串。
- if start_num is omitted, the search starts with the first character
  如果省略了start_num，则搜索从第一个字符开始
SEARCH(find_text,within_text,start_num)
SEARCH(find_text，within_text，start_num)

搜索陷阱 (SEARCH Traps)

The SEARCH function will return the position of the first matching string, regardless of case. If you need a case sensitive search, use the FIND function, which we'll see later in the 30XL30D challenge.

无论大小写如何，SEARCH函数都将返回第一个匹配字符串的位置。如果您需要区分大小写的搜索，请使用FIND函数，稍后在30XL30D挑战中将看到该函数。

示例1：在字符串中查找文本 (Example 1: Find Text in a String)

Use the SEARCH function to look for text within a text string. In this example, we're looking for a single character (entered in cell B5), within a text string in cell B2.

使用SEARCH函数在文本字符串中查找文本。在此示例中，我们正在单元格B2的文本字符串中查找单个字符(在单元格B5中输入)。

=SEARCH(B5,B2)

=搜索(B5，B2)

If the text is found, the SEARCH function returns the number of its starting position in the text string. If it's not found, the result is a #VALUE! error.

如果找到了文本，则SEARCH函数将返回其在文本字符串中的起始位置编号。如果找不到，则结果为#VALUE！错误。

You could use IFERROR to wrap the SEARCH function, and display a message, if the result is an error. The IFERROR is available in Excel 2007 and later versions. For earlier versions, you can use IF with ISERROR.

如果结果是错误的，则可以使用IFERROR包装SEARCH函数并显示一条消息。 IFERROR在Excel 2007和更高版本中可用。对于早期版本，可以将IF与ISERROR一起使用。

=IFERROR(SEARCH(B5,B2),"Not Found")

= IFERROR(SEARCH(B5，B2)，“未找到”)

示例2：在SEARCH中使用通配符 (Example 2: Use wildcards with SEARCH)

Another way to check the SEARCH results is with the ISNUMBER function. If the string is found, the SEARCH result is a number, so the ISNUMBER result is TRUE. If the text is not found, SEARCH results in an error, and ISNUMBER returns FALSE.

检查SEARCH结果的另一种方法是使用ISNUMBER函数。如果找到字符串，则SEARCH结果为数字，因此ISNUMBER结果为TRUE。如果找不到该文本，则SEARCH会导致错误，并且ISNUMBER返回FALSE。

You can also use wildcards in the find_text argument. The * (asterisk) represents any number of characters, or no characters, and the ? (question mark) represents a single character.

您也可以在find_text参数中使用通配符。 *(星号)代表任意数量的字符，或者不包含任何字符，而？ (问号)代表单个字符。

In this example, the * wildcard is used, so central, center and centre are all found in the street addresses.

在此示例中，使用*通配符，因此中心，中心和中心都可以在街道地址中找到。

=ISNUMBER(SEARCH($E$2,B3))

= ISNUMBER(SEARCH($ E $ 2，B3))

示例3：为SEARCH指定起始编号 (Example 3: Specify Start Number for SEARCH)

By typing two minus signs (double unary) in front of the ISNUMBER function, it returns 1/0 instead of TRUE/FALSE. Then, a SUM function in cell E2 can total the number of records where the text string was found.

通过在ISNUMBER函数前面键入两个减号(双一进制)，它将返回1/0，而不是TRUE / FALSE。然后，单元格E2中的SUM函数可以总计找到该文本字符串的记录数。

In this example, City and Occupation are shown in column B. We want to find all occupations with the text string entered in cell E1. The formula in cell C2 is:

在此示例中，B列显示了城市和职业。我们要查找在单元格E1中输入文本字符串的所有职业。 C2单元格中的公式为：

=–ISNUMBER(SEARCH($E$1,B2))

= –ISNUMBER(SEARCH($ E $ 1，B2))

The formula found the string that contain "bank", but one of those is in a City name, not the occupation:

该公式找到了包含“ bank”的字符串，但其中一个是城市名称而不是职业名称：

There is a pipe character after each city name, so we can add a SEARCH for that. Its position can be used as the start_number argument in the main SEARCH, so the cities will be ignored when searching.

每个城市名称后都有一个竖线字符，因此我们可以为此添加SEARCH。它的位置可用作主SEARCH中的start_number参数，因此在搜索时将忽略城市。

Now, with the revised formula, only the rows with "bank" in the occupation are counted.

现在，使用修改后的公式，仅计算职业中带有“ bank”的行。

=–ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2)))

= –ISNUMBER(SEARCH($ E $ 1，B2，SEARCH(“ |”，B2)))

下载SEARCH功能文件 (Download the SEARCH Function File)

To see the formulas used in today's examples, you can download the SEARCH function sample workbook. The file is zipped, and is in xlsx file format, with no macros.

要查看当前示例中使用的公式，可以下载SEARCH函数示例工作簿 。该文件已压缩，格式为xlsx，没有宏。