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

大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）6.4.2任務(wù)實(shí)現(xiàn)第6章Excel綜合應(yīng)用6.4獎(jiǎng)學(xué)金統(tǒng)計(jì)1.任務(wù)分析

（5）用圖表展示每個(gè)班的各等級(jí)獎(jiǎng)學(xué)金人數(shù)，如圖所示。輔導(dǎo)員李老師現(xiàn)在要統(tǒng)計(jì)獎(jiǎng)學(xué)金獲獎(jiǎng)情況，包括：2.實(shí)現(xiàn)過程

1）數(shù)據(jù)準(zhǔn)備在相關(guān)素材中，“獎(jiǎng)學(xué)金名單”工作表存放的是所有獲得獎(jiǎng)學(xué)金的名單，“獎(jiǎng)學(xué)金標(biāo)準(zhǔn)”工作表存放的是各等級(jí)獎(jiǎng)學(xué)金對(duì)應(yīng)的獎(jiǎng)學(xué)金金額。因此，新建“獎(jiǎng)學(xué)金統(tǒng)計(jì).xlsx”工作簿，將上述2張工作表復(fù)制到該工作簿中，并將“獎(jiǎng)學(xué)金名單”工作表重命名為“獎(jiǎng)學(xué)金發(fā)放統(tǒng)計(jì)”。要填寫每個(gè)學(xué)生的獎(jiǎng)學(xué)金數(shù)額，需要根據(jù)每個(gè)人的獲獎(jiǎng)等級(jí)，查找到對(duì)應(yīng)的獎(jiǎng)學(xué)金標(biāo)準(zhǔn)，因此可以用VLOOKUP函數(shù)來實(shí)現(xiàn)。操作步驟如下：

2）計(jì)算學(xué)生應(yīng)發(fā)獎(jiǎng)學(xué)金①選擇“獎(jiǎng)學(xué)金發(fā)放統(tǒng)計(jì)”工作表的E3單元格，單擊“公式”選項(xiàng)卡/“函數(shù)庫”組/“查找與引用”的VLOOKUP函數(shù)。②在“函數(shù)參數(shù)”對(duì)話框中，選擇D3作為Lookup_value參數(shù)值，選擇“獎(jiǎng)學(xué)金標(biāo)準(zhǔn)!$A$2:$B$4”作為Table_array參數(shù)值，Col_index_num設(shè)置為2，Range_lookup是指為0。對(duì)應(yīng)的公式為“=VLOOKUP(D3,獎(jiǎng)學(xué)金標(biāo)準(zhǔn)!$A$2:$B$4,2,0)”。③選擇E3單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。2.實(shí)現(xiàn)過程要從獎(jiǎng)學(xué)金學(xué)生名單的班級(jí)信息（B2:B33單元格區(qū)域）而得到每個(gè)獲獎(jiǎng)班級(jí)的班級(jí)名稱，實(shí)際上可以認(rèn)為是從有重復(fù)數(shù)據(jù)的序列中去掉重復(fù)數(shù)據(jù)，可以使用數(shù)據(jù)篩選的高級(jí)篩選功能來實(shí)現(xiàn)。操作步驟如下：

3）填充班級(jí)名稱①選擇B2:B33單元格區(qū)域。②單擊“數(shù)據(jù)”選項(xiàng)卡/“排序和篩選”組/“高級(jí)”按鈕，彈出如圖所示的“高級(jí)篩選”對(duì)話框。2.實(shí)現(xiàn)過程要從獎(jiǎng)學(xué)金學(xué)生名單的班級(jí)信息（B2:B33單元格區(qū)域）而得到每個(gè)獲獎(jiǎng)班級(jí)的班級(jí)名稱，實(shí)際上可以認(rèn)為是從有重復(fù)數(shù)據(jù)的序列中去掉重復(fù)數(shù)據(jù)，可以使用數(shù)據(jù)篩選的高級(jí)篩選功能來實(shí)現(xiàn)。操作步驟如下：

