Excel在學校中的應用28-學生成績綜合統(tǒng)計表(一)

1、Excel在學校中的應用28-學生成績綜合統(tǒng)計表（一）4.6學生成績綜合統(tǒng)計表（一）案例背景期末考試后，除了要統(tǒng)計各個分數段人數分布情況外（前面章節(jié)已經介紹了多種統(tǒng)計分數段的方法）可能還要統(tǒng)計年級和各班的及格人數與及格率，優(yōu)秀人數與優(yōu)秀率，以及各班里前、后若干名（如年級前20名和后20名）的人數和比例等。本案例以某中學高一年級4個教學班100名學生的期末考試的3科成績和總分為樣本，統(tǒng)計全年級4個班級學生的期末考試及格人數與及格率，優(yōu)秀人數與優(yōu)秀率，以及各班里年級前20名和后20名的人數和比例，以便進行綜合分析。同時將前20名和后20名學生的成績在統(tǒng)計表中標示出來，以使得前、后20名的成績能夠在

2、成績表突出顯示。要實現(xiàn)本案例中的功能，學員應該掌握以下EXCEL技術點基礎知識條件格式函數應用ISNUMBER函數SUM函數，LARGE函數，SMALL函數綜合應用函數嵌套數組公式。最總效果展示4.6.1 創(chuàng)建原始數據表Step1創(chuàng)建工作簿、重名工作表創(chuàng)建工作簿“綜合統(tǒng)計表.xls”，然后將工作表重命名為“統(tǒng)計表”，刪除多余工作表。Step2輸入原始成績在單元格A49輸入表格標題：“高一年級期末考試成績統(tǒng)計表”，選擇你好單元格區(qū)域A49:G49，單擊“合并及居中”按鈕，字體、字型、字號設置為黑體加粗16號。在單元格區(qū)域A50:G50輸入字段名：“班級”，“學號”，“姓名”，“語文”，“數學“，

3、“英語”和“總分“。在單元格區(qū)域A51：F150輸入全年級4個班100名學生的“語文”，“數學“，“英語”成績在單元格G51輸入公式：“=SUM(D51:F51)”，然后按鍵確認，計算出第一個學生的“總分”。選中單元格G51，雙擊單元格G51右下角的填充柄即可完成其余學生“總分”的統(tǒng)計工作。選中單元格區(qū)域A50:G150,按圖2樣式設置邊框，添加底紋，文本居中顯示。4.6.2統(tǒng)計及格人數與及格率Step1創(chuàng)建統(tǒng)計表框架在單元格區(qū)域A1:J9按圖3樣式輸入“高一年級各班及格人數與及格率統(tǒng)計表”框架信息。Step2輸入統(tǒng)計分數線選中單元格區(qū)域B3:D3，輸入“60”，按組合鍵確認。Step3統(tǒng)計及

