ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[Excel 函数与公式] [开_149]中国式评分排名问题(已总结)

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2014-11-17 08:59 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 看见星光 于 2014-12-22 21:27 编辑

题目:
成绩表为某个中国公司对员工多次考核成绩的统计。
请在答题区计算随机四个员工最大成绩在所有员工最大成绩里的排名(排名遵循中国式排名的规则)
答题要求:
1、成绩表得分最高分不超过150,均为整数,有零,没有空格,不会有负数,(打负数,那还是人咧?)
2、剔除每个员工除最高分外的所有成绩,只按每个员工的最高得分进行排名统计
   最高得分可能存在排名并列的情况,此时按中国式排名规则进行处理。
   请答题者链接到以下位置查看题目分析,以便于解答者理解答题要求。
3,答题区四个人名,均从成绩单里随机抽取。
4、所有计算结果采用函数公式完成,不能使用函数以外的其他方法(如VBA)。
5、不允许使用定义名称。
6、不允许使用辅助列以及对成绩表进行排序等更改操作
7,以上数据中,“A9:C35,F24:F27”数据区域可以在公式中直接引用,其他范围视为辅助区。注意ROW(A:A),COLUMN(A:D)之类,并未实际使用单元格内的数据,不违规。但SUM(A:A)之类,实际使用了单元格内的数据,为违规。
8、函数公式需通过2003版本EXCEL验证。
9、答题区允许使用自定义格式
奖励:
1、公式长度小于等于160得1分
2、其他答案,根据情况奖励财富若干
附件: 星光--再议中国式排名.rar (27.62 KB, 下载次数: 757)

注意竞赛区答题规则:
每个参与竞赛的会员禁止多占楼层,不能多帖回复,即只能有一个回帖。
若需要修改,则在原帖编辑或补充(也可联系7版或我代为编辑)。
否则予以扣财富处罚;严重者取消比赛资格。 害羞2.gif

竞赛日期:2014-11-17至2014-12-12

(点击图片会清晰):
115417kc5wvj8c5889wjpg.gif



评分

16

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-11-17 12:40 | 显示全部楼层
本帖最后由 看见星光 于 2014-11-22 19:26 编辑

我先答吧,答完撒手困觉。138字:
  1. =COUNT(MATCH(MAX((B$10:B$34=F24)*C$10:C$34)+ROW(A:A)-1,(FREQUENCY(A$10:A$34,MATCH(B$10:B$34,B$10:B$34,)+1/(C$10:C$34+1%))>0)*C$10:C$35,))
复制代码
frequency中国式排名的简短方式142字:
  1. =COUNT(0/FREQUENCY((FREQUENCY(A$10:A$34,MATCH(B$10:B$34,B$10:B$34,)+1/(C$10:C$34+1%))>0)*C$10:C$35,MAX((B$10:B$34=F24)*C$10:C$34)+ROW(A:A)-1))
复制代码
128字(违规的整列引用,改MATCH更简点,不过这题是为frequency出的。。。):
  1. =COUNT(0/FREQUENCY((FREQUENCY(A$10:A$34,MATCH(B$10:B$34,B:B,)-9+1/(C$10:C$34+1%))>0)*C$10:C$35,MAX(IF(B:B=F24,C:C))+ROW(A:A)-1))
复制代码





评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-11-17 16:10 | 显示全部楼层
本帖最后由 13732120571 于 2014-11-18 12:15 编辑

重新做个公式
单元格数组公式 下拉
  1. =SUM(N(MAX((B$10:B$34=M24)*C$10:C$34)<(FREQUENCY(IF(FREQUENCY(A$10:A$34-9%,MATCH(B$10:B$34,B$10:B$34,)-C$10:C$34%%),C$10:C$34),C$10:C$34)>0)*C$10:C$35))+1
复制代码

评分

