利用Excel自带函数统计学生的考试成绩.docVIP

利用Excel自带函数统计学生的考试成绩 一、首先制作如下电子表格：（截图时因为电脑屏幕较小，将表格截成了两个图） 下面需要①在成绩栏中输入函数，让表格自动根据成绩判断等级并显示出来。②在参考人数栏输入函数判断参考人数并显示出来。③在实算人数栏输入函数判断实算人数并显示出来。④在及格人数栏输入函数判断及格人数并显示出来。⑤在优秀人数栏输入函数判断优秀人数并显示出来。⑥算及格率。⑦算优秀率。⑧算平均分。【假设满分为100分, 59.5分以下等级为“D”，59.5（包括59.5）分至79.5分为“C”，79.5（包括79.5）分至89.5分为“B”，89.5（包括89.5分）以上为“A”。 实算率为94%】 二、用if函数判断等级 在“D6”单元格中输入以下公式： =iF(AND(100=O6,O6=89.5),A,IF(AND(89.5O6,O6=79.5),B,IF(AND(79.5O6,O6=59.5),C,D,)))) 然后通过拖放鼠标的方式在等级栏处填充公式。公式中的引号要用英文的。 三、用count函数统计参考人数 在“O18”单元格中输入以下公式： =IF(COUNT(C6:C26,G6:G26,K6:K26,O6:O17)=0,,COUNT(C6:C26,G6:G26,K6:K26,O6:O17)) 四、参考人数乘实算率得实算人数 在“O19”单元格中输入以下公式： =IF(AND(O180,O18=75),ROUND(O18*0.94,0),) 五、用countif函数统计及格人数 在“O20”单元格中输入以下公式： =IF(COUNT(C6:C26,G6:G26,K6:K26,O6:O17),IF(COUNTIF(C6:C26,=59.5)+COUNTIF(G6:G26,=59.5)+COUNTIF(K6:K26,=59.5)+COUNTIF(O6:O17,=59.5)ROUND(O18*0.94,0),ROUND(O18*0.94,0),COUNTIF(C6:C26,=59.5)+COUNTIF(G6:G26,=59.5)+COUNTIF(K6:K26,=59.5)+COUNTIF(O6:O17,=59.5)),) 六、用countif函数统计及格人数 在“O22”单元格中输入以下公式： =IF(COUNT(C6:C26,G6:G26,K6:K26,O6:O17),IF(COUNTIF(C6:C26,=89.5)+COUNTIF(G6:G26,=89.5)+COUNTIF(K6:K26,=89.5)+COUNTIF(O6:O17,=89.5)ROUND(O18*0.94,0),ROUND(O18*0.94,0),COUNTIF(C6:C26,=89.5)+COUNTIF(G6:G26,=89.5)+COUNTIF(K6:K26,=89.5)+COUNTIF(O6:O17,=89.5)),) 七、求及格率与优秀率（可以事先将单元格格式设置成百分数并保留2位小数） 在“O21”单元格中输入： =IF(AND(O18=0,O18=75),ROUND(O20/O19,4),) 在“O23”单元格中输入： =IF(AND(O18=0,O18=75),ROUND(O22/O19,4),) 八、统计平均分 将单元格“O24”的格式设置为保留两位小数的数值格式，并输入下列公式： =IF(COUNT(C6:C26,G6:G26,K6:K26,O6:O17)=0,,IF(AND(COUNT(C6:C26,G6:G26,K6:K26,O6:O17)0,ROUND(O18*0.06,0)1),AVERAGE(C6:C26,G6:G26,K6:K26,O6:O17),IF(ROUND(O18*0.06,0)=1,(SUM(C6:C26,G6:G26,K6:K26,O6:O17)-SMALL((C6:C26,G6:G26,K6:K26,O6:O17),1))/O19,IF(ROUND(O18*0.06,0)=2,(SUM(C6:C26,G6:G26,K6:K26,O6:O17)-SMALL((C6:C26,G6:G26,K6:K26,O6:O17),1)-SMALL((C6:C26,G6:G26,K6:K26,O6:O17),2))/O19,IF(ROUND(O18*0.06,0)=3,(SUM(C6:C26,G6:G26,K6:K26,O6:O17)-SMALL((C6:C26,G6:G26,K6:K26,O6:O17),1)-SMALL((C6:C26,G6:G26,K6:K26,O6:O17),2)-SMALL((C6:C26,G6:G26,K6:K26,O6:O17),3))/O19,IF(ROUND(O18*0.06,0)=4