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

willin2000

答题方式:直接在回复中贴上答案或上传附件即可.

kkitt

O2=IF(ROW(A5)>4*COUNTA($M:$M),"",INDEX(CHOOSE(COLUMNS($A:A),$1:$1,L:L,ROW($1:$9999)),MID(TEXT(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$100,OFFSET($C:$C,,{0,3,6,9}))*10^5+{1,4,7,10}+ROW($2:$100)*100,ROW(A1)),REPT("0         00",2)),28-COLUMN(A1)*9,9)))

neverquest

最高4分啊！先占位，有空再做做！

冻豆腐

=IF(ROW()>COUNTA($M:$M)*4-3,"",INDEX(CHOOSE(COLUMN(A1),TRANSPOSE($1:$1),$M:$M,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)),CHOOSE(COLUMN(A1),12,8,4),4)))
247个字符，考虑到字符原因运行速度会比较慢!
简化一下243个字符（2007，2003下都能运行）
=IF(ROW()>COUNTA($M:$M)*4-3,"",INDEX(CHOOSE(COLUMN(A1),TRANSPOSE($1:$1),$M$1:$M$101,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)))
在2007下正常，但在03版下城市会出现那非数字错误（237个字符）
=IF(ROW()>COUNTA($M:$M)*4-3,"",INDEX(CHOOSE(COLUMN(A1),TRANSPOSE($1:$1),$M:$M,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)))

veobai

疑问：M列选择的城市是否连续？即城市与城市之间能不能有空单元格~

可以连续,也可以不连续,但如果只针对连续也算正确 -willin2000

呵呵，那就先交个答案，适用于连续和非连续两种情况~
可以修改其中的数字来适应城市的增加和数据行的增加~
281个字符：
=IF(ROW()>4*COUNTA($M:$M)-3,"",LOOKUP(INDEX(INT(--RIGHT(LARGE(SUMIF(OFFSET($A$2,,{0,3,6,9},80),$M$2:$M$10,OFFSET($C$2,,{0,3,6,9},))*100+{1,4,7,10}+ROW($1:$9)%,ROW(A1)),{5,2,9})*{1,1,0.01}),COLUMN(A1)),ROW($1:$1000),CHOOSE(COLUMN(A1),TRANSPOSE($A$1:$L$1),$M$2:$M$10,ROW($1:$1000))))

270个字符
=IF(ROW()>4*COUNTA($M:$M)-3,"",LOOKUP(INDEX(RIGHT(LARGE(SUMIF(OFFSET($A$2,,{0,3,6,9},80),$M$2:$M$10,OFFSET($C$2,,{0,3,6,9},80))+{1,4,7,10}%+ROW($2:$10)%%,ROW(A1)),{4,2,9})/{100,1,1},COLUMN(A1)),ROW($1:$1000),CHOOSE(COLUMN(A1),TRANSPOSE($1:$1),$M$1:$M$10,ROW($1:$1000))))

253字符
=IF(ROW()>4*COUNTA($M:$M)-3,"",INDEX(CHOOSE(COLUMN(A1),TRANSPOSE($1:$1),$M$1:$M$10,ROW($1:$1000)),INDEX(RIGHT(LARGE(SUMIF(OFFSET($A$2,,{0,3,6,9},80),$M$2:$M$10,OFFSET($C$2,,{0,3,6,9},))+{1,4,7,10}%+ROW($2:$10)%%,ROW(A1)),{4,2,9})/{100,1,1},COLUMN(A1))))

excel07中 248字符
=IF(ROW()>4*COUNTA($M:$M)-3,"",INDEX(CHOOSE(COLUMN(A1),TRANSPOSE($1:$1),$M:$M,ROW($1:$1000)),INDEX(RIGHT(LARGE(SUMIF(OFFSET($A$2,,{0,3,6,9},80),$M$2:$M$10,OFFSET($C$2,,{0,3,6,9},))+{1,4,7,10}%+ROW($2:$10)%%,ROW(A1)),{4,2,9})/{100,1,1},COLUMN(A1))))

jyhxr

第1个公式,336字符,有待简化,
=IF(ROW()+3>COUNTA($M:$M)*4,"",IF(COLUMN()-14<3,INDIRECT(MID(TEXT(LARGE({1;4;7;10}+COLUMN($B:$CV)*1000+SUMIF(OFFSET($A:$A,,{0;3;6;9}),TRANSPOSE($M$2:$M$100),OFFSET($A:$A,,{2;5;8;11}))*10^5,ROW(A1)),"0000R??C13R01C??"),17-COLUMN(A1)*6,6),),LARGE(SUMIF(OFFSET($A:$A,,{0;3;6;9}),TRANSPOSE($M$2:$M$100),OFFSET($A:$A,,{2;5;8;11})),ROW(A1))))

shlzml

=IF(ROW()>COUNTA($M:$M)*4-3,"",INDEX(CHOOSE(MIN(COLUMN(A1)),$1:$1,L:L,ROW($1:$9999)),MOD(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$101,OFFSET($C:$C,,{0,3,6,9}))*10^5+ROW($2:$101)*100+{1,4,7,10},ROW(A1)),10^(COLUMN(A1)^2+1))/10^CHOOSE(COLUMN(A1),,2,5)))

Scarlett_88

取巧了，不知算不算对。见笑了。
=T(INDIRECT(TEXT(MID(RIGHT(REPT(0,9)&LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9,2}),$M$2:$M$101,OFFSET($C:$C,,{0,3,6,9,2}))*10^12+{1,4,7,10,20}+1140001300100+ROW($2:$101)*10^7,ROW()-1),21),92-COLUMN()*5,5),"R000C00"),))&IF(COLUMN()=17,TEXT(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$101,OFFSET($C:$C,,{0,3,6,9})),ROW()-1),"0;;"),"")

下拉至O19后内容不是空.不得分. -willin2000

lanyuu

Q2=SUM((OFFSET($A$3,,MATCH(1,1/($A$1:$L$1=O2))-1,65500)=P2)*OFFSET($A$3,,MATCH(1,1/($A$1:$L$1=O2))+1,65500))
好像有点慢，用的数组公式，不知道行不行

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

lzzgzby

317字符
=IF(ROW(A1)>16,"",IF(COLUMN(A1)=3,LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$5,OFFSET($C:$C,,{0,3,6,9})),ROW(A1)),INDEX(CHOOSE(COLUMN(A1),T(OFFSET($A$1,,{0;3;6;9})),$M$2:$M$5),MID(RIGHT(LARGE(SUMIF(OFFSET($A:$A,,{0,3,6,9}),$M$2:$M$5,OFFSET($C:$C,,{0,3,6,9}))*10^9+{1,2,3,4}*10+{1;2;3;4},ROW(A1)),2),COLUMN(A1),1))))
不会用text函数，长度缩不了，先占个位！