(精品)如何运用电子表格模板快速统计学生.doc

如何运用电子表格模板快速统计学生成绩摘 要：本文阐述了用Excel制作一个能够自动统计成绩的电子表格模板的具体方法和步骤，只要在“空白”模板中填上学生成绩，各种统计数据（均分、及格率、优分率、分数段等）就立刻显现出来关键词：学校 电子表格 成绩统计每次考试结束后，面对诸多的统计项目，许多教师开始在计算机中使用Excel软件进行统计可是里面涉及的函数公式让很多教师头疼，而且稍不注意就容易出错笔者结合多年的工作实践，用Excel制作了一个能够自动统计成绩的电子表格模板提供给教师只要在“空白”模板中填上学生成绩，各统计数据就立刻显现出来，能够让教师方便快捷、准确无误进行统计，轻松完成繁重的工作，班级的教学质量数据一目了然，彻底告别那些眼花缭乱的繁杂统计工作，提高了工作效率一、登分图一如图一，我们把第一个工作表命名为“成绩册”，在A1—M1单元格中分别输入姓名，班级，考号，考室，语文，数学，……，总分，名次阅卷结束后，完成登分工作1、计算学生的总成绩在L2单元格输入“=SUM(E2:K2)”后按回车键或单击编辑栏中的输入按钮“√”确认如果要计算每个学生的总成绩，可用鼠标对准L2单元格右下角的填充柄，向下拖动至L320单元格，L2单元格的公式即被复制到了L3 ：L320单元格区域。

2、按总分排名在单元格M2输入“=RANK(L2,$L$1:$L$320)” 后按回车键或单击编辑栏中的输入按钮“√”确认用鼠标对准M2单元格右下角的填充柄，向下拖动至M320单元格，M2单元格的公式即被复制到了M3 ：M320单元格区域二、使用函数 FREQUENCY统计各分数段的人数图二如图二，使用函数 FREQUENCY 统计各分数段的人数由于函数 FREQUENCY 返回一个数组，所以它必须以数组公式的形式输入所有数组公式编辑完公式后，必须按 Ctrl+Shift+Enter 以锁定公式1、设置区间及分割点（如图二所示Q2:R21区域）2、在S2单元格输入“=FREQUENCY(L$1:L$320,$Q$2:$Q$17)” 用鼠标对准S2单元格右下角的填充柄，向下拖动至S17单元格，S2单元格的公式即被复制到了S3 ：S17单元格区域按 Ctrl+Shift+Enter结束3、统计总分最高分在S18单元格输入“=MAX($L:$L)” 后按回车键或单击编辑栏中的输入按钮“√”确认4、统计总分最低分在S19单元格输入“=MIN(IF(L2:L320<>0,L2:L320))” 后按回车键或单击编辑栏中的输入按钮“√”确认。

5、统计应考人数在S20单元格输入“=COUNT($L:$L)” 后按回车键或单击编辑栏中的输入按钮“√”确认6、统计实考人数在S21单元格输入“=SUMPRODUCT(($L$2:$L$320<>0)*($L$2:$L$320>=1))” 后按回车键或单击编辑栏中的输入按钮“√”确认三、偏科分析图三如图三，我们把第二个工作表命名为“偏科分析”， 在A1单元格输入“=成绩册!A1” 用鼠标对准A1单元格右下角的填充柄，向下拖动至M320单元格，工作表“成绩册”中的学生成绩即被复制到了A1 ：M320单元格区域删除考号，考室这两列的内容，分别在语文、数学、英语、政治、历史、物理、化学后插入一列，在单元格D1、F1、H1、J1、L1、N1、P1中输入“名次”，在单元格D2输入“=RANK(C2,$C$2:$C$320)” 用鼠标对准D2单元格右下角的填充柄，向下拖动至D320单元格，D2单元格的公式即被复制到了D3 ：D320单元格区域通过语文这一科的名次和总分的排名对比，可以看出哪些同学的语文是否偏科如901班姚越同学的语文排名全校103名，而他的总分在全校排名并列12名，说明他的语文偏科，希望能引起该班的语文教师和姚越同学的重视。

