Excel公式技巧：查找函数示例研究

excelperfect

假设单元格A1中有一个字符串：

askananswer

 

如果我们使用公式：

=FIND('a',A1)

会返回：1，即字符“a”在字符串中开始出现的位置。

 

我们可以指定被查找的字符串（示例中为A1中的字符串）起始位置，例如2，则公式：

=FIND('a',A1,2)

会返回：4，即在字符串的第2个位置开始查找字符“a”，发现的其从第2个位置开始首次出现的位置是4。

 

如果我们要使用一个公式，获得字符“a”在字符串中出现的所有位置，那该怎么办呢？

 

可以使用数组公式：

=FIND('a',A1,ROW(INDIRECT('1:'&LEN(A1))))

或者：

=FIND('a',A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1)))

得到由包含字符“a”在字符串中出现的位置组成的数组：

{1;4;4;4;6;6;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

 

我们看到，得到的数组中有重复值，也有错误值，这是由查找函数多次查找得到的结果。接下来，我们想要得到仅由字符“a”在字符串中出现的位置组成的数组：

{1;4;6}

 

我们知道，数组公式中：

ROW(INDIRECT('1:'&LEN(A1)))

将会得到一个连续数值组成的数组，示例中单元格A1中的字符串长度为11，因此得到的数组为：

{1;2;3;4;5;6;7;8;9;10;11}

 

将上面的数组与FIND函数产生的结果进行比较：

{1;4;4;4;6;6;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}={1;2;3;4;5;6;7;8;9;10;11}

得到由布尔值组成的数组：

{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

我们可以看到，数组中TRUE值对应的正好是字符“a”在字符串中的位置。使用IF函数取出这些位置值：

IF({TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},{1;2;3;4;5;6;7;8;9;10;11})

得到：

{1;FALSE;FALSE;4;FALSE;6;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

 

因为SMALL函数不能处理错误值，因此使用IFERROR函数去除错误值：

IFERROR({1;FALSE;FALSE;4;FALSE;6;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},””)

得到：

{1;FALSE;FALSE;4;FALSE;6;'';'';'';'';''}

 

再使用SMALL函数得到上面数组中的数值：

SMALL({1;FALSE;FALSE;4;FALSE;6;'';'';'';'';''},{1;2;3;4;5;6;7;8;9;10;11})

得到：

{1;4;6;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}

 

再使用IFERROR函数去掉错误值：

IFERROR({1;4;6;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!},””)

得到：

{1;4;6;'';'';'';'';'';'';'';''}

 

综上，完整的数组公式为：

=IFERROR(SMALL(IFERROR(IF(FIND('a',A1,ROW(INDIRECT('1:'&LEN(A1))))=ROW(INDIRECT('1:'&LEN(A1))),ROW(INDIRECT('1:'&LEN(A1)))),''),ROW(INDIRECT('1:'&LEN(A1)))),'')

 

实际上，我们可以使用：

LEN(A1)-LEN(SUBSTITUTE(A1,'a',''))

计算出字符串要查找的字符“a”的数量：

3

从而：

ROW(INDIRECT('1:'&LEN(A1)-LEN(SUBSTITUTE(A1,'a',''))))

得到：

{1;2;3}

 

这样，上述数组公式可以修改为：

=SMALL(IFERROR(IF(FIND('a',A1,ROW(INDIRECT('1:'&LEN(A1))))=ROW(INDIRECT('1:'&LEN(A1))),ROW(INDIRECT('1:'&LEN(A1)))),''),ROW(INDIRECT('1:'&LEN(A1)-LEN(SUBSTITUTE(A1,'a','')))))

得到数组：

{1;4;6}