[第58期]单元格倒取不重复数字[结题]

风冷

=SUM(MID(A1,SMALL(IF(MATCH(MID(A1,61-ROW($1:$60),1),MID(A1,61-ROW($1:$60),1),)=ROW($1:$60),61-ROW($1:$60)),ROW(INDIRECT("1:"&COUNT(FIND(ROW($1:$10)-1,A1))))),1)*10^ROW(INDIRECT("1:"&COUNT(FIND(ROW($1:$10)-1,A1)))))/10

先发一个

kkitt

B1=MID(SUM(RIGHT(LARGE(MOD(SMALL((MID(A1,ROW($1:$60),1)=(COLUMN(A:J)-1)&"")*ROW($1:$60)*10+(COLUMN(A:J)-1)*1001,ROW($1:$10)*60),1000),ROW($1:$10)))*10^-ROW($1:$10))&0,3,COUNT(FIND(ROW($1:$10)-1,A1)))

数组公式下拉

[ 本帖最后由 kkitt 于 2010-4-1 14:55 编辑 ]

wshcw

题目没看完整,吓我一跳"+++++++++++++++++"最长不超过60个字节,长度个个不等，即  LEN(A1)<60".
原以为公式长度要求<60字呢?

[ 本帖最后由 wshcw 于 2010-3-29 12:17 编辑 ]

wjh619993018

hyc2222

255字符
=RIGHT(0&SUM(RIGHT(LARGE((ISERR(1/(ROW($1:$60)-MATCH("*"&COLUMN(A:J)-1&"*",RIGHT(A1,ROW($1:$60)),)))*(ROW($1:$60)*10^9+FIND(COLUMN(A:J)-1,RIGHT(A1,ROW($1:$60))&5^19)/1%+COLUMN(J:S))),ROW($1:$10)))%*10^ROW($2:$11)),COUNT(SEARCH("*"&(ROW($1:$10)-1)&"*",A1)))
238字符
=RIGHT(0&SUM(MID(A1&REPT("0",61-LEN(A1)),LEN(A1)+1-SMALL((MATCH(LEFT(RIGHT(A1,ROW($1:$60))),LEFT(RIGHT(A1,ROW($1:$60))),)=((A1<>0)*ROW($1:$60)))*ROW($1:$60),ROW($1:$60)),1)*10^(61-ROW($1:$60)))/10,COUNT(SEARCH("*"&(ROW($1:$10)-1)&"*",A1)))
213字符
=LEFT(IF(RIGHT(A1)="0","0","")&SUM(MID(REPT(0,70-LEN(A1))&A1,71-SMALL(MATCH(COLUMN(A:J)-1,--LEFT(RIGHT("0123456789"&A1,ROW($1:$70))),),ROW($1:$10)),1)*10^(11-ROW($1:$10))),COUNT(SEARCH("*"&(ROW($1:$10)-1)&"*",A1)))

[ 本帖最后由 hyc2222 于 2010-4-22 23:27 编辑 ]

weifaxiang

=MID(SUM(MID(REPT(0,10)&A1,LARGE(FIND("&",SUBSTITUTE(1023456789&A1,ROW($1:$10)-1,"&",1+LEN(A1)-LEN(SUBSTITUTE(A1,{0;1;2;3;4;5;6;7;8;9},"")))),ROW($1:$10)),1)/10^ROW($1:$10))&REPT(0,10),3,COUNT(FIND(ROW($1:$10)-1,A1)))，数组公式

[ 本帖最后由 weifaxiang 于 2010-4-1 14:51 编辑 ]

lb_bn

老师们都很极积,咱也来参加一个.

[ 本帖最后由 lb_bn 于 2010-4-22 19:31 编辑 ]

moon2778

=LEFT(IF(RIGHT(A1)="0","0","")&SUM((RIGHT(SMALL(MATCH(ROW($1:$10)-1,--LEFT(RIGHT(1234567890&A1,ROW($1:$70))),0)*100+ROW($1:$10)-1,ROW($1:$10))))*10^(10-ROW($1:$10))),COUNT(FIND(ROW($1:$10)-1,A1)))

[ 本帖最后由 moon2778 于 2010-4-1 21:06 编辑 ]

donghan

B1输入以下字符：
1、{=LEFT(IF(RIGHT(A1)="0",0,)&SUM(LEFT(RIGHT(5^19&A1,LARGE(MATCH(COLUMN(A:J)-1,--MID(5^19&A1,LEN(A1)+15-COLUMN(1:1),1),),ROW($1:$10))))*10^ROW($1:$10)),COUNT(FIND(COLUMN(A:J)-1,A1)))}下拉，
2、{=MID(10^10+SUM(LEFT(RIGHT(REPT(0,14)&A1,SMALL(MATCH(COLUMN(A:J)-1,--MID(5^19&A1,LEN(A1)+15-COLUMN(1:1),1),),ROW($1:$10))))/10^ROW($1:$10))*10^10,2,COUNT(FIND(COLUMN(A:J)-1,A1)))}
3、{=MID(10^10+SUM(LEFT(RIGHT(REPT(0,14)&A1,SMALL(MATCH(COLUMN(A:J)-1,--MID(5^19&A1,LEN(A1)+15-COLUMN(1:1),1),),ROW($1:$10))))*10^(10-ROW($1:$10))),2,COUNT(FIND(COLUMN(A:J)-1,A1)))}
适应数值为0和空的情况
COLUMN(1:1)可以换成COLUMN(A:IU)

其它方法见附件

[ 本帖最后由 donghan 于 2010-4-12 17:37 编辑 ]

amulee

重在参与

=MID(SUM(MID(5^19&A1,74-SMALL(MATCH(ROW($1:$10)-1&"",MID(5^19&A1,74-ROW($1:$74),1),),ROW($1:$10)),1)/10^ROW($2:$11))&1%,4,COUNT(FIND(ROW($1:$10)-1,A1)))