用EXCEL轻松的工作

1、.用EXCEL轻松的工作 目录用EXCEL轻松的准备考前工作1用EXCEL轻松的处理学生成绩6Excel快速查找成绩表中出错的号码12用EXCEL进行学生成绩管理小技巧14巧用Excel函数统计体育成绩18Excel应对特殊学生成绩分析统计21Excel函数自动判断、填充信息26用Excel实现网络化的考试29excel为学生考试成绩排名34用EXCEL轻松的准备考前工作 大考在即，主任要求“考务工作必须电子化”，为了万无一失，还特意提供给班主任一份考务工作流程图： 考前：考场编排打印单科成绩册打印考场记录单打印准考证号 考后：录入各科成绩计算总分、平均分并排定名次统计各科分数段人数、及格率、

2、优秀率及综合指数打印各种统计报表制作各科统计分析图表 既然任务已经明确，先把考前的准备工作做好吧！具体工作包括：考场编排打印单科成绩册打印考场记录单打印准考证号 1、单科成绩册的编制和准考证号的自动编制 （1）我负责的班级这次考试共7门课程，为了区分科目，只需在Excel表头处设置“科目”、“班级”和“任课教师姓名”等。考生的姓名可以从学生学籍表中提取，考号如何编制和处理呢？为了以后登分和查找方便，考号基本按学籍号顺序把整个年级的考号放在一起编码。编码由两部分组成：考场编号（三位数字），班内顺序号（三位数字）。为了加快录入速度，分别把它们放在两列中，这样可以分别进行填充。 （2）新建工作表“考

3、场编号”（如图1），从学生学籍表中复制“姓名”列置于该表A列，首先设置B（准考证号）、C（班内编号）、D（考场编号）列的数据格式为文本方式。 （3）在B2单元格输入公式“=D2&C2”（其中的“&”为连接运算，将“考场编号”和“班内编号”连接成一个字符串），用鼠标选中B2单元格，将鼠标指向该区域右下角的填充柄，双击鼠标（这可是本人总结出来的最快的填充方法，下称“双击填充法”）即可将该公式自动填充至最后一个考生（因为此时还没有在C、D列输入数据，暂时B列中数据也没有出现）。在C2单元格中输入“001”，C3单元格输入“002”，用鼠标拖动区域C2：C3，将鼠标指向该区域右下角的填

4、充柄，用上述“双击填充大法”即可对全班学生顺序编号。然后每隔30人（一个标准考场）插入一空行，在D2和D3单元格中全部输入“001”，同样用“双击填充大法”迅速将第一考场的编号全部填充。此时B2：B31单元格已经全部自动填上了考生的准考证号。对第二考场，只需在D33和D34格中输入“002”，并向下填充即可。 2、打印考场记录单 考场记录单是供监考教师监考时核对考生数目和身份时用的。在上述“考场编号”工作表中，利用Excel的“分类汇总”功能，可以快速方便的达到这一目的。操作步骤如下： （1）在表中单击任一数据单元格，在“数据”菜单中，单击“分类汇总”命令。 （2）在“分类字段”下拉列表框中，

