ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[Excel 函数与公式] 【54期函数】运动会积分运算函数题(已结题)

[复制链接]

TA的精华主题

TA的得分主题

发表于 2009-10-21 12:45 | 显示全部楼层 |阅读模式
求运动会的排名和积分(具体要求见附件)



补充说明:
1.第一小题,答案正确的得1分,字符数包括等号少于200得2分.第二小题如果添加一个辅助列,答案正确得1分,直接用数组公式完成答案正确得2分。如果第一小题字符少于200,第二小题数组公式(不用辅助列)少于230字符,再得1分。也就是说最高分能得5分。
2.技术分大于9分的,第二题不能用辅助列,只能一次性数组公式完成
3.鉴于时间的不确定性,定于11月23日左右结题,前后时间差为3天左右,保险起见,请家人20号前发帖。
4.A2:A4不会变化

如果第一小题觉得太难的话,也可以用结果做第二小题

[ 本帖最后由 冻豆腐 于 2009-11-23 09:38 编辑 ]

本帖子中包含更多资源

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

x

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2009-10-21 18:37 | 显示全部楼层

回复 1楼 冻豆腐 的帖子

有几个问题:
1.只有男子和女子两个组吗?
2.名次数据区域是否会超过37行?
3.A2:A4会不会变化?
第一小题太难
先来第二小题
244
E8:F15=INDEX(A8:B19,RIGHT(LARGE(B8:B19*10^9+MMULT(COUNTIF(OFFSET(A26,MOD(COLUMN(A:CX)-1,6),INT(((COLUMN(A:CX))-1)/6)*2),A8:A19)*N(OFFSET(B26,MOD(COLUMN(A:CX)-1,6),INT(((COLUMN(A:CX))-1)/6)*2)),10^(7-MOD(ROW(1:102)-1,6)))+ROW(1:12),ROW(1:8)),2),{1,2})

[ 本帖最后由 lzzgzby 于 2009-11-6 11:57 编辑 ]

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2009-10-26 09:04 | 显示全部楼层
楼主:弱弱的问一句,公式中可否使用定义名称?

TA的精华主题

TA的得分主题

发表于 2009-11-9 15:39 | 显示全部楼层
重在参与:
第1题182字符:
B8=ROUND(SUM((A$26:AG$37=A8)*SUBTOTAL(1,OFFSET(A$2,(A$25:AG$25=A$4)*2+(B$37:AH$37=12),TEXT(B$26:AH$37,"0;;1;!1"),,TEXT(COUNTIF(OFFSET(B$26,,COLUMN(B:AH)-2,12),B$26:AH$37),"0;;1")))),1)
第2题245字符
E8:F15
=INDEX(A:B,RIGHT(LARGE(B8:B19*10^9+ROW(8:19)+MMULT(LOOKUP(12*ROW(1:12)+COLUMN(A:F)-12,ROW(1:144),FREQUENCY(IF(ISTEXT(A26:AG37),MATCH(A26:AG37,A8:A19,)%+B26:AH37%%,4^8),ROW(1:12)%+COLUMN(A:L)%%))*10^(8-COLUMN(A:F)),ROW(1:6)^0),ROW(1:8)),2),{1,2})
均为数组公式
第2题投机公式227字符
=INDEX(A:B,RIGHT(LARGE(B8:B19*10^9+ROW(8:19)+MMULT(LOOKUP(12*ROW(1:12)+B1:G1-12,ROW(1:144),FREQUENCY(IF(ISTEXT(A26:AG37),MATCH(A26:AG37,A8:A19,)%+B26:AH37%%,4^8),ROW(1:12)%+B1:M1%%))*10^(8-B1:G1),ROW(1:6)^0),ROW(1:8)),2),{1,2})

