大學計算機基礎——基于計算思維（Windows10+Office2016）5.4.2任務實現第5章Excel基本應用5.4課程成績計算1.任務分析

（1）將“相關素材.xlsx”工作簿中的成績空白表復制到“大學計算機成績.xlsx”工作簿中的“課堂考勤成績”工作表之后，將其命名為“課程成績”工作表，并設置出勤成績、課堂表現、課后實訓以及大作業的比重分別為20%，20%，20%，40%。并將學生的出勤成績、課堂表現、課后實訓、大作業以及期末考試成績復制到相應位置。制作如圖所示的表格，要求：（2）計算學生的平時成績及總評成績（平時成績和總評成績四舍五入為整數）。平時成績=出勤成績×出勤成績比重+課堂表現×課堂表現比重+課后實訓×課后實訓比重+大作業×大作業比重，總評成績=平時成績×平時成績比重+期末成績×（1-平時成績比重）。1.任務分析

（3）計算學生的課程績點。課程總評成績為100分的課程績點為4.0，60分的課程績點為1.0，60分以下課程績點為0，課程績點帶一位小數。60分～100分間對應的績點計算公式如下：制作如圖所示的表格，要求：

（4）計算學生的總評等級。總評成績>=90分計為A，總評成績>=80分計為B，總評成績>=70分計為C，總評成績>=60分計為D，其他計為F。（5）根據學生的總評成績進行排名。1.任務分析

（6）將班級前10名的數據用灰色底紋進行標注。制作如圖所示的表格，要求：（7）鎖定平時成績、總評成績、課程績點、總評等級、總評排名區域，防止誤輸入。（8）對“課程成績”工作表進行打印設置。打印要求為：上、下頁邊距為3cm，左、右頁邊距為2cm，紙張采用B5紙橫向、水平居中打印。如果成績單需要分多頁打印，則每頁需要打印標題（第1行到第4行）信息。2.實現過程

1）工作表制作完成如圖所示的工作表的樣式及內容設置。操作步驟如下：①打開“相關素材.xlsx”工作簿，選擇“課程成績空白表”工作表，鼠標定位在工作表名稱處，單擊鼠標右鍵，在彈出的快捷菜單中選擇“移動或復制（M）…”。在如圖所示的“移動或復制工作表”對話框中，選擇工作表移至“大學計算機成績.xlsx”工作簿的“Sheet2”工作表之前，并勾選“建立副本”。2.實現過程

1）工作表制作完成如圖所示的工作表的樣式及內容設置。操作步驟如下：②將“大學計算機成績.xlsx”工作簿的“課程成績空白表”工作表重命名為“課程成績”工作表。③選擇“課程成績”工作表的C5:F5單元格區域，將其數據格式設為“百分比”，通過“減少小數位數”按鈕，設置小數位數為0，如圖所示。2.實現過程

1）工作表制作完成如圖所示的工作表的樣式及內容設置。操作步驟如下：④選擇C6單元格，輸入“=”，切換到“課堂考勤成績”工作表，選擇O5單元格，按下回車鍵確認輸入，C6單元格編輯欄區域的內容為“=課堂考勤成績!O5”，表示C6單元格中的出勤成績引用自“課堂考勤成績”工作表的O5單元格。選擇C6單元格，移動鼠標，當鼠標變成黑色填充柄時，雙擊鼠標左鍵，完成公式的復制。⑤在不改變表格格式的前提下將“相關素材.xlsx”工作簿的“成績數據素材”工作表中的課堂表現、課后實訓、大作業以及期末考試成績復制到相應位置。平時成績=∑?〖各項成績×各項成績比重〗，總評成績=平時成績×平時成績比重+期末成績×（1-平時成績比重）。由于在公式復制時，各項比重的引用地址不隨公式位置的變化而變化，因此需要使用絕對地址。操作步驟如下：2.實現過程