5、单击需要用来分类汇总的数据列（“考场编号”列）。 (3）在“汇总方式”下拉列表框中，选择“计数”。 （4）在“选定汇总项（可有多个）”框中，复选“考场编号”框。 （5） 复选“每组数据分页”选项，以便每个考场单打印一页。其他两项“替换当前分类汇总”和“汇兑结果显示在数据下方”可根据情况选定。 （6）单击确定，考场记录单就已经做好了（如图2）。 （7）为了使每一页都打印同样的表头：“2001年期末考试 第X考场 考场单”，单击菜单“文件页面设置页眉/页脚自定义页眉(C)”，进行相关设置。为了更加美观，在“页面设置”的“页边距”窗口复选“水平居中”和“垂直居中”，并定义好纸张的大小。 说明： （1

6、）“分类汇总”前，必须按“考场编号”列排序，本例中已经是排好顺序的。 （2）对“分类汇总结”的结果不满意时，可以清除“分类汇总”，Excel将同时清除分级显示和插入“分类汇总”时产生的所有自动分页符。 方法是：单击任一单元格，在“数据”菜单中，选择“分类汇总”命令，单击全部删除按钮。 3、打印准考证号码条 准考证号码条一般粘贴在课桌左上角，供考生寻找自己的座位用，过去主要靠手工抄写的方法完成。这里我用word的“邮件合并”功能巧妙地完成了这一任务。方法如下： 启动word 2000，执行“工具”菜单下的“邮件合并”命令，弹出“邮件合并帮助器”，首先在当前窗口创建一个邮件标签。依次点击“创建邮件

7、标签活动窗口”，然后点击获取数据按钮，在下拉列表中选“打开数据源”，在文件类型列表框中把文件类型改为“MS Excel工作簿”，打开刚才“考场编号”工作表，按确定按钮。接着设置主文档，点击新建标签按钮，弹出如图3所示“新建自定义标签”对话框。 在“标签名称”文本框中填上“座位号”，在“页面尺寸”下拉列表框中选“A4横向”。按确定按钮，出现“创建标签”对话框。点击“插入合并域”，插入“准考证号”、“姓名”两个域，按确定按钮，最后按合并按钮。 好，现在的考前准备工作基本完成，只等考试成绩出来了！用EXCEL轻松的处理学生成绩 期末考试结束后，主任要求班主任自已统计本班成绩，尽快上报教导处。流程包括

8、录入各科成绩计算总分、平均分并排定名次统计各科分数段人数、及格率、优秀率及综合指数打印各种统计报表制作各科统计分析图表等。有了Excel，我们可用不着躬着身、驼着背、拿着计算器一个一个算着学生的成绩了！ 我迅速地打开电脑，启动Excel2000，录入学生的考试成绩，如图1所示。然后在J2单元格处输入公式"=sum(c2:i2)",然后拖动填充柄向下填充，便得到了每人的总分。接着在k2单元格处输入公式"=average(c2:i2)"，然后拖动填充柄向下填充，便得到了每人的平均分。 平均分只需保留一位小数，多了没用。所以选中第k列，用鼠标右键单击，从弹出的

9、快捷菜单中选"设置单元格格式（F）",如图2所示，在数字标签中选中"数值"，小数位数设置为1位。 下面按总分给学生排出名次。 在L2单元格处输入公式"RANK(J2,J$2:J$77,0)",然后拖动填充柄向下填充，即可得到每人在班中的名次（请参考图1）。 说明：此处排名次用到了RANK函数，它的语法为： RANK(number,ref,order) 其中number为需要找到排位的数字。 Ref为包含一组数字的数组或引用。Ref 中的非数值型参数将被忽略。 Order为一数字，指明排位的方式。 ·如果 order 为 0

10、或省略，Microsoft Excel 将 ref 当作按降序排列的数据清单进行排位。 ·如果 order 不为零，Microsoft Excel 将 ref 当作按升序排列的数据清单进行排位。 最后，单击L1单元格，然后在“工具”菜单中选“排序”“升序”，即可按照名次顺序显示各学生成绩。 另外，我们还希望把不及格的学科突出显示，最好用红色显示。于是拖拉选择C2:E78(即所有学生语、数、外三科成绩)，然后执行"格式"菜单下"条件格式"命令，弹出"条件格式对话框"。我们把条件设为小于72分的用红色显示（因为这三科每科总分为1

11、20分），点击"格式"按钮，把颜色设为红色。再按"确定"按钮。然后用同样的方法把理、化、政、历四科小于60分的也用红色显示（因为这四科每科总分为100分）。 下面我们来统计各科的分数段以及及格率、优生率、综合指数等。下面我们来统计各科的分数段以及及格率、优生率、综合指数等。 （1）60分以下人数：在C78单元格处输入公式"=COUNTIF(C2:C77,"60")"，拖动填充柄向右填充至I78单元格处； （2）60分69分人数：在C79单元格处输入公式"=COUNTIF(C2:C77,"=60&

12、quot;)-COUNTIF(C2:C77,"=70")"，拖动填充柄向右填充； （3）70分79分人数：在C80单元格处输入公式"=COUNTIF(C2:C77,"=70")-COUNTIF(C2:C77,"=80")"，拖动填充柄向右填充； （4）80分89分人数：在C81单元格处输入公式"=COUNTIF(C2:C77,"=80")-COUNTIF(C2:C77,"=90")"，拖动填充柄向右填充； （5）90分以上人数：在C82单元格处输

