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

1、Excel 綜合應(yīng)用 1成績表統(tǒng)計(jì) 與分析河南質(zhì)量工程職業(yè)學(xué)院教案首頁第 7 章 節(jié) 需 4 學(xué)時(shí)審閱人授課課題第 7 章 Excel 綜合應(yīng)用 1 成績表統(tǒng)計(jì)與分析學(xué)時(shí)4授課時(shí)間2008 年 11 月 5 日 星期 三 第 1 、2 節(jié)知識(shí)目標(biāo)統(tǒng)計(jì)函數(shù) COUNT 、COUNTIF 和邏輯判斷函數(shù) IF 的使用 方法，條件格式的設(shè)置方法，圖表的創(chuàng)建、修改能力目標(biāo)熟練掌握統(tǒng)計(jì)函數(shù) COUNT 、COUNTIF 的使用方法，熟 練掌握邏輯判斷函數(shù) IF 的使用方法，掌握條件格式的設(shè)置方 法，熟練使用圖表的創(chuàng)建、修改素質(zhì) 教育 目標(biāo)穿插上機(jī)，提高學(xué)生的動(dòng)手能力教學(xué)重點(diǎn)統(tǒng)計(jì)函數(shù) COUNT 、COU

2、NTIF 和邏輯判斷函數(shù) IF 的使用 方法教學(xué)難點(diǎn)條件格式的設(shè)置方法授課方法多媒體教學(xué)儀器投影儀，多媒體電腦教學(xué)反饋第 7 2 頁第 7 章 Excel 綜合應(yīng)用 1 成績表統(tǒng)計(jì)與分析7.1 成績表統(tǒng)計(jì)案例分析 任務(wù)的提出 班主任王老師要制作“成績統(tǒng)計(jì)表” 、“成 績等級(jí)表”及“成績統(tǒng)計(jì)圖。解決方案7.2.1 用統(tǒng)計(jì)函數(shù)與公式制作“成績統(tǒng)計(jì)表” 跨工作表的單元格引用打開“第 7 章素材”文件夾下的“統(tǒng)計(jì)表 （素材） .xls”文件，選擇“成績統(tǒng)計(jì)表” 工作表。從“各科成績表”中，將四門課程的“班 級(jí)平均分”、“班級(jí)最高分”和“班級(jí)最低 分”的數(shù)據(jù)引用到如右圖所示的“成績統(tǒng) 計(jì)表”中的相應(yīng)單元

3、格中。統(tǒng)計(jì)函數(shù) COUNTA 及 COUNT 的使用 將“各科成績表”中各門課程的“應(yīng)考人 數(shù)”和“參考人數(shù)” 的統(tǒng)計(jì)結(jié)果放置到 “成 績統(tǒng)計(jì)表”中的相應(yīng)單元格中。 在“插入函數(shù)”對(duì)話框中，選擇“統(tǒng)計(jì)”， 在“選擇函數(shù)”列表框中，選擇 “COUNTA ”函數(shù)，單擊“確定”按鈕。 打開“函數(shù)參數(shù)”對(duì)話框。進(jìn)行如下圖 所示的設(shè)置。“COUNT ”函數(shù)的設(shè)置類似(略) 。 條件統(tǒng)計(jì)函數(shù) COUNTIF 的功能COUNTIF 函數(shù)的功能： 統(tǒng)計(jì)指定區(qū)域內(nèi)滿足給定條件的單元格數(shù) 目。語法格式：COUNTIF(range,criteria)其中：Range 指定單元格區(qū)域， Criteria 表示指定的條

4、件表達(dá)式。條件表達(dá)式的形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為 60、60 、=90 或 缺考 等用 COUNTIF 函數(shù)，將“各科成績表” 中各門課程的缺考人數(shù)以及各分?jǐn)?shù)段人 數(shù)的統(tǒng)計(jì)結(jié)果放置到 “成績統(tǒng)計(jì)表” 中的 相應(yīng)單元格 。在“函數(shù)參數(shù)”對(duì)話框中，進(jìn)行如下圖 所示的設(shè)置：編輯欄中的函數(shù)為 =COUNTIF（ 各科成績表 !D2:D38, 缺考 ）。各分?jǐn)?shù)段人數(shù)的統(tǒng)計(jì)條件對(duì)應(yīng)函數(shù)90-100（人）=COUNTIF（ 各 科 成 績 表!D2:D38,=9080-89（人）=COUNTIF（ 各 科 成 績 表 !D2:D38,=80）- B970-79（人）=COUNTIF（

