Excel中求優(yōu)分率、及格率及各分?jǐn)?shù)段人數(shù)

1、Excel 中求優(yōu)分率、及格率及各分?jǐn)?shù)段人數(shù)每次考試， 我們都要對(duì)考試情況作分析， 所分析的項(xiàng)目包括有參考班級(jí)，參考人數(shù)、平 均分，合格人數(shù)、合格率、優(yōu)秀人數(shù)、優(yōu)秀率，最高分、最低分以及各分?jǐn)?shù)段所占的人數(shù)： 100分人數(shù)、8099分人數(shù)、6079人數(shù)、3059人數(shù)、30分以下人數(shù)。這個(gè)統(tǒng)計(jì)比較 復(fù)雜，且工作量大，如果用人工計(jì)算，不但效率低，且出錯(cuò)機(jī)率大，而用EXCEL 函數(shù)可以我們可采用以下方法：新建空白 重命名為成績(jī)分析表、各科考試成績(jī)。很好解決這個(gè)問(wèn)題。Excel 工作簿，將自動(dòng)建立的 Sheet1 、 Sheet2 工作表 其中各科考試成績(jī)工作表用于輸入各科成績(jī)。（一）設(shè)計(jì)各科成績(jī)工作表

2、在各科考試成績(jī)工作表第一行中輸入分析名稱，如“ * 中學(xué) 2004 2005 學(xué)年上期各 科成績(jī)分析表”，在第二行從 A1 單元格開(kāi)始順序輸入記錄字段名：班級(jí)、學(xué)號(hào)、姓名、政 治、計(jì)算機(jī)、數(shù)學(xué)、英語(yǔ)、操作基礎(chǔ)、電子技術(shù)。假設(shè)全級(jí)學(xué)生有 695 人，所以從第 3 行到第 698 行用來(lái)輸入所有學(xué)生各科的成績(jī)。（二）設(shè)計(jì)成績(jī)分析工作表下面就以計(jì)算機(jī)科分析表設(shè)計(jì)為例進(jìn)行描述，其他科的分析相似操作。1. 設(shè)計(jì)分析表將 A1：I1 合并并居中， 輸入“* 中學(xué) 20042005 學(xué)年第一學(xué)期成績(jī)分析表”。 用同 樣的方法將 A2： I2 合并并居中，輸入“科目：計(jì)算機(jī)”其他文字按表格內(nèi)容輸入即可。2.

3、求科各班參考人數(shù)我們?cè)谶@里可采用 COUNTIF函數(shù)，COUNTIF函數(shù)用于計(jì)算給定區(qū)域內(nèi)滿足特定條件的單 元格的數(shù)目，所以在 C4單元格中輸入“ =COUNTIF各科考試成績(jī)！$A$3:各科考試成 績(jī)!$A$698,"=A4 ”）”， 即可求出107班的參考人數(shù)。然后選中 C4單元格，拖動(dòng) C4單元 格右下角的小“十”字圖標(biāo)至 C7單元格（復(fù)制公式），這樣，就能自動(dòng)得到 C4至C7單元 格的公式。最后在 C8 單元格中輸入“ =SUM（C4:C7”） ，用于分析全年級(jí)參考總?cè)藬?shù)。3. 求科各班總分我們?cè)谶@里可采用 SUMIF 函數(shù)， SUMIF 函數(shù)功能是根據(jù)指定條件對(duì)若干單元格求

4、和， 在D 4單元格中輸入“ =SUMIF各科考試成績(jī)！$A$3:各科考試成績(jī)！$A$698,"= A4", 各科 考試成績(jī) !$H$3: 各科考試成績(jī) !$H$698) ”，該單元格公式作用為：計(jì)算 107 班計(jì)算機(jī)科總 分。然后對(duì)D4至D7單元格進(jìn)行向下自動(dòng)填充操作，就可以自動(dòng)得到D5至D7單元格的公式。最后在D8單元格中輸入“ =SUM(D4 D7)，求出全年級(jí)總分。4. 求各班平均分在E4中輸入“ =D4/C4',算出107班的平均分，然后用拖動(dòng)方法對(duì)E4對(duì)E7單元格進(jìn)行向下自動(dòng)真充操作，就可以自動(dòng)得到E5至E7單元格的公式，最后在 E8單元格中輸入“ =A

5、verage(D4：D7)，求出全年級(jí)平均分。5. 求各班合格人數(shù)我們?cè)谶@里可采用數(shù)組公式進(jìn)行多重標(biāo)準(zhǔn)單元計(jì)算。首先在F4 單元格中輸入“ =SUM(各科考試成績(jī)!$A$2:各科考試成績(jī)!$A$698=A4)*(各科考試成績(jī)!$H$3:各科考 試成績(jī)!$H$698,>=60) ”，按下 SHIFT+ CTRL鍵敲回車，Excel會(huì)自動(dòng)在公式兩側(cè)加上大括 號(hào)，生成數(shù)組公式。然后對(duì) F4至F7單元格進(jìn)行向下自動(dòng)填充操作。最后在 F8中輸入“=SUM(F4:F7)” ，求出全級(jí)及格人數(shù)。6. 求各班合格率我們可在 G4 單元格中輸入“ =IF(C4=0,0,F4/C4);100 ”，為避免分母

