ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[Excel 函数与公式] [第129期]评选足球先生(开帖待总结)

[复制链接]

TA的精华主题

TA的得分主题

发表于 2020-2-18 15:02 | 显示全部楼层 |阅读模式
本帖最后由 delete_007 于 2020-3-29 11:22 编辑

题目说明:
  C罗/梅西/姆巴佩/内马尔4人入选世界足球先生评选。B:G列为四位球员“各赛事技术资料”,是数据源。
  为便于评选,K4:N14单元格区域展示指定球员的赛事表现;Q5:Q22展示指定球员参加各赛事的次数。
  模拟结果见右侧W:AS列。
  第一题的数据源为B:G列和K4:N4,第二题的数据源为B:G列和K4:N14。

  第一题:根据K4:N4的赛事和I4指定的球员,按进球数量降序一行赛事表头一行进球数的形式排列。如果进球数量相同,则按从左向右顺序排列。见模拟结果。
  第二题:根据K4:N4(从左至右)的赛事顺序,列出I4球员参加各赛事的次数序号。比如选择C罗,联赛参加了5届,则列出联赛-1,联赛-2...联赛-5,欧锦赛没有参加,就要跳过,不显示。

答题要求:
  •   纯函数题目,除录入公式外,允许其他任何操作。
  •   允许使用{4;14;20;30}、"联赛 欧锦赛欧冠 美州杯"等类似常量或常量数组。
  •   每题仅可用一个公式。可以向右或向下填充,也可使用区域数组公式。
  •   公式需通过EXCEL 2013版验证。

评分规则:
  •   第一题,小于170字符(含等号)得技术1分;
  •   第二题,小于140字符(含等号)得技术1分;
  •   两题评分不关联,即第一题未答出也可单独答第二题。
  •   其他正确答案给予适当财富奖励(原则不超过50财富)。

竞赛日期:
  2020年2月18日 至 2020年3月20日



模拟结果:





单选投票, 共有 31 人参与投票
您所在的用户组没有投票权限

本帖子中包含更多资源

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

x

评分

12

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2020-2-18 15:33 | 显示全部楼层
本帖最后由 delete_007 于 2020-2-19 09:57 编辑

第一题,K5数组公式右拉下拉(163):
=INDIRECT(TEXT(SMALL(IF(K$4&$I$5=$B$5:$B33&$C$5:$G33,TIME(99-$C$6:$G34,{3,4,5,6,7},IF(MOD(ROW(),2),MATCH(K$4,$B:$B,)-1,ROW($6:34))),9+1%/8),ROW(A2)/2),"rscm"),)&""
第二题,Q5:Q22区域数组公式(135):
=INDEX(TEXT(LOOKUP(ROW(5:25),{1,2,3,4,5}*5,K4)&-MOD(ROW(5:25),5)-1,";"),SMALL(IF(1-ISERR(-K5:N14),ROW(1:10)/2+{0,5,10,15},21),ROW()-4))
换成OFFSET(127):
=INDEX(TEXT(T(OFFSET(J4,,ROW(5:25)/5))&-MOD(ROW(5:25),5)-1,";"),SMALL(IF(1-ISERR(-K5:N14),ROW(1:10)/2+{0,5,10,15},21),ROW()-4))


评分

13

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-2-18 17:52 | 显示全部楼层
本帖最后由 王嘉玲 于 2020-2-18 18:50 编辑

哥哥好啊     原来合并单元格里面有数据的呀



补充内容 (2020-2-26 22:50):
第一题161字符,从小到大排列。
=INDIRECT(TEXT(SMALL(IF($B$5:$B34&$C$5:$G34=K$4&$I$5,TIME({3,4,5,6,7},,IF(0>-1^ROW(),MATCH(K$4,$B:$B,)-1,ROW($6:35)))-$C$6:$G35+99,99.99),ROW(A2)/2),"rsch"),)&""

补充内容 (2020-2-26 22:54):
第二题95字符,LOOKUP(ROW()-SUBTOTAL)的更深层次用法
=TEXT(LOOKUP("々",K$4:O$4&-(0&ROW()-5-COUNTIF(OFFSET(J$5:J$14,,,,COLUMN(A:E)),"> ")/2)-1),";;0")

评分

14

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-2-18 20:17 来自手机 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 sbdk007 于 2020-2-27 20:12 编辑

