《Excel中求优分率和及格率》由会员分享，可在线阅读，更多相关《Excel中求优分率和及格率（3页珍藏版）》请在金锄头文库上搜索。
1、Excel中求优分率和及格率2006-11-23 12:27每次考试，我们都要对考试情况作分析，所分析的项目包括有参考班级，参考人数、平均分，合 格人数、合格率、优秀人数、优秀率，最高分、最低分以及各分数段所占的人数：100分人数、8099分人数、6079人数、3059人数、30分以下人数。这个统计比较复杂，且工作量 大，如果用人工计算，不但效率低，且出错机率大，而用EXCEL函数可以很好解决这个问题。我们可采用以下方法：新建空白Excel工作簿，将自动建立的Sheetl、Sheet2工作表重命名 为成绩分析表、各科考试成绩。其中各科考试成绩工作表用于输入各科成绩。(一) 设计各科成绩工作表在
2、各科考试成绩工作表第一行中输入分析名称，如“ *中学20042005学年上期各科成绩 分析表”，在第二行从A1单元格开始顺序输入记录字段名：班级、学号、姓名、政治、计算机、数学、英语、操作基础、电子技术。假设全级学生有695人，所以从第3行到第698行用来输入所有学生各科的成绩。(二) 设计成绩分析工作表下面就以计算机科分析表设计为例进行描述，其他科的分析相似操作。1. 设计分析表将A1 : 11合并并居中，输入中学20042005学年第一学期成绩分析表”。用同样的方 法将A2 : I2合并并居中，输入“科目：计算机”其他文字按表格内容输入即可。2. 求科各班参考人数我们在这里可采用COUNT
3、IF函数，COUNTIF函数用于计算给定区域内满足特定条件的单元格 的数目，所以在C4单元格中输入“ =COUNTIF各科考试成绩！ $A$3:各科考试成 绩!$A$698,“=A4 ”)” ，即可求出107班的参考人数。然后选中C4单元格，拖动C4单元 格右下角的小“十”字图标至C7单元格(复制公式)，这样，就能自动得到C4至C7单元格的公式。最后在C8单元格中输入“ =SUM(C4:C7”)，用于分析全年级参考总人数。3. 求科各班总分我们在这里可采用SUMIF函数，SUMIF函数功能是根据指定条件对若干单元格求和，在D 4单元格中输入“ =SUMIF各科考试成绩!$A$3:各科考试成绩!
4、$A$698,”= A4”,各科 考试成 绩!$H$3:各科考试成绩!$H$698) ”，该单元格公式作用为：计算107班计算机科总分。然后对 D4至D7单元格进行向下自动填充操作，就可以自动得到D5至D7单元格的公式。最后在D8单元格中输入“ =SUM(D4 D7)，求出全年级总分。4. 求各班平均分在E4中输入“ =D4/C4算出107班的平均分，然后用拖动方法对E4对E7单元格进行向下自动真充操作，就可以自动得到E5至E7单元格的公式，最后在E8单元格中输入“ =Average(D4 :D7)，求出全年级平均分。5. 求各班合格人数我们在这里可采用数组公式进行多重标准单元计算。首先在F4
5、单元格中输入“=SUM(各科考试成绩!$A$2:各科考试成绩!$A$698=A4)*(各科考试成绩!$H$3:各科考 试成 绩!$H$698,=60) ”，按下SHIFT+ CTRL键敲回车Excel会自动在公式两侧加上大括号，生成数 组公式。然后对F4至F7单元格进行向下自动填充操作。最后在F8中输入“ =SUM(F4:F7),求出全级及格人数。6. 求各班合格率我们可在G4单元格中输入“ =IF(C4=0,0,F4/C4);100”，为避免分母为0的错误，用了 IF函 数。公式的作用为：若107班参考人数不为0,则合格率为合格人数除以参考人数后乘以100。然后对G4至G7单元格进行向下自动
6、填充操作。然后在G8中输入Average(G4:G7),算出全年级的平均合格率。7. 求各班优生人数一般对分数高于或等于80分的学生称为优生。这个问题跟求合格率类似,首先在H4单元格中输入“ =SUM(各科考试成绩!$A$2:各科考试成绩!$A$698=A4门各科考试成绩！ $ H $ 3 :各 科考试成绩！ $H$698,=80) ”。然后对H4至H7单元格进行向下自动填充操作。最后在H8单元格中输入“ =SUM(H4:H7)，以求得全年级优生人数。8. 求各班优生率跟求合格率类似，首先在I4单元格中输入“ =IF(C4=0,0,H4/C4);100”，为避免分母为0的错 误，用了 IF函数
7、。公式的作用为：若107班参考人数不为0,则优生率为优生人数除以参考人数后乘 以100。然后对I4至I7单元格进行向下自动填充操作。最后在I8中输入“ =average(l4:l7)出全 年级的平均优生率。9. 求各班最高分求最高分可用max函数，这个函数的作用是求岀参数中的最大值。所以在 C11中输入“=MAX(IF(各科考试成绩!$A$3洛科考试成绩!$A$698=A4,各科考试成绩!$H$3:各科考试 成绩!$H$698) ”按下SHIFT + CTRL键敲回车，生成数组公式。即可算出107班计算机科中的最高分。然后对C12至C14单元格进行向下自动填充操作。最后在C15单元格中输入 “
8、=MAX(C11:C14)，以求得全年级的最高分。10. 求各班最低分最低分的算法与最高分的算法很相似，只是函数名不同，求最低分可用MIN函数，这个函数的作用是求出参数中的最小值。所以在D11中输入“ =M I N （IF （各科考试成绩!$A$3:各科 考试成绩！ $A$698=A4 :各科考试成绩！ $H$3咯科考试成绩！ $H$698） ”按下SHIFT+ CTRL 键敲回车，生成数组公式。即可算出107班计算机科中的最低分。然后对D12至D14单元格进行向 下自动填充操作。最后在H15单元格中输入“ =MIN （D11:D14） ”，以求得全年级的最低分。11. 求100分的人数计算分
9、数段人数仍可用数组的方法，如计算100分可用人数可在E11中输入“ =SUM （各科考试成绩!$A$2洛科考试成绩!$A$698=A4） * （各科考试成绩！ $H$3:各科考试 成绩!$H$698,=100） ”，按下SHIFT+ CTRL键敲回车，然后对E12至E14单元格进行向下自动 填充操作。然后在E15单元格中输入“ =SUM （E11:E14”，以求得全年级100分的人数。12. 求8099分数段人数求计算机科各班8099分数段人数，将用到三个条件进行标准单元计算。首先在F11中输入二SUM （各科考试成绩!$A$2:各科考试成绩!$A$698=A4）;（各科考试成绩!$H$3:各
10、科 考试成 绩！ $H$698,=80）按下SHIFT + CTRL键敲回车，生成数组公式。然后对F12至F14单元格进行向下自动填充操作。最后在F15单元格中输入“ =SUM （F11:F14”，求出全年级8099分数段人数。13. 求6079分数段人数求计算机科各班6079分数段人数，类似上述方法，只改变条件即可，首先在G11中输入 =SUM （各科考试成绩!$A$2:各科考试成绩!$A$698=A4）;（各科考试成绩!$H$3:各科考 试成 绩！$H$698,v79） ） * （各科考试成绩!$H$3:各科考试成绩!$H$698,=60）按下SHIFT + CTRL 键敲回车，生成数组公式。然后对F12至F14单元格进行向下自动填充操作。最后在G15单元格中输入“ =SUM （G11:G14），求出全年级6079分数段人数。同样的操作，可以分别分析各班计算机科共它各分数段人数和总分各分数段人数。
