Excel綜合應(yīng)用-成績(jī)表統(tǒng)計(jì)與分析

會(huì)計(jì)學(xué)1Excel綜合應(yīng)用—成績(jī)表統(tǒng)計(jì)與分析22023/1/18學(xué)習(xí)目標(biāo)熟練掌握統(tǒng)計(jì)函數(shù)COUNT、COUNTIF的使用方法熟練掌握邏輯判斷函數(shù)IF的使用方法掌握條件格式的設(shè)置方法熟練使用圖表的創(chuàng)建、修改第1頁/共35頁32023/1/18目錄7.1成績(jī)表統(tǒng)計(jì)案例分析7.2實(shí)現(xiàn)方法7.2.1用統(tǒng)計(jì)函數(shù)與公式制作“成績(jī)統(tǒng)計(jì)表”跨工作表的單元格引用統(tǒng)計(jì)函數(shù)COUNTA及COUNT的使用條件統(tǒng)計(jì)函數(shù)COUNTIF的使用公式計(jì)算7.2.2用IF函數(shù)與條件格式制作“各科等級(jí)表”邏輯判斷函數(shù)IF的使用條件格式7.2.3用圖表向?qū)е谱鞒煽?jī)統(tǒng)計(jì)圖使用“圖表向?qū)А眲?chuàng)建圖表修改圖表格式化圖表7.3案例總結(jié)7.4課后練習(xí)第2頁/共35頁42023/1/187.1成績(jī)表統(tǒng)計(jì)案例分析任務(wù)的提出班主任王老師要制作“成績(jī)統(tǒng)計(jì)表”、“成績(jī)等級(jí)表”及“成績(jī)統(tǒng)計(jì)圖。解決方案

第3頁/共35頁52023/1/18跨工作表的單元格引用打開“第7章素材”文件夾下的“統(tǒng)計(jì)表（素材）.xls”文件，選擇“成績(jī)統(tǒng)計(jì)表”工作表。從“各科成績(jī)表”中，將四門課程的“班級(jí)平均分”、“班級(jí)最高分”和“班級(jí)最低分”的數(shù)據(jù)引用到如右圖所示的“成績(jī)統(tǒng)計(jì)表”中的相應(yīng)單元格中。第4頁/共35頁62023/1/18統(tǒng)計(jì)函數(shù)COUNTA及COUNT的使用

