[第97期]按倒序不重复取出数字排列(三)[已评分]

fangjianp · 发表于 2013-8-23 10:23

本帖最后由 delete_007 于 2013-10-11 14:22 编辑

题目说明：
1.A1:A14为数值型字符和文本格式的纯数字字母字符串.
2.字符串字符个数最多会有100个.
3.A列数据不会全是空,至少会有一个非零数字.
4.求出A1:A14中出现的数字,按出现倒序不重复排列.

答题要求：
1.不修改原数据，不使用辅助列,定义名称,VBA等.
2.在D1输入公式得出结果.
3.除输入D1公式外不得进行任何形式的其他操作.
4.修改公式里的参数可以适应200行以上的数据.
5.在数据变动后,结果仍须正确.
6.2003版excel能通过

评分：
1.结果正确,公式长度(含=)小于440个字符,得1分.
2.公式长度(含=)小于280个字符,得2分.
3.公式长度(含=)小于230个字符,得3分.

截至日期:2013年09月25日

再发现随意占据楼层,将取消其参赛资格,并扣除财富分50分!!!

delete_007 · 发表于 2013-8-23 14:00

本帖最后由 delete_007 于 2013-9-25 11:45 编辑

这个题真费神，先交个237的答案。

=MID(SUM(RIGHT(SMALL(MATCH(ROW(1:10)-1&"*",RIGHT(IF(ROW(1:2800)>2600,5^19)&LOOKUP(15-ROW(1:2800)%/2,ROW(1:14),A1:A14&""),MOD(ROW(200:2999),200)),)/1%+ROW(1:10)-1,ROW(1:10)))/10^ROW(1:10))&0,3,COUNT(MATCH("*"&ROW(1:10)-1&"*",A1:A14&"",)))

复制代码

206字符，这个公式不具有扩展性。

=MID(SUM(RIGHT(SMALL(MATCH(ROW(1:10)-1&"*",RIGHT(5^19&A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14,ROW(1:1500)),)/1%+ROW(1:10)-1,ROW(1:10)))/10^ROW(1:10))&0,3,COUNT(MATCH("*"&ROW(1:10)-1&"*",A1:A14&"",)))

复制代码

243字符，这个公式有待优化。

=IF(LOOKUP(1,-RIGHT(A1:A14)),"",0)&SUM((0&MID(SMALL(--TEXT(LOOKUP(ROW(2:11),SMALL((0&MID(A1:A14,COLUMN(A:CV),1))*1.000001+(COLUMN(A:CV)<=LEN(A1:A14))+ROW(1:14)%+COLUMN(A:CV)/10^5,ROW(1:1400)))-ROW(1:10),"[>];1"),ROW(1:10)),8,1))*10^ROW(2:11))%

复制代码

218字符，这个是通用公式。

=IF(LOOKUP(1,-RIGHT(A1:A14)),"",0)&SUM((0&MID(SMALL(--TEXT(LOOKUP(ROW(11:20),SMALL((1&MID(A1:A14,COLUMN(A:CV),1))*1.0000001+ROW(1:14)%+COLUMN(A:CV)/10^5,ROW(1:1400)))-ROW(10:19),"[>];1"),ROW(1:10)),9,1))*10^ROW(2:11))%

复制代码

进一步优化，208字符。（针对A1:A14均有100个字符，且无0出现的情况，用A15单元格来容错）

=IF(LOOKUP(1,-RIGHT(A1:A14)),,0)&SUM(RIGHT(SMALL(TEXT(LOOKUP(ROW(11:20),SMALL((1&MID(A1:A15,COLUMN(A:CV),1))*1.000001+ROW(1:15)%+COLUMN(A:CV)%%,ROW(1:1500)))-ROW(10:19),"[>];1")*1e6,ROW(1:10)))*10^ROW(2:11))%

复制代码

第一、二公式是一种思路，三、四、五是另一种思路。适应200行数据的公式，216字符。

