ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 26225|回复: 56

[Excel 函数与公式] [第83期]舞林大会之交换舞伴

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2011-10-19 09:39 | 显示全部楼层 |阅读模式
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 fangjianp 于 2011-11-27 14:36 编辑

题目、要求和评分规则见附件及说明

答题期间:2011-10-24  ~  2011-11-26 17:00 , 过期不再评分,答题结束请勿修改答案。

请大家认真审题:
6、屏蔽由引用空单元格出现的“0”值,请在公式最后用&""方法,以便统一计算公式长度
7、只准引用源数据(A1:C25,F2:G2)及已有的结果

因此整列引用如A:A等都超出了引用范围




本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

评分

4

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-10-24 13:33 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 fangjianp 于 2011-10-29 19:28 编辑

我的预设解为:
  1. =INDEX(A$2:A$25,MATCH(MOD(INDEX(-1^(B$2:B$25=F$2)*C$2:C$25,MATCH(,COUNTIF(F$2:G2,A$2:A$25),)),MAX(C$2:C$25+1)*{-1,1}),-1^(B$2:B$25=F$2)*C$2:C$25,))&""
复制代码
F3:G3多单元格数组公式,然后下拉至F14:G14
由于题目规定只准引用A1:C25,F2:G2,所以公式中没有A:A类似的引用,否则公式可缩短至142个字符,现为150个字符,字符长度142的公式如下
  1. =INDEX(A:A,MATCH(MOD(INDEX(-1^(B$2:B$25=F$2)*C$2:C$25,MATCH(,COUNTIF(F$2:G2,A$2:A$25),)),(MAX(C:C)+1)*{-1,1}),-1^(B$1:B$25=F$2)*C$1:C$25,))&""
复制代码

评分

4

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-10-24 20:35 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 hjj0451 于 2011-10-27 19:56 编辑

都删了,保留这个,为了避免超出引用范围,活也没干了。F3:G14多单元格数组公式,234字符:
  1. =INDEX(A1:A25,MID(RIGHT(SMALL(IF(C2:C25*(B2:B25=F2)+TRANSPOSE(C2:C25*(B2:B25=G2))=MAX(C2:C25)+1,IF(C2:C25<TRANSPOSE(C2:C25),ROW(A2:A25),TRANSPOSE(ROW(A2:A25)))/1%+ROW(A2:A25)+TRANSPOSE(ROW(A2:A25))%,25025),ROW(A1:A12)),5),{1,4},2))&""
复制代码

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-10-25 07:59 | 显示全部楼层
本帖最后由 fugb-2010 于 2011-10-31 09:24 编辑

F3:G14=INDEX(A2:A25,--MID(TEXT(SMALL(IF(N(IF(B2:B25=F2,C2:C25,)+TRANSPOSE(IF(B2:B25=G2,C2:C25,))=MAX(C2:C25)+1),IF(ROW(1:24)>=COLUMN(A:X),COLUMN(A:X),ROW(1:24))*10000+ROW(1:24)*100+COLUMN(A:X),242424),ROW(1:24)),"000000"),{3,5},2))&""
多单元数组公式一次完成

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-10-25 08:28 | 显示全部楼层
本帖最后由 悟空师弟 于 2011-10-27 20:30 编辑

F3单元格输入公式左拉下拉完成。
225字符:
=OFFSET($A$1,RIGHT(SMALL(IF((COLUMN($A:$X)>ROW($1:$24))*(TRANSPOSE($B$2:$B$25)<>$B$2:$B$25)*(TRANSPOSE($C$2:$C$25)+$C$2:$C$25=MAX($C$1:$C$25)+1),IF($B$2:$B$25=F$2,ROW($1:$24),COLUMN($A:$X))+ROW(1:24)/1%,9924),ROW(A1)),2),)&""


坐等高手精彩答案!




评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-10-25 09:40 | 显示全部楼层
本帖最后由 ilyte 于 2011-10-25 11:22 编辑

来一驮大的,希望高手见了别笑话偶。
G3单元格数组公式: (长度239)
  1. =SUBSTITUTE(IF(G$2="男",INDEX(B$1:B$25,LARGE((C$2:C$25=G$2)*ROW(B$2:B$25),ROW(1:1))),INDEX(A$1:A$25,MAX(((C$2:C$25+IF(ISNA(VLOOKUP(F3,$A$2:C$25,3,0)),25,VLOOKUP(F3,$A$2:C$25,3,0))-1)=COUNTA(B$2:B$25)/2)*(B$2:B$25=G$2)*ROW(D$2:D$25)))),$A$1,"")
