请教，excel判断五位数的后三位，有三个相等返回1，两个相等返回2，否则返回3

Question

=IF(OR(AND(MID(B2,3,1)=MID(B2,4,1),AND(MID(B2,4,1)=MID(B2,5,1))),"1""2"),"3")

Answer 1

=3-SUM(MID(B2,3,1)=MID(B2,4,1),MID(B2,4,1)=MID(B2,5,1),MID(B2,3,1)=MID(B2,5,1))+PRODUCT(MID(B2,3,1)=MID(B2,4,1),MID(B2,4,1)=MID(B2,5,1),MID(B2,3,1)=MID(B2,5,1))追问

你这个我试过，可以实现，谢谢了，如果我想把123换成文字应该怎么做呢？

追答

这个本来是按数字来的，文字的话，可以IF条件来做，偷个懒，直接在原公式上改了：
=CHOOSE(3-SUM(MID(B2,3,1)=MID(B2,4,1),MID(B2,4,1)=MID(B2,5,1),MID(B2,3,1)=MID(B2,5,1))+PRODUCT(MID(B2,3,1)=MID(B2,4,1),MID(B2,4,1)=MID(B2,5,1),MID(B2,3,1)=MID(B2,5,1)),"三同","二同","零同")；

简单点，可以：
=CHOOSE(MIN(SUM(MID(B2,3,1)=MID(B2,4,1),MID(B2,4,1)=MID(B2,5,1),MID(B2,3,1)=MID(B2,5,1))+1,3),"零同","二同","三同")

Answer 2

=LOOKUP(MAX(SUMPRODUCT((MID(A1,ROW(3:5),1)=MID(A1,3,1))*1),SUMPRODUCT((MID(A1,ROW(3:5),1)=MID(A1,4,1))*1),SUMPRODUCT((MID(A1,ROW(3:5),1)=MID(A1,5,1))*1)),{1,2,3},{3,2,1})

Answer 3

公式错了
把数字例子发上来。追问

12345 判断后三位，不相等返回不相等，全部相等返回全等，两位相等返回二相等

追答

=IF(AND(MID(A13,3,1)=MID(A13,4,1),MID(A13,3,1)=MID(A13,5,1)),3,IF(OR(MID(A13,3,1)=MID(A13,4,1),MID(A13,3,1)=MID(A13,5,1),MID(A13,4,1)=MID(A13,5,1)),2,0))
用这个最简单，答案应该是3,2,0

Answer 4

=IF(MAX(FREQUENCY(MIDB(B1,ROW($1:$5),1)+0,ROW($1:$10)-1))>=3,"1",IF(MAX(FREQUENCY(MIDB(B1,ROW($1:$5),1)+0,ROW($1:$10)-1))>=2,"2","3"))