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

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

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

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

4、格人數(shù)在單元格B4輸入如下公式，然后按組合鍵確認(rèn)?！?SUM(ISNUMBER(D$51:D$150)*(D$51:D$150>=B3)”在單元格B5輸入如下公式，按組合鍵確認(rèn)?！?SUM($A$51:$A$150=1)*(D$51:D$150>=B$3)”在單元格區(qū)域B6：B8分別輸入如下公式，按組合鍵確認(rèn)?！?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)“，然后按鍵確認(rèn)，即可驗(yàn)證上述各班統(tǒng)計(jì)數(shù)之和是否等于年級統(tǒng)計(jì)數(shù)。選中單元格區(qū)域B4：B9，向右拖曳單元格B9右下角的填充柄至E列即可完成“數(shù)學(xué)“、”英語“和”總分“的相應(yīng)統(tǒng)計(jì)工作。Step4統(tǒng)計(jì)及格率在單元格G4輸入公式“=B4/100“，然后按鍵確認(rèn)。選中單元格G4，向下拖曳單元格G4右下角的填充柄至單元格G8即可完成各班：“語文“學(xué)科及格率的統(tǒng)計(jì)。在單元格G9輸入公式：“=SUM(G5:G8)“，然后按鍵確認(rèn)。選中單元格區(qū)域G4：G9，向右拖曳單元格G9右下角的填充柄至J列即可完成“數(shù)學(xué)“、”英語“和”總分“及格率的統(tǒng)計(jì)

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

7、mp;gt;=B14)”在單元格B16輸入如下公式，然后按組合鍵確認(rèn)?！?SUM($A$51:$A$150=1)*(D$51:D$150>=B$14)”在單元格區(qū)域B17：B19分別輸入如下公式，然后按組合鍵確認(rèn)?！?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)“，然后按鍵確認(rèn)，即可驗(yàn)證上述

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

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

10、ISNUMBER(D$51:D$150)*(D$51:D$150>=B25)”在單元格B27輸入如下公式，然后按組合鍵確認(rèn)?！?SUM($A$51:$A$150=1)*(D$51:D$150>=B$25)”在單元格區(qū)域B28:B30分別輸入如下公式，按組合鍵確認(rèn)?！?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)”，然后按鍵確認(rèn)。即可驗(yàn)證上述各班統(tǒng)計(jì)數(shù)之和是否等于年級統(tǒng)計(jì)數(shù)。選中單元格區(qū)域B26：B31，向右拖曳單元格B31右下角的填充柄至E列完成公式填充，即可得到“數(shù)學(xué)“、”英語“和”總分“前20名中年級和各班所占人數(shù)。Step4統(tǒng)計(jì)中各班所占年級前20名的比例選中單元格區(qū)域G26:J26,輸入“100%”，然后按組合鍵確認(rèn)。選中單元格G27，向下拖曳單元格G27右下角的填充柄至G30完成公式填充。在單元格G31中輸入公式：“=SUM(G27:G30)”，然后按鍵確認(rèn)。選中單元格區(qū)域G27:G31，向右拖曳單元格G31右下角的填充柄至J列完成公式填充。4.6.5統(tǒng)計(jì)年

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

13、$A$51:$A$150=1)*(D$51:D$150<=B$36)”在單元格區(qū)域B39:B41分別輸入如下公式，按組合鍵確認(rèn)。“=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)”，然后按鍵確認(rèn)。即可驗(yàn)證上述各班統(tǒng)計(jì)數(shù)之和是否等于年級統(tǒng)計(jì)數(shù)。選中單元格區(qū)域B37：B42，向右拖曳單元格B42右下角的

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

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

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

17、值返回TRUE或FALSE。例如，如果數(shù)值為對空白單元格的引用，函數(shù)ISBLANK返回邏輯值TRUE，否則返回FALSE。使用格式：ISBLANK(value)ISERR(value)ISERROR(value)ISLOGICAL(value)ISNA(value)ISNONTEXT(value)ISNUMBER(value)ISREF(value)ISTEXT(value)Value為需要進(jìn)行檢驗(yàn)的數(shù)值。分別為：空白(空白單元格)、錯(cuò)誤值、邏輯值、文本、數(shù)字、引用值或?qū)τ谝陨先我鈪?shù)的名稱引用。案例公式解析：4.6.2Step3之的單元格B4中公式為：“=SUM(ISNUMBER(D$51:D$150)*(D$