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

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))

复制代码

hsw212

辅助列实现。以后如果能想出不用辅助列的再来更改。

丢丢表格

先做一个 201 字的
H24=SUM(N(OFFSET(B$10,,MOD(MATCH(LARGE(IF($C$14:$C$31=G24,B$14:B$31*100+D$14:D$31),{1,2,3}),LARGE($B$14:$B$31*100+(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))))

H24:H29=MMULT(N(OFFSET(B10,,MOD(MATCH(MOD(-LARGE(-(CODE(C14:C31)&B14:B31&D14:D31),ROW(1:6)*3-{2,1,0}),1000),LARGE((MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)*D14:D31+B14:B31/1%,A14:A31),)-1,6))),{1;1;1})
  202 字

   用 CODE  是不严谨的, 不知楼主是否可用?
      不用 CODE 的
H24:H29=MMULT(N(OFFSET(B10,,MOD(MATCH(MOD(-LARGE(-(MATCH(C14:C31,G24:G29,)&B14:B31&D14:D31),ROW(1:6)*3-{2,1,0}),1000),LARGE((MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)*D14:D31+B14:B31/1%,A14:A31),)-1,6))),{1;1;1})
   212 字




补充内容 (2014-10-5 11:03):
=MMULT(N(OFFSET(B10,,MOD(MATCH(MOD(SMALL(MATCH(C14:C31,G24:G29,)*10^5+B14:B31*10^3+D14:D31,A14:A19*3-{2,1,0}),10^5),LARGE((MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)*D14:D31+B14:B31*10^3,A14:A31

补充内容 (2014-10-5 11:04):
=MMULT(N(OFFSET(B10,,MOD(MATCH(MOD(SMALL(MATCH(C14:C31,G24:G29,)*10^5+B14:B31*10^3+D14:D31,A14:A19*3-{2,1,0}),10^5),LARGE((MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)*D14:D31+B14:B31*10^3,A14:A31),)-1,6))),{1;1;1})

hsl215

辅助列公式93字符

1. =SUM(IF(ISNA(LOOKUP(IF(C$14:C$31=G24,E$14:E$31),A$9:H$10)),,LOOKUP(IF(C$14:C$31=G24,E$14:E$31),A$9:H$10)))

复制代码

评分公式106字符

1. =SUM(IF(ISNA(LOOKUP(IF(C$14:C$31=G24,E$14:E$31),A$9:H$10)),,LOOKUP(IF(C$14:C$31=G24,E$14:E$31),A$9:H$10)))

复制代码

hsl215

没权限编辑不了，只能重新发一个贴了，暂时只能用辅助列做出来了，
辅助列公式

1. =SUM(--(FREQUENCY((B$14:B$31=B14)*D$14:D$31,IF((B$14:B$31=B14)*D$14:D$31>=D14,D$14:D$31))>0))

复制代码

评分公式

1. =SUM(IF(ISNA(LOOKUP(IF(C$14:C$31=G24,E$14:E$31),A$9:G$10)),,LOOKUP(IF(C$14:C$31=G24,E$14:E$31),A$9:G$10)))

复制代码

yqzxxcz

1. =SUM(LOOKUP(MMULT(N(N(OFFSET(D$13,MATCH({1;2;3}&G24,RIGHT(B$14:B$31)&C$14:C$31,),))<=TRANSPOSE((--RIGHT(B$14:B$31)={1,2,3})*IF(MATCH(B$14:B$31&D$14:D$31,B$14:B$31&D$14:D$31,)=ROW($1:$18),D$14:D$31))),ROW($1:$18)^0),ROW($1:$6),B$10:G$10))

复制代码

yqzxxcz

忘记上传附件了