用Excel打造年级成绩统计模板.docx

《用Excel打造年级成绩统计模板.docx》由会员分享，可在线阅读，更多相关《用Excel打造年级成绩统计模板.docx（5页珍藏版）》请在优知文库上搜索。

1、用Excel打造年级成绩统计模板在学校，老师经常用电子表格来处理学生的成绩。一两个班的成绩处理都很熟练了，但要你处理一个年级30多个班的成绩全校90多个班）的时候，你还能愉快地接受这个任务吗？尤其是高三，月考不断，要求统计、分析要快速准确，学生、老师、主任、校长都在焦急地等着你的处理结果的时候，你不快点能行吗？最好是成绩一旦录入结束，几秒钟后就可以打印出所有的结果！一般学校都没有购置专业的处理软件，学校不同，要求也不同，专业的软件也不太实用，大多数还是用EXCCl或WPS来处理。其实用电子表格也可以快速处理整个年级的成绩，几秒钟绝对可以出结果。不过，要想偷懒，必须要做好前期的准备工作，最重要的

2、就是做一个万能的成绩统计模板。高中文理分科，全年级打乱顺序编排考室。语文和英语文理同卷，要给出年级平均分，其它科要分文、理科统计，还要到班统计，各科分数段统计。高三要根据前一年的高考录取分数线给出本届高考预计上线的人数等等信息。领导想要什么，你就显示什么。要想有一个完美的结果，最好能按你的意愿统一安排考试，这样可以减少很多麻烦。考试安排和登分册都可以在同一个工作表（模板）中设计，打印的时候，将不需要的信息隐藏就行了。一、表头设计：1、考室和座位插在姓名和性别之间，既可以隐藏不打印，又可防止“总分”求和时出现“此单元格中的公式引用了有相邻附加数字的范围”这样的提示影响表格的美观。2、总分右边的列

3、不会打印出来（通过分页预览可实现），可以充分利用。其中“性别”用于处理男女人数，T2单元格中输入公式：=IF（H2=女，1,），向下拖拉填充。意思是：假设单元格H2是“女”，那么显示值1,是“男”那么不显示即空）IF函数的语法结构：IF（条件,结果1,结果2）,对满足条件的数据进行处理，条件满足那么输出结果1,不满足那么输出结果2。右边的“语文”、“数学”、“总分”等列，用于处理缺考人数的统计。U2单元格中输入：=IF（I2=,1,）,即没有语文成绩（12单元格中为空），那么显示1.用1表示该考生没考语文。其它类似。AE列等用于统计分数段：600,公式：=IF（R2599,1,），即总分在60

4、0分以上的，显示1.否那么不显示；590,公式：=IF（R2589,1,）,即总分在590分以上的,显示1.否那么不显示;同样设置AU等列，用于统计语文成绩120分以上、110分上、100分上90分以上、80分上的人数。二、数据处理SUMIF函数根据指定条件对假设干单元格求和。语法SUMIF（range,criteria,sum_range）1） range为用于条件判断的单元格区域。2） criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32或apples。3） sum_range是需要求和的实际单元格。1、学文科的女生人数：

5、在B1406单元格中输入：=SUMIF（B2:B1401,“文科,T2:T1401）,意即：单元格区域从B2到B1401,条件是“文科”，实际求和单元格从T2到T1401,这就是学文科的女生人数。2、学文科的男生人数：在C1406单元格中输入：=COUNTlF（B2:B1401J=文科）-B1406,即学文科的总人数减去学文科的女生人数。COUNTIF函数COUNTIF函数对区域中满足单个指定条件的单元格进行计数。语法；COUNTIF（range,criteria）中文表示就是CoUNTIF（数据区域，条件表达式）其中：=COUNTlF（B2:B1401,二文科）计算的是文科的总人数。同理可得

6、到理科男女生人数。3、各班的男女生人数。女生人数，同1,如1班：在B1412单元格中输入：=SUMIF（A2:A1401,T2:T1401）,在“班级”这一列A2:A1401中计算it班的女生数。当然男生人数就是1班总人数减去1班女生人数就行了：在C1412单元格中输入：=COUNTIf（A2:A1401,D-B1412参考人数的统计：有两种方法，或者说两种思路：以文科语文的参考人数统计为例：文科总人数减去缺考人数：文科总人数前面已算出，可直接利用D1406单元格，也可用公式：COUNTIF（B2:B1401,文科”）。语文缺考的人数利用U列来统计SUMlF（B2:B1401,文科,U2rU1

7、404）o因此在11408单元格中输入公式：二（COuNTIF（B2:B1401,“文科）-SUMIF（B2:B1401,文科”,U2:U1404）在11409单元格中只需将以上公式中的文科”改为理科”即可求出理科语文的参考人数。利用SUmProdUCt函数求文科语文的参考人数，很简单：在II408单元格中输入：=Sumproduct（B2：Bi401=文科”）*（12：i40i）即可。条件求和的另一种方法：Sumproduct函数的用法另一种用法：Sumproduct（Al:AlOO=数值1）*（B1:BlO0数值2）,Cl:ClOO）用于统计第一行到第100行中，A列等于数值1,B列大于数

