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

天地有雪

不知道有没有理解错题目，每分都做了！应该能拿一分吧！1分答卷：在附件中，E14:E31为辅助列
2分答卷：数组下拉

1. {=SUM(LOOKUP(MMULT(N(B$14:B$31&D$14:D$31<=TRANSPOSE((MATCH(B$14:B$31&D$14:D$31,B$14:B$31&D$14:D$31,)=A$14:A$31)*B$14:B$31&D$14:D$31)),A$14:A$31^0)-CHOOSE(B$14:B$31,9,5,0),B$9:G$10)*(C$14:C$31=G24))}

复制代码

3分答卷：H24:H29多单元格数组

1. {=MMULT(N(G24:G29=TRANSPOSE(C14:C31)),LOOKUP(MMULT(N(B14:B31&D14:D31<=TRANSPOSE((MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)*B14:B31&D14:D31)),D14:D31^0)-CHOOSE(B14:B31,9,5,0),B9:G10))}

复制代码

---

内存数组公式可改为：=MMULT(N(G24:G29=TRANSPOSE(C14:C31)),LOOKUP(MMULT(N(B14:B31+D14:D31%%<=TRANSPOSE((MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)*D14:D31%%+B14:B31)),D14:D31^0)-CHOOSE(B14:B31,12,6,0),B9:G10))
——delete_007

youonghuman

交卷了^_^

WJX-online

E14:E31

1. =VLOOKUP(N(IF({1},--RIGHT(LARGE(MATCH(B9:D9&G24:G29,B14:B31&C14:C31,)+{3,2,1}/1%%+(7-A14:A19)/1%,A14:A31),2))),A14:D31,4,)+(3-INT((A14:A31-1)/6))/1%

复制代码

多单元格数组。
最后结果处H24:H29

1. =MMULT(LOOKUP(INDEX(MATCH(E14:E31,LARGE(IF(FREQUENCY(E14:E31,E14:E31),E14:E31,(3-INT((ROW(1:19)-1)/6))/1%),A14:A31),)-A14:A31+MOD(A14:A31-1,6)+1,N(IF(1,A14:A19+B9:D9*6-6))),B9:G10),{1;1;1})

复制代码

多单元格数组公式。

zhaojiaoyang200

1. =MMULT(N(TRANSPOSE(C14:C31)=G24:G29),N(OFFSET(A10,,MMULT((B14:B31=TRANSPOSE(B14:B31))*(D14:D31<=TRANSPOSE(D14:D31)),1/MMULT((B14:B31=TRANSPOSE(B14:B31))*(D14:D31=TRANSPOSE(D14:D31)),ROW(1:18)^0)))))

复制代码

188字符，

1. =MMULT(N(TRANSPOSE(C14:C31)=G24:G29),N(OFFSET(A10,,MMULT((B14:B31=TRANSPOSE(B14:B31))*(D14:D31<=TRANSPOSE(D14:D31)),1/MMULT(N(B14:B31%+D14:D31=TRANSPOSE(B14:B31%+D14:D31)),ROW(1:18)^0)))))

复制代码

198字符

极地飞雪007

=MMULT(TRANSPOSE((C14:C32=TRANSPOSE(G24:G29))*SUMIF(B9:G9,MMULT(--(IF((D14:D32<=TRANSPOSE(D14:D32))*(B14:B32=TRANSPOSE(B14:B32)),TRANSPOSE(IF(FREQUENCY(B14:B31/1%+D14:D31,B14:B31/1%+D14:D31)>0,1,0)),0)>0),ROW(1:19)^0),B10:G10)),ROW(1:19)^0)
可得内存数组结果，但长度242

cl63519

=MMULT(N(G24:G29=TRANSPOSE(C14:C31)),N(OFFSET(B10,0,MOD(MATCH(B14:B31/1%+D14:D31,LARGE(IF(FREQUENCY(B14:B31/1%+D14:D31,B14:B31/1%+D14:D31)>0,B14:B32/1%+D14:D32,B14:B32/1%),ROW(1:19)),-1)-1,6))))

193字符，B14:B32多用了一个单元格.

安然祥和

只会这样,。。。。

cl63519

cl63519 发表于 2014-10-6 10:53
=MMULT(N(G24:G29=TRANSPOSE(C14:C31)),N(OFFSET(B10,0,MOD(MATCH(B14:B31/1%+D14:D31,LARGE(IF(FREQUENCY( ...

MMULT(N(G24:G29=TRANSPOSE(C14:C31)),N(OFFSET(B10,,MOD(MATCH(B14:B31/1%+D14:D31,LARGE(IF(FREQUENCY(B14:B31/1%+D14:D31,B14:B31/1%+D14:D31),B14:B32/1%+D14:D32,B14:B32/1%),ROW(1:19)),-1)-1,6))))
刚好190字符，b14:b32多用了一个单元格.

cjh-01

感觉好难，写了个用辅助列的，参与参与！

5blessyou

对于题目要求第四点不是特别明白

4、以上数据中，“A9:G10、A13:D31和G23:G29”区域可以在公式中直接引用，其他范围视为辅助区。

不知道如果使用COLUMN(A:R)这样的区域是否算辅助区域，如果这个也算的话就有点长了

1. =MMULT(N(G24:G29=TRANSPOSE(C14:C31)),LOOKUP(TRANSPOSE(MMULT(TRANSPOSE(1^ROW(A14:A31)),(D14:D31>=TRANSPOSE(D14:D31))*(B14:B31=TRANSPOSE(B14:B31))*(MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31))),B9:G10))

复制代码

不算的话那就这样吧

1. =MMULT(N(G24:G29=TRANSPOSE(C14:C31)),LOOKUP(TRANSPOSE(MMULT(1^COLUMN(A:R),(D14:D31>=TRANSPOSE(D14:D31))*(B14:B31=TRANSPOSE(B14:B31))*(MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31))),B9:G10))

复制代码

整列引用不知可行

1. =MMULT(N(G24:G29=TRANSPOSE(C14:C31)),LOOKUP(TRANSPOSE(MMULT(1^COLUMN(A:R),(D14:D31>=TRANSPOSE(D14:D31))*(B14:B31=TRANSPOSE(B14:B31))*(MATCH(B14:B31&D14:D31,B:B&D:D,)=row(14:31)))),B9:G10))

复制代码