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

大學計算機基礎——基于計算思維（Windows10+Office2016）6.3.2任務實現(xiàn)第6章Excel綜合應用6.3獎學金評定（1）確定其級別身份為“主要干部”、“一般干部”還是“無”。如圖所示。（2）根據(jù)獎學金條件限制的單科課程績點要求，分別統(tǒng)計單科課程績點>=2.5的門次，單科課程績點∈[2.4,2.5)的門次以及單科課程績點∈[2.2,2.5)的門次。1.任務分析

要計算某學生是否獲得獎學金，需要：

（3）根據(jù)級別身份，計算每個學生達到獎學金要求的課程門次，如表所示。1.任務分析要計算某學生是否獲得獎學金，需要：表6-1達到獎學金要求的課程門次級別身份達到獎學金要求的課程門次主要干部單科課程績點>=2.5的門次+最多2門單科課程績點∈[2.2,2.5)一般干部單科課程績點>=2.5的門次+最多2門單科課程績點∈[2.4,2.5)無單科課程績點>=2.5的門次

（4）獎學金資格判定：如果達到獎學金要求的課程門次=修讀課程門次，則表明其有評選獎學金資格，否則就沒有評選獎學金資格。（5）篩選出有獎學金資格的名單，并對名單按平均課程績點排序，當平均課程績點相同時，按德育績點排序。（6）計算獎學金名額。一等獎學金名額不超過專業(yè)總?cè)藬?shù)2％（含，下同），二等獎學金名額不超過專業(yè)總?cè)藬?shù)5％，三等獎學金名額不超過專業(yè)總?cè)藬?shù)8％，人數(shù)出現(xiàn)小數(shù)的，采用去尾法計算。（7）按給定名額數(shù)量，對符合獎學金的學生進行評定。（8）確定最終獎學金名單，并給出按姓名查詢詳細情況。1.任務分析

在相關素材中，“職務級別”工作表存放的是如右上圖所示的職務與級別的對應關系，“任職一覽表”工作表存放的是如左上圖所示的所有擔任學生干部的名單，“獎學金計算”工作表存放的是如右下圖所示的篩選出來的可能符合獎學金評選條件的名單。因此，新建“獎學金評定.xlsx”工作簿，將上述3張工作表復制到該工作簿中。2.實現(xiàn)過程1）數(shù)據(jù)準備根據(jù)“職務級別”工作表的對應關系，將“任職一覽表”工作表中學生的擔任職務轉(zhuǎn)換為對應的職務級別。要得到“陳俊曄”同學的職務級別（D3），就需要根據(jù)他的“擔任職務”（C3單元格），在“職務級別”工作表中的!$A$2:$B$18單元格區(qū)域去查找。操作步驟如下：

2.實現(xiàn)過程2）確定擔任職務的對應級別①選擇“任職一覽表”工作表的D3單元格，單擊“公式”選項卡/“函數(shù)庫”組/“查找與引用”按鈕的VLOOKUP函數(shù)，彈出如圖所示的“函數(shù)參數(shù)”對話框。

2.實現(xiàn)過程2）確定擔任職務的對應級別②在“函數(shù)參數(shù)”對話框中，選擇C3作為Lookup_value參數(shù)值，選擇職務級別!$A$2:$B$18作為Table_array參數(shù)值，Col_index_num設置為2，Range_lookup是指為0。對應的公式為“=VLOOKUP(C3,職務級別!$A$2:$B$18,2,0)”，其含義是：根據(jù)當前C3單元格的值，去單元格區(qū)域!$A$2:$B$18的第一列查找，如果找到匹配項，則將單元格區(qū)域!$A$2:$B$18的對應匹配項所在行的第二列的值填充到當前位置。③選擇D3單元格，移動鼠標，當鼠標變成黑色填充柄時，雙擊鼠標左鍵，完成函數(shù)及公式的復制?？梢酝ㄟ^在“獎學金計算”工作表運用VLOOKUP函數(shù)，將“任職一覽表”中的相應數(shù)據(jù)查找出來。操作步驟如下：

2.實現(xiàn)過程3）確定獎學金名單的級別身份①如圖所示，選擇“任職一覽表”工作表的A3:D57單元格區(qū)域，單擊“公式”選項卡/“定義的名稱”組的“定義名稱”按鈕，在彈出的“新建名稱”對話框中，將其命名為“職務信息”。這樣，需要引用“任職一覽表”工作表A3:D57單元格區(qū)域的地方都可以用“職務信息”名稱來替代。

