《計算機(jī)應(yīng)用基礎(chǔ) Win10+Office 2016》教案 模塊四 Excel 2016的應(yīng)用（二）

PAGE8PAGE8PAGE9PAGE9

課題Excel2016的應(yīng)用（二）——處理學(xué)生成績表數(shù)據(jù)課時2課時（90min）教學(xué)目標(biāo)知識技能目標(biāo)：了解公式和函數(shù)的概念，并認(rèn)識公式中的運(yùn)算符了解單元格引用的作用和類型掌握利用公式和函數(shù)對工作表數(shù)據(jù)進(jìn)行計算與分析的操作掌握利用條件格式對滿足特定條件的單元格以醒目方式突出顯示的方法掌握保護(hù)工作表數(shù)據(jù)的方法素質(zhì)目標(biāo)：熟悉國家政策，增強(qiáng)數(shù)據(jù)安全意識、法律意識；了解救援抗災(zāi)事件，培養(yǎng)熱愛祖國、關(guān)愛他人的美德教學(xué)重難點(diǎn)教學(xué)重點(diǎn)：Excel2016中公式和函數(shù)的概念及應(yīng)用教學(xué)難點(diǎn)：（1）利用公式和函數(shù)對工作表數(shù)據(jù)進(jìn)行計算與分析的操作（2）利用條件格式對滿足特定條件的單元格以醒目方式突出顯示的方法教學(xué)方法演示法、問答法、講授法、情景教學(xué)法教學(xué)用具電腦、投影儀、多媒體課件、教材教學(xué)設(shè)計第1節(jié)課：→→→傳授新知（5min）→項(xiàng)目講解（7min）→→項(xiàng)目講解（20min）第2節(jié)課：實(shí)操演練（15min）→項(xiàng)目講解（5min）→實(shí)操演練（5min）→項(xiàng)目講解（7min）→實(shí)操演練（8min）→課堂小結(jié)（3min）→作業(yè)布置（2min）教學(xué)過程主要教學(xué)內(nèi)容及步驟設(shè)計意圖第一節(jié)課課前任務(wù)【教師】布置課前任務(wù)，和學(xué)生負(fù)責(zé)人取得聯(lián)系，讓其提醒同學(xué)通過文旌課堂APP或其他學(xué)習(xí)軟件，完成課前任務(wù)（1）請大家提前預(yù)習(xí)本節(jié)課內(nèi)容。（2）了解《中華人民共和國數(shù)據(jù)安全法》?！緦W(xué)生】完成課前任務(wù)通過課前任務(wù)，讓學(xué)生提前了解所學(xué)課程的內(nèi)容，增加學(xué)生的求知欲考勤

