第5章Excel綜合應(yīng)用——成績(jī)表統(tǒng)計(jì)與分析講述

1、學(xué)習(xí)目標(biāo)熟練掌握統(tǒng)計(jì)函數(shù)COUNT、COUNTIF及COUNTIFS的使用方法熟練掌握邏輯判斷函數(shù)IF的使用方法掌握條件格式的設(shè)置方法熟練使用圖表的創(chuàng)建、修改及格式化2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析28.1 成績(jī)表統(tǒng)計(jì)案例分析 8.2 實(shí)現(xiàn)方法8.2.1 用統(tǒng)計(jì)函數(shù)與公式制作“成績(jī)統(tǒng)計(jì)表”跨工作表的單元格引用 統(tǒng)計(jì)函數(shù)COUNTA及COUNT的使用 條件統(tǒng)計(jì)函數(shù)COUNTIF與COUNTIFS的使用 公式計(jì)算 8.2.2 用IF函數(shù)與條件格式制作“各科等級(jí)表”單元格數(shù)據(jù)的清除與刪除邏輯判斷函數(shù)IF的使用 條件格式 8.2.3用圖表向?qū)е谱鞒煽?jī)統(tǒng)計(jì)圖 創(chuàng)建圖表 修改

2、圖表 格式化圖表 8.3 案例總結(jié)8.4 課后練習(xí)2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析38.1成績(jī)表統(tǒng)計(jì)案例分析 任務(wù)的提出班主任王老師要制作 “成績(jī)統(tǒng)計(jì)表”、“成績(jī)等級(jí)表” 及“成績(jī)統(tǒng)計(jì)圖。解決方案2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析4跨工作表的單元格引用 打開(kāi)“統(tǒng)計(jì)表（素材）.xlsx”文件，選擇“成績(jī)統(tǒng)計(jì)表”工作表。 從“各科成績(jī)表”中，將四門(mén)課程的“班級(jí)平均分”、“班級(jí)最高分”和“班級(jí)最低分”的數(shù)據(jù)引用到“成績(jī)統(tǒng)計(jì)表”中的相應(yīng)單元格中 。2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析5統(tǒng)計(jì)函數(shù)COUNT及COUNTA的

3、使用 將“各科成績(jī)表”中各門(mén)課程的“參考人數(shù)”和“應(yīng)考人數(shù)”的統(tǒng)計(jì)結(jié)果放置到“成績(jī)統(tǒng)計(jì)表”中的相應(yīng)單元格中。 在“開(kāi)始”選項(xiàng)卡的“編輯”組中單擊“自動(dòng)求和”按鈕 旁邊的下拉箭頭，在彈出的列表中選擇“計(jì)數(shù)”選項(xiàng)?？捎?jì)算出“參考人數(shù)”。 在“公式”選項(xiàng)卡的“函數(shù)庫(kù)”組中單擊“其他函數(shù)”按鈕 ，在下拉列表中選擇“統(tǒng)計(jì)”選項(xiàng)，在彈出的下拉列表框中選擇“COUNTA”。可計(jì)算出“應(yīng)考人數(shù)”。2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析6條件統(tǒng)計(jì)函數(shù)COUNTIF的功能 COUNTIF函數(shù)的功能：統(tǒng)計(jì)指定區(qū)域內(nèi)滿(mǎn)足給定條件的單元格數(shù)目。 語(yǔ)法格式：COUNTIF(range,criter

4、ia) 其中：Range 指定單元格區(qū)域，Criteria表示指定的條件表達(dá)式。 條件表達(dá)式的形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為 60、60、=90 或 缺考等。2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析7條件統(tǒng)計(jì)函數(shù)COUNTIF的使用 在“成績(jī)統(tǒng)計(jì)表”的相應(yīng)單元格中，用COUNTIF函數(shù)統(tǒng)計(jì)“各科成績(jī)表”中4門(mén)課程的“缺考”人數(shù)、 “90-100”以及“小于60”分?jǐn)?shù)段的人數(shù)。 在“函數(shù)參數(shù)”對(duì)話(huà)框中，進(jìn)行如下圖所示的設(shè)置：編輯欄中的函數(shù)為 =COUNTIF(各科成績(jī)表!D2:D38,缺考)。 用相同方法可以統(tǒng)計(jì)出“90-100”以及“小于60”分?jǐn)?shù)段的

