學(xué)生成績綜合統(tǒng)計表

4.6學(xué)生成績綜合統(tǒng)計表（一）案例背景期末考試后，除了要統(tǒng)計各個分?jǐn)?shù)段人數(shù)分布情況外（前面章節(jié)已經(jīng)介紹了多種統(tǒng)計分?jǐn)?shù)段的方法）可能還要統(tǒng)計年級和各班的及格人數(shù)與及格率，優(yōu)秀人數(shù)與優(yōu)秀率，以及各班里前、后若干名（如年級前20名和后20名）的人數(shù)和比例等。本案例以某中學(xué)高一年級4個教學(xué)班100名學(xué)生的期末考試的3科成績和總分為樣本，統(tǒng)計全年級4個班級學(xué)生的期末考試及格人數(shù)與及格率，優(yōu)秀人數(shù)與優(yōu)秀率，以及各班里年級前20名和后20名的人數(shù)和比例，以便進(jìn)行綜合分析。同時將前20名和后20名學(xué)生的成績在統(tǒng)計表中標(biāo)示出來，以使得前、后20名的成績能夠在成績表突出顯示。要實(shí)現(xiàn)本案例中的功能，學(xué)員應(yīng)該掌握以下EXCEL技術(shù)點(diǎn)●基礎(chǔ)知識條件格式●函數(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)計表.xls”，然后將工作表重命名為“統(tǒng)計表”，刪除多余工作表。Step2輸入原始成績①在單元格A49輸入表格標(biāo)題：“高一年級期末考試成績統(tǒng)計表”，選擇你好單元格區(qū)域A49:G49，單擊“合并及居中”按鈕，字體、字型、字號設(shè)置為黑體加粗16號。②在單元格區(qū)域A50：G50輸入字段名：“班級”，“學(xué)號”，“姓名”，“語文”，“數(shù)學(xué)“，“英語”和“總分“。③在單元格區(qū)域A51：F150輸入全年級4個班100名學(xué)生的“語文”，“數(shù)學(xué)“，“英語”成績④在單元格G51輸入公式：“=SUM(D51:F51)”，然后按鍵確認(rèn)，計算出第一個學(xué)生的“總分”。⑤選中單元格G51，雙擊單元格G51右下角的填充柄即可完成其余學(xué)生“總分”的統(tǒng)計工作。⑥選中單元格區(qū)域A50：G150，按圖2樣式設(shè)置邊框，添加底紋，文本居中顯示。4.6.2統(tǒng)計及格人數(shù)與及格率Step1創(chuàng)建統(tǒng)計表框架在單元格區(qū)域A1:J9按圖3樣式輸入“高一年級各班及格人數(shù)與及格率統(tǒng)計表”框架信息。Step2輸入統(tǒng)計分?jǐn)?shù)線①選中單元格區(qū)域B3:D3，輸入“60”，按組合鍵確認(rèn)。②在單元格E3中輸入“180”。Step3統(tǒng)計及格人數(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>=B$3))”④在單元格B9中輸入公式：“=SUM(B5:B8)“，然后按鍵確認(rèn)，即可驗(yàn)證上述各班統(tǒng)計數(shù)之和是否等于年級統(tǒng)計數(shù)。⑤選中單元格區(qū)域B4：B9，向右拖曳單元格B9右下角的填充柄至E列即可完成“數(shù)學(xué)“、”英語“和”總分“的相應(yīng)統(tǒng)計工作。Step4統(tǒng)計及格率①在單元格G4輸入公式“=B4/100“，然后按鍵確認(rèn)。②選中單元格G4，向下拖曳單元格G4右下角的填充柄至單元格G8即可完成各班：“語文“學(xué)科及格率的統(tǒng)計。③在單元格G9輸入公式：“=SUM(G5:G8)“，然后按鍵確認(rèn)。④選中單元格區(qū)域G4：G9，向右拖曳單元格G9右下角的填充柄至J列即可完成“數(shù)學(xué)“、”英語“和”總分“及格率的統(tǒng)計和驗(yàn)證工作。⑤選中單元格區(qū)域G4：J9，單擊菜單“格式“→”單元格“，彈出”單元格格式“對話框，切換到數(shù)字選項(xiàng)卡，在”分類“選項(xiàng)框中選擇”百分比“，然后單擊”確定“按鈕，即可將該區(qū)域數(shù)值設(shè)置成百分比形式顯示。4.6.3統(tǒng)計優(yōu)秀人數(shù)與優(yōu)秀率Step1創(chuàng)建統(tǒng)計表框架在單元格區(qū)域A12:J20按圖3樣式輸入“高一年級各班優(yōu)秀人數(shù)與優(yōu)秀率統(tǒng)計表”框架信息。Step2輸入統(tǒng)計分?jǐn)?shù)線⑨單擊“確定”按鈕，即可完成單元格D51中條件格式的設(shè)置。Step2傳遞條件格式①選中單元格D51,單擊“格式刷”按鈕。②選中數(shù)據(jù)區(qū)域D51:G150即可完成整個數(shù)據(jù)區(qū)域條件格式的設(shè)置。③選中數(shù)據(jù)區(qū)域D51:G150，重新設(shè)置邊框（修復(fù)邊框）。關(guān)鍵知識點(diǎn)講解：IS類函數(shù)本部分描述了用來檢驗(yàn)數(shù)值或引用類型的九個工作表函數(shù)。這些函數(shù)，概括為IS類函數(shù)，可以檢驗(yàn)數(shù)值的類型并根據(jù)參數(shù)取值返回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ù)值。分別為：空白（空白單元格）、錯誤值、邏輯值、文本、數(shù)字、引用值或?qū)τ谝陨先我鈪?shù)的名稱引用。案例公式解析：4.6.2Step3之①的單元格B4中公式為：“=SUM(ISNUMBER(D$51:D$150)*(D$51:D$150>=B3))”其中ISNUMBER(D$51:D$150)含有100個元素為一維數(shù)組，ISNUMBER判斷單元格區(qū)域D$51:D$150是否是數(shù)值，因?yàn)榭赡軙袑W(xué)生沒有參加該課程考試，這時會在相應(yīng)位置出現(xiàn)“缺考”或類似字樣文本，因?yàn)閱卧?/p>