13、入公式"=COUNTIF(C2:C77,"=90")"，拖动填充柄向右填充； （6）平均分：在C83单元格处输入公式"=AVERAGE(C2:C77)",拖动填充柄向右填充至I83； （7）最高分：在C84单元格处输入公式"=MAX(C2:C77)",拖动填充柄向右填充至I84； （8）低分率：是指各科40分以下人数与总人数的比值。在C85单元格处输入公式"=COUNTIF(C2:C77,"=40")/COUNT(C2:C77)*100"，拖动填充柄向右填充至I85； （9）

14、及格率：语、数、外三科及格分为72分，所以在C86单元格处输入公式"=(COUNTIF(C2:C77,"=72")/COUNT(C2:C77)*100"，并拖动填充柄向右填充至E86；而理、化、政、历等四科及格分60分，所以在F86单元格处输入公式"=(COUNTIF(F2:F77,"=60")/COUNT(F2:F77)*100"，并拖动填充柄向右填充至I86； （10）优生率：语、数、外三科96分以上为优生，所以在C87单元格处输入公式"=(COUNTIF(C2:C77,"=96"

15、)/COUNT(C2:C77)*100"，拖动填充柄向右填充至E87；理、化、政、历等四科80分以上为优生，所以在F87单元格处输入公式"=(COUNTIF(F2:F77,"=80")/COUNT(F2:F77)*100"，拖动填充柄向右填充至I87处；如图3所示。 （11）综合指数：我们学校的综合指数的计算公式为z=(1+优生率低分率)/2+及格率+平均分/该科总分/3。所以在C88单元格处输入公式"=(1+C87/100-C85/100)/2+C86/100+C83/120)/3"，拖动填充柄向右填充至E88；在F88单

16、元格处输入公式"=(1+F87/100-F85/100)/2+F86/100+F83/100)/3"，拖动填充柄向右填充至I88。如图3所示。 对了，为了让别人对各科的分数段有一个较直观的认识，可以考虑采用图表。单击“插入”菜单中“图表”命令，弹出“图表向导”对话框，在“图表类型”列表框中选择一种图型，如“饼图”，单击“下一步”，单击“数据区域”文本框右边的压缩列表框，拖拉选择B78：C82，再次点击该压缩列表框；单击“下一步”，输入图表标题，如“高一（1）班语文成绩分析图”；单击“下一步”，再单击“完成”。如图4所示。其它各科同样处理，但在拖拉选择数据区域时，因为是不连续

17、的区域，所以要按住“Ctrl”键。好！一切OK！ 且慢！为了以后的考试中不再重复上述繁琐的工作，最好把上述工作表另存为一个模板。于是我把上述工作表复制一份到另一工作簿中，然后删掉所有学生的单科成绩（即表中C2:I77部分），执行"文件"菜单中的"另存为"命令，在"保存类型"下拉列表框中选"模板（*.xlt）"，把它保存为一个模板文件，这下可以一劳永逸了。Excel快速查找成绩表中出错的号码为了高考，学校购进了光电阅卷机，方便了老师们的阅卷，而且还可以很方便地将学生的卷一部分成绩导出到Excel表格。老师们只需要将每

18、位学生的卷二部分得分再输入到Excel表格中，两部分成绩相加，就可以得到该生该科目的最后成绩了。看起来似乎很简单，不过，卷一成绩表中只有学生的准考证号而没有姓名信息，又经常有个别学生将自己的准考证号涂错，结果学生的卷一、卷二的号、名对应不起来，把个简单的事情搞得挺麻烦。那么能不能利用Excel现有的功能，把这些出错学生的姓名快速找出来呢？答案当然应该是肯定的。 如图1所示，这是一份卷一和卷二成绩表。前者系自动生成，而后者我们可以手工输入并将它和卷一成绩表放到同一个工作簿中。如果有错误考号出现的话，那么通常出现在“卷一成绩”工作表中。1我们要做的是：单击“卷一成绩”工作表中的C1：E！单元格，分

