维格尔无畏舰:函数提取不重复的文件后缀名
来源:百度文库 编辑:偶看新闻 时间:2024/04/28 12:20:34
问题1:纯粹提取后缀结果公式(数组)
="*"&MID(A2,LARGE((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)=".")*ROW(INDIRECT("1:"&LEN(A2))),1),100)
************************************************************************************************
问题2:提取A2:A8区域内不重复的文件后缀名
欢乐小爪(461699029)
符合题意:(数组)=INDEX(SUBSTITUTE(RIGHT($A$2:$A$9,5-FIND(".",RIGHT("."&$A$2:$A$9,4))),".","*."),SMALL(IF(MATCH(RIGHT($A$2:$A$8,3),RIGHT($A$2:$A$8,3),)=ROW($1:$7),ROW($1:$7),8),ROW()-1))
看B9的错误
-*-*---*-*-*-*-*-*--*-*-*-*-*-*-*-*-*-
下面无妄之地(632389740)公式
悄然的解决了这个错误
=INDEX(SUBSTITUTE(RIGHT($A$2:$A$9,5-FIND(".",RIGHT("."&$A$2:$A$9,4))),".","*."),SMALL(IF(MATCH(RIGHT($A$2:$A$9,3),RIGHT($A$2:$A$9,3),)=ROW($1:$8),ROW($1:$8),8),MIN(8,ROW(A1))))
区别在于考虑的深浅(知识点)
ROW()-1 vs MIN(8,ROW(A1))