[第129期]评选足球先生（开帖待总结）

delete_007

• 竞赛题目源自solextrade
  

题目说明：
  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日



模拟结果：

delete_007

第一题，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))

王嘉玲

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



补充内容 (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")

sbdk007

第一题: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))

满坛皆为吾师

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

micch

这事整的，想用区域数组，就是不会，只好一个公式右拉下拉了。问题是结果是文本不是数字,不知道行不行，用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")),),)&""

复制代码

@刘卓

第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))),"")参与一下吧，想不出

丢丢表格

第一问  （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))),";;")

LSL1128xgw

题一：
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))

藍色DE流光

第一题

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)),"")

复制代码