19、别输入“卷二分数”、“总分”、“姓名”，然后单击C2单元格，输入如下公式：=VLOOKUP($A2,卷二成绩!$A$2:$C$21,3,FALSE)，按下回车键，现在，应该可以看到该单元格出现了该同学的卷二分数。单击D2单元格，输入公式：=SUM(B2:C2)，按下回车键后计算出该同学的实际分数。单击E2单元格，输入公式：=VLOOKUP($A2,卷二成绩!$A$2:$C$21,2,FALSE)，按下回车键就可以查找到该考号所对应的学生姓名了。 选中C2：E2单元格，将鼠标移至右下角的填充句柄，向下拖动至最后一行，松开鼠标，则所有学生的卷二成绩、总分及相应姓名都可以显示出来了。如果准考证号涂写

20、有错误的话，则会出现错误信息进行必要的提醒。如图2所示。怎么样？不错吧！2点击“卷二成绩”工作表。单击D1单元格和E1单元格，分别输入“卷一分数”和“总分”。然后单击中D2单元格，输入如下内容：=VLOOKUP($A2,卷一成绩!$A$2:$C$21,2,FALSE)，按下回车键，显示该同学卷一成绩。单击E2单元格，输入公式：=SUM(C2:D2)，按下回车键，显示总分。将鼠标移至右下角的填充句柄，按下鼠标向下拖动到最后一行。则可以得到全体学生的全部成绩。如果“卷一成绩”工作表中准考证号涂写有错，那么同样会出现相关提示。如图3所示。综合两份工作表中的相关信息，现在我们很容易就可以看出本例是83

21、1160207同学出错了。3您可以把这个工作簿保存起来，以后每次考试，只需要把两份成绩分别拷贝到相应的工作表中就可以了。方便吧？用EXCEL进行学生成绩管理小技巧一、快速转换学生考试成绩等级 有的时候，会遇到要将学生的考试成绩按实际考试分数转换成相应成绩等级的情况，如将考试成绩在90分以上的成绩转换成“A+”形式，85-89分的成绩转换成“A”形式.。一般情况，在EXCEL表格中大家会采用IF()函数来设计公式进行转换，这样所设计的公式会变得很复杂，如果进行转换的成绩等级类型超过IF()函数的最大嵌套(7层)时，IF()函数就无能为力了。这时我们可用如下的方法来简化操作。 1、打开学生成绩工作