3

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-11-17 19:45 | 显示全部楼层
本帖最后由 丢丢表格 于 2014-11-22 21:24 编辑
  1. =COUNT(0/FREQUENCY(IF(FREQUENCY(-A$10:A$34-0.5,-MATCH(B$10:B$34,B$10:B$34,)-C$10:C$34%%),C$10:C$34),IF(C$10:C$34>=MAX((B$10:B$34=F24)*C$10:C$34),C$10:C$34)))
  2.         157 字    频率分布 向下趋近法

  3. =COUNT(0/FREQUENCY(IF(C$10:C$34>=MAX((B$10:B$34=F24)*C$10:C$34),C$10:C$34),MOD(LOOKUP(ROW($2:$26)*999,SMALL(MATCH(B$10:B$34,B$10:B$34,)*1000+C$10:C$34,A$10:A$34)),1000)))
  4.        170 字   向量 向上趋远法

  5. =COUNT(0/FREQUENCY(IF(C$10:C$34>=MAX((B$10:B$34=F24)*C$10:C$34),C$10:C$34),MOD(SMALL(IF(B$10:B$34=TRANSPOSE(B$10:B$34),C$10:C$34)+COLUMN(A:Y)*1000,A$10:A$34*25),1000)))
  6.        168字    矩阵横向扩展法


复制代码


  1. G24:G27=MATCH(MOD(SMALL(IF(B10:B34=TRANSPOSE(F24:F27),C10:C34)+COLUMN(A:D)*1000,{1;2;3;4}*25),1000),LARGE((FREQUENCY(IF(FREQUENCY(-A10:A34-0.5,-MATCH(B10:B34,B10:B34,)-C10:C34%%),C10:C34),C10:C34)>0)*C10:C35,A10:A34),)

  2. 区域数组公式
复制代码

不会用 SUM 了, 只能在上面公式的基础上化功夫:
=COUNT(0/FREQUENCY(IF(FREQUENCY(-A$10:A$34-0.9,-MATCH(B$10:B$34,B$10:B$34,)-C$10:C$34%%),C$10:C$34)-MAX((B$10:B$34=F24)*C$10:C$34),ROW($1:$150)))
              145  字

=COUNT(MATCH(ROW($1:151)-1,FREQUENCY(-A$10:A$34-0.9,-MATCH(B$10:B$34,B$10:B$34,)-C$10:C$34%%)^0*C$10:C$35-MAX((B$10:B34=F24)*C$10:C34),))
              137 字

评分

6

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-11-17 20:57 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 opqazxc 于 2014-11-19 20:32 编辑
  1. =COUNT(0/(MMULT(COLUMN(A:Y),N(INT(LOG(MMULT(N(B$10:B$34=TRANSPOSE(B$10:B$34)),99^C$10:C$34),99))=COLUMN(1:1)))*(COLUMN(1:1)>MAX((F24=B$10:B$34)*C$10:C$34))))+1
复制代码
``
  1. =COUNT(0/FREQUENCY(MMULT(N(B$10:B$34=TRANSPOSE(B$10:B$34)),99^C$10:C$34)+1,99^ROW($1:151))/(ROW(A:A)-1>MAX((F24=B$10:B$34)*C$10:C$34)))+1
复制代码
``





补充内容 (2014-12-15 19:39):
``都没注意考虑有效数字了 ,第2个公式FRE第1参数加1跟没加似得``
第2个公式FRE第1参数+1改为*2
就自己一个改正补充``
公式就第一个没问题以第一个为准``

评分

5

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-11-18 15:18 | 显示全部楼层
本帖最后由 笑着...两年过去 于 2014-11-20 10:00 编辑

额,还不算纠结
  1. =COUNT(MATCH(ROW($1:150),MOD(SMALL((B$10:B$34=TRANSPOSE(B$10:B$34))*C$10:C$34+COLUMN(A:Y)/1%%,A$10:A34*25),10^4),)/(MAX((F24=B$10:B34)*C$10:C34)<ROW($1:150)))+1
复制代码

评分

4

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-11-18 22:34 | 显示全部楼层
本帖最后由 cl63519 于 2014-11-20 10:44 编辑

