[第109期]综合评分问题（二）（已开贴）

jianbing

1. =SUM((C$14:C$31=G24)*INDEX(INDEX(B$10:G$10,N(IF(1,MOD(ROW($1:$18)-1,6)+1))),N(IF(1,MATCH(B$14:B$31/1%%+D$14:D$31,LARGE(IF(MATCH(B$14:B$31/1%%+D$14:D$31,B$14:B$31/1%%+D$14:D$31,)=ROW($1:$18),B$14:B$31/1%%+D$14:D$31,B$14:B$31/1%%),ROW($1:$18)),)))))
   

复制代码

参与一下吧。几年没接触Excel, 有点转向了。
简化一下

1. =SUM((C$14:C$31=G24)*INDEX(INDEX(B$10:G$10,N(IF(1,MOD(ROW($1:$18)-1,6)+1))),N(IF(1,MATCH(B$14:B$31/1%%+D$14:D$31,LARGE(IF(MATCH(B$14:B$31&D$14:D$31,B$14:B$31&D$14:D$31,)=ROW($1:$18),B$14:B$31/1%%+D$14:D$31,B$14:B$31/1%%),ROW($1:$18)),)))))

复制代码

改变一下思路，内存数组公式，194字符，接近成功 ：）

1. =MMULT(N(G24:G29=TRANSPOSE(C14:C31)),N(OFFSET(A10,,MOD(MATCH(B14:B31/1%%+D14:D31,LARGE(IF(MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=ROW(1:18),B14:B31/1%%+D14:D31,B14:B31/1%%),ROW(1:18)),)-1,6)+1)))

复制代码

终于好好象成功了 :) 185 字符。　结果对了

1. =MMULT(N(G24:G29=TRANSPOSE(C14:C31)),INDEX(B10:G10,MOD(MATCH(B14:B31+D14:D31%%,LARGE(IF(MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=ROW(1:18),B14:B31+D14:D31%%,B14:B31),ROW(1:18)),)-1,6)+1))

复制代码

cmxxccxx

水平较差，所以还是第一次进这个版块答题，不知道是否符合要求

1. =SUM((C$14:C$31=G24)*MMULT(IF(MATCH(B$14:B$31+D$14:D$31%,LARGE(IF(FREQUENCY(B$14:B$31+D$14:D$31%,B$14:B$31+D$14:D$31%),B$14:B$31+D$14:D$31%,B$14:B$32),ROW($1:$18)),)-(3-B$14:B$31)*6=B$9:G$9,B$10:G$10,0),ROW($1:$6)^0))

复制代码

huahua2005

生疏了，练一下~242字符~
H24:K29区域

1. =MMULT(N(G24:G29=TRANSPOSE(C14:C31)),HLOOKUP(MMULT(MATCH(B14:B31/1%+IF({1,0},D14:D31,100*(B14:B31<>3)),LARGE(IF(FREQUENCY(B14:B31/1%+D14:D31,B14:B31/1%+D14:D31),B14:B31/1%+D14:D31),A14:A31),{0,-1})*IF({1,0},1,N(B14:B31<>3)),{1;-1}),B9:G10,2))

复制代码

fugb-2010

=MMULT(LOOKUP(MOD(MATCH(INDEX(D14:D31,MATCH(G24:G29&B9:D9,C14:C31&B14:B31,))%+B9:D9,LARGE((MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)*D14:D31%+B14:B31,A14:A31),)-1,6)+1,B9:G9,B10:G10),{1;1;1})

wangyuhu

都要结束了吧

辅助列复杂了点
E14=HLOOKUP(COUNT(1/(D14<=(MATCH(IF($B$14:$B$31=B14,$D$14:$D$31),IF($B$14:$B$31=B14,$D$14:$D$31,999),)=ROW($1:$18))*$D$14:$D$31)),B$9:G$10,2,)
H24=SUMIF($C$14:$C$31,G24,$E$14:$E$31)

ggsmart

我用辅助列试一下。

lq93808-2

知道竞赛期阴已经过去,但还是想把几天所想的答案上传,评不评分无所谓.但求不扣分就行了.

delete_007

==========竞赛结束，评分稍候==========

delete_007

评分建议如下，如有疑问请与我联系。

ykqrs

1. =MMULT(LOOKUP(MOD(MATCH(B9:D9/1%+MMULT((G24:G29=TRANSPOSE(C14:C31))*TRANSPOSE(D14:D31),N(B14:B31=B9:D9)),LARGE((MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)*D14:D31+B14:B31/1%,A14:A31),)-1,6)+1,B9:G10),{1;1;1})

复制代码

你说怎么就不再闷头想想，重新规划规划，下边这个多好

1. =MMULT(N(TRANSPOSE(C14:C31)=G24:G29),LOOKUP(MOD(MATCH(B14:B31/1%%+D14:D31,LARGE((MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)*D14:D31+B14:B31/1%%,A14:A31),)-1,6)+1,B9:G10))

复制代码