22、表(格式内容见图1)。 图1 2、在G2到I12单元格录入考试成绩分数段与考试成绩等级对照表。 3、在D3单元格录入公式“=INDEX(I$3:I$12,MATCH(1,(C3>=G$3:G$12)*(C3<=H$3:H$12),0)”，由于该公式为数组公式，在录入完上述内容后，必须同时按下“Ctrl+Shift+Enter”键，为上述公式内容加上数组公式标志即大括号“”。该公式的作用就是，根据C3单元格中的学生成绩，在D3单元格自动将该成绩转换成相应的成绩等级。 4、将光标移到D3单元格，向下拖动填充柄至D12单元格，将公式进行快速复制，这样就可以迅速完成转换学生成绩等级的工作(

23、效果见图2)。 图2 5、还可以按照自己的喜好，将G2至I12的单元格区域设置为“隐藏”，以使表格更加美观。 二、快速统计学生考试成绩分布情况 在利用EXCEL管理学生考试成绩时，常常要统计各分数段学生考试成绩的分布情况，如果采用下面介绍的这种方法，就能使这项工作变得非常方便。 1、打开学生成绩工作表(本例仍使用上例的工作表，格式见图1)。 2、在G3至G6单元格录入学生考试成绩的统计分段点。如在本例中采用的统计分段点为：60、69、79、89，即统计60分以下、61-69、70-79、80-89、90分以上五个学生考试成绩区段的人数分布情况，当然你也可以根据自己的实际需要在此进行不同的设置。

24、 3、选中要进行公式设计的单元格区域B14至B18，按下F2键，录入公式“=FREQUENCY(C3:C12,G3:G6)”，由于该公式为数组公式，在录入完上述内容后，必须同时按下“Ctrl+Shift+Enter”键，为上述公式内容加上数组公式标志即大括号“”。 4、当上述操作完成后，在B14至B18单元格就迅速得到了正确的学生考试成绩分布情况(图3)。 图3 5、值得注意的是，在我们设计统计区段时，这个统计区段必须比统计分段点的数据个数多一个。这个多出来的统计区段表示超出最高间隔的数值个数。例如，在本例中，我们设计的统计分段点为60、69、79、89四个数值，这时要想取得正确的统计区段分布

25、数据，就必须在B14至B18五个单元格中输入 FREQUENCY() 函数计算的结果，多出来的这一个单元格将返回学生成绩表中大于90分的成绩的人数。巧用Excel函数统计体育成绩 用Excel计算学生学习成绩已经是一个老话题了，但很少有用Excel来计算体育成绩。体育成绩跟其它成绩不同，学生测试以后没有直接得出分数，而是一项一项的数据，如100米跑了12秒这样的数据。然后要把这些数据按照国家体育锻炼标准折合成各项分数，最后还要将各项分数按一定比例累加起来得出最终的体育成绩。 如果用Excel的LOOKUP函数来做就显得非常简单，你只要在Excel中进行几个简单的操作就可以计算出学生体育成绩。下

26、面我们看看如何用Excel来计算学生的体育成绩： Step1 录入各项成绩 体育科目里有些成绩，如中长跑是以时间来记分的，一个一个录入时间太麻烦了，所以为了提高录入速度，对于要求输入时间格式的单元格(如我们表中第D列)，可以选定D列，然后点击“格式单元格”，在“数字”选项卡下选择“自定义”格式， 暗 窀袷健弊远逦 ?#”型(图1)。这样当我们需要录入时间“245”时，只要在单元格中录入“245”回车即可得到，给我们录入时间成绩带来极大的方便。 在每一项(列)成绩录完后应在该列右边空上一列用来给这项成绩打分(图2)。如果你经常用到这个评分表，还不如把这个文件保存为模板文件(*.XLT)，下次用到

27、的时候就不必重新设置了。 Excel应对特殊学生成绩分析统计 1考试混合编，成绩统计-老方法遇到新问题 关于使用Excel进行学生成绩处理，已经是老话题了。但在实际工作中还是会有很多新问题，例如，现在很多学校都是全年级各班混在一起考试，以防考试改卷中的不正当竞争。而统计成绩时，则是将已判分但未拆封的考卷统一交到教务处，先按座位号顺序(每本考卷的自然顺序)录入各科分数，再分析统计出全年级各科成绩。举例说明，如图1(记录11至830隐藏了)，要统计二(1)班优秀人数，传统做法就是先按考试号排序，再通过公式“=COUNTIF(分数!D2:D69,">=96")”求出。它的弊

28、端是要手工逐个修改 “D2:D69”这个参数中的两个行号(2和69)，这可是一项工作量很大的工作。当然，简单的方法还是有的，往下看吧。 2初步准备-考试号里提班级 如图1，从B列的考试号中取出前三位(班级编号)放在S列，即在单元格S2输入公式“=LEFT(B2,3)”，然后双击(或拖动)S2单元格右下角的填充柄即可。 3再做辅表-班级等级二合一 在图1所示的工作簿中再新建一工作表，并将其命名为“等级”，在单元格A1中输入公式“=分数!A1”，回车，选定A1，按住A1右下角的填充柄向右下拖至C840单元格，将“分数”工作表中的姓名、考号、座位号引用到“等级”工作表中(注意，千万不能复制粘贴过来，

29、这样不能保持两表数据的一致性)。再选定C1，按住C1右下角的填充柄向右拖至L1单元格，将语文、数学等9个学科科目引用过来。接着，在D2单元格中输入IF嵌套公式“=IF(分数!D2>=96,分数!$S2&&"a",IF(分数!D2>=72,分数!$S2&&"b",IF(分数!D2<48,分数!$S2&&"d",分数!$S2&&"c")”。D2单元格中公式的含义是：看“分数!D2”单元格中的分数(即“分数”工作表中李悦的语文分数)是否大于

30、等于96。如果是，则在D2单元格中填入“201a”“分数”工作表中S2单元格中的字符“201”加上“a”(“201”表示二(1)班，“a”表示成绩等级为“优秀”)；如果不是(即小于96)，再看是否大于等于72。如果是，则在D2单元格中填入“201b”；如果不是(即小于72)，再看是否小于48。如果是，则在D2单元格中填入“201d”；如果不是(即小于72大于48)，则在D2单元格中填入“201c”。最后按住D2单元格右下角的填充柄向右下拖至L840单元格，就可以将每个学生各科成绩的等级及所属班级都填好了 4最终统计-所需数据瞬间齐 辅表制好之后，言归正传回到“统计”工作表(如图2)中，在A17

31、到E28单元格区域中利用自动填充功能再制作一小块辅助数据(如图2)。 万事俱备，下面开始班级总人数及优秀率、及格率等的统计了。仍以二(1)班优秀率为例，现在就改用这样的公式了“COUNTIF(等级!$D:$D,$B17)”，即对“等级”工作表中D列所有单元格进行统计(等级!$D:$D)，找出值为“201a”(本工作表即“统计”工作表的$B17的值，代表二(1)班优秀率)的单元格数目。 具体做法如下： (1)班级总人数(在B4单元格中输入)：“=COUNTIF(分数!$S:$S,A17)”； (2)优秀人数（在C4单元格中输入)：“=COUNTIF(等级!$D:$D,$B17)”； (3)优秀率

32、（在D4单元格中输入)：“=C4/$B4 100”； (4)及格人数（在E4单元格中输入)：“=COUNTIF(等级!$D:$D,$B17)+COUNTIF(等级!$D:$D,$C17)”； (5)及格率(在F4单元格中输入)：“=E4/$B4 100”； (6)低分人数(在G4单元格中输入)：”=COUNTIF(等级!$D:$D,$E17)”； (7)低分率(在H4单元格中输入)：“=G4/$B4 100”； 到此为止，其余数据通过自动填充功能，瞬间即可完成。 5方法点评-一表成，终年用，一劳而永逸 (1)不同年级成绩统计的简单套用：比如，首先制作好了一年级的统计表，通过复制粘贴将第一个工作