2.實現(xiàn)過程3）確定獎學金名單的級別身份③如圖所示，將鼠標定位在Table_array參數(shù)區(qū)域，單擊“公式”選項卡/“定義的名稱”組/“用于公式”的“職務信息”，將名稱“職務信息”作為Table_array參數(shù)值。對應的公式為“=VLOOKUP(A4,職務信息,4,0)”，結果顯示為“#N/A”。②選擇“獎學金計算”工作表的C4單元格，單擊“公式”選項卡/“函數(shù)庫”組/“查找與引用”的VLOOKUP函數(shù)，選擇A4作為Lookup_value參數(shù)值，Col_index_num設置為4，Range_lookup是指為0。

2.實現(xiàn)過程3）確定獎學金名單的級別身份⑤選擇C4單元格，單擊“公式”選項卡/“函數(shù)庫”組/“邏輯”的IFERROR函數(shù)。在如圖所示的對話框中，Value值為剪切的內(nèi)容（“VLOOKUP(A4,職務信息,4,0)”），Value_if_error值為“無”。對應的公式為“=IFERROR(VLOOKUP(A4,職務信息,4,0),"無")”，結果顯示為“無”。⑥選擇C4單元格，移動鼠標，當鼠標變成黑色填充柄時，雙擊鼠標左鍵，完成函數(shù)及公式的復制。④將C4單元格編輯欄區(qū)域中“=”之后的內(nèi)容剪切下來。根據(jù)獎學金條件限制的單科課程績點要求，分別統(tǒng)計單科課程績點>=2.5的門次，單科課程績點∈[2.4,2.5)的門次以及單科課程績點∈[2.2,2.5)的門次，可以采用COUNTIF及COUNTIFS函數(shù)來實現(xiàn)。操作步驟如下：

2.實現(xiàn)過程4）統(tǒng)計單科課程績點范圍①K4單元格的公式為“=COUNTIF(E4:I4,">=2.5")”。②L4單元格的公式為“=COUNTIFS(E4:I4,"<2.5",E4:I4,">=2.4")”。③M4單元格的公式為“=COUNTIFS(E4:I4,"<2.5",E4:I4,">=2.2")”。④分別選擇K4、L4及M4單元格，移動鼠標，當鼠標變成黑色填充柄時，雙擊鼠標左鍵，完成函數(shù)及公式的復制。根據(jù)級別身份，計算每個學生達到獎學金要求的課程門次。由于“主要干部”最多可以有2門單科課程績點∈[2.2,2.5)，“一般干部”最多可以有2門單科課程績點∈[2.4,2.5)，因此可以用IF函數(shù)來對不同級別身份身份進行判斷，而MIN函數(shù)則可以求出最多可以有2門單科課程績點∈[2.2,2.5)和最多可以有2門單科課程績點∈[2.4,2.5)，其邏輯表達如表所示。操作步驟如下：

2.實現(xiàn)過程5）符合獎學金課程門次計算表6-2達到獎學金要求的課程門次計算邏輯級別身份達到獎學金要求的課程門次主要干部單科課程績點>=2.5的門次+MIN(單科課程績點∈[2.2,2.5)的門次,2)一般干部單科課程績點>=2.5的門次+MIN(單科課程績點∈[2.4,2.5)的門次,2)無單科課程績點>=2.5的門次

2.實現(xiàn)過程5）符合獎學金課程門次計算①選擇N4單元格，插入IF函數(shù)，IF函數(shù)的Logical_test的值為“C4="主要干部"”，鼠標定位在Value_if_true區(qū)域，選擇K4單元格，然后輸入“+”，在名稱框選擇需要嵌入的MIN函數(shù)，如圖所示。

2.實現(xiàn)過程5）符合獎學金課程門次計算②在如圖所示的MIN函數(shù)的對話框中，Number1區(qū)域選擇M4單元格，Number2的值輸入2。

2.實現(xiàn)過程5）符合獎學金課程門次計算③單擊確定后，鼠標定位在N4單元格編輯區(qū)域的IF中間位置，單擊編輯欄前的“插入函數(shù)”圖標，彈出IF函數(shù)對話框。在Value_if_false的輸入框中，在名稱框選擇需要嵌入的IF函數(shù)，IF函數(shù)的參數(shù)分別為：Logjcal_test的值為“C4="一般干部"”，Value_if_true的值為“K4+MIN(L4,2)”，Value_if_false的值為“K4”。N4單元格的最后公式為“=IF(C4="主要干部",K4+MIN(M4,2),IF(C4="一般干部",K4+MIN(L4,2),K4))”，結果顯示為5。④選擇N4單元格，移動鼠標，當鼠標變成黑色填充柄時，雙擊鼠標左鍵，完成函數(shù)及公式的復制。如果達到獎學金的課程門次與本學期所學的課程門次相等，就表示該學生具備獎學金資格。因此O4單元格可以根據(jù)N4單元格的值是否與課程總門次（B2）是否相等，來填寫是否具備獎學金資格。需要注意的是，由于課程總門次（B2）不隨公式的位置變化而發(fā)生變化，因此，需要使用絕對地址。操作步驟如下：

