Office綜合應(yīng)用教程任務(wù)4統(tǒng)計(jì)學(xué)生成績

任務(wù)4:統(tǒng)計(jì)學(xué)生成績學(xué)習(xí)目的:掌握Excel中公式的使用掌握Excel中函數(shù)的使用知識要點(diǎn):Excel中常見的幾種函數(shù)Excel中用公式完成統(tǒng)計(jì)Excel中用函數(shù)完成統(tǒng)計(jì)技能要點(diǎn)：如何使用幫助單元格的正確引用任務(wù)4:統(tǒng)計(jì)學(xué)生成績教學(xué)重點(diǎn)：公式的輸入函數(shù)的調(diào)用必要的統(tǒng)計(jì)實(shí)現(xiàn)教學(xué)難點(diǎn)：統(tǒng)計(jì)函數(shù)的用法任務(wù)4教學(xué)內(nèi)容一、任務(wù)綜述：教務(wù)員李四要對初三段學(xué)生的成績

進(jìn)行統(tǒng)計(jì)，先按班級統(tǒng)計(jì)出每位學(xué)生各

科成績的總分、每位學(xué)生成績的平均分、每位學(xué)生總分的班級名次；最后將三個

班級的成績按總分進(jìn)行段名次排列，統(tǒng)

計(jì)出每門課程最高分和最低分。李四采用Excel2003軟件，使用公式與函數(shù)功能完成了成績統(tǒng)計(jì)任務(wù)。任務(wù)4教學(xué)內(nèi)容二、任務(wù)實(shí)施1.收集原始數(shù)據(jù)與理解統(tǒng)計(jì)指標(biāo)明確統(tǒng)計(jì)對象與統(tǒng)計(jì)指標(biāo)統(tǒng)計(jì)對象：班級、人數(shù)、課程名稱統(tǒng)計(jì)指標(biāo)：總分、平均分、排序、名次等等搜集信息及建立成績表收集信息過程建立成績表的關(guān)鍵是建立表項(xiàng)目任務(wù)4教學(xué)內(nèi)容先打開包含所需統(tǒng)計(jì)信息的數(shù)據(jù)表2.用公式計(jì)算總分與平均分選中I4單元格，輸入“＝D4+E4+F4+G4+H4”，然后按下回車鍵，總分即可求出，如圖4.2所示。任務(wù)4教學(xué)內(nèi)容接下來選中I4單元格，然后向下拖動填充柄即可完成其余學(xué)生總分的統(tǒng)計(jì)。平均分的計(jì)算方法也是一樣，選中J4單元格，輸入“＝

(D4+E4+F4+G4+H4)/5”，然后按下回車鍵，平均分即可求出，如圖4.4所示。任務(wù)4教學(xué)內(nèi)容3．使用函數(shù)計(jì)算總分選中I4單元格，執(zhí)行菜單欄中的“插入”→“函數(shù)”命令，如圖4.6所示。任務(wù)4教學(xué)內(nèi)容在彈出的“插入函數(shù)”對話框中，選中函數(shù)“SUM”，如圖4.7、圖4.8所示。任務(wù)4教學(xué)內(nèi)容函數(shù)參數(shù)的輸入方法：1）.可以直接在參數(shù)的文本框中直接輸入單元格或數(shù)值2）.也可以直接單擊文本框后的按鈕然后直接拖動鼠標(biāo)選中單元格區(qū)域，對話框中將自動根據(jù)所選中的區(qū)域顯示，單擊此區(qū)域后的按鈕 返回至“函數(shù)參數(shù)”對話框。任務(wù)4教學(xué)內(nèi)容4.求每門課程的平均分、最高分、最低分平均分、最高分、最低分統(tǒng)計(jì)結(jié)果的位置設(shè)計(jì)如圖4.14所示。任務(wù)4教學(xué)內(nèi)容求平均分使用函數(shù)—AVERAGE求最高分使用函數(shù)—MAX求最低分使用函數(shù)—MIN任務(wù)4實(shí)踐內(nèi)容（1）要求：①分別在教師提供的數(shù)據(jù)表-成績分析表.xls上按字段要求進(jìn)行如下操作：思考：上面分別用公式和函數(shù)的方法求得總分和平均分，你覺得哪種方法更合適？任務(wù)4實(shí)踐內(nèi)容（1）思考：此處的平均分和前一題的平均分區(qū)別在哪里？容易出錯在哪里？（要特別注意選擇計(jì)算區(qū)域的正確性）要求：②分別在教師提供的數(shù)據(jù)表-成績分析表.xls上按字段要求進(jìn)行如下操作：任務(wù)4教學(xué)內(nèi)容5．求班名次和段名次提一個問題？有兩位同學(xué)的總分是一樣的，請問他們的名次有區(qū)別嗎？思考：正常情況下，我們要確定董宏峰同學(xué)在初三（1）班的總分名次，是如何來做的？求此結(jié)果，需要知道幾個已有條件：①該學(xué)生的總分成績②班級總分成績的序列340

