【54期函数】运动会积分运算函数题（已结题）

冻豆腐

求运动会的排名和积分（具体要求见附件）



补充说明：
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 编辑 ]

lzzgzby

有几个问题：
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 编辑 ]

trustwxq

楼主：弱弱的问一句，公式中可否使用定义名称？

jyhxr

重在参与:
第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 编辑 ]

rongjun

第一小题，数组公式：
=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 编辑 ]

冻豆腐

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

小题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 编辑 ]

wenshui2006

觉得这道题有点复杂了..
.学习了.

yslhy1111

下载了发现完全不会。就没来参与

lzzgzby

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楼三位大师了得啊。

冻豆腐

当初考虑时是为了公式简便，如果考虑位置不一样的话，就要用
=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))，来判断行偏移了