4、格人數在單元格B4輸入如下公式，然后按組合鍵確認?！?SUM(ISNUMBER(D$51:D$150)*(D$51:D$150>=B3)”在單元格B5輸入如下公式，按組合鍵確認?！?SUM($A$51:$A$150=1)*(D$51:D$150>=B$3)”在單元格區(qū)域B6：B8分別輸入如下公式，按組合鍵確認?！?SUM($A$51:$A$150=2)*(D$51:D$150>=B$3)”“=SUM($A$51:$A$150=3)*(D$51:D$150>=B$3)”“=SUM($A$51:$A$150=4)*(D$51:D$150&am

5、p;gt;=B$3)”在單元格B9中輸入公式：“=SUM(B5:B8)“，然后按鍵確認，即可驗證上述各班統(tǒng)計數之和是否等于年級統(tǒng)計數。選中單元格區(qū)域B4：B9，向右拖曳單元格B9右下角的填充柄至E列即可完成“數學“、”英語“和”總分“的相應統(tǒng)計工作。Step4統(tǒng)計及格率在單元格G4輸入公式“=B4/100“，然后按鍵確認。選中單元格G4，向下拖曳單元格G4右下角的填充柄至單元格G8即可完成各班：“語文“學科及格率的統(tǒng)計。在單元格G9輸入公式：“=SUM(G5:G8)“，然后按鍵確認。選中單元格區(qū)域G4：G9，向右拖曳單元格G9右下角的填充柄至J列即可完成“數學“、”英語“和”總分“及格率的統(tǒng)計

6、和驗證工作。選中單元格區(qū)域G4:J9,單擊菜單“格式單元格“彈出”單元格格式“對話框，切換到數字選項卡，在”分類“選項框中選擇”百分比“，然后單擊”確定“按鈕，即可將該區(qū)域數值設置成百分比形式顯示。4.6.3統(tǒng)計優(yōu)秀人數與優(yōu)秀率Step1創(chuàng)建統(tǒng)計表框架在單元格區(qū)域A12:J20按圖3樣式輸入“高一年級各班優(yōu)秀人數與優(yōu)秀率統(tǒng)計表”框架信息。Step2輸入統(tǒng)計分數線選中單元格區(qū)域B14:D14，輸入“90”，按組合鍵確認。在單元格E3中輸入“270”。Step3統(tǒng)計優(yōu)秀人數在單元格B15輸入如下公式，然后按組合鍵確認。“=SUM(ISNUMBER(D$51:D$150)*(D$51:D$150&a

7、mp;gt;=B14)”在單元格B16輸入如下公式，然后按組合鍵確認?！?SUM($A$51:$A$150=1)*(D$51:D$150>=B$14)”在單元格區(qū)域B17：B19分別輸入如下公式，然后按組合鍵確認?！?SUM($A$51:$A$150=2)*(D$51:D$150>=B$14)”“=SUM($A$51:$A$150=3)*(D$51:D$150>=B$14)”“=SUM($A$51:$A$150=4)*(D$51:D$150>=B$14)”在單元格B20中輸入公式：“=SUM(B16:B19)“，然后按鍵確認，即可驗證上述

8、各班統(tǒng)計數之和是否等于年級統(tǒng)計數。選中單元格區(qū)域B15：B20，向右拖曳單元格B20右下角的填充柄至E列即可完成“數學“、”英語“和”總分“的相應統(tǒng)計工作。Step4統(tǒng)計優(yōu)秀率在單元格G15輸入公式“=B15/100“，然后按鍵確認。選中單元格G15，向下拖曳單元格G15右下角的填充柄至單元格G19即可完成各班：“語文“學科及格率的統(tǒng)計。在單元格G20輸入公式：“=SUM(G16:G19)“，然后按鍵確認。選中單元格區(qū)域G15：G20，向右拖曳單元格G20右下角的填充柄至J列即可完成“數學“、”英語“和”總分“優(yōu)秀率的統(tǒng)計和驗證工作。選中單元格區(qū)域G15:J20,單擊菜單“格式單元格“，彈出”

9、單元格格式“對話框，切換到數字選項卡，在”分類“選項框中選擇”百分比“，然后單擊”確定“按鈕，即可將該區(qū)域數值設置成百分比形式顯示。4.6.4統(tǒng)計年級前20名中各班所占人數和比例Step1創(chuàng)建統(tǒng)計表框架在單元格區(qū)域A23:J31按圖12樣式輸入統(tǒng)計表框架信息。Step2計算”前20名”分數線在單元格B25輸入公式“=LARGE(D$51:D$150,$B$23)然后按鍵確認。選中單元格B25，向右拖曳單元格B25右下角的填充柄至E列完成公式填充，即可得到“數學“、”英語“和”總分“第20名的分數。Step3統(tǒng)計年級和各班所占前20名人數在單元格B26輸入如下公式，然后按組合鍵確認?！?SUM(

10、ISNUMBER(D$51:D$150)*(D$51:D$150>=B25)”在單元格B27輸入如下公式，然后按組合鍵確認。“=SUM($A$51:$A$150=1)*(D$51:D$150>=B$25)”在單元格區(qū)域B28:B30分別輸入如下公式，按組合鍵確認。“=SUM($A$51:$A$150=2)*(D$51:D$150>=B$25)”“=SUM($A$51:$A$150=3)*(D$51:D$150>=B$25)”“=SUM($A$51:$A$150=4)*(D$51:D$150>=B$25)”在單元格B31輸入公

11、式：“=SUM(B27:B30)”，然后按鍵確認。即可驗證上述各班統(tǒng)計數之和是否等于年級統(tǒng)計數。選中單元格區(qū)域B26：B31，向右拖曳單元格B31右下角的填充柄至E列完成公式填充，即可得到“數學“、”英語“和”總分“前20名中年級和各班所占人數。Step4統(tǒng)計中各班所占年級前20名的比例選中單元格區(qū)域G26:J26,輸入“100%”，然后按組合鍵確認。選中單元格G27，向下拖曳單元格G27右下角的填充柄至G30完成公式填充。在單元格G31中輸入公式：“=SUM(G27:G30)”，然后按鍵確認。選中單元格區(qū)域G27:G31，向右拖曳單元格G31右下角的填充柄至J列完成公式填充。4.6.5統(tǒng)計年

12、級后20名中各班所占人數和比例Step1創(chuàng)建統(tǒng)計表框架在單元格區(qū)域A34:J42按圖17樣式輸入統(tǒng)計表框架信息。Step2計算”后20名”分數線在單元格B36輸入公式“=SMALL(D$51:D$150,$B$34)”然后按鍵確認。選中單元格B36，向右拖曳單元格B36右下角的填充柄至E列完成公式填充，即可得到“數學“、”英語“和”總分“倒數第20名的分數。Step3統(tǒng)計年級和各班所占后20名人數在單元格B37輸入如下公式，按組合鍵確認?！?SUM(ISNUMBER(D$51:D$150)*(D$51:D$150<=B36)”在單元格B38輸入如下公式，按組合鍵確認?！?SUM(

13、$A$51:$A$150=1)*(D$51:D$150<=B$36)”在單元格區(qū)域B39:B41分別輸入如下公式，按組合鍵確認?！?SUM($A$51:$A$150=2)*(D$51:D$150<=B$36)”“=SUM($A$51:$A$150=3)*(D$51:D$150<=B$36)“=SUM($A$51:$A$150=4)*(D$51:D$150<=B$36)在單元格B42輸入公式：“=SUM(B38:B41)”，然后按鍵確認。即可驗證上述各班統(tǒng)計數之和是否等于年級統(tǒng)計數。選中單元格區(qū)域B37：B42，向右拖曳單元格B42右下角的

14、填充柄至E列完成公式填充，即可得到“數學“、”英語“和”總分“后20名中年級和各班所占人數。Step4統(tǒng)計中各班所占年級后20名的比例選中單元格區(qū)域G37:J37,輸入“100%”，然后按組合鍵確認。在單元格G38中輸入公式：“=B38/B$37”然后按鍵確認。選中單元格G38，向下拖曳單元格G38右下角的填充柄至G41完成公式填充。在單元格G42中輸入公式：“=SUM(G38:G41)”，然后按鍵確認。選中單元格區(qū)域G38:G42，向右拖曳單元格G42右下角的填充柄至J列完成公式填充。4.6.6在成績表中標示前20名和后20名的分數Step1設置條件格式選中單元格D51,單擊菜單“格式”-“

15、條件格式”，彈出“條件格式”對話框。在“條件格式”對話框中單擊“條件1”的下箭頭選擇“公式”,在其右側的文本框中輸入：“=D51>=B$25”。單擊“格式”按鈕彈由“單元格格式”對話框。在“單元格格式”對話框中切換到“字體”選項卡，在“字形”列表框中選擇“加粗加斜”。單擊“顏色”右側的下箭頭按鈕，在彈出的“顏色”調色板中選擇“黃色”，切換到“圖案”選項卡，在顏色框中選擇“紅色”。單擊“確定”按鈕返回“條件格式”對話框。單擊“添加”按鈕，單擊“條件2”的下箭頭選擇“公式”,在其右側的文本框中輸入：“=D51<=B$36”。單擊“格式”按鈕彈由“單元格格式”對話框。在“

16、單元格格式”對話框中切換到“字體”選項卡，在“字形”列表框中選擇“加粗”。單擊“顏色”右側的下箭頭按鈕，在彈出的“顏色”調色板中選擇“紅色”，切換到“圖案”選項卡，在顏色框中選擇“淺綠色”。單擊“確定”按鈕返回“條件格式”對話框。單擊“確定”按鈕，即可完成單元格D51中條件格式的設置。Step2傳遞條件格式選中單元格D51,單擊“格式刷”按鈕。選中數據區(qū)域D51:G150即可完成整個數據區(qū)域條件格式的設置。選中數據區(qū)域D51:G150，重新設置邊框（修復邊框）關鍵知識點講解：IS類函數本部分描述了用來檢驗數值或引用類型的九個工作表函數。這些函數，概括為IS類函數，可以檢驗數值的類型并根據參數取

17、值返回TRUE或FALSE。例如，如果數值為對空白單元格的引用，函數ISBLANK返回邏輯值TRUE，否則返回FALSE。使用格式：ISBLANK(value)ISERR(value)ISERROR(value)ISLOGICAL(value)ISNA(value)ISNONTEXT(value)ISNUMBER(value)ISREF(value)ISTEXT(value)Value為需要進行檢驗的數值。分別為：空白(空白單元格)、錯誤值、邏輯值、文本、數字、引用值或對于以上任意參數的名稱引用。案例公式解析：4.6.2Step3之的單元格B4中公式為：“=SUM(ISNUMBER(D$51:D$150)*(D$