將“各科成績(jī)表”中各門課程的“應(yīng)考人數(shù)”和“參考人數(shù)”的統(tǒng)計(jì)結(jié)果放置到“成績(jī)統(tǒng)計(jì)表”中的相應(yīng)單元格中。在“插入函數(shù)”對(duì)話框中，選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”列表框中，選擇“COUNTA”函數(shù)，單擊“確定”按鈕。打開“函數(shù)參數(shù)”對(duì)話框。進(jìn)行如下圖所示的設(shè)置?！癈OUNT”函數(shù)的設(shè)置類似（略）。第5頁/共35頁72023/1/18條件統(tǒng)計(jì)函數(shù)COUNTIF的功能COUNTIF函數(shù)的功能：統(tǒng)計(jì)指定區(qū)域內(nèi)滿足給定條件的單元格數(shù)目。語法格式：COUNTIF(range,criteria)其中：Range指定單元格區(qū)域，Criteria表示指定的條件表達(dá)式。條件表達(dá)式的形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為60、"60"、">=90"或"缺考"等。第6頁/共35頁82023/1/18條件統(tǒng)計(jì)函數(shù)COUNTIF的使用用COUNTIF函數(shù)，將“各科成績(jī)表”中各門課程的缺考人數(shù)以及各分?jǐn)?shù)段人數(shù)的統(tǒng)計(jì)結(jié)果放置到“成績(jī)統(tǒng)計(jì)表”中的相應(yīng)單元格。在“函數(shù)參數(shù)”對(duì)話框中，進(jìn)行如下圖所示的設(shè)置：編輯欄中的函數(shù)為=COUNTIF(各科成績(jī)表!D2:D38,"缺考")。第7頁/共35頁92023/1/18各分?jǐn)?shù)段人數(shù)的統(tǒng)計(jì)條件對(duì)應(yīng)函數(shù)90-100(人)=COUNTIF(各科成績(jī)表!D2:D38,">=90"

80-89(人)=COUNTIF(各科成績(jī)表!D2:D38,">=80")-B9

70-79(人)=COUNTIF(各科成績(jī)表!D2:D38,">=70")-B9-B10

60-69(人)=COUNTIF(各科成績(jī)表!D2:D38,">=60")-B9-B10-B11

59以下(人)=COUNTIF(各科成績(jī)表!D2:D38,"<60")

第8頁/共35頁102023/1/18公式計(jì)算在“成績(jī)統(tǒng)計(jì)表”中，計(jì)算各門課程的及格率和優(yōu)秀率。計(jì)算及格率：COUNTIF(各科成績(jī)表!D$2:D$38,">=60")/COUNT(各科成績(jī)表!D$2:D$38)

或：1-B13/B7計(jì)算優(yōu)秀率：=COUNTIF(各科成績(jī)表!D$2:D$38,">=90")/COUNT(各科成績(jī)表!D$2:D$38)或：B9/B7第9頁/共35頁112023/1/18思考題（一）如何引用同一工作簿中不同工作表的數(shù)據(jù)？引用單元格的數(shù)據(jù)與復(fù)制粘貼數(shù)據(jù)有何區(qū)別？統(tǒng)計(jì)函數(shù)COUNT與COUNTA的區(qū)別是什么？條件統(tǒng)計(jì)函數(shù)COUNTIF的應(yīng)用場(chǎng)合是什么？其中的兩個(gè)參數(shù)各代表什么？在“各科成績(jī)表”工作表中分別統(tǒng)計(jì)出男、女生人數(shù)應(yīng)該用什么函數(shù)？試寫出函數(shù)的表達(dá)式。第10頁/共35頁122023/1/18單元格數(shù)據(jù)的刪除與清除將“各科成績(jī)表”工作表復(fù)制一份，并將復(fù)制后的工作表更名為“各科等級(jí)表”。在“各科等級(jí)表”中，清除“大學(xué)英語”、“計(jì)算機(jī)應(yīng)用”、“高等數(shù)學(xué)”及“應(yīng)用文寫作”列中的分?jǐn)?shù)內(nèi)容，并清除“總分”、“名次”列的所有屬性；刪除“班級(jí)平均分”、“班級(jí)最高分”、“班級(jí)最低分”所在單元格區(qū)域。第11頁/共35頁132023/1/18邏輯判斷函數(shù)IF的功能IF函數(shù)的功能是：判斷給出的條件是否滿足，如果滿足返回一個(gè)值，如果不滿足則返回另一個(gè)值。語法格式：IF(logical_test,value_if_true,value_if_false)共包括三個(gè)參數(shù)，其中：Logical_test邏輯判斷表達(dá)式；value_if_true表達(dá)式為真時(shí)返回的值；value_if_false表達(dá)式為假時(shí)返回的值。IF函數(shù)的邏輯判斷方法第12頁/共35頁142023/1/18邏輯判斷函數(shù)IF的使用利用If函數(shù)對(duì)“各科成績(jī)表”中的“應(yīng)用文寫作”成績(jī)?cè)?0分以上的，在“各科等級(jí)表”中“應(yīng)用文寫作”的對(duì)應(yīng)位置設(shè)置為“及格”，否則為“不及格”。選擇目標(biāo)單元格G2，在“函數(shù)參數(shù)”對(duì)話框中進(jìn)行如下圖所示的設(shè)置。第13頁/共35頁152023/1/18邏輯判斷函數(shù)IF的使用（續(xù)）按下表所示的分?jǐn)?shù)與等級(jí)的對(duì)應(yīng)關(guān)系，利用If嵌套函數(shù)對(duì)“各科成績(jī)表”中的“大學(xué)英語”、“計(jì)算機(jī)應(yīng)用”、“高等數(shù)學(xué)”三門課程的分?jǐn)?shù)，在“各科等級(jí)表”中的對(duì)應(yīng)科目中進(jìn)行相應(yīng)的等級(jí)設(shè)置。分?jǐn)?shù)等級(jí)缺考缺考90<=分?jǐn)?shù)A80<=分?jǐn)?shù)<90B70<=分?jǐn)?shù)<80C60<=分?jǐn)?shù)<70D分?jǐn)?shù)<60E第14頁/共35頁162023/1/18邏輯判斷函數(shù)IF的使用（續(xù)）按下表所示的分?jǐn)?shù)與等級(jí)的對(duì)應(yīng)關(guān)系，利用If嵌套函數(shù)對(duì)“各科成績(jī)表”中的“大學(xué)英語”、“計(jì)算機(jī)應(yīng)用”、“高等數(shù)學(xué)”三門課程的分?jǐn)?shù)，在“各科等級(jí)表”中的對(duì)應(yīng)科目中進(jìn)行相應(yīng)的等級(jí)設(shè)置。第15頁/共35頁172023/1/18條件格式在“各科等級(jí)表”中，利用條件格式將所有“不及格”或所有等級(jí)為“E”的單元格設(shè)置成“黃色底紋紅色加粗字體”；將所有“缺考”的單元格設(shè)置為“淺青綠底紋梅紅色加粗字體”。在菜單欄中選擇“格式”→“條件格式”命令，打開“條件格式”對(duì)話框，進(jìn)行如下圖所示的設(shè)置。第16頁/共35頁182023/1/18條件格式（續(xù)）在“各科成績(jī)表”中，將“標(biāo)題”行及“班級(jí)平均分”行之間（不包括這兩行）的偶數(shù)行數(shù)據(jù)區(qū)域設(shè)置為“冰藍(lán)色底紋深藍(lán)色字體”。在菜單欄中選擇“格式”→“條件格式”命令，打開“條件格式”對(duì)話框，進(jìn)行如下圖所示的設(shè)置。第17頁/共35頁192023/1/18思考題（二）對(duì)單元格數(shù)據(jù)的清除和刪除操作有何區(qū)別？IF函數(shù)有幾個(gè)參數(shù)，每個(gè)參數(shù)的意義是什么？IF函數(shù)最多可以有幾層嵌套？如何設(shè)置條件格式？第18頁/共35頁202023/1/18問題分析在下圖所示的單元格中，有“分?jǐn)?shù)”、“等級(jí)”兩列數(shù)據(jù)，其中“等級(jí)”數(shù)據(jù)是根據(jù)“分?jǐn)?shù)”按以下條件計(jì)算所得：

