【65期】按要求制作打印表格 [已结束]

冻豆腐 · 发表于 2010-8-28 09:47

题目A1:B101为数据区域，A列为小学生报名的序列号，B列为相应的初中录取学校，数据按A列报名号排序，答案区域E至H列
现在要将这个表格打印，发给各个初中学校，为了节省纸张，分开两列打印，已知一张纸上能打印20个学生，为了好校对现要求如下
要求
1.一张纸上的顺序，是按报名号，从上到下，再从上到下，也就是10连续，第二列接着第一列，第二张纸第一列接着第一张纸的第二列继续打印
2.为了一个学校能够拿到完整的信息，比如这个学校为22个人，那么打印两张纸，第二张纸只有两个学生，其余空白
3.具体显示如左侧结果所示，题头已经给出（小学报名序号，初中录取学校），只要填入相关正确的学生信息
4.表格打印顺序按录取学校顺序，颜色已标出，即甲乙丙丁戊顺序
5.用一个公式右下拉完成，不准用vba，自定义名称，不使用辅助列，表格中出现的可以使用。
6.所有初中学校都会出现，不会出现那所学校没人的情况。
评分方法
1.只要字符不超过400个，得3个技术分，50财富
2.在答题期间，无答案占楼层者，进行处罚。
3.希望题目大家能够自己完成，为公平起见，如发现私下要答案者，一经发现，作弊双方都扣除5个技术分，技术分不够者，扣除财富。
此题个人认为不是很浅，所以在字符上放宽要求，主要看答题者思路。
答题截止时间为9月22日，之后答复者不给予评分。
题目可能考虑不周，有问题处请大家及时通知，本人进行改正。

[ 本帖最后由 dul 于 2010-9-30 15:59 编辑 ]

wjh619993018 · 发表于 2010-8-29 00:47

计算等号396字符
E2公式：=OFFSET($A$1,SMALL(IF($B$2:$B$101=INDEX($C:$C,SMALL(IF(COLUMN(1:1)>CEILING(COUNTIF($B:$B,$C$2:$C$6)/2,10),9,ROW($2:$6)),ROW()-1)),ROW($1:$100),999),(FLOOR(ROW()-2,10)+(COLUMN()>6)*5-MOD(SMALL(IF(COLUMN(1:1)>CEILING(COUNTIF($B:$B,$C$2:$C$6)/2,10),999,ROW($1:$5)/1%+MMULT(N(ROW($2:$6)>COLUMN($A:$E)),CEILING(COUNTIF($B:$B,$C$1:$C$5)/2,10))),ROW()-1),100))*2+MOD(ROW()-2,10)+1),MOD(COLUMN()-1,2))&""

gdfcx · 发表于 2010-8-29 20:37

359字符，数组公式如下：
=INDEX(OFFSET($A:$A,,MATCH(E$1,$1:$1,)-1),SMALL(IF($B$2:$B$101=LOOKUP(ROW()-2,MMULT(N(ROW($1:$6)>=COLUMN($A:$F)),FLOOR(COUNTIF($B:$B,$C$1:$C$6),10)),$C$2:$C$7),ROW($2:$101),4^8),MOD(ROW()-2+10*INT((ROW()-2)/10)+IF(COLUMN()>6,10,),LOOKUP(ROW()-2,MMULT(N(ROW($1:$5)>={1,2,3,4,5}),FLOOR(COUNTIF($B:$B,$C$1:$C$5),10)),CEILING(COUNTIF($B:$B,$C$2:$C$6),20)))+1))&""

在B2:B31为丙时，看看结果就知道原因了，可惜了。无法得分。

[ 本帖最后由冻豆腐于 2010-9-23 21:50 编辑 ]

泓() · 发表于 2010-8-31 08:00

看是400字符对我来说这个定位极其精准的数据了
想了好久好久才不超400（以我目前的思路）

397字符
E2
=OFFSET($A$1,SMALL(IF($B$2:$B$998=OFFSET($C$2,SUM(--(ROW()-1>MMULT(N(ROW($2:$6)>{1,2,3,4,5}),ROUNDUP(COUNTIF($B$2:$B$998,$C$2:$C$6)/20,)*10))),),ROW($2:$998),999),(INT((ROW()-2)/10)+(COLUMN()>6))*10+ROW()-1-SUM((ROW($1:$5)<SUM(--(ROW()-1>MMULT(N(ROW($2:$6)>{1,2,3,4,5}),ROUNDUP(COUNTIF($B$2:$B$998,$C$2:$C$6)/20,)*10)))+1)*ROUNDUP(COUNTIF($B$2:$B$998,$C$2:$C$6)/20,)*10)*2)-1,MOD(COLUMN()-1,2))&""

