大學(xué)計算機(jī)基礎(chǔ)-基于計算思維（Windows 10+Office 2016）（第2版） 教案-教學(xué)設(shè)計 第6章 Excel綜合應(yīng)用用

《信息技術(shù)——基于WPS+數(shù)據(jù)思維》教案第6章Excel綜合應(yīng)用6.1項目分析《信息技術(shù)——office2016+計算思維》授課導(dǎo)航第6章Excel綜合應(yīng)用6.1項目分析授課時間授課地點內(nèi)容摘要1.獎學(xué)金評定的制作任務(wù)分析。教學(xué)目標(biāo)知識目標(biāo)1.明晰該項目要求。技能目標(biāo)1.能明確項目要求，做好學(xué)習(xí)計劃。教學(xué)設(shè)備教學(xué)多媒體設(shè)備，計算機(jī)材料準(zhǔn)備教學(xué)課件、軟件；視頻教學(xué)資料、網(wǎng)絡(luò)教學(xué)資源。教法案例教學(xué)法、啟示法、直觀教學(xué)法、歸納總結(jié)法等。學(xué)法合作學(xué)習(xí)法、自主學(xué)習(xí)法等。教學(xué)重點1.獎學(xué)金評定的效果要求。教學(xué)難點1.獎學(xué)金評定效果達(dá)成的策略。備注

教學(xué)環(huán)節(jié)教學(xué)內(nèi)容與教師活動學(xué)生活動組織教學(xué)課前準(zhǔn)備好多媒體課件，上課時引導(dǎo)學(xué)生就坐，宣布課堂紀(jì)律。課前預(yù)習(xí)導(dǎo)入新課輔導(dǎo)員李老師從成績系統(tǒng)中導(dǎo)出了電子信息工程專業(yè)13級的全體學(xué)生的成績，他需要對比了解各班級的成績情況以及評定本學(xué)期的獎學(xué)金。自由討論自主問答知識講解根據(jù)學(xué)校的獎學(xué)金評選條例，除了要滿足相關(guān)的基本條件外，成績必須滿足：（1）德育績點3.0以上（含，下同），平均課程績點2.88以上，單科課程績點2.5以上。其中：平均課程績點（不含德育）計算公式為：課程學(xué)分績點課程學(xué)分=（課程學(xué)分×課程績點）課程學(xué)分，平均（2）為鼓勵模范帶頭、積極奉獻(xiàn)精神，擔(dān)任學(xué)生干部的，可以適當(dāng)下調(diào)單科課程績點。其中，擔(dān)任主要學(xué)生干部的學(xué)生，其單科課程績點可在上述基礎(chǔ)上適當(dāng)下調(diào)0.3（不超過兩門，含兩門）。一般學(xué)生干部的單科課程績點可適當(dāng)下調(diào)0.1（不超過兩門，含兩門）。其中，主要學(xué)生干部是指校團(tuán)委、校學(xué)生會副部長以上（含副部長）的學(xué)生干部，院分團(tuán)委、學(xué)生會部長（含部長）以上學(xué)生干部，班級班長、團(tuán)支部書記。其他學(xué)生干部為一般學(xué)生干部。（3）獎學(xué)金按平均課程績點排序，當(dāng)平均課程績點相同時，按德育績點排序。（4）一等獎學(xué)金名額不超過專業(yè)總?cè)藬?shù)2％（含，下同），二等獎學(xué)金名額不超過專業(yè)總?cè)藬?shù)5％，三等獎學(xué)金名額不超過專業(yè)總?cè)藬?shù)8％，人數(shù)出現(xiàn)小數(shù)的，采用去尾法計算。（5）當(dāng)平均課程績點和德育績點相同導(dǎo)致獎學(xué)金等級不同時，獎學(xué)金等級按最高等級計算，下一等級獎學(xué)金名額相應(yīng)減少。如果平均課程績點和德育績點相同導(dǎo)致最后一個等級名額不夠時，則自動擴(kuò)充獎學(xué)金。（6）獎學(xué)金一次性發(fā)放。一等獎1500元/人，二等獎1250元/人，三等獎750元/人。認(rèn)真聽課做好筆記