=IF(LOOKUP(1,-RIGHT(A1:A200)),,0)&SUM(RIGHT(SMALL(TEXT(LOOKUP(ROW(11:20),SMALL((1&MID(A1:A201,COLUMN(A:CV),1))*1.00000001+ROW(1:201)%%+COLUMN(A:CV)%%%,ROW(1:20100)))-ROW(10:19),"[>];1")*1e8,ROW(1:10)))*10^ROW(2:11))%

复制代码

笑着...两年过去 · 发表于 2013-8-24 14:50

本帖最后由笑着...两年过去于 2013-8-25 20:08 编辑

先冒个泡泡，有时间再修改。不知道03的能不能过

=LEFT(TEXT(SUM(LOOKUP(LARGE(MATCH(ROW(1:10)-1,--RIGHT(LARGE(IF(MID(A1:A14,COLUMN(A:CV),1)="",COLUMN(A:CV),MID(A1:A14,COLUMN(A:CV),1)+ROW(1:14)*10^4+COLUMN(A:CV)*100),ROW(1:500))),),ROW(1:10)),ROW(1:500),RIGHT(LARGE(IF(MID(A1:A14,COLUMN(A:CV),1)="",COLUMN(A:CV),MID(A1:A14,COLUMN(A:CV),1)+ROW(1:14)*10^4+COLUMN(A:CV)*100),ROW(1:500))))*10^ROW(1:10)),REPT(0,11)),COUNT(0/MMULT(COLUMN(A:N)^0,IF(ISERR(FIND(COLUMN(A:J)-1,A1:A14)),,1))))

复制代码

ykqrs · 发表于 2013-8-24 17:20

本帖最后由 ykqrs 于 2013-9-7 09:45 编辑

看到这个帖子，想起以前学习过头版一个类似的帖子，对比了一下，难在这是区域，由于不全是文本，phonetic行不通，苦苦思索一天，超套！先留个记号吧，这个不算，与要求4不符

=MID(SUM(LEFT(RIGHT(A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14,SMALL(MATCH(COLUMN(A:J)-1&"*",RIGHT(1/17&A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14,ROW($1:$1500)),),COLUMN(A:J))))/10^COLUMN(A:J))&0,3,COUNT(FIND(COLUMN(A:J)-1,A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14)))

复制代码

下面这个是要把数据源全部改成文本格式，和要求3不符，也不算

=MID(SUM(LEFT(RIGHT(PHONETIC(A1:A14),SMALL(MATCH(COLUMN(A:J)-1&"*",RIGHT(1/17&PHONETIC(A1:A14),ROW($1:$1500)),),COLUMN(A:J))))/10^COLUMN(A:J))&0,3,COUNT(FIND(COLUMN(A:J)-1,PHONETIC(A1:A14))))

复制代码

数字文本格式混合的确很麻烦，07通过，

=MID(SUM(RIGHT(SMALL(1*IFERROR(MATCH(ROW(1:10)-1&"*",RIGHT(IF(N(OFFSET(A15,-(INT(ROW(100:1499) /100)),)),N(OFFSET(A15,-(INT(ROW(100:1499)/100)),)),T(OFFSET(A15,-(INT(ROW(100:1499)/100)),))),MOD(ROW(1:1400)-1,100)+1),)&ROW(1:10)-1,10^5),ROW(1:10)))/10^ROW(1:10))&0,3,COUNT(0/(MMULT(COLUMN(A:N)^0,1-ISERR(FIND(COLUMN(A:J)-1,A1:A14))))))

复制代码

很郁闷，03依旧通不过，match出来的错误值不知怎么变成一个大的数字,258字

=MID(SUM(RIGHT(SMALL(1*(IFERROR(MATCH(ROW(1:10)-1&"*",INDEX(RIGHT(A1:A14,COLUMN(A:CV)),N(IF(1,15-INT(ROW(100:1499)/100))),N(IF(1,MOD(ROW(1:1400)-1,100)+1))),),1500)&ROW(1:10)-1),ROW(1:10)))/10^ROW(1:10)),3,COUNT(MATCH("*"&ROW(1:10)-1&"*",LEFT(A1:A14,100),)))