2.實現(xiàn)過程6）獎學金資格判定①選擇O4單元格，選擇IF函數(shù)并填寫相應參數(shù)。O4的公式為“=IF(N4=$B$2,"是","否")”，結果顯示為“是”。②選擇O4單元格，移動鼠標，當鼠標變成黑色填充柄時，雙擊鼠標左鍵，完成函數(shù)及公式的復制。將有獎學金資格的學生名單篩選出來，并對名單按平均課程績點排序，當平均課程績點相同時，按德育績點排序。操作步驟如下：

2.實現(xiàn)過程7）篩選和排序符合獎學金名單①選擇A3:P92單元格區(qū)域，單擊“開始”選項卡/“編輯”組/“排序和篩選”按鈕的“篩選”。②單擊O3單元格的篩選下三角，在彈出的菜單中只勾選“是”。狀態(tài)欄顯示“在89條記錄中找到69個”，表明有69個學生具備獎學金資格。③鼠標定位在“平均課程績點”列的任一單元格，單擊“開始”選項卡/“編輯”組/“排序和篩選”按鈕的“降序”，J3單元格旁就出現(xiàn)了，表明數(shù)據(jù)就按照平均課程績點的大小進行了降序排序。④鼠標定位在當前工作表的任一位置，單擊“開始”選項卡/“編輯”組/“排序和篩選”按鈕的“自定義排序”，在彈出的“排序”對話框中添加如圖所示的條件。

2.實現(xiàn)過程7）篩選和排序符合獎學金名單根據(jù)獎學金評定規(guī)則，首先要確定每個等級的獲獎人數(shù)：一等獎學金名額不超過專業(yè)總?cè)藬?shù)2％（含，下同），二等獎學金名額不超過專業(yè)總?cè)藬?shù)5％，三等獎學金名額不超過專業(yè)總?cè)藬?shù)8％，人數(shù)出現(xiàn)小數(shù)的，采用去尾法計算。操作步驟如下：

2.實現(xiàn)過程8）獎學金名額確定①將相關素材的“獎學金名額”工作表復制到“獎學金評定.xlsx”工作簿中。②計算每個等級的獲獎人數(shù)，由于人數(shù)出現(xiàn)小數(shù)的，要采用去尾法計算，因此，可以使用ROUNDDOWM函數(shù)來實現(xiàn)。D2單元格的公式為“=ROUNDDOWN($B$1*B2,0)”，結果為4。③選擇D2單元格，移動鼠標，當鼠標變成黑色填充柄時，雙擊鼠標左鍵，完成函數(shù)及公式的復制。計算得到一等獎4名，二等獎10名，三等獎16名。要根據(jù)獎學金名額來評定獎學金，還需要考慮如下規(guī)則：當平均課程績點和德育績點相同導致獎學金等級不同時，獎學金等級按最高等級計算，下一等級獎學金名額相應減少。如果平均課程績點和德育績點相同導致最后一個等級名額不夠時，則自動擴充獎學金。操作步驟如下：

2.實現(xiàn)過程9）獎學金評定①單擊“獎學金計算”工作表的J3單元格的篩選按鈕，在彈出的菜單中選擇“數(shù)字篩選”的“10個最大的值”，如圖所示。②在彈出的“自動篩選前10個”對話框中選擇顯示4項，如圖所示。

2.實現(xiàn)過程9）獎學金評定③狀態(tài)欄顯示“在89個記錄中找到4個”，將其獎學金區(qū)域填充為“一等獎”。④單擊J3單元格的篩選按鈕，篩選出前14項（一、二等獎的人數(shù)），將其獎學金區(qū)域為空的10個單元格填寫為“二等獎”。

2.實現(xiàn)過程9）獎學金評定⑤單擊J3單元格的篩選按鈕，篩選出前30項（一、二、三等獎的人數(shù)），狀態(tài)欄顯示“在89個記錄中找到31個”。觀察最后的數(shù)據(jù)，發(fā)現(xiàn)第30和第31個數(shù)據(jù)的平均課程績點和德育績點相同，因此符合三等獎的學生有17名，將其獎學金區(qū)域為空的17個單元格填寫為“三等獎”。⑥選擇整個工作表，將其內(nèi)容復制，粘貼到一個新的工作表，命名為“獎學金最終名單”。需要按獎學金最終名單數(shù)據(jù)，按“姓名”查詢每個學生的詳細情況。由于查詢的姓名是獎學金最終名單的學生，因此可以用數(shù)據(jù)有效性來進行姓名限制。而要查詢到其他的具體情況，可以根據(jù)姓名去查詢獎學金