ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 2020-2-22 21:10 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 LWQ张无忌 于 2020-2-26 22:42 编辑

=IFERROR(IF(ISEVEN(ROW()),IF(K4="","",SMALL(IFERROR(FIND(K4&$B$5:$B$35&$I$5,OFFSET($C$1:$G$1,MATCH(K$4,$B:$B,)-2,)&K$4&$C$4:$G$34&$C$5:$G$35)*$C$5:$G$35,"鲜"),ROW($N$1))),LOOKUP(6-MOD(LARGE(IFERROR(FIND(K$4&$I$5,$B$5:$B$35&$C$4:$G$34)*$C$5:$G$35/1%+6-COLUMN($A:$E),""),INT(ROW(A2)/2)),100),ROW($1:$5),OFFSET($C$1:$G$1,MATCH(K$4,$B:$B,)-2,))),"")
第一题火车

减几个=IFERROR(OFFSET($B$1,IF(ISEVEN(ROW()),MAX(IFERROR(FIND(K4&K$4&$I$5,OFFSET($C$1:$G$1,MATCH(K$4,$B:$B,)-2,)&$B$5:$B$34&$C$5:$G$34)*ROW($5:$35),"")),MATCH(K$4,$B:$B,)-2),MIN(6-RIGHT(LARGE(IFERROR(FIND(K$4&$I$5,$B$5:$B$35&$C$4:$G$34)*$C$5:$G$35/1%+(6-COLUMN($A:$E)),""),INT(ROW(A2)/2))))),"")

再减几个
=IFERROR(IF(MOD(ROW(),2),OFFSET($B$1,MATCH(K$4,$B:$B,)-2,6-MOD(LARGE(IFERROR(--(FIND(K$4&$I$5,$B$5:$B$35&$C$4:$G$34)*$C$5:$G$35&{5,4,3,2,1}),""),ROW(B2)/2),10)),LARGE(IF($I$5=IF($B$4:$B$35=K$4,$C$4:$G$35),$C$5:$G$36),ROW(B1)/2)),"")

第二题区域数组进化
=TRIM(MID(CONCAT(IFERROR(TRANSPOSE(IF(-K5:N14<0,K4:N4&-ROW(1:10)/2&REPT(" ",99))),"")),ROW(1:18)*99-98,99))
第二题区域数组

=IFERROR(INDEX(K4:N4,,SMALL(IFERROR(--IF(-K5:N14<0,{1,2,3,4}),""),ROW(1:18)))&"-"&RIGHT(SMALL(IFERROR(IF(-K5:N14<0,{1,2,3,4}*10+ROW(1:10)/2),""),ROW(1:18))),"")









评分

13

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-2-23 20:04 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 太阳之子 于 2020-3-2 20:50 编辑

第一题,公式1,单数组,173字符,返回的数字为数值型;
公式2,单数组,166字符,返回的数字为文本型,会影响第二题计算。如果不用常量数组,{3,4,5,6,7}改成COLUMN($B:$G),增加2字符,为168字符。
  1. =IFERROR(INDIRECT(TEXT(SMALL(IF(($B$5:$B34=K$4)*($C$5:$G34=$I$5),TIME(,IF(D4&9<7&0,MATCH(K$4,$B:$B,)-1,ROW($6:35)),COLUMN($C:$G))+60-$C$6:$G35),INT(ROW(A2)/2)),"RMCS"),),"")
  2. =INDIRECT(TEXT(SMALL(60+IF(($B$5:$B34=K$4)*($C$5:$G34=$I$5),TIME(,IF(D4&9<7&0,MATCH(K$4,$B:$B,)-1,ROW($6:35)),{3,4,5,6,7})-$C$6:$G35,4/7),INT(ROW(A2)/2)),"RMCS"),)&""
复制代码


