请教高手,如何通过Excel筛选
用IF函数解决这个问题:在A2栏输入手机号,在C2栏输入该公式=IF(AND(MID(A2,8,1)=MID(A2,9,1),MID(A2,10,1)=MID(A2,11,1)),"AABB",IF(MID(A2,8,3)="000","000A",IF(RIGHT(A2,1)=MID(A2,10,1),"AA",IF(AND(MID(A2,8,1)=MID(A2,10,1),MID(A2,9,1)=MID(A2,11,1)),"ABAB",IF(AND((MID(A2,10,1)-MID(A2,9,1))=1,MID(A2,11,1)-MID(A2,10,1)=1),"ABC",IF(A...全部
用IF函数解决这个问题:在A2栏输入手机号,在C2栏输入该公式=IF(AND(MID(A2,8,1)=MID(A2,9,1),MID(A2,10,1)=MID(A2,11,1)),"AABB",IF(MID(A2,8,3)="000","000A",IF(RIGHT(A2,1)=MID(A2,10,1),"AA",IF(AND(MID(A2,8,1)=MID(A2,10,1),MID(A2,9,1)=MID(A2,11,1)),"ABAB",IF(AND((MID(A2,10,1)-MID(A2,9,1))=1,MID(A2,11,1)-MID(A2,10,1)=1),"ABC",IF(AND(MID(A2,8,1)=MID(A2,11,1),MID(A2,9,1)=MID(A2,10,1)),"ABBA",""))))))
详见附档举例
补充尾数为8的也是靓号的公式=IF(AND(MID(A2,8,1)=MID(A2,9,1),MID(A2,10,1)=MID(A2,11,1)),"AABB",IF(MID(A2,8,3)="000","000A",IF(RIGHT(A2,1)=MID(A2,10,1),"AA",IF(AND(MID(A2,8,1)=MID(A2,10,1),MID(A2,9,1)=MID(A2,11,1)),"ABAB",IF(AND((MID(A2,10,1)-MID(A2,9,1))=1,MID(A2,11,1)-MID(A2,10,1)=1),"ABC",IF(AND(MID(A2,8,1)=MID(A2,11,1),MID(A2,9,1)=MID(A2,10,1)),"ABBA",IF(RIGHT(A2,1)="8","8","")))))))。
收起