大學(xué)計(jì)算機(jī)基礎(chǔ) 課件 6.2.2 任務(wù)實(shí)現(xiàn)

大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）6.2.2任務(wù)實(shí)現(xiàn)第6章Excel綜合應(yīng)用6.2成績分析1.任務(wù)分析

李老師要對電子信息工程專業(yè)13級的全體學(xué)生成績做分析，要求：（1）計(jì)算學(xué)生的平均課程績點(diǎn)。（2）按平均課程績點(diǎn)及德育績點(diǎn)降序排序。（3）如圖所示，分類匯總每個班的平均課程績點(diǎn)的平均值。1.任務(wù)分析

李老師要對電子信息工程專業(yè)13級的全體學(xué)生成績做分析，要求：（4）如圖所示，分類匯總每個班的平均課程績點(diǎn)的平均值及班級人數(shù)。1.任務(wù)分析

李老師要對電子信息工程專業(yè)13級的全體學(xué)生成績做分析，要求：（5）如圖所示，不改變原始數(shù)據(jù)的前提下統(tǒng)計(jì)每個班的德育及平均課程績點(diǎn)平均值。1.任務(wù)分析

李老師要對電子信息工程專業(yè)13級的全體學(xué)生成績做分析，要求：（6）如圖所示，不改變原始數(shù)據(jù)的前提下統(tǒng)計(jì)每個班的德育績點(diǎn)的最大值和最小值。1.任務(wù)分析

李老師要對電子信息工程專業(yè)13級的全體學(xué)生成績做分析，要求：（7）如圖所示，篩選出可能獲得獎學(xué)金的學(xué)生名單，即德育績點(diǎn)3.0以上（含，下同），平均課程績點(diǎn)2.88以上，單科課程績點(diǎn)2.2以上（因?yàn)椤爸饕刹俊背煽兛凕c(diǎn)可以下調(diào)0.3）。由于學(xué)生成績數(shù)據(jù)多，在進(jìn)行成績操作時，希望表題、學(xué)生姓名以及課程信息等一直顯示在屏幕上，可以通過凍結(jié)窗格來實(shí)現(xiàn)。操作步驟如下：2.實(shí)現(xiàn)過程

1）復(fù)制信息并凍結(jié)窗格①新建工作簿“成績分析.xlsx”，打開“相關(guān)素材.xlsx”，同時選擇“成績素材”和“課程學(xué)分”工作表，將它們復(fù)制到“成績分析.xlsx”工作簿，關(guān)閉“相關(guān)素材.xlsx”。將“成績分析.xlsx”的“成績素材”工作表重命名為“平均課程績點(diǎn)計(jì)算”。②選擇“平均課程績點(diǎn)計(jì)算”工作表的B3單元格。③單擊“視圖”選項(xiàng)卡/“窗口”組/“凍結(jié)窗格”按鈕的“凍結(jié)拆分窗格”。就實(shí)現(xiàn)了表題、學(xué)生姓名以及課程信息等一直顯示在屏幕上。可以通過鼠標(biāo)左鍵拖動窗口的滾動條來查看凍結(jié)的效果。平均課程績點(diǎn)（不含德育）計(jì)算公式為：，平均課程績點(diǎn)帶兩位小數(shù)位（四舍五入)。具體操作如下：2.實(shí)現(xiàn)過程

2）平均課程績點(diǎn)計(jì)算①選擇I3單元格，輸入“=”，用鼠標(biāo)選取D3單元格，輸入“*”，用鼠標(biāo)選取“課程學(xué)分”工作表的B2單元格，按下F4鍵，將B2轉(zhuǎn)換為絕對地址，此時，I3單元格編輯區(qū)域的內(nèi)容為“=D3*課程學(xué)分!$B$2”。在此基礎(chǔ)上，輸入“+”，重復(fù)前面的操作，完成課程學(xué)分績點(diǎn)=的計(jì)算。I3單元格編輯區(qū)域的內(nèi)容最終為“=D3*課程學(xué)分!$B$2+平均課程績點(diǎn)計(jì)算!E3*課程學(xué)分!$B$3+平均課程績點(diǎn)計(jì)算!F3*課程學(xué)分!$B$4+平均課程績點(diǎn)計(jì)算!G3*課程學(xué)分!$B$5+平均課程績點(diǎn)計(jì)算!H3*課程學(xué)分!$B$6)”。

=

2.實(shí)現(xiàn)過程