2）計算平時成績及總評成績①選擇G6單元格，輸入“=”，用鼠標選取C6單元格，輸入“*”，用鼠標選取C5單元格，按下F4鍵，將C5轉換為絕對地址，此時，G6單元格編輯區域的內容為“=C6*$C$5”。在此基礎上，輸入“+”，重復前面的操作，完成平時成績=∑?〖各項成績×各項成績比重〗的計算。G6單元格編輯區域的內容最終為“=C6*$C$5+D6*$D$5+E6*$E$5+F6*$F$5”。②選擇I6單元格，完成總評成績的計算。I6單元格編輯區域的內容最終為“=G6*$L$3+H6*(1-$L$3)”。2.實現過程

3）將平時成績和總評成績四舍五入為整數①選擇G6單元格編輯欄中“=”之后的內容，按下Ctrl+X鍵，將平時成績計算公式（C6*$C$5+D6*$D$5+E6*$E$5+F6*$F$5）剪切下來。單擊“公式”選項卡/“函數庫”組/“數學與三角函數”的ROUND函數，彈出如圖5-48所示的函數參數對話框。將鼠標定位在Number區域，按下Ctrl+V鍵，將平時成績計算公式粘貼到此處。在Num_digits區域輸入0，單擊確定按鈕。G6單元格編輯區域的內容最終為“=ROUND(C6*$C$5+D6*$D$5+E6*$E$5+F6*$F$5,0)”。2.實現過程

3）將平時成績和總評成績四舍五入為整數②對I6單元格中的內容進行四舍五入取整運算。I6單元格編輯區域的內容最終為“=ROUND(G6*$L$3+H6*(1-$L$3),0)”。③分別選擇G6和I6單元格，移動鼠標，當鼠標變成黑色填充柄時，雙擊鼠標左鍵，完成函數及公式的復制。課程績點計算公式

結果帶一位小數（四舍五入）。操作步驟如下：2.實現過程

4）計算成績績點

課程績點計算公式

結果帶一位小數（四舍五入）。操作步驟如下：2.實現過程

4）計算成績績點

③選擇J6單元格，移動鼠標，當鼠標變成黑色填充柄時，雙擊鼠標左鍵，完成函數及公式的復制。④選擇J6:J41單元格區域，設置其數值格式為“數字，帶1位小數”。由于總評成績>=90分計為A，總評成績>=80分計為B，總評成績>=70分計為C，總評成績>=60分計為D，其他計為F，因此可以用IF嵌套來實現，對應的邏輯結構如圖所示。2.實現過程

5）計算總評等級操作步驟如下：①選擇K6單元格，單擊“公式”選項卡/“函數庫”組/“邏輯”的IF函數，在對應的參數區域輸入如表所示的第1行參數后，將光標定位在Value_if_false區域，選擇需要嵌入的函數IF，將彈出第2個IF函數，如圖所示。2.實現過程

5）計算總評等級IF函數層次Logical_testValue_if_trueValue_if_false1I6>=90A光標定位在此處，選擇第2層IF2I6>=80B光標定位在此處，選擇第3層IF3I6>=70C光標定位在此處，選擇第4層IF4I6>=60DF光標定位在這里后進行函數選擇②在第2個IF函數中對應的參數區域輸入如表所示的第2行參數后，再將光標定位在Value_if_false區域，選擇需要嵌入的函數IF，將彈出第3個IF函數。③在第3個IF函數中對應的參數區域輸入如表所示的第3行參數后，再將光標定位在Value_if_false區域，選擇需要嵌入的函數IF，將彈出第4個IF函數。④在第4個IF函數中對應的參數區域輸入如表所示的第4行參數。K6單元格編輯欄的最終內容為“=IF(I6>=90,"A",IF(I6>=80,"B",IF(I6>=70,"C",IF(I6>=60,"D","F"))))”。⑤選擇K6單元格，移動鼠標，當鼠標變成黑色填充柄時，雙擊鼠標左鍵，完成函數及公式的復制。2.實現過程

5）計算總評等級根據學生的總評成績進行排名，分數最高的排名第1。可以用RANK.EQ函數來實現，參與排位的數據區域需要使用絕對地址。操作步驟如下：2.實現過程