條件

分?jǐn)?shù) 等級(jí) 分?jǐn)?shù)>=90 優(yōu)秀

90>分?jǐn)?shù)>=80 良好

80>分?jǐn)?shù)>=70 中等

70>分?jǐn)?shù)>=60 及格 分?jǐn)?shù)<60 不及格請(qǐng)分析以下表達(dá)式能否根據(jù)給定條件求出正確的結(jié)果？如果有錯(cuò)，請(qǐng)說明出錯(cuò)原因和改正方法。=if(A2>=90,優(yōu)秀,if(90>A2>=80,良好,if(80>A2>=70,中等,if(70>A2>=60,及格,if(A2<60,不及格,false)))))第19頁/共35頁212023/1/18制作各科成績(jī)統(tǒng)計(jì)圖圖表比數(shù)據(jù)更易于表達(dá)數(shù)據(jù)之間的關(guān)系以及數(shù)據(jù)變化的趨勢(shì)。“成績(jī)統(tǒng)計(jì)圖”可以直觀地表現(xiàn)各門課程各分?jǐn)?shù)段人數(shù)。第20頁/共35頁222023/1/18使用“圖表向?qū)А眲?chuàng)建圖表在“成績(jī)統(tǒng)計(jì)表”中，根據(jù)各分?jǐn)?shù)段人數(shù)及缺考人數(shù)制作圖表。要求如下：圖表類型為“簇狀柱形圖”；數(shù)據(jù)系列產(chǎn)生在“列”；圖表標(biāo)題為“成績(jī)統(tǒng)計(jì)圖”；分類（X）軸為“等級(jí)”；數(shù)值（Y）軸為“人數(shù)”；將圖表“作為其中的對(duì)象插入”到“成績(jī)統(tǒng)計(jì)表”中。第21頁/共35頁232023/1/18選擇圖表類型在菜單欄中選擇“插入”→“圖表”命令，打開“圖表向?qū)?4步驟之1-圖表類型”對(duì)話框，進(jìn)行如右圖所示的設(shè)置。單擊“下一步”按鈕第22頁/共35頁242023/1/18選擇源數(shù)據(jù)按住Ctrl鍵的同時(shí)，分別在當(dāng)前工作表中選擇不連續(xù)的兩個(gè)區(qū)域，如下圖所示，并在“圖表向?qū)?4步驟之2-圖表源數(shù)據(jù)”對(duì)話框中，顯示相應(yīng)的設(shè)置。第23頁/共35頁252023/1/18設(shè)置圖表選項(xiàng)單擊“下一步”按鈕，打開“圖表向?qū)?4步驟之3-圖表選項(xiàng)”對(duì)話框，并進(jìn)行如下圖所示的設(shè)置。第24頁/共35頁262023/1/18選擇圖表位置“下一步”按鈕，打開“圖表向?qū)?4步驟之4-圖表位置”對(duì)話框，進(jìn)行如下圖所示的設(shè)置。第25頁/共35頁272023/1/18修改圖表在圖表的制作過程中四步中的任何一步出錯(cuò)，都不必從頭開始，只需單擊圖表，使圖表處于激活狀態(tài)，從“圖表”菜單中，選擇對(duì)應(yīng)的命令即可；也可單擊圖表后，單擊鼠標(biāo)右鍵，從快捷菜單中，選擇對(duì)應(yīng)的命令。第26頁/共35頁282023/1/18修改圖表要求在“成績(jī)統(tǒng)計(jì)表”工作表中，對(duì)圖表進(jìn)行如下修改：將圖表類型改為“三維簇狀柱形圖”；數(shù)據(jù)系列產(chǎn)生在“行”，并從圖表中刪除缺考人數(shù)；在圖表中“顯示數(shù)據(jù)表”；將圖表“作為新工作表插入”，并將新工作表命名為“成績(jī)統(tǒng)計(jì)圖”。操作步驟略第27頁/共35頁292023/1/18圖表的組成第28頁/共35頁302023/1/18格式化圖表在“成績(jī)統(tǒng)計(jì)圖”工作表中，對(duì)圖表的外觀進(jìn)行如下修飾：將圖表標(biāo)題設(shè)置為“幼圓、22號(hào)、藍(lán)色、加粗”；將圖表區(qū)的填充效果設(shè)置為“羊皮紙”；將背景墻的填充效果設(shè)置為“水滴”；為圖例添加“陰影邊框”。第29頁/共35頁312023/1/18設(shè)置圖表區(qū)的填充效果在“圖表”工具欄上的“圖表對(duì)象”下拉列表中，選擇“圖表區(qū)”，單擊“圖表”工具欄上的“圖表區(qū)格式”按鈕，打開“圖表區(qū)格式”對(duì)話框，在“圖案”選項(xiàng)卡中，單擊“填充效果”按鈕，打開“填充效果”對(duì)話框，在“漸變”選項(xiàng)卡中，選擇“預(yù)設(shè)”單選按鈕，在“預(yù)設(shè)顏色”下拉列表中，選擇“羊皮紙”選項(xiàng)，在“底紋樣式”欄中，選擇“中心輻射”單選項(xiàng)，單擊“確定”按鈕。“填充效果”對(duì)話框的設(shè)置，如右圖所示。第30頁/共35頁322023/1/18設(shè)置背景墻的填充效果鼠標(biāo)指向“背景墻”，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“背景墻格式”命令，打開“背景墻格式”對(duì)話框，在“圖案”選項(xiàng)卡中，單擊“填充效果”按鈕，打開“填充效果”對(duì)話框。選擇“紋理”選項(xiàng)卡，在“紋理”列表框中，選擇“水滴”，如右圖所示，單擊“確定”按鈕。第31頁/共35頁332023/1/18思考題（三）選擇圖表的數(shù)據(jù)源時(shí)，如何選擇不連續(xù)的數(shù)據(jù)區(qū)域？如何修改圖表？對(duì)已建立完成的圖表，應(yīng)如何添加“圖表標(biāo)題”？對(duì)圖表進(jìn)行格式化時(shí)，應(yīng)該如何操作？將“圖表區(qū)”的填充效果設(shè)置為“雨后初晴”，應(yīng)如何操作？第32頁/共35頁342023/1/18案例總結(jié)本章主要介紹了：統(tǒng)計(jì)函數(shù)COUNT、COUNTA、COUNTIF；在使用COUNTIF函數(shù)時(shí)應(yīng)該注意，當(dāng)公式需要復(fù)制時(shí)，如果參數(shù)“Range”的引用區(qū)域固定不變，通常應(yīng)使用絕對(duì)引用或區(qū)域命名方式實(shí)現(xiàn)；如果參數(shù)“Criteria”是表達(dá)式或字符串，必須在兩邊加上西文雙引號(hào)。邏輯判斷函數(shù)IF以及嵌套函數(shù)的使用；當(dāng)邏輯判斷給出的條件多于兩個(gè)時(shí)，通常采用IF函數(shù)的嵌套。在使用IF函數(shù)時(shí)，應(yīng)注意函數(shù)多層嵌套時(shí)的括號(hào)匹配，并