复制代码

又闷头想了一下午，IFERROR给去掉了，07可用，但在03中超套，272字，汗。。。。。

=MID(SUM(RIGHT(SMALL(MATCH(ROW(1:10)-1&"*",INDEX(RIGHT(MID(5^19,ROW(1:14)*14-13,14)&A1:A14,COLUMN(A:DJ)),N(IF(1,15-INT(ROW(114:1709)/114))),N(IF(1,MOD(ROW(1:1596)-1,114)+1))),)/1%+ROW(1:10)-1,ROW(1:10)))/10^ROW(1:10)),3,COUNT(MATCH("*"&ROW(1:10)-1&"*",LEFT(A1:A14,100),)))

复制代码

又花了两三个小时，终于把那一层层的套去掉了，03通过，269字

=MID(SUM(RIGHT(SMALL(MATCH(ROW(1:10)-1&"*",RIGHT(MID(REPT(5^19,114),(1597-ROW(1:1596))*14-13,14)&LOOKUP(15.999-ROW(114:1709)/114,ROW(1:14),A1:A14),MOD(ROW(1:1596)-1,114)+1),)/1%+ROW(10:19),ROW(1:10)))/10^ROW(1:10)),3,COUNT(MATCH("*"&ROW(1:10)-1&"*",LEFT(A1:A14,100),)))

复制代码

适合A列全空，在03中修改参数可适应560行左右，264字,再等想想，能否减去35字，争取3分

=MID(SUM(RIGHT(SMALL(MATCH(ROW(1:10)-1&"*",RIGHT(MID(REPT(5^19,114),(1597-ROW(1:1596))*14-13,14)&LOOKUP(15.999-ROW(114:1709)/114,ROW(1:14),A1:A14),MOD(ROW(1:1596)-1,114)+1),)/1%+ROW(10:19),ROW(1:10)))/10^ROW(1:10))&0,3,COUNT(MATCH("*"&ROW(1:10)-1&"*",A1:A14&"",)))

复制代码

今天又看了看，稍微修剪下，242字，还差13字

=MID(SUM(RIGHT(SMALL(MATCH(ROW(1:10)-1&"*",RIGHT(IF(ROW(1:1596)>1482,5^19)&LOOKUP(15.999-ROW(114:1709)/114,ROW(1:14),A1:A14),MOD(ROW(1:1596)-1,114)+1),)/1%+ROW(10:19),ROW(1:10)))/10^ROW(1:10))&0,3,COUNT(MATCH("*"&ROW(1:10)-1&"*",A1:A14&"",)))

复制代码

继续。。。。。。。。。想

opqazxc · 发表于 2013-8-26 17:17

本帖最后由 opqazxc 于 2013-8-28 17:44 编辑

=IF(LOOKUP(1,-RIGHT(A1:A200)),,0)&SUM(MOD(LARGE(IF(ISNA(MATCH("*"&ROW(1:10)-1&"*",A1:A200&"",)),,MATCH(ROW(1:10)-1&"",MID(LOOKUP(201-ROW(1:20000)/100,ROW(1:200),A1:A200),1-MOD(ROW(101:20100),-100),1),)*10+ROW(1:10)-1),ROW(1:10)),10)*10^ROW(1:10))/10

复制代码

含等号249··

再减

=IF(LOOKUP(1,-RIGHT(A1:A200)),,0)&SUM(MOD(LARGE(IF(ISNA(MATCH("*"&ROW(1:10)-1&"*",A1:A200&"",)),,MATCH(ROW(1:10),1+MID(LOOKUP(201-ROW(A:A)/101,ROW(A:A),A:A),MOD(-ROW(A:A),101),1),)*10+ROW(1:10)-1),ROW(1:10)),10)*10^ROW(1:10))/10

复制代码

含等号228``

再看了看答题要求第4条
自己原先貌似看错成公式可以适应200行数据了
应该是写个以A1:A14为原数据
到时再改下公式的参数可以适应200行以上的数据