第二题,公式1,列数组,136字符。公式2,列数组,132字符
  1. =IFERROR(LOOKUP(ROW()-5,SUBTOTAL(2,OFFSET(J:J,,,,COLUMN(A:D))),K4:N4)&-MOD(SMALL(IF(K6:N14<99,ROW(2:10)/2+COLUMN(A:D)*6),ROW()-4),6),"")
  2. =IFERROR(LOOKUP(ROW()-5,SUBTOTAL(2,OFFSET(J:J,,,,{1,2,3,4})),K4:N4)&-MOD(SMALL(IF(K6:N14<99,ROW(2:10)/2+{1,2,3,4}*9),ROW()-4),9),"")
复制代码








评分

12

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-2-24 14:53 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 江苏永能朱兴华 于 2020-2-25 15:42 编辑

第一题:
=IFERROR(INDIRECT(TEXT(SMALL(IF($B$5:$B34&$C$5:$G34=K$4&$I$5,60-$C$6:$G35+TIME(,{3,4,5,6,7},IF(-1^ROW()=1,ROW($6:35),MATCH(K$4,$B:$B,)-1)),99),ROW(A2)/2),"RSCM"),),"")

=IFERROR(INDIRECT(TEXT(SMALL(IF($B$5:$B34&$C$5:$G34=K$4&$I$5,60-$C$6:$G35+TIME({3,4,5,6,7},,IF(-1^ROW()=1,ROW($6:35),MATCH(K$4,$B:$B,)-1)),99),ROW(A2)/2),"RSCh"),),"")


第二题不知这样写算不算:

=REPT(LOOKUP("鲜",K$4:O$4&-1/(1/(0&ROW(A1)-SUBTOTAL(2,OFFSET(J$5,,,10,COLUMN(A:E)))))),ROW()-5<COUNT(K:N))

=TEXT(LOOKUP("鲜",K$4:O$4&-1/(1/(0&ROW(A1)-SUBTOTAL(2,OFFSET(J$5,,,10,COLUMN(A:E)))))),";;")













评分

11

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-2-28 22:23 | 显示全部楼层
本帖最后由 sayhi95 于 2020-2-29 10:36 编辑

题一,170以内好难,先交,175
  1. =IFERROR(INDIRECT(TEXT(SMALL(IF(($B$5:$B34=K$4)*($C$5:$G34=$I$5),99-$C$6:$G35+TIME(,{3,4,5,6,7},IF(MOD(ROW(),2),MATCH(K$4,$B:$B,)-1,ROW($6:35)))),INT(ROW(A2)/2)),"RsCm"),),"")
复制代码
强行改到166
  1. =INDIRECT(TEXT(SMALL(IF(($B$5:$B34=K$4)*($C$5:$G34=$I$5),99-$C$6:$G35+TIME(,{3,4,5,6,7},IF(MOD(ROW(),2),MATCH(K$4,$B:$B,)-1,ROW($6:35))),9/7%),ROW(A2)/2),"RsCm"),)&""
复制代码
题二,严格遵守这一条“第二题的数据源为B:G列和K4:N14”不引用不在这两个区域内的任意单元格,116
  1. =IF(ROW(B1)>COUNT(K$5:N$14),"",LOOKUP("唑",K$4:N$4&-TEXT(ROW(B1)-1%-SUBTOTAL(2,OFFSET(K$6,,-1,9,{1,2,3,4,5})),"0;")))
复制代码
借两个单元格投机,95
  1. =TEXT(LOOKUP("唑",K$4:O$4&-TEXT(ROW(A1)-1%-SUBTOTAL(2,OFFSET(J$6,,,9,{1,2,3,4,5})),"0;")),";;@")
复制代码
题二与题一166解法配套方案1,130
  1. =IF(ROW(B1)>COUNTIF(K$5:N$14,"><")/2,"",LOOKUP("唑",K$4:N$4&-TEXT(ROW(B1)-1%-COUNTIF(OFFSET(K$5,,-1,10,{1,2,3,4,5}),"><")/2,"0;")))
