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

jacky1998

1. =MMULT(--(TRANSPOSE(C14:C31)=G24:G29),N(OFFSET(A10,,MMULT(1-ISNA(MATCH(COLUMN(A:R)+ROW(1:18)%,LARGE((TRANSPOSE(D14:D31)<=D14:D31)*(TRANSPOSE(B14:B31)=B14:B31)*(MATCH(D14:D31,D14:D31,)+COLUMN(A:R)%),ROW(1:324)),)),ROW(1:18)^0))))

复制代码

内存数组，不好意思，超长啦。

1. =MMULT(N(TRANSPOSE(C14:C31)=G24:G29),N(OFFSET(A10,,MMULT(1-ISNA(MATCH(D14:D31+COLUMN(A:CW)-1+ROW(1:18)%+(B14:B31)%%,SMALL(D14:D31+COLUMN(A:R)%+(B14:B31)%%,ROW(1:324)),)),ROW(1:101)^0))))
   

复制代码

13732120571

12楼不能在编辑，特在这里再发一贴
内存数组，185字符

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

复制代码

200240

看能否混到一分。用三个辅助列，再加一个超长公式。

xianxing110a

sweetian

很勉强地算是完成了吧

1. =MMULT(N(G24:G29=TRANSPOSE(C14:C31)),TRANSPOSE(LOOKUP(MMULT(1^COLUMN(A:R),(D14:D31>TRANSPOSE(D14:D31))*(B14:B31=TRANSPOSE(B14:B31))/MMULT(N(D14:D31+B14:B31*1000=TRANSPOSE(D14:D31+B14:B31*1000)),1^ROW(1:18)))+1,B9:G9,B10:G10)))

复制代码

xianxing110a

1. =SUM((MMULT((TRANSPOSE(B$14:B$31)=B$14:B$31)*(TRANSPOSE((MATCH(B$14:B$31&D$14:D$31,B:B&D:D,)-13=ROW($1:$18))*D$14:D$31)>=IF(C$14:C$31=$G24,D$14:D$31)),ROW($1:$18)^0)=B$9:G$9)*B$10:G$10)

复制代码

精简了下公式

xianxing110a

1. =SUM((MMULT((TRANSPOSE(B$14:B$31)=B$14:B$31)*(TRANSPOSE((MATCH(B$14:B$31&D$14:D$31,B:B&D:D,)=ROW($14:$31))*D$14:D$31)>=IF(C$14:C$31=$G24,D$14:D$31)),ROW($1:$18)^0)=B$9:G$9)*B$10:G$10)

复制代码

上面match写的多余了点

---

整列引用，公式可能出错，稍做修改即可得正确答案。测试数据见评分附件。
=SUM((MMULT((TRANSPOSE(B$14:B$31)=B$14:B$31)*(TRANSPOSE((MATCH(B$14:B$31&D$14:D$31,B$14:B$31&D$14:D$31,)=A$14:A$31)*D$14:D$31)>=IF(C$14:C$31=$G24,D$14:D$31)),ROW($1:$18)^0)=B$9:G$9)*B$10:G$10)
——delete_007

笑着...两年过去

H24下拉

1. =SUM(N(OFFSET(A$10,,(C$14:C$31=G24)*(MMULT((TRANSPOSE(D$14:D$31)>D$14:D$31)*(B$14:B$31=TRANSPOSE(B$14:B$31))/MMULT(COLUMN(A:R)^0,N(B$14:B$31&D$14:D$31=TRANSPOSE(B$14:B$31&D$14:D$31))),A$14:A$31^0)+1))))

复制代码

H24:H29区域数组

1. =TRANSPOSE(MMULT(COLUMN(A:R)^0,SUMIF(9:9,(C14:C31=TRANSPOSE(G24:G29))*(MMULT((TRANSPOSE(D14:D31)>D14:D31)*(B14:B31=TRANSPOSE(B14:B31))/MMULT(COLUMN(A:R)^0,N(B14:B31&D14:D31=TRANSPOSE(B14:B31&D14:D31))),A14:A31^0)+1),10:10)))

复制代码

xia125602

H24=SUM(N(OFFSET(A$10,,MMULT(--(TRANSPOSE((B$14:B$31={1,2,3})*(MATCH(B$14:B$31&D$14:D$31,B$14:B$31&D$14:D$31,)=ROW($1:$18))*D$14:D$31-N(OFFSET(D$13,MATCH({1,2,3}&G24,B$14:B$31&C$14:C$31,),)))>=0),ROW($1:$18)^0))))  下拉

jsxjd

H24数组公式下拉，182 chars:

1. =SUM(LOOKUP(MOD(MATCH(B$14:B$31/1%%+D$14:D$31,LARGE(B$14:B$31/1%%+(MATCH(B$14:B$31&D$14:D$31,B$14:B$31&D$14:D$31,)=A$14:A$31)*D$14:D$31,A$14:A$31),)-1,6)+1,B$9:G$10)*(C$14:C$31=G24))

复制代码