2）平均課程績點(diǎn)計(jì)算②剪切I3單元格編輯欄中“=”之后的內(nèi)容，在“=”之后輸入“(”，粘貼剛才剪切的內(nèi)容，再輸入“)”，I3單元格編輯區(qū)域的內(nèi)容為“=(D4*課程學(xué)分!$B$2+平均課程績點(diǎn)計(jì)算!E4*課程學(xué)分!$B$3+平均課程績點(diǎn)計(jì)算!F4*課程學(xué)分!$B$4+平均課程績點(diǎn)計(jì)算!G4*課程學(xué)分!$B$5+平均課程績點(diǎn)計(jì)算!H4*課程學(xué)分!$B$6)”。③將鼠標(biāo)定位在I3單元格編輯區(qū)域中內(nèi)容的最后，輸入“/”。④單擊編輯欄的名稱框區(qū)域，選取SUM函數(shù)（如果沒有顯示SUM函數(shù)，則通過“其他函數(shù)”來選取），如圖所示。2.實(shí)現(xiàn)過程

2）平均課程績點(diǎn)計(jì)算⑤SUM函數(shù)的Number1參數(shù)設(shè)置為“課程學(xué)分!$B$2:$B$6”。I3單元格的內(nèi)容顯示為“3.686666667”。⑥剪切I3單元格編輯欄中“=”之后的內(nèi)容，單擊“插入函數(shù)”按鈕，在I3單元格插入ROUND函數(shù)。⑦ROUND函數(shù)的Number參數(shù)設(shè)置為剪切的內(nèi)容“D3*課程學(xué)分!$B$2+平均課程績點(diǎn)計(jì)算!E3*課程學(xué)分!$B$3+平均課程績點(diǎn)計(jì)算!F3*課程學(xué)分!B4+平均課程績點(diǎn)計(jì)算!G3*課程學(xué)分!$B$5+平均課程績點(diǎn)計(jì)算!H3*課程學(xué)分!$B$6)/SUM(課程學(xué)分!$B$2:$B$6)”，Num_digits參數(shù)設(shè)置為2。I3單元格編輯區(qū)域的內(nèi)容為“=ROUND((D3*課程學(xué)分!$B$2+平均課程績點(diǎn)計(jì)算!E3*課程學(xué)分!$B$3+平均課程績點(diǎn)計(jì)算!F3*課程學(xué)分!$B$4+平均課程績點(diǎn)計(jì)算!G3*課程學(xué)分!$B$5+平均課程績點(diǎn)計(jì)算!H3*課程學(xué)分!$B$6)/SUM(課程學(xué)分!$B$2:$B$6),2)”，I3單元格顯示的結(jié)果為“3.69”。⑧選擇I3單元格，移動鼠標(biāo)，當(dāng)鼠標(biāo)變成填充柄時，雙擊鼠標(biāo)左鍵，完成公式的復(fù)制。由于排序和分類匯總會改變原有數(shù)據(jù)的結(jié)構(gòu)，而篩選會導(dǎo)致數(shù)據(jù)的顯示發(fā)生變化，因此可以將“平均課程績點(diǎn)計(jì)算”工作表復(fù)制成新的工作表。操作步驟如下：2.實(shí)現(xiàn)過程

3）排序、篩選和分類匯總數(shù)據(jù)準(zhǔn)備①選擇“平均課程績點(diǎn)計(jì)算”工作表，復(fù)制為“平均課程績點(diǎn)計(jì)算(2)”工作表。②由于被復(fù)制的工作表引用了“平均課程績點(diǎn)計(jì)算”工作表的數(shù)據(jù)，為防止數(shù)據(jù)混亂，需要將其I列的數(shù)據(jù)復(fù)制后再“值粘貼”。③選擇“平均課程績點(diǎn)計(jì)算(2)”工作表，復(fù)制為“平均課程績點(diǎn)計(jì)算(3)”工作表、“平均課程績點(diǎn)計(jì)算(4)”工作表及“平均課程績點(diǎn)計(jì)算(5)”工作表。④將“平均課程績點(diǎn)計(jì)算(2)”工作表重命名為“成績排序”，將“平均課程績點(diǎn)計(jì)算(3)”工作表重命名為“分類匯總1”，將“平均課程績點(diǎn)計(jì)算(4)”工作表重命名為“分類匯總2”，將“平均課程績點(diǎn)計(jì)算(5)”工作表重命名為“數(shù)據(jù)篩選”。按平均課程績點(diǎn)及德育績點(diǎn)降序排序。操作步驟如下：2.實(shí)現(xiàn)過程

4）成績排序①鼠標(biāo)定位在“成績排序”工作表數(shù)據(jù)區(qū)域的任一單元格。②選擇“數(shù)據(jù)”選項(xiàng)卡/“排序和篩選”組/“排序”按鈕，在彈出的如圖所示對話框中，勾選“數(shù)據(jù)包含標(biāo)題”，選擇主要關(guān)鍵字為“平均課程績點(diǎn)”，排序依據(jù)為“數(shù)值”，次序?yàn)椤敖敌颉薄０雌骄n程績點(diǎn)及德育績點(diǎn)降序排序。操作步驟如下：2.實(shí)現(xiàn)過程