弄个可以按F9的

=IF(LOOKUP(1,-RIGHT(A1:A14)),,0)&SUM(MOD(LARGE(IF(ISNA(MATCH("*"&ROW(1:10)-1&"*",A1:A14&"",)),,MATCH(ROW(1:10),1+MID(LOOKUP(15-ROW(1:1414)/101,ROW(1:14),A1:A2),MOD(-ROW(1:1414),101),1),)*10+ROW(1:10)-1),ROW(1:10)),10)*10^ROW(1:10))/10

复制代码

含等号234

再返回整列引用``

=IF(LOOKUP(1,-RIGHT(A1:A14)),,0)&SUM(MOD(LARGE(IF(ISNA(MATCH("*"&ROW(1:10)-1&"*",A1:A14&"",)),,MATCH(ROW(1:10),1+MID(LOOKUP(15-ROW(A:A)/101,ROW(A:A),A:A),MOD(-ROW(A:A),101),1),)*10+ROW(1:10)-1),ROW(1:10)),10)*10^ROW(1:10))/10

复制代码

含等号225=IF(LOOKUP(1,-RIGHT(A1:A14)),,0)&SUM(MOD(LARGE(IF(ISNA(MATCH("*"&ROW(1:10)-1&"*",A1:A14&"",)),,MATCH(ROW(1:10),1+MID(LOOKUP(15-ROW(A:A)/101,ROW(A:A),A:A),MOD(-ROW(A:A),101),1),)*10+ROW(1:10)-1),ROW(1:10)),10)*10^ROW(1:10))/10
改参数的话也就红色字体的A14和15相对改下吧``

没了

等结果了
``

opqazxc · 发表于 2013-8-31 10:10

本帖最后由 opqazxc 于 2013-8-31 17:21 编辑

怎么又不能编辑了呢？
不能整列的话就以这个吧

=IF(LOOKUP(1,-RIGHT(A1:A14)),,0)&SUM(MOD(LARGE(IF(ISNA(MATCH("*"&ROW(1:10)-1&"*",A1:A14&"",)),,MATCH(ROW(1:10),1+MID(LOOKUP(15-ROW(1:1414)/101,ROW(1:14),A1:A2),MOD(-ROW(1:1414),101),1),)/1%+ROW(10:19)),ROW(1:10)),10)*10^ROW(1:10))/10

复制代码

含等号233
昨天下午刚看到想很久
减了一字符

想了很久
一减减一字符
终于

=IF(LOOKUP(1,-RIGHT(A1:A14)),,0)&SUM(MOD(LARGE(IF(ISNA(MATCH("*"&ROW(1:10)-1&"*",A1:A14&"",)),,MATCH(ROW(1:10),1+MID(LOOKUP(15-ROW(1:1400)%,ROW(1:14),A1:A2),1-MOD(ROW(1:1400),-100),1),)/1%+ROW(10:19)),ROW(1:10)),10)%*10^ROW(2:11))

复制代码

含等号230字符还差1
接着再减一字符

=IF(LOOKUP(1,-RIGHT(A1:A14)),,0)&SUM(MOD(LARGE(IF(ISNA(MATCH("*"&ROW(1:10)-1&"*",A1:A14&"",)),,MATCH(ROW(1:10),1+MID(LOOKUP(15-ROW(1:1400)%,ROW(1:14),A1:A2),1-MOD(ROW(1:1400),-100),1),)+ROW(10:19)%),ROW(1:10))*10,1)*10^ROW(1:10))

复制代码

含等号229字符
然后浮点了``

jsxjd · 发表于 2013-9-1 20:40

本帖最后由 jsxjd 于 2013-9-5 18:22 编辑

这一期和 42 期比：1、逆序难度大；2、字符要求高；3、分数低
看来版主藏有秘密武器。
目前仅搞到 275 Chars ，但适用于“全空”或“全满”，要搞到 230 起码再脱一层。

