[第111期]三个数字的组合（开帖待总结）

ys19840718

1. =TEXT(RIGHT(LARGE(1*TEXT(SUBSTITUTE(TEXT(IF(MMULT(N(MMULT(IFERROR(1*TEXT(FIND(INT((COLUMN(A:AD)-1)/3),TEXT(SUBSTITUTE(MMULT((MOD(ROW(1:64)*2/2^COLUMN(A:F),2)>=1)*A2:F2*10^((6-COLUMN(A:F))*2),1^ROW(1:6)),"00",),"[<1e6];\0"),MOD(COLUMN(A:AD)-1,3)*2+1),"[<"&MOD(COLUMN(C:AF),3)*2+3&"]1;\0"),),N(INT(ROW(3:32)/3)=COLUMN(A:J)))=3),1^ROW(1:10)),MMULT(ROUND(MOD(SMALL(ROW(1:64)+MID(TEXT(SUBSTITUTE(MMULT((MOD(ROW(1:64)*2/2^COLUMN(A:F),2)>=1)*A2:F2*10^((6-COLUMN(A:F))*2),1^ROW(1:6)),"00",),"000000"),COLUMN(A:F),1)%,(ROW(1:64)-1)*6+COLUMN(A:F)),1)/1%,)*10^{5,4,3,2,1,0},1^ROW(1:6)))&MMULT(N(MMULT(IFERROR(1*TEXT(FIND(INT((COLUMN(A:AD)-1)/3),TEXT(SUBSTITUTE(MMULT((MOD(ROW(1:64)*2/2^COLUMN(A:F),2)>=1)*A2:F2*10^((6-COLUMN(A:F))*2),1^ROW(1:6)),"00",),"[<1e6];\0"),MOD(COLUMN(A:AD)-1,3)*2+1),"[<"&MOD(COLUMN(C:AF),3)*2+3&"]1;\0"),),N(INT(ROW(3:32)/3)=COLUMN(A:J)))=3)*(COLUMN(A:J)-1),1^ROW(1:10)),"[<>]10000000;;;\0"),REPT(COLUMN(A:J)-1,3),),"[<1e5];\0"),ROW(1:21)),4),"[>]000 0;")

复制代码

题目字面思路，971字符，实在太长了，而且用了iferror，07或以上版本才能用，里面有些小地方还能改下，减点字符，但是没什么实质性帮助，思路问题，250以内应该有数学规律吧，期待答案

cl63519

=TEXT(MOD(LARGE(--TEXT(MMULT(--MID(TEXT(MMULT(--RIGHT(SMALL(IFERROR((INT((ROW(1:400)-1)/20)+1)*100+MID(TEXT(FREQUENCY(ROW(1:20)*10+MID(N(OFFSET(A2,0,MID(SMALL(IF(MMULT(N(MID(ROW(123:456),{2,3},1)>MID(ROW(123:456),{1,2},1)),{1;1})+(MID(ROW(123:456),3,1)<="6")=3,ROW(123:456),""),ROW(1:20)),{1,1,2,2,3,3},1)-1)),{1,2,1,2,1,2},1)*1,INT((ROW(1:400)-1)/2)+10),"[=4]31;00"),{1,2},1)*10+MOD(INT((ROW(1:400)-1)/2),10),""),40*ROW(1:20)-4+COLUMN(A:D)),2),10^(8-2*ROW(1:4))),REPT("0",8)),{1,3,5,7,2,4,6,8},1),10^(8-ROW(1:8))),"[<11130000]!0;[>11200000]!0;0"),ROW(1:20)),10000),"000 0;;")

疑问：怎么是9个结果？也没有发现错误呀。

cl63519

