恼人的部分匹配查找

excelperfect

这天有空，小范照常开始了Excel的研习。俗话说，一天不练，手生脚慢；两天不练，功夫减半；三天不练，成了门外汉。对于自己热爱的Excel，小范从不马虎。

他想研究前不久在chandoo.org网站上看到的一道题。小范习惯于浏览一些优秀的Excel站点，从中汲取营养，建议想要提高Excel技术水平的人养成这个习惯。

这道题是这样的，从一列数据中提取出正确的内容，如下图1所示。假设要剔除的单词或字符总位于末尾，并且数据不是按字母顺序排列的。

图1

确实有点头疼！如果只是查找“-”的位置，然后再提前该字符前的字符串，那很简单：

=IF(FIND('-',B3),LEFT(B3,FIND('-',B3)-1),B3)

然而，该列数据中不只是有字符“-”，还会有“(”和“/”字符，并且这些字符的位置都会随着字符串的变化而变化，每行数据中这些字符后面的单词字符串长度也不同。

这涉及到一次查找多个不同字符的问题。

只有3个字符，使用条件判断语句应该不会太长，经过尝试后，小范写下了公式：

=IFERROR(IFERROR(IFERROR(LEFT(B3,FIND('-',B3)-1),LEFT(B3,FIND('(',B3)-1)),LEFT(B3,FIND('/',B3)-1)),B3)

还不错，只是判断有点让人眼花缭乱。但是，在这种情形下，与IF函数相比，IFERROR函数已经太好了。

还有其它的方法么。

经过一番探索后，小范想到了利用数组。

先将每行数据暴力拆解，这通常使用MID/ROW函数组合来实现：

MID(B3,ROW($1:$100),1)

其中假设每行数据的字符不会超过100。这样，就得到了由构成该行数据的单个字符组成的数组。

然后将其与字符“-”、“(”、“/”进行比较：

MID(B3,ROW($1:$100),1)={'-','(','/'}

得到一个由TRUE/FALSE值组成的数组，其中的TRUE值就是存在字符“-”、“(”、“/”中的一个。

在前面加上双减号：

--(MID(B3,ROW($1:$100),1)={'-','(','/'})

将这个数组转换成1/0组成的数组，再与{1;1;1}相乘：

MMULT(--(MID(B3,ROW($1:$100),1)={'-','(','/'}),{1;1;1})

求数组中各行的和，转换成一个由0/1组成的单列数组。其中1的位置即字符“-”、“(”或“/”出现的位置。

然后使用MATCH函数查找1：

MATCH(1,MMULT(--(MID(B3,ROW($1:$100),1)={'-','(','/'}),{1;1;1}),0)

得到字符“-”、“(”或“/”出现的位置，然后使用LEFT函数提取出其左侧的字符串。

完整的公式如下：

=IF(ISNUMBER(MATCH(1,MMULT(--(MID(B3,ROW($1:$100),1)={'-','(','/'}),{1;1;1}),0)),LEFT(B3,MATCH(1,MMULT(--(MID(B3,ROW($1:$100),1)={'-','(','/'}),{1;1;1}),0)-1),B3)

这是一个数组公式，输入结束时要按Shift+Ctrl+Enter组合键。

终于解答出来了，小范长吁一口气。

再看看别人的答案吧。学习他人编写的公式，也是快速提升公式编写能力的一个途径。

于是，小范看到了这个公式：

=IF(COUNT(1/ISERR(SEARCH({'-','(','/'},B3)))=3,B3,D2)

牛！看来是我想多了。SEARCH/FIND函数就可以用来查找多个字符呀。这样，公式就变简单了。使用SEARCH/FIND函数在行数据中依次查找字符“-”、“(”和“/”，如果都没有找到，就会返回3个错误值，表明获取的数据就是该行数据本身，否则就取上一行已获取的数据。

简单！看来，提升无止境，还得继续练习。