大學(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成績(jī)分析1.任務(wù)分析

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

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

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

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

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

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

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

=

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

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

2）平均課程績(jī)點(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+平均課程績(jī)點(diǎn)計(jì)算!E3*課程學(xué)分!$B$3+平均課程績(jī)點(diǎn)計(jì)算!F3*課程學(xué)分!B4+平均課程績(jī)點(diǎn)計(jì)算!G3*課程學(xué)分!$B$5+平均課程績(jī)點(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+平均課程績(jī)點(diǎn)計(jì)算!E3*課程學(xué)分!$B$3+平均課程績(jī)點(diǎn)計(jì)算!F3*課程學(xué)分!$B$4+平均課程績(jī)點(diǎn)計(jì)算!G3*課程學(xué)分!$B$5+平均課程績(jī)點(diǎn)計(jì)算!H3*課程學(xué)分!$B$6)/SUM(課程學(xué)分!$B$2:$B$6),2)”，I3單元格顯示的結(jié)果為“3.69”。⑧選擇I3單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成公式的復(fù)制。由于排序和分類匯總會(huì)改變?cè)袛?shù)據(jù)的結(jié)構(gòu)，而篩選會(huì)導(dǎo)致數(shù)據(jù)的顯示發(fā)生變化，因此可以將“平均課程績(jī)點(diǎn)計(jì)算”工作表復(fù)制成新的工作表。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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