Office綜合應用教程任務4統(tǒng)計學生成績

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

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

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

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

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

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

？

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

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

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

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

“確定”按鈕。此時系統(tǒng)彈出“函數(shù)參數(shù)”對話框，在“Range”文本框中輸入“D4:D139”，在“Criteria”文本框中輸入“">=85"”，如圖4.19所示。然后按回車鍵即可求得成績屬于優(yōu)的人數(shù)。接下來向右拖動填空柄至單元格H143，即可求出所有科目中成績屬于優(yōu)的人數(shù)，如圖4.20所示。計算的連續(xù)區(qū)域滿足的給定條件任務4教學內(nèi)容如果已經(jīng)掌握了函數(shù)格式，就不需通過“插入函數(shù)參數(shù)”對話框和“函數(shù)參數(shù)”對話框進行設置，直接在編輯欄輸入函數(shù)表達式即可。選擇單元格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。任務4實踐內(nèi)容（2）要求：①分別在教師提供的數(shù)據(jù)表-成績分析表.xls上按字段要求進行如下操作：思考：用EXCEL中的總分排序結(jié)果對照“班名次”和“段名次”為什么求得第一個學生的名次后，不能把填充柄直接下拉至其他兩個班級？任務4實踐內(nèi)容（2）要求：②分別在教師提供的數(shù)據(jù)表-成績分析表.xls上按字段要求進行如下操作：思考：注意分數(shù)段條件的設置，如85分以上—是指包含85分如何快速求證結(jié)果的正確與否？任務4討論內(nèi)容公式與函數(shù)的應用方式?單元格范圍選擇有哪些方式?相對引用與絕對引用的區(qū)別?RANK排序與一般數(shù)據(jù)排序意義上有何不同?Excel可設定哪些條件表達式?任務4總結(jié)EXCEL中最強大的功能之一就是其函數(shù)功能。按照不同用途分為財務、日期與時間、數(shù)學與三角函數(shù)、統(tǒng)計、查找與引用、數(shù)據(jù)庫、文本、邏輯、信息。用戶需要使

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

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

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

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

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

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

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