Excel中求優(yōu)分率和及格率

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

2、成績分析表”，在第二行從A1 單元格開始順序輸入記錄字段名：班級、學(xué)號、姓名、政治、計算機(jī)、數(shù)學(xué)、英語、操作基礎(chǔ)、電子技術(shù)。假設(shè)全級學(xué)生有 695 人，所以從第 3 行到第 698 行用來輸入所有學(xué)生各科的成績。(二) 設(shè)計成績分析工作表 下面就以計算機(jī)科分析表設(shè)計為例進(jìn)行描述，其他科的分析相似操作。1. 設(shè)計分析表將 A1：I1 合并并居中， 輸入“* 中學(xué) 20042005 學(xué)年第一學(xué)期成績分析表”。 用同 樣的方法將 A2： I2 合并并居中，輸入“科目：計算機(jī)”其他文字按表格內(nèi)容輸入即可。2. 求科各班參考人數(shù)我們在這里可采用 COUNTIF函數(shù)，COUNTIF函數(shù)用于計算給定區(qū)域內(nèi)滿

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

4、t;= A4", 各科 考試成績 !$H$3: 各科考試成績 !$H$698) ”，該單元格公式作用為：計算 107 班計算機(jī)科總 分。然后對D4至D7單元格進(jìn)行向下自動填充操作，就可以自動得到D5至D7單元格的公式。最后在D8單元格中輸入“ =SUM(D4 D7)，求出全年級總分。4. 求各班平均分在E4中輸入“ =D4/C4',算出107班的平均分，然后用拖動方法對E4對E7單元格進(jìn)行向下自動真充操作，就可以自動得到E5至E7單元格的公式，最后在 E8單元格中輸入“ =Average(D4：D7)，求出全年級平均分。5. 求各班合格人數(shù)我們在這里可采用數(shù)組公式進(jìn)行多重標(biāo)準(zhǔn)

5、單元計算。首先在F4 單元格中輸入“ =SUM(各科考試成績！$A$2:各科考試成績！$A$698=A4)*(各科考試成績！$H$3:各科考 試成績!$H$698, >=60) ”，按下SHIFT+ CTRL鍵敲回車，Excel會自動在公式兩側(cè)加上大括 號，生成數(shù)組公式。然后對 F4至F7單元格進(jìn)行向下自動填充操作。最后在 F8中輸入“ =SUM(F4:F7),求出全級及格人數(shù)。6. 求各班合格率我們可在 G4 單元格中輸入“ =IF(C4=0，0，F(xiàn)4/C4);100 ”，為避免分母為 0 的錯誤，用 了 IF 函數(shù)。公式的作用為：若 107 班參考人數(shù)不為 0，則合格率為合格人數(shù)除以

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

7、中輸入“ =IF(C4=0,0,H4/C4);100 ”， 為避免分母為 0 的錯誤，用了 IF 函數(shù)。公式的作用為：若 1 07班參考人數(shù)不為 0，則優(yōu)生率為優(yōu)生人數(shù) 除以參考人數(shù)后乘以 100。然后對 I4 至 I7 單元格進(jìn)行向下自動填充操作。最后在 I8 中輸 入“ =average(I4:I7)"算出全年級的平均優(yōu)生率。9. 求各班最高分求最高分可用 MAX函數(shù)，這個函數(shù)的作用是求出參數(shù)中的最大值。所以在C11中輸入“=MAX(IF(各科考試成績!$A$3:各科考試成績!$A$698=A4,各科考試成績!$H$3:各科考試 成績!$H$698) ”按下SHIFT + CTR

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

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

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