33、表(“分数”工作表)的内容更改为二年级的数据表，则二年级的成绩统计便自然而成。 (2)多次考试成绩统计的简单套用：这次考试的统计表，到下次考试成绩统计时，照用不误，只将第一个工作表换成新生的成绩记载就可以了。 在Excel中快速完成成绩统计查询每次考试结束之后，最让老师头痛的就是成绩查询了。现在，我们可以利用Excel 2000的系统函数建立一个成绩查询系统，只要您在指定单元格中输入欲查询成绩的学生姓名，他的各科考试成绩和名次就会自动显示出来。非常便利。在Excel中，双击“Sheet1”，将其命名为“成绩统计”，然后建立一个如图1所示的成绩统计表，输入全部学生的各科成绩备用。用常规的方法计算

34、出各学生的总分，并排出名次（这里就不详述做法了）。图1双击“Sheet2”，将其命名为“成绩查询”。在“成绩查询”工作表中，建立如图2所示的表格。单击B2单元格，输入欲查询成绩的学生姓名。单击B3单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B $3:$I$56,2,FALSE)”，按回车键，则可以得到该名学生的语文成绩；单击B4单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,3,FALSE)”，按回车键，得到数学成绩；单击B5单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,4,FALSE)”，按回车键，得到英语成绩

35、；单击B6单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,5,FALSE)”，按回车键，得到机械成绩；单击B7单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,6,FALSE)”，按回车键，得到电工成绩；单击B8单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,7,FALSE)”，按回车键，得到总分；单击B9单元格，在其中输入“=VLOOKUP($B$2,成绩统计!$B$3:$I$56,8,FALSE)”，按回车键，得到名次。到此，我们就可以得到该名学生的全部考试成绩以及总分、名次了。图2有了这个，以后

36、我们只需要在“成绩查询”工作表的B2单元格输入要查询成绩的学生姓名，然后回车，则相关的信息就立刻可以显示。现在，查询成绩该很轻松了吧！Excel函数自动判断、填充信息笔者的一位朋友在一所大学的学生信息中心工作，她常常需要用Excel制作如图1所示的学生信息表。可以看出这张表格有来自不同年级、学院和系别的同学，由于这个原因，平时她基本上都是手动输入这些信息的。由于笔者也完成过类似的任务，知道位于第一列的学号是按一定规律编排的。为了提高工作效率，其实可以根据这个规律，结合Excel的公式和函数快速地从学号中提取出相应的信息，自动填入到相应的单元格中。当数据量比较大时，这种方法的优越性非常明显。 一