[ 本帖最后由泓() 于 2010-8-31 09:29 编辑 ]

wangg913 · 发表于 2010-8-31 09:47

老师出题，支持学习。

=OFFSET($A$1,RIGHT(SMALL(IF({1,0},MATCH($B$2:$B$101,$C:$C,),SMALL(7.1-(COLUMN($A:$T)<=CEILING(COUNTIF($B:$B,$C$2:$C$6),20)-COUNTIF($B:$B,$C$2:$C$6))*{5;4;3;2;1},ROW($1:$100)))/1%%+ROW($1:$100),ROW()+INT(ROW(A10)/10+INT(COLUMN(B1)/2))*10-21),4),MOD(COLUMN(B1),2))&""
===========================================================================================
=OFFSET($A$1,RIGHT(SMALL(IF({1,0},MATCH($B$2:$B$101,$C:$C,),SMALL(7.1-(COLUMN($A:$T)<=MOD(20-MOD(COUNTIF($B:$B,$C$2:$C$6),20),20))*{5;4;3;2;1},ROW($1:$100)))/1%%+ROW($1:$100),ROW()+INT(ROW(A10)/10+INT(COLUMN(B1)/2))*10-21),4),MOD(COLUMN(B1),2))&""

[ 本帖最后由 wangg913 于 2010-9-9 08:48 编辑 ]

fugb-2010 · 发表于 2010-9-1 10:10

建议竞赛题评分标准是否按字元数分档评分，好让多一点初学者参与！
本人对以下操作定义名词：第1部分：自动分页(每10行为1页)；第2部分：自动分节(每1个学校名为1节)；第3部分：强制隔列分栏
经过三次编辑更改最终结果如下
1、不使用表格中C2：C6学校名称数组公式为：（字元数389）
E2=OFFSET($A$1,SMALL(IF($B$2:$B$150=LOOKUP(ROW()-2,MMULT(10*(ROW($1:$5)>=COLUMN($B:$F)),ROUNDUP(COUNTIF($B$2:$B$150,{"甲";"乙";"丙";"丁";"戊"})/20,)),{"甲";"乙";"丙";"丁";"戊"}),ROW($B$2:$B$150),4^8),ROW(A1)+(INT((ROW()-2)/10)+INT(COLUMN(A:A)/3))*10+LOOKUP(ROW()-2,MMULT(MMULT(10*(ROW($1:$5)>=COLUMN($B:$F)),ROUNDUP(COUNTIF($B$2:$B$150,{"甲";"乙";"丙";"丁";"戊"})/20,)),{1,-2})))-1,MOD(COLUMN(A:A)-1,2))&""
2、使用表格中C2：C6学校名称数组公式为：（字元数353）
E2=OFFSET($A$1,SMALL(IF($B$2:$B$150=LOOKUP(ROW()-2,MMULT(10*(ROW($1:$5)>=COLUMN($B:$F)),ROUNDUP(COUNTIF($B$2:$B$150,$C$2:$C$6)/20,)),$C$2:$C$6),ROW($B$2:$B$150),4^8),ROW(A1)+(INT((ROW()-2)/10)+INT(COLUMN(A:A)/3))*10+LOOKUP(ROW()-2,MMULT(MMULT(10*(ROW($1:$5)>=COLUMN($B:$F)),ROUNDUP(COUNTIF($B$2:$B$150,$C$2:$C$6)/20,)),{1,-2})))-1,MOD(COLUMN(A:A)-1,2))&""

[ 本帖最后由 fugb-2010 于 2010-9-6 08:15 编辑 ]

sunbin200388 · 发表于 2010-9-2 14:19

在E2输入公式
=OFFSET($A$1,SMALL(IF(LOOKUP(ROW()-1.5,MMULT(N(ROW($1:$5)>COLUMN($A:$E)),CEILING(COUNTIF($B$2:$B$101,$C$2:$C$6),20))/2,$C$2:$C$6)=$B$2:$B$101,ROW($1:$100),999),CEILING(ROW()-1,10)-(COLUMN()<7)*10+ROW()-1-LOOKUP(2*ROW()-3.5,MMULT(N(ROW($1:$5)>COLUMN($A:$E)),CEILING(COUNTIF($B$2:$B$101,$C$2:$C$6),20)))),MOD(COLUMN()-1,2))&""

