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

delete_007

题目：明细数据为某项目组6个成员的三个项目的测试结果得分(每个成员都参加测试，不考虑缺考情况)。

---

               请依据评分标准按各个成员在各项目内的中国式排名进行评分，并将结果进行汇总。
目标：通过“明细数据”根据“评分标准”计算各个成员的实际累计评分。

题目要求：
    1、所有得分可能存在排名并列的情况，请答题者链接到以下位置查看题目分析，以便于解答者理解答题要求。
    2、所有计算结果采用函数公式完成，不能使用函数以外的其他方法（如VBA）。
    3、不允许使用定义名称。
    4、以上数据中，“A9:G10、A13:D31和G23:G29”区域可以在公式中直接引用，其他范围视为辅助区。
    5、函数公式需通过2003版本EXCEL验证。

结果评分：
    1、在解答中使用辅助单元得出正确结果者，评1分。
    2、仅在“H24:H29区域”直接通过公式得出累计评分且公式长度小于250的，评2分。
    3、仅在“H24:H29区域”直接通过公式得出累计评分且结果为内存数组且公式长度小于190字符的，起评分为3分。




竞赛日期：2014-9-11至2014-10-10

zxh361

占个位先，吼吼！！！


补充内容 (2014-10-22 08:56):
不是有意占楼，现在才发现有个补充功能。

cleverzhzhf

占位，拿一分：
E14数组公式，下拖：

1. =IF(MATCH(B14&D14,$B$14:$B$31&$D$14:$D$31,)=ROW()-13,D14,)

复制代码

F14数组公式，下拖：

1. =HLOOKUP(SUM(($E$14:$E$31>=D14)*($B$14:$B$31=B14)),$B$9:$G$10,2,)

复制代码

H24普通公式，下拖：

1. =SUMIF(C:C,G24,F:F)

复制代码

---

H24:H29多单元格数组，188字符：

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

复制代码

opqazxc

1. =MMULT(N(I24:I29=TRANSPOSE(C14:C31)),LARGE(A10:G10,MMULT((B14:B31=TRANSPOSE(B14:B31))*(D14:D31<=TRANSPOSE(D14:D31)),N(MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31))))

复制代码

``
如果LARGE（A10:G10，
改为large(10:10,会不会视为辅助区域呢？

如果不会我就想再省2字符``

---

1. =MMULT(N(I24:I29=TRANSPOSE(C14:C31)),N(OFFSET(B10,,MMULT(N(MMULT(N(B14:B31=TRANSPOSE(B14:B31)),N(D14:D31=COLUMN(A:CV)))*(D14:D31<COLUMN(A:CV))>0),ROW(1:100)^0))))

复制代码

1. =MMULT(N(G24:G29=TRANSPOSE(C14:C31)),N(OFFSET(B10,,MMULT(N(MMULT(N(B14:B31=TRANSPOSE(B14:B31)),N(D14:D31=COLUMN(1:1)))*(D14:D31<COLUMN(1:1))>0),ROW(1:256)^0))))

复制代码

补充内容 (2014-9-19 23:41):
补充说明下：前两个公式由于写的时候让我移动了下所以I24：I29应该改为G24：G29``

补充内容 (2014-9-19 23:45):
再减一字符``

1. =MMULT(N(G24:G29=TRANSPOSE(C14:C31)),LARGE(B10:G10,MMULT((MMULT(N(B14:B31=TRANSPOSE(B14:B31)),N(D14:D31=COLUMN(1:1)))>0)*(D14:D31<=COLUMN(1:1)),ROW(1:256)^0)))

复制代码

``

ys19840718

204字符，h24单元格三键输入下拉

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

复制代码

wlc5201130

能得出正确结果就能得1分是吧。那就先弄1分在说。
法1（364字符)
=SUM(N(OFFSET(A$10,,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),),ROW($1:$18)),)-MMULT((B$14:B$31=TRANSPOSE(B$19:B$20))*MATCH(TRANSPOSE(B$22:B$23)/1%,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),),ROW($1:$18)),-1),{1;1})))*(C$14:C$31=G24))
数组下拉

法2  （214字符)
=SUM(N(OFFSET(A$10,,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),),ROW($1:$18)),)-IF(B$14:B$31=1,9,IF(B$14:B$31=2,5,))))*(C$14:C$31=G24))
此公式关键用了常量9和5，不知道符不符合要求。
数组下拉