4）成績排序③如圖所示，單擊“添加條件”，選擇次要關(guān)鍵字為“德育績點(diǎn)”，排序依據(jù)為“數(shù)值”，次序?yàn)椤敖敌颉?。要分類匯總每個班的平均課程績點(diǎn)的平均值，需要先按班級排序后再進(jìn)行分類匯總。具體操作如下：2.實(shí)現(xiàn)過程

5）按班級分類匯總平均課程績點(diǎn)平均值①如圖所示，鼠標(biāo)定位在“分類匯總1”工作表B列的任意單元格。選擇“開始”選項(xiàng)卡/“編輯”組/“排序和篩選”按鈕的“升序”，按班級升序排序數(shù)據(jù)。要分類匯總每個班的平均課程績點(diǎn)的平均值，需要先按班級排序后再進(jìn)行分類匯總。具體操作如下：2.實(shí)現(xiàn)過程

5）按班級分類匯總平均課程績點(diǎn)平均值②如圖所示，選擇“數(shù)據(jù)”選項(xiàng)卡/“分級顯示”組/“分類匯總”按鈕，在彈出的對話框中選擇分類字段為“班級”，匯總方式為“平均值”，選定匯總項(xiàng)為“平均課程績點(diǎn)”。③切換到“2級”顯示，就可以看到分類匯總的結(jié)果了。要分類匯總每個班的平均課程績點(diǎn)平均值及班級人數(shù)，就需要先按班級對平均課程績點(diǎn)分類匯總，然后再按班級對姓名進(jìn)行分類匯總。具體操作步驟如下：2.實(shí)現(xiàn)過程

6）按班級分類匯總平均課程績點(diǎn)平均值及人數(shù)①鼠標(biāo)定位在“分類匯總2”工作表B列的任意單元格。選擇“開始”選項(xiàng)卡/“編輯”組/“排序和篩選”按鈕的“升序”，按班級升序排序數(shù)據(jù)。②選擇“數(shù)據(jù)”選項(xiàng)卡/“分級顯示”組/“分類匯總”按鈕，在彈出的對話框中選擇分類字段為“班級”，匯總方式為“平均值”，選定匯總項(xiàng)為“平均課程績點(diǎn)”。要分類匯總每個班的平均課程績點(diǎn)平均值及班級人數(shù)，就需要先按班級對平均課程績點(diǎn)分類匯總，然后再按班級對姓名進(jìn)行分類匯總。具體操作步驟如下：2.實(shí)現(xiàn)過程

6）按班級分類匯總平均課程績點(diǎn)平均值及人數(shù)③如圖所示，再次選擇“數(shù)據(jù)”選項(xiàng)卡/“分級顯示”組/“分類匯總”按鈕，在彈出的對話框中選擇分類字段為“班級”，匯總方式為“計(jì)數(shù)”，選定匯總項(xiàng)為“姓名”，不勾選“替換當(dāng)前分類匯總”（表明在原有的分類匯總基礎(chǔ)上再分類匯總）。④切換到“3級”顯示，就可以看到分類匯總的結(jié)果了。要不改變原始數(shù)據(jù)的前提下統(tǒng)計(jì)每個班的德育績點(diǎn)及平均課程績點(diǎn)的平均值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

7）統(tǒng)計(jì)每個班的德育及平均課程績點(diǎn)平均值①如同所示，鼠標(biāo)定位在“平均課程績點(diǎn)計(jì)算”工作表的任一單元格，選擇“插入”選項(xiàng)卡/“表格”組/“數(shù)據(jù)透視表”按鈕的“數(shù)據(jù)透視表”。要不改變原始數(shù)據(jù)的前提下統(tǒng)計(jì)每個班的德育績點(diǎn)及平均課程績點(diǎn)的平均值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

7）統(tǒng)計(jì)每個班的德育及平均課程績點(diǎn)平均值②彈出如圖所示的“創(chuàng)建數(shù)據(jù)透視表”對話框。在該對話框中，系統(tǒng)已經(jīng)自動選擇要分析的數(shù)據(jù)區(qū)域（用戶也可以自己修改數(shù)據(jù)區(qū)域）。選擇將產(chǎn)生的數(shù)據(jù)透視表放置在新工作表中。要不改變原始數(shù)據(jù)的前提下統(tǒng)計(jì)每個班的德育績點(diǎn)及平均課程績點(diǎn)的平均值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

