【在线等】筛选出含6、8、9的尾数是ABAB、ABBA和不含689的尾数是ABAB、ABBA的号

zsshot

本来的目的有二：一是“如果号码尾数是ABAB,同时后4位含6或8或9，则返回‘含689的ABAB’，否则，直接返回‘ABAB’”
                二是“如果号码尾数是ABBA,同时后4位含6或8或9，则返回‘含689的ABBA’，否则，直接返回‘ABBA’”
但是没能用两个IF实现，用了N多IF嵌套语句实现了，但是只有2007格式支持，2003不支持，想求各位高人再想想办法优化下，让2003也能支持了，谢谢！
函数如下：
=IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,3)),LEFT(RIGHT(A1,2))=LEFT(RIGHT(A1,4)),RIGHT(A1)="6"),"A6A6",
IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,3)),LEFT(RIGHT(A1,2))=LEFT(RIGHT(A1,4)),LEFT(RIGHT(A1,2))="6"),"6A6A",
IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,3)),LEFT(RIGHT(A1,2))=LEFT(RIGHT(A1,4)),RIGHT(A1)="8"),"A8A8",
IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,3)),LEFT(RIGHT(A1,2))=LEFT(RIGHT(A1,4)),LEFT(RIGHT(A1,2))="8"),"8A8A",
IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,3)),LEFT(RIGHT(A1,2))=LEFT(RIGHT(A1,4)),RIGHT(A1)="9"),"A9A9",
IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,3)),LEFT(RIGHT(A1,2))=LEFT(RIGHT(A1,4)),LEFT(RIGHT(A1,2))="9"),"9A9A",
IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,3)),LEFT(RIGHT(A1,2))=LEFT(RIGHT(A1,4))),"ABAB","")
))))))
&IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,4)),LEFT(RIGHT(A1,3))=LEFT(RIGHT(A1,2)),LEFT(RIGHT(A1,2))="6"),"A66A",
IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,4)),LEFT(RIGHT(A1,3))=LEFT(RIGHT(A1,2)),RIGHT(A1)="6"),"6AA6",
IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,4)),LEFT(RIGHT(A1,3))=LEFT(RIGHT(A1,2)),LEFT(RIGHT(A1,2))="8"),"A88A",
IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,4)),LEFT(RIGHT(A1,3))=LEFT(RIGHT(A1,2)),RIGHT(A1)="8"),"8AA8",
IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,4)),LEFT(RIGHT(A1,3))=LEFT(RIGHT(A1,2)),LEFT(RIGHT(A1,2))="9"),"A99A",
IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,4)),LEFT(RIGHT(A1,3))=LEFT(RIGHT(A1,2)),RIGHT(A1)="9"),"9AA9",
IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,4)),LEFT(RIGHT(A1,3))=LEFT(RIGHT(A1,2))),"ABBA","")
))))))

[ 本帖最后由 zsshot 于 2010-9-29 11:02 编辑 ]

zsshot

高人帮帮忙吧！

endend1980

=IF(OR(OR(MID(RIGHT(A1,4),ROW($1:$4),1)*1=6),OR(MID(RIGHT(A1,4),ROW($1:$4),1)*1=8),OR(MID(RIGHT(A1,4),ROW($1:$4),1)*1=9)),SUBSTITUTE(RIGHT(A1,4),1,"A"),RIGHT(A1,4))  数组公式下拉

草民1982111

D2=IF(SUM(N(ISNUMBER(FIND({6,8,9},RIGHT(A1,4)))))<1,SUBSTITUTE(SUBSTITUTE(RIGHT(A1,4),1,"A"),LOOKUP(1,N(ISNUMBER(FIND({2,3,4,5,7},SUBSTITUTE(RIGHT(A1,4),1,"A")))),{2,3,4,5,7}),"B"),SUBSTITUTE(RIGHT(A1,4),1,"A"))数组,下拉

[ 本帖最后由 草民1982111 于 2010-9-29 10:31 编辑 ]

栀子香

没看懂。。。

zsshot

非常感谢！

xuexi2xuexi

看对否，只是把公式简化了一下
=IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,3)),LEFT(RIGHT(A1,2))=LEFT(RIGHT(A1,4))),
IF(OR(RIGHT(A1)="6",RIGHT(A1)="8",RIGHT(A1)="9"),"A"&RIGHT(A1)&"A"&RIGHT(A1),
IF(OR(LEFT(RIGHT(A1,2))="6",LEFT(RIGHT(A1,2))="8",LEFT(RIGHT(A1,2))="9"),LEFT(RIGHT(A1,2))&"A"&LEFT(RIGHT(A1,2))&"A","ABAB")),"")
&IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,4)),LEFT(RIGHT(A1,2))=LEFT(RIGHT(A1,3))),
IF(OR(RIGHT(A1)="6",RIGHT(A1)="8",RIGHT(A1)="9"),RIGHT(A1)&"AA"&RIGHT(A1),
IF(OR(LEFT(RIGHT(A1,2))="6",LEFT(RIGHT(A1,2))="8",LEFT(RIGHT(A1,2))="9"),"A"&LEFT(RIGHT(A1,2))&LEFT(RIGHT(A1,2))&"A","ABBA")),"")

草民1982111

原帖由 xuexi2xuexi 于 2010-9-29 11:04 发表
看对否，只是把公式简化了一下
=IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,3)),LEFT(RIGHT(A1,2))=LEFT(RIGHT(A1,4))),
IF(OR(RIGHT(A1)="6",RIGHT(A1)="8",RIGHT(A1)="9"),"A"&RIGHT(A1)&"A"&RIGHT(A1),
IF(OR(LEFT(RIGHT ...

公式牛人,共同期待高手简化公式.

zsshot

原帖由 xuexi2xuexi 于 2010-9-29 11:04 发表
看对否，只是把公式简化了一下
=IF(AND(RIGHT(A1)=LEFT(RIGHT(A1,3)),LEFT(RIGHT(A1,2))=LEFT(RIGHT(A1,4))),
IF(OR(RIGHT(A1)="6",RIGHT(A1)="8",RIGHT(A1)="9"),"A"&RIGHT(A1)&"A"&RIGHT(A1),
IF(OR(LEFT(RIGHT ...

高人！