复制代码
配套方案2,100
  1. =TEXT(LOOKUP("唑",K$4:O$4&-TEXT(ROW(B1)-1%-COUNTIF(OFFSET(J$5,,,10,{1,2,3,4,5}),"><")/2,"0;")),";;@")
复制代码

评分

11

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-3-3 21:08 | 显示全部楼层
本帖最后由 wodewan 于 2020-3-5 09:38 编辑

第一题:166字(K5,右拉下拉)
=INDIRECT(TEXT(99+SMALL(IF($C$4:$G$34&$B$4:$B$34=$I$5&K$4,TIME(,COLUMN($C:$G),IF(MOD(ROW(),2),MATCH(K$4,$B:$B,)-1,ROW($5:$35)))-$C$5:$G$35,1%),ROW(A2)/2),"RSCM"),)&""

第二题:175字(区域数组,超字数了)
=TEXT(INDEX(T(OFFSET(J4,,ROW(1:88)/10))&-MOD(ROW(1:88),10),SMALL(--IF(FREQUENCY(IF(C1:G34=I5,MATCH(B1:B34,K4:N4,)),ROW(1:3))<COLUMN(A:E),80,ROW(1:4)&COLUMN(A:E)),ROW()-4)),"")

评分

8

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-3-16 15:10 | 显示全部楼层
本帖最后由 mmlzh 于 2020-3-19 17:53 编辑

=INDIRECT(TEXT(MOD(SMALL(IF($B$4:$B34&$C$4:$G34=K$4&$I$5,(IF(ISODD(ROW()),MATCH(K$4,$B:$B,)-1,ROW($5:35))&COLUMN($C:$G))-$C$5:$G35/1%%,99),ROW(A2)/2),10^3),"r0c0"),)&""


=TEXT(LOOKUP("球",K$4:O$4&-(0&ROW()-MMULT(MMULT({1,1,1,1,1},COUNTIFS(OFFSET(B:B,,ROW($1:$5)),I$5,B:B,K$4:N$4)),N(ROW($1:$4)<COLUMN(A:E)))-5)-1),";")
=TEXT(LOOKUP("球",K$4:O$4&-(0&ROW()-COUNTIF(OFFSET(J$5:J$14,,,,COLUMN(A:E)),">""")/2-5)-1),";")

评分

10

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-3-18 20:16 | 显示全部楼层
本帖最后由 我走我流 于 2020-3-23 10:20 编辑

回来太晚,占个楼,学习下楼上各位大神,,先来第1问,都是严重超标的
=IFERROR(IF(ISODD(ROW(A2)),LARGE(IF(($B$5:$B$33=K$4)*($C$5:$G$33=$I$5),$C$6:$G$34),INT(ROW(A2)/2)),INDEX($A:$G,MATCH(K$4,$B:$B,)-1,1/MOD(LARGE(IF(($B$5:$B$33=K$4)*($C$5:$G$33=$I$5),$C$6:$G$34+1/COLUMN($C:$G)),INT(ROW(A2)/2)),1))),"")
  233字符

=IFERROR(INDIRECT(TEXT(RIGHT(LARGE(IF(($B$5:$B33=K$4)*($C$5:$G33=$I$5),$C$6:$G34/1%-COLUMN($C:$G)+IF(MOD(ROW(A1),2),MATCH(K$4,$B:$B,)-1,ROW($6:34))%+COLUMN($C:$G)%%),INT(ROW(A2)/2)),4),"R0C00"),),"")
199字符,,终于混到170了。。。。
=IFERROR(INDIRECT(TEXT(SMALL(IF(($B$5:$B33&$C$5:$G33=K$4&$I$5),TIME(50-$C$6:$G34,{3,4,5,6,7},IF(-1^ROW()<0,MATCH(K$4,$B:$B,)-1,ROW($6:34)))),INT(ROW(A2)/2)),"rScm"),),"")