5、人數(shù)。2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析8條件統(tǒng)計(jì)函數(shù)COUNTIFS的功能 COUNTIFS函數(shù)的功能是：將條件應(yīng)用于跨多個(gè)區(qū)域的單元格，并計(jì)算符合所有條件的次數(shù)。 語(yǔ)法格式：COUNTIFS (Criteria_range1, Criteria1, Criteria_range2, Criteria2) 其中： Criteria_range1 必需。在其中計(jì)算關(guān)聯(lián)條件的第一個(gè)區(qū)域， Criteria1必需。條件表達(dá)式； Criteria_range2, Criteria2, .可選。附加的區(qū)域及其關(guān)聯(lián)條件。最多允許 127 個(gè)區(qū)域/條件對(duì)。2022-3-17第8

6、章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析9條件統(tǒng)計(jì)函數(shù)COUNTIFS的使用 在“成績(jī)統(tǒng)計(jì)表”的相應(yīng)單元格中，用COUNTIFS函數(shù)統(tǒng)計(jì)“各科成績(jī)表”中4門(mén)課程的“80-89”、“70-79”以及“60-69”分?jǐn)?shù)段的人數(shù)。 在打開(kāi)的“函數(shù)參數(shù)”對(duì)話(huà)框中填寫(xiě)上兩組單元格范圍和條件，如下圖所示，單擊“確定”按鈕。 用相同方法可以統(tǒng)計(jì)出“70-79”以及“60-69”分?jǐn)?shù)段的人數(shù)。2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析10公式計(jì)算 在“成績(jī)統(tǒng)計(jì)表”中，計(jì)算各門(mén)課程的優(yōu)秀率和及格率 。 計(jì)算優(yōu)秀率 ：=COUNTIF(各科成績(jī)表!D$2:D$38,=90)/COUNT(各科成

7、績(jī)表!D$2:D$38) 或：=B9/B6 計(jì)算及格率 ：=COUNTIF(各科成績(jī)表!D$2:D$38,=60)/COUNT(各科成績(jī)表!D$2:D$38) 或：=1-B13/B6 2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析11思考題（一） 如何引用同一工作簿中不同工作表的數(shù)據(jù)？ 引用單元格的數(shù)據(jù)與復(fù)制粘貼數(shù)據(jù)有何區(qū)別？ 統(tǒng)計(jì)函數(shù)COUNT與COUNTA的區(qū)別是什么？ 條件統(tǒng)計(jì)函數(shù)COUNTIF和COUNTIFS的應(yīng)用場(chǎng)合是分別什么？ 在“各科成績(jī)表”工作表中分別統(tǒng)計(jì)出男、女生人數(shù)應(yīng)該用什么函數(shù)？試寫(xiě)出函數(shù)的表達(dá)式。2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)

8、與分析12單元格數(shù)據(jù)的刪除與清除 復(fù)制“各科成績(jī)表”工作表，并將復(fù)制后的工作表更名為“各科等級(jí)表”。在“各科等級(jí)表”中，清除4門(mén)課程列中的分?jǐn)?shù)內(nèi)容；清除“總分”列的所有屬性；刪除分?jǐn)?shù)統(tǒng)計(jì)所在單元格區(qū)域。 按Delete鍵，只清除選定單元格的內(nèi)容，而保留了單元格的格式。 在“開(kāi)始”選項(xiàng)卡的“編輯”組中單擊“清除”按鈕 ，在彈出的列表中選擇“全部清除”選項(xiàng)。此時(shí)單元格中的內(nèi)容和格式全部被清除。 在“開(kāi)始”選項(xiàng)卡的“單元格”組中單擊“刪除”按鈕 ，這時(shí)單元格區(qū)域的內(nèi)容會(huì)由其他內(nèi)容替代。2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析13邏輯判斷函數(shù)IF的功能 IF函數(shù)的功能是： 判斷給