6、為 0的錯(cuò)誤，用 了 IF函數(shù)。公式的作用為：若 107班參考人數(shù)不為0,則合格率為合格人數(shù)除以參考人數(shù) 后乘以100。然后對(duì)G4至G7單元格進(jìn)行向下自動(dòng)填充操作。然后在 G8中輸入 Average(G4:G7), 算出全年級(jí)的平均合格率。7. 求各班優(yōu)生人數(shù)一般對(duì)分?jǐn)?shù)高于或等于 80 分的學(xué)生稱為優(yōu)生。這個(gè)問(wèn)題跟求合格率類似,首先在H4單元格中輸入“ =SUM(各科考試成績(jī)!$A$2:各科考試成績(jī)!$A$698=A4)*(各科考試成績(jī)！ $ H $ 3 :各科考試成績(jī)！$H$698,>=80) ”。然后對(duì) H4至H7單元格進(jìn)行向下自動(dòng)填充操作。 最后在H8單元格中輸入“ =SUM(H4

7、:H7”，以求得全年級(jí)優(yōu)生人數(shù)。8. 求各班優(yōu)生率跟求合格率類似, 首先在 I4 單元格中輸入“ =IF(C4=0,0,H4/C4);100 ”, 為避免分母為 0 的錯(cuò)誤,用了 IF 函數(shù)。公式的作用為：若 107班參考人數(shù)不為 0,則優(yōu)生率為優(yōu)生人數(shù) 除以參考人數(shù)后乘以 100。然后對(duì)I4至I7單元格進(jìn)行向下自動(dòng)填充操作。最后在 I8中輸 入“ =average(I4:I7) ”算出全年級(jí)的平均優(yōu)生率。9. 求各班最高分求最高分可用 MAX函數(shù)，這個(gè)函數(shù)的作用是求出參數(shù)中的最大值。所以在C11中輸入“=MAX（IF（各科考試成績(jī)！$A$3:各科考試成績(jī)！$A$698=A4,各科考試成績(jī)！$

8、H$3:各科考試 成績(jī)!$H$698） ”按下SHIFT + CTRL鍵敲回車，生成數(shù)組公式。即可算出107班計(jì)算機(jī)科中的最高分。然后對(duì) C12至C14單元格進(jìn)行向下自動(dòng)填充操作。最后在C15單元格中輸入“=MAX（C11:C14”） ，以求得全年級(jí)的最高分。10. 求各班最低分最低分的算法與最高分的算法很相似，只是函數(shù)名不同，求最低分可用MIN函數(shù)，這個(gè)函數(shù)的作用是求出參數(shù)中的最小值。所以在D 11中輸入“ =MI N（IF（各科考試成績(jī)!$A$3:各科考試成績(jī)!$A$698=A4 :各科考試成績(jī)!$H$3:各科考試成績(jī)!$H$698） ”按下 SHIFT+ CTRL鍵敲回車，生成數(shù)組公式。

9、即可算出107班計(jì)算機(jī)科中的最低分。 然后對(duì)D12至D14單 元格進(jìn)行向下自動(dòng)填充操作。最后在H15單元格中輸入“ =MIN（D11:D14）”，以求得全年級(jí)的最低分。11. 求 100 分的人數(shù)計(jì)算分?jǐn)?shù)段人數(shù)仍可用數(shù)組的方法，如計(jì)算 100分可用人數(shù)可在 E11 中輸入“ =SUM（各科考試成績(jī)!$A$2:各科考試成績(jī)!$A$698=A4）*（各科考試成績(jī)！ $ H $ 3 : 各科 考試成績(jī)!$H$698,=100） ”，按下SHIFT+ CTRL鍵敲回車，然后對(duì)E12至E14單元格進(jìn)行 向下自動(dòng)填充操作。然后在 E15單元格中輸入“ =SUM（E11:E14”，以求得全年級(jí) 100分的

10、人數(shù)。12. 求8099分?jǐn)?shù)段人數(shù)求計(jì)算機(jī)科各班8099分?jǐn)?shù)段人數(shù)，將用到三個(gè)條件進(jìn)行標(biāo)準(zhǔn)單元計(jì)算。首先在F11中輸入=SUM（各科考試成績(jī)!$A$2:各科考試成績(jī)!$A$698=A4）;（各科考試成績(jī)!$H$3:各科 考試成績(jī) !$H$698,<100）*（ 各科考試成績(jī) !$H$3: 各科考試成績(jī) !$H$698,>=80） 按下 SHIFT + CTRL鍵敲回車，生成數(shù)組公式。然后對(duì)F12至F14單元格進(jìn)行向下自動(dòng)填充操作。最后在F15單元格中輸入“ =SUM（F11:F14”，求出全年級(jí)8099分?jǐn)?shù)段人數(shù)。13. 求 60 79 分?jǐn)?shù)段人數(shù)求計(jì)算機(jī)科各班 6079 分?jǐn)?shù)段人數(shù)，類似上述方法，只改變條件即可，首先在G11 中輸入=SUM（各科考試成績(jī)!$A$2:各科考試成績(jī)!$A$698=A4）;（各科考試成績(jī)!$H$3:各科考 試成績(jī) !$H$698,<79）*（ 各科考試成