（2min）【教師】使用文旌課堂APP進(jìn)行簽到【學(xué)生】班干部報請假人員及原因培養(yǎng)學(xué)生的組織紀(jì)律性，掌握學(xué)生的出勤情況情景導(dǎo)入（3min）【教師】講述“小譚對表格中的數(shù)據(jù)進(jìn)行計算和處理”情景，并提出以下問題：如果你是小譚，你打算如何幫助李老師處理成績表中的數(shù)據(jù)呢？通過情景導(dǎo)入的方法，引導(dǎo)學(xué)生主動思考，使學(xué)生身臨其境，激發(fā)學(xué)生的學(xué)習(xí)興趣傳授新知（5min）【教師】通過PPT課件來展示計算和處理完成的工作表的效果，并講解相關(guān)知識認(rèn)識公式、函數(shù)和公式中的運(yùn)算符公式和函數(shù)公式由運(yùn)算符和參與運(yùn)算的操作數(shù)組成。運(yùn)算符可以是算術(shù)運(yùn)算符、比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符；操作數(shù)可以是常量、單元格引用和函數(shù)等。要輸入公式必須先輸入“=”，然后在其后輸入運(yùn)算符和操作數(shù)，否則Excel會將輸入的內(nèi)容作為文本型數(shù)據(jù)處理。函數(shù)是預(yù)先定義好的表達(dá)式，它必須包含在公式中。每個函數(shù)都由函數(shù)名和參數(shù)組成，其中函數(shù)名表示將執(zhí)行的操作（如求平均值函數(shù)AVERAGE），參數(shù)表示函數(shù)將使用的值的單元格地址，通常是一個單元格區(qū)域，也可以是更為復(fù)雜的內(nèi)容。在公式中合理地使用函數(shù)，可以完成諸如求和、求平均值、邏輯判斷等數(shù)據(jù)處理操作。公式中的運(yùn)算符（1）文本運(yùn)算符。使用文本運(yùn)算符“&”（與號）可將兩個或多個文本值串起來產(chǎn)生一個連續(xù)的文本值。例如：輸入“祝你”&“快樂、開心！”會生成“祝你快樂、開心！”。（2）算術(shù)運(yùn)算符。算術(shù)運(yùn)算符如表所示，其作用是完成基本的數(shù)學(xué)運(yùn)算，并產(chǎn)生數(shù)值結(jié)果算術(shù)運(yùn)算符含義實(shí)例+（加號）加法A1+A2-（減號）減法或負(fù)數(shù)A1-A2*（星號）乘法A1*2/（正斜杠）除法A1/3%（百分號）百分比50%^（脫字號）乘方2^3（3）比較運(yùn)算符。比較運(yùn)算符如表所示。它們的作用是比較兩個值，并得出一個邏輯值，即“TRUE”（真）或“FALSE”（假）。比較運(yùn)算符含義比較運(yùn)算符含義>（大于號）大于>=（大于等于號）大于等于<（小于號）小于<=（小于等于號）小于等于=（等于號）等于<>（不等于號）不等于（4）引用運(yùn)算符。運(yùn)算引用運(yùn)算符含義實(shí)例:（冒號）區(qū)域運(yùn)算符，用于引用單元格區(qū)域B5:D15,（逗號）聯(lián)合運(yùn)算符，用于引用多個單元格區(qū)域B5:D15,F5:I15（空格）交叉運(yùn)算符，用于引用兩個單元格區(qū)域的交叉部分B7:D7C6:C8單元格引用相同或不同工作簿、工作表間的引用對于同一工作表中的單元格引用，直接輸入單元格或單元格區(qū)域地址即可。在當(dāng)前工作表中引用同一工作簿、不同工作表中的單元格的表示方法為工作表名稱!單元格或單元格區(qū)域地址在當(dāng)前工作表中引用不同工作簿中的單元格的表示方法為[工作簿名稱.xlsx]工作表名稱!單元格（或單元格區(qū)域）地址相對引用、絕對引用和混合引用（1）相對引用：Excel默認(rèn)的單元格引用方式。它直接用單元格的列標(biāo)和行號表示單元格，如B5；或用引用運(yùn)算符表示單元格區(qū)域，如B5:D15。在移動或復(fù)制公式時，系統(tǒng)會根據(jù)移動的位置自動調(diào)整公式中相對引用的單元格地址。（2）絕對引用：指在單元格的列標(biāo)和行號前面都加上“$”符號，如$B$5。不論將公式復(fù)制或移動到什么位置，絕對引用的單元格地址都不會改變。（3）混合引用：指引用中既包含絕對引用又包含相對引用，如A$1或$A1等，用于表示列變行不變或列不變行變的引用。三、Excel中的常用函數(shù)Excel提供了大量的函數(shù)，下表列出了常用的函數(shù)類型和使用范例。函數(shù)類型函數(shù)使用范例常用SUM（求和）、AVERAGE（求平均值）、MAX（求最大值）、MIN（求最小值）、COUNT（計數(shù)）等=AVERAGE(F2:F7)

表示求F2:F7單元格區(qū)域中數(shù)字的平均值財務(wù)DB（資產(chǎn)的折扣值）、IRR（現(xiàn)金流的內(nèi)部報酬率）、PMT（分期償還額）等=PMT(B4,B5,B6)

表示在輸入利率、周期和規(guī)則作為變量時，計算周期支付值日期