9、出的條件是否滿(mǎn)足，如果滿(mǎn)足返回一個(gè)值，如果不滿(mǎn)足則返回另一個(gè)值。 語(yǔ)法格式： IF(logical_test,value_if_true,value_if_false) 共包括三個(gè)參數(shù)，其中： Logical_test 邏輯判斷表達(dá)式； value_if_true 表達(dá)式為真時(shí)返回的值； value_if_false 表達(dá)式為假時(shí)返回的值。2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析14邏輯判斷函數(shù)IF的使用 在“各科等級(jí)表”的“應(yīng)用文寫(xiě)作”列中，利用IF函數(shù)引用“各科成績(jī)表”中“應(yīng)用文寫(xiě)作”列的對(duì)應(yīng)分?jǐn)?shù)進(jìn)行邏輯判斷，如果分?jǐn)?shù)在60分以上的返回“及格”，否則返回“不及格”。

10、選擇目標(biāo)單元格G2 ，在“函數(shù)參數(shù)”對(duì)話(huà)框中進(jìn)行如下圖所示的設(shè)置。 2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析15邏輯判斷函數(shù)IF的使用（二） 在“各科等級(jí)表”的“應(yīng)用文寫(xiě)作”列中，利用IF函數(shù)再添加一個(gè)條件：對(duì)“各科成績(jī)表”的“應(yīng)用文寫(xiě)作”列中成績(jī)?yōu)椤叭笨肌钡目忌?，在?duì)應(yīng)位置仍設(shè)置為“缺考”。 在“各科等級(jí)表”中選擇目標(biāo)單元格G2，選擇除“=”以外的其他內(nèi)容，按快捷鍵Ctrl+X。 在對(duì)話(huà)框中進(jìn)行如下圖所示的設(shè)置。其中在“Value_if_false”編輯框中，將插入點(diǎn)定位后，按快捷鍵Ctrl+V。2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析16邏輯判斷

11、函數(shù)IF的使用（三）分?jǐn)?shù)等級(jí)缺考缺考分?jǐn)?shù)=90A90分?jǐn)?shù)=80B80分?jǐn)?shù)=70C70分?jǐn)?shù)=60D分?jǐn)?shù)60E2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析17 在“各科等級(jí)表”的“大學(xué)英語(yǔ)”、“計(jì)算機(jī)應(yīng)用”、“高等數(shù)學(xué)”列中，利用IF嵌套函數(shù)，引用“各科成績(jī)表”中對(duì)應(yīng)的3門(mén)課程的分?jǐn)?shù)，按下表中分?jǐn)?shù)與成績(jī)等級(jí)的對(duì)應(yīng)關(guān)系，在相應(yīng)的科目中進(jìn)行成績(jī)等級(jí)設(shè)置。邏輯判斷函數(shù)IF的使用（四）（1）在目標(biāo)單元格D2中輸入“=”。（2）單擊“名稱(chēng)框”處的“IF”，打開(kāi)“函數(shù)參數(shù)”對(duì)話(huà)框。（3）在“函數(shù)參數(shù)”對(duì)話(huà)框中進(jìn)行如下圖所示的設(shè)置。并將插入點(diǎn)定位在第3個(gè)參數(shù)“Value_if_false”編輯