6.2成績分析《信息技術(shù)——office2016+計算思維》授課導(dǎo)航第6章Excel綜合應(yīng)用6.2成績分析授課時間授課地點內(nèi)容摘要本任務(wù)需要成績分析的制作，包括知識點解析和任務(wù)實現(xiàn)，總結(jié)提高。包含以下內(nèi)容：1.凍結(jié)窗格2.數(shù)據(jù)排序3.數(shù)據(jù)篩選4.分類匯總5.數(shù)據(jù)透視表教學(xué)目標(biāo)知識目標(biāo)1.了解數(shù)據(jù)處理方法。2.明確任務(wù)的要求及實現(xiàn)方法。技能目標(biāo)1.能在Excel中實現(xiàn)：凍結(jié)窗格、數(shù)據(jù)排序、數(shù)據(jù)篩選、分類匯總、數(shù)據(jù)透視表。教學(xué)設(shè)備教學(xué)多媒體設(shè)備，計算機(jī)材料準(zhǔn)備教學(xué)課件、軟件；視頻教學(xué)資料、網(wǎng)絡(luò)教學(xué)資源。教法案例教學(xué)法、啟示法、直觀教學(xué)法、歸納總結(jié)法等。學(xué)法合作學(xué)習(xí)法、自主學(xué)習(xí)法等。教學(xué)重點1.Excel的數(shù)據(jù)處理方法。教學(xué)難點1.數(shù)據(jù)透視表的使用。備注教學(xué)環(huán)節(jié)教學(xué)內(nèi)容與教師活動學(xué)生活動組織教學(xué)課前準(zhǔn)備好多媒體課件，上課時引導(dǎo)學(xué)生就坐，宣布課堂紀(jì)律。課前預(yù)習(xí)6.2.1知識點解析1.凍結(jié)窗格對一個超寬超長表格中的數(shù)據(jù)進(jìn)行操作時，當(dāng)行、列標(biāo)題行消失后，有時會記錯各行、列標(biāo)題的相對位置。為解決該問題，可以將行、列的標(biāo)題部分和數(shù)據(jù)區(qū)域通過“凍結(jié)窗格”視圖來實現(xiàn)將標(biāo)題部分保留在屏幕上不動，而數(shù)據(jù)區(qū)域部分則可以滾動。2.數(shù)據(jù)排序很多時候，希望表格中的數(shù)據(jù)按照某種方式來組織，可以使用“數(shù)據(jù)排序”功能來實現(xiàn)。“數(shù)據(jù)排序”通常是對選定的數(shù)據(jù)按照某一列或多列的升序或降序進(jìn)行排序。3.數(shù)據(jù)篩選數(shù)據(jù)篩選的目的是從一堆數(shù)據(jù)中找出想要的數(shù)據(jù)。通過數(shù)據(jù)篩選功能，可以將符合條件的數(shù)據(jù)集中顯示在工作表上，而將不符合條件的數(shù)據(jù)暫時隱藏起來。4.分類匯總?cè)绻枰獙xcel工作表中的數(shù)據(jù)按類別進(jìn)行求和、計數(shù)等，可以使用分類匯總功能。所謂分類匯總，就是先對數(shù)據(jù)進(jìn)行排序（通過排序進(jìn)行分類），然后再進(jìn)行匯總。5.數(shù)據(jù)透視表如果需要對Excel工作表中的數(shù)據(jù)進(jìn)行深入的分析，可以使用數(shù)據(jù)透視表功能。數(shù)據(jù)透視表是基于Excel工作表中的數(shù)據(jù)而產(chǎn)生的動態(tài)匯總表格。數(shù)據(jù)透視表功能提供了一種比分類匯總功能更強(qiáng)大的方式來分析數(shù)據(jù)。自由討論自主問答6.2.2任務(wù)實現(xiàn)1.任務(wù)分析李老師要對電子信息工程專業(yè)13級的全體學(xué)生成績做分析，要求：（1）計算學(xué)生的平均課程績點。（2）按平均課程績點及德育績點降序排序。（3）分類匯總每個班的平均課程績點的平均值。（4）分類匯總每個班的平均課程績點的平均值及班級人數(shù)。（5）不改變原始數(shù)據(jù)的前提下統(tǒng)計每個班的德育及平均課程績點平均值。（6）不改變原始數(shù)據(jù)的前提下統(tǒng)計每個班的德育績點的最大值和最小值。（7）篩選出可能獲得獎學(xué)金的學(xué)生名單，即德育績點3.0以上（含，下同），平均課程績點2.88以上，單科課程績點2.2以上（因為“主要干部”成績績點可以下調(diào)0.3）。2.實現(xiàn)過程1）復(fù)制信息并凍結(jié)窗格由于學(xué)生成績數(shù)據(jù)多，在進(jìn)行成績操作時，希望表題、學(xué)生姓名以及課程信息等一直顯示在屏幕上，可以通過凍結(jié)窗格來實現(xiàn)。2）平均課程績點計算平均課程績點（不含德育）計算公式為課程學(xué)分績點課程學(xué)分=（課程學(xué)分×課程績點）課程學(xué)分，平均3）排序、篩選和分類匯總數(shù)據(jù)準(zhǔn)備由于排序和分類匯總會改變原有數(shù)據(jù)的結(jié)構(gòu)，而篩選會導(dǎo)致數(shù)據(jù)的顯示發(fā)生變化，因此可以將“平均課程績點計算”工作表復(fù)制成新的工作表。4）成績排序按平均課程績點及德育績點降序排序。5）按班級分類匯總平均課程績點平均值要分類匯總每個班的平均課程績點的平均值，需要先按班級排序后再進(jìn)行分類匯總。6）按班級分類匯總平均課程績點平均值及人數(shù)要分類匯總每個班的平均課程績點平均值及班級人數(shù)，就需要先按班級對平均課程績點分類匯總，然后再按班級對姓名進(jìn)行分類匯總。7）統(tǒng)計每個班的德育及平均課程績點平均值要不改變原始數(shù)據(jù)的前提下統(tǒng)計每個班的德育績點及平均課程績點的平均值，可以使用數(shù)據(jù)篩選功能。8）統(tǒng)計每個班的德育績點的最大值和最小值要統(tǒng)計每個班的德育績點的最大值和最小值，可以使用數(shù)據(jù)篩選功能。9）篩選出可能獲得獎學(xué)金的學(xué)生名單根據(jù)獎學(xué)金評選規(guī)則，可能獲得獎學(xué)金的學(xué)生成績應(yīng)該滿足：德育績點3.0以上（含，下同），平均課程績點2.88以上，單科課程績點2.2以上（因為“主要干部”成績績點可以下調(diào)0.3）。認(rèn)真聽課做好筆記6.2.3總結(jié)提高1.高級篩選利用Excel的數(shù)據(jù)篩選功能，可以將符合條件的結(jié)果顯示在原有的數(shù)據(jù)表格中，不符合條件的將自動隱藏。但在有些時候，可能需要更復(fù)雜的篩選。針對高級篩選，首先需要設(shè)置篩選條件區(qū)域。篩選條件有3個特征：（1）條件的標(biāo)題要與數(shù)據(jù)表的原有標(biāo)題完全一致。（2）多字段間的條件若為“與”關(guān)系，則寫在一行。（3）多字段間的條件若為“或”關(guān)系，則寫在下一行。2.數(shù)據(jù)透視表與篩選利用數(shù)據(jù)透視表的篩選功能，可以對透視結(jié)果進(jìn)行進(jìn)一步的分析統(tǒng)計。如圖6-28所示，利用數(shù)據(jù)透視表，可以分專業(yè)統(tǒng)計每個考場及考試時間的參加考試學(xué)生人數(shù)。通過增加“性別”作為報表篩選字段，就可以篩選出每個考場的參加考試男女生人數(shù)等。3.數(shù)據(jù)透視表與切片器利用切片器功能，可以讓數(shù)據(jù)分析與呈現(xiàn)更加可視化。4.數(shù)據(jù)透視圖Excel在生成數(shù)據(jù)透視表時，可以同時生成數(shù)據(jù)透視圖，從而更直觀地顯示統(tǒng)計結(jié)果。5.多重合并如果需要對多個工作表中的數(shù)據(jù)進(jìn)行統(tǒng)計，可以使用數(shù)據(jù)透視表的多重合并功能。認(rèn)真總結(jié)本課程相關(guān)知識。作業(yè)布置認(rèn)真完成學(xué)生活動評價設(shè)計1.學(xué)生平時成績評定表（40%）平時成績評定表序號考核內(nèi)容評價標(biāo)準(zhǔn)分值得分1出勤情況全勤20請假1次扣1分，曠課1次扣2分2學(xué)習(xí)態(tài)度學(xué)習(xí)態(tài)度端正，認(rèn)真好學(xué)，積極主動20其他情況，視實際表現(xiàn)酌情減扣分3課堂表現(xiàn)課堂紀(jì)律好，認(rèn)真聽講，積極思考、討論、回答問題20其他情況，視實際表現(xiàn)酌情減扣分4作業(yè)情況全部按時完成作業(yè)，保質(zhì)保量20其他情況，視實際表現(xiàn)酌情減扣分5文明禮貌尊師愛友，文明禮貌，誠實守信，助人為樂，品德良好20其他情況，視實際表現(xiàn)酌情減扣分合計滿分100分，權(quán)重0.2分：2.項目/任務(wù)成績（60%）項目成績占總成績的60%。項目成績主要以每個項目/任務(wù)學(xué)習(xí)結(jié)束后，以理論知識考試及實操技能考核為依據(jù)。項目成績評定表見下表：成績評定表序號評分標(biāo)準(zhǔn)分值評分1任務(wù)需求的明確和實現(xiàn)策略確定202凍結(jié)窗格、數(shù)據(jù)排序、數(shù)據(jù)篩選、分類匯總、數(shù)據(jù)透視表603制作過程能提出個性化Excel編輯的方法，舉一反三20備注合計：滿分100分，權(quán)重0.6教師簽名：教學(xué)反思