246字符:
"第"&MATCH(MAX(IF($B$10:$B$34=F24,$C$10:$C$34,"")),LARGE(IF(FREQUENCY(LARGE(MOD(LOOKUP(ROW($1:$25)/1%%+150,SMALL(MATCH($B$10:$B$34,$B$10:$B$34,0)/1%%+$C$10:$C$34,ROW($1:$25))),10^4),ROW($1:$25)),$C$10:$C$34)>0,$C$10:$C$34,-10^9),ROW($1:$25)),-1)&"名"

思路没变,硬凑到200字符:
=MATCH(MAX(IF(B$10:B$34=F27,C$10:C$34,"")),LARGE(IF(FREQUENCY(MOD(LOOKUP(ROW($2:$26)/1%%-1,SMALL(MATCH(B$10:B$34,B$10:B$34,0)/1%%+C$10:C$34,ROW($1:$25))),10^4),C$10:C$34),C$10:C$34,-1),ROW($1:$25)),-1)

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-11-19 09:15 | 显示全部楼层
本帖最后由 江河行地. 于 2014-11-20 11:19 编辑

=COUNT(1/FREQUENCY(MOD(SMALL((MATCH(B$10:B$34,B$10:B$34,)=COLUMN(A:Z))*C$10:C$34+COLUMN(A:Z)*1000,ROW($1:$25)*25),1000),IF(MAX((F24=$B$10:$B$34)*C$10:C$34)<=MOD(SMALL((MATCH(B$10:B$34,B$10:B$34,)=COLUMN(A:Z))*C$10:C$34+COLUMN(A:Z)*1000,ROW($1:$25)*25),1000),MOD(SMALL((MATCH(B$10:B$34,B$10:B$34,)=COLUMN(A:Z))*C$10:C$34+COLUMN(A:Z)*1000,ROW($1:$25)*25),1000))))

=25-FREQUENCY(MOD(SMALL((MATCH(B$10:B$34,B$10:B$34,)=COLUMN(A:Z))*C$10:C$34+COLUMN(A:Z)/1%%,ROW($1:$25)*25),10^4),MAX((F24=B$10:B34)*C$10:C34))-(-1^(MAX((F24=B$10:B34)*C$10:C34)>=134))
再来个少点的,还超160


补充内容 (2014-11-22 14:21):
==SUM(N(MOD(SMALL((MATCH(B$10:B$34,B$10:B$34,)=COLUMN(A:Z))*C$10:C$34+COLUMN(A:Z)/1%%,ROW($1:$25)*25),10^4)>MAX((F24=B$10:B34)*C$10:C34)))-(-1^(MAX((F24=B$10:B34)*C$10:C34)>127))

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-11-19 16:06 | 显示全部楼层
=SUM(($C$10:$C$34>=MAX((F24=$B$10:$B$34)*$C$10:$C$34))*($C$10:$C$34=MOD(SMALL(($B$10:$B$34=TRANSPOSE($B$10:$B$34))*($C$10:$C$34)+COLUMN(A:Y)/1%%,25*ROW($1:$25)),10^4))/COUNTIF($C$10:$C$34,$C$10:$C$34)),数组公式下拉,如果不考虑美元号,162个字符,先占个位,有空再优化

评分

1

查看全部评分

TA的精华主题

TA的得分主题

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

G24.数组下拉:
  1. =COUNT(MATCH(MAX((B$10:B$34=F24)*C$10:C$34)+ROW($1:$150),FREQUENCY(-A$10:A$34-0.1,-MATCH(B$10:B$34,B$10:B$34,)-C$10:C$34%%)^0*C$10:C$34,))+1
复制代码
 
140字.湊數~~
 
=============================================
<補充>
FREQUENCY(A$10:A$34-0.1,MATCH(B$10:B$34,B$10:B$34,)-C$10:C$34%%) 應可以少2個負號!
 

评分

4

查看全部评分

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

本版积分规则

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

GMT+8, 2024-3-29 10:04 , Processed in 0.063199 second(s), 12 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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