5、 各 科 成 績 表!D2:D38,=70）-B9-B1060-69（人）=COUNTIF（ 各 科 成 績第 7 6 頁表!D2:D38,=60）-B9-B10-B11=COUNTIF（ 各 科 成 績 表!D2:D38,=60）/COUNT（ 各 科 成績表 !D$2:D$38） 或： 1-B13/B7計(jì)算優(yōu)秀率：=COUNTIF（ 各 科 成 績 表 !D$2:D$38,=90）/COUNT（ 各 科 成績表 !D$2:D$38）或： B9/B7思考題（一） 如何引用同一工作簿中不同工作表的數(shù) 據(jù)？ 引用單元格的數(shù)據(jù)與復(fù)制粘貼數(shù)據(jù)有何 區(qū)別？ 統(tǒng)計(jì)函數(shù) COUNT 與 COUNTA 的區(qū)

6、別是 什么？ 條件統(tǒng)計(jì)函數(shù) COUNTIF 的應(yīng)用場(chǎng)合是什 么？其中的兩個(gè)參數(shù)各代表什么？ 在“各科成績表 ”工作表中分別統(tǒng)計(jì)出男、 女生人數(shù)應(yīng)該用什么函數(shù)？試寫出函數(shù)的 表達(dá)式。單元格數(shù)據(jù)的刪除與清除 將“各科成績表”工作表復(fù)制一份，并將 復(fù)制后的工作表更名為“各科等級(jí)表” 。在 “各科等級(jí)表” 中，清除“大學(xué)英語”、“計(jì) 算機(jī)應(yīng)用”、“高等數(shù)學(xué)”及“應(yīng)用文寫作” 列中的分?jǐn)?shù)內(nèi)容，并清除“總分” 、“名次” 列的所有屬性；刪除“班級(jí)平均分” 、“班 級(jí)最高分”、“班級(jí)最低分”所在單元格區(qū)域。7.2.2 用 IF 函數(shù)與條件格式制作 “各科等級(jí)表” 邏輯判斷函數(shù) IF 的功能IF 函數(shù)的功能是

7、：判斷給出的條件是否滿足，如果滿足返 回一個(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ù)對(duì)“各科成績表”中的“應(yīng)用文寫作”成績?cè)?60 分以上的，在“各科等 級(jí)表”中“應(yīng)用文寫作”的對(duì)應(yīng)位置設(shè)置 為“及格”，否則為“不及格” 。選擇目標(biāo)單元格 G2 ，在“函數(shù)參數(shù)” 對(duì)話框中進(jìn)行如下圖所示的設(shè)置。按下表所示的分

8、數(shù)與等級(jí)的對(duì)應(yīng)關(guān)系，利 用 If 嵌套函數(shù)對(duì)“各科成績表”中的“大 學(xué)英語”、“計(jì)算機(jī)應(yīng)用”、“高等數(shù)學(xué)”三 門課程的分?jǐn)?shù)，在“各科等級(jí)表”中的對(duì) 應(yīng)科目中進(jìn)行相應(yīng)的等級(jí)設(shè)置 。分?jǐn)?shù)等級(jí)第 7 10 頁缺考缺考90=分?jǐn)?shù)A80= 分 數(shù) 90B70= 分 數(shù) 80C60= 分 數(shù) 70D分?jǐn)?shù)=90優(yōu)秀90分 數(shù)=80良好80分 數(shù)=70中等70分 數(shù)=60及格分?jǐn)?shù)=90, 優(yōu) 秀 ,if(90A2=80, 良 好 ,if(80A2=70, 中 等 ,if(70A2=60, 及 格,if(A260,不及格 ,false)第 7 14 頁7.2.3用圖表向?qū)е谱鞒煽兘y(tǒng)計(jì)圖 制作各科成績統(tǒng)計(jì)圖 圖表

9、比數(shù)據(jù)更易于表達(dá)數(shù)據(jù)之間的關(guān)系以及數(shù) 據(jù)變化的趨勢(shì)。 “成績統(tǒng)計(jì)圖 ”可以直觀地表現(xiàn) 各門課程各分?jǐn)?shù)段人數(shù)。使用“圖表向?qū)?”創(chuàng)建圖表在“成績統(tǒng)計(jì)表 ”中，根據(jù)各分?jǐn)?shù)段人數(shù)及 缺考人數(shù)制作圖表。要求如下： 圖表類型為“簇狀柱形圖” ； 數(shù)據(jù)系列產(chǎn)生在“列” ；第 7 15 頁圖表標(biāo)題為“成績統(tǒng)計(jì)圖” ；分類（ X）軸為“等級(jí)”；數(shù)值（ Y）軸 為“人數(shù)”；將圖表“作為其中的對(duì)象插入”到“成 績統(tǒng)計(jì)表”中 。選擇圖表類型在菜單欄中選擇 “插入 ”“圖表”命令，打 開 “圖表向?qū)?-4 步驟之 1-圖表類型 ”對(duì)話 框，進(jìn)行如右圖所示的設(shè)置。單擊“下一步 ”按鈕第 7 16 頁選擇源數(shù)據(jù)按住 Ct