[ 本帖最后由 jyhxr 于 2009-11-17 16:46 编辑 ]

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2009-11-14 15:42 | 显示全部楼层
第一小题,数组公式:
=ROUND(SUM(IF(A$26:AG$37=A8,SUBTOTAL(1,OFFSET(A$2,(A$25:AG$25=A$4)*2+COUNTIF(OFFSET(B$26,,COLUMN(B:AH)-2,12),9),B$26:AH$37,,COUNTIF(OFFSET(B$26,,COLUMN(B:AH)-2,12),B$26:AH$37))))),1)
第二小题,多单元格数组公式:
=INDEX(A:B,RIGHT(LARGE(B8:B19*10^9+MMULT(LOOKUP(ROW(1:12)*12-12+COLUMN(A:F),ROW(1:144),FREQUENCY(IF(ISTEXT(A26:AG37),MATCH(A26:AG37,A8:A19,)/1%+B26:AH37),ROW(1:12)/1%+COLUMN(A:L))),10^(8-ROW(1:6)))+ROW(8:19),ROW()-7),2),{1,2})



[ 本帖最后由 rongjun 于 2009-11-14 15:47 编辑 ]

本帖子中包含更多资源

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

x

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-11-23 09:35 | 显示全部楼层

竞赛结题

这期函数题,下载多,答题少,前半个月没人答题,以为要创无人答题的历史了,在此先谢谢楼上几位捧场。

小题1的答题关键是,subtotal函数的支持多维引用,所以在A1:M4中利用0来填充单元格,就是方便利用平均值,范围引用。4楼,5楼的思路一样,就是在排错方面处理不一样。

小题2的难点在于降维数,把三维的降到二维。有两种思路。
     1。利用offset函数取数,文本用T来取,数字用N来取,还有一个遇到的问题是嵌套问题。有兴趣的朋友可以自己先试试,体验嵌套移位。2楼就是这种思路,不过名次6的可能会有3个,应该取到8行,不过对答题没太大影响。
      2。利用frequency函数降维(可以看帮助),虽然会有145个数,但是lookup函数,后面的两个参数,可以个数不相同,这是我这次学到的知识。4楼,5楼的思路。

我的答案:
小题1:=ROUND(SUM(IF(ISNUMBER(B$26:AI$37),SUBTOTAL(1,OFFSET(A$2,(A$25:AH$25=A$4)*2+(B$37:AI$37=12),B$26:AI$37,,COUNTIF(OFFSET(A$26:A$37,,COLUMN(A:AH)),B$26:AI$37))))*(A$26:AH$37=A8)),1)
小题2:
=OFFSET(A7,RIGHT(LARGE(B8:B19*10^11+MMULT((T((OFFSET(A26,MOD(COLUMN(H:EM),8),INT(COLUMN(H:EM)/8)*2-2)))=A8:A19)*10^(8-N(OFFSET(A26,MOD(COLUMN(H:EM),8),INT(COLUMN(H:EM)/8)*2-1))),ROW(1:136)^0)*100+ROW(1:12),ROW(1:8)),2),{0,1})

越俎代庖了两期,感谢大理兄的支持。接下来要做大理兄的好题了。呵呵

[ 本帖最后由 冻豆腐 于 2009-11-23 09:43 编辑 ]

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2009-11-23 22:10 | 显示全部楼层
觉得这道题有点复杂了..
.学习了.

TA的精华主题

TA的得分主题

发表于 2009-11-24 09:53 | 显示全部楼层
下载了发现完全不会。就没来参与

TA的精华主题

TA的得分主题

发表于 2009-11-25 09:57 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
1.通过(A$25:AH$25=A$4)*2+(B$37:AI$37=12)巧妙地避开判断24行的麻烦;
但a2:a4如有顺序变化,女子也有1500m,或许还有一点点麻烦。
2.学习了subtotal的多维引用与frequency函数(配合lookup)降维作用。
3.3、4、5楼三位大师了得啊。

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-11-25 11:20 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助

回复 9楼 lzzgzby 的帖子

当初考虑时是为了公式简便,如果考虑位置不一样的话,就要用
=IF(ISNA(MATCH("*"&A$25:AH$25&"*",A$2:A$4,)),,MATCH("*"&A$25:AH$25&"*",A$2:A$4,)-1)*((A$25:AH$25=A$4)+(B$37:AI$37=12)),来判断行偏移了
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-3-29 21:49 , Processed in 0.056932 second(s), 17 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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