法3  区域数组(208)
=MMULT(TRANSPOSE(N(OFFSET(A10,,MATCH(B14:B31&D14:D31,""&LARGE((MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)*(B14:B31&D14:D31),A14:A31),)-TEXT(B14:B31-2,"!0;9;5"))))*(TRANSPOSE(C14:C31)=G24:G29),A14:A31^0)

补充内容 (2014-10-2 12:07):
如果得分存在相同的情况话  326字符
=SUM(N(OFFSET(A$10,,MATCH(--(B$14:B$31&D$14:D$31),LARGE((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),)-MMULT((B$14:B$

补充内容 (2014-10-2 12:08):
$31={2,1})*MATCH({3000,2000},LARGE((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*1000+D$14:D$31),A$14:A$31),-1),{1;1})))*(C$14:C$31=G24))

oyzhjr

先用辅助列做一个吧，回头再改。

chunlin1wang

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

复制代码

danny-fei

1. =SUM(INDEX(B$10:G$10,N(IF(1,MMULT(1/MMULT(COLUMN(A:R)^0,(B$14:B$31=TRANSPOSE(B$14:B$31))*(D$14:D$31=TRANSPOSE(D$14:D$31)))*(D$14:D$31<=TRANSPOSE(D$14:D$31))*(B$14:B$31=TRANSPOSE(B$14:B$31)),ROW(1:18)^0))))*(C$14:C$31=G24))

复制代码

因为存在回复后不能编辑的BUG，所以烦请将 4F，5F, 本楼层合并到一起，

经过努力，终于完成，187字符，H24:H29多单元格数组公式，

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

复制代码

补充内容 (2014-9-15 12:54):
最新公式，169字符：=MMULT(N(TRANSPOSE(C14:C31)=G24:G29),LOOKUP(MMULT((D14:D31<=TRANSPOSE(D14:D31))*(B14:B31=TRANSPOSE(B14:B31)),N(MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)),B9:G10))

看见星光

先占个坑，第一次参赛，好歹看看能不能混一分。。。
用辅助列的公式,E14下拉：

1. =OFFSET(A$10,,MATCH(D14,LARGE(IF(FREQUENCY(IF($B$14:$B$31=B14,$D$14:$D$31),$D$14:$D$31),$D$14:$D$31),ROW($1:$6)),))

复制代码

结果公式：

1. =SUMIF($C$14:$D$31,G24,$E$14)

复制代码

不用辅助列的公式。。不管对错投了吧，真该好好学习天天向上了，不该瞎折腾。
（245）：

1. =SUM(OFFSET(A$10,,INDEX(FREQUENCY(IF(MATCH(B$14:B$31&D$14:D$31,B$14:B$31&D$14:D$31,)=A$14:A$31,MATCH(B$14:B$31,B$14:B$31,)/1%%+D$14:D$31),SMALL(IF(C$14:C$31=G24,MATCH(B$14:B$31,B$14:B$31,)/1%%+D$14:D$31),{1;2;3})-{500,0})+1,N(IF({1},{3;5;7})))))

复制代码

奇怪，为什么有的版本OFFSET不套N可以用SUM求和，有的必须用。我本来以为里面使用了***，所以不用再加N了，但测试有的版本还是有问题。那加上，247：

1. =SUM(N(OFFSET(A$10,,INDEX(FREQUENCY(IF(MATCH(B$14:B$31&D$14:D$31,B$14:B$31&D$14:D$31,)=A$14:A$31,MATCH(B$14:B$31,B$14:B$31,)/1%%+D$14:D$31),SMALL(IF(C$14:C$31=G24,MATCH(B$14:B$31,B$14:B$31,)/1%%+D$14:D$31),{1;2;3})-{500,0})+1,N(IF({1},{3;5;7}))))))

复制代码

再简一下~209（取巧啦，这么多机关，应该是允许这样操作的吧，如果不允许，那还是按上面的为准吧，嘻嘻）：

1. =SUM(OFFSET(A$10,,INDEX(FREQUENCY(IF(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/1%%+D$14:D$31),SMALL(IF(C$14:C$31=G24,B$14:B$31/1%%+D$14:D$31),{1;2;3})-{500,0})+1,N(IF({1},{3;5;7})))))

复制代码