？

I4I$4:I$48任務(wù)4教學(xué)內(nèi)容選中單元格K4，執(zhí)行菜單欄中的“插入”→“函數(shù)”命令，出現(xiàn)“插入函數(shù)”對話框，在“選擇類別”下拉列表中選擇

“統(tǒng)計(jì)”，在“選擇函數(shù)”列表框中選擇“RANK”，然后單擊“確定”按鈕。此時(shí)系統(tǒng)彈出“函數(shù)參數(shù)”對話框，在

“Number”文本框中輸入“I4”，在“Ref”文本框中輸

入“I$4:I$48”，“Order”文本框中留空即可。單擊“確定”按鈕后，可求得第一位學(xué)生的班名次，向下拖動填充柄至單元格K48，如圖4.18所示。任務(wù)4教學(xué)內(nèi)容同理求得初三（2）班和初三（3）班的班級名次。段名次的求法就比較簡單，只需在“Ref”文本框中輸入“I$4:I$139”，即可實(shí)現(xiàn)求段名次。任務(wù)4教學(xué)內(nèi)容6．統(tǒng)計(jì)各門課程各分?jǐn)?shù)段的人數(shù)分?jǐn)?shù)段的劃分規(guī)則是：85～100分屬于優(yōu)70～84分屬于中60～69分屬于及格0～59分屬于不及格后面的任務(wù)還要介紹分檔各分?jǐn)?shù)段劃分規(guī)則任務(wù)4教學(xué)內(nèi)容此處使用COUNTIF函數(shù)實(shí)現(xiàn)先選擇單元格，執(zhí)行菜單欄中的“插入”→“函數(shù)”命令，在“插入函數(shù)”對話框的“選擇類別”下拉列表中選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”列表框中選擇“COUNTIF”并單擊

“確定”按鈕。此時(shí)系統(tǒng)彈出“函數(shù)參數(shù)”對話框，在“Range”文本框中輸入“D4:D139”，在“Criteria”文本框中輸入“">=85"”，如圖4.19所示。然后按回車鍵即可求得成績屬于優(yōu)的人數(shù)。接下來向右拖動填空柄至單元格H143，即可求出所有科目中成績屬于優(yōu)的人數(shù)，如圖4.20所示。計(jì)算的連續(xù)區(qū)域滿足的給定條件任務(wù)4教學(xué)內(nèi)容如果已經(jīng)掌握了函數(shù)格式，就不需通過“插入函數(shù)參數(shù)”對話框和“函數(shù)參數(shù)”對話框進(jìn)行設(shè)置，直接在編輯欄輸入函數(shù)表達(dá)式即可。選擇單元格D144，輸入

“=COUNTIF(D4:D139,”>=70“)-COUNTIF(D4:D139,

”>=85“)”，按回車鍵后，向右拖動填空柄至單元格H144。選擇單元格D145，輸入

