[第46期]指定城市分类求和排序[已结]

zhanhewang

O2=IF(N$1=$P$1,TEXT(LARGE(SUMIF(OFFSET($A$3,,{0,3,6,9},40),$M$2:$M$99,OFFSET($C$3,,{0,3,6,9})),MIN(400,ROW(A1))),"0;;"),INDIRECT(TEXT(RIGHT(LARGE(--TEXT(SUMIF(OFFSET($A$3,,{0,3,6,9},40),$M$2:$M$99,OFFSET($C$3,,{0,3,6,9}))*10^5+ROW($1:$98)*(N$1<>$N$1)/1%+({0,3,6,9}-11)^(N$1=$N$1)+112,"[<9999]!0"),MIN(400,ROW(A1))),5),"R0c00;;r1c99"),))&""
惭愧，想了好长时间，也简化不了。327个字符。期待学习。

mxqchina

长度326（太长了，速度还很慢）
O3
=CHOOSE(IF(COUNTA($M:$M)*4<ROW()+3,1,LEN(O$1)),"",INDIRECT(MID(TEXT(MOD(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$101,OFFSET($C:$C,,{0,3,6,9}))*10^6+ROW($M$2:$M$101)+{1,4,7,10}*10^3,ROW(A1)),10^6),"r!01c000r000c13"),IF(O$1="季度",1,8),6),),LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$101,OFFSET($C:$C,,{0,3,6,9})),ROW(A1)))
数组公式。

结果不正确,不得分. -willin2000

chrisfang

先给几个答案吧：
O2=IF(O$1=$Q$1,TEXT(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$101,OFFSET($C$1,,{0,3,6,9})),ROW(A1)),"#;;"),INDEX(IF($Q2="",$Z:$Z,IF(O$1=$O$1,$1:$1,$M:$M)),INDEX(LEFT(RIGHT(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$101,OFFSET($C$1,,{0,3,6,9}))*10^5+ROW($2:$101)*100+{1,4,7,10},ROW(B1)),{2,5}),3),,COLUMN(A1))))&""

O2=IF(O$1=$Q$1,TEXT(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$101,OFFSET($C$1,,{0,3,6,9})),ROW(A1)),"#;"),INDIRECT(INDEX(IF($Q2="",{"R1C2","R"},{"R1C","R"})&LEFT(RIGHT(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$101,OFFSET($C$1,,{0,3,6,9}))*10^5+ROW($2:$101)*100+{1,4,7,10},ROW(B1)),{2,5}),3)&{"","C13"},,COLUMN(A1)),0))&""

O2=IF(O$1=$Q$1,IF($O2="","",SUMIF(OFFSET($A:$A,,MATCH($O2,$1:$1,)-1),$P2,OFFSET($C$1,,MATCH($O2,$1:$1,)-1))),INDEX(IF(O$1=$O$1,IF($P2="",$Z:$Z,$1:$1),$M:$M),LEFT(RIGHT(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$101,OFFSET($C$1,,{0,3,6,9}))*10^5+ROW($2:$101)*100+{1,4,7,10},ROW(B1)),IF(O$1=$O$1,2,5)),3)))&""

O2=IF(O1=$Q1,TEXT(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$101,OFFSET($C$1,,{0,3,6,9})),ROW(A1)),"#;"),INDEX(IF($Q2="",$Z:$Z,IF(O1=$O1,$1:$1,$M:$M)),LEFT(RIGHT(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$101,OFFSET($C$1,,{0,3,6,9}))*10^5+ROW($2:$101)*100+{1,4,7,10},ROW(B1)),5-($O1=O1)*3),3)))&""

O2=IF(O1=$Q1,TEXT(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M101,OFFSET($C$1,,{0,3,6,9})),ROW(A1)),"#;"),INDEX(IF($Q2="",Z:Z,IF(O1=$O1,$1:$1,$M:$M)),RIGHT(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M101,OFFSET($C$1,,{0,3,6,9}))*10^3+IF(O1=$O1,{1,4,7,10},ROW($2:101)),ROW(B1)),3)))&""

lanyuu

q2=IF(O2="","",SUM((OFFSET($A$3,,MATCH(1,1/($A$1:$L$1=O2))-1,65)=P2)*OFFSET($A$3,,MATCH(1,1/($A$1:$L$1=O2))+1,65)))

不符合答题要求2, 不得分. -willin2000

willin2000

本期题主要是看只使用一次求和公式的方法,如果答出基本上都能满足3分的字符数限制.
2分公式基本上2次求和公式,并且优化的比较好.
1分公式基本上是能做出来并有一定优化.
其中kkitt,冻豆腐,veobai的公式最合出题初衷. 使用一次求和.
本题对使用了一次求和的公式没有作进一步优化的要求,所以字符数上比较宽泛.
长度223的公式:
=IF(ROW(A5)>4*COUNTA($M:$M),"",INDEX(CHOOSE(COLUMNS($A:A),$1:$1,L:L,ROW($A:A)),MID(LARGE(10^14+SUMIF(OFFSET($A:A,,{0,3,6,9}),$M$2:$M$101,OFFSET($C$1,,{0,3,6,9}))*10^8+ROW($2:$101)*10^4+{1,4,7,10},ROW(A1)),72-COLUMN()*4,4)))
(如果是更多行,即有1万行,可参考上面REPT的方式).

本题目的:了解SUMIF,和COLUMNS, 以及INDEX和CHOOSE的组合应用.

zhanhewang

谢谢版主，你辛苦了。又学了一招：INDEX(CHOOSE(COLUMNS($A:A),$1:$1,L:L,ROW($A:A)),　　。
我还有一点想请教：上述式中的COLUMNS($A:A)换成COLUMN(A:A)为何不行。

willin2000

原帖由 zhanhewang 于 2009-6-1 11:46 发表
谢谢版主，你辛苦了。又学了一招：INDEX(CHOOSE(COLUMNS($A:A),$1:$1,L:L,ROW($A:A)),　　。
我还有一点想请教：上述式中的COLUMNS($A:A)换成COLUMN(A:A)为何不行。

COLUMNS($A:B)的结果是2,COLUMN(B:B)的结果{2}(数组), 由于INDEX不支持3维引用,所以直接用COLUMN(B:B)不支持,但可以用MAX(COLUMN(B:B))解决,只是没有COLUMNS简洁.

zhanhewang

原帖由 willin2000 于 2009-6-1 15:49 发表


COLUMNS($A:B)的结果是2,COLUMN(B:B)的结果{2}(数组), 由于INDEX不支持3维引用,所以直接用COLUMN(B:B)不支持,但可以用MAX(COLUMN(B:B))解决,只是没有COLUMNS简洁.

谢谢willin2000版主。正因为有您这样的前辈，EXCELHOME才走到今天，也必将走向更加辉煌的明天。谢谢！！

zhanhewang

我一直在关函数和公式栏的竞赛题，可已好长时间没见到新的题目了。真想有既锻炼思维，又可加技术分的机会。恳请各位前辈不要让晚生太失望了。