大學計算機基礎 課件 6.2.2 任務實現(xiàn)

大學計算機基礎——基于計算思維（Windows10+Office2016）6.2.2任務實現(xiàn)第6章Excel綜合應用6.2成績分析1.任務分析

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

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

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

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

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

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

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

=

2.實現(xiàn)過程

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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