“=COUNTIF(D4:D139,”>=60“)-COUNTIF(D4:D139,

”>=70“)”，按回車鍵后，向右拖動填空柄至單元格H145。選擇單元格D146，輸入“=COUNTIF(D4:D139,”<60“)”，按回車鍵后，向右拖動填空柄至單元格H146。任務(wù)4實(shí)踐內(nèi)容（2）要求：①分別在教師提供的數(shù)據(jù)表-成績分析表.xls上按字段要求進(jìn)行如下操作：思考：用EXCEL中的總分排序結(jié)果對照“班名次”和“段名次”為什么求得第一個學(xué)生的名次后，不能把填充柄直接下拉至其他兩個班級？任務(wù)4實(shí)踐內(nèi)容（2）要求：②分別在教師提供的數(shù)據(jù)表-成績分析表.xls上按字段要求進(jìn)行如下操作：思考：注意分?jǐn)?shù)段條件的設(shè)置，如85分以上—是指包含85分如何快速求證結(jié)果的正確與否？任務(wù)4討論內(nèi)容公式與函數(shù)的應(yīng)用方式?單元格范圍選擇有哪些方式?相對引用與絕對引用的區(qū)別?RANK排序與一般數(shù)據(jù)排序意義上有何不同?Excel可設(shè)定哪些條件表達(dá)式?任務(wù)4總結(jié)EXCEL中最強(qiáng)大的功能之一就是其函數(shù)功能。按照不同用途分為財(cái)務(wù)、日期與時(shí)間、數(shù)學(xué)與三角函數(shù)、統(tǒng)計(jì)、查找與引用、數(shù)據(jù)庫、文本、邏輯、信息。用戶需要使

用函數(shù)時(shí)，可選擇函數(shù)類型，再選擇該子類中的函數(shù)。若無

法判斷使用函數(shù)的類型，可選擇“全部”，再逐步下拉查找。除了SUM、AVERAGE、IF、COUNTIF、COUNT、MAX等常用函數(shù)外。一般在使用函數(shù)的過程中，可以通過幫助文件獲取操作方法與步驟，或者直接通過F1幫助系統(tǒng)獲取你想了解的比如函數(shù)格式、具體使用步驟等等任務(wù)4總結(jié)任務(wù)4總結(jié)在函數(shù)或公式的使用過程中，范圍的選擇是關(guān)鍵，同時(shí)要確定是相對引用還是絕對引用。嘗試將上述例子中引用單元格范圍時(shí)的“$”符號去掉，然后用填充柄實(shí)現(xiàn)其他單元格的統(tǒng)計(jì)，觀察問題所在。在使用公式與函數(shù)進(jìn)行統(tǒng)計(jì)時(shí)，其結(jié)果顯示出來后一定要經(jīng)過人工的檢查與核對，不能忽略這一重要的環(huán)節(jié)。盡量采用復(fù)制、粘貼的方法，獲取已經(jīng)經(jīng)過驗(yàn)證的正確函數(shù)表達(dá)式。任務(wù)4實(shí)戰(zhàn)訓(xùn)練建立如圖4.27所示的表格，保存為“某某公司工資表.xls”。任務(wù)4實(shí)戰(zhàn)訓(xùn)練

1）選擇其中的“Sheet1”工作表，利用IF函數(shù)求“職務(wù)津貼”。

條件：總經(jīng)理的職務(wù)津貼為3000元，經(jīng)理的職務(wù)津貼為2000元，工程師的職務(wù)津貼為1500元，其余員工的職務(wù)津貼為1000元。2）計(jì)算應(yīng)發(fā)工資。

應(yīng)發(fā)工資＝基本工資＋職務(wù)津貼＋基本工資×加班天數(shù)/20任務(wù)4實(shí)戰(zhàn)訓(xùn)練3）利用IF函數(shù)求“所得稅”。所得稅＝（基本工資－1600）×稅率條件：基本工資≤1600基本工資－1600＜500500≤基本工資－1600＜20002000≤基本工資－1600＜50005000≤基本工資－1600＜20000稅率為0%稅率為5%稅率為10%稅率為15%稅率為20%任務(wù)4實(shí)戰(zhàn)訓(xùn)練4）計(jì)算實(shí)發(fā)工資。實(shí)發(fā)工資＝應(yīng)發(fā)工資－基本工資×加班天數(shù)/20－所得稅

5）利用ROUND函數(shù)對“實(shí)發(fā)工資”進(jìn)行四舍五入并保留小數(shù)點(diǎn)后一位小數(shù)。6）將表中相應(yīng)的數(shù)字格式設(shè)置為“貨幣”格式。

7）將各列調(diào)整到最合