3）填充班級(jí)名稱③在“高級(jí)篩選”對(duì)話框中，選擇方式為“將篩選結(jié)果復(fù)制到其他位置”，復(fù)制到$G$3，并勾選“選擇不重復(fù)的記錄”。這樣，就從B2:B33單元格區(qū)域篩選出了獲獎(jiǎng)學(xué)生的對(duì)應(yīng)班級(jí)名稱。④選擇G3:G8單元格區(qū)域，將其復(fù)制到G12:G17單元格區(qū)域。2.實(shí)現(xiàn)過程為H3單元格添加“按班級(jí)統(tǒng)計(jì)獎(jiǎng)學(xué)金總額”批注，為G12單元格添加“按班統(tǒng)計(jì)各等級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)人數(shù)”批注。操作步驟如下：

4）添加批注①如圖所示，選擇H3單元格，單擊“審閱”選項(xiàng)卡/“批注”組/“新建批注”按鈕，在對(duì)應(yīng)的批注框中輸入“按班級(jí)統(tǒng)計(jì)獎(jiǎng)學(xué)金總額”。此時(shí)，“新建批注”按鈕變成“編輯批注”。2.實(shí)現(xiàn)過程為H3單元格添加“按班級(jí)統(tǒng)計(jì)獎(jiǎng)學(xué)金總額”批注，為G12單元格添加“按班統(tǒng)計(jì)各等級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)人數(shù)”批注。操作步驟如下：

4）添加批注②選擇“按班級(jí)統(tǒng)計(jì)獎(jiǎng)學(xué)金總額”文本，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“設(shè)置批注格式”將其設(shè)置為紅色，加粗顯示。③調(diào)整批注欄到合適大小。④單擊“審閱”選項(xiàng)卡/“批注”組/“顯示所有批注”按鈕，將批注顯示在屏幕上。⑤為G12單元格添加“按班統(tǒng)計(jì)各等級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)人數(shù)”批注。2.實(shí)現(xiàn)過程要統(tǒng)計(jì)每個(gè)班的獎(jiǎng)學(xué)金總額，實(shí)際上是對(duì)獲獎(jiǎng)名單按“班級(jí)”來對(duì)“應(yīng)發(fā)獎(jiǎng)學(xué)金”進(jìn)行求和。例如，要統(tǒng)計(jì)“13電子1”的獎(jiǎng)學(xué)金總額，就是要將獲獎(jiǎng)名單中屬于“13電子1”的數(shù)據(jù)過濾出來，然后將過濾出來的“應(yīng)發(fā)獎(jiǎng)學(xué)金”相加即可。上述要求可以使用SUMIF函數(shù)。操作步驟如下：

5）統(tǒng)計(jì)每個(gè)班的獎(jiǎng)學(xué)金總額2.實(shí)現(xiàn)過程①選擇H4單元格，單擊單元格編輯欄前的插入函數(shù)按鈕，通過查找，找到并插入SUMIF函數(shù)，彈出如圖所示的“函數(shù)參數(shù)”對(duì)話框。

5）統(tǒng)計(jì)每個(gè)班的獎(jiǎng)學(xué)金總額2.實(shí)現(xiàn)過程②選擇B3:B33單元格區(qū)域作為Range參數(shù)值，G4單元格作為Criteria參數(shù)值，E3:E33單元格區(qū)域作為Sum_range參數(shù)。其含義是：將B3:B33單元格區(qū)域中等于G4單元格內(nèi)容的行過濾出來，將過濾得到的行所對(duì)應(yīng)的E列單元格區(qū)域中的值相加作為結(jié)果。由于在公式復(fù)制時(shí)，B3:B33單元格區(qū)域和E3:E33單元格區(qū)域都是不應(yīng)該變化的，因此將其轉(zhuǎn)化為絕對(duì)引用。H4單元格的最終公式為“=SUMIF($B$3:$B$33,G4,$E$3:$E$33)”。③選擇H4單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。要統(tǒng)計(jì)每個(gè)班獎(jiǎng)學(xué)金各等級(jí)的人數(shù)，實(shí)際上是對(duì)獲獎(jiǎng)名單按“班級(jí)”及“獎(jiǎng)學(xué)金資格”來匯總對(duì)應(yīng)的單元格個(gè)數(shù)。例如，要統(tǒng)計(jì)“13電子1”獲得“一等獎(jiǎng)”的人數(shù)，就是要將獲獎(jiǎng)名單中屬于“13電子1”的數(shù)據(jù)過濾出來，然后在過濾出來的數(shù)據(jù)中再用“一等獎(jiǎng)”進(jìn)行過濾，最后統(tǒng)計(jì)過濾出來的“一等獎(jiǎng)”單元格數(shù)。上述要求可以使用COUNTIFS函數(shù)。操作步驟如下：