第一题:162
=INDIRECT(TEXT(SMALL(IF($B$5:$B34&$C$5:$G34=K$4&$I$5,TIME(40-$C$6:$G35,COLUMN($C:$G),IF(-1^ROW()+1,ROW($6:35),MATCH(K$4,$B:$B,)-1)),59.11),ROW(A2)/2),"rscm"),)&""


第二题:
下拉137
=IFERROR(INDEX(T(OFFSET(J$4,,COLUMN(E:X)/5))&-MOD(COLUMN(E:X),5)-1,SMALL(IF(LENB(K$5:N$14)>2,ROW($2:$11)/2+COLUMN(A:D)*5-5),ROW()-4)),"")
区域数组,128
=IFERROR(INDEX(T(OFFSET(J4,,ROW(5:24)/5))&-MOD(ROW(5:24),5)-1,SMALL(IF(LENB(K5:N14)>2,ROW(2:11)/2+COLUMN(A:D)*5-5),ROW()-4)),"")
学习海鲜老师的错误值利用,111
=REPT(LOOKUP("咗",K$4:N$4&-(0&ROW()-5-COUNTIF(OFFSET(J$5,,,10,COLUMN(A:D)),"><")/2)-1),ROW()-5<COUNT(-K$5:N$14))

评分

14

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-2-18 20:30 | 显示全部楼层
本帖最后由 满坛皆为吾师 于 2022-5-14 19:04 编辑

2022年了,回想当年养的鸡还是没有偷成。

点评

请勿占楼  发表于 2020-4-15 16:33

评分

11

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-2-18 22:16 | 显示全部楼层
本帖最后由 micch 于 2020-2-29 21:33 编辑

这事整的,想用区域数组,就是不会,只好一个公式右拉下拉了。问题是结果是文本不是数字,不知道行不行,用iferror可以得到数值型数字
文本结果。
  1. =INDIRECT(TEXT(SMALL(99-IF($B$5:$B34&$C$5:$G34=K$4&$I$5,$C$6:$G35-TIME(,{3,4,5,6,7},IF(ISODD(ROW()),MATCH(K$4,$B:$B,)-1,ROW($6:35))),1%),ROW(A2)/2),"rscm"),)&""
复制代码
  1. =LOOKUP("在",TEXT(K$4:O$4&-(0&ROW()-COUNTIF(OFFSET(J$5:J$14,,,,COLUMN(A:E)),">'")/2-5)-1,"0;"))
复制代码

数字结果
  1. =IFERROR(INDIRECT(TEXT(SMALL(99-IF($B$5:$B34&$C$5:$G34=K$4&$I$5,$C$6:$G35-TIME(,{3,4,5,6,7},IF(ISODD(ROW()),MATCH(K$4,$B:$B,)-1,ROW($6:35)))),ROW(A2)/2),"rscm"),),"")
复制代码
  1. =LOOKUP("在",TEXT(K$4:O$4&-(0&ROW()-SUBTOTAL(2,OFFSET(J:J,,,,COLUMN(A:E)))-5)-1,"0;"))
复制代码
不知道2013版对text参数有没有啥限制,原来用的是  ";;0" ";;"  来着,改为  "0;"  试试
二题不用一题的结果,根据B:G和k4:n4得出结果
  1. =LOOKUP("在",TEXT(K$4:O$4&-(0&ROW()-5-MMULT(MMULT({1,1,1,1,1},COUNTIFS(B:B,J$4:N$4,OFFSET(B:B,,ROW($1:$5)),I$5)),N(ROW($1:$5)<COLUMN(B:F))))-1,"0;"))
复制代码
试试一题区域数组,太长了,有空再试试能不能减下来吧
  1. =INDEX(INDIRECT(TEXT(MOD(SMALL(-IF($B$5:$B34&$C$5:$G34=K$4&$I$5,$C$6:$G35-TIME({3,4,5,6,7},,ROW($6:35)),1%),ROW(A2)/2),1)+MATCH(K$4,$B:$B,)-1,{"rdch";"rsch"}),),)&""
复制代码
K5:K6右拉下拉
K5:K14右拉的公式减不下去了
  1. =INDEX(INDIRECT(TEXT(MOD(SMALL(-IF($B5:$B34&$C5:$G34=K4&$I5,$C6:$G35-TIME({3,4,5,6,7},,ROW(6:35)),1%),ROW(2:11)/2),1)+MATCH(K4,$B:$B,)-1,IF(ISODD(ROW()),"rdch","rsch")),),)&""