6）總評成績排名①選擇L6單元格，單擊單元格編輯欄前的“插入函數”按鈕，在彈出的“插入函數”對話框中選擇“統計函數”的RANK.EQ函數。2.實現過程

6）總評成績排名②在如圖所示的RANK.EQ函數的Number參數處輸入“I6”，Ref參數處選擇單元格區域I6:I41，選擇Ref參數I6:I41，按下F4鍵，將其轉變為絕對地址區域$I$6:$I$41，在Order參數處輸入“0”。L6單元格編輯欄的最終內容為“=RANK.EQ(I6,$I$6:$I$41,0)”，表示按降序方式計算I6在單元格區域$I$6:$I$41中的排名。2.實現過程

6）總評成績排名③選擇L6單元格，移動鼠標，當鼠標變成黑色填充柄時，雙擊鼠標左鍵，完成函數及公式的復制。可以看到，在所有RANK.EQ中，Number參數隨著單元格位置的變化而變化，而Ref的參數都為$I$6:$I$41，表示要計算的總是在$I$6:$I$41區域中的排名。將班級前10名的數據用灰色底紋標識出來，可以用條件格式來實現。操作步驟如下：2.實現過程

7）特別標注信息①選擇單元格區域A6:L41，單擊“開始”選項卡/“格式”組/“條件格式”按鈕的“新建規則”。②在彈出的如圖所示的“新建格式規則”對話框中，選擇規則類型為“使用公式確定要設置格式的單元格”。單擊“為符合此公式的值設置格式”區域，選擇L6單元格，按下F4鍵兩次，編輯規則公式顯示為“=$L6”，在其后面輸入“<=10”，編輯規則公式為“=$L6<=10”。選擇“格式”，設置為“灰色底紋”，單擊“確定”按鈕。將班級前10名的數據用灰色底紋標識出來，可以用條件格式來實現。操作步驟如下：2.實現過程

7）特別標注信息可以看到，總評排名為前10名的所有單元格都用灰色底紋進行標注了。公式“=$L6<=10”指明的條件格式為：如果對應行的第L列的值<=10，則對所有符合條件的單元格區域用灰色底紋標識出來了。由于平時成績、期末成績、總評成績、成績績點、總評等級以及總評排名等是通過公式和函數計算出來的，為防止誤修改，可以對這些指定的單元格進行鎖定。操作步驟如下：2.實現過程

8）利用審閱功能鎖定單元格①鼠標單擊“課程成績”工作表的行標題和列標題交接處圖標，單擊鼠標右鍵，在彈出的快捷菜單中選擇“設置單元格格式”。②在如圖所示的“設置單元格格式”對話框中，切換到“保護”選項卡，不勾選“鎖定”和“隱藏”復選框，單擊確定。2.實現過程

8）利用審閱功能鎖定單元格③選擇不允許編輯的單元格區域（G6:G41，I6:L41），單擊鼠標右鍵，選擇“設置單元格格式”，在格式設置中，勾選“鎖定”復選框。④選擇當前工作表的任一單元格，切換到“審閱”選項卡/“保護”組/“保護工作表”按鈕，在彈出的如圖所示的“設置保護工作表”對話框中，選取“保護工作表及鎖定的單元格內容”復選框，勾選“選取鎖定單元格”和“選定解除鎖定的單元格”復選框，清除其他復選框，單擊確定。這樣，平時成績等區域中的單元格就不能被編輯了。①單擊“頁面布局”選項卡/“頁面設置”組/“頁邊距”按鈕的“自定義邊距”，在彈出的“頁面設置”對話框的“頁邊距”選項卡中，設置上、下頁邊距為3cm，左、右頁邊距為2cm，勾選“水平”居中方式。②單擊“頁面布局”選項卡/“頁面設置”組/“紙張方向”按鈕的“橫向”。③單擊“頁面布局”選項框/“頁面設置”組/“紙張大小”按鈕的“B5（JIS）”。④單擊“頁面布局”選項卡/“頁面設置”組/“打印標題”按鈕，在彈出的“頁面設置”對話框的“工作表”選項卡中，光標定位在“頂端標題行”輸入框，用鼠標選定1