6）統(tǒng)計(jì)每個(gè)班獎(jiǎng)學(xué)金各等級(jí)的人數(shù)2.實(shí)現(xiàn)過程①選擇H13單元格，單擊“公式”選項(xiàng)卡/“函數(shù)庫”組/“其他函數(shù)”按鈕的“統(tǒng)計(jì)”下面的COUNTIFS函數(shù)，彈出如圖所示的“函數(shù)參數(shù)”對(duì)話框。

6）統(tǒng)計(jì)每個(gè)班獎(jiǎng)學(xué)金各等級(jí)的人數(shù)2.實(shí)現(xiàn)過程②選擇$B$3:$B$33單元格區(qū)域作為Criteria_range1參數(shù)值，選擇G13作為Criteria1參數(shù)值，選擇$D$3:$D$33單元格區(qū)域作為Criteria_range2參數(shù)值，輸入“一等獎(jiǎng)”作為Criteria2參數(shù)值，其含義是：將$B$3:$B$33單元格區(qū)域中等于G13單元格內(nèi)容的數(shù)據(jù)過濾出來，將過濾得到數(shù)據(jù)中$D$3:$D$33單元格區(qū)域中等于“一等獎(jiǎng)”的數(shù)據(jù)再過濾出來，最后統(tǒng)計(jì)過濾出來的$D$3:$D$33單元格區(qū)域中的單元格數(shù)。H13單元格的最終公式為“=COUNTIFS($B$3:$B$33,G13,$D$3:$D$33,"一等獎(jiǎng)")”。

6）統(tǒng)計(jì)每個(gè)班獎(jiǎng)學(xué)金各等級(jí)的人數(shù)2.實(shí)現(xiàn)過程③選擇H13單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。④I13單元格的最終公式為“=COUNTIFS($B$3:$B$33,G13,$D$3:$D$33,"二等獎(jiǎng)")”。⑤選擇I13單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。⑥J13單元格的最終公式為“=COUNTIFS($B$3:$B$33,G13,$D$3:$D$33,"三等獎(jiǎng)")”。⑦選擇J13單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。要制作如圖所示的各班級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)情況對(duì)比圖，可以用“三維柱形圖”來實(shí)現(xiàn)。操作步驟如下：

7）制作各班級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)情況對(duì)比圖2.實(shí)現(xiàn)過程①選擇G12:J17單元格區(qū)域的任一單元格。②單擊“插入”選項(xiàng)卡/“圖表”組/“柱形圖”按鈕，在彈出的菜單中選擇“三維柱形圖”的“三維簇狀柱形圖”。③單擊“圖表工具/設(shè)計(jì)”選項(xiàng)卡/“圖表布局”組/“布局5”按鈕。④將圖表標(biāo)題改為“2013級(jí)電子信息工程專業(yè)獎(jiǎng)學(xué)金統(tǒng)計(jì)圖”。要制作如圖所示的各班級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)情況對(duì)比圖，可以用“三維柱形圖”來實(shí)現(xiàn)。操作步驟如下：

7）制作各班級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)情況對(duì)比圖2.實(shí)現(xiàn)過程⑤將縱坐標(biāo)軸標(biāo)題改為“獲獎(jiǎng)人數(shù)”。⑥單擊“圖表工具/布局”選項(xiàng)卡/“坐標(biāo)軸”組/“數(shù)據(jù)