复制代码
然后左拖、下拉填充

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-10-25 19:42 | 显示全部楼层
本帖最后由 涅磐86970 于 2011-11-14 18:50 编辑

     2、列出的舞伴名称次序必须与模拟答案一致
这个?必须是王安石 苏洵这样下来么?

按部就班 不知道对不对 - - 不对的话烦请通知下  谢谢~~
=IF(COLUMN()=6,INDEX($A:$A,SMALL(IF($B$2:$B$25=$F$2,ROW($B$2:$B$25),26),ROW(A1))),INDEX($A:$A,MIN(IF(($C$2:$C$25=(COUNTA(A:A)/2+1/2-MAX(($A$2:$A$25=E3)*($C$2:$C$25))))*($B$2:$B$25=$G$2),ROW($C$2:$C$25),26))))&"

不太明白- -
INDEX(A1:A25,RIGHT(TEXT(IF({1,0},SMALL(IF(B2:B25=F2,C2:C25+ROW(2:25)%,25.25),ROW(1:24)),LARGE(IF(B2:B25=G2,C2:C25+ROW(2:25)%,25%),ROW(1:24))),".00"),2))&""


理解题意了- - 貌似不会做- - 现在好懒,动不得脑子- -。。。。。。

TA的精华主题

TA的得分主题

发表于 2011-10-26 12:10 | 显示全部楼层
本帖最后由 鬼狐 于 2011-11-3 00:11 编辑

223字符,先拿一分再说{:soso_e121:}
  1. =INDEX($A:$A,MOD(SMALL(IF(($C$2:$C$25+TRANSPOSE($C$2:$C$25*($B$2:$B$25=IF(COLUMN()=6,$G$2,$F$2)))=MAX($C:$C)+1)*($B$2:$B$25=F$2),IF(ROW($1:$24)>COLUMN($A:$X),COLUMN($A:$X),ROW($1:$24))/1%+ROW($2:$25),9999),ROW(A1)),100))&""
复制代码
另外,问个问题,能不能引用非答题区域的单元格啊,比如d2或者是e2
=================================================
总算明白了什么是不能整列引用了~~
改一下,增加10个字符,还是可以得一分^_^
  1. =INDEX($A$1:$A$25,MOD(SMALL(IF(($C$2:$C$25+TRANSPOSE($C$2:$C$25*($B$2:$B$25=IF(COLUMN()=6,$G$2,$F$2)))=MAX($C$2:$C$25)+1)*($B$2:$B$25=F$2),IF(ROW($1:$24)>COLUMN($A:$X),COLUMN($A:$X),ROW($1:$24))/1%+ROW($2:$25),9925),ROW(A1)),100))&""
复制代码

=================================================
大体思路不变,改成F3:G14区域数组,207字符,先送上,还差17个字符- -!
  1. =INDEX(A2:A25,MID(SMALL(IF((C2:C25+TRANSPOSE(C2:C25)=MAX(C2:C25)+1)*(B2:B25=F2)*(TRANSPOSE(B2:B25=G2)),IF(ROW(1:24)>COLUMN(A:X),COLUMN(A:X),ROW(1:24))+ROW(1:24)%+COLUMN(A:X)%%,24.2424)%,ROW()-2),{5,7},2))&""
复制代码

=================================================
上面的公式有点小问题,10%显示的是0.1而不是0.10,所以当用mid截取有些时候会出现错误
改一下,还是207个字符,估计这种思路中,207已经是极限了吧
=INDEX(A2:A25,MID(SMALL(IF((C2:C25+TRANSPOSE(C2:C25)=MAX(C2:C25)+1)*(B2:B25=F2)*TRANSPOSE(B2:B25=G2),IF(ROW(1:24)>COLUMN(A:X),COLUMN(A:X),ROW(1:24))+ROW(1:24)%+COLUMN(A:X)%%,24.2424)%,ROW()-2)&0,{5,7},2))&""