12、框中。 （4）再次單擊“名稱(chēng)框”處的“IF”。第2次打開(kāi)“函數(shù)參數(shù)”對(duì)話(huà)框，在“Logical_test”處輸入“各科成績(jī)表!D2=90 優(yōu)秀90分?jǐn)?shù)=80良好80分?jǐn)?shù)=70中等70分?jǐn)?shù)=60及格分?jǐn)?shù)=90,優(yōu)秀,if(90A2=80,良好,if(80A2=70,中等,if(70A2=60,及格,if(A260,不及格,false)2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析23制作各科成績(jī)統(tǒng)計(jì)圖 圖表比數(shù)據(jù)更易于表達(dá)數(shù)據(jù)之間的關(guān)系以及數(shù)據(jù)變化的趨勢(shì)。 “成績(jī)統(tǒng)計(jì)圖”可以直觀地表現(xiàn)各門(mén)課程各分?jǐn)?shù)段人數(shù)。2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析24創(chuàng)建圖表

13、2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析25 在“成績(jī)統(tǒng)計(jì)表”中，根據(jù)各分?jǐn)?shù)段人數(shù)及缺考人數(shù)制作圖表。要求如下：圖表類(lèi)型為“簇狀柱形圖” 。 選擇目標(biāo)單元格區(qū)域A8:E13。 在“插入”選項(xiàng)卡的“圖表”組中單擊“柱形圖”按鈕 ，在彈出的下拉列表中選擇“二維柱形圖”區(qū)域的“簇狀柱形圖”選項(xiàng)。生成的圖表如右圖所示。這里有錯(cuò)誤選擇數(shù)據(jù)源2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析26 選中圖表，選擇“圖表工具-設(shè)計(jì)”選項(xiàng)卡，在“數(shù)據(jù)”組中單擊“選擇數(shù)據(jù)”按鈕 ，彈出“選擇數(shù)據(jù)源”對(duì)話(huà)框。 在保留原選定區(qū)域的基礎(chǔ)上，按住Ctrl鍵不放，再選擇“A2:E2”區(qū)域，

14、如下圖所示?？梢钥吹?，工作表中選定區(qū)域出現(xiàn)了兩個(gè)閃動(dòng)的虛線(xiàn)框，虛線(xiàn)框的單元格以絕對(duì)地址的形式出現(xiàn)在“圖表數(shù)據(jù)區(qū)域”文本框中，新增的標(biāo)題區(qū)域“A2:E2”，已反映在圖例項(xiàng)中，替換了“系列1”、“系列4”。單擊“確定”按鈕后，圖表也同步更新。設(shè)置圖表布局與圖表樣式 2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析27 設(shè)置圖表布局為“布局9”、圖表樣式為“樣式26”。 在“圖表工具-設(shè)計(jì)”選項(xiàng)卡的“圖表布局”組中單擊列表框旁的“其他”按鈕 ，彈出“圖表布局”列表框，選擇“布局9”。 在“圖表工具-設(shè)計(jì)”選項(xiàng)卡的“圖表樣式”組中單擊列表框旁的“其他”按鈕 ，彈出“圖表樣式”列表框，選擇

15、“樣式26”。設(shè)置效果如下圖所示。修改圖表（一）2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析28在“成績(jī)統(tǒng)計(jì)表”工作表中，為圖表添加“模擬運(yùn)算表”、將“圖例”位置移動(dòng)到頂部顯示。 選中圖表，選擇“圖表工具-布局”選項(xiàng)卡，在“標(biāo)簽”組中單擊“模擬運(yùn)算表”按鈕 ，在彈出的下拉列表中選擇“顯示模擬運(yùn)算表和圖例項(xiàng)標(biāo)示”選項(xiàng)。 在“標(biāo)簽”組中單擊“圖例”按鈕 ，在彈出的下拉列表中選擇“在頂部顯示圖例”選項(xiàng)。設(shè)置效果如右圖所示。修改圖表 （二）在“成績(jī)統(tǒng)計(jì)表”工作表中，將圖表類(lèi)型改為“簇狀圓柱圖”、“切換行/列”，從圖表中刪除“缺考人數(shù)”、將圖表移動(dòng)到工作簿的新工作表中，并將新工作表命名

16、為“成績(jī)統(tǒng)計(jì)圖”。 單擊圖表。選擇“圖表工具-設(shè)計(jì)”選項(xiàng)卡。 在“類(lèi)型”組中單擊“更改圖表類(lèi)型”按鈕 ，打開(kāi)“更改圖表類(lèi)型”對(duì)話(huà)框，在“柱形圖”區(qū)域中選擇“簇狀圓柱圖”選項(xiàng)。 在“數(shù)據(jù)”組中單擊“選擇數(shù)據(jù)”按鈕 ，打開(kāi)“選擇數(shù)據(jù)源”對(duì)話(huà)框，單擊“切換行/列”按鈕，圖例項(xiàng)變?yōu)樾袠?biāo)題。 在“圖例項(xiàng)（系列）”欄中，選擇“缺考人數(shù)”系列，單擊“刪除”按鈕。 在“位置”組中單擊“移動(dòng)圖表”按鈕 ，打開(kāi)“移動(dòng)圖表”對(duì)話(huà)框，選擇“新工作表”單選按鈕，在右邊的文本框中輸入“成績(jī)統(tǒng)計(jì)圖”。2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析29修改圖表（三） 在“成績(jī)統(tǒng)計(jì)圖”工作表中，調(diào)整三維視圖的角

