学校Excel“分班”终极技巧放送 Post By：2008-6-22 11:38:35

[复制链接]

前进

电梯直达

1^#

发表于 2009-1-13 05:54:40 | 只看该作者回帖奖励

|倒序浏览 |阅读模式

　暑假即将结束时，一所私立学校在火烧眉毛之际，请本“教头”去为新生分班，本“教头”驾驶“粮食发动机”驱动的Bike到了以后，才发现他们的机器上没有装FoxPro，学生成绩还是在Word中录入的，而且机器还没有光驱(关门挤鼻子——真是巧)。　　按常理，得找光盘→拆机器→装光驱→安装FoxPro→写程序，一番拳脚折腾下来，肯定得“日落西山红霞飞”了。但是本人借助Excel，仅用10分钟就“把营归”了，本“教头”
不敢独专其利，谨将操作步骤简述如下，让那些不会FoxPro的同行们，也能轻轻松松赚一顿小酒，杀一杀“内存病毒”，呵呵。
　　领会精神明确方法
　　本“教头”一边风度翩翩地喝着主人敬过来的水，一边“耐心+虚心”地询问分班的要求，半杯水进入“内存”后，本教头就深刻地领会了校方分班的指导精神：
　　1、各班的男生、女生比例要基本相同。
　　2、按成绩分为8个班，采用名次“轮回法”，如图1所示。

图1
　　转换数据调整字段
　　受命于危难之际，本“教头”自不敢怠慢，喝完主人一杯水，就将满腔热情落实在具体行动上了。
　　1、创建“新生分班”工作簿
　　打开Excel，新建一工作簿“新生分班”，将Sheet1改名为“学生数据”。
　　2、插入新生数据到“新生分班”工作簿
　　打开存储学生数据的Word文档，将其中的表格数据“复制、粘贴”到Excel中的“新生分班/学生数据”工作表中。
　　3、调整工作表结构
　　打开“学生数据”工作表，调整工作表结构如图2所示。

图2
　　排名排序汇总分班
　　1、在“年级名次”栏填充名次
　　查看学生名单，在“年级名次”一列中，单击鼠标左键，选择D2单元格(第1位同学名次所在单元格)，“照葫芦画瓢”地输入下列公式“=RANK(C2,C2:C401,0)”
本“教头”提示：
　　(1)公式中的“C”为成绩所在列的列号，C2、C401分别为第一位同学和最后一位同学的成绩所在的单元格，具体字母、数字根据实际情况而定。公式中的“”不可省略，最后一个“0”也不可省略。
　　(2)注意不要用“=IF(c2=c1,d1,row(c2)-1)”，不信？试试就知道了。
　　输入完以上公式后回车，即可自动得出该学生的名次如图3所示。此后，鼠标左键双击(如果你愿意，也可以用“拖拉##”)D2单元格右下角的控点(是一个小黑方块)，即可自动得出全部学生的名次了。

图3
　　2、按性别、名次排序
　　选择菜单“数据→排序”，显示图4所示对话框，按照图中的样子调整参数，单击[确定]按钮即可。

图4
　　3、填充班号
　　在“班号”一栏中，按照如此循环填充班号：
　　1-2-3-4-5-6-7-8→8-7-6-5-4-3
　　-2-1→1-2-3-4-5-6-7-8→8-7-6-5-4-3-2-1……
　　[方法一]：手工方法
　　虽说是手工，但实际上并不慢，因为只需人工填写一个循环节即可，其余的可以复制、粘贴。
　　[方法二]：自动填充
　　(1)选择菜单“工具→选项”，在弹出的对话框中单击“自定义序列”标签，在左侧小窗口中单击“新序列”，在右侧小窗口中输入“b8,b7,b6,b5,b4,b3,b2,b1,b1,b2,b3,b4,b5,b6,b7,b8”(b为班，引号内的每一个逗号都代表一个回车。注意序列是b8—b1—b1—b8，而不是b1—b8—b8—b1，为什么？自己试一试就知道了)，如图5所示。输入完毕，单击[确定]按钮退出。

图5
　　(2)在“E2”(第一名同学“班号”所在的单元格)中输入“b1”后，用鼠标左键双击“E2”单元格右下角的控点，即可自动得出全部学生的班号。
　　本“教头”提示：
　　自定义序列可以修改，修改时在图5左侧的小窗口中选择自定义的序列后，单击右边的小窗口即可编辑修改
4、按班号排序
　　选择菜单“数据→排序”，显示新的对话框，调整参数如图6所示，单击[确定]按钮即可。

图6
　　本“教头”提示：
　　图中的“次要关键字”、“第三关键字”可根据实际需要进行调整。
　　5、按“班号”分类汇总

图7
　　(1)选择菜单“数据→分类汇总”，显示如图7所示的对话框，调整参数如图所示(一定要选中“每组数据分页”，否则打印出来班与班之间不分页，那就得用“剪刀##”了)，然后单击[确定]按钮即可。
　　(2)选择菜单“编辑→替换”，将“计数”替换为“班人数”。
　　6、设置页面，排版打印

图8
　　选择菜单“文件→页面设置”，显示对话框，调整参数如图8所示，单击[确定]按钮即可(图中参数中要注意“缩放比例”，它的大小由内容决定：内容窄、短，则比例要大；反之，比例要小)。

图9
　　接着在“页面设置对话框→工作表”中按图9所示调整好参数后单击[确定]按钮即可(要注意“顶端标题行”的设置，假设有N行标题，就是“1:N”)。
　　好了，到此任务已经完成，我的分班经历也已经结束，本“教头”喝酒杀毒去也(盛情难却呀)

收藏0