與時間DATA（日期）、HOUR（小時數(shù)）、SECOND（秒數(shù)）、TIME（時間）等=DATA(C2,D2,E2)

表示返回C2,D2,E2所代表的日期數(shù)學(xué)

與三角=ABS(E4)

表示得到E4單元格中數(shù)值的絕對值，即不帶負(fù)號的絕對值統(tǒng)計AVERAGE（求平均值）、RANK.EQ（求大小排名）、COUNTIF（統(tǒng)計單元格區(qū)域中符合指定條件的單元格數(shù)）=COUNTIF(H3:H13,">=120")

表示求H3:H13單元格區(qū)域中數(shù)據(jù)大于等于120的單元格數(shù)邏輯AND（與）、OR（或）、FALSE（假）、TRUE（真）、IF（如果）、NOT（非）表示使用條件測試A3是否大于等于B5，條件結(jié)果要么為真，要么為假保護(hù)數(shù)據(jù)的方法工作簿制作好后，用戶可以利用Excel提供的保護(hù)功能，通過設(shè)置密碼的方式，對其結(jié)構(gòu)和窗口進(jìn)行保護(hù)，或?qū)φ麄€工作表及工作表中的部分單元格進(jìn)行保護(hù)，以防止他人進(jìn)行修改?！緦W(xué)生】聆聽、思考、理解、記錄通過教師的講解，使學(xué)生了解Excel2016計算和處理工作表中數(shù)據(jù)的方法項(xiàng)目講解（7min）【教師】通過多媒體對計算和處理學(xué)生成績表的數(shù)據(jù)進(jìn)行分步驟演示講解小譚通過以上知識的學(xué)習(xí)已經(jīng)對處理學(xué)生成績表中的數(shù)據(jù)躍躍欲試了，下面我們就來看看小譚是如何一步步將其處理完成的吧！一、使用求和按鈕計算平均分（具體步驟見教材）（1）打開本書配套素材“模塊四”/“項(xiàng)目一”/“學(xué)生成績表（美化）”工作簿，將其以“學(xué)生成績表（處理）”為名另存到“模塊四”/“項(xiàng)目二”文件夾中。（2）單擊要計算平均分的單元格H3，然后單擊“開始”選項(xiàng)卡“編輯”組中的“求和”按鈕右側(cè)的下拉按鈕，在展開的下拉列表中選擇“平均值”選項(xiàng)。（3）此時，可看到單元格和編輯欄中自動顯示要計算平均值的單元格區(qū)域，對該區(qū)域進(jìn)行確認(rèn)。如果不正確的話，可以在工作表中拖動鼠標(biāo)重新選擇，這里保持默認(rèn)。（4）按“Enter”鍵，即可計算出第一個學(xué)生的平均分。（5）選中含有公式的單元格H3，將鼠標(biāo)指針移到該單元格右下角的填充柄處，此時鼠標(biāo)指針由空心十字形變成實(shí)心的十字形，按住鼠標(biāo)左鍵向下拖動，至目標(biāo)位置后釋放鼠標(biāo)，將求平均值公式復(fù)制到同列的其他單元格中，計算出其他學(xué)生的平均分。二．使用公式計算總分（具體步驟詳見教材）（1）單擊要計算總分的單元格I3，輸入等號“=”，然后輸入要參與運(yùn)算的單元格和運(yùn)算符E3+F3+G3，也可以直接單擊要參與運(yùn)算的單元格，將其添加到公式中。（2）按“Enter”鍵或單擊編輯欄中的“輸入”按鈕結(jié)束公式編輯，計算出第一個學(xué)生的總分。（3）將鼠標(biāo)指針移到I3單元格右下角的填充柄處，待鼠標(biāo)指針變成實(shí)心的十字形時，按住鼠標(biāo)左鍵向下拖動，至目標(biāo)位置后釋放鼠標(biāo)，將求和公式復(fù)制到同列的其他單元格中，計算出其他學(xué)生的總分。【學(xué)生】聆聽、思考、理解、記錄通過一邊演示一邊講解Excel2016計算和處理數(shù)據(jù)的過程，讓學(xué)生更加直觀的了解Excel2016的功能和作用，方便學(xué)生記憶實(shí)操演練（8min）【教師】組織學(xué)生以小組為單位，按照上述步驟進(jìn)行實(shí)際操作假如你是小譚，你會不會以上兩個任務(wù)的操作呢？試試看吧！【學(xué)生】聆聽、思考、處理數(shù)據(jù)【教師】巡堂輔導(dǎo)，及時解決學(xué)生遇到的問題通過實(shí)操演練加深學(xué)生的記憶，讓學(xué)生趁熱打鐵，增加學(xué)生學(xué)習(xí)的信心項(xiàng)目講解（20min）【教師】通過多媒體對計算和處理學(xué)生成績表的數(shù)據(jù)進(jìn)行分步驟演示講解學(xué)會了求和按鈕的使用和在單元格中輸入公式的方法，下面要引入函數(shù)來處理數(shù)據(jù)了，先來看看小譚是怎樣做的吧！三．常用函數(shù)的應(yīng)用（具體步驟詳見教材）1．使用RANK.EQ函數(shù)計算總分排名（1）單擊“名次”列中的單元格J3，然后單擊編輯欄左側(cè)的“插入函數(shù)”按鈕，打開“插入函數(shù)”對話框，選擇“統(tǒng)計”類別，然后選擇“RANK.EQ”函數(shù)。（2）單擊“確定”按鈕，打開“函數(shù)參數(shù)”對話框，單擊第一個參數(shù)編輯框，然后在工作表中選擇要進(jìn)行排位的單元格I3。（3）單擊第2個參數(shù)編輯框，然后在工作表中拖動鼠標(biāo)選擇參與排位的單元格區(qū)域I3:I32，松開鼠標(biāo)可在編輯框中看到選擇的單元格區(qū)域。（4）按鍵盤上的“F4”鍵將選擇的單元格區(qū)域轉(zhuǎn)換為絕對引用，這樣可以保證后面復(fù)制排序公式時，公式內(nèi)容不變，從而使返回的排名準(zhǔn)確。（5）單擊“確定”按鈕，計算出第一個學(xué)生的排名名次，即J3單元格在單元格區(qū)域I3:I32中的排名。拖動J3單元格的填充柄到單元格J32，計算出其他學(xué)生的名次。2．使用IF函數(shù)判斷平均分等次（1）根據(jù)假設(shè)條件，在K3單元格中輸入公式“=IF(G3>=90,"優(yōu)",IF(G3>=80,

"良",IF(G3>=70,"中",IF(G3>=60,"及格",IF(G3<60,"不及格",0)))))”。（2）向下拖動K3單元格的填充柄到K32單元格后釋放鼠標(biāo)，可依據(jù)平均分判斷出所有學(xué)生的等次。3．使用MAX和MIN函數(shù)計算總分最高分和最低分（1）在工作表數(shù)據(jù)的下方創(chuàng)建計算表格。（2）在單元格F34輸入公式“=MAX(I3:I32)”，按“Enter”鍵得到總分最高分。（3）在單元格F35輸入公式“=MIN(I3:I32)”，按“Enter”鍵得到總分最低分。4．使用COUNTIF和COUNT函數(shù)統(tǒng)計人數(shù)（1）在單元格F36輸入公式“=COUNTIF(E3:E32,">=60")”，高等數(shù)學(xué)課程的及格人數(shù)。（2）在單元格F37輸入公式“=COUNT(F3:F32)”，按“Enter”鍵得到專業(yè)英語課程的實(shí)際參加考試人數(shù)。5．使用REPLACE函數(shù)替換學(xué)號（1）在“學(xué)號”列的右側(cè)插入一個輔助列。右擊B列，在彈出的快捷菜單中選擇“插入”選項(xiàng)。（2）在輔助列的B3單元格輸入公式“=REPLACE(A3,1,2,"18")”，按“Enter”鍵得到替換結(jié)果。（3）向下拖動B3單元格的填充柄，到B32單元格后釋放鼠標(biāo)，可看到所有學(xué)號的替換效果。（4）選中B3:B32單元格區(qū)域，然后按“Ctrl+C”組合鍵，將選擇的單元格區(qū)域數(shù)據(jù)復(fù)制，單擊A3單元格，然后單擊“開始”選項(xiàng)卡“剪貼板”組中“粘貼”按鈕下方的下拉按鈕，在展開的下拉列表中選擇“值”選項(xiàng)，即可將所選數(shù)據(jù)以值方式粘貼。（5）右擊輔助列B，在彈出的快捷菜單中選擇“刪除”選項(xiàng)，將輔助列刪除。6．使用VLOOKUP函數(shù)獎勵不同等次的學(xué)生（1）根據(jù)假設(shè)條件，在L3單元格輸入公式“=VLOOKUP(K3,{"優(yōu)",200;"良",150;"中",100;"及格",50;"不及格",0},2,)”。（2）按“Enter”鍵得到查找結(jié)果，向下拖動L3單元格的填充柄到L32單元格后釋放鼠標(biāo)，可看到所有學(xué)生的獎學(xué)金。（3）在單元格F35輸入公式“=MIN(I3:I32)”，按“Enter”鍵得到總分最低分。7．使用SUMIF函數(shù)計算女生獲得的獎學(xué)金總額（1）在F38單元格輸入公式“=SUMIF(D3:D32,"女",L3:L32)”。（2）按“Enter”鍵得到計算結(jié)果。【學(xué)生】聆聽、思考、理解、記錄通過一邊演示一邊講解Excel2016計算和處理數(shù)據(jù)的過程，讓學(xué)生更加直觀的了解Excel2016的功能和作用，方便學(xué)生記憶第二節(jié)課實(shí)操演練（15min）【教師】組織學(xué)生以小組為單位，按照上述步驟進(jìn)行實(shí)際操作下面請同學(xué)們跟著小譚的步伐將以上7種常用函數(shù)應(yīng)用到表格中吧！【學(xué)生】聆聽、思考、處理數(shù)據(jù)【教師】巡堂輔導(dǎo)，及時解決學(xué)生遇到的問題通過實(shí)操演練加深學(xué)生的記憶，讓學(xué)生趁熱打鐵，增加學(xué)生學(xué)習(xí)的信心項(xiàng)目講解（5min）【教師】通過多媒體對計算和處理學(xué)生成績表的數(shù)據(jù)進(jìn)行分步驟演示講解在Excel中應(yīng)用條件格式，可以讓滿足特定條件的單元格以醒目方式突出顯示，便于對工作表數(shù)據(jù)進(jìn)行更好的比較和分析。我們先來看看小譚是怎樣做的。四．使用條件格式標(biāo)識學(xué)生成績（具體步驟詳見教材）（1）選擇要添加條件格式的單元格區(qū)域，本例選擇E3:G32單元格區(qū)域。（2）單擊“開始”選項(xiàng)卡“樣式”組中的“條件格式”按鈕，在展開的下拉列表中選擇“突出顯示單元格規(guī)則”選項(xiàng)，再在展開的子列表中選擇一種具體的條件，如“大于”選項(xiàng)。（3）打開“大于”對話框，設(shè)置“大于”對話框中的參數(shù)。（4）單擊“確定”按鈕。此時，各課程成績大于80的單元格，背景為淺紅色，字體顏色為深紅色顯示。（5）保持單元格區(qū)域的選中，在“條件格式”下拉列表中選擇“突出顯示單元格規(guī)則”/“介于”選項(xiàng)，打開“介于”對話框，設(shè)置突出顯示參數(shù)，最后單擊“確定”按鈕。（6）保持單元格區(qū)域的選中，使用同樣的方法打開“介于”對話框，設(shè)置突出顯示參數(shù)，最后單擊“確定”按鈕，即可將成績數(shù)據(jù)工作表中各分?jǐn)?shù)段突出顯示?！緦W(xué)生】聆聽、思考、理解、記錄通過一邊演示一邊講解Excel2016計算和處理數(shù)據(jù)的過程，讓學(xué)生更加直觀的了解Excel2016的功能和作用，方便學(xué)生記憶實(shí)操演練（5min）【教師】組織學(xué)生以小組為單位，按照上述步驟進(jìn)行實(shí)際操作下面請同學(xué)們也來幫助李老師使用條件格式對學(xué)生的成績進(jìn)行標(biāo)識吧！【學(xué)生】聆聽、思考、處理數(shù)據(jù)【教師】巡堂輔導(dǎo)，及時解決學(xué)生遇到的問題通過實(shí)操演練加深學(xué)生的記憶，讓學(xué)生趁熱打鐵，增加學(xué)生學(xué)習(xí)的信心項(xiàng)目講解（7min）【教師】通過多媒體對計算和處理學(xué)生成績表的數(shù)據(jù)進(jìn)行分步驟演示講解2021年6月10日，第十三屆全國人民代表大會常務(wù)委員會第二十九次會議通過《中華人民共和國數(shù)據(jù)安全法》，自2021年9月1日起施行。這是我國第一部以數(shù)據(jù)為保護(hù)對象的法律，作為全球數(shù)據(jù)安全綜合立法的首創(chuàng)性探索，對于全球數(shù)據(jù)安全、利用安全具有引領(lǐng)和示范意義。那么Excel2016是如何進(jìn)行數(shù)據(jù)保護(hù)的呢？我們來看看小譚的做法……五．保護(hù)數(shù)據(jù)（具體步驟詳見教材）1．保護(hù)工作簿（1）選中要進(jìn)行保護(hù)的工作簿，單擊“審閱”選項(xiàng)卡“保護(hù)”組中的“保護(hù)工作簿”按鈕。（2）在打開的對話框中選中“結(jié)構(gòu)”復(fù)選框，然后在“密碼”編輯框中輸入保護(hù)密碼并單擊“確定”按鈕，再在打開的對話框中輸入同樣的密碼并確定。（3）對工作簿執(zhí)行保存操作后，刪除、移動、復(fù)制、重命名、隱藏工作表或插入新的工作表等操作均無效（但允許對工作表內(nèi)的數(shù)據(jù)進(jìn)行操作）。（4）要撤銷工作簿的保護(hù)，可單擊“審閱”選項(xiàng)卡“保護(hù)”組中的“保護(hù)工作簿”按鈕，在打開的對話框中輸入保護(hù)工作簿時設(shè)置的密碼然后保存，即可撤銷工作簿的保護(hù)。2．保護(hù)工作表（1）在工作簿中選擇要進(jìn)行保護(hù)的工作表，如“成績數(shù)據(jù)”，然后單擊“審閱”選項(xiàng)卡“保護(hù)”組中的“保護(hù)工作表”按鈕，或在右鍵菜單中選擇“保護(hù)工作表”選項(xiàng)，打開“保護(hù)工作表”對話框。（2）在“取消工作表保護(hù)時使用的密碼”編輯框中輸入密碼；在“允許此工作表的所有用戶進(jìn)行”列表框中選擇允許操作的選項(xiàng)，然后單擊“確定”按鈕，并在隨后打開的對話框中輸入同樣的密碼后單擊“確定”按鈕。（3）此時，工作表中的所有單元格都被保護(hù)起來，不能進(jìn)行在“保護(hù)工作表”對話框中沒有選擇的操作。如果試圖進(jìn)行這些操作，系統(tǒng)會彈出提示對話框，提示用戶該工作表是受保護(hù)。（4）要撤銷工作表的保護(hù)，只需單擊“審閱”選項(xiàng)卡“保護(hù)”組中的“撤銷工作表保護(hù)”按鈕。若設(shè)置了密碼保護(hù)，此時會打開“撤銷工作表保護(hù)”對話框，輸入保護(hù)時的密碼，方可撤銷工作表的保護(hù)。3．保護(hù)單元