cl63519 发表于 2015-2-26 10:57
=TEXT(MOD(LARGE(--TEXT(MMULT(--MID(TEXT(MMULT(--RIGHT(SMALL(IFERROR((INT((ROW(1:400)-1)/20)+1)*100+M ...

=IFERROR(TEXT(MOD(LARGE(--TEXT(MMULT(--MID(TEXT(MMULT(--RIGHT(SMALL(ROW(1:20)*100+MID(TEXT(SMALL(ROW(1:20)*10^8+LEFT(SUBSTITUTE(MMULT(MID(TEXT(MMULT(MOD(SMALL(INT(ROW(10:210)/10)*100+FREQUENCY(INT(IF(FREQUENCY(ROW(1:20)*100+MID(N(OFFSET(A2,0,MID(SMALL(IF(MMULT(N(MID(ROW(123:456),{2,3},1)>MID(ROW(123:456),{1,2},1)),{1;1})+(MID(ROW(123:456),3,1)<="6")=3,ROW(123:456),""),ROW(1:20)),{1,1,2,2,3,3},1)-1)),{1,2,1,2,1,2},1)*10+{1,1,2,2,3,3},INT((ROW(1:1200)-1)/6)*10+MOD(INT((ROW(1:1200)-1)/2),3)+101)>0,INT((ROW(1:1200)-1)/6)*10+MOD(INT((ROW(1:1200)-1)/2),3)+101,10^9)/10),ROW(10:209))*10+MOD(ROW(10:210),10),10*ROW(1:20)-4+COLUMN(A:D)),100),10^(8-2*ROW(1:4))),REPT("0",8)),{3,4,5,6,7,8,1,2},1)*1,10^(8-ROW(1:8))),3&(COLUMN(A:J)-1)&0,3&(COLUMN(A:J)-1)&1&(COLUMN(A:J)-1)),8)*1,ROW(1:20)*10),REPT("0",10)),{3,5,7,9},2),ROW(1:20)*4-4+COLUMN(A:D)),2),10^(8-2*ROW(1:4))),REPT("0",8)),{1,3,5,7,2,4,6,8},1),10^(8-ROW(1:8))),"[<11130000]!0;[>11200000]!0;0"),ROW(1:21)),10000),"000 0;;"),"")

swyrlbq

忙完春节，看到帖子未开，凑合一条公式。
B5:B25=TEXT(LARGE(MMULT(SMALL(ROW(1:640)/1%+(MMULT(1+-1^INT(ROW(1:640)*2/2^COLUMN(A:F)),ROW(1:6)^0)=6)*(1+-1^INT(ROW(1:640)*2/2^COLUMN(A:F)))*(1-ISERR(FIND(INT((ROW(1:640)-1)/64),A2:F2)))/2*(LEFT(A2:F2)+RIGHT(A2:F2)-INT((ROW(1:640)-1)/64)+1),ROW(1:640)*6-{2,1,0})-ROW(1:640)/1%,10^{3;2;1})+INT((ROW(1:640)-1)/64),ROW(1:21))-1110,"000 0;;")


为了改善运算速度，再整一条公式。
B5:B25=TEXT(LARGE(--(SUBSTITUTE((MMULT(ISNUMBER(FIND(ROW(1:10)-1,A2:F2))*9-8,1+-1^INT(COLUMN(A:BL)*2/2^ROW(1:6)))=6)*MMULT(10^(10-LEFT(A2:F2)-RIGHT(A2:F2)+ROW(1:10))*(LEFT(A2:F2)+RIGHT(A2:F2)-ROW(1:10)+1),1+-1^INT(COLUMN(A:BL)*2/2^ROW(1:6)))/2,0,)&ROW(1:10)-1),ROW(1:21)),"[<10] ;000 0")



简化公式，268字符。离250还是相差甚远。
B5:B25=TEXT(LARGE(--(SUBSTITUTE((MMULT(1-ISERR(FIND(ROW(1:10)-1,A2:F2))*9,1+-1^INT(COLUMN(A:BL)*2/2^ROW(1:6)))=6)*MMULT(10^(10-SUBSTITUTE(A2:F2,ROW(1:10)-1,,1))*SUBSTITUTE(A2:F2,ROW(1:10)-1,,1),1+-1^INT(COLUMN(A:BL)*2/2^ROW(1:6)))/2,0,)&ROW(1:10)-1),ROW()-4),"[<10] ;000 0")

delete_007

这个题看来也没有人再提交答案了，就此截止吧。
过两天我再评分总结。

delete_007

经过测试，根据题目规定的评分规则，评分结果如下：
如有疑问请与我联系。

Cellfarmer

有空对两个优秀公式做了计算步骤解析,以使初学者能看清作者的思路.

13732120571

206字符

1. =TEXT(LARGE((MMULT(1-ISERROR(SEARCH(TEXT(ROW(1:7899)-5&10,"0!*0!*0,"),0&123456789)*MATCH("*"&MID(ROW(10001:17899),{2,3,4},1)&RIGHT(ROW(1:7899))&"*",A2:F2&A2:F2,)),{1;1;1})=3)*ROW(1:7899),ROW()-4),"000 0;;")

复制代码

小石榴123

高人很多啊~~

alan57

1. =LET(_a,{1,2,3,4,5,6},_b,ROW($1:$600),_c,FILTER(_b,MAP(_b,LAMBDA(x,COUNT(FIND(_a,x))=3))),_d,UNIQUE(MAP(_c,LAMBDA(x,CONCAT(SORT(MID(x,ROW(1:3),1)))))),_e,DROP(REDUCE(0,_d,LAMBDA(x,y,VSTACK(x,TOROW(SORT(INDEX(A2:F2,,MID(y,ROW(1:3),1))))))),1),_f,ROW(1:10)-1,TOCOL(BYROW(_e,LAMBDA(s,LET(_g,MMULT(ISNUMBER(FIND(_f,s))*1,ROW(1:3)^0),_h,FILTER(_f,_g>=3),_i,MAP(s,LAMBDA(x,SUBSTITUTE(x,_h,"",1))),TEXTJOIN(" ",,CONCAT(_i),_h)))),3))

复制代码

长火车