OFFSET 函数实例（入门级）敬请方家指正

valleytale

内容详见附件： OFFSET实例1（排考号）.zip (111.93 KB, 下载次数: 185)

2011-4-27 16:27 上传

点击文件名下载附件
基于年级考号总表生成考室编排及空成绩表

期待高手指点的问题：

⒈ 《考室编成》⑺⑻这两张表 在填入公式的时候 仍然费了太多手工  （每个考室的编排小有差异，更主要的是在下还不太会用Excel的查找引用函数），期待高手给个更高效的公式 可以从第1个考室 直接刷到最后的…… 而不是像在下这样弱智地去修改每个考室的第1个公式，一个考室一个考室的刷公式……

⒉ 如要求把《考室编排》制作成‘S型排列’的样式，第一个公式要怎么写 才可以从头刷到尾 不必逐一修正？敬请诸位方家勿吝赐教~~

⒊ 空成绩表里面的公式（以《成绩单⑺》为例）=IF(ISERROR(OFFSET('7年级'!$A$1,ROW()-1+51*(INT(COLUMN()/3)),MOD(COLUMN(),3)-1,,)),"",OFFSET('7年级'!$A$1,ROW()-1+51*(INT(COLUMN()/3)),MOD(COLUMN(),3)-1,,))，那个‘51’是笔者依据‘考生人数／成绩单列数’用手工拟合算出来填入公式的；笔者一直在想，能否利用Excel 本身提供的某个‘数值拟合’函数来计算适合的值（就是空成绩单最适合的行数）……（不知道怎么表述清楚，希望同志们能明白在下想说的意思）

补充说明：笔者上班用的是 Excel 2007，但为适应仍然使用 Excel2003 的同仁，上传小作之前已将2003会报错的函数修正了过来，如仍有出错提示，还望海涵~~

lhx120824

原帖由 valleytale 于 2011-4-27 16:27 发表
内容详见附件：913645

期待高手指点的问题：

⒈ 《考室编成》⑺⑻这两张表 在填入公式的时候 仍然费了太多手工  （每个考室的编排小有差异，更主要的是在下还不太会用Excel的查找引用函数），期待高手给个更 ...

你的
‘考生人数／成绩单列数’
中的  成绩单列数 怎么确定？

valleytale

原帖由 lhx120824 于 2011-4-27 18:14 发表
你的
‘考生人数／成绩单列数’
中的  成绩单列数 怎么确定？

为了把空成绩单打印在1张（横向）A4或2张（纵向并列）A4纸上，大体上要向这个比例靠拢：

（行数×行高）／∑（列数×列宽）＝210/297（A4纸宽高比）

[ 本帖最后由 valleytale 于 2011-4-27 21:45 编辑 ]

valleytale

昨晚笔者就2楼大大提出的问题又思索了下，觉得之前是自己把那个问题（算出适合的空成绩单列数）理解复杂了……
其实最简单的办法就是基于各年级考生人数和空成绩单有效列数，写个公式来计算那个‘行数’就行了，然后可以根据打印预览的效果来 调整列数和列宽，要找到一个合适的行数并不难……
基于上述思路，重新设计了〈成绩单⑺〉〈成绩单⑻〉〈成绩单⑼〉这3张工作表里面的公式，看看是不是更灵活了： OFFSET实例1（排考号）.zip (105.27 KB, 下载次数: 129)

2011-4-28 15:27 上传

点击文件名下载附件

其实就是把之前的公式里面：

(OFFSET('7年级'!$A$1,ROW()-1+51*(INT(COLUMN()/3)),MOD(COLUMN(),3)-1,,)

这部分修改成：

(OFFSET('7年级'!$A$1,ROW()-1+ROUNDUP(COUNT('7年级'!$A:$A)/(COUNTA($1:$1)/3),0)*(INT(COLUMN()/3)),MOD(COLUMN(),3)-1,,)

基于相应年级考生总人数（COUNT('7年级'!$A:$A)）和空成绩单有效列数（(COUNTA($1:$1)/3)）用 ROUNDUP(…,0) 这个函数来计算出空成绩单适合的行数（这个‘行数’值用于给OFFSET函数提供参数‘Rows’）

[ 本帖最后由 valleytale 于 2011-4-28 15:36 编辑 ]