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

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

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

（3）計(jì)算學(xué)生的課程績(jī)點(diǎn)。課程總評(píng)成績(jī)?yōu)?00分的課程績(jī)點(diǎn)為4.0，60分的課程績(jī)點(diǎn)為1.0，60分以下課程績(jī)點(diǎn)為0，課程績(jī)點(diǎn)帶一位小數(shù)。60分～100分間對(duì)應(yīng)的績(jī)點(diǎn)計(jì)算公式如下：制作如圖所示的表格，要求：

（4）計(jì)算學(xué)生的總評(píng)等級(jí)?？傇u(píng)成績(jī)>=90分計(jì)為A，總評(píng)成績(jī)>=80分計(jì)為B，總評(píng)成績(jī)>=70分計(jì)為C，總評(píng)成績(jī)>=60分計(jì)為D，其他計(jì)為F。（5）根據(jù)學(xué)生的總評(píng)成績(jī)進(jìn)行排名。1.任務(wù)分析

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

1）工作表制作完成如圖所示的工作表的樣式及內(nèi)容設(shè)置。操作步驟如下：①打開(kāi)“相關(guān)素材.xlsx”工作簿，選擇“課程成績(jī)空白表”工作表，鼠標(biāo)定位在工作表名稱處，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“移動(dòng)或復(fù)制（M）…”。在如圖所示的“移動(dòng)或復(fù)制工作表”對(duì)話框中，選擇工作表移至“大學(xué)計(jì)算機(jī)成績(jī).xlsx”工作簿的“Sheet2”工作表之前，并勾選“建立副本”。2.實(shí)現(xiàn)過(guò)程

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

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

2）計(jì)算平時(shí)成績(jī)及總評(píng)成績(jī)①選擇G6單元格，輸入“=”，用鼠標(biāo)選取C6單元格，輸入“*”，用鼠標(biāo)選取C5單元格，按下F4鍵，將C5轉(zhuǎn)換為絕對(duì)地址，此時(shí)，G6單元格編輯區(qū)域的內(nèi)容為“=C6*$C$5”。在此基礎(chǔ)上，輸入“+”，重復(fù)前面的操作，完成平時(shí)成績(jī)=∑?〖各項(xiàng)成績(jī)×各項(xiàng)成績(jī)比重〗的計(jì)算。G6單元格編輯區(qū)域的內(nèi)容最終為“=C6*$C$5+D6*$D$5+E6*$E$5+F6*$F$5”。②選擇I6單元格，完成總評(píng)成績(jī)的計(jì)算。I6單元格編輯區(qū)域的內(nèi)容最終為“=G6*$L$3+H6*(1-$L$3)”。2.實(shí)現(xiàn)過(guò)程

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

3）將平時(shí)成績(jī)和總評(píng)成績(jī)四舍五入為整數(shù)②對(duì)I6單元格中的內(nèi)容進(jìn)行四舍五入取整運(yùn)算。I6單元格編輯區(qū)域的內(nèi)容最終為“=ROUND(G6*$L$3+H6*(1-$L$3),0)”。③分別選擇G6和I6單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。課程績(jī)點(diǎn)計(jì)算公式

結(jié)果帶一位小數(shù)（四舍五入）。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

4）計(jì)算成績(jī)績(jī)點(diǎn)

課程績(jī)點(diǎn)計(jì)算公式

結(jié)果帶一位小數(shù)（四舍五入）。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

4）計(jì)算成績(jī)績(jī)點(diǎn)

③選擇J6單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。④選擇J6:J41單元格區(qū)域，設(shè)置其數(shù)值格式為“數(shù)字，帶1位小數(shù)”。由于總評(píng)成績(jī)>=90分計(jì)為A，總評(píng)成績(jī)>=80分計(jì)為B，總評(píng)成績(jī)>=70分計(jì)為C，總評(píng)成績(jī)>=60分計(jì)為D，其他計(jì)為F，因此可以用IF嵌套來(lái)實(shí)現(xiàn)，對(duì)應(yīng)的邏輯結(jié)構(gòu)如圖所示。2.實(shí)現(xiàn)過(guò)程

