电子表格制作实例1——用Excel制作学生成绩统计与查询.doc

用Excel实现学生信息与成绩查询问题1，班主任老师能否一次查询A生各次考试成绩？问题2，任课老师能否一次查询所教学科的各次考试成绩？回答：能。制作过程一、制作学生信息与成绩工作表1.打开Excel，新建一个名为“学生信息与成绩查询”的工作簿。2.反复插入工作表，使工作簿内共有10个工作表。然后依次命名为：XSXX(学生信息)，CJ1(成绩1，代表学期的第1次考试，其余依此类推)，CJ2，CJ3，CJ4，CJ5，CX1(查询1)，CX2。3.制作XSXX(学生信息)的工作表。在A1中输入“学生基本信息”，选中A1：K2单元格区域，格式合并居中。在A3：K3单元格中，依次输入列的标题字段学号、学生、 性别、民族、籍贯、出身年月、家庭住址、邮政编码、家长姓名、联系电话、备注。输入学号：学号一般都是一组序列号，因此可以用填充柄（填充柄：位于选定区域右下角的小黑方块。用鼠标指向填充柄时，鼠标的指针变为黑十字）拖拉输入。在A4、 A5中先分别输入XH01、XH02，然后选中A4、A5用填充柄向下拖拉至需要填入学号的单元格。学生姓名的输入：这个工作量比较大，无捷径可走，只能仔细一个个的输入，需要说明的是姓名为两个字的中间留一空格，以便在后面的查询时供条件判断。名称命名：命名的名称便于在学生成绩表中引用，选中A3，单击“插入”，指向“名称”，单击“定义”，名称为“学号”，引用位置：=XSXS!$A$4:$A$97。选中A4，名称为“姓名”，引用位置：=XSXX!$B$4:$B$97。(或者选中B4:B97单元格区域，在名称框中输入“姓名”后按下回车。特别说明：本文重在说明使用电子表格的一些方法与技巧，因此有些地方不免累赘)点拨：在数据的有效性设置中，“序列”来源的引用只能是单一的行或列，并且不能是其它工作表的引用，单元格名称可以在工作簿中引用。4.制作学生“XXXX年X考试学生成绩表”工作表。选择CJ1，后按住Shift，选择CJ5，使以CJ1到CJ5五个工作表成为一个工作组，以便同时编辑。工作组中表格的字段设计如下表（图附后）图表 1ABCDEFGHIJKLMN1XX年X考试学生成绩表 2学号学生语文 数学英语物理化学政治历史地理生物总分百分率名次3120120120100100505050507601004XH01XS0189.591.0103.080.583.544.04445.048.562982.845XH02XS0282.061.085.043.560.536.015.529.038.545159.3476XH03XS0374.06271677134.03541.04650165.928C3：K3分别为各科考试的卷面总分值。A4单元格中输入公式：=XSXX!A4。(公式实现CJ表中的学号与XSXX表中学号的链接)B4单元格中输入公式：=XSXX!B4。(或者对单元格进行数据的有效性设置，允许序列，序列来源为“=姓名”，姓名就是XSXX表中定义的名称，实际中引号不能输入。设置后，就可以从下拉列表中选择姓名，输入姓名亦可。不过，数据的有效性设置不能选择工作组，只能在一个工作表的单元格或区域中进行，但设置后可以进行复制粘贴操作。单击“工具选项”，选择“自定义序列”选项，选中列表框中“新序列”，在 “从单元格中导入序列”获取框中输入“XSXX!姓名”，然后点击“导入确定”按钮，完成序列添加。以后就可以以序列方式输入学生姓名。不过，XSXS工作表中的数据改变，自定义序列就要重新添加。)提示：公式、有效性、序列输入可以说是殊途同归，这里显然用公式更好些。L3中输入公式“=SUM(C3:K3)”统计总分。M3中输入公式“=L3/(SUM($C$3:$K$3)*100”计算百分率。N4中输入公式“=RANK(L4,$L$4:$L$96)”根据总分排名次。复制公式，用填充柄向下分别将公式复制到需要的行(这里是96行，根据学生人数而定)。点拨1：工作表中行列删除，切记不要删除公式的开始行和结尾行，含有公式的列勿做删除，如果删除将破坏查询工作表的单元格区域的引用，从而出现公式错误，对于确实不需要的列，可以采取隐藏方式达到不显示(也不会被打印)，方法是选中列标，右击鼠标，在弹出的快捷菜单中，选择“隐藏”。二、制作学生信息与成绩查询工作表CX1工作表供班主任老师分析本班学生一学期成绩使用1.在A1单元格中输入“学生成绩查询1 (按名次每次可查20个)”，用鼠标拖拉到N1，后格式为“合并居中”。2.在A2，B2，C2，D2，分别输入“输入期考”“输入名次”“学号”“学生”字段；回到CJ1工作表，连续选中C2：N2并复制，返回CX1工作表，选中E2单元格，后粘贴；在P2单元格中输入“名次”字段。3选中A3：A22单元格，格式为“合并居中”，再进行数据的有效性设置，允许序列，来源为“1，2，3，4，5”,代表学期的5次考试，供后面公式中的条件判断。4.在B4中输入公式“=B3+1”并用填充柄向下将公式复制到B22单元格。这样以后在使用时只需在B3中输入一个名次，B4：B22中就自动递增。5.在C3单元格中输入公式“=IF($A$3=1,INDEX(cj1!A$4:A$96,MATCH($B$3,cj1!$N$4:$N$96,0),IF($A$3=2,INDEX(cj2!A$4:A$96,MATCH($B$3,cj2!$N$4:$N$96,0),IF($A$3=3,INDEX(cj3!A$4:A$96,MATCH($B$3,cj3!$N$4:$N$96,0),IF($A$3=4,INDEX(cj4!A$4:A$96,MATCH($B$3,cj4!$N$4:$N$96,0), MATCH($B$3,cj5!$N$4:$N$96,0)，并用填充柄向右拖拉到P3单元格。点拨2：公式中仅有A$4:A$96 为相对引用列与绝对引用行的混合引用。因为公式向右复制中，学科字段改变，所以公式中相对列也要改变，以返回相应学科的数据。cj1!是对工作簿中Cj1工作表的引用，cj1!A$4:A$96表示引用工作表CJ1中A$4:A$96的区域。选中C3：P3单元格，先进行“编辑替换(查找内容为$B$3，替换为$B3)”，将绝对引用替换为绝对引用列相对引用行的混合引用，因为公式在向下复制中，公式中引用的B3：B22行的逻辑值也应相应变化。然后将C3：P3单元格的公式用填充柄向下复制到22行。公式看起来很繁琐，观察你会发现，一长串公式实际上只由三个函数嵌套而成，下边我们对公式逆推一一解说， 公式MATCH($B$3,cj5!$N$4:$N$96,0) 返回$B$3单元格中的值与“cj5”工作表$N$4:$N$96数组中相匹配的数组元素的相应位置，参数0是指定与之相等的第1个元素。公式INDEX(cj4!A$4:A$96,MATCH($B$3,cj4!$N$4:$N$96,0) 返回指定的列(工作表CJ4中A$4:A$96)与行(MATCH($B$3,cj4!$N$4:$N$96,0)返回的结果)交叉处的单元格的数值。公式IF($A$3=4,INDEX(cj4!A$4:A$96,MATCH($B$3,cj4!$N$4:$N$96,0) ,MATCH($B$3,cj5!$N$4:$N$96,0) 条件判断，如果$A$3=4,( $A$3，逻辑值单元格，“=4”逻辑值，表明公式将在CJ4工作表中查询并返回结果。)返回公式的结果，否则返回公式的结果。点拨3：如果将上述公式中$N$4:$N$96(名次列)替换成$A$4:$A$96(学号列)就可以按学号查询，替换成$B$4:$B$96(姓名列)就可以按姓名查询。制作完成的如图2(因工作表较宽，为了适应文档版面制作图片时隐藏了一些列，图3、图5同此。)图表 26.在A23输入“学生成绩综合查询2”(按学号或姓名查询一个学生一学期5次考试的综合成绩，或按名次查询一学期5次考试任意名次的得主。)7.复制A2：P2的字段，粘贴到A24：P24，将“输入名次”改为“查分条件”。8.在A25：A29分别输入1，2，3，4，5，代表学期的5次考试。B25：B29格式为合并居中，插入批注，“按姓名两字姓名中间留一格空,要区分全半角，并且必须与CJ表中的完全匹配学号至少5位数名次最多2位数查分”，条件查询提示。9.在C25中输入公式“=IF(LEN($B$25)4,INDEX(cj1!A$4:A$96,MATCH($B$25,cj1!$A$4:$A$96,0),IF(LEN($B$25)2,INDEX(cj1!A$4:A$96,MATCH($B$25,cj1!$B$4:$B$96,0),INDEX(cj1!A$4:A$96,MATCH($B$25,cj1!$N$4:$N$96,0)”，将公式复制到C26:C29,后将C26，C27，C28，C29公式中的cj1!(工作表的引用)分别替换为cj2! ，cj3! ，cj4!， cj5!，选中C25：C29单元格并用填充柄拖拉向右复制公式到P25：P29。公式解说，LEN($B$25) 返回单元格$B$25中文本字符串的字符数，大于4表明输入的是学号(在CJ工作表中学号至少是5位)，大于2表明输入的是姓名(如果姓名是2个字，中间加1空格)，否则输入的是名次。公式IF依据条件返回结果，这里就不赘述，依照C3单元格的公式揣摩。如图3、4、5分别按学号、学生姓名、名次查询的结果。图表 3图表 4图表 5CX2工作表 供科任老师分析所教班级学生一学期学科的走势。1.A1中输入“选择学科”字段。2.B1中进行数据的有效性设置，允许序列，来源为“1，2，3，4，5，6”,分别代表学生成绩工作表中的C2,D2,E2,F2,G2,H2的学科默认为N2的名次。插入批注：“语文C=1 数学D=2英语E=3 物理F4 化学G5政治H6名次N默认”供条件选择。3.C1中输入表头“学生单科考成绩查询表”并将C1合并居中到F1。4.A2:F2中分别输入“输入姓名”“cj1”“cj2”“cj3”“cj4”“cj5”5.B3中输入公式“=IF($B$1=1,INDEX(cj1!$C$4:$C$96,MATCH($A$3,cj1!$B$4:$B$96,0),IF($B$1=2,INDEX(cj1!$D$4:$D$96,MATCH($A$3,cj1!$B$4:$B$96,0),IF($B$1=3,INDEX(cj1!$E$4:$E$96,MATCH($A$3,cj1!$B$4:$B$96,0),IF($B$1=4,INDEX(cj1!$F$4:$F$96,MATCH($A$3,cj1!$B$4:$B$96,0),IF($B$1=5,INDEX(cj1!$G$4:$G$96,MATCH($A$3,cj1!$B$4:$B$96,0),IF($B$1=6,INDEX(cj1!$H$4:$H$96,MATCH($A$3,cj1!$B$4:$B$96,0),INDEX(cj1!$N$4:$N$96,MATCH($A$3,cj1!$B$4:$B$96,0)”，用填充柄向右将公式复制到F3，选中B3：F3将$A$3全部替换为$A3，再将C3，D3，E3，F3中的cj1!分别替换为cj2! ，cj3! ，cj4!， cj5!，然后选中B3：F3用填充柄向下将公式复制到97行。B3公式的意义是：当逻辑值的单元格$B$1的值为1时(表明查找语文学科的成绩)，运行函数INDEX，否则运行嵌套函数IF。运行函数INDEX（返回指定的行与列交叉处的单元格引用）时，先运行函数MATCH（返回在指定方式下与指定数值匹配的数组中元素的相应位置。），查找逻辑值的单元格$A3中的值（学生名字）在数组c1!$B$4:$B$96（学生名字列）中的行位置，再运行函数INDEX，返回其（学生名字行的位置）与数组列c1!$C$4:$C$96（语文科成绩列）相交的值（即学生的语文成绩）。选中A4单元格，进行数据的有效性设置，允许序列，来源“=姓名”(实际输入中不加引号，姓名就是xsxs表中定义的$b$4:$b$97单元格区域的名称。)，在查询中就可以从下拉列表中选择姓名，输入姓名亦可。制作完成的单科查询表如图6图表 6制作TJ统计表格。图表 7选择CJ1，后按住Shift，选择CJ5，使以CJ1到CJ5五个工作表成为一个工作组，后在CJ1工作表A100：M117单元格区域中设计表格如图。在b101单元格中输入公式：=TRUNC($A$100*A102-0.01,2) ，确定分数段。同时选中c101:c111输入数组公式“=FREQUENCY(C$4:C$96,$B$101:$B$111) ”输入完后按下“Ctrl+Shift+Enter”组合键确认(函数 FREQUENCY 返回一个数组，所以必须以数组公式的形式输入)，就可以统计出“语文”成绩的分数段。后用填充柄向右拖拉复制到m101:m111。说明：FREQUENCY(data_array,bins_array) Data_array为一数组或对一组数值的引用，用来计算频率。Bins_array 为间隔的数组,设定对data_array进行频率计算的分段点。 c113=ROUND(SUBTOTAL($B113,C$4:C$96),2)，用填充柄向下拖拉复制到c117。分别将c113:c117公式中的相对行的引用改为绝对引用，后用填充柄向右拖拉复制到m113:m117单元格中。说明： SUBTOTAL(function_num,ref1,ref2,.