内存数组八之足球先生

solextrade

内存数组八之足球先生

其实这道题出自竞赛题之前，但在评选竞赛题时被PK下来了，发上来老师们热闹热闹。
题目说明有点繁琐了，可是没想到更简练的说明方法，大家对照答案，凑合着看吧。
足球先生.rar (12.4 KB, 下载次数: 34)

2020-2-28 20:54 上传

点击文件名下载附件

sbdk007

小罗老师，答题会不会影响竞赛的结果

solextrade

sbdk007 发表于 2020-2-28 20:18
小罗老师，答题会不会影响竞赛的结果

不会的，不一样的套路
不好意思各位，没注意把模拟结果的进球数量显示为2位小数了，请调整至显示整数。

王嘉玲

看不懂，眼花得很。不是说欧冠大于联赛吗？怎么姆巴佩的联赛跟欧冠都是27，却又选择了联赛？

micch

1. =INDEX(A:C,SECOND(MIN(IF(COUNTIFS(E$1:E1,LEFT(A$2:A$23,3)&"*",F$1:F1,B$2:B$23)=0,INT(ROW($1:$22)/8)/1%%+3.1^("欧冠"=RIGHT(A$2:A$23,2))*C$2:C$23*10+MMULT(SUMIFS(C:C,A:A,LEFT(A$2:A$23)&{"*联赛","*欧冠"},B:B,B$2:B$23),1/{48;1440})+ROW($2:$23)%/864))),)

复制代码

E2:G2

据说先发答案的都死得快，所以还是我先死吧。

sbdk007

小罗老师这题的坑真不少，开个火车450个字符
=INDEX(A:A,MOD(SMALL(IF(FREQUENCY(MATCH($B$2:$B$23,$B$2:$B$23,)*10+MATCH(LEFT($A$2:$A$23,2)&"*",$A$2:$A$23,),MATCH($B$2:$B$23,$B$2:$B$23,)*10+MATCH(LEFT($A$2:$A$23,2)&"*",$A$2:$A$23,)+$C$2:$C$23%*3.01^(RIGHT($A$2:$A$23)="冠")),MATCH($B$2:$B$23,$B$2:$B$23,)*10^8+$C$2:$C$23*10^6*3.01^(RIGHT($A$2:$A$23)="冠")+TRANSPOSE(MMULT(COLUMN(A:V)^0,(LEFT($A$2:$A$23)&$B$2:$B$23=TRANSPOSE(LEFT($A$2:$A$23)&$B$2:$B$23))*$C$2:$C$23))/1%+ROW($2:$23)),ROW(A1)),100))

sbdk007

再减一下，400
=INDEX(A:A,MOD(SMALL(IFERROR(IF(FREQUENCY(ROW($10:$200),MATCH($B$2:$B$23,$B$2:$B$23,)*10+MATCH(LEFT($A$2:$A$23,2)&"*",$A$2:$A$23,)+$C$2:$C$23%*3.01^(RIGHT($A$2:$A$23)="冠")),MATCH($B$2:$B$23,$B$2:$B$23,)*10^8+$C$2:$C$23*10^6*3.01^(RIGHT($A$2:$A$23)="冠")+MMULT((LEFT($A$2:$A$23)&$B$2:$B$23=TRANSPOSE(LEFT($A$2:$A$23)&$B$2:$B$23))*TRANSPOSE($C$2:$C$23),ROW(1:22)^0)/1%+ROW($2:$23)),1=0),ROW(A1)),100))

micch

简炼说？每赛季每人选出一个数据，按进球从少到多排序？联赛/3和欧冠比较，相同进球，比联赛，联赛还相同比欧冠？

好像昨天回了呀？居然看不到？关禁闭了？

sbdk007

这审核也太久了吧，再简化一下
=INDEX(A:A,MOD(SMALL(IFERROR(IF(FREQUENCY(ROW($10:$200),MATCH($B$2:$B$23,$B:$B,)*10+MATCH(LEFT($A$2:$A$23,2)&"*",$A:$A,)+$C$2:$C$23%*3.01^(RIGHT($A$2:$A$23)="冠")),MATCH($B$2:$B$23,$B:$B,)*10^8+$C$2:$C$23*10^6*3.01^(RIGHT($A$2:$A$23)="冠")+SUMIFS($C:$C,$A:$A,LEFT($A$2:$A$23,2)&"*",$B:$B,$B$2:$B$23)/1%+ROW($2:$23)),1=0),ROW(A1)),100))

sbdk007

micch 发表于 2020-2-29 08:24
简炼说？每赛季每人选出一个数据，按进球从少到多排序？联赛/3和欧冠比较，相同进球，比联赛，联赛还相同比 ...

我还以为我是第一个开火车的，没想到M老师的精妙公式早出来了