6.3獎學(xué)金評定《信息技術(shù)——office2016+計算思維》授課導(dǎo)航第6章Excel綜合應(yīng)用6.3獎學(xué)金評定授課時間授課地點內(nèi)容摘要本任務(wù)需要成績分析的制作，包括知識點解析和任務(wù)實現(xiàn)，總結(jié)提高。包含以下內(nèi)容：1.VLOOKUP函數(shù)2.IFERROR函數(shù)教學(xué)目標(biāo)知識目標(biāo)1.熟悉函數(shù)。2.明確任務(wù)的要求及實現(xiàn)方法。技能目標(biāo)1.能在Excel中實現(xiàn)：VLOOKUP函數(shù)；FERROR函數(shù)。教學(xué)設(shè)備教學(xué)多媒體設(shè)備，計算機(jī)材料準(zhǔn)備教學(xué)課件、軟件；視頻教學(xué)資料、網(wǎng)絡(luò)教學(xué)資源。教法案例教學(xué)法、啟示法、直觀教學(xué)法、歸納總結(jié)法等。學(xué)法合作學(xué)習(xí)法、自主學(xué)習(xí)法等。教學(xué)重點1.函數(shù)的操作方法。教學(xué)難點1.運(yùn)用函數(shù)。備注

教學(xué)環(huán)節(jié)教學(xué)內(nèi)容與教師活動學(xué)生活動組織教學(xué)課前準(zhǔn)備好多媒體課件，上課時引導(dǎo)學(xué)生就坐，宣布課堂紀(jì)律。課前預(yù)習(xí)6.3.1知識點解析1.VLOOKUP函數(shù)要填寫“錄取名錄表”中每個學(xué)生的錄取專業(yè)名稱及輔導(dǎo)員，就需要根據(jù)每個學(xué)生的錄取專業(yè)代碼去查找“專業(yè)代碼對應(yīng)表”中的數(shù)據(jù)。例如，要填寫“陳萍萍”的錄取專業(yè)名稱及輔導(dǎo)員，就需要根據(jù)“陳萍萍”的專業(yè)代碼“1301”，在“專業(yè)代碼對應(yīng)表”中找到專業(yè)代碼“1301”所在的行，然后將“1301”所在行對應(yīng)的錄取專業(yè)名稱及輔導(dǎo)員，將其填寫到對應(yīng)位置。因此，實現(xiàn)上述填充查找的關(guān)鍵是：（1）明確要填充的結(jié)果（錄取專業(yè)名稱，輔導(dǎo)員）。（2）要填充的結(jié)果所依據(jù)的查找元素（錄取專業(yè)代碼）。（3）被查找的元素在哪里（專業(yè)代碼對應(yīng)表），稱之為查找區(qū)域。（4）在查找區(qū)域的什么位置查找（專業(yè)代碼列）。（5）找到被查找的元素后，哪些結(jié)果作為填充值（對應(yīng)專業(yè)代碼行所在的專業(yè)名稱及專職輔導(dǎo)員）。Excel提供了VLOOKUP函數(shù)來實現(xiàn)上述功能。VLOOKUP函數(shù)可以在查找區(qū)域的第一列中查找指定的值，然后返回與該值同行的其他列的數(shù)據(jù)。VLOOKUP函數(shù)的語法格式為VLOOKUP(Lookup_value,Table_arrary,Col_index_num,Range_lookup)，參數(shù)的含義分別是：（1）Lookup_value：表示要填充的結(jié)果所依據(jù)的查找元素。（2）Table_arrary：被查找的元素所在的查找區(qū)域，并要求在查找區(qū)域的第1列去查找。（3）Col_index_num：找到被查找的匹配項后，被查找到的結(jié)果位于查找區(qū)域的第幾列。（4）Range_lookup：查找時是大致匹配查找（1或ture）還是精確匹配查找（0或false）。VLOOKUP的返回結(jié)果為要填充的元素。根據(jù)VLOOKUP函數(shù)的語法格式，要填寫“陳萍萍”的錄取專業(yè)名稱，對應(yīng)的VLOOKUP參數(shù)值是：（1）Lookup_value為B15，即根據(jù)“陳萍萍”的錄取專業(yè)代碼值去查找。（2）Table_arrary為A3:B8，A3:B8為查找區(qū)域，要確保所依據(jù)的查找元素（錄取專業(yè)代碼）對應(yīng)的專業(yè)代碼在查找區(qū)域的第1列。（3）Col_index_num為2，表示找到對應(yīng)的匹配項后，返回A3:B8區(qū)域中找到的行數(shù)據(jù)中的第2列，即對應(yīng)的專業(yè)名稱值作為函數(shù)的結(jié)果。（4）Range_lookup為0，表示精確查找。因此，C15單元格對應(yīng)的公式為“=VLOOKUP(B15,A3:B8,2,0)”。由于公式復(fù)制時，查找區(qū)域A3:B8是不發(fā)生改變的，因此C15單元格對應(yīng)的最終公式為“=VLOOKUP(B15,$A$3:$B$8,2,0)”。而要填寫“陳萍萍”的輔導(dǎo)員，D15單元格對應(yīng)的公式為“=VLOOKUP(B15,$A$3:$C$8,3,0)”。也可將單元格區(qū)域A3:C8定義為“專業(yè)代碼區(qū)域”名稱，這樣，C15單元格格的公式可以寫為“=VLOOKUP(B15,專業(yè)代碼區(qū)域,2,0)”，而D15單元格對應(yīng)的公式為“=VLOOKUP(B15,專業(yè)代碼區(qū)域,3,0)”。2.IFERROR函數(shù)由于“劉嘉楠”的錄取專業(yè)代碼在“專業(yè)代碼對應(yīng)表”（A3:B7）中找不到，因此填充結(jié)果為#N/A，表示公式執(zhí)行過程中出現(xiàn)了“值不可用”的錯誤?？梢允褂肐FERROR函數(shù)來捕獲和處理公式中的錯誤。IFERROR函數(shù)的語法格式為IFERROR(value,value_if_error)，其參數(shù)的含義是：（1）Value：為可能出錯的公式。如果公式不出錯，就將Value公式的結(jié)果作為返回值。（2）Value_if_error：公式計算結(jié)果錯誤時返回的值。如上例所示，可以將C15單元格的公式修改為“=IFERROR(VLOOKUP(B15,$A$3:$B$7,2,0),"無此專業(yè)代碼")”來解決專業(yè)代碼不存在的情況。這樣，在進(jìn)行公式復(fù)制后，“劉嘉楠”的錄取專業(yè)名稱顯示為“無此專業(yè)代碼”。自由討論自主問答6.3.2任務(wù)實現(xiàn)1.任務(wù)分析要計算某學(xué)生是否獲得獎學(xué)金，需要：（1）確定其級別身份為“主要干部”、“一般干部”還是“無”。（2）根據(jù)獎學(xué)金條件限制的單科課程績點要求，分別統(tǒng)計單科課程績點>=2.5的門次，單科課程績點∈[2.4,2.5)的門次以及單科課程績點∈[2.2,2.5)的門次。（3）根據(jù)級別身份，計算每個學(xué)生達(dá)到獎學(xué)金要求的課程門次，如表6-1所示。級別身份達(dá)到獎學(xué)金要求的課程門次主要干部單科課程績點>=2.5的門次+最多2門單科課程績點∈[2.2,2.5)一般干部單科課程績點>=2.5的門次+最多2門單科課程績點∈[2.4,2.5)無單科課程績點>=2.5的門次（4）獎學(xué)金資格判定：如果達(dá)到獎學(xué)金要求的課程門次=修讀課程門次，則表明其有評選獎學(xué)金資格，否則就沒有評選獎學(xué)金資格。（5）篩選出有獎學(xué)金資格的名單，并對名單按平均課程績點排序，當(dāng)平均課程績點相同時，按德育績點排序。（6）計算獎學(xué)金名額。一等獎學(xué)金名額不超過專業(yè)總?cè)藬?shù)2％（含，下同），二等獎學(xué)金名額不超過專業(yè)總?cè)藬?shù)5％，三等獎學(xué)金名額不超過專業(yè)總?cè)藬?shù)8％，人數(shù)出現(xiàn)小數(shù)的，采用去尾法計算。（7）按給定名額數(shù)量，對符合獎學(xué)金的學(xué)生進(jìn)行評定。（8）確定最終獎學(xué)金名單，并給出按姓名查詢詳細(xì)情況。2.實現(xiàn)過程1）數(shù)據(jù)準(zhǔn)備在相關(guān)素材中，“職務(wù)級別”工作表存放的是如圖6-35右上圖所示的職務(wù)與級別的對應(yīng)關(guān)系，“任職一覽表”工作表存放的是如圖6-35左上圖所示的所有擔(dān)任學(xué)生干部的名單，“獎學(xué)金計算”工作表存放的是如圖6-35右下圖所示的篩選出來的可能符合獎學(xué)金評選條件的名單。因此，新建“獎學(xué)金評定.xlsx”工作簿，將上述3張工作表復(fù)制到該工作簿中。2）確定擔(dān)任職務(wù)的對應(yīng)級別根據(jù)“職務(wù)級別”工作表的對應(yīng)關(guān)系，將“任職一覽表”工作表中學(xué)生的擔(dān)任職務(wù)轉(zhuǎn)換為對應(yīng)的職務(wù)級別。要得到“陳俊曄”同學(xué)的職務(wù)級別（D3），就需要根據(jù)他的“擔(dān)任職務(wù)”（C3單元格），在“職務(wù)級別”工作表中的!$A$2:$B$18單元格區(qū)域去查找。3）確定獎學(xué)金名單的級別身份將“任職一覽表”工作表中學(xué)生的職務(wù)級別對應(yīng)到“獎學(xué)金計算”工作表中的“級別身份”，如果沒有相應(yīng)的職務(wù)級別，則顯示“無”。需要根據(jù)“獎學(xué)金計算”工作表的“姓名”，來查找“任職一覽表”工作表中的“姓名”，如果找到，就將其“職務(wù)級別”填充到對應(yīng)的“級別身份”位置。4）統(tǒng)計單科課程績點范圍根據(jù)獎學(xué)金條件限制的單科課程績點要求，分別統(tǒng)計單科課程績點>=2.5的門次，單科課程績點∈[2.4,2.5)的門次以及單科課程績點∈[2.2,2.5)的門次，可以采用COUNTIF及COUNTIFS函數(shù)來實現(xiàn)。5）符合獎學(xué)金課程門次計算根據(jù)級別身份，計算每個學(xué)生達(dá)到獎學(xué)金要求的課程門次。6）獎學(xué)金資格判定如果達(dá)到獎學(xué)金的課程門次與本學(xué)期所學(xué)的課程門次相等，就表示該學(xué)生具備獎學(xué)金資格。因此O4單元格可以根據(jù)N4單元格的值是否與課程總門次（B2）是否相等，來填寫是否具備獎學(xué)金資格。需要注意的是，由于課程總門次（B2）不隨公式的位置變化而發(fā)生變化，因此，需要使用絕對地址。7）篩選和排序符合獎學(xué)金名單將有獎學(xué)金資格的學(xué)生名單篩選出來，并對名單按平均課程績點排序，當(dāng)平均課程績點相同時，按德育績點排序。8）獎學(xué)金名額確定根據(jù)獎學(xué)金評定規(guī)則，首先要確定每個等級的獲獎人數(shù)：一等獎學(xué)金名額不超過專業(yè)總?cè)藬?shù)2％（含，下同），二等獎學(xué)金名額不超過專業(yè)總?cè)藬?shù)5％，三等獎學(xué)金名額不超過專業(yè)總?cè)藬?shù)8％，人數(shù)出現(xiàn)小數(shù)的，采用去尾法計算。9）獎學(xué)金評定要根據(jù)獎學(xué)金名額來評定獎學(xué)金，還需要考慮如下規(guī)則：當(dāng)平均課程績點和德育績點相同導(dǎo)致獎學(xué)金等級不同時，獎學(xué)金等級按最高等級計算，下一等級獎學(xué)金名額相應(yīng)減少。如果平均課程績點和德育績點相同導(dǎo)致最后一個等級名額不夠時，則自動擴(kuò)充獎學(xué)金。10）獎學(xué)金名單查詢需要按獎學(xué)金最終名單數(shù)據(jù)，按“姓名”查詢每個學(xué)生的詳細(xì)情況。由于查詢的姓名是獎學(xué)金最終名單的學(xué)生，因此可以用數(shù)據(jù)有效性來進(jìn)行姓名限制。而要查詢到其他的具體情況，可以根據(jù)姓名去查詢獎學(xué)金最終名單中對應(yīng)的數(shù)據(jù)，可以使用VLOOKUP來實現(xiàn)。認(rèn)真聽課做好筆記6.3.3總結(jié)提高1.VLOOKUP函數(shù)值得注意的是，如果VLOOKUP函數(shù)的查找區(qū)域中第一列中有多個相同的值，VLOOKUP函數(shù)只能返回與查找值相同的第1條數(shù)據(jù)所對應(yīng)的其他列的值。2.HLOOKUP函數(shù)HLOOKUP函數(shù)與VLOOKUP相似，都是用來查找數(shù)據(jù)。其不同點是VLOOKUP函數(shù)在查找區(qū)域中第1列找到匹配項后，將匹配項所在行對應(yīng)的某列的值作為結(jié)果返回。而HLOOKUP函數(shù)則是在查找區(qū)域中第1行找到匹配項后，將匹配項所在行對應(yīng)的某行的值作為結(jié)果返回。3.名稱管理如果需要對工作簿中已經(jīng)定義的名稱進(jìn)行查看、編輯或者刪除等操作，可以通過“公式”選項卡/“定義的名稱”組/“名稱管理器”按鈕，打開“名稱管理器”對話框來進(jìn)行操作。認(rèn)真總結(jié)本課程相關(guān)知識。作業(yè)布置認(rèn)真完成學(xué)生活動評價設(shè)計1.學(xué)生平時成績評定表（40%）平時成績評定表序號考核內(nèi)容評價標(biāo)準(zhǔn)分值得分1出勤情況全勤20請假1次扣1分，曠課1次扣2分2學(xué)習(xí)態(tài)度學(xué)習(xí)態(tài)度端正，認(rèn)真好學(xué)，積極主動20其他情況，視實際表現(xiàn)酌情減扣分3課堂表現(xiàn)課堂紀(jì)律好，認(rèn)真聽講，積極思考、討論、回答問題20其他情況，視實際表現(xiàn)酌情減扣分4作業(yè)情況全部按時完成作業(yè)，保質(zhì)保量20其他情況，視實際表現(xiàn)酌情減扣分5文明禮貌尊師愛友，文明禮貌，誠實守信，助人為樂，品德良好20其他情況，視實際表現(xiàn)酌情減扣分合計滿分100分，權(quán)重0.2分：2.項目/任務(wù)成績（60%）項目成績占總成績的60%。項目成績主要以每個項目/任務(wù)學(xué)習(xí)結(jié)束后，以理論知識考試及實操技能考核為依據(jù)。項目成績評定表見下表：成績評定表序號評分標(biāo)準(zhǔn)分值評分1任務(wù)需求的明確和實現(xiàn)策略確定202能夠掌握：VLOOKUP函數(shù)；FERROR函數(shù)。。603制作過程能提出個性化Excel編輯的方法，舉一反三20備注合計：滿分100分，權(quán)重0.6教師簽名：教學(xué)反思