复制代码



评分

14

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-2-19 00:41 | 显示全部楼层
第1题,K5:=IFERROR(INDIRECT(TEXT(LARGE(IF(K$4&$I$5=$B$5:$B$34&$C$5:$G$34,TIME($C$6:$G$35-COLUMN(C:G)%,ROW($6:$35),COLUMN($C:$G))),INT(ROW(A2)/2)),IF(MOD(ROW(),2),"""R"&MATCH(K$4,$B:$B,)-1&"C""s","RmCs")),),"")

第2题,Q5:
=IFERROR(INDEX($4:$4,SMALL(IF(K$5:N$14>"",COLUMN(K:N)),ROW(A1)))&"-"&RIGHT(SMALL(IF(K$5:N$14>"",COLUMN(K:N)+ROW($2:$11)%/2),ROW(A1))),"")参与一下吧,想不出

评分

11

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-2-20 19:52 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 丢丢表格 于 2020-2-25 14:48 编辑

第一问  (162字)
=INDIRECT(TEXT(SMALL(IF($B$5:$B34&$C$5:$G34=K$4&$I$5,TIME(99-$C$6:$G35,{3,4,5,6,7},IF(MOD(ROW(),2),MATCH(K$4,$B:$B,)-1,ROW($6:35))),7.1^5),ROW(A2)/2),"rscm"),)&""


第2问  (95字)
=TEXT(LOOKUP("唑",K$4:O$4&-1/(1/(0&ROW(A1)-COUNTIF(OFFSET(J$5,,,10,{1,2,3,4,5}),"*?")/2))),";;")

评分

15

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-2-21 10:21 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 LSL1128xgw 于 2020-2-22 08:45 编辑

题一:
168
=INDIRECT(TEXT(LARGE(IF($B$4:$B34&$C$4:G34=K$4&$I$5,$C$5:G35-COLUMN($C:G)%*3599/864+IF(-1^ROW()<0,MATCH(K$4,$B:$B,)-1,ROW($5:35))/1440,1/7),INT(ROW(A2)/2)),"rmcs"),)&""
题二:
127
=IFERROR(INDEX(T(OFFSET(I4,,ROW(9:68)/10))&-MOD(ROW(9:68)/2,5)-1,SMALL(IF(K5:N14>"",COLUMN(A:D)*10+ROW(1:10)),ROW()*2-8),1),"")
129
=INDEX(TEXT(T(OFFSET(I4,,ROW(9:68)/10))&-MOD(ROW(9:68)/2,5)-1,";;;@"),SMALL(IF(K5:N14>"",COLUMN(A:D)*10+ROW(1:10),58),ROW()*2-8))

评分

12

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-2-22 15:11 | 显示全部楼层
本帖最后由 藍色DE流光 于 2020-2-25 20:43 编辑

第一题
  1. =INDIRECT(TEXT(SMALL(IF($B$5:$B$33&$C$5:$G$33=K$4&$I$5,99-$C$6:$G$34+TIME(,{3,4,5,6,7},IF(MOD(ROW(),2),MATCH(K$4,$B:$B,)-1,ROW($6:$34))),99.99),ROW(A2)/2),"rscm"),)&""
复制代码
第二题
  1. =IFNA(LOOKUP("鲜",K$4:N$4&-1-(0&ROW()-5+COUNTIF(OFFSET(N$5,,,10,-{4,3,2,1}),"><")/2-TEXT(COUNT(-K$5:N$14),"[<"&ROW(A1)&"]99"))),"")
复制代码
  1. =IF(ROW(A1)>COUNT(-K$5:N$14),"",LOOKUP("足",K$4:N$4&-1-(0&ROW()-5-COUNTIF(OFFSET(J$5,,,10,{1,2,3,4}),"><")/2)))
复制代码
  1. =IFNA(INDEX(K$4:O$4&-COUNTIF(Q$4:Q4,K$4:N$4&"*")-1,SMALL(IF(K$5:N$14>"",{1,2,3,4},5),ROW(A1)*2)),"")
复制代码


评分

13

查看全部评分

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

本版积分规则

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

GMT+8, 2024-7-13 20:35 , Processed in 0.042216 second(s), 13 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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