斗俩下载站提供最新Win11系统下载,Ghost Win10下载,win7系统下载,软件下载

xp教程办公软件教程seo游戏攻略

windows7旗舰版
当前位置:主页 > 办公软件教程 >

用WPS表格完成片区成绩统计

发布时间:2022-08-26 来源:win7旗舰版 浏览量:

电子表格(Spreadsheet),又称电子数据表,是一类模拟纸上计算表格的计算机程序。电子表格可以输入输出、显示数据,也利用公式计算一些简单的加减法。可以帮助用户制作各种复杂的表格文档,进行繁琐的数据计算,并能对输入的数据进行各种复杂统计运算后显示为可视性极佳的表格,同时它还能形象地将大量枯燥无味的数据变为多种漂亮的彩色商业图表显示出来,极大地增强了数据的可视性。

  我校在对教师进行绩效考核中,需要各位教师所教学科的人平分、及格率、优生率、差生率在片区几所学校所有班级中的排位情况,每到期末我的工作量都相当大,为了达到一劳永逸的目的,就制作了一个片区成绩统计表。下面就将此表制作的过程作一简要说明。望这篇文章能起到抛砖引玉的作用,敬请各位同仁指教。

  一、制作所需表格

  首先,将需要的工作表制作出来。为了保证各表间数据引用方便,利于修改,我们尽量使几个工作表的样式、格式一致。为了减少工作量,示例表中我只做了三所学校(分别是“学校甲”、“学校乙”,“学校丙”),每所学校三个班,实际中我们可以根据实情进行增减,方法都是一样的。

  1.制作学校甲三个班的成绩统计表,如图1:

用WPS表格完成片区成绩统计 三联教程

  因为现在的学籍管理要求每个班人数不得超过70人,所以我就为每个班预定了70行(图1为了完整显示内容,隐藏了部分单元格),再将每个班学校名称列和班次列的数据录入。

  2.将工作表“学校甲”复制出工作表“首页”,在基本不动表格样式的情况下,做出如图2所示表格:

用WPS表格完成片区成绩统计_wps教程_本站

  3.再将工作表“学校甲”复制一个工作表“片区汇总”,将三个班后的分析部分及空行删除掉(图3),

用WPS表格完成片区成绩统计_wps教程_本站

  再将“学校甲”三个班的表格复制两次到此表中(不要复制标题行,第一次复制后将“学校甲”替换为“学校乙”,第二次复制后将“学校甲”替换为“学校丙”,如图4),同样删除各班后分析部分及空行。

用WPS表格完成片区成绩统计_wps教程_本站

  4.因为我们想要了解本校各科各项指数在片区中的排位,所以另外还要制作一张各项指数的统计表。我们依然可以将工作表“学校甲”复制出“片区统计”,将表格调整为图5样式制作出“人平分”的统计表,再复制出“及格率”、“优生率”、“差生率”的统计表。

用WPS表格完成片区成绩统计_wps教程_本站

  至此,需要的工作表就全制作好了(为了减少工作量,工作表“学校乙”、“学校丙”待工作表“学校甲”所有需要的公式录入完成后再复制)。

  二、利用数据有效性制作下拉列表

  表格是制作出来了,但表格内还有很多地方需要填入数据,如标题行还需要此次检测的年份、年级、期段,成绩栏还需要显示各学科名称等,为了使工作簿能多次使用,我们可以利用数据有效性来制作下拉列表,提供选择项。

  首先,在工作表“首页”任一空白处将年份、年级、期段、学科的序列录入。如图6:

用WPS表格完成片区成绩统计_wps教程_本站

  接着,选中“首页”标题行中第一个合并的单元格,再点击菜单栏中的“数据”——“有效性”(图7),

用WPS表格完成片区成绩统计_wps教程_本站

  在弹出的对话框“允许”下选择“序列”(图8),

用WPS表格完成片区成绩统计_wps教程_本站

  在“来源”处输入年份序列下所有年份的范围(也可以点击“来源”处文本框右侧的按钮后再拖选所有年份的单元格,如图9),再点“确定”。

用WPS表格完成片区成绩统计_wps教程_本站

  这样,年份的下拉列表就制作完成了(图10)。

用WPS表格完成片区成绩统计_wps教程_本站

  用同样的方法,也将年级、期段、学科的下拉列表也制作出来(“学科”的下拉列表可以只做一个再复制或拖拽填充出来,但前提是在首次输入学科序列时,必须在行号、列号前加绝对引用符号“$”,否则,后面的下拉列表就会变)。将所有下拉列表都制作出来后,我们就可以将录入年份、年级、期段、学科序列的所在行全部隐藏起来。

  (未完,2楼继续)