6.4獎學(xué)金統(tǒng)計《信息技術(shù)——office2016+計算思維》授課導(dǎo)航第6章Excel綜合應(yīng)用6.4獎學(xué)金統(tǒng)計授課時間授課地點內(nèi)容摘要本任務(wù)需要成績分析的制作，包括知識點解析和任務(wù)實現(xiàn)，總結(jié)提高。包含以下內(nèi)容：1.批注2.SUMIF函數(shù)3.COUNTIFS函數(shù)的條件區(qū)域教學(xué)目標(biāo)知識目標(biāo)1.熟悉批注和函數(shù)的使用方法。2.明確任務(wù)的要求及實現(xiàn)方法。技能目標(biāo)1.能在Excel中實現(xiàn)：批注，SUMIF函數(shù)，COUNTIFS函數(shù)的條件區(qū)域。教學(xué)設(shè)備教學(xué)多媒體設(shè)備，計算機(jī)材料準(zhǔn)備教學(xué)課件、軟件；視頻教學(xué)資料、網(wǎng)絡(luò)教學(xué)資源。教法案例教學(xué)法、啟示法、直觀教學(xué)法、歸納總結(jié)法等。學(xué)法合作學(xué)習(xí)法、自主學(xué)習(xí)法等。教學(xué)重點1.批注及函數(shù)的使用方法。教學(xué)難點1.使用COUNTIFS函數(shù)。備注

