天给同学们出一个函数题目,高手信手拈来,但对于函数基础薄弱的新手来说,还是有些难度的。
如下图所示。A列是由“-”分隔的字符串,要求在B列用公式返回最后一个“-”的位置。
题目产生原因,说起来还真有些好笑。兰色正在发愁今天的微信写什么好,突然看到某大网站一篇关于娱乐圈小三的新闻。不知怎么的突然灵光一闪,想到了FIND 函数第3个参数。本来想直接写篇教程介绍一下,可觉得这样大家印象不深,于是就出了一个小题目,看看大家是否有关注FIND的“小三”。
说了这么多,FIND的小三是什么呢?这个需要先交待一下。
=FIND(查找字符,在哪个字符串中查找 ,查找的开始位置)
例 FIND("-","A-DFD-FE") 返回的是第一个 - 的位置2
FIND("-","A-DFD-FE",3) 从第3个字符向后查找,返回的是第2个-的位置6.
从几百个同学的答案看,都是采用了传统的解题思路。公式相对都较复杂一些,如果你用了FIND的第3个参数,公式瞬间大大简化。
公式1:=LOOKUP(99,FIND("-",A3,ROW($1:$99))) 不需三键输入
公式2:=COUNT(FIND("-",A4,ROW($1:$99))) 需要按ctrl+shift+enter三键输入
公式3:=COUNT(FIND("-",A4,ROW(A:A))) 需要三键输入
以上三个公式,其实是一个思路,即在FIND函数第三个参数上做文章。
无论是ROW($1:$99)还是ROW(A:A)都是为了生成{1;2;3;4。。。。}一组序数。作为FIND的第三个参数 FIND("-",A3,ROW($1:$99))),可以实现分别从A3字符串(ABC-FE-35-45)的第1,2,3,4,5,6...开始向后查找"-"。查找的结果是
4;4;4;4;7;7;7;10;10;10;#VALUE!;#VALUE!;#VALUE!.....从第10起后面全是错误值。
本题要求的结果是 最后一个"-"的位置,而上面的结果中,最后一个数字10恰好是答案。怎么把它取出来呢。
LOOKUP函数可以实现至下而上模糊查找,并且可以忽略错误值,所以公式就可以写为:
LOOKUP(99,FIND(...)) 其中99是一个对查找结果足够大的数。如果字符串可能会很长,也可以用9^9替代99.
除了用LOOKUP提取最后一个数,我们也发现
4;4;4;4;7;7;7;10;10;10;#VALUE!;#VALUE!;#VALUE!.....中的数字个数也是10,也是我们需要的结果。而COUNT函数可以忽略错误值统计数字个数,即公式得到进一步简化。
=COUNT(find())
而ROW(A:A)可以取得从1到A列最后一行行数的一个超大序列。如果纯为了简化公式,也可以用它替换ROW($1:99),但兰色不建议这样做,因为这么大的计算量太耗费计算机内存了。
至此为此,昨天题目的答案已解开,不知道你懂了没有。这个题目,目前为止只有一个excel精英论坛的版主想到了最简答案。所以。。你们学会了就成第2个。嘿嘿!
从这个题目中,也提醒我们要关注一下其他函数的“小三”和"小四"。EXCEL即然让他们存在,必然有他们的用处。
联系客服