[求助]如何设置公式生成1至6年级各班的学生考号名单？

lz889999

各位老师:
    每学期期末都要给各年级各班的班主任和学生打印考试的考号，数据量大，工作量繁重，所以， 烦请老师根据“一至六年级总花名册”工作表中的固定格式及相应的内容，在“各年级各班考号”工作表中绿色区域中将各年级各班的学生有关考号的信息按照黄色区域的格式“示例”用函数公式将一至六年级各班的学生考号自动生成填入相应的表中，然后，各年级各班用A4打印出来（每班只打印一张A4的纸）。这样，减少人工用大量的时间去复制和粘贴，以提高工作效率。
   老师也可以另外从新设计便于函数公式操作的版式！
    谢谢！！     qC24UXfp.rar (106.73 KB, 下载次数: 408)

2008-5-4 21:03 上传

点击文件名下载附件
[求助]如何设置公式生成1至6年级各班的学生考号名单？

little-key

1、公式法：

通过=SMALL(IF(一至六年级总花名册!$M$6:$M$1629=$C$3,一至六年级总花名册!$D$6:$D$1629),ROW(A1))数组公式就可以把学籍号调取出来，因为学籍号是唯一的，因此别的使用查询即可。

2、高级筛选。可以使用高级筛选的方法，不过这种方法只能使用单列显示。

3、参考http://club.excelhome.net/viewthread.php?tid=201882&replyID=&skin=0

lz889999

QUOTE:

以下是引用little-key在2008-5-4 21:12:07的发言：

1、公式法：

通过=SMALL(IF(一至六年级总花名册!$M$6:$M$1629=$C$3,一至六年级总花名册!$D$6:$D$1629),ROW(A1))数组公式就可以把学籍号调取出来，因为学籍号是唯一的，因此别的使用查询即可。

2、高级筛选。可以使用高级筛选的方法，不过这种方法只能使用单列显示。

3、参考http://club.excelhome.net/viewthread.php?tid=201882&replyID=&skin=0

        little-key老师：非常感谢！我还是先采用第一种方案，但是“因此别的使用查询即可”中的查询函数公式，我是这样写的：A6==VLOOKUP($B6,一至六年级总花名册!$C6:$C1629,3)        C6==VLOOKUP($B6,一至六年级总花名册!$A6:$AP1629,5),等等为什么总是不正确，烦请给我修改一下！谢谢！

chury11

vlookup(,,,0)参数错误

samsoncsr

QUOTE:

以下是引用lz889999在2008-5-4 22:28:26的发言：

        little-key老师：非常感谢！我还是先采用第一种方案，但是“因此别的使用查询即可”中的查询函数公式，我是这样写的：A6==VLOOKUP($B6,一至六年级总花名册!$C6:$C1629,3)        C6==VLOOKUP($B6,一至六年级总花名册!$A6:$AP1629,5),等等为什么总是不正确，烦请给我修改一下！谢谢！

A6：=VLOOKUP(B6,IF({1,0},一至六年级总花名册!$D$6:$D$3000,一至六年级总花名册!$C$6:$C$3000),2,)

C6：=VLOOKUP($B6,一至六年级总花名册!$D$6:$F$3000,2,)

E6：=VLOOKUP($B6,一至六年级总花名册!$D$6:$F$3000,3,)

samsoncsr

考虑了一下，还是这样效率高些：

输入班次，表格和班主任自动变化。

观察了一下：不同的学校班级不会重复（如果会重复，公式要更改），那就用2个数组公式：

A6：=INDEX(一至六年级总花名册!C:C,SMALL(IF(一至六年级总花名册!$M$1:$M$3000=各年级各班考号!$C$3,ROW($1:$3000),63356),ROW(1:1)))&""

按Ctrl+Shift+Enter结束。

选中A6，下托至37行，选中A6:A37，右托3列。

F6：=INDEX(一至六年级总花名册!C:C,SMALL(IF(一至六年级总花名册!$M$1:$M$3000=各年级各班考号!$C$3,ROW($1:$3000),63356),ROW(33:33)))&""

按Ctrl+Shift+Enter结束。

选中F6，下托至37行，选中F6:F37，右托3列。

还有个要注意的：如果班级人数多于64人，表格数据会溢出，那么可以更改以上公式的红色部分。

提醒一下：在“一至六年级总花名册”表中的M列班级数值输入有些问题，很多存在空格，这样会造成查找不到，我已经通过分列的方法修正。

  