5）計(jì)算總評(píng)等級(jí)操作步驟如下：①選擇K6單元格，單擊“公式”選項(xiàng)卡/“函數(shù)庫(kù)”組/“邏輯”的IF函數(shù)，在對(duì)應(yīng)的參數(shù)區(qū)域輸入如表所示的第1行參數(shù)后，將光標(biāo)定位在Value_if_false區(qū)域，選擇需要嵌入的函數(shù)IF，將彈出第2個(gè)IF函數(shù)，如圖所示。2.實(shí)現(xiàn)過(guò)程

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

5）計(jì)算總評(píng)等級(jí)根據(jù)學(xué)生的總評(píng)成績(jī)進(jìn)行排名，分?jǐn)?shù)最高的排名第1?？梢杂肦ANK.EQ函數(shù)來(lái)實(shí)現(xiàn)，參與排位的數(shù)據(jù)區(qū)域需要使用絕對(duì)地址。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

6）總評(píng)成績(jī)排名①選擇L6單元格，單擊單元格編輯欄前的“插入函數(shù)”按鈕，在彈出的“插入函數(shù)”對(duì)話框中選擇“統(tǒng)計(jì)函數(shù)”的RANK.EQ函數(shù)。2.實(shí)現(xiàn)過(guò)程

6）總評(píng)成績(jī)排名②在如圖所示的RANK.EQ函數(shù)的Number參數(shù)處輸入“I6”，Ref參數(shù)處選擇單元格區(qū)域I6:I41，選擇Ref參數(shù)I6:I41，按下F4鍵，將其轉(zhuǎn)變?yōu)榻^對(duì)地址區(qū)域$I$6:$I$41，在Order參數(shù)處輸入“0”。L6單元格編輯欄的最終內(nèi)容為“=RANK.EQ(I6,$I$6:$I$41,0)”，表示按降序方式計(jì)算I6在單元格區(qū)域$I$6:$I$41中的排名。2.實(shí)現(xiàn)過(guò)程

6）總評(píng)成績(jī)排名③選擇L6單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制?？梢钥吹?，在所有RANK.EQ中，Number參數(shù)隨著單元格位置的變化而變化，而Ref的參數(shù)都為$I$6:$I$41，表示要計(jì)算的總是在$I$6:$I$41區(qū)域中的排名。將班級(jí)前10名的數(shù)據(jù)用灰色底紋標(biāo)識(shí)出來(lái)，可以用條件格式來(lái)實(shí)現(xiàn)。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

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

7）特別標(biāo)注信息可以看到，總評(píng)排名為前10名的所有單元格都用灰色底紋進(jìn)行標(biāo)注了。公式“=$L6<=10”指明的條件格式為：如果對(duì)應(yīng)行的第L列的值<=10，則對(duì)所有符合條件的單元格區(qū)域用灰色底紋標(biāo)識(shí)出來(lái)了。由于平時(shí)成績(jī)、期末成績(jī)、總評(píng)成績(jī)、成績(jī)績(jī)點(diǎn)、總評(píng)等級(jí)以及總評(píng)排名等是通過(guò)公式和函數(shù)計(jì)算出來(lái)的，為防止誤修改，可以對(duì)這些指定的單元格進(jìn)行鎖定。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

8）利用審閱功能鎖定單元格①鼠標(biāo)單擊“課程成績(jī)”工作表的行標(biāo)題和列標(biāo)題交接處圖標(biāo)，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“設(shè)置單元格格式”。②在如圖所示的“設(shè)置單元格格式”對(duì)話框中，切換到“保護(hù)”選項(xiàng)卡，不勾選“鎖定”和“隱藏”復(fù)選框，單擊確定。2.實(shí)現(xiàn)過(guò)程

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