10、rl 鍵的同時(shí)，分別在當(dāng)前工作表中 選擇不連續(xù)的兩個(gè)區(qū)域，如下圖所示，并 在“圖表向?qū)?-4 步驟之 2-圖表源數(shù)據(jù) ”對(duì)話 框中，顯示相應(yīng)的設(shè)置。第 7 17 頁設(shè)置圖表選項(xiàng)單擊“下一步 ”按鈕，打開 “圖表向?qū)?-4 步 驟之 3-圖表選項(xiàng) ”對(duì)話框，并進(jìn)行如下圖所 示的設(shè)置。第 7 18 頁選擇圖表位置“下一步”按鈕，打開 “圖表向?qū)?-4 步驟之 4-圖表位置 ”對(duì)話框， 進(jìn)行如下圖所示的 設(shè)置。修改圖表 在圖表的制作過程中四步中的任何一步 出錯(cuò)，都不必從頭開始，只需單擊圖表， 使圖表處于激活狀態(tài)，從 “圖表 ”菜單中， 選擇對(duì)應(yīng)的命令即可；也可單擊圖表后， 單擊鼠標(biāo)右鍵，從快捷菜單中

11、，選擇對(duì)應(yīng) 的命令。修改圖表要求第 7 19 頁在 “成績統(tǒng)計(jì)表 ”工作表中，對(duì)圖表進(jìn)行如 下修改：將圖表類型改為“三維簇狀柱形圖” ； 數(shù)據(jù)系列產(chǎn)生在“行” ，并從圖表中刪 除缺考人數(shù)；在圖表中“顯示數(shù)據(jù)表” ；將圖表“作為新工作表插入” ，并將新 工作表命名為“成績統(tǒng)計(jì)圖” 。 操作步驟略圖表的組成第 7 20 頁格式化圖表在 “成績統(tǒng)計(jì)圖 ”工作表中，對(duì)圖表的外觀 進(jìn)行如下修飾：將圖表標(biāo)題設(shè)置為 “幼圓、22號(hào)、藍(lán)色、 加粗”；將圖表區(qū)的填充效果設(shè)置為“羊皮紙” ； 將背景墻的填充效果設(shè)置為“水滴” ； 為圖例添加“陰影邊框” 。設(shè)置圖表區(qū)的填充效果在 “圖表 ”工具欄上的 “圖表對(duì)象

12、”下拉列 表中，選擇 “圖表區(qū) ”，單擊 “圖表”工具欄 上的 “圖表區(qū)格式 ”按鈕，打開 “圖表區(qū)格 式”對(duì)話框，在“圖案 ”選項(xiàng)卡中，單擊 “填充效果 ”按 鈕，打開 “填充效果 ”對(duì)話框，在 “漸變 ”選 項(xiàng)卡中，選擇 “預(yù)設(shè) ”單選按鈕，在 “預(yù)設(shè)顏 色”下拉列表中，選擇 “羊皮紙 ”選項(xiàng)，在“底 紋樣式 ”欄中，選擇“中心輻射 ”單選項(xiàng)，單第 7 21 頁擊“確定”按鈕?！疤畛湫Ч?”對(duì)話框的設(shè)置 如右圖所示。設(shè)置背景墻的填充效果鼠標(biāo)指向 “背景墻”，單擊鼠標(biāo)右鍵，在彈 出的快捷菜單中選擇 “背景墻格式 ”命令， 打開 “背景墻格式 ”對(duì)話框，在 “圖案 ”選項(xiàng) 卡中，單擊 “填充效

13、果 ”按鈕，打開 “填充效第 7 22 頁 果”對(duì)話框。選擇 “紋理 ”選項(xiàng)卡，在 “紋理 ”列表框中， 選擇“水滴”，如右圖所示，單擊 “確定 ”按 鈕。思考題（三）選擇圖表的數(shù)據(jù)源時(shí)，如何選擇不連續(xù)的第 7 23 頁數(shù)據(jù)區(qū)域？如何修改圖表？對(duì)已建立完成的圖表，應(yīng)如何添加 “圖表標(biāo)題”？對(duì)圖表進(jìn)行格式化時(shí)，應(yīng)該如何操作？ 將 “圖表區(qū) ”的填充效果設(shè)置為 “雨后初晴”，應(yīng)如何操作？案例總結(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)。第 7 24 頁 邏輯判斷函數(shù) IF 以及嵌套函數(shù)的使用； 當(dāng)邏輯判斷給出的條件多于兩個(gè)時(shí)， 通常采用 IF 函數(shù)的嵌套。在使用 IF 函 數(shù)時(shí)，應(yīng)注意函數(shù)多層嵌套時(shí)的括號(hào)匹 配，并且公式中的所有符號(hào)必須為西文 字符。設(shè)置條件格式； 圖表的創(chuàng)建、修改及