用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站
用WPS表格完成片区成绩统计_wps教程_本站

  三、利用函数求人平分、及格率、优生率、差生率

  接下来就将所有表中涉及到的函数分别进行说明。

  1.工作表“首页”中,在“各学科总分”后的“总分”单元格下用SUM函数求出所有学科的总分数,在单元格O4中录入公式:“=SUM(E4:N4)”(其它如“学校甲”、“片区汇总”表中“总分”一列都如此,后面就不缀述了)。

  接着,在“及格分数段”后的单元格内求出及格分数段(因为各学科的总分不确定,所以只能用公式求),在“及格分数段”后的单元格内录入函数“=E4*0.6”,再复制出所有学科的及格分数段。

  再接着,在“各科优生段”后的单元格内求出优生分数段(因为我校的各科“优生”是指进入全片区所有学生前30%的学生,所以“优生段”就是指所有学生数的前30%最后一名的分数,例如:片区某年级共500人,前30%就是150人,那么前第150名的分数就是每个学科的优生段。“各学科差生段”也类似,只不过改为求后30%第一名的分数为差生段。),在“各科优生段”后第一个单元格内录入公式“=LARGE(片区汇总!E5:E634,ROUND(COUNT(片区汇总!E5:E634)*0.3,0))”,这个公式主要是用LARGE函数求出工作表“片区汇总”第一个学科学生成绩的第K个最大值(这个“K”的值就通过COUNT函数求出“片区汇总”第一个学科的总人数,再乘以0.3,再用ROUND函数四舍五入求出的整数值),再将这个公式复制到其它学科。

  最后,用SMALL函数求出“各学科差生段”,第一个学科的公式是:“=SMALL(片区汇总!E6:E634,ROUND(COUNT(片区汇总!E6:E634)*0.3,0))”,这个公式是用SMALL函数求出工作表“片区汇总”第一个学科学生成绩的第K个最小值(这个“K”的值与上面的 “K”值相同),再复制出其它学科的差生段公式。

  这样,工作表“首页”就完全制作成功了(图11),这个表中的及格段、优生段、差生段数据将作为其它工作表引用的基础。

用WPS表格完成片区成绩统计_wps教程_本站

  2.将工作表“学校甲”制作完成。

  首先,将标题行完善,在第一个合并的单元格中录入公式“=IF(首页!$D$1="","",首页!$D$1)”(公式中的if函数是为了在表格无数据时使该单元格也显示为空白,纯属美观需要,并不是必须的,如果只要正确求得数据,录入“=首页!$D$1”就可以了,本文IF函数的作用都如此),在第二个合并的单元格中录入公式“=IF(首页!$F$1="","",首页!$F$1)”,在第三个合并的单元格中录入公式“=IF(首页!$I$1="","",首页!$I$1)”,这样,“首页”标题选择了什么年份、年级、期段,“学校甲”就会显示相同的内容了。

  接着,用同样的方法将学科名称也与“首页”同步,为了保证拖拽复制的准确,在录入公式时,就不加绝对引用符号:“=IF(首页!E3="","",首页!E3)”。

  接下来,再将各班“人平分”、“及格率”、“优生率”、“差生率”四个指数的公式录入,在这里就会引用到“首页”求出来的各学科“及格段”、“优生段”、“差生段”的数据了。分别在第一个学科下的四个指数单元格中录入公式:人平分—— “=IF(ISERROR(AVERAGE(E5:E74)),"",AVERAGE(E5:E74))”,及格率—— “=IF(ISERROR(COUNTIF(E5:E74,">="&首页!E$5)/COUNTA(E5:E74)),"",COUNTIF(E5:E74,">="&首页!E$5) /COUNTA(E5:E74))”,优生率——“=IF(ISERROR(COUNTIF(E5:E74,">="&首页!E$6) /COUNTA(E5:E74)),"",COUNTIF(E5:E74,">="&首页!E$6)/COUNTA(E5:E74))”,差生率——“=IF(ISERROR(COUNTIF(E5:E74,">="&首页!E$7)/COUNTA(E5:E74)),"",COUNTIF(E5:E74,">="&首页!E$7) /COUNTA(E5:E74))”。接着再选中刚才录入数据的四个单元格,向右拖拽填充,将公式也复制到其它学科的单元格内。接着再将所有学科下“及格率”、“优生率”、“差生率”这三项的单元格选中,通过依次点击“右键”——“设置单元格格式”——“数字”——“百分比”——“确定”,将其设置成百分比(如果设置成百分比后无法正确显示数据,就将其字号减小)。

  最后,再选中1班四个指数项的所有单元格,将其复制到2班、3班。工作表“学校甲”制作就算完成了(图12)。

