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

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

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

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

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

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

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

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