=MID(SUM(MOD(LARGE(LOOKUP(ROW(1:10)*10^8,SMALL(MID(A1:A14&REPT(0,101),COLUMN(A:CW),1)*(10^8+1)+ROW(1:14)*10^5+COLUMN(A:CW)/1%-10^8*(COLUMN(A:CW)>LEN(A1:A14)),ROW(1:1414)))-(ROW(1:10)-1)*10^8,ROW(1:10)),10)*10^-ROW(1:10))+10^-11,3,COUNT(MATCH("*"&ROW(1:10)-1&"*",A1:A14&"",)))

复制代码

终于有新进展。
228 Chars：适用于全空/仅0/全满

=MID(SUM(RIGHT(LARGE(LOOKUP(ROW(11:20)*10^8,SMALL((1&MID(A1:A14,COLUMN(A:CW),1))*(10^8+1)+ROW(1:14)*10^5+COLUMN(A:CW)/1%,ROW(1:1414)))-ROW(10:19)*10^8,ROW(1:10)))*10^-ROW(2:11))&1%,4,COUNT(MATCH("*"&ROW(1:10)-1&"*",A1:A14&"",)))

复制代码

swyrlbq · 发表于 2013-9-1 20:54

本帖最后由 swyrlbq 于 2013-9-13 12:07 编辑

此题的难度在于很难突破七层嵌套，所以要尽量用加减法，避免用mod、right之类的函数。以下为20行的公式：
方法一：等于法（突破嵌套的方法）317字符
=IF(LOOKUP(9,-RIGHT(A1:A20)),"",0)&SUM(RIGHT(LARGE(-TEXT((LARGE(COLUMN(J:S)/1%%+(2001-ROW(1:2000))*(LARGE(ROW(1:20)/1%%+COLUMN(A:CV)/1%+(1&MID(A1:A20,COLUMN(A:CV),1)),ROW(1:2000))-(2101-ROW(1:2000))/1%=COLUMN(J:S)),(10-COLUMN(A:J))*2000+1)-COLUMN(J:S)/1%%)/1%+COLUMN(J:S),"[<20]99999!0"),ROW(1:10)))*10^(ROW(1:10)-1))

方法二：if+match（用if容match 查不到出现的错误）277字符
=IF(LOOKUP(9,-RIGHT(A1:A20)),"",0)&SUM(RIGHT(SMALL(-IF(MMULT(1^COLUMN(A:T),1-ISERR(FIND(COLUMN(A:J)-1,A1:A20))),MATCH(COLUMN(J:S),LARGE(ROW(1:20)/1%%+COLUMN(A:CV)/1%+(1&MID(A1:A20,COLUMN(A:CV),1)),ROW(1:2000))-(2101-ROW(1:2000))/1%,)&COLUMN(J:S),),ROW(1:10)))*10^(ROW(1:10)-1))

方法三lookup法，（lookup具有查找数值本身的优势，且不会返错，容易容错，容易携带数据信息）
基本构成228字符：
=IF(LOOKUP(9,-RIGHT(A1:A20)),"",0)&SUM(RIGHT(LARGE(-TEXT((LOOKUP(COLUMN(K:T)/1%%,SMALL((1&MID(A1:A20,COLUMN(A:CV),1)&ROW(11:30)/1%)+COLUMN(A:CV)-1,ROW(1:2000)))-COLUMN(J:S)/1%%)&COLUMN(J:S),"0;0!0"),ROW(1:10)))*10^(ROW(1:10)-1))

优化后，215字符：
=IF(LOOKUP(9,-RIGHT(A1:A20)),"",0)&SUM(RIGHT(LARGE(-TEXT(LOOKUP(COLUMN(K:T)/1%%,SMALL((1&MID(A1:A20,COLUMN(A:CV),1)&ROW(11:30))/1%+COLUMN(A:CV),ROW(1:2000)))/1%-COLUMN(J:S)*999999,"0;0!0"),ROW(1:10)))*10^ROW(2:11))%