8、值2时，C列的数值和。跟SUMlF函数条件求和有相同成效。利用=SumPrOdUCt（A1:AlOO=数值1）*1.Cl:CloO）和=SUmif（A1:AlOO数值1*,cl:ClOo）结果一样。另，Sumproduct（A1:AlOO=数值1）*（814100数值2）*&1:（：100数值3）表示统计第1行到第100行中，A列等于数值1,B列大于数值2,C列小于数值3的记录的数量。可用于统计满足某条件的记录数。如统计某个班某科参考人数、某个分数段（如80分以上）的人数等。上面的人数统计也可用SUmPrOdUCt函数，这样还简单些，不用总分右边的辅助列了，可以减少很多不必要的计算。4、年级平

9、均分：语文和英语文理同卷，用全年级的语文（英语）总分除以全年级语文（英语）参考人数即可。其它文理科平均分：文科语文的总分除以文科语文的参考人数即可。5、各班的平均分：各科总分除以各科参考人数。三、利用SUmif、count、sumproduct函数处理数据A、B、C等是关键的单元格，其它单元格可以参照这些单元格的公式进行计算。相应的公式：A=SUMPRoDUCT(B2B1401=”文科”)*(H2:H1401=女)B=SUMPRoDUCT(B2:B1401F文科”)*(H2:H1401=n男)C=SUMPRoDUCT(B2:B1401二”理科)*(H2:H1401=男)D=SUM(12:114

10、01)/CoUNT(12:11401)E=SUM(K2:K1401)COUNT(K2:K1401)F=SUMlF(B2:B1401J文科”,12:11401)/SUMPRoDUCT(B2:B1401=文科”)*(12:114010”)G=SUMPRoDUCT(B2:B1401=”文科”)*(12:114010”)H=SUMPRODUCT(B2:B1401二“理科)*(12:11401。”)1班数据的处理：I=SUMPRODUCT(A2:A1401=l)*(H2:H1401=女)，1班女生人数J=SUMPRODUCT(A2:A1401=l)*(H2:H1401二男)，1班男生人数K=B1412+

11、C1412,即求和，1班总人数1.=SUMIF(A2:A1401,n=rI2:I1401)/SUMPR0DUCT(A2:A1401=ir(I2:I1401o),1班语文平均分以下公式输入技巧：复制语文平均分的计算公式，粘贴到数学、英语、物理等单元格，再用鼠标拖动相应科目的计算范围就行了。M=SUMIF(A2:A140IJ=1,J2J1401)SUMPR0DUCT(A2:A1401=l)*(J2:J1401m),1班数学平均分N=SUMlF(A2:A1401,”=l”,K2K1401)/SUMPRoDUCT(A2:A1401=l)*(K2:K1401o”)，1班英语平均分O=SUMIF(A2A1

12、401,=,1.21.1401)SUMPRODUCT(A2A1401=l)*(1.21.1401,r),1班物理平均分P=SUMIF(A2:A1401,n=r;M2:M1401)/SUMPR0DUCT(A2:A1401=l)*(M2:M1401o,u),1班化学平均分Q=SUMIF(A2:A1401,”=l”,N2:N1401)/SUMPRoDUCT(A2:A1401=l)*(N2:Nl4010”)，1班生物平均分R=SUMIF(A2:A1401,”=l”,02:0140)/SUMPRe)DUCT(A2:A1401=l)*(02:0140o”),1班政治平均分S=SUMIF(A2:A1401,

13、n=r,P2:P1401)/SUMPRODUCT(A2:A1401=l)*(P2:P1401,H,),1班历史平均分T=SUMlF(A2:A1401,=l”,Q2Q1401)/SUMPRoDUeT(A2:A1401=l)*(Q2:Q1401”)，1班地理平均分U=SUMIF(A2:A1401；=1,R2:R1401)/SUMPRODUCT(A2:A1401=1)*(R2:R1401M),1班总分平均分其它各班数据的理：选中1班女生单元格B1412中公式=SUMPRODUCT(A2:A1401=l)*(H2:H1401二女)，按CtrHC复制，移动光标到2班的12单元格、3班的13单元格、同时按

14、Ctrl+V进行粘贴，得到所有的班的女生人数，注意：都与1班相同。注意，不要用鼠标向下拖拉的方式复制操作。同样的方法处理“男生”，平均分右边的“语文”、“数学”、“总分”。这样得到了所有班的男生人数及各科的平均分都与1班完全相同。再用查找与替换操作进行修改，得到各班的真实数据：选中2班所在行，执行查找与替换,查找内容为：=1,替换内容为：=2(即2班，3班改为：=3,4班改为：=4,30班就改为：=30)点击“全部替换”即可。如图优秀率和合格率的处理，同平均分。优秀率：二该科优秀人数/该科参考人数，如1班语文，公式为：=SUMPRODUCT(A2:A1401=1)*(I2:I1401=120)

15、/SUMPRODUCT(A2:A1401=1)*(I2:I140111)*100物理优秀率，公式为：=SUMPRODUCT(A2:A1401=1)*(1.2:1.1401=96)/SUMPRODUCT(A2:A1401=1)*(I2:1140111,)*100合格率：二该科合格人数/该科参考人数，如1班语文，公式为：=SUMPRODUCT(A2:A1401=1)*(12:11401=90)/SUMPRoDUCT(A2:A1401=1)*(12:1140111,)*100其它操作相似。分数段人数统计：总分600以上人数，参考公式：=SUMPRODUCT(A2:A1401=1)*(R2:R1401=600)语文成绩120分以上人数，参考公式：=SUMPRODUCT(B2:B1401=文科,)*(I2I1401=120)长二MAX(I2:I1401)CDEFGHIJK1.MNOPQR篇号