判断单元格内数字是否重复与保留非重复的数字的相关公式

判断单元格内数字是否重复的公式

1 =IF(DEVSQ(-MID(A1,ROW($1:$3),1))=2,,”不”)&”连续”

2 =IF(AVEDEV(-MID(A1,ROW($1:$3),1))=2/3,,”不”)&”连续”

3 =IF(DEVSQ(-MID(F1,ROW($1:$4),1))=5,,”不”)&”连续”

4 =ISNUMBER(FIND(“111”,SUM(10^MID(A1,ROW($1:$3),1))))

保留非重复的数字相关数组公式

1 {=RIGHT(TEXT(SUM(MID(A5,SMALL(–TEXT(FIND(ROW($1:$10)-1,A5&”0123456789″),”[<=”&LEN(A5)&”]#;1″),ROW($1:$10)),1)*10^(10-ROW($1:$10))),REPT(“0”,10)),COUNT(FIND(ROW($1:$10)-1,A5)))}

2{=RIGHT(0&SUM(MID(A5,SMALL(–TEXT(FIND(ROW($1:$10)-1,A5&1023456789),”[<=”&LEN(A5)&”]#;1″),ROW($1:$10)),1)*10^(10-ROW($1:$10))),(LEFT(A5)=0)+COUNT(FIND(ROW($1:$10)-1,A5)))}

3{=SUBSTITUTE(SUM(RIGHT(0&MID(A5,SMALL(FIND(ROW($1:$10)-1,A5&1023456789),ROW($1:$10)),1))*10^(1-ROW($1:$10))),”.”,””)}