17、度，修改圖表標(biāo)題及坐標(biāo)軸標(biāo)題。 選擇“成績(jī)統(tǒng)計(jì)圖”工作表中的圖表。單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“三維旋轉(zhuǎn)”命令，打開(kāi)“設(shè)置圖表區(qū)格式”對(duì)話(huà)框，在“三維旋轉(zhuǎn)”的“圖表縮放”區(qū)域中勾選“直角坐標(biāo)軸”復(fù)選框。 鼠標(biāo)單擊并選中“圖表標(biāo)題”文字，輸入“成績(jī)統(tǒng)計(jì)圖”。 鼠標(biāo)右鍵單擊橫坐標(biāo)軸位置，從快捷菜單中選擇“編輯文字”，刪除原有文字，輸入“等級(jí)”。用相同的方法，在縱坐標(biāo)軸位置輸入“人數(shù)”。2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析30格式化圖表2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析31 在“成績(jī)統(tǒng)計(jì)圖”工作表中，設(shè)置“圖表區(qū)”、“背景墻”的填充效果；

18、修飾“圖表標(biāo)題”。效果如右圖所示。 選擇“圖表區(qū)”。 在“形狀樣式”組中選擇“細(xì)微效果-藍(lán)色，強(qiáng)調(diào)顏色1”。 在“形狀效果” 列表中選擇“陰影” 中的“內(nèi)部”區(qū)域中的“內(nèi)部右上角”選項(xiàng)。 選擇“背景墻”。 在“形狀樣式”組中選擇“紋理”中的“藍(lán)色面巾紙”選項(xiàng)。 選擇圖表標(biāo)題。 設(shè)置字體為“隸書(shū)”、字號(hào)為“28”。 選擇第4行第2列的藝術(shù)字樣式。 在“文本效果” 的“陰影”選項(xiàng)中選擇“外部”區(qū)域中的“左下斜偏移”選項(xiàng)。思考題（三） 選擇圖表的數(shù)據(jù)源時(shí)，如何選擇不連續(xù)的數(shù)據(jù)區(qū)域？ 如何修改圖表？ 對(duì)已建立完成的圖表，應(yīng)如何添加“圖表標(biāo)題”？ 對(duì)圖表進(jìn)行格式化時(shí)，應(yīng)該如何操作？ 將“圖表區(qū)”的填充效

19、果設(shè)置為“雨后初晴”，應(yīng)如何操作？2022-3-17第8章 Excel綜合應(yīng)用1成績(jī)表統(tǒng)計(jì)與分析32案例總結(jié) （一） 統(tǒng)計(jì)函數(shù)COUNT、COUNTA、COUNTIF、COUNTIFS 在使用COUNTIF函數(shù)時(shí)應(yīng)該注意，當(dāng)公式需要復(fù)制時(shí)，如果參數(shù)“Range”的引用區(qū)域固定不變，通常應(yīng)使用絕對(duì)引用或區(qū)域命名方式實(shí)現(xiàn)；如果參數(shù)“Criteria”是表達(dá)式或字符串，必須在兩邊加上西文雙引號(hào)。 在使用COUNTIFS函數(shù)時(shí)應(yīng)該注意，雖然多個(gè)指定區(qū)域無(wú)需彼此相鄰，但必須具有相同的行數(shù)和列數(shù)。條件之間是“與”關(guān)系。 邏輯判斷函數(shù)IF以及嵌套函數(shù)的使用； 當(dāng)邏輯判斷給出的條件多于兩個(gè)時(shí)，通常采用IF函數(shù)的嵌套。在使用IF函數(shù)時(shí)，應(yīng)注意函數(shù)多層