比如有上万个电话号码，我要从中筛选出尾号为AAAA,AAA,AABB,AAAB,ABAB,AA.该如何实现？请尽量写详细些，谢谢！
ããææºå·ç çéä¸è¬çååä½ï¼ä¸è¾¹å举äºä¸ä¸ªä¾åï¼ä»¥ååä½ä¸ºåï¼åå«æåâAAAAâãâABBBâãâAABBâãâABABâåç±»ï¼
ããAAAAå ¬å¼ï¼=IFERROR(INDEX(A:A,SMALL(IF(MOD(RIGHT(A$2:A$1000,4),1111)=0,ROW($2:$1000),1=0),ROW(A1))),"")ï¼
ããABBBå ¬å¼ï¼=IFERROR(INDEX(A:A,SMALL(IF((MOD(RIGHT(A$2:A$1000,3),111)=0)*(MOD(RIGHT(A$2:A$1000,4),1111)>0),ROW($2:$1000),1=0),ROW(A1))),"")ï¼
ããAABBå ¬å¼ï¼=IFERROR(INDEX(A:A,SMALL(IF((MMULT(N(MOD(MID(A$2:A$1000,{8,10},2),11)=0),{1;1})=2)*(MOD(MID(A$2:A$1000,9,2),11)>0),ROW($2:$1000),1=0),ROW(A1))),"")ï¼
ããABABå ¬å¼ï¼=IFERROR(INDEX(A:A,SMALL(IF((MID(A$2:A$1000,8,2)=RIGHT(A$2:A$1000,2))*(MOD(MID(A$2:A$1000,9,2),11)>0),ROW($2:$1000),1=0),ROW(A1))),"")ã
ãã以ä¸å ¬å¼å为æ°ç»å ¬å¼ï¼éè¦ä¸é®ç»æã
加上AAAA, AAA, AABB, AAAB, ABAB, AA.共五个辅助列
并分别写上对应的公式:
AAAA,=IF(AND(MID(A2,LEN(A2)-3,1)=MID(A2,LEN(A2)-2,1),MID(A2,LEN(A2)-2,1)=MID(A2,LEN(A2)-1,1),MID(A2,LEN(A2)-1,1)=MID(A2,LEN(A2)-0,1)),"AAAA","")
AAA,=IF(AND(MID(A2,LEN(A2)-2,1)=MID(A2,LEN(A2)-1,1),MID(A2,LEN(A2)-1,1)=MID(A2,LEN(A2)-0,1)),"AAA","")
AABB,=IF(AND(MID(A2,LEN(A2)-3,1)=MID(A2,LEN(A2)-2,1),MID(A2,LEN(A2)-1,1)=MID(A2,LEN(A2)-0,1)),"AABB","")
AAAB,=IF(AND(MID(A2,LEN(A2)-3,1)=MID(A2,LEN(A2)-2,1),MID(A2,LEN(A2)-2,1)=MID(A2,LEN(A2)-1,1)),"AAAB","")
ABAB,=IF(AND(MID(A2,LEN(A2)-3,1)=MID(A2,LEN(A2)-1,1),MID(A2,LEN(A2)-2,1)=MID(A2,LEN(A2)-0,1)),"ABAB","")
AA.
=IF(AND(MID(A2,LEN(A2)-1,1)=MID(A2,LEN(A2)-0,1)),"AA","")
再对号码和这几个辅助列构成的表用自动筛选,就可以实现.对几个列组起来筛选就可以实现去除重复的选择(如AAAA 选空 AAA选非空 就不会出现2222等在222中)本回答被提问者和网友采纳
AAAA：
B1=IF(AND(MID(A1,8,1)=MID(A1,9,1),MID(A1,9,1)=MID(A1,10,1),MID(A1,10,1)=MID(A1,11,1)),"AAAA","")
AAA：
C1=IF(AND(MID(A1,8,1)<>MID(A1,9,1),MID(A1,9,1)=MID(A1,10,1),MID(A1,10,1)=MID(A1,11,1)),"AAA","")
AABB：
D1=IF(AND(MID(A1,8,1)=MID(A1,9,1),MID(A1,9,1)<>MID(A1,10,1),MID(A1,10,1)=MID(A1,11,1)),"AABB","")
AAAB：
E1=IF(AND(MID(A1,8,1)=MID(A1,9,1),MID(A1,9,1)=MID(A1,10,1),MID(A1,10,1)<>MID(A1,11,1)),"AAAB","")
ABAB：
F1=IF(AND(MID(A1,8,1)<>MID(A1,9,1),MID(A1,8,1)=MID(A1,10,1),MID(A1,9,1)=MID(A1,11,1)),"ABAB","")
AA：
G1=IF(AND(MID(A1,8,1)<>MID(A1,9,1),MID(A1,9,1)<>MID(A1,10,1),MID(A1,10,1)=MID(A1,11,1)),"AA","")追问
谢谢！请问ABC的该如何筛选？
追答=IF(AND(MID(A1,8,1)MID(A1,9,1),MID(A1,9,1)MID(A1,10,1),MID(A1,10,1)MID(A1,11,1)),"ABC","")
追问如果ABC是123那用这个公式只能选出123的号码是吧？我说的AAAA.ABC只是个例子 目的是想选出1111.666、234.5566此类吉祥号，请问能实现吗？求解答！谢谢啦！追分哦！
追答一、1234样：
=IF(AND(SUM(VALUE(MID(A1,8,1)),1)=VALUE(MID(A1,9,1)),SUM(VALUE(MID(A1,9,1)),1)=VALUE(MID(A1,10,1)),SUM(VALUE(MID(A1,10,1)),1)=VALUE(MID(A1,11,1))),"四升顺","")
二、4321样：
=IF(AND(VALUE(MID(A1,8,1))=SUM(VALUE(MID(A1,9,1)),1),VALUE(MID(A1,9,1))=SUM(VALUE(MID(A1,10,1)),1),VALUE(MID(A1,10,1))=SUM(VALUE(MID(A1,11,1)),1)),"四降顺","")
三、9123样：
=IF(AND(SUM(VALUE(MID(A1,8,1)),1)VALUE(MID(A1,9,1)),SUM(VALUE(MID(A1,9,1)),1)=VALUE(MID(A1,10,1)),SUM(VALUE(MID(A1,10,1)),1)=VALUE(MID(A1,11,1))),"后三升顺","")
四、1987样：
=IF(AND(VALUE(MID(A1,8,1))SUM(VALUE(MID(A1,9,1)),1),VALUE(MID(A1,9,1))=SUM(VALUE(MID(A1,10,1)),1),VALUE(MID(A1,10,1))=SUM(VALUE(MID(A1,11,1)),1)),"后三降顺","")
五、7890样：
=IF(AND(SUM(VALUE(MID(A5,8,1)),1)=VALUE(MID(A5,9,1)),SUM(VALUE(MID(A5,9,1)),1)=VALUE(MID(A5,10,1)),SUM(VALUE(MID(A5,10,1)),1)(MID(A5,11,1))),"前三升顺","")
六、9870样：
=IF(AND(VALUE(MID(A6,8,1))=SUM(VALUE(MID(A6,9,1)),1),VALUE(MID(A6,9,1))=SUM(VALUE(MID(A6,10,1)),1),VALUE(MID(A6,10,1))SUM(VALUE(MID(A6,11,1)),1)),"前三降顺","")
说明：if最多可以用七层，加上前面的五种，没有办法一次性全部用。只有分列输入了。
所建议你增加一附属列，这列里输入以下公式（设数据为A列，附加列为B列）：
=IF(OR(AND(INT(RIGHT(A1,4)/1000)=VALUE(RIGHT(A1,1)),INT(RIGHT(A1,3)/100)=VALUE(RIGHT(A1,1)),INT(RIGHT(A1,2)/10)=VALUE(RIGHT(A1,1))),AND(INT(RIGHT(A1,4)/1000)=INT(RIGHT(A1,2)/10),INT(RIGHT(A1,3)/100)=INT(RIGHT(A1,2)/10)),AND(INT(RIGHT(A1,4)/1000)=INT(RIGHT(A1,3)/100),INT(RIGHT(A1,2)/10)=VALUE(RIGHT(A1,1))),AND(INT(RIGHT(A1,4)/1000)=INT(RIGHT(A1,2)/10),INT(RIGHT(A1,3)/100)=VALUE(RIGHT(A1,1))),INT(RIGHT(A1,4)/1000)=INT(RIGHT(A1,3)/100)),1,"")
公式看起来很复杂。公式里，right(a1,4)就是取a1的后四位，而int(right(a1,4)/100)是取后四位数上的百位数字，然后分别比较。比较的条件分别是：AAAA、AAAB、AABB、ABAB、AA。自己慢慢看吧。公式是太长了点，看是否能折腾一个数组公式出来，你先凑合着吧追问
谢谢！请问ABC的该如何筛选？
