学生成绩统计表模板.xls

学生成绩表是否101班前10名是否102班前10名班级姓名分数名次等级各等级人数处理后的分数101班学生总分103易君6210丙甲等66.200102曾毅669丙6.646301101程志2315丁2.300102梁智3211丁3.200102彭显3112丁乙等23.100102刘成2116丁2.1102班学生总分00103胡飞2514丁2.500103李强2813丁2.800101周航1085甲丙等21034310101范书1162甲1010101林森1191甲1010102柳鹏1094甲10103班学生总分01103高明1113甲丁等61000101赵云976甲9.710102李星847乙8.430901103刘承838乙8.300总分1115“$”是绝对引用符号,在行号或者列号前加上此符号后，将此单元格复制到其它单元格中时,自变量的范围不会改变。上面的分数是这样处理的：如果得分不少于100，那么记为10分，否则将得分除以10。最高分是用1表示，否则用0表示平均分69.69119各班前10名个数优秀人数6优秀率37.5%最低分101班102班及格人数8及格率50.0%43低分(少于40)人数6低分率37.5%21利用上述函数也可以统计各班各个等级人数，或者统计符合多个条件的人数标准差36.83变异系数0.53H列中的函数也可以设置为IF(C3=100,10,C3/10)”，这样如果某单元格的结果为10，那么“10”就是文本，无法参与计算。（这样的设置和原设置的区别在于10加上了引号)。函函数数“IF(AND(B2=6,C2=6),IF(OR(B2=8,C2=8),优优秀秀,及及格格),不不及及格格)”的的意意义义是是：如如果果B2,C2中中的的数数值值都都大大于于6，则则记记为为及及格格，否否则则记记为为不不及及格格，而而及及格格时时，如如果果有有一一个个值值大大于于8，则则记记为为优优秀秀是否103班前10名是否101班前10名是否102班前10名是否103班前10名MOD,ROW函数的应用10016 600105 500004 400003 300002 200001 100006 600005 501004 401003 301002 200101 110016 601005 500104 410013 3注意上述函数的设置方法和左侧的设置方法不同。103班3是用1表示，否则用0表示各班前10名个数利用上述函数也可以统计各班各个等级人数，或者统计符合多个条件的人数函函数数“IF(AND(B2=6,C2=6),IF(OR(B2=8,C2=8),优优秀秀,及及格格),不不及及格格)”的的意意义义是是：如如果果B2,C2中中的的数数值值都都大大于于6，则则记记为为及及格格，否否则则记记为为不不及及格格，而而及及格格时时，如如果果有有一一个个值值大大于于8，则则记记为为优优秀秀学生分数原始的计算的区分度廖云豪63总分满分得分率区分度胡鹏程117后27的学生得分457512840 0.3563080.490498442曾智涛108前27的学生得分1087312840 0.846807杜荣华32杨公博113计算区分度综合算式 0.4904984王浩然111闫 凯92柳林志97好的计算区分度的方法陈三友107总人数396如果选中E12(E或13)单元格，再用鼠标点击编辑栏，则编辑栏中的大括号会消失，次数按下回车键，仍然没有大括号，只有同时按下CTRL+SHIFT+ENTER键，才自动将上述公式加上大括号。胡颖峰5927%的人数107陈 康57高27%总分10873孟 瑞59低27%总分4575郑 文85区分度0.4904984吴元春20王晓勇87试题区分度计算方法一：1、定义名称：data=数据区；2、计算总人数的27%是多少人（本例为107）3、计算高27%总分：A3=SUM(LARGE(data,ROW(A1:A107)数组公式4、计算低27%总分：A4=SUM(SMALL(data,ROW(A1:A107)数组公式5、区分度：A5=(A3-A4)/107/试卷满分注意：1、进行第3步操作时，必须在编辑栏中输入公式“=SUM(LARGE(B2:B397,ROW(A1:A107)”，输入后要按CTRL+SHIFT+ENTER键，则自动将上述公式加上大括号。第4步和第3步大致相同；2、上述两个数组公式不能在合并后的单元格中输入。蔡晓彬20王 鑫115陈 涛91余 磊73邓 千64张奥然93杨 昭42任 帆80王善良41王锐雄77姚鹏飞43马 丹30王方梁32区分度计算方法二：1.定义名称：data=数据区2.总人数：A1=count(data)3.27%人数：A2=ROUND(A1*0.27,0)4.高27%总分：A3=SUM(LARGE(data,ROW(INDIRECT(1:&A2)数组公式5.低27%总分：A4=SUM(SMALL(data,ROW(INDIRECT(1:&A2)数组公式6.区分度：A5=(A3-A4)/A2/试卷满分胡 程84张小芳49卢正彬68周 巧75崔 婧120万星宇117刘 荫72周维维72高 雪44SUM(LARGE(B2:B397,1;2;3;4;5;6;7;8;9)的意义就是B2:B397中的第1到第9个最大值的和,此公式可以简化为数组公式SUM(LARGE(B2:B397,ROW(1:9)或者SUM(LARGE(B2:B397,ROW(A1:A9)涂培莉74赵 茜112何媛媛85余斯倩48李 思89分数段人数分数段的文本形式任 丽6905.500.0%05.5陈敏慧875.510.500.0%5.510.5胡慧文7610.515.530.8%10.515.5周 瑾4415.520.541.0%15.520.5蔡慧莹9220.525.561.5%20.525.5张玲玲4825.530.592.3%25.530.5叶 婷5730.535.582.0%30.535.5柳晓筱8635.540.5112.8%35.540.5陈小雨7040.545.5184.5%40.545.5刘媛媛10545.550.5133.3%45.550.5彭杨康11450.555.5102.5%50.555.5曾 亮10155.560.5225.6%55.560.5邓薛刚9160.565.5256.3%60.565.5杜亚伟8865.570.5194.8%65.570.5毛 爽8370.575.5235.8%70.575.5曾易磊8275.580.5369.1%75.580.5丁 明10680.585.5328.1%80.585.5胡 攀9085.590.54110.4%85.590.5朱祥昊10290.595.5338.3%90.595.5李文波7095.5100.5328.1%95.5100.5余 义62100.5105.5215.3%100.5105.5陈 斌71105.5110.5133.3%105.5110.5彭荣伟106110.5115.5133.3%110.5115.5李 勇80115.5120.541.0%115.5120.5付少波86合计396100.0%赵钰宸104朱 键69廖金员43贺 桃74郑路凯85李江峰87王体威40王 洋37房泽宇15崔 灿99付馨培104李 真84刘丽萍61谭晓倩86毛振圆93周俊峰100林 亮88张 唯89邓 艳88刘 丽100杨珊珊90各分数段分布频率统计图05.515.520.530.535.545.550.5 60.565.575.580.590.595.5 105.5110.50.0%2.0%4.0%6.0%8.0%10.0%12.0%分 数 段频 率杨雅蓉75王一娇58周 静61汪小平61杜宇菲85张春燕77张 琪47叶 超58张志敏63谭晓梅55李可欣58周小林22何布芸26江世鹏103杜国威112陈亚军96颜 健110朱 乔97刘 雄82刘宇超88刘 勒91齐 晶77叶健清92高锦衣67刘 新88王 毅102雷 平64付 强80洪 文66叶中清85唐 帅52黄法定100黄树明60柳红豆57王雅文87彭 珂97易园栓53曹 抗26陈玉林107周昌波78宋美欣99张 青116陈弘婷93骆 琦102刘智妍97王小诗80吴长慧97各分数段分布频率统计图05.515.520.530.535.545.550.5 60.565.575.580.590.595.5 105.5110.50.0%2.0%4.0%6.0%8.0%10.0%12.0%分 数 段频 率赵启慧95蔡亚亚18曾钰凡43李奇奇103蒲 琳95何 点57但小文97向 静82叶 丽93熊利娟43陈明明70周 妍92邓碧金57李长琼64郑玲芳41余美雪32刘亚豪107唐永超107邹 涛108郑嘉文89王 冰86李铭杰93赵 赓83杨佃宇84王 骄98曹 磊93苏 奥56汤迎潇82王 思78钱泽威107薛 鹏91胡 雷67吴兴元57范永存61赵梦豪53胡 刚25周子豪14易 成46陈 波26范 尧97钱雨薇94陈 晨89林 玲83苏 楠78余 容80黄 芳82王雪儿68柯 蕊85童丽静77佘 卉87薛丹丹100罗 毅90赵筱丹44钟 璐45郑 巧64高 琳81冉雪梅78杨 柳50陈巧妮54胡梦雅77熊佩珊56陈小青40刘 玮34尚玉雯70沈李丽32介丹丹72许 彩107李孟林101邓韵俊112刘金鑫105徐世文88阚 松91朱 威79张天毅76胡晓维101马景存96舒 啸87刘 波78陈夏杰90汪 骏84苏 奇38王 越50刘 磊64崔垭林82李 林64董云飞48庄重五55万呈栋56潘 峰51谭顺生75兰 乔95武 军80陈家琪99陶 鑫100周 倩61姜晓芳100高清清80余艳琼88杨倩霞93罗 蓉62陈 娟76张 逍72史继红95孙 巧79王慧敏42田 丹69马 凡78程 娜77杨段坤71刘双园61阳 倩43姜彬彬35刘 芳40李 佳12丁 莲77徐 来114许久晨114汪卓元103张运锴107黄 晨81周长锐104周长剑77祝 青43周晓东90沈 林80黎乃能91陈林军78吴 健68周泽政63胡王斌90朱艺杰95谭 鑫57汤加俊49陶明洋81张 锐68刘 涛71付 伟40邹 杨60熊崇华22卞 飞25谭国行45吴艳芳59陈海霞73吕文敏93代 红75熊小容61邹 璇100龙兆媛54熊维薇96朱迎霞63王 荣87陈 晨88杨玉芹75王 茜41陈雪凌66刘 路72冉小倩66杨 欢79汤 蕾47陈科兰52张甜甜28杨 军103李 凡32谢伍泉99马兴峰38宋文哲74赵 靖26杜 杲111周 波52任俊杰41李 斌98向 阳87沈文山114李向伟86熊 兵71黑奔潇85叶 凯84曾钒峰86董志瀚99赵汕杰101李文进平104汪 旭63熊春杰89王树坤94平科峰88陈 山67代文勇61杨 鹏87杨 戬37马思凡66胡 杰87盖梦琪92金 萍37赵雪廷98李佳静64黄宜君101周婧喆110黄家羽65杨新锦80赵雨洁102李雪静84陈 星97刘礼蓉92李渊茹49崔雪莉84刘佳佳29杨 晨77范 晴35张同娟73张罗兰64王凌翰89江傲祥85杨小辉97杨 勇23宋 健92张雪原77胥 明74高 煦82柯丛煦111冯 伟74马 良104王 卡38赵文怡87杜 杳99屈克豪28陶君晗114周凌云93赵翔宇92刘奎林88龙俊杙92刘 昊56田 欣92罗之伟17周丕正47张 高25胡思思96李思奇86谢嘉欣95孟 竹100李 晨103卞露媚81潘 婷62刘 梦79杨 鸣77吴晓荣66李 荣39陈联欢59屈冉格97许诗语100周 玲86王西燕78刘 灿80张 露84杜静雯81刘佳艺82杨丽莎59石梦云48赵 静42陈晓婷29牟薇苇73董圆圆59用FREQUENCY函数计算分数段人数比例010.500.0%10.520.571.8%20.530.5153.8%30.540.5194.8%40.550.5317.8%50.560.5328.1%60.570.54411.1%70.580.55914.9%80.590.57318.4%90.5100.56516.4%100.5110.5348.6%110.5120.5174.3%合计396100.0%注意：使用FREQUENCY函数时，必须选中区域L3:L13,再输入公式，输入后按ctrl+shift+enter键结束输入。试题区分度计算方法一：1、定义名称：data=数据区；2、计算总人数的27%是多少人（本例为107）3、计算高27%总分：A3=SUM(LARGE(data,ROW(A1:A107)数组公式4、计算低27%总分：A4=SUM(SMALL(data,ROW(A1:A107)数组公式5、区分度：A5=(A3-A4)/107/试卷满分注意：1、进行第3步操作时，必须在编辑栏中输入公式“=SUM(LARGE(B2:B397,ROW(A1:A107)”，输入后要按CTRL+SHIFT+ENTER键，则自动将上述公式加上大括号。第4步和第3步大致相同；2、上述两个数组公式不能在合并后的单元格中输入。区分度计算方法二：1.定义名称：data=数据区2.总人数：A1=count(data)3.27%人数：A2=ROUND(A1*0.27,0)4.高27%总分：A3=SUM(LARGE(data,ROW(INDIRECT(1:&A2)数组公式5.低27%总分：A4=SUM(SMALL(data,ROW(INDIRECT(1:&A2)数组公式6.区分度：A5=(A3-A4)/A2/试卷满分SUM(LARGE(B2:B397,1;2;3;4;5;6;7;8;9)的意义就是B2:B397中的第1到第9个最大值的和,此公式可以简化为数组公式SUM(LARGE(B2:B397,ROW(1:9)或者SUM(LARGE(B2:B397,ROW(A1:A9)各分数段分布频率统计图05.515.520.530.535.545.550.560.565.575.580.590.595.5 105.5110.50.0%2.0%4.0%6.0%8.0%10.0%12.0%分 数 段频 率各分数段分布频率统计图05.515.520.530.535.545.550.560.565.575.580.590.595.5 105.5110.50.0%2.0%4.0%6.0%8.0%10.0%12.0%分 数 段频 率奇题偶题475451541813445233303126奇题和偶题的皮尔森相关系数r0.891595850信度系数=2*r/(1+r)0.94268929274144301951424643484439325855474144364253974838443843333639322649524649474646474642举例姓名语WEN数英将原语文分数颠倒排INDIRECT函数主要功能是间接引用，可提取某一个单元格中的内容。学生155151597603SUM(INDIRECT(ADDRESS(3,2):INDIRECT(ADDRESS(6,4)表示求第3行2列到第6行4列之间的单元格的和学生256542056学生371664382学生11 INDIRECT(A&ROW()*3-2)就是A13单元格的数值,因为ROW()就是I5单元格的行号，5*3-213学生46447975学生5130130652576130130INDIRECT(ADDRESS(ROW(),2)指当前行的第二列的单元格中的内容学生661864911语WEN INDIRECT(B&2)就是B2单元格的内容学生766422270学生8809992781327SUM(INDIRECT(d3:d&A25)就是D3:D23单元格的数值和，因为A25单元格的内容就是23学生974916368学生1092 10193112学生11584644586161因为ADDRESS(18,5)就是指第18行、第5列单元格的地址，即E18,所以INDIRECT(ADDRESS(18,5)指E18单元格的内容学生12112311792学生136860297415ROW(INDIRECT(B&D3)就是B15单元格的行号，因为D3单元格的数字是15学生1478565380学生157077 11266837SUM(INDIRECT(B25&C25):INDIRECT(B26&C26)表示C3:D10单元格的数字和，因为B25和C25两个单元格的内容分别是C和3，B26和C26两个单元格的内容分别是D和10，所以，如果改变B25、C25、B26和C26中的内容，F17单元格中的内容也随之改变。学生161186886161学生177648 102130学生185939864学生1982879071E3单元格中的函数INDIRECT(B&ROW($B$23)+ROW($B$3)-ROW()的值就是B23单元格的值,因为ROW($B$23)+ROW($B$3)-ROW()26+3-323，ROW()指当前单元格的行号。学生205697 10956学生219777665523 C3D10语文分数排序方法倒序排正排序序号对应的分数513087611112175655971366569218565682201158809746178156164765826674107168711268707011707168195574661130766421578617788058492825614649256680975539711211211213051658INDIRECT函数主要功能是间接引用，可提取某一个单元格中的内容。SUM(INDIRECT(ADDRESS(3,2):INDIRECT(ADDRESS(6,4)表示求第3行2列到第6行4列之间的单元格的和INDIRECT(A&ROW()*3-2)就是A13单元格的数值,因为ROW()就是I5单元格的行号，5*3-213INDIRECT(ADDRESS(ROW(),2)指当前行的第二列的单元格中的内容SUM(INDIRECT(d3:d&A25)就是D3:D23单元格的数值和，因为A25单元格的内容就是23因为ADDRESS(18,5)就是指第18行、第5列单元格的地址，即E18,所以INDIRECT(ADDRESS(18,5)指E18单元格的内容ROW(INDIRECT(B&D3)就是B15单元格的行号，因为D3单元格的数字是15SUM(INDIRECT(B25&C25):INDIRECT(B26&C26)表示C3:D10单元格的数字和，因为B25和C25两个单元格的内容分别是C和3，B26和C26两个单元格的内容分别是D和10，所以，如果改变B25、C25、B26和C26中的内容，F17单元格中的内容也随之改变。E3单元格中的函数INDIRECT(B&ROW($B$23)+ROW($B$3)-ROW()的值就是B23单元格的值,因为ROW($B$23)+ROW($B$3)-ROW()26+3-323，ROW()指当前单元格的行号。题题号号1234567891011121314151617学生133333336696047473学生233333336696077573学生3333000300301000000学生433333306696275480学生503333306666160020学生6303330320611577111学生73333333661060771075学生830333334055110200学生933333336686710341学生1000330030656920110学生1133333036696770553学生1233333336686671033学生1333333336696672431学生1433333336676420100学生153333333661060771085学生1630333336686376321学生1733333306686370430学生1833333336695177070学生19003000000201100000学生2033333336686270431学生2133333336686352201学生2233333332676570000和总分的相关系数（correl相关系数）0.6 0.5#DIV/0!0.71 0.75 0.56 0.29 0.79 0.71 0.98 0.67-0.56 0.76 0.62 0.71 0.790.7皮皮尔尔森森相相关关系系数数0.6 0.5#DIV/0!0.71 0.75 0.56 0.29 0.79 0.71 0.98 0.67-0.56 0.76 0.62 0.71 0.790.7说明：1、correl相关系数和皮尔森相关系数计算出来的结果完全相同，说明二者是一回事；2、因为第3题所有学生得分一样，所以它们不存在和总分的相关性。第12题和总分的相关系数之所以为负数，是因为总分高的学生此题得分较低，而总分低的人此题的得分较高。第10题的相关系数之所以较高，是因为总分高的学生此题得分也高，而总分低的人此题的得分也低；3、相关系数在1和1之间，越接近1，说明相关性越强，越接近1，说明相关性越差。总分7377257148568536633972677153866661691664605411说明：1、correl相关系数和皮尔森相关系数计算出来的结果完全相同，说明二者是一回事；2、因为第3题所有学生得分一样，所以它们不存在和总分的相关性。第12题和总分的相关系数之所以为负数，是因为总分高的学生此题得分较低，而总分低的人此题的得分较高。第10题的相关系数之所以较高，是因为总分高的学生此题得分也高，而总分低的人此题的得分也低；3、相关系数在1和1之间，越接近1，说明相关性越强，越接近1，说明相关性越差。班级姓名性别语数英总分语文等级数学等级英语等级901学生1男529966217FBE901学生2男5654114224FFA901学生3男71115102288DAA901学生4女644787198EFC901学生5女1126525202AEF901学生6女616649176EEF902学生7女664222130EFF902学生8女588855201FCF902学生9女6011217189EAF902学生10男688911168ECF902学生11男785653187DFF902学生12男7077112259DDA903学生13男928288262BCC903学生14男7948102229DFA903学生15男457898221FDB903学生16女828770239CCD903学生17女448155180FCF903学生18女1127766255ADE1、该年级多少男生99班级语文等级分数数学等级分数2、903男生数学总分208901153、901女生总分均分是多少192192901114、901、902两班语文均分6868901365、总分高于220的同学中，英语最高分是多少114901216、三门都在80分以上的人总分相加是多少262901627、共有几个人三门都在80分以上1901228、语文或者英语有一门小于60，但是数学大于80的学生数5902215此式较简902149、901男生语文的标准差是多少？22.49错错误误！902269、902男生语文的标准差是多少？8.18正正确确！9022410、所有女生总分的标准差34.08902319023390354和F31比较，F32单元格的数据之所以正确，是因为用了IF函数，使得当($A2:$A19=901)*($C2:$C19=男)不成立时，返回值为FALSE,求标准差的公式STDEVP忽略这个逻辑值，所以只求了D2,D3,D4三个标准差。F31单元格的数据之所以错误，是因为数组(A2:A19=901)*(C2:C19=男)*D2:D19相当于数组(D2:D4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)，而我们只需要求D2:D4的标准差。9033190313903449031490363期期末末学学生生星星级级评评价价表表说明：总评分数达到400分，语文或者数学有一门达到40分的学生可以评为五星，总评分数达到200分，语文或者数学有一门达到20分的学生可以评四星，否则只能评为三星。姓名总评分数 语文数学星级学生14001560学生26875420学生35986643学生42354797学生54566525学生65638649学生71594222学生88629992学生94589163学生1065210193学生111254644学生121453117学生133276029学生148625653学生1510077112学生161238688学生1763248102学生181209398学生191478790学生2055597109学生212337766E43:E63单元格中的函数REPT(RIGHT($E$42,1),4)的意义是：重复E42单元格中的从右边起，第一个字符（即）4次。说明：总评分数达到400分，语文或者数学有一门达到40分的学生可以评为五星，总评分数达到200分，语文或者数学有一门达到20分的学生可以评四星，否则只能评为三星。11、各班各个等级的人数语数英901A112B010C001D100E221F222902A011B000C020D210E300F125903A101B101C131D121E001F211英语等级分数12、各等级分的人数语数英2901611269015010690140014901310019012221190260111902500019024020190232101902230019036101690351014903413169033121590320013未计等级分为1分的人数12原始分数提取B列中的前两个数字将C列中的数字转换为数字以“分”字为标志，确定“分”字的位置以第一个逗号为标志，确定第一个逗号后面的第一个数字的位置35分353534分343433分3333王三，8分，差5432分3232猪八戒，65分，及格7531分3131张四五，106分，优8530分3030欧阳庆春，79分，不及格 8629分2929求和28分282827分2727地址引用上述地址的内容26分2626第14行，第2列的地址$B$1425分25分2525第15行，第3列的地址$C$152424分2424第14行，第3列的地址C$142523分2323第19行，第1列的地址$A19AAA22分2222第19行，第2列的地址B19合计21分2121AAA合计0.00420C19单元格中的数字之所以是0，是因为C4到C18单元格中的数字实际上是文本，不能相加。通过函数VALUE转换后才是真正的数字，可以相加。K10单元格情况相同。确定分数所占的字符数再从上面的位置开始，提取前J列中指定的字符个数将提取的字符转换为数字综合算式18882656565310610610627979790258258用INDEX函数提取相同内容25分2425AAA合计学学生生语语数数英英物物语语用用INDEX函数求和魏江滨559222455黄色区域中第4列的和1305徐俊如5654204356黄色区域中第1列的和1568杨洪6447234664黄色区域中第2列的和1698许阳杰7166436671黄色区域中第1行的和110张磊8078916880黄色区域中第3列的和180陈勇佳8788665887黄色区域中当前行的和299胡向军8281706582杨璇881181181008688罗斌787154577878徐杨雯66114528266喻扬77100978477邱超7594585575118118表示引用黄色区域的第8行，第2列高力891129890897878因为引用的是单列区域，只需输入是第几行焦世豪93859084931568因为引用的是单行区域，只需输入是第几列陈洋891051028189胡国威9191968391下面的函数和上述函数结果相同郑超导931041009093118118李正901081118590宋键6868651768杨盼76105614176合计156816981419130515681568选中G1单元格，点击旁边的下拉箭头，选择相应的科目，G2:G21单元格如何变化？设置有效性的条件时，只有选择序列才会出现下拉箭头。选中G1单元格，点击旁边的下拉箭头，选择相应的科目，G2:G21单元格如何变化？设置有效性的条件时，只有选择序列才会出现下拉箭头。