以下,是另外的思路,177个字符,还是F3:G14区域数组

  1. =INDEX(A2:A25,MOD(SMALL(IF(B2:B25=F2:G2,MATCH(ABS({0,1}*(MAX(C2:C25)+1)-C2:C25),ABS(IF(B2:B25=F2,,MAX(C2:C25)+1)-C2:C25),)/1%%+{1,2}/1%+ROW(1:24),999924),ROW()*2-{5,4}),100))&""
复制代码

=================================================
想了好久,还是只差一个字符,真恶心,F3:G14区域数组166,先上传

  1. =INDEX(A2:A25,MOD(SMALL(IF(B2:B25=F2:G2,MATCH(ABS({0,1}*MAX(C2:C25+1)-C2:C25),ABS((B2:B25=G2)*MAX(C2:C25+1)-C2:C25),)/1%+{0,50}+ROW(1:24),9924),ROW()*2-{5,4}),50))&""
复制代码
另外,听说可以F列区域然后右拉,如果是这样子的话,就很简单了,只要138就可以搞定了,不知真假,先上传
  1. =INDEX($A2:$A25,MOD(SMALL(IF($B2:$B25=F2,MATCH($C2:$C25,ABS(($B2:$B25<>F2)*MAX($C2:$C25+1)-$C2:$C25),)*50+ROW(1:24),9924),ROW()-2),50))&""
复制代码
=================================================
发现改成单元格数组公式的话,也才154,比F3:G14区域小,总算搞定了,哇哈哈~~~
F3右下拉
  1. =INDEX($A$2:$A$25,MOD(SMALL(IF($B$2:$B$25=F$2,MATCH($C$2:$C$25,ABS(($B$2:$B$25<>F$2)*MAX($C$2:$C$25+1)-$C$2:$C$25),)*50+ROW($1:$24),9924),ROW()-2),50))&""
复制代码

点评

思路特别,破常规,本期竞赛最佳公式!  发表于 2011-12-3 13:14

评分

5

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-10-26 21:18 | 显示全部楼层
  1. =INDEX($A:$A,RIGHT(SMALL(MMULT(MATCH(MID("*"&F$2,{1,2},1)&ROW(INDIRECT("1:"&COUNT($C$2:$C$25)/2)),$B$2:$B$25&ABS((COUNT($C$2:$C$25)/2+1)*($B$2:$B$25="女")-$C$2:$C$25),),{100;1}),ROW(A1)),2)+1)
复制代码
搞一个未容错的先占位,190个字

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-10-27 12:20 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 hopeson2010 于 2011-10-27 14:33 编辑

参与一下:238字符!
  1. INDEX(A:A,MID(SMALL(10^7+TEXT(ROW(2:25)/1%%+{0.01,1}*IF(B2:B25=F2:G2,TEXT(MATCH({"女","男"}&MAX(C:C)+1-C2:C25,B1:B25&C1:C25,),"[>"&ROW(2:25)&"];\0"))+ROW(2:25)*{1,0.01},"[>"&ROW(2:25)/1%%+ROW(2:25)&"];999999\.99"),ROW(1:48)),{7,10},2))&""
复制代码

减化一下:222字符(但引用区域有错)先保留
  1. =INDEX(A:A,MID(SMALL(10^7+TEXT(ROW(2:25)/1%%+IF(B2:B25=F2:G2,TEXT(MATCH({"女","男"}&MAX(C:C)+1-C2:C25,B1:B25&C1:C25,),"[>"&ROW(2:25)&"];\0"))*{1,100}+ROW(2:25)*{100,1},"[>"&ROW(2:25)*10100&"];999999"),ROW(1:48)),{5,7},2))&""
复制代码
228字符:不知道这样可不可以!但在公式的区域内没有错!
  1. =INDEX(A2:A25,MID(SMALL(10^7+TEXT(ROW(1:24)/1%%+IF(B2:B25=F2:G2,TEXT(MATCH({"女","男"}&MAX(C2:C25)+1-C2:C25,B2:B25&C2:C25,),"[>"&ROW(1:24)&"];\0"))*{1,100}+ROW(1:24)*{100,1},"[>"&ROW(1:24)*10100&"];992424"),ROW(1:48)),{5,7},2))&""
复制代码

评分

2

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 15:42 , Processed in 0.052499 second(s), 11 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表