其余各科的偏科分析和语文学科的操作类似四、填写几个报表1、表（一）制作好如图四所示的表头后，在B6单元格输入“=成绩册!S3”，在B7单元格输入“=B6”，在C6单元格输入“=成绩册!S4”，在C7单元格输入“=B7+C6”，其余类推表四图五2、表（二）—统计全校各科均分、及格、优分、最高分、最低分，以语文学科为例：制作好如图五所示的表头后，①统计均分，在A6单元格输入“=AVERAGE(成绩册!$E2:$E320)”，②统计及格人数，在B6单元格输入“=COUNTIF(成绩册!$E:$E,">=72")”，③统计及格率，在C6单元格输入“=COUNTIF(成绩册!$E2:$E320,">=72")/COUNTA(成绩册!$E2:$E320)*100”，④统计优分人数，在D6单元格输入“=COUNTIF(成绩册!$E:$E,">=96")”， ⑤统计优分率，在E6单元格输入“=COUNTIF(成绩册!$E2:$E320,">=96")/COUNTA(成绩册!$E2:$E320)*100”，⑥统计最高分，在F6单元格输入“=MAX(成绩册!$E:$E)”，⑦统计最低分，在G6单元格输入“=MIN(成绩册!$E2:$E320)”，其余各科类推。

图六2、表（三）—统计各班各科均分、及格、优分、最高分、最低分，以901班语文学科为例：制作好如图六所示的表头后，① 统计应考人数，在B4单元格输入“=COUNTIF(成绩册!$B:$B,"=901")”，② 统计实考人数，在C4单元格输入“=SUMPRODUCT((成绩册!$B$2:成绩册!$B$320=901)*(成绩册!$E$2:$E$320>=1))”，③ 统计均分，在E4单元格输入“=SUMIF(成绩册!$B$2:成绩册!$B$320,"=901",成绩册!$E$2:成绩册!$E$320)/'表（三）'!C4”，④ 统计及格人数，在F4单元格输入“=SUMPRODUCT((成绩册!$B$2:成绩册!$B$320=901)*(成绩册!$E$2:成绩册!$E$320>=72))”，⑤ 统计及格率，在G4单元格输入“=F4/C4*100”，⑥ 统计优分人数，在H4单元格输入“=SUMPRODUCT((成绩册!$B$2:成绩册!$B$320=901)*(成绩册!$E$2:成绩册!$E$320>=96))”， ⑦ 统计优分率，在I4单元格输入“=H4/C4*100”，⑧ 统计最高分，在J4单元格输入“{=MAX(IF(成绩册!$B$2:成绩册!$B$320=901,成绩册!$E$2:成绩册!$E$320))}”，⑨ 统计最低分，在K4单元格输入“{=MIN(IF(成绩册!$B$2:成绩册!$B$320=901,成绩册!$E$2:成绩册!$E$320))}”，其余各科类推。

3、表（四）—各班前100名分布情况制作好如图七所示的表头后，在B4单元格输入“=SUMPRODUCT((成绩册!$B$2:成绩册!$B$11=901)*(成绩册!$L$2:$L$11>=1))”，在C4单元格输入“=SUMPRODUCT((成绩册!$B$12:成绩册!$B$21=901)*(成绩册!$L$12:$L$21>=1))”，其余类推图七五、制作模板完成上述过程之后，在“文件”菜单中选择“另存为”命令，出现“另存为”对话框在“保存类型”列表框中选择“模板”，在“保存位置”出现Templates文件夹，单击“保存”按钮如图八）图八六、运用电子表格模板统计学生成绩 图九⑴启动Excel，选择“文件”菜单中的“新建”命令，在工作簿窗口的右侧弹出“新建工作簿”任务窗格⑵在任务窗格中，单击“模板”选项区中的“本机上的模板”，打开“模板”对话框⑶在该对话框中，单击“电子方案表格”选项卡，在下面的“文件”列表框中列出了已有的工作簿模板如图九）⑷单击“2010年秋季九年级第一次月考”模板，单击“确定”按钮，系统将打开选中的模板文件⑸在成绩册中输入学生的各科成绩，单击“保存”即可这时由于学生人数发生变化，表（三）可能出现图十的现象：图十图十一选中C4：BG9区域，选择“编辑”菜单下的 “查找和替换”功能，将“成绩册!$B$2:成绩册!#REF!”替换为“成绩册!$B$2:成绩册! $B$315”，单击“全部替换”；再将“成绩册!$E$2:成绩册!#REF!”替换为“成绩册!$E$2:成绩册! $E$315”，其余类推即可。

七、特别说明⑴ 函数中的双引号为英文状态下的双引号；⑵ 数组公式中的“{}”不是手工输入的，而是编辑完数组公式后，必须按 Ctrl+Shift+Enter 结束，系统自动加上的“{}”参考文献：《Excel 2003中文电子表格》，全国专业技术人员计算机应用能力考试专家委员会编写，沈阳：辽宁人民出版社；辽宁电子出版社，2005.11。