右拉下拉
============================================================================================
=OFFSET($A$1,SMALL(IF(LOOKUP(ROW()-2,MMULT(N(ROW($1:$5)>{1,2,3,4,5}),CEILING(COUNTIF($B$2:$B$101,$C$2:$C$6),20))/2,$C$2:$C$6)=$B$2:$B$101,ROW($1:$100),999),CEILING(ROW()-1,10)-(COLUMN()<7)*10+ROW()-1-LOOKUP(2*ROW()-4,MMULT(N(ROW($1:$5)>{1,2,3,4,5}),CEILING(COUNTIF($B$2:$B$101,$C$2:$C$6),20)))),MOD(COLUMN()-1,2))&""
共计316字符

[ 本帖最后由 sunbin200388 于 2010-9-7 13:08 编辑 ]

chunlin1wang · 发表于 2010-9-2 21:34

kkitt · 发表于 2010-9-6 00:49

E2=INDEX($A:$B,RIGHT(SMALL(IF($B$2:$B$101=TRANSPOSE($C$1:$C$99),ROW($2:$101),999)+1000*COLUMN($A:$CU),FLOOR(ROW(A1)-1,10)+ROW(A1)+(COLUMN(A1)>2)*10+LOOKUP(FLOOR(ROW(A1)-1,10)+ROW(A1)+(COLUMN(A1)>2)*10-1,MMULT(N(ROW($1:$6)>=COLUMN($A:$F)),CEILING(COUNTIF($B:$B,$C$1:$C$6),20)),ROW($C$1:$C$6)*100-MMULT(N(ROW($1:$6)>=COLUMN($A:$F)),CEILING(COUNTIF($B:$B,$C$1:$C$6),20)))),3),2-MOD(COLUMN(A1),2))&""

piny · 发表于 2010-9-7 00:25

最先思路
526字元
E2=OFFSET($A$1,IF(ISNA(MATCH(ROW(A1)+(COLUMN()>6)*10+10*INT((ROW()-2)/10),MMULT(--($B$2:$B$101=TRANSPOSE($B$2:$B$101))*(COLUMN($B:$CW)<=ROW($2:$101)),ROW($2:$101)^0)+MMULT(--(COLUMN($A:$E)<MATCH($B$2:$B$101,$C$2:$C$6,)),--CEILING(COUNTIF($B:$B,$C$2:$C$6),20)),)),101,MATCH(ROW(A1)+(COLUMN()>6)*10+10*INT((ROW()-2)/10),MMULT(--($B$2:$B$101=TRANSPOSE($B$2:$B$101))*(COLUMN($B:$CW)<=ROW($2:$101)),ROW($2:$101)^0)+MMULT(--(COLUMN($A:$E)<MATCH($B$2:$B$101,$C$2:$C$6,)),--CEILING(COUNTIF($B:$B,$C$2:$C$6),20)),)),MOD(COLUMN(B1),2))&""

数组右拉下拉

簡化一
322字元
E2=OFFSET($A$1,MATCH(ROW(A1)+(COLUMN()>6)*10+10*INT((ROW()-2)/10),MMULT(--($B$2:$B$299=TRANSPOSE($B$2:$B$299))*(TRANSPOSE(ROW($2:$299))<=ROW($2:$299)),ROW(2:299)^0)+MMULT(--(COLUMN($A:$E)<IF(ISNA(MATCH($B$2:$B$299,$C$2:$C$6,)),1,MATCH($B$2:$B$299,$C$2:$C$6,))),--CEILING(COUNTIF($B:$B,$C$2:$C$6),20)),),MOD(COLUMN(B1),2))&""

數組右拉下拉

簡化二
294字元
E2=OFFSET($B$1,MATCH(ROW(A1)+(COLUMN()>6)*10+10*INT((ROW()-2)/10),MMULT(-({1,2,3,4,5}<IF($B$2:$B$350="",,MATCH($B$2:$B$350,$C$2:$C$6,))),-CEILING(COUNTIF($B:$B,$C$2:$C$6),20))-MMULT(-($B$2:$B$350=TRANSPOSE($B$2:$B$350))*(TRANSPOSE(ROW($2:$350))<=ROW($2:$350)),ROW(2:350)^0),),-MOD(COLUMN(),2))&""
數組右拉下拉

[ 本帖最后由 piny 于 2010-9-20 09:52 编辑 ]

		自动登录	找回密码
密码			免费注册

[Excel 函数与公式] 【65期】按要求制作打印表格 [已结束]

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

评分

本帖子中包含更多资源

评分

评分

本帖子中包含更多资源

评分

浏览过的版块