[53期]一道排序函数练习题供大家练练手 -------已结

冻豆腐

一道函数练习题，改编自rongjun老师的一道题，供有兴趣的家人练习一下。具体要求见附件。

。。。。。。。。。。。。。。。。。。。。。。。。。


国庆期间一直没有看题，对答题的朋友和将要答题的朋友，补充几点说明
1。为了公平起见现改为一个下拉公式。
2。答题区域只是一个范围，实际要求是公式下拉也不能出错，请大家注意排错。
3。公式就按题目里面的字符处理，但不能使用code和char函数。
4。只要能做出来就有1分，公式包括等号要求为少于等于320个字符的加2分，少于等于265字符加3分，精彩答案再额外加分。

本人第一次出题，有很多不足之处，望大家见谅
本帖将于10月22日结题

[ 本帖最后由 冻豆腐 于 2009-10-21 09:56 编辑 ]

lzzgzby

用if{{1,0}构造数组，排错只到C40
244
=INDEX(A:A,MOD(LARGE(IF({1,0},MMULT(COUNTIF(A$1:A$20,{">","="}&A$1:A$20),{1;100}/1%%)+ROW($1:$20)*101,IF((COUNTIF(OFFSET(A$1,,,ROW($1:$20)),A$1:A$20)<3)*ROW($1:$20),MMULT(COUNTIF(A$1:A$20,{">","="}&A$1:A$20),{1;100}/1%%)+21,21)),ROW()),100))&""(速度较快）
234
=INDEX(A:A,MOD(LARGE(IF({1,0},MMULT(COUNTIF(A:A,{">","="}&A$1:A$20),{1;100}/1%%)+ROW($1:$20)*101,IF((COUNTIF(OFFSET(A$1,,,ROW($1:$20)),A$1:A$20)<3)*ROW($1:$20),MMULT(COUNTIF(A:A,{">","="}&A$1:A$20),{1;100}/1%%)+21,21)),ROW()),100))&""（速度太慢）

[ 本帖最后由 lzzgzby 于 2009-10-16 14:18 编辑 ]

zhanhewang

国庆假期一直没空，今天花了不少功夫，勉强凑一个，305字符。再次修改256、257个字符。
=INDEX(A:A,MOD(SMALL(IF(MATCH(A$1:A$20,A:A,)=ROW($1:$20),IF(COUNTIF(A:A,A$1:A$20)>COLUMN(A:G)-2-(COUNTIF(A:A,A$1:A$20)>1),(9-COUNTIF(A:A,A$1:A$20))*100+COUNTIF(A:A,"<="&$A$1:$A$20)+ROW($1:$20)*1%+0.5*(COUNTIF(A:A,A$1:A$20)<COLUMN(A:G)))),ROW(A1)),1)/1%)&""

=INDEX(A:A,MOD(SMALL(IF(MATCH(A$1:A$20,A:A,)=ROW($1:$20),(9-COUNTIF(A:A,A$1:A$20)+10*(COUNTIF(A:A,A$1:A$20)<COLUMN(A:G)-1-(COUNTIF(A:A,A$1:A$20)>1)))*100+COUNTIF(A:A,"<="&A$1:A$20)+IF(COUNTIF(A:A,A$1:A$20)>=COLUMN(A:G),ROW($1:$20),90)*1%),ROW(A1)),1)/1%)&""
==============================================
在A1:A20都是一个字母时会出错，不得分
                                                       ====wshcw===
==============================================

[ 本帖最后由 wshcw 于 2009-10-21 11:04 编辑 ]

wjh619993018

含等号共247字符
=INDEX(A:A,MOD(LARGE(ABS((MATCH(1&A$1:A99,1&A$1:A99,)=ROW($1:99))*(COUNTIF(A:A,A$1:A99)*10^6+COUNTIF(A:A,">"&A$1:A99)*10^3+ROW($1:99))*((2+TEXT(COUNTIF(A:A,A$1:A99),"[>1]0;!0"))>=COLUMN(1:1))-99*(COUNTIF(A:A,A$1:A99)<COLUMN(1:1))),ROW()),1000))&""

含等号共215字符
=INDEX(A:A,RIGHT(LARGE(COUNTIF(A:A,A$1:A99)*10^4+COUNTIF(A:A,">"&A$1:A99)/1%+IF({1,0,0},ROW($1:99),IF(MATCH(1&A$1:A99,1&A$1:A99,)=ROW($1:99),IF({0,1,0},-1,IF(COUNTIF(A:A,A$1:A99)>1,-1,1-10^5)),1-10^5)),ROW()),2))&""

[ 本帖最后由 wjh619993018 于 2009-10-18 15:52 编辑 ]

syz105729913

219字符，刚刚能达到要求
如果是要一个公式下拉的话把我的公式加个绝对引用就好了。

1. =INDEX(A:A,RIGHT(LARGE(COUNTIF(A$1:A$20,A$1:A$20)*10^4+COUNTIF(A$1:A$20,">="&A$1:A$20)/1%+IF({1,0,0},ROW($1:$20),IF(MATCH(A$1:A$20,A$1:A$20,)=ROW($1:$20),IF(COUNTIF(A$1:A$20,A$1:A$20)>1,{0,0.99,0.99},{0,0.99,-99999}),-99999)),ROW()),2))&""

复制代码

=============================================
在A1:A20都是一个字母时会重复，不得分
                                                  ===wshcw==
=============================================

[ 本帖最后由 wshcw 于 2009-10-21 11:05 编辑 ]

rongjun

凑个热闹
多单元格数组：
=INDEX(A:A,MOD(LARGE(COUNTIF(A1:A20,A1:A20)/1%%+COUNTIF(A1:A20,">"&A1:A20)/1%+IF({1,0},-(COUNTIF(OFFSET(A1,,,ROW(1:20)),A1:A20)>2)*10^6-1,ROW(1:20)),ROW()),100))&""

xcd

189字符
数组公式
已发短消息

xcd版主答案，贴出来分享
C1,数组公式下拉,189字
=INDEX(A:A,RIGHT(LARGE(IF({1,0,0},1,MATCH(A$1:A$20,A:A,)=ROW($1:$20))*(COUNTIF(A:A,A$1:A$20)>{0,0,1})*MMULT(COUNTIF(A:A,{"",">"}&A$1:A$20)*10^{4,2},{1;1})+ROW($1:$20)-{0,1,1}%,ROW()),2))&""

[ 本帖最后由 wshcw 于 2009-10-21 11:16 编辑 ]

jyhxr

不知是否正确,请冻大师指教了,
假设数据区域不存在空白单元格;
因为结果区域行数最大范围不可能超过源区域行数的2倍,假设公式只在数据区域行数的2倍内下拉;
189字符
=INDEX(A:A,RIGHT(LARGE(MMULT(COUNTIF(A:A,{"=",">="}&A$1:A$20)*10^{9,7},{1;1})+IF({1,0},9*10^5+ROW($1:$20),IF(COUNTIF(OFFSET(A$1,,,ROW($1:$20)),A$1:A$20)<3,4^8,-(10^12+4^8))),ROW(A1)),5))&""
193字符
=INDEX(A:A,RIGHT(LARGE(COUNTIF(A:A,A$1:A$20)*10^9+COUNTIF(A:A,">="&A$1:A$20)*10^7+IF({1,0},9*10^5+ROW($1:$20),IF(COUNTIF(OFFSET(A$1,,,ROW($1:$20)),A$1:A$20)<3,4^8,-(10^12+4^8))),ROW(A1)),5))&""
数组公式
上述公式不适合数据列中同时包含数字与字符单元格的情况,COUNTIF(A:A,">="&A$1:A$20)不能正确判别数值与字符的大小,且运算效率低下,改进一下:
226字符
=INDEX(A:A,RIGHT(LARGE(COUNTIF(A$1:A$20,A$1:A$20)*10^9+MMULT(N(A$1:A$20<=TRANSPOSE(A$1:A$20)),ROW($1:$20)^0)*10^7+IF({1,0},9*10^5+ROW($1:$20),IF(COUNTIF(OFFSET(A$1,,,ROW($1:$20)),A$1:A$20)<=2,4^8,-(10^12+4^8))),ROW(A1)),5))&""
注:上面2个公式虽然较短,但有不足,缺点是不能下拉到源数据行数2倍以外的地方,拉到41行时会出错(实际上41行开始不会有结果了);
以下公式可继续下拉;
比较通用一点的公式,246字符
=IF(ROW()>2*COUNTA(A:A),"",INDEX(A:A,RIGHT(LARGE(COUNTIF(A$1:A$20,A$1:A$20)*10^9+MMULT(N(A$1:A$20<=TRANSPOSE(A$1:A$20)),ROW($1:$20)^0)*10^7+IF({1,0},9*10^5+ROW($1:$20),(COUNTIF(OFFSET(A$1,,,ROW($1:$20)),A$1:A$20)>2)*-(10^12)+4^8),ROW(A1)),5)))&""
注:此公式源数据多于17232个时可能不会正确插入空行;
不太可能出错的公式,318字符
=IF(ROW()>=COUNTA(A:A)+2*SUM(1/COUNTIF(A$1:A$20,A$1:A$20))-SUM(N(COUNTIF(A$1:A$20,A$1:A$20)=1)),"",INDEX(A:A,RIGHT(LARGE(COUNTIF(A$1:A$20,A$1:A$20)*10^9+MMULT(N(A$1:A$20<=TRANSPOSE(A$1:A$20)),ROW($1:$20)^0)*10^7+IF({1,0},9*10^5+ROW($1:$20),(COUNTIF(OFFSET(A$1,,,ROW($1:$20)),A$1:A$20)>2)*-(10^12)+4^8),ROW(A1)),5)))&""
题后有感:
      花了三天时间，很不轻松地做了几个答案,也不知道是否能够弄上1分,此题对我来说难度虽然较高,但实用性强,富有创意,能激发他人思维,在此对冻大师的辛勤劳作深表感谢,虽然初次出题,已经可见冻大师的精湛技艺,相信此题仅是冻大师高超技术的冰山之一角,如果能在出题时把所有情况考虑周全些,把答题要求说明更详细点,将会有更多坛友积极响应,踊跃参与的。

[ 本帖最后由 jyhxr 于 2009-10-18 15:11 编辑 ]

chrisfang

做了两个解法，一个254字符，一个196字符，应该还能简化。

fmlshq

只是出于一股子热情，斗胆也试了一下，最后搞得自己挣扎了四五天，没日没夜脑子里都是这个问题，基本上算是完成了一半的要求
{=LOOKUP((--RIGHT(LARGE(--(COUNTIF(OFFSET($A$1,,,COUNTA($A:$A)),OFFSET($A$1,,,COUNTA($A:$A)))&LOOKUP(OFFSET($A$1,,,COUNTA($A:$A)),{"A","B","C","D","E","F","G","H";26,25,24,23,22,21,20,19})),ROW()),2)),{19;20;21;22;23;24;25;26},{"H";"G";"F";"E";"D";"C";"B";"A"})}
呵呵，剩下的，不是在我能力所能解决的范围之内了！


得，该回“家”学习了！



============================================
结果不正确，不得分,谢谢参与.
                                                     ===wshcw===
=============================================

[ 本帖最后由 wshcw 于 2009-10-21 11:09 编辑 ]