扩展公式为200行，（四核cpu，需运行约2分钟）222字符：
=IF(LOOKUP(9,-RIGHT(A1:A200)),"",0)&SUM(RIGHT(LARGE(-TEXT(LOOKUP(COLUMN(K:T)*10^5,SMALL((1&MID(A1:A200,COLUMN(A:CV),1)&ROW(101:300))/1%+COLUMN(A:CV),ROW(1:20000)))/1%-COLUMN(J:S)*9999999,"0;0!0"),ROW(1:10)))*10^ROW(2:11))%

476363814 · 发表于 2013-9-3 17:19

本帖最后由 476363814 于 2013-9-5 10:22 编辑

先想办法拿到 1分再说，请楼主验证
374个字符

=MID(SUM(RIGHT(LARGE((FIND("K",SUBSTITUTE(7/19&A1:A14,COLUMN(A:J)-1,"K",LEN(7/19&A1:A14)-LEN(SUBSTITUTE(7/19&A1:A14,{0,1,2,3,4,5,6,7,8,9},""))))/1%+ROW(1:14)/1%%%+COLUMN(A:J)-1)*(--MMULT(N(ROW(1:14)<=COLUMN(A:N)),1-ISERR(FIND(COLUMN(A:J)-1,A1:A14)))=1)*(1-ISERR(FIND(COLUMN(A:J)-1,A1:A14))),ROW(1:10)))/10^ROW(1:10),10^-11),3,COUNT(MATCH("*"&COLUMN(A:J)-1&"*","K"&A1:A14,)))

复制代码

xzk235 · 发表于 2013-9-4 13:10

本帖最后由 xzk235 于 2013-9-5 19:50 编辑

=IF(-RIGHT(LOOKUP(1,0/LEN(A:A),A:A)),,0)&SUM(RIGHT(SMALL(-TEXT(MATCH("*"&ROW(1:10)-1,""&SMALL(-(ROW(1:15)*100+COLUMN(A:CV)-1&MID(A1:A15,COLUMN(A:CV),1)&MID(A1:A15,COLUMN(A:CV),1)&MID(A1:A15,COLUMN(A:CV),1)),ROW(1:1400)),),"[>"&SUM(LEN(A:A))&"]!0;0!"&ROW(1:10)-1),ROW(1:10)))*10^ROW(1:10)/10)

复制代码

291字符。
嵌套超层没法了，MID(A1:A15,COLUMN(A:CV),1)连接了3次，汗，本来用REPT的。先发出来再研究研究。
如暂不考虑100行以上数据时，可以少连接一次，节省27字符，291-27=264
为避免错误，数据范围A1:A14的引用范围用了A1:A15，避免match查找不到。如A1:A14全为相同数字（如1）且长度为100时，其它数字会MATCH出错。或者把A:CV的列引用增加10列也行。
------------------上面那公式慢的没底了，测试个100行数据能算几分钟，以下方法2，就是有点长，速度还行。--------------------------

=IF(-RIGHT(LOOKUP(1,0/LEN(A:A),A:A)),,0)&SUM(RIGHT(SMALL(IF(ROW(A1:A200)=--RIGHT(LARGE(ROW(A1:A200)*(FIND(COLUMN(A:J)-1,A1:A200&1/17)<=LEN(A1:A200))+COLUMN(A:J)*1000,2001-COLUMN(A:J)*200),3),ROW(A1:A200)/1%%+COLUMN(A:J)-1+FIND("A",SUBSTITUTE(A1:A200,COLUMN(A:J)-1,"A",LEN(A1:A200)-LEN(SUBSTITUTE(A1:A200,{0,1,2,3,4,5,6,7,8,9},))))*10,10^9),COLUMN(A:J)))*10^COLUMN(A:J)/10)

复制代码

372字符。

		自动登录	找回密码
密码			免费注册

[Excel 函数与公式] [第97期]按倒序不重复取出数字排列(三)[已评分]

本帖子中包含更多资源

评分

点评

评分

评分

评分

点评

评分

点评

评分

评分

评分

评分

最新热点 /1