37、、前提条件：了解学号的编排规律 利用这个方法的前提条件是学号是按一定规律编排的，在实际情况中几乎都是如此。例如，图1表格中学号的编排规律是：学号的第一位数代表生源地区，如果是“5”就代表“外地生源”，如果是“4”就代表“本地生源”。第二至第三位代表年级，第四至第六位代表学院，比如“110”代表“数科院”，“111”代表“信息学院”等。第七至第八位代表“系别”，比如“24”代表“数学系”，“27”代表“计算机系”等，最后三位代表所在班级的学号。 二、 使用IF函数建立公式从学号中提取信息 1由于“学号”的数据类型为文本，因此设置A列的单元格格式的数据类型为“文本”。 2选中C2单元格，建立根据学

38、号得出学生生源信息数据的公式。在C2单元格中输入的公式为：“=IF(A2="","",IF(LEFT(A2,1)="5","外地生源",IF(LEFT(A2,1)="4","本地生源","无效的生源代码")”。这个公式的含义是：如果在A2中还没填入学号，就返回一个空字符串，如果填入的学号的第一位字符为“5”，则返回字符“外地生源”，如果第一位字符为“4”，则返回“本地生源”，如果是其他字符，就返回“无效的生源代码”。输入公式后，利用公式填充柄复制公式到下方

39、恰当的单元格。后面请每次输入公式后，都复制到恰当的单元格。 （函数具体用法请参阅Excel常用函数及实例剖析） 小提示：由于学号的数据类型为文本，而不是数值，所以在条件“LEFT(A2,1)="5"”中，要对数字5加引号。下面的情况同理。 3选中D2单元格，建立根据学号得出学生年级数据的公式。在D2单元格中输入的公式为：“=IF(A2="","",MID(A2,2,2)&"级")”。其中MID(A2,2,2)的含义是，从A2学号中的第二位开始，提取二个字符。 4选中E2单元格，建立根据学号得出学生学院的公式

40、。在E2单元格中输入的公式为：“=IF(MID(A2,4,3)="110","数科院",IF(MID(A2,4,3)="111","信息学院",IF(MID(A2,4,3)="112","外语学院","无效的学院代码")”。这个公式的含义是：如果代表学院的字符串为“110”，则返回“数科院”；若是“111”，则返回“信息学院”；若是“112”则返回“外语学院”，若是其他情况，则返回“无效的学院代码”。 小提示：这里嵌套使用了IF函数，由于IF函数最多只能嵌

41、套七层，因此最多可以根据代码返回八个学院。如果实际情况中需要使用的学院数目超过了八个，则可以使用自定义函数。用Excel实现网络化的考试 早就想设计一个在线的网络考试系统，但是笔者的编程技术有限，暂时还无法完成一个正规的考试系统。后来经过摸索，笔者使用Excel完成了一个简单的考试系统，经过几次考试的实验与改进，完全可以在平时的教学中运用。现把实现的方法与大家分享。服务器端设置首先在教师机上建立一个test文件夹，并建立两个电子表格文件，分别命名为“test.xls”和“汇总.xls”，其中“test.xls”为试卷文件，“汇总.xls”是用来汇总的文件。同时在该文件夹内建立58个文件夹（假设

42、58个学生机），可以通过批处理的方法实现：md s1md s2md s58然后再将test.xls文件分别复制到这58个文件夹中：我们可以首先向s1中复制一个test.xls文件，然后再执行copy.dat，copy.dat内容如下：copy s1test.xls s2copy s2test.xls s3copy s56test.xls s57copy s57test.xls s58现在每个文件夹中都有一个test.xls了，下面我们编辑test.xls和汇总.xls文件。1 试卷testxls的设置test.xls如图1所示（假设以20道选择题为例）。其中C1单元格为姓名填写处，E1单元格为

43、学号填写处，A3A22为答案填写区（20道题），选择题的选项用数字“1、2、3、4”，不要用字母，以防止学生输入字母与空格的混合。并把答题区的颜色设置为灰色，防止学生抄袭。2 汇总xls的设置如图2所示，建立两个表，第一个为答案表，用来收集试卷的第一列的答案和学号姓名。A2单元格为“='D:tests1test.xls选择填空题'!$E$1”（统计学号），B2单元格为“=D:tests1test.xls选择填空题'!$C$1”（统计姓名），C2:V2分别为“='D:tests1test.xls选择填空题'!$A$3”（统计答案）、“='D:tests1test.xls选择填空题'!$A$4”“='D:te