第2问开个长火车-355字
=IFERROR(INDEX(K$4:N$4,MATCH(1=1,ROW(A1)<=MMULT(N(ROW($1:$4)>=COLUMN(A:D)),MMULT(COUNTIFS(OFFSET(B$5:B$34,,COLUMN(A:E)),$I$5,B$5:B$34,TRANSPOSE(K$4:N$4)),ROW(1:5)^0)),))&-COUNTIF(Q4:Q$4,INDEX(K$4:N$4,MATCH(1=1,ROW(A1)<=MMULT(N(ROW($1:$4)>=COLUMN(A:D)),MMULT(COUNTIFS(OFFSET(B$5:B$34,,COLUMN(A:E)),$I$5,B$5:B$34,TRANSPOSE(K$4:N$4)),ROW(1:5)^0)),))&"*")-1,"")
=CONCAT(TEXT(SMALL(IF(N(INDIRECT("r"&ROW($3:7)*2&"c"&COLUMN(K:N),)),ROW($1:5)+{1,2,3,4}*10,99),ROW(A1)),"[="&{1,2,3,4}&ROW($1:5)&"]"&K$4:N$4&-ROW($1:5)&";"))   
再减去几个字符,还是超标
=CONCAT(TEXT(SMALL(IF(SUBTOTAL(4,OFFSET(J$4,ROW($1:5)*2,{1,2,3,4})),ROW($1:5)+{1,2,3,4}*10,99),ROW(A1)),"[="&{1,2,3,4}&ROW($1:5)&"]"&K$4:N$4&-ROW($1:5)&";"))  

  140字
=IFNA(HLOOKUP("*",K$4:N4&(0&COUNTIF(OFFSET(J:J,,{1,2,3,4}),">0")-COUNTIF(Q$4:Q4,K$4:N$4&"*")-1)-COUNTIF(OFFSET(J:J,,{1,2,3,4}),">0"),1,),"")






评分

11

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-3-18 23:46 | 显示全部楼层
题1
=INDIRECT(TEXT(LARGE(IF($B$4:$B34&$C$4:$G34=K$4&$I$5,$C$5:$G35+TIME({5,4,3,2,1},IF(ISODD(ROW()),MATCH(K$4,$B:$B,)-1,ROW($5:35)),{3,4,5,6,7}),1%),INT(ROW(A2)/2)),"rmcs"),)&""
硬减几个字符凑数
=INDIRECT(TEXT(LARGE(IF($B:$B&$C:$G=K$4&$I$5,$C$2:$G35+TIME({5,4,3,2,1},IF(ISODD(ROW()),MATCH(K$4,$B:$B,)-1,ROW($2:35)),{3,4,5,6,7}),1%),INT(ROW(A2)/2)),"rmcs"),)&""

=INDIRECT(TEXT(MOD(SMALL(IF($B$4:$B34&$C$4:$G34=K$4&$I$5,(IF(MOD(ROW(),2),MATCH(K$4,$B:$B,)-1,ROW($5:35))&{3,4,5,6,7})-$C$5:$G35/1%%,99),ROW(A2)/2),1000),"r0c0"),)&""

题2
=IFERROR(INDEX(T(OFFSET(J4,,ROW(5:24)/5))&-MOD(ROW(5:24),5)-1,SMALL(IF((K5:N13>"")*MOD(ROW(1:9),2),ROW(2:10)/2+{0,1,2,3}*5),ROW()-4)),"")

评分

10

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2020-3-29 11:21 | 显示全部楼层
先开贴,请以上楼层不要再编辑,以免影响评分。

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2020-3-30 09:10 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 delete_007 于 2020-3-30 09:11 编辑

如楼评分如下,如有疑议,请与我联系。(财富奖励,酌情添加,未在表格中反应。)

本帖子中包含更多资源

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

x

评分

10

查看全部评分

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

本版积分规则

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

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

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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