w8dALotB.rar (108.67 KB, 下载次数: 196)

2008-5-5 00:42 上传

点击文件名下载附件

[此贴子已经被作者于2008-5-5 0:49:28编辑过]

lz889999

QUOTE:

以下是引用samsoncsr在2008-5-5 0:22:12的发言：

考虑了一下，还是这样效率高些：

输入班次，表格和班主任自动变化。

观察了一下：不同的学校班级不会重复（如果会重复，公式要更改），那就用2个数组公式：

A6：=INDEX(一至六年级总花名册!C:C,SMALL(IF(一至六年级总花名册!$M$1:$M$3000=各年级各班考号!$C$3,ROW($1:$3000),63356),ROW(1:1)))&""

按Ctrl+Shift+Enter结束。

选中A6，下托至37行，选中A6:A37，右托3列。

F6：=INDEX(一至六年级总花名册!C:C,SMALL(IF(一至六年级总花名册!$M$1:$M$3000=各年级各班考号!$C$3,ROW($1:$3000),63356),ROW(33:33)))&""

按Ctrl+Shift+Enter结束。

选中F6，下托至37行，选中F6:F37，右托3列。

还有个要注意的：如果班级人数多于64人，表格数据会溢出，那么可以更改以上公式的红色部分。

提醒一下：在“一至六年级总花名册”表中的M列班级数值输入有些问题，很多存在空格，这样会造成查找不到，我已经通过分列的方法修正。

  

0YPowzcq.rar (146.45 KB, 下载次数: 307)

2008-5-5 22:45 上传

点击文件名下载附件
[求助]如何设置公式生成1至6年级各班的学生考号名单？

samsoncsr

如果按照人头分组，第二个表的C3不应该是班级吧？我暂时变更为考场1～考场45（有效性的数据源隐藏于A列）

 

j5WNbYHl.rar (147.02 KB, 下载次数: 245)

2008-5-6 00:12 上传

点击文件名下载附件
[求助]如何设置公式生成1至6年级各班的学生考号名单？

lz889999

QUOTE:

以下是引用samsoncsr在2008-5-6 0:13:00的发言：

如果按照人头分组，第二个表的C3不应该是班级吧？我暂时变更为考场1～考场45（有效性的数据源隐藏于A列）

 

       samsoncsr老师：衷心感谢您的帮助和指导！这个“考号和座签号工作表”终于基本完成了！能够从手工中解脱出来了！在以后的实际运用中，若需有修正的地方，还要请你再出手相救！谢谢！

  另外：有一点比较含糊，请指导：

      [提醒一下：在“一至六年级总花名册”表中的M列班级数值输入有些问题，很多存在空格，这样会造成查找不到，我已经通过分列的方法修正。]----这是你的回复！

    1、在““一至六年级总花名册”表中的M列班级数值输入有些问题，很多存在空格，这样会造成查找不到，”用什么方法可以看出来“M列班级数值输入有些问题，存在空格“。

    2、您通过“分列的方法修正”，我也试着用”分列“的方法试了一下，没看出什么,也没有变化，还是文本？  

    3、“M列班级数值”实质是“M列班级文本转换为数值”，按照您的“分列修正法”，我没有做出来，然后，我通过复制“M列班级”》编辑》选择性粘贴》加”的方法，转换成数值，这种方法正确吗？

   谢谢老师！

samsoncsr

可能是我判断错了，应该是M列的是文本格式数值，（可能原本M列是文本格式，你把M列设置为常规，可是M列的数据并没有立马生效，这个是新手经常遇到的困惑，在论坛中也是经常被询问，我个人觉的这个是EXCEL的BUG吧？通过分列可以让其生效，），在有些公式判断中，同样的数值，但是格式不同（如文本型数值和数字型数值），可能造成出错。

至于如何判断，我是在看筛选结果的时候发觉不对劲，才去校对M列的数据，发现有问题的。

如果感兴趣，你可以把你一楼发的附件重新做公式后，选不同班级看看学生数量是否正确就知道了。

分列步骤：

选中M列》数据/分列》完成

也可以这样：

复制空列（比如BF列），选中M列，编辑》选择性粘贴》选“加”》确定

你的方法没试过，你可以用公式判断一下有没有生效（比如ISTEXT（A1），结果是TRUE的就是文本）。

祝老师为祖国多多培育人才：—）

[此贴子已经被作者于2008-5-6 20:36:10编辑过]