栏目分类

你的位置:盛煌娱乐 > 业务范围 >

业务范围

Excel Search函数和SearchB函数模糊查找的用法12个实例,含与Mid、Sum、Index、Match和Count组合

在 Excel 中,Search函数用于查找指定文本在查找文本的文本中的位置,SearchB函数与Search函数的功能一样,但它们有区别,Search函数把全角(如“汉字”)和半角(如“数字和字母”)字符都计作一个字符,而SearchB函数把全角字符计作两个字节、半角字符计作一个字节。另外,Search函数和SearchB函数与Find函数和FindB函数的区别为:前两个忽略大小写且允许使用通配符模糊查找,后两个区分大小写且不允许使用通配符。

Search函数和SearchB函数可与 Mid、Sum、Index、Match、IsNumber 和 Count 等函数组合使用,例如 Mid + Search(或 SearchB)组合实现截取指定字符串,Count + Search函数组合实现统计一列中包含指定文本的单元格数,Sum + IsNumber + Search函数组合实现一次查找多个值,Index + Match + IsNumber + Search函数组合实现返回两列匹配的对应单元格。

一、Excel Search函数和SearchB函数的语法

1、Search函数表达式:SEARCH(Find_Text, Within_Text, [Start_Num])

中文表达式:SEARCH(查找文本, 要查找文本的文本, [查找开始位置])

2、SearchB函数表达式:SEARCHB(Find_Text, Within_Text, [Start_Num])

中文表达式:SEARCHB(查找文本, 要查找文本的文本, [查找开始位置])

3、说明:

