ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 23982|回复: 79

[Excel 函数与公式] [第109期]综合评分问题(二)(已开贴)

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2014-9-11 10:00 | 显示全部楼层 |阅读模式
本帖最后由 delete_007 于 2014-10-21 15:10 编辑

题目:明细数据为某项目组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







单选投票, 共有 31 人参与投票

投票已经结束

19.35% (6)
67.74% (21)
9.68% (3)
3.23% (1)
您所在的用户组没有投票权限

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

评分

11

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-9-11 14:03 | 显示全部楼层
占个位先,吼吼!!!


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

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-9-11 14:58 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 cleverzhzhf 于 2014-9-11 16:32 编辑

占位,拿一分:
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))
复制代码

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

点评

还是传个附件吧,我看起来方便点。  发表于 2014-9-11 16:04

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-9-11 21:13 | 显示全部楼层
本帖最后由 delete_007 于 2014-9-16 11:32 编辑
  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)))
复制代码
``

点评

学习了下,不怎么透彻,发现还是利用了数据的特点,比如有小数时,应该会出错吧……  发表于 2014-11-21 19:49
这两个字符就别省了。  发表于 2014-9-12 08:21

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-9-11 23:35 | 显示全部楼层
本帖最后由 delete_007 于 2014-10-10 10:08 编辑

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))
复制代码

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-9-12 09:33 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 wlc5201130 于 2014-9-19 11:32 编辑

能得出正确结果就能得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))

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-9-12 09:55 | 显示全部楼层

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

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-9-12 11:10 | 显示全部楼层
本帖最后由 chunlin1wang 于 2014-9-12 11:12 编辑
  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))
复制代码

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-9-12 12:43 | 显示全部楼层
本帖最后由 delete_007 于 2014-9-12 13:43 编辑
  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))

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-9-12 13:40 | 显示全部楼层
本帖最后由 看见星光 于 2014-9-14 15:17 编辑

先占个坑,第一次参赛,好歹看看能不能混一分。。。
用辅助列的公式,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})))))
复制代码


本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

评分

3

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-7-13 21:38 , Processed in 0.052598 second(s), 12 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表