教學(xué)環(huán)節(jié)教學(xué)內(nèi)容與教師活動學(xué)生活動組織教學(xué)課前準(zhǔn)備好多媒體課件，上課時引導(dǎo)學(xué)生就坐，宣布課堂紀(jì)律。課前預(yù)習(xí)6.4.1知識點解析1.批注通過批注，可以對單元格的內(nèi)容添加注釋或者說明。如圖6-50所示，通過對“小計”添加批注，可以清晰知道小計金額的由來。由于批注類似于Word中的文本框，因此，可以通過設(shè)置批注的填充效果為圖片，而讓“活頁夾”添加圖片批注。2.SUMIF函數(shù)SUMIF函數(shù)的語法格式為：SUMIF(Range,Criteria,Sum_range)，其中，Range為要過濾的單元格區(qū)域（即第1步中確定的單元格區(qū)域），Criteria為指定的條件（即第2步中給定的條件），Sum_range表示需要對哪些單元格求和（即第3步中找到的單元格區(qū)域）。SUMIF函數(shù)將得到符合條件的和（即第4步中的對應(yīng)單元格區(qū)域的求和）。對應(yīng)的公式為“=SUMIF(B3:B10,"北京",C3:C10)”。其含義是：將B3:B10單元格區(qū)域中為“北京”的數(shù)據(jù)過濾出來，在過濾出來的數(shù)據(jù)中求對應(yīng)的C3:C10單元格數(shù)值的和。3.COUNTIFS函數(shù)的條件區(qū)域其對應(yīng)的參數(shù)是：（1）Criteria_range1參數(shù)值為B3:B10單元格區(qū)域。（2）Criteria1參數(shù)值為“北京”。（3）Criteria_range2參數(shù)值為C3:C10單元格區(qū)域。（4）Criteria2參數(shù)值為“>=17000”。公式為“=COUNTIFS(B3:B10,"北京",C3:C10,">=17000")”。其含義是：將B3:B10單元格區(qū)域中為“北京”的數(shù)據(jù)過濾出來，在過濾出來的數(shù)據(jù)中，再將C3:C10單元格區(qū)域中>=170000的數(shù)據(jù)過濾出來，在得到的數(shù)據(jù)區(qū)域中統(tǒng)計C3:C10單元格區(qū)域的單元格個數(shù)。自由討論自主問答6.4.2任務(wù)實現(xiàn)1.任務(wù)分析輔導(dǎo)員李老師現(xiàn)在要統(tǒng)計獎學(xué)金獲獎情況，包括：（1）計算每個人應(yīng)獲得的獎學(xué)金數(shù)額。（2）為了按班級總體發(fā)放獎學(xué)金，需要統(tǒng)計每個班獲得的獎學(xué)金總額。（3）為了對比各班之間的情況，需要按班統(tǒng)計每個班的各等級獎學(xué)金獲獎人數(shù)。（4）為H3單元格添加“按班級統(tǒng)計獎學(xué)金總額”批注，為G12單元格添加“按班統(tǒng)計各等級獎學(xué)金獲獎人數(shù)”批注。（5）用圖表展示每個班的各等級獎學(xué)金人數(shù)。2.實現(xiàn)過程1）數(shù)據(jù)準(zhǔn)備在相關(guān)素材中，“獎學(xué)金名單”工作表存放的是所有獲得獎學(xué)金的名單，“獎學(xué)金標(biāo)準(zhǔn)”工作表存放的是各等級獎學(xué)金對應(yīng)的獎學(xué)金金額。因此，新建“獎學(xué)金統(tǒng)計.xlsx”工作簿，將上述2張工作表復(fù)制到該工作簿中，并將“獎學(xué)金名單”工作表重命名為“獎學(xué)金發(fā)放統(tǒng)計”。2）計算學(xué)生應(yīng)發(fā)獎學(xué)金要填寫每個學(xué)生的獎學(xué)金數(shù)額，需要根據(jù)每個人的獲獎等級，查找到對應(yīng)的獎學(xué)金標(biāo)準(zhǔn)，因此可以用VLOOKUP函數(shù)來實現(xiàn)。3）填充班級名稱要從獎學(xué)金學(xué)生名單的班級信息（B2:B33單元格區(qū)域）而得到每個獲獎班級的班級名稱，實際上可以認(rèn)為是從有重復(fù)數(shù)據(jù)的序列中去掉重復(fù)數(shù)據(jù)，可以使用數(shù)據(jù)篩選的高級篩選功能來實現(xiàn)。4）添加批注為H3單元格添加“按班級統(tǒng)計獎學(xué)金總額”批注，為G12單元格添加“按班統(tǒng)計各等級獎學(xué)金獲獎人數(shù)”批注。5）統(tǒng)計每個班的獎學(xué)金總額要統(tǒng)計每個班的獎學(xué)金總額，實際上是對獲獎名單按“班級”來對“應(yīng)發(fā)獎學(xué)金”進(jìn)行求和。例如，要統(tǒng)計“13電子1”的獎學(xué)金總額，就是要將獲獎名單中屬于“13電子1”的數(shù)據(jù)過濾出來，然后將過濾出來的“應(yīng)發(fā)獎學(xué)金”相加即可。上述要求可以使用SUMIF函數(shù)。6）統(tǒng)計每個班獎學(xué)金各等級的人數(shù)要統(tǒng)計每個班獎學(xué)金各等級的人數(shù)，實際上是對獲獎名單按“班級”及“獎學(xué)金資格”來匯總對應(yīng)的單元格個數(shù)。7）制作各班級獎學(xué)金獲獎情況對比圖要制作各班級獎學(xué)金獲獎情況對比圖，可以用“三維柱形圖”來實現(xiàn)。認(rèn)真聽課做好筆記6.4.3總結(jié)提高1.SUMIFS函數(shù)要統(tǒng)計“北京”地區(qū)游戲積分在17000分以上（含）的總積分，需要：（1）將“北京”地區(qū)的所有數(shù)據(jù)過濾出來。（2）在過濾出來的數(shù)據(jù)中，對積分>=17000的數(shù)據(jù)進(jìn)行再次過濾。（3）統(tǒng)計最后過濾出來的積分>=17000的和?？梢允褂肧UMIFS函數(shù)來實現(xiàn)。SUMIF函數(shù)用來對區(qū)域中滿足多個條件的單元格求和。SUMIF函數(shù)的語法格式為SUMIFS(Sum_