用WPS表格完成片区成绩统计_wps教程_本站

  3.制作完成工作表“片区汇总”。

  首先,按照上述的方法将标题与学科部分的公式录入完成。

  为了减少工作量,让各班分数只录入一次,可以利用公式将各班的分数引用到“片区汇总”中来。在1班第一个学生的第一个学科成绩单元格内录入公式:“=IF(学校甲!E5="","",学校甲!E5)”,再拖拽复制出1班所有学生各科成绩的公式。用同样的方法我们依次将2班、3班的公式录入。

  最后,我们将工作表“学校甲”复制出工作表“学校乙”、工作表“学校丙”,再按照上面的方法也将学校乙、学校丙各班学生的成绩公式录入。

  至此,工作表“片区汇总”也制作完成了(图13)。

用WPS表格完成片区成绩统计_wps教程_本站

  四、完成工作表“片区统计”

  接下来是制作最麻烦的一个工作表“片区统计”。

  1.还是按前面的方法将标题行完善。

  2.将“学科”行也按前面的方法录入公式,但这里要注意的是:我们要将各班的某个统计指数排位,所以,在录入各学科名称的引用公式时,要隔一列录入一个学科名称引用公式。在第一个学科后的那个单元格录入公式:“=IF(C4="","","名次")”,这样,当第一个学科显示学科名称时,该单元格就会显示“名次”二字,否则就显示空白,再将这个公式复制到每个学科后的单元格内。按照同样的方法,分别将“及格率”、“优生率”、“差生率”的“学科”、“名次”的公式也录入(因为这个表中列数太多,为了方便公式的录入,可以将“学科”列或“名次”列的填充上颜色)。

  3.接下来是最麻烦的一步——引用各班的各项指数,这就不能复制了,必须得一个单元格一个单元格的录入公式。例如,在“人平分”项,“学校甲1 班”第一个学科单元格中录入公式:“=学校甲!E76”,这个公式表示该单元格的数据引用工作表“学校甲”E76单元格的数据,工作表“学校甲”E76单元格就是学校甲1班第一个学科的人平分。

  4.最后,利用RANK函数求出各项指数各班各学科片区排位——这也是我们最终想要得到的数据。在“人平分”指数项“学校甲1班”第一个学科后的 “名次”列录入公式:“=IF(ISERROR(RANK(C5,C$5:C$13)),"",RANK(C5,C$5:C$13))”(这个公式的意思是:如果用RANK函数求单元格C5相对于C5至C13的降序排位的结果是错误的——ISERROR函数就是检测一个值是否错误,此单元格就显示为空白,否则就显示用RANK函数求单元格C5相对于C5至C13的降序排位的结果),再拖拽复制公式到C13单元格,再选中C5:C13后复制公式到“人平分” 指数项其它学科后的“名次”列。再按上述方法将“及格率”、“优生率”、“差生率”的名次排位公式录入(图14)。

用WPS表格完成片区成绩统计_wps教程_本站

  “片区统计”完成了,前面所有工作表的数据,都是为得到本表的统计结果服务的。

  五、完善工作簿“片区成绩统计”

  到此,工作簿“片区成绩统计”已经基本完成了,但是,为了防止工作表的格式以及公式不小心被修改或删掉,可以将以后不需编辑的单元格保护起来。在以后的使用过程中,实际只需要对“首页”中检测的年份、年级、期段、学科名称、各学科总分以及各班学生的考号、姓名、各科成绩进行录入,所以,可以分别将 “首页”及各校统计表中需要录入数据的单元格选中,再点击“工具”——“保护”——“允许用户编辑区域”(图15)

用WPS表格完成片区成绩统计_wps教程_本站

  ——“新建”(图16)

用WPS表格完成片区成绩统计_wps教程_本站

  ——“确定”(图17)

用WPS表格完成片区成绩统计_wps教程_本站

  ——“保护工作表”(图18)

用WPS表格完成片区成绩统计_wps教程_本站

  ——输入密码后点“确定”,再输入一次密码点“确定”(图19)。

用WPS表格完成片区成绩统计_wps教程_本站

  这样,“片区成绩统计”工作簿就算完全制作成功了。最后,将选中工作表“首页”中“年份”单元格,再将本工作簿保存为模板,以备后用。

  附件:片区成绩统计示例表.xls 密码:123。

片区成绩统计示例表.xls
片区成绩统计示例表.xlt


金山WPS Office专业版的安全性经过几百家权威机构及组织证明,金山wps办公套装无限扩展用户个性化定制和应用开发的需求;专为中国用户使用习惯的量身定制的wps Office软件,金山wps是中国最好的office办公软件。

关键词: 用WPS表格完成片区成绩统计_wps图文详细教程_本站 
我要分享:

相关推荐

本站发布的ghost系统仅为个人学习测试使用,请在下载后24小时内删除,不得用于任何商业用途,否则后果自负,请支持购买微软正版软件!

如侵犯到您的权益,请及时通知我们,我们会及时处理。手机站

Copyright @ 2018 斗俩下载站(http://www.doulia.cn) 版权所有  xml地图 邮箱: