第7章Excel綜合應(yīng)用1—成績表統(tǒng)計與分析

1、主編：許晞主編：許晞 副主編：劉艷麗副主編：劉艷麗 曾煌興曾煌興 鄭杰鄭杰 秦高德秦高德第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析22022-5-26學(xué)習(xí)目標(biāo)學(xué)習(xí)目標(biāo)熟練掌握統(tǒng)計函數(shù)COUNT、COUNTIF的使用方法熟練掌握邏輯判斷函數(shù)IF的使用方法掌握條件格式的設(shè)置方法熟練使用圖表的創(chuàng)建、修改第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析32022-5-26目錄目錄7.1 成績表統(tǒng)計案例分析 7.2 實現(xiàn)方法7.2.1 用統(tǒng)計函數(shù)與公式制作“成績統(tǒng)計表”跨工作表的單元格引用跨工作表的單元格引用 統(tǒng)計函數(shù)統(tǒng)計函數(shù)COUNTACOUNTA及及COUNTCOUNT的使用的使用 條件統(tǒng)計函數(shù)條件統(tǒng)

2、計函數(shù)COUNTIFCOUNTIF的使用的使用 公式計算公式計算 7.2.2 用IF函數(shù)與條件格式制作“各科等級表”邏輯判斷函數(shù)邏輯判斷函數(shù)IFIF的使用的使用 條件格式條件格式 7.2.3用圖表向?qū)е谱鞒煽兘y(tǒng)計圖 使用使用“圖表向?qū)D表向?qū)А眲?chuàng)建圖表創(chuàng)建圖表 修改圖表修改圖表 格式化圖表格式化圖表 7.3 案例總結(jié)7.4 課后練習(xí)第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析42022-5-267.1成績表統(tǒng)計案例分析成績表統(tǒng)計案例分析 任務(wù)的提出班主任王老師要制作 “成績統(tǒng)計表”、“成績等級表” 及“成績統(tǒng)計圖。解決方案第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析52022-5-26跨工作表

3、的單元格引用跨工作表的單元格引用打開“第7章素材”文件夾下的“統(tǒng)計表（素材）.xls”文件，選擇“成績統(tǒng)計表”工作表。從“各科成績表”中，將四門課程的“班級平均分”、“班級最高分”和“班級最低分”的數(shù)據(jù)引用到如右圖所示的“成績統(tǒng)計表”中的相應(yīng)單元格中 。第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析62022-5-26統(tǒng)計函數(shù)統(tǒng)計函數(shù)COUNTACOUNTA及及COUNTCOUNT的使用的使用 將“各科成績表”中各門課程的“應(yīng)考人數(shù)”和“參考人數(shù)”的統(tǒng)計結(jié)果放置到“成績統(tǒng)計表”中的相應(yīng)單元格中。在“插入函數(shù)”對話框中，選擇“統(tǒng)計”，在“選擇函數(shù)”列表框中，選擇“COUNTA”函數(shù)，單擊“確定”按

4、鈕。打開“函數(shù)參數(shù)”對話框。進行如下圖所示的設(shè)置?！癈OUNT”函數(shù)的設(shè)置類似（略）。第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析72022-5-26條件統(tǒng)計函數(shù)條件統(tǒng)計函數(shù)COUNTIF的功能的功能 COUNTIF函數(shù)的功能：統(tǒng)計指定區(qū)域內(nèi)滿足給定條件的單元格數(shù)目。語法格式：COUNTIF(range,criteria)其中：Range 指定單元格區(qū)域，Criteria表示指定的條件表達式。條件表達式的形式可以為數(shù)字、表達式或文本。例如，條件可以表示為 60、60、=90 或 缺考等。第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析82022-5-26條件統(tǒng)計函數(shù)條件統(tǒng)計函數(shù)COUNTIF的使用

5、的使用用COUNTIF函數(shù)，將“各科成績表”中各門課程的缺考人數(shù)以及各分數(shù)段人數(shù)的統(tǒng)計結(jié)果放置到“成績統(tǒng)計表”中的相應(yīng)單元格 。在“函數(shù)參數(shù)”對話框中，進行如下圖所示的設(shè)置：編輯欄中的函數(shù)為 =COUNTIF(=COUNTIF(各科成績表各科成績表!D2:D38,!D2:D38,缺考缺考 )。 第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析92022-5-26各分數(shù)段人數(shù)的統(tǒng)計各分數(shù)段人數(shù)的統(tǒng)計 條件對應(yīng)函數(shù)90-100(人)=COUNTIF(各科成績表各科成績表!D2:D38,=90 80-89(人)=COUNTIF(各科成績表各科成績表!D2:D38,=80)- B9 70-79(人)=CO

6、UNTIF(各科成績表各科成績表!D2:D38,=70)-B9-B10 60-69(人)=COUNTIF(各科成績表各科成績表!D2:D38,=60)-B9-B10-B11 59以下(人)=COUNTIF(各科成績表各科成績表!D2:D38,=60)/COUNT(各科成績表!D$2:D$38) 或：或：1-B13/B7 1-B13/B7 計算優(yōu)秀率 ：=COUNTIF(=COUNTIF(各科成績表各科成績表!D$2:D$38,=90)/COUNT(!D$2:D$38,=90)/COUNT(各科成績各科成績表表!D$2:D$38) !D$2:D$38) 或：或：B9/B7 B9/B7 第7章 E

7、xcel綜合應(yīng)用1成績表統(tǒng)計與分析112022-5-26思考題（一）思考題（一）如何引用同一工作簿中不同工作表的數(shù)據(jù)？引用單元格的數(shù)據(jù)與復(fù)制粘貼數(shù)據(jù)有何區(qū)別？統(tǒng)計函數(shù)COUNT與COUNTA的區(qū)別是什么？條件統(tǒng)計函數(shù)COUNTIF的應(yīng)用場合是什么？其中的兩個參數(shù)各代表什么？在“各科成績表”工作表中分別統(tǒng)計出男、女生人數(shù)應(yīng)該用什么函數(shù)？試寫出函數(shù)的表達式。第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析122022-5-26單元格數(shù)據(jù)的刪除與清除單元格數(shù)據(jù)的刪除與清除 將“各科成績表”工作表復(fù)制一份，并將復(fù)制后的工作表更名為“各科等級表”。在“各科等級表”中，清除“大學(xué)英語”、“計算機應(yīng)用”、“高等

8、數(shù)學(xué)”及“應(yīng)用文寫作”列中的分數(shù)內(nèi)容，并清除“總分”、“名次”列的所有屬性；刪除“班級平均分”、“班級最高分”、“班級最低分”所在單元格區(qū)域 。第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析132022-5-26邏輯判斷函數(shù)邏輯判斷函數(shù)IF的功能的功能 IF函數(shù)的功能是：判斷給出的條件是否滿足，如果滿足返回一個值，如果不滿足則返回另一個值。語法格式：IF(logical_test,value_if_true,value_if_false)共包括三個參數(shù)，其中：Logical_test 邏輯判斷表達式；value_if_true 表達式為真時返回的值；value_if_false 表達式為假時返回

9、的值。IF函數(shù)的邏輯判斷方法函數(shù)的邏輯判斷方法第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析142022-5-26邏輯判斷函數(shù)邏輯判斷函數(shù)IF的使用的使用利用If函數(shù)對“各科成績表”中的“應(yīng)用文寫作”成績在60分以上的，在“各科等級表”中“應(yīng)用文寫作”的對應(yīng)位置設(shè)置為“及格”，否則為“不及格” 。選擇目標(biāo)單元格G2 ，在“函數(shù)參數(shù)”對話框中進行如下圖所示的設(shè)置。 第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析152022-5-26邏輯判斷函數(shù)邏輯判斷函數(shù)IF的使用（續(xù)）的使用（續(xù)）按下表所示的分數(shù)與等級的對應(yīng)關(guān)系，利用If嵌套函數(shù)對“各科成績表”中的“大學(xué)英語”、“計算機應(yīng)用”、“高等數(shù)學(xué)”三門課

10、程的分數(shù)，在“各科等級表”中的對應(yīng)科目中進行相應(yīng)的等級設(shè)置 。分數(shù)等級缺考缺考90=分數(shù)A80=分數(shù)90B70=分數(shù)80C60=分數(shù)70D分數(shù)=90 優(yōu)秀90分數(shù)=80良好80分數(shù)=70中等70分數(shù)=60及格分數(shù)=90,優(yōu)秀,if(90A2=80,良好,if(80A2=70,中等,if(70A2=60,及格,if(A260,不及格,false)第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析212022-5-26制作各科成績統(tǒng)計圖制作各科成績統(tǒng)計圖 圖表比數(shù)據(jù)更易于表達數(shù)據(jù)之間的關(guān)系以及數(shù)據(jù)變化的趨勢。 “成績統(tǒng)計圖”可以直觀地表現(xiàn)各門課程各分數(shù)段人數(shù)。第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析

11、222022-5-26使用使用“圖表向?qū)D表向?qū)А眲?chuàng)建圖表創(chuàng)建圖表 在“成績統(tǒng)計表”中，根據(jù)各分數(shù)段人數(shù)及缺考人數(shù)制作圖表。要求如下：圖表類型為“簇狀柱形圖”；數(shù)據(jù)系列產(chǎn)生在“列”；圖表標(biāo)題為“成績統(tǒng)計圖”；分類（X）軸為“等級”；數(shù)值（Y）軸為“人數(shù)”；將圖表“作為其中的對象插入”到“成績統(tǒng)計表”中 。第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析232022-5-26選擇圖表類型選擇圖表類型在菜單欄中選擇“插入”“圖表”命令，打開“圖表向?qū)?4步驟之1-圖表類型”對話框，進行如右圖所示的設(shè)置。 單擊“下一步”按鈕 第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析242022-5-26選擇源數(shù)據(jù)選

12、擇源數(shù)據(jù)按住Ctrl鍵的同時，分別在當(dāng)前工作表中選擇不連續(xù)的兩個區(qū)域，如下圖所示，并在“圖表向?qū)?4步驟之2-圖表源數(shù)據(jù)”對話框中，顯示相應(yīng)的設(shè)置。 第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析252022-5-26設(shè)置圖表選項設(shè)置圖表選項單擊“下一步”按鈕，打開“圖表向?qū)?4步驟之3-圖表選項”對話框，并進行如下圖所示的設(shè)置。 第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析262022-5-26選擇圖表位置選擇圖表位置“下一步”按鈕，打開“圖表向?qū)?4步驟之 4-圖表位置”對話框， 進行如下圖所示的設(shè)置。第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析272022-5-26修改圖表修改圖表在圖表的

13、制作過程中四步中的任何一步出錯，都不必從頭開始，只需單擊圖表，使圖表處于激活狀態(tài)，從 “圖表”菜單中，選擇對應(yīng)的命令即可；也可單擊圖表后，單擊鼠標(biāo)右鍵，從快捷菜單中，選擇對應(yīng)的命令。第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析282022-5-26修改圖表要求修改圖表要求 在“成績統(tǒng)計表”工作表中，對圖表進行如下修改：將圖表類型改為“三維簇狀柱形圖”；數(shù)據(jù)系列產(chǎn)生在“行”，并從圖表中刪除缺考人數(shù)；在圖表中“顯示數(shù)據(jù)表”；將圖表“作為新工作表插入”，并將新工作表命名為“成績統(tǒng)計圖”。操作步驟略第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析292022-5-26圖表的組成圖表的組成 第7章 Exce

14、l綜合應(yīng)用1成績表統(tǒng)計與分析302022-5-26格式化圖表格式化圖表 在“成績統(tǒng)計圖”工作表中，對圖表的外觀進行如下修飾：將圖表標(biāo)題設(shè)置為“幼圓、22號、藍色、加粗”；將圖表區(qū)的填充效果設(shè)置為“羊皮紙”；將背景墻的填充效果設(shè)置為“水滴”；為圖例添加“陰影邊框”。第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析312022-5-26設(shè)置圖表區(qū)的填充效果設(shè)置圖表區(qū)的填充效果在“圖表”工具欄上的“圖表對象”下拉列表中，選擇“圖表區(qū)”，單擊“圖表”工具欄上的“圖表區(qū)格式”按鈕，打開“圖表區(qū)格式”對話框，在“圖案”選項卡中，單擊“填充效果”按鈕，打開“填充效果”對話框，在“漸變”選項卡中，選擇“預(yù)設(shè)”單選

15、按鈕，在“預(yù)設(shè)顏色”下拉列表中，選擇“羊皮紙”選項，在“底紋樣式”欄中，選擇“中心輻射”單選項，單擊“確定”按鈕。“填充效果”對話框的設(shè)置，如右圖所示。 第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析322022-5-26設(shè)置背景墻的填充效果設(shè)置背景墻的填充效果鼠標(biāo)指向“背景墻”，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“背景墻格式”命令，打開“背景墻格式”對話框，在“圖案”選項卡中，單擊“填充效果”按鈕，打開“填充效果”對話框。選擇“紋理”選項卡，在“紋理”列表框中，選擇“水滴”，如右圖所示，單擊“確定”按鈕。 第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析332022-5-26思考題（三）思考題（

16、三）選擇圖表的數(shù)據(jù)源時，如何選擇不連續(xù)的數(shù)據(jù)區(qū)域？如何修改圖表？對已建立完成的圖表，應(yīng)如何添加“圖表標(biāo)題”？對圖表進行格式化時，應(yīng)該如何操作？將“圖表區(qū)”的填充效果設(shè)置為“雨后初晴”，應(yīng)如何操作？第7章 Excel綜合應(yīng)用1成績表統(tǒng)計與分析342022-5-26案例總結(jié)案例總結(jié) 本章主要介紹了：統(tǒng)計函數(shù)COUNT、COUNTA、COUNTIF；在使用在使用COUNTIFCOUNTIF函數(shù)時應(yīng)該注意，當(dāng)公式需要復(fù)制時，如果參數(shù)函數(shù)時應(yīng)該注意，當(dāng)公式需要復(fù)制時，如果參數(shù)“Range”Range”的引用區(qū)域固定不變，通常應(yīng)使用絕對引用或區(qū)域命名方的引用區(qū)域固定不變，通常應(yīng)使用絕對引用或區(qū)域命名方式實現(xiàn)；如果參數(shù)式實現(xiàn)；如果參數(shù)“Criteria”Criteria”是表達式或字符串，必須在兩邊加上是表達式或字符串，必須在兩邊加上西文雙引號。西文雙引號。邏輯判斷函數(shù)IF以及嵌套函數(shù)的使用；當(dāng)邏輯判斷給出的條件多于兩個時，通常采用當(dāng)邏輯判斷給出的條件多于兩個時，通常采用IFIF函數(shù)的嵌套。在使用函數(shù)的嵌套。在使用IFIF函數(shù)時，應(yīng)注意函數(shù)多層嵌套時的括號匹配，并且公式中的所有符函數(shù)時，應(yīng)注意函數(shù)多層嵌套時的括號匹配，并且公式中的所有符號必須為西文字符。號必須為西文字符。 設(shè)置條件格式；圖表的創(chuàng)建、修改及格式化特別要注意正確選擇源數(shù)據(jù)。特別要注意正確選擇源數(shù)據(jù)。在圖表