A、Search函数把每个字符都计作一个字节,无论是半角字符(如“数字或字母”)还是全角字符(如“汉字”;SearchB函数把半角字符计作 1 个字节、全角字符计作 2 个字节。Search函数和SearchB函数都忽略大小写,还可以使用通配符问号(?)或星号(*);而Find函数和FindB函数要求区分大小写且不能使用通配符,这也是它们的区别。

B、可以在 Find_Text 中使用通配符问号(?)或星号(*),问号表示任意一个字符,星号表示任意一个或多个字符;如果要查找通配符,需要在它们前面加转义字符 ~,则查找问号表示为 ~?、查找星号表示为 ~*。

C、如果查找空文本 (""),则返回第一个字符的位置 1。

D、Start_Num 为可选项,如果省略,则从第 1 个字符开始查找;如果 Start_Num 小于等于 0 或大于等于 Within_Text 的长度,Search 与 SearchB 都返回值错误 #VALUE!。

E、如果在 Within_Text 中没有找到查找文本,也返回值错误 #VALUE!。

二、Excel Search函数的使用方法及实例

(一)查找单词或词组的实例

1、选中 B1 单元格,输入公式 =SEARCH("tutolail",A1),按回车,返回 16;选中 B2,输入公式 SEARCH("教程",A2),按回车,也返回16;操作过程步骤,如图1所示:

图片

2、公式说明:

A、公式 =SEARCH("tutolail",A1) 中,单词 tutolail 为查找文本,A1 为查找文本的文本,公式的意思是:在 A1 中查找 tutolail 并返回 tutolail 的第一个字母 t 在 A1 中的位置。

B、公式 =SEARCH("教程",A2) 的意思是:在 A2 中查找“教程”并返回“教程”的第一个字“教”在 A2 中的位置;两个公式都返回 16,且查找文本前都为 15 个字符,而 A1 中 tutolail 前全为字母,A2 中“教程”前有字母、数字和汉字,说明 Search函数把字母、数字和汉字都算一个字符。

(二)使用通配符 ? 和 * 的实例

(1)使用 *

1、假如要在 Excel function option tutolail 查找 option。双击 B1 单元格,把公式 =SEARCH("o*n",A1) 复制到 B1,按回车,返回13;双击 B1,把 o*n 改 op*n,按回车,返回 16;操作过程步骤,如图2所示:

图片

图2

2、公式说明:

公式 =SEARCH("o*n",A1) 中的查找文本 n*o 有一个星号,意思是找以 n 开头且以 o 结尾的字符串,而 Excel function option tutolail 中,至少有三处符合条件,即 on、option 和 on option,而返回 13 是 on 的位置,因此要查找 option,必须在 o*n 的 o 后再加p。

(2)使用 ?

1、同样以在 Excel function option tutolail 中查找 option 为例。双击 B1 单元格,把公式 =SEARCH("o????n",A1) 复制到 B1,按回车,返回 16;操作过程步骤,如图3所示:

图片

图3

2、公式说明:

公式 =SEARCH("o????n",A1) 中的查找文本 o????n 有四个问号,每个问号代表一个字符,意思是查找以 n 开头且以 o 结尾并且 n 与 o 之间只有四个字符的字符串,所以恰好返回单词 option 在 A1 中的位置。

(三)查找空文本("")和空格(" ")的实例

1、双击 B1 单元格,把公式 =SEARCH("",A1) 复制到 B1,按回车,返回 1;再次双击 B1,把鼠标定位到双引号 "" 中,按一下空格键,在双引号中加一个空格,按回车,返回 6;操作过程步骤,如图4所示:

图片

图4

2、公式 =SEARCH("",A1) 查找空文本(""),Search函数默认返回第 1 个字符的位置 1;公式 =Search(" ",A1) 查找空格,返“Excel function option tutolail”中的第 1 个空格的位置 6。

(四)查找半角双引号(")与全角双引号(“”)的实例

1、双击 B1 单元格,把公式 =SEARCH("""",A1) 复制到 B1,按回车,返回 7;双击 B2,输入公式 =SEARCH("“",A2),按回车,返回 12;再次双击 B2,把 “ 改为 ”,按回车,返回 15;操作过程步骤,如图5所示:

图片

图5

2、公式说明:

A、公式 =SEARCH("""",A1) 查找半角双引号 ",值得注意的是,必须输入两个半角双引号,这样返回的位置仍然是第一个双引号的位置。

B、公式  =SEARCH("“",A2) 查找左边的全角双引号,查找右边的全角双引号,只需把 “ 改为 ”,但要注意,Excel 的默认字体不会显示全角双引号,需要把字体设置为中文字体(如“宋体”)才会显示。

(五)开始查找位置不在范围内导致 #VALUE! 错误的实例

1、双击 B1 单元格,把公式 =SEARCH("基础",A1,0) 复制到 B1,按回车,返回值错误 #VALUE!;双击 B1,将 0 改为 1,按回车,返回14;再次双击 B1,将 1 改为 17,按回车,返回 #VALUE!;操作过程步骤,如图6所示:

图片

图6

2、公式说明:

A、公式 =SEARCH("基础",A1,0) 中的 0 表示从第 0 个字符开始查找,但Search函数要求至少要从第 1 个字符开始查找,因此返回错误;把 0 改为 1 后,立即返回“基础”在 A1 中的正确位置。

B、公式 =SEARCH("基础",A1,17) 从第 17 个字符开始查找,但 A1 中的字符只有 16 个,所以返回错误。

(六)查找不到文本返回错误及处理方法实例

1、双击 B1 单元格,把公式 =SEARCH("3",A1) 复制到 B1,按回车,返回值错误 #VALUE!;再次双击 B1,把公式改为 =IFERROR(SEARCH("3",A1),"未找到"),按回车,返回“未找到”;操作过程步骤,如图7所示:

图片

图7

2、公式说明:

A、公式 =SEARCH("3",A1) 要在“Excel 2016 函数基础教程”中查找 3,但没有找到,所以返回值错误。

B、在公式 =IFERROR(SEARCH("3",A1),"未找到") 中,IfError函数用于 SEARCH("3",A1) 执行出错时的处理,如果 SEARCH("3",A1) 返回错误,则 IfError 返回“未找到”,否则返回 SEARCH("3",A1) 的返回值。

三、Excel SearchB函数的使用方法及实例

(一)使用通配符星号 * 的实例

1、双击 B1 单元格,把公式 =SEARCHB("o*n",A1) 复制到 B1,按回车,返回 13;再次双击 B1,在 A1 后输入“,14”,按回车,返回16;操作过程步骤,如图8所示:

图片

图8

2、公式说明:

A、公式 =SEARCHB("o*n",A1) 中,查找文本 o*n 使用了 *,表示查找以 o 开头且以 n 结尾的字符串,返回值为 13 ,是 A1 中 on 的位置。

B、公式 =SEARCHB("o*n",A1,14) 同样是查找 o*n,但从第 14 个字符开始查找,因此返回单词 option 的位置 16。

(二)使用通配符问号 ? 的实例

1、双击 B1 单元格,把公式 =SEARCHB("o????n",A1) 复制到 B1,按回车,返回 16;双击 B2,把公式 =SEARCHB("函????????程",A2) 复制到 B2,按回车,返回 12;作过程步骤,如图9所示:

图片

图9

2、公式说明:

A、公式 =SEARCHB("o????n",A1) 的查找文本 o????n 中使用了四个问号 ?,每个问号代表一个字符,意思是:在 A1 中查找以 o 开头且以 n 结尾并且 o 与 n 之间只能有四个字符的字符串。

B、公式 =SEARCHB("函????????程",A2) 的查找文本“函????????程”中,“函与程”之间有八个问号,每个问号代表一个字节(即一个半角字符),由于“函与程”之间有四个字汉字,每个汉字占两个字节,因此四个汉字要八个问号。

四、Excel Search函数和SearchB函数的应用实例

(一)Mid + Search函数组合实现截取指定字符串

1、假如要从“Excel function Excel 2016 函数基础教程 option tutolail”中截取“Excel 2016 函数基础教程”。双击 A2 单元格,把公式 =MID(A1,SEARCH("e*l",A1,5),SEARCH("程",A1)-SEARCH("e*l",A1,5)+1) 复制到 A2,按回车,返回“Excel 2016 函数基础教程”;操作过程步骤,如图10所示:

图片

图10

2、公式=MID(A1,SEARCH("e*l",A1,5),SEARCH("程",A1)-SEARCH("e*l",A1,5)+1)说明:

A、SEARCH("e*l",A1,5) 用于从 A1 中第 5 个字符开始查找以 e 开头且以 l 结尾的单词,即截取要查找字符串的开头单词 Excel,由于在此单词之前已经有一个 Excel,因此要从第 5 个字符开始以跳过第一个 Excel;SEARCH("e*l",A1,5) 的返回值为 16。

B、SEARCH("程",A1) 用于返回要截取字符串的最后一个字“程”字在 A1 中的位置,结果为 32;SEARCH("程",A1)-SEARCH("e*l",A1,5)+1 用于计算要截字符串的长度,代入值为 32 - 16 + 1 = 17,如果仅用 32 - 16,截得的字符串会少一个,所以要加 1。

C、则公式变为 =MID(A1,16,17),最后从 A1 中从第 16 个字符开始截取 17 个字符,即截得“Excel 2016 函数基础教程”。

提示:MidB + SearchB 也可以实现同样功能,公式可以这样写:=MIDB(A1,SEARCHB("e*l",A1,5),SEARCHB("程",A1)-SEARCHB("e*l",A1,5)+LENB("程")),由于每个汉字占两个字节,因此后面要加 2,即 LENB("程")。

(二)Count + Search函数组合实现统计一列中包含指定文本的单元格数

1、假如要统计 A 列中包含指定字符的单元格个数。双击 A11 单元格,把公式 =COUNT(SEARCH("*"&C2&"*",A$2:A$10)) 复制到 A11,按Ctrl + Shift + 回车,返回 4;操作过程步骤,如图11所示:

图片

图11

2、公式=COUNT(SEARCH("*"&C2&"*",A$2:A$10)) 说明:

A、"*"&C2&"*" 表示查找中间包含 C2 且两边为任意字符的文本,* 也可以表示空文本(即 "")。

B、A$2 是对列的相对引用对行的绝对引用,2 前的 $ 表示绝对引用,即往下拖时,A2 不会变 A3、A4 等;A$2:A$10 以数组形式返回 A2 至 A10 中的所有值。

C、SEARCH("*"&C2&"*",A$2:A$10) 执行时,"*"&C2&"*" 变为 "*衬衫*";接着,第一次从 A2 至 A10 中取出 A2,然后在 A2 中查找 "*衬衫*",由于找到了,因此返回 1;第二次取出 A3,没有找到 "*衬衫*",所以返回值错误 #VALUE!;其它的以此类推,最后返回数组 {1;#VALUE!;#VALUE!;1;#VALUE!;1;#VALUE!;#VALUE!;1}。

D、则公式变为 =COUNT({1;#VALUE!;#VALUE!;1;#VALUE!;1;#VALUE!;#VALUE!;1}),最后统计数组中的元素个数,由于 #VALUE! 不统计,所以统计结果为 4。

(三)Sum + IsNumber + Search函数组合实现一次查找多个值

1、假如要一次同时查找 4 个值。双击 A2 单元格,把公式 =SUM(ISNUMBER(SEARCH({"excel","函数",2016,2019},A1))*1) 复制到 A2,按回车,返回查找结果 3;操作过程步骤,如图12所示:

图片

图12

2、公式=SUM(ISNUMBER(SEARCH({"excel","函数",2016,2019},A1))*1) 说明:

A、SEARCH({"excel","函数",2016,2019},A1) 的找查找文本是一个包含 4 个元素的数组,即要在 A1 中查找 4 个值;执行时,第一次从数组中取第一个元素 excel,接着在 A1 中查找,返回结果为 1;第二次从数组中取第二个元素“函数”,也在 A1 中查找,结果为 27;其它的以此类推,最后返回 {1,27,22,#VALUE!},因在 A1 中找不到 2019,因此返回 #VALUE!。

B、则公式变为 =SUM(ISNUMBER({1,27,22,#VALUE!})*1),接着,用 IsNumber 检查数组中的元素是否为数字,如果是,返回 True,否则返回 False;则公式变为 =SUM({TRUE,TRUE,TRUE,FALSE}*1),进一步计算用数组中的每个元素与 1 相乘,相乘时,True 转为 1、False 转为 0,则公式变为 =SUM({1,1,1,0}),最后把数组中的每个元素相加,结果为 3。

(四)Index + Match + IsNumber + Search函数组合实现返回两列匹配的对应单元格

1、如果 A 列的“货号”中含有 B 列“批次”,则返回对应的“数量”。双击 D2 单元格,把公式 =INDEX(C$2:C$8,MATCH(1=1,ISNUMBER(SEARCH(B$2:B$8,A2)),)) 复制到 D2,按回车,返回 870;选中 D2,把鼠标移到 D2 右下角的单元格填充柄上,鼠标变为加号后,按住左键然后往下拖,则返回剩余的“数量”;操作过程步骤,如图13所示:

图片

图3

2、公式=INDEX(C$2:C$8,MATCH(1=1,ISNUMBER(SEARCH(B$2:B$8,A2)),)) 说明:

A、B$2:B$8 以数组形式返回 B2 至 B8 中的值,即 {"A";"B";"C";"D";"E";"F";"G"};则 SEARCH(B$2:B$8,A2) 变为 SEARCH({"A";"B";"C";"D";"E";"F";"G"},A2),接着从数组中取出每个元素,然后在 A2 中查找,如果找到返回 1,否则返回 #VALUE!;最后返回 {#VALUE!;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!}。

B、则 ISNUMBER(SEARCH(B$2:B$8,A2)) 变为 ISNUMBER({#VALUE!;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!}),进一步计算,用 IsNumber 判断数组的每个元素,如果是数字的返回 True,否则返回 False;最后返回 {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}。

C、1=1 用于返回 True,因为 IsNumber 返回的数组元素就是 True 和 False,这样便于判断。

D、则 MATCH(1=1,ISNUMBER(SEARCH(B$2:B$8,A2)),) 变为 MATCH(TRUE,{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE},),接着,在数组中查找 True,找到后返回它在数组中的位置,最后返回 4。

E、则公式变为 =INDEX(C$2:C$8,4),最后用Index函数返回 C2 至 C8 中第四行的值,即返回 870。

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报。