7）統(tǒng)計(jì)每個班的德育及平均課程績點(diǎn)平均值③在如圖所示的工作表中，選擇要添加到報表的“班級”、“德育績點(diǎn)”及“平均課程績點(diǎn)”字段，就在工作表的左側(cè)生成了每個班的德育及平均課程績點(diǎn)的和。選擇“數(shù)據(jù)透視表工具/設(shè)計(jì)”選項(xiàng)卡/“數(shù)據(jù)透視表樣式”組的“數(shù)據(jù)透視表樣式淺色17”。要不改變原始數(shù)據(jù)的前提下統(tǒng)計(jì)每個班的德育績點(diǎn)及平均課程績點(diǎn)的平均值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

7）統(tǒng)計(jì)每個班的德育及平均課程績點(diǎn)平均值④單擊上圖所示的“求和項(xiàng)：德育績點(diǎn)”旁的下三角，彈出如圖所示的快捷菜單，選擇“值字段設(shè)置(N)…”。要不改變原始數(shù)據(jù)的前提下統(tǒng)計(jì)每個班的德育績點(diǎn)及平均課程績點(diǎn)的平均值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

7）統(tǒng)計(jì)每個班的德育及平均課程績點(diǎn)平均值⑤在彈出的如圖所示的“值字段設(shè)置”對話框中，將計(jì)算類型改為“平均值”。⑥將“求和項(xiàng)：平均課程績點(diǎn)”的計(jì)算類型改為“平均值”，就可以得到每個班的德育及平均課程績點(diǎn)的平均值了。⑦將透視結(jié)果所在的工作表重命名為“數(shù)據(jù)透視表1”。要統(tǒng)計(jì)每個班的德育績點(diǎn)的最大值和最小值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

8）統(tǒng)計(jì)每個班的德育績點(diǎn)的最大值和最小值①鼠標(biāo)定位在“平均課程績點(diǎn)計(jì)算”工作表的任一單元格，選擇“插入”選項(xiàng)卡/“表格”組/“數(shù)據(jù)透視表”按鈕的“數(shù)據(jù)透視表”。②在數(shù)據(jù)透視工作表中，選擇要添加到報表的“班級”及“德育績點(diǎn)”字段。③如圖所示，鼠標(biāo)指向“德育績點(diǎn)”，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“添加到數(shù)值”。要統(tǒng)計(jì)每個班的德育績點(diǎn)的最大值和最小值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

8）統(tǒng)計(jì)每個班的德育績點(diǎn)的最大值和最小值④將數(shù)值中的“求和項(xiàng)：德育績點(diǎn)”的計(jì)算類型改為“最大值”，“求和項(xiàng)：德育績點(diǎn)2”的計(jì)算類型改為“最小值”，就可以得到每個班的德育績點(diǎn)的最大值和最小值了。⑤透視結(jié)果所在的工作表重命名為“數(shù)據(jù)透視表2”。根據(jù)獎學(xué)金評選規(guī)則，可能獲得獎學(xué)金的學(xué)生成績應(yīng)該滿足：德育績點(diǎn)3.0以上，平均課程績點(diǎn)2.88以上，單科課程績點(diǎn)2.2以上。因此，可以篩選出滿足上述條件的數(shù)據(jù)。具體操作步驟如下：2.實(shí)現(xiàn)過程

9）篩選出可能獲得獎學(xué)金的學(xué)生名單①如圖所示，鼠標(biāo)定位在“數(shù)據(jù)篩選”工作表的任一單元格。選擇“數(shù)據(jù)”選項(xiàng)卡/“排序和篩選”組的“篩選”按鈕。根據(jù)獎學(xué)金評選規(guī)則，可能獲得獎學(xué)金的學(xué)生成績應(yīng)該滿足：德育績點(diǎn)3.0以上，平均課程績點(diǎn)2.88以上，單科課程績點(diǎn)2.2以上。因此，可以篩選出滿足上述條件的數(shù)據(jù)。具體操作步驟如下：2.實(shí)現(xiàn)過程

9）篩選出可能獲得獎學(xué)金的學(xué)生名單②如圖所示，單擊C2單元格（德育績點(diǎn)）旁的下三角，在彈出的快捷菜單中選擇“數(shù)字篩選”的“大于或等于(O)…”。根據(jù)獎學(xué)金評選規(guī)則，可能獲得獎學(xué)金的學(xué)生成績應(yīng)該滿足：德育績點(diǎn)3.0以上，平均課程績點(diǎn)2.88以上，單科課程績點(diǎn)2.2以上。因此，可以篩選出滿足上述條件的數(shù)據(jù)。具體操作步驟如下：2.實(shí)現(xiàn)過程

9）篩選出可能獲得獎學(xué)金的學(xué)生名單③在彈出的如圖所示的“自定義自動篩選方式”對話框中，將“德育績點(diǎn)”設(shè)置為“大于或等于3.0”。根據(jù)獎學(xué)金評選規(guī)則，