信息技術基礎 教案-教學設計 單元2 電子表格處理

《信息技術基礎》教學設計案例名稱2.1員工培訓成績表制作學時4案例簡介本案例為培養(yǎng)造就德才兼?zhèn)涞母咚刭|人才，江南錫商科技有限公司準備為企業(yè)員工進行企業(yè)文化、職業(yè)素質、信息安全、創(chuàng)新思維等方面進行培訓，突出技術創(chuàng)新能力、善于解決復雜工程高技能人才隊伍。并對培訓的成績表進行匯總和分析，統(tǒng)計培訓各科成績的總分、平均分并對各科總分進行排名；對單科成績的最高分、最低分，平均分等相關數(shù)據(jù)統(tǒng)計。對培訓成績單科優(yōu)秀和各科都優(yōu)秀的職工發(fā)放獎勵。知識目標1）了解工作簿和工作表的概念。2）理解單元格名稱和活動單元格。3）了解填充柄的作用。能力目標1）能編輯Excel電子表格數(shù)據(jù)。2）能設置Excel電子表格的格式。3）能應用Excel電子表格的函數(shù)。4）能使用Excel電子表格的條件格式。素養(yǎng)目標1）養(yǎng)成團隊協(xié)作意識和較強的溝通能力。2）具有較高的職業(yè)素養(yǎng)和創(chuàng)新能力。教學內(nèi)容教學過程【教學情境導入】江南錫商科技有限公司準備為企業(yè)員工進行企業(yè)文化、職業(yè)素質、信息安全、創(chuàng)新思維等方面進行培訓，并對培訓成績單科優(yōu)秀和各科都優(yōu)秀的職工發(fā)放獎勵?！締栴}導入】如何快捷地統(tǒng)計并發(fā)放獎金？（目的：引導學生進入EXCEL電子表格處理學習，從整體上把握本單元將要講述的核心問題；教學手段：提問與講解相結合）【任務分析】在Excel中要完成該案例需要使用數(shù)據(jù)驗證、填充柄、函數(shù)計算、條件格式等功能。其具體操作可分為5個任務：新建工作簿、編輯數(shù)據(jù)、計算數(shù)據(jù)、引用不同工作表數(shù)據(jù)計算、條件格式的設置?！緦W習任務】2.1.1新建工作簿【任務說明】任務說明新建工作簿，并在輸入內(nèi)容后以“員工培訓獎勵.xlsx”保存工作簿?！救蝿詹襟E】1）啟動Excel2016程序。2）進入Excel2016的工作界面，如圖2-1所示。從標題欄可以看出，該工作簿的默認名稱為“工作簿1”，此工作簿包含1張默認的工作表名為Sheet1，單擊sheet1旁邊的加號按鈕，就可以添加一張新工作表sheet2。3）或者在指定目錄下建立“員工培訓獎勵.xlsx”工作簿，雙擊打開。4）在“sheet1”工作表中A1輸入“江南錫商科技有限公司員工培訓獎勵”。5）選中A2:G2單元格區(qū)域，單擊“開始”→“對齊方式”右側的對話框啟動器，在彈出的“設置單元格格式”對話框對齊”選項卡中,單擊選中“文本控制”下的“自動換行”，單擊“確定”按鈕。6）在A2到G2單元格依次輸入“員工號”，“企業(yè)文化（是否90分及以上）”，“職業(yè)素養(yǎng)（是否90分及以上）”，“信息安全（是否90分及以上）”，“創(chuàng)新思維（是否90分及以上）”，“有一科目是否90分及以上”，“所有科目是否90分及以上”，“獎勵總額”。7）在A3單元格中輸入員工號“001”?！菊n堂提問】：大家輸入員工號顯示正確嗎？【解決方法和思路】若直接輸入“001”，按【Enter】鍵后，“001”前面的“0”會被自動刪除。因為Excel程序將其默認為數(shù)值，數(shù)值前面的“0”是沒有意義的。正確的方法是，先輸入一個半角單引號（’），然后輸入數(shù)據(jù)。此時Excel程序將其認為是文本，并在單元格的左上角出現(xiàn)一個綠色的三角標注，如圖2-3所示，然后拖動填充柄填充到A42單元格?；蛘呦劝袮3單元格設置為文本格式，再輸入“001”，在A3單元格的左上角出現(xiàn)一個綠色的三角標注，然后拖動填充柄填充到A42單元格。8）利用填充柄輸入其他員工號。選中A3單元格，將鼠標指針移動到A3單元格右下角的黑色方塊上，當鼠標指針變成實心的黑色十字形時，向下拖動到A42，松開鼠標即可完成自動填充。9）選中A1:G1單元格區(qū)域，選擇“開始”→“對齊方式”組中→“合并后居中”按鈕。10）選中A1:G1單元格區(qū)域，選擇“開始”→“字體”→設置字體：黑體，字號：16號。選中A2:G2單元格區(qū)域，選擇“開始”→“字體”→設置字體：宋體，字號：12號，單擊B加粗。選中A3:G42單元格區(qū)域，選擇“開始”→“字體”→設置字體：宋體，字號：11號。11）選擇“開始”→“單元格”→“格式”→“行高”命令（或者右擊行號，在彈出的快捷菜單中選擇“行高”命令），在彈出的“行高”對話框中輸入行高值“30”，單擊“確定”按鈕。設置第二行的行高為28磅，其他行高設置為16磅。12）選中A列，選擇“開始”→“單元格”→“格式”→“列寬”命令（或者右擊列標A，在彈出的快捷菜單中選擇“列寬”命令），在彈出的“列寬”對話框中輸入列寬值“6”，單擊“確定”按鈕，如圖2-5所示。13）用同樣方法設置B:F的列寬設置為17.5磅，G列的列寬為8磅。14）雙擊B2單元格在編輯狀態(tài)下，把插入點移到移到企業(yè)文化后，按住ALT+Enter鍵強制換行。把C2:F2各列內(nèi)容也強制換行。15）選中A2:G42單元格區(qū)域，選擇“開始”→“字體”→“邊框”→“其他邊框”命令，在彈出的“設置單元格格式”對話框（如圖2-8）的“邊框”選項卡中，設置“線條樣式”為粗實線，單擊“外邊框”按鈕，再選擇細實線，單擊“內(nèi)部”按鈕，單擊“確定”按鈕。圖2-8“設置單元格格式”對話框16）選中A2:H2單元格區(qū)域，選擇“開始”→“字體”→“填充顏色”→“白色，背景1，深色25%”命令。17）單擊“保存”按鈕，打開“另存為”對話框，輸入文件名為“員工培訓獎勵”，單擊“確定”按鈕。若用第3步建立的“員工培訓獎勵.xlsx”工作簿，只需單擊“快速訪問工具欄”上的“保存”按鈕即可?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導?！菊n堂提問】如何在一列或一行相鄰單元格中輸入相同（或有規(guī)律）的數(shù)據(jù)？【教師提示】如果要在一列或一行相鄰單元格中輸入相同（或有規(guī)律）的數(shù)據(jù)，可先輸入第1（第2）個數(shù)據(jù)，再按住填充柄（單元格右下角的黑色方塊）拖動。還可以選擇“開始”→“編輯”→“填充”命令進行其他方式的自動填充?！菊n后思考】怎樣在工作表中輸入不同類型的數(shù)值數(shù)據(jù)如正、負數(shù)、分數(shù)、百分數(shù)等？2.1.2編輯數(shù)據(jù)【任務說明】在“員工培訓成績表.xlsx”工作簿中的“員工培訓成績表”工作表中按照要求輸入內(nèi)容，并在輸入內(nèi)容后保存?！救蝿詹襟E】1）打開“員工培訓成績表.xlsx”工作簿，單擊A1單元格，選擇“開始”→“單元格”→“插入”→“插入工作表行（R）”，在第一行前插入一空行。2）單擊“員工姓名”，選擇“開始”→“單元格”→“插入”→“插入工作表列（C）”，在“員工姓名”前插入一空列；再選中“員工姓名”后兩列，選擇“開始”→“單元格”→“插入”→“插入工作表列（C）”，在“員工姓名”后添加兩空列。3）在A1輸入“江南錫商科技有限公司員工培訓成績表”。4）在A2輸入“員工號”，填充A3:A42的員工號，“員工號”第一個值為“001”。5）在C2,D2單元格依次輸入“性別”，“所在部門”，要求性別、所在部門的輸入要使用“數(shù)據(jù)驗證”的序列輸入。6）選中C3:C42單元格區(qū)域，選擇“數(shù)據(jù)”→“數(shù)據(jù)工具”→“數(shù)據(jù)驗證”→“數(shù)據(jù)驗證”命令，如圖2-10所示，彈出“數(shù)據(jù)驗證”對話框，在“允許”下拉列表中選擇“序列”選項，在“來源”輸入框輸入“男,女”，注意序列間的分隔符號必須使用英文輸入法下的逗號，如圖2-11所示，單擊“確定”按鈕,參照樣張，選擇錄入性別“男”或者“女”。7）用同樣的方法設置所在部門的D3:D42序列，注意序列間的分隔符號必須使用英文輸入法下的逗號，然后參照樣張，選擇相應序列，錄入員工所在部門。8）在A43，A44，A45依次輸入“各科最高成績”，“各科最低成績”，“各科平均成績”。9）選中A43:D43單元格區(qū)域，單擊“開始”→“對齊方式”→“合并后居中”按鈕。同樣方法設置A44:D44單元格區(qū)域、A45:D45單元格區(qū)域合并后居中。10）在I43，I44，I45依次輸入“員工總人數(shù) ”，“女員工總人數(shù)”，“各科總分低于300分人數(shù)”。11）選中I43:J43單元格區(qū)域，單擊“開始”→“對齊方式”→“合并后居中”按鈕。同樣方法設置I44:J44單元格區(qū)域、I45:J45單元格區(qū)域合并后居中。12）設置A1:K1合并后居中，字體為黑體、16號；A2:K2字體為宋體、11號、加粗、居中；A3:K45字體為宋體、11號、居中。13）設置A3:H45單元格區(qū)域外邊框為粗邊框，內(nèi)邊框為細邊框。14）單擊“保存”按鈕，保存“員工培訓成績表.xlsx”工作簿，最后完成?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導?！菊n堂提問】合并并居中與跨列居中有什么區(qū)別？【教師提示】合并并居中是將選定的單元格全部合并起來，成為一個單元格，再將其中的內(nèi)容居中，而跨列居中僅將每個單元格的內(nèi)容居中，如果第一個單元格的內(nèi)容比單元格寬度長，跨列居中可以將該內(nèi)容居中在你選定的幾個單元格的總寬度之間，但是單元格并沒有合并?！菊n后思考】怎樣在工作表中輸入正確的身份證號碼和電話號碼？2.1.3計算數(shù)據(jù)【任務說明】在“員工培訓成績表.xlsx”工作簿中，按照以下要求計算：各科總分、各科平均分；各科最高成績、各科最低成績、各科平均成績；員工人數(shù)、女員工人數(shù)、各科總分低于300分人數(shù)、并使用Rank函數(shù)按照總分降序排名?！救蝿詹襟E】1）打開“員工培訓成績表.xlsx”。用SUM函數(shù)計算各科總分。單擊“各科總分”下的I3單元格，選擇“公式”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“選擇函數(shù)”列表框中選擇“SUM”函數(shù)。2）單擊“確定”按鈕，彈出如圖2-15所示的“函數(shù)參數(shù)”對話框，在Number1文本框中自動出現(xiàn)一個求和的范圍，檢查是否是計算總分數(shù)值的范圍。若范圍不正確，可對其進行修改。最后單擊“確定”按鈕，完成“各科總分”的計算。（注意：sum函數(shù)參數(shù)可以有1-255的參數(shù)，Number1里的參數(shù)必須要有，其他參數(shù)可以省略）。圖2-15“函數(shù)參數(shù)”對話框3）利用填充柄計算其他員工的各科總分。單擊I3單元格，將鼠標指針移至其右下角的填充柄上，當指針形狀變成實心的黑色十字形時，向下拖動直至I42單元格；或者單擊I3單元格，將鼠標指針移至其右下角的填充柄上雙擊。此時完成所有員工的各科總分的計算。4）用AVERAGE函數(shù)計算各科總分。單擊選中“各科平均分”下的J3單元格，選擇“公式”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“選擇函數(shù)”列表框中選擇“AVERAGE”函數(shù)，單擊“確定”按鈕，在彈出的“函數(shù)參數(shù)”對話框中將默認參數(shù)“E3:I3”修改為“E3:H3”，單擊“確定”按鈕。5）利用填充柄對員工各科平均分進行計算。單擊I3單元格，將鼠標指針移至其右下角，當指針形狀變成實心的黑色十字形時，向下拖動直至I42單元格。此時完成所有學生總分的計算。6）用RANK函數(shù)按照總分降序排名次。單擊“總分排名”下的K3單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”下輸入“RANK”，單擊“轉到(G)”按鈕，然后在“選擇函數(shù)”列表框中選擇“RANK”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)。單擊“確定”按鈕，利用填充柄向下填充其他排名數(shù)據(jù)。7）使用MAX函數(shù)各科計算最高成績，選中E43，插入函數(shù)，選擇MAX函數(shù)，選擇函數(shù)參數(shù)E3:E42，單擊“確定”按鈕，利用填充柄向右填充到H43即可。8）使用MIN函數(shù)計算各科最低成績，選中E44，插入函數(shù)，選擇MIN函數(shù)，選擇函數(shù)參數(shù)E3:E42，單擊“確定”按鈕，利用填充柄向右填充到H44即可。9）使用AVERAGE函數(shù)計算各科平均成績，選中E45，插入函數(shù)，選擇AVERAGE函數(shù)，選擇函數(shù)參數(shù)E3:E42，單擊“確定”按鈕，利用填充柄向右填充到H45即可。10）使用COUNT函數(shù)統(tǒng)計員工人數(shù)，選中K43，插入函數(shù)，選擇COUNT函數(shù)，選擇函數(shù)參數(shù)K3:K42，單擊“確定”按鈕。11）使用COUNTIF函數(shù)統(tǒng)計女員工人數(shù)，選中K44，插入函數(shù)，搜索選擇COUNTIF函數(shù)，設置函數(shù)參數(shù)，單擊“確定”按鈕。12）使用COUNTIF函數(shù)統(tǒng)計各科總分低于300分人數(shù)，插入函數(shù)，選擇COUNTIF函數(shù)，設置函數(shù)參數(shù)如圖2-19所示，單擊“確定”按鈕。13）最后計算完成后效果如圖2-20所示，保存工作簿。單擊左上角的“自定義快速訪問工具欄”上的“保存”按鈕，保存“員工培訓成績表.xlsx”?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導?！菊n堂提問】為什么在RANK函數(shù)第二個參數(shù)$I$3:$I$42，單元格區(qū)域的列標和行號前都加上一個“$”符號呢？【教師提示】這是Excel中的絕對引用，RANk函數(shù)的第二個參數(shù)是$I$3:$I$42，這是I3:I42單元格區(qū)域的絕對引用，$I$3:$I$42不會隨著公式的變化而變化。這里是為了在利用填充柄進行公式的填充時使$I$3:$I$42單元格區(qū)域的位置保持不變，得到正確的名次結果，如果引用I3:I42單元格區(qū)域，就是I3:I42單元格區(qū)域的相對引用，拖動填充柄向下填充時，單元格區(qū)域的也會向下移動，就會得到錯誤結果。單元格地址：可以通過選中單元格地址后按【F4】鍵在相對地址、絕對地址和混合地址之間相互轉換。RANk函數(shù)的第三個參數(shù)是0或忽略，表示降序排名次，總分最高分是第一名；非零，表示升序排名次，總分最高分是最后一名。2.1.4跨工作表引用數(shù)據(jù)【任務說明】在打開的“員工培訓成績表.xlsx”工作簿和“員工培訓獎勵.xlsx”工作簿中，把“員工培訓獎勵.xlsx”工作簿中的“員工培訓獎勵”工作表復制到“員工培訓成績表”工作簿中的“員工培訓成績表”工作表之后，關閉“員工培訓獎勵.xlsx”工作簿，保存“員工培訓成績表.xlsx”工作簿。在“員工培訓成績表.xlsx”工作簿的“員工培訓獎勵”工作表中，按照以下要求計算：使用if函數(shù)單科成績90分及以上包含90分設置為1，否則為0；使用if函數(shù)計算所有科目90分及以上包含90分設置為1，否則為0；計算獎勵總額:單科成績90分及以上獎金每科100元，所有科目90分及以上獎金500元，最后保護的“員工培訓獎勵”工作表?！救蝿詹襟E】1）重命名工作表：打開“員工培訓獎勵.xlsx”工作簿，雙擊“sheet1”工作表標簽或右擊“sheet1”工作表標簽→選擇“重命名”→輸入“員工培訓成績表”。打開“員工培訓成績表.xlsx”工作簿，用同樣方法重命名“sheet1”工作表名為“員工培訓獎勵”。2）不同工作簿之間復制工作表。在“員工培訓獎勵”工作簿中，右擊“員工培訓獎勵”工作表標簽→選擇“移動或復制(M)”命令。3）打開“移動或復制工作表”對話框（如圖2-22），在“將選定工作表移至工作簿（T）”下選擇“員工培訓成績表.xlsx”工作簿，在“下列選定工作表之前(B):”下選擇“（移至最后）”，單擊選中“建立副本”前的復選框，單擊“確定”按鈕?！菊n堂提問】“移動或復制工作表”操作有什么不同？【教師提示】打開“移動或復制工作表”對話框，在“將選定工作表移至工作簿（T）”下選擇“員工培訓成績表.xlsx”工作簿，在“下列選定工作表之前(B):”下選擇“（移至最后）”，單擊選中“建立副本”前的復選框，單擊“確定”按鈕，這是復制操作，。打開“移動或復制工作表”對話框，在“將選定工作表移至工作簿（T）”下選擇“員工培訓成績表.xlsx”工作簿，在“下列選定工作表之前(B):”下選擇“（移至最后）”，單擊“確定”按鈕，這是移動操作。移動或復制工作表”操作的不同就在于是否選中“建立副本”前的復選框，選中“建立副本”前的復選框，就是復制操作。4）退出“員工培訓獎勵.xlsx”工作簿，保存“員工培訓成績表.xlsx”工作簿，單擊“員工培訓獎勵”工作表標簽進行計算。5）使用IF函數(shù)計算單科成績90分及以上設置為1，否則為0。選中B3單元格，選擇“公式”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“選擇函數(shù)”列表框中選擇“IF”函數(shù)，打開“IF函數(shù)參數(shù)”對話框，光標在第一個參數(shù)閃動，單擊“員工培訓成績表”標簽，再單擊E3單元格，如圖2-23輸入各參數(shù)，單擊“確定”按鈕。雙擊右下角的填充柄填充下面數(shù)據(jù)。6）同樣方法計算C、D、E列數(shù)據(jù)或拖動填充C、D、E列數(shù)據(jù)。7）使用IF函數(shù)計算所有科目90分及以上設置為1，否則為0。選中F3單元格，選擇“公式”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“選擇函數(shù)”列表框中選擇“IF”函數(shù)，設置IF函數(shù)參數(shù)后，單擊“確定”按鈕。8）利用公式計算獎勵總額。Excel中公式以“=”開頭，選中F3單元格，輸入公式：“=COUNTIF(B3:E3,1)*100+F3*500”或者“=SUM(B3:E3)*100+F3*500”后回車即可。9）保護“員工培訓獎勵”工作表。選中“員工培訓獎勵”工作表，選擇“審閱”→“更改”→“保護工作表”命令，彈出“保護工作表”對話框，在“取消工作表保護時使用的密碼”文本框中輸入密碼，如圖2-25所示，單擊“確定”按鈕，在彈出的“確認密碼”對話框中再次輸入密碼，單擊“確定”按鈕?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導。2.1.5設置條件格式【任務說明】利用條件格式藍色數(shù)據(jù)條漸變填充修飾“員工培訓成績表”工作表的“各科總分”列(I3:I42單元格區(qū)域）；將排名前三位的相應單元格背景顏色設置成紅色?！救蝿詹襟E】1）利用條件格式藍色數(shù)據(jù)條漸變填充修飾各科總分列。如圖2-26所示，選中I3:I42單元格區(qū)域，選擇“開始”→“樣式”→“條件格式”→“數(shù)據(jù)條”→漸變填充下的“藍色數(shù)據(jù)條”命令。圖2-26條件格式藍色數(shù)據(jù)條漸變填充2）將排名前三位的相應單元格背景顏色設置成紅色。選中k3:K42單元格區(qū)域，選擇“開始”→“樣式”→“條件格式”→“突出顯示單元格規(guī)則”→“其他規(guī)則”命令，在彈出的“新建格式規(guī)則”對話框、中設置“單元格值”為“小于或等于”，輸入數(shù)值3，單擊“格式（F）…”按鈕，在彈出的“設置單元格格式”對話框中在“填充”選項卡下，將背景色設置為紅色，單擊“確定”按鈕?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導?！菊n后思考】怎樣在工作表中標記計算機前10%的成績或后十名的成績？單元小結本項目案例是完成一個員工培訓成績表的制作。該項目案例通過新建工作簿并輸入數(shù)據(jù)、通過對員工培訓成績表工作簿的數(shù)據(jù)計算、統(tǒng)計、排名次等操作，最后通過跨工作簿完成工作表的復制，跨工作表數(shù)據(jù)計算，以及條件格式的設置等來完成。對于初學者能掌握利用填充柄完成數(shù)據(jù)的輸入及數(shù)據(jù)的計算，可以提高學習者制作表格和表格計算的能力。課后練習完成中國大學MOOC（愛課程）平臺上的全部測驗和課程討論話題?！缎畔⒓夹g基礎》教學設計案例名稱員工信息統(tǒng)計表制作學時4案例簡介為建設一支素質優(yōu)良的人才隊伍，江南錫商科技有限公司將對企業(yè)員工信息建立員工信息表，包括員工基本信息如：員工號、姓名、性別、學歷、職稱、年齡、工齡、聯(lián)系電話等信息。并對員工學歷、職稱等信息進行相關統(tǒng)計，以便于合理安排工作，保證企業(yè)的可持續(xù)發(fā)展。在Excel中要完成該案例要學會應用Excel中的文本函數(shù)、日期函數(shù)、查找函數(shù)、統(tǒng)計函數(shù)等多種函數(shù)在實際生活中的靈活應用。知識目標掌握公式的組成及使用。掌握函數(shù)的組成及使用。能力目標能應用函數(shù)從已有信息提取有用數(shù)據(jù)。能熟練使用Excel的中文本函數(shù)。能熟練使用Excel中的日期函數(shù)。能使用相關函數(shù)統(tǒng)計數(shù)據(jù)。素養(yǎng)目標(1)具有數(shù)據(jù)意識和數(shù)據(jù)中挖掘信息的能力。(2)養(yǎng)成不斷精進技能的習慣，提高數(shù)據(jù)處理能力。教學內(nèi)容教學過程【問題導入】員工信息是怎樣計算出來的呢？（目的：引導學生進入課程，從整體上把握本單元將要講述的核心問題；教學手段：提問與講解相結合）【案例分析】在Excel中要完成該案例要學會應用Excel中的文本函數(shù)、日期函數(shù)、查找函數(shù)、統(tǒng)計函數(shù)等多種函數(shù)在實際生活中的靈活應用。任務一編輯員工信息【任務分析】打開“員工信息統(tǒng)計表.xlsx”工作簿，利用已有的信息，完善員工信息統(tǒng)計表數(shù)據(jù)?！救蝿罩v演】1）使用MID函數(shù)輸入員工的出生年，員工的身份證號包含了員工的很多信息，第7位到第11位是員工的出生年。選中D3單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”下輸入“MID”，單擊“轉到(G)”按鈕，然后在“選擇函數(shù)”列表框中選擇“MID”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)。單擊“確定”按鈕，利用填充柄向下填充其他數(shù)據(jù)。2）用TEXT函數(shù)和MID函數(shù)提取身份證號碼的出生日期或者用DATE函數(shù)和MID函數(shù)，輸入員工的出生日期。選中E3單元格，輸入公式“=--TEXT(MID(C3,7,8),"0000-00-00")”或者輸入公式“=DATE(MID(C3,7,4),MID(C3,11,2),MID(C3,13,2))”,按回車鍵即可，雙擊填充柄向下填充其他數(shù)據(jù)。3）使用MID函數(shù)取身份證第17位。選中F3單元格，輸入公式“=MID(C3,17,1)”,按回車鍵即可，雙擊填充柄向下填充其他數(shù)據(jù)。4）使用ISODD函數(shù)判斷第17位是否奇數(shù)。選中G3單元格，輸入公式“=ISODD(F3)”或“=ISODD(MID(C3,17,1))”,按回車鍵即可，雙擊填充柄向下填充其他數(shù)據(jù)。5）使用IF函數(shù)輸入性別男，女。選中H3單元格，輸入公式“=IF(G3,”男”,”女”)”或者輸入公式“=IF(ISODD(MID(C3,17,1),”男”,”女”)”按回車鍵即可，雙擊填充柄向下填充其他數(shù)據(jù)。6）使用LEFT函數(shù)取身份證的前兩位。選中I3單元格，輸入公式“=LEFT(C3,2)”,按回車鍵即可，雙擊填充柄向下填充其他數(shù)據(jù)。7）使用VLOOKUP函數(shù)從“地區(qū)代碼與名稱”工作表中根據(jù)“地區(qū)的代碼”查找到相應的籍貫輸入。選中J3單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”下輸入“VLOOKUP”，單擊“轉到(G)”按鈕，然后在“選擇函數(shù)”列表框中選擇“VLOOKUP”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)。單擊“確定”按鈕，利用填充柄向下填充其他數(shù)據(jù)。8）計算工齡。工齡可以根據(jù)入職時間來計算，利用today()函數(shù)獲取當前日期，減去入職時間得到日期相差的天數(shù)，除以365得到相差的年數(shù)，利用INT()函數(shù)取得年數(shù)的整數(shù)。在P3單元格輸入公式“=INT((today()-O3)/365)”，按【Enter】鍵，并利用填充柄填充其他員工工齡。9）計算年齡。年齡說法比較多，這里采用的是基本計算年齡的方法，就是今年的年數(shù)減去出生年數(shù)，在Q3單元格輸入公式“=YEAR(TODAY())-D3”，按【Enter】鍵，設置Q3為數(shù)值，小數(shù)位數(shù)為0。并利用填充柄填充其他員工年齡。10）為保護隱私，使用REPLACE函數(shù)為聯(lián)系電話打碼，從第四位開始，中間的4位打*。選中S3單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”下輸入“REPLACE”，單擊“轉到(G)”按鈕，然后在“選擇函數(shù)”列表框中選擇“REPLACE”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)。單擊“確定”按鈕，利用填充柄向下填充其他數(shù)據(jù)?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導?！菊n堂提問】Excel公式中有哪些運算符，優(yōu)先級是怎樣的？【教師提示】運算符的優(yōu)先級運算符含義優(yōu)先級:（冒號）引用運算符1（空格）,（逗號）-（負號）負數(shù)2%（百分號）百分比3（脫字號）乘方4*和/乘和除5+和-加和減6&字符連接符7=比較運算符8<和><=>=<>【課后思考】如何正確引用單元格？任務二格式化工作表【任務分析】打開“員工信息統(tǒng)計表.xlsx”工作簿,對表格中的文本和數(shù)據(jù)進行字體格式的設置，行高、列寬的調整，以及對單元格的邊框和底紋的添加，使表格顯得更加美觀?！救蝿罩v演】1）設置標題行的行高為30，第二行的行高為45，其余行的行高為15。選中行1，選擇“開始”→“單元格”→“格式”→“行高”命令（或者右擊行號1，在彈出的快捷菜單中選擇“行高”命令），在彈出的“行高”對話框中輸入行高值“30”，單擊“確定”按鈕；選中行2，用同樣方法設置行2行高為45；選中A3:A42單元格區(qū)域，選擇“開始”→“單元格”→“格式”→“行高”命令，在彈出的“行高”對話框中輸入行高值“15”，單擊“確定”按鈕。2）設置標題行文字跨列居中，設置字體黑體、字號16號，其他行文字水平居中、垂直居中，宋體、11號。選中A1:T1單元格區(qū)域，單擊“開始”→“對齊方式”右側的“對齊方式”啟動器，在彈出的“設置單元格格式”對話框（如圖2-34）的“對齊”選項卡中，設置水平對齊方式為“跨列居中”，垂直對齊方式為“居中”；再選擇“字體”選項卡，設置字體為黑體、16號，單擊“確定”按鈕。選中A2:T42單元格區(qū)域，用同樣方法設置格式，水平對齊方式為“居中”，垂直對齊方式為“居中”，設置字體為宋體、11號。3）選中所有列自動調整列寬。選中A2:T42單元格區(qū)域，選擇“開始”→“單元格”→“格式”→“自動調整列寬”命令。4）選中A2:T42單元格區(qū)域，選擇“開始”→“字體”→“邊框”→“其他邊框”命令，在彈出的“設置單元格格式”對話框的“邊框”選項卡中，設置“線條樣式”為雙實線，顏色深藍色，單擊“外邊框”按鈕，再選擇細實線，顏色深藍色，單擊“內(nèi)部”按鈕，單擊“確定”按鈕。5）選中A2:T2單元格區(qū)域，選擇“開始”→“字體”→“填充顏色”→“白色，背景1，深色25%”命令。完成效果如圖所示【任務操作】根據(jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導?！菊n堂提問】合并并居中與跨列居中有什么區(qū)別？【教師提示】合并并居中是將選定的單元格全部合并起來，成為一個單元格，再將其中的內(nèi)容居中，而跨列居中僅將每個單元格的內(nèi)容居中，如果第一個單元格的內(nèi)容比單元格寬度長，跨列居中可以將該內(nèi)容居中在你選定的幾個單元格的總寬度之間，但是單元格并沒有合并?！菊n后思考】怎樣在工作表中輸入正確的身份證號碼？任務三統(tǒng)計員工信息【任務分析】打開“員工信息統(tǒng)計表.xlsx”工作簿,單擊“員工信息統(tǒng)計”工作表標簽，完成統(tǒng)計表1、統(tǒng)計表2、統(tǒng)計表3的數(shù)據(jù)統(tǒng)計，插入圖表并美化?！救蝿罩v演】1）使用COUNTIF函數(shù)統(tǒng)計學歷是?？?、本科、碩士人數(shù)。選中B3單元格，選擇“公式”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“選擇函數(shù)”列表框中選擇“COUNTIF”，單擊“確定”按鈕，打開COUNTIF函數(shù)參數(shù)對話框，第一個參數(shù)Range，選擇員工信息表中的K3:K42，按【F4】變成絕對地址，第二個參數(shù)單擊A3單元格，單擊“確定”按鈕，將鼠標指針移B3單元格右下角的填充柄上→鼠標指針變?yōu)楹谏?”時雙擊鼠標左鍵→完成填充。2）使用公式計算不同學歷人數(shù)所占百分比。選中C3單元格，輸入公式“=B3/SUM($B$3:$B$5)”,按回車鍵即可，將鼠標指針移C3單元格右下角的填充柄上→鼠標指針變?yōu)楹谏?”時雙擊鼠標左鍵→完成填充。3）設置C3:C5單元格區(qū)域數(shù)值為百分比格式并保留兩位小數(shù)。選中C3:C5單元格區(qū)域選擇“開始”→“數(shù)字”啟動器→打開“設置單元格格式”對話框→“數(shù)字”選項卡→“百分比”→小數(shù)位數(shù)2位，單擊“確定”按鈕。4）使用COUNTIFS函數(shù)計算統(tǒng)計表2的數(shù)據(jù)。單擊選中G3單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”下輸入“COUNTIFS”，單擊“轉到(G)”按鈕，然后在“選擇函數(shù)”列表框中選擇“COUNTIFS”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)。單擊“確定”按鈕，利用填充柄向下填充到G5單元格。5）用4）同樣的方法，使用COUNTIFS函數(shù)計算G6:G8、G9:G11、G12:G14單元格區(qū)域的值。6）使用SUMIF函數(shù)計算統(tǒng)計表3的數(shù)據(jù)。單擊選中G3單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”下輸入“SUMIF”，單擊“轉到(G)”按鈕，然后在“選擇函數(shù)”列表框中選擇“SUMIF”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)。單擊“確定”按鈕，利用填充柄向下填充到K5單元格。7）使用餅圖直觀顯示不同學歷占比。先選中A2:A5單元格區(qū)域，按住Ctrl鍵，再選中C2:C5，選擇“插入”→“圖表”→“餅圖”→“二維餅圖”下的“餅圖”，就會插入一個餅圖。8）將圖表插入到當前工作表的“A8:C20"單元格區(qū)域內(nèi)。選中圖表→光標呈現(xiàn)四向箭頭時按住鼠標左鍵不放將其拖動到“A8:C20”單元格區(qū)域內(nèi)。9）選中圖表→圖表工具→設計→“圖表布局”組中→單擊添加“圖表元素”下拉按鈕→在彈出的下拉列表中選擇→數(shù)據(jù)標簽→“數(shù)據(jù)標簽外(O)”命令。10）選中圖表→圖表工具→設計→“圖表布局”組中→單擊添加“圖表元素”下拉按鈕→在彈出的下拉列表中選擇【圖表標題】下的(圖表上方】→將其標題修改為“學歷占比圖”。11）使用簇狀柱形直觀顯示不同職稱人數(shù)對比。先選中J2:K5單元格區(qū)域，選擇"插入"→"圖表"→"插入柱形圖或條形圖"→"二維柱形圖"下的"簇狀柱形圖"，插入一個簇狀柱形圖。12）參照9）10）在數(shù)據(jù)標簽內(nèi)顯示人數(shù)，修改圖表標題為“職稱人數(shù)對比圖”。13）不顯示圖例。圖表工具→設計→“圖表布局”組→單擊添加“圖表元素”下拉按鈕→在彈出的下拉列表中選擇【圖例】→“無”?；蜻x中圖例，按【Delete】鍵刪除。14）選中圖表→光標呈現(xiàn)四向箭頭時按住鼠標左鍵不放將其拖動到“J8:N20”單元格區(qū)域內(nèi)。15以“布局2”和“樣式4”修飾圖表。圖表工具→設計→“圖表布局”組-快速布局→選擇“布局2”，圖表樣式→選擇“樣式4”修飾圖表。最后完成效果如圖所示?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導。【課堂提問】圖表的編輯包括哪些方面?【教師提示】更改圖表類型：在圖表空白處右擊，在彈出的快捷菜單中選擇“圖表類型”命令。更改數(shù)據(jù)源：在圖表的數(shù)據(jù)系列上右擊，在彈出的快捷菜單中選擇“選擇數(shù)據(jù)”命令，在彈出的“選擇數(shù)據(jù)源”對話框中設置數(shù)據(jù)區(qū)域。行列數(shù)據(jù)對換：選中圖表后，選擇“圖表工具-設計”→“數(shù)據(jù)”→“切換行/列”命令。設置圖表選項：包括修改圖表標題、添加網(wǎng)格線、更改圖例、添加數(shù)據(jù)標志等。設置圖表格式：包括設置圖表標題格式、設置坐標軸格式、設置數(shù)據(jù)系列格式、設置圖表區(qū)和繪圖區(qū)格式等?！菊n后思考】怎樣合理選擇圖表類型？單元小結本項目案例是完成一個員工信息統(tǒng)計表的制作。該項目案例通過編輯員工信息、格式化員工信息表、員工信息統(tǒng)計并對員工統(tǒng)計信息進行了圖表可視化展示，能夠通過身份證信息提取有用信息，能夠熟練相關函數(shù)的使用，使學生處理數(shù)據(jù)的能力大大增強。課后練習完成中國大學MOOC（愛課程）平臺上的全部測驗和課程討論話題?！缎畔⒓夹g基礎》教學設計案例名稱員工工資查詢表制作學時4案例簡介本項目案例是完成一個員工工資表的制作以及工資信息的查詢。該項目案例完成要學會應用Excel中的IF函數(shù)、公式、查找函數(shù)等多種數(shù)據(jù)計算在實際生活中的應用，以及利用自動篩選功能和高級篩選功能篩選出符合要求員工的信息。知識目標(1)了解函數(shù)的嵌套的含義和使用。(2)了解各種函數(shù)的使用。能力目標(1)能熟練掌握IF函數(shù)的嵌套應用。(2)能掌握絕對地址的使用。(3)能篩選Excel電子表格的數(shù)據(jù)。素養(yǎng)目標(1)養(yǎng)成職業(yè)規(guī)劃能力，具有終身學習能力。(2)養(yǎng)成數(shù)據(jù)保護習慣和大局觀的責任意識。教學內(nèi)容教學過程【問題導入】工資數(shù)據(jù)如何快速錄入并進行相關計算呢？（目的：引導學生進入課程，從整體上把握本單元將要講述的核心問題；教學手段：提問與講解相結合）【案例分析】2023年4月初，江南錫商科技有限公司圍繞構建科學規(guī)范的人才評價機制，將根據(jù)員工職稱、職務、學歷、績效考核信息并根據(jù)相關規(guī)定計算員工的各項扣款項目重新完善員工3月工資，員工根據(jù)員工號查詢員工姓名信息和工資明細。錄入基礎數(shù)據(jù)，要求如下。錄入基本工資（使用IF()函數(shù)），助理工程師：3000，工程師：3600，高級工程師：4000錄入崗位工資（使用IF()函數(shù)），職員：3000，部門經(jīng)理：4800 錄入學歷工資（使用IF()函數(shù)），碩士：800，本科：500，?？疲?00 計算數(shù)據(jù)。計算獎金：利用if函數(shù)，根據(jù)績效評分計算獎金，請根據(jù)“績效評分獎金計算規(guī)則”計算表中的數(shù)據(jù)計算獎金列（J4:J43單元格區(qū)域）的內(nèi)容。計算應發(fā)工資： 應發(fā)工資=基本工資+崗位工資+學歷工資+獎金+出差補貼計算“扣款項目”中的“扣款合計”。 ①計算“扣稅”?？鄱愐?guī)則：5000及以下不交稅，5000到8000之間交減去5000，再減去扣除的四金后差額的3%，8000到17000之間交交減去5000，再減去扣除的四金后差額的10%。 ②計算“扣款項目”中的“扣款合計”。 扣款合計=養(yǎng)老保險+醫(yī)療保險+失業(yè)保險+住房公積金+扣稅。 計算“實發(fā)工資”，實發(fā)工資=應發(fā)工資-扣款合計。查詢員工工資：利用員工號查詢數(shù)據(jù)，統(tǒng)計工資數(shù)據(jù)：完成工資數(shù)據(jù)統(tǒng)計表的數(shù)據(jù)統(tǒng)計。任務一錄入數(shù)據(jù)【任務分析】利用IF函數(shù)錄入基礎工資數(shù)據(jù)?！救蝿罩v演】1．錄入基本工資。助理工程師的基本工資是3000，工程師的基本工資是3600，高級工程師的基本工資是4000。使用IF函數(shù)錄入基本工資，選中F4，在F4中輸入公式:“=IF(D4="助理工程師",3000,IF(D4="工程師",3600,4000))”，按回車鍵，并利用填充柄填充其他員工基本工資。2．錄入崗位工資。職員的崗位工資是3000，部門經(jīng)理的崗位工資是4800。使用IF函數(shù)錄入崗位工資。選中G4，在G4中輸入公式:“=IF(E4="職員",3000,4800)”，按回車鍵，并利用填充柄填充其他員工崗位工資。3．錄入學歷工資，碩士的學歷工資是800，本科的學歷工資是500，?？频膶W歷工資是300。使用IF函數(shù)錄入學歷工資。選中H4，在H4中輸入公式:“=IF(C4="專科",300,IF(C4="本科",500,800))”，按回車鍵，并利用填充柄填充其他員工學歷工資?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導?！菊n堂提問】函數(shù)的輸入？函數(shù)嵌套？【教師提示】函數(shù)輸入方法1：直接在單元格中或編輯欄輸入，如:=SUM(H3:K3)。方法2：在“公式”功能區(qū)的“函數(shù)庫”分組中，單擊“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，選擇所需的函數(shù)，在函數(shù)參數(shù)對話框中輸入相應的參數(shù)即可。函數(shù)嵌套函數(shù)嵌套是指一個函數(shù)可以作為另一個函數(shù)的參數(shù)使用。例1：MAX(AVERAGE(A2:A8),C5,B5,D6)公式中MAX是第一級公式，AVERAGE是第二級公式。執(zhí)行順序：先執(zhí)行AVERAGE函數(shù)，再執(zhí)行MAX函數(shù)。例2：IF(ISODD(MID(D2,17,1))公式中IF是第一級公式，ISODD是第二級公式，MID是第三級公式。執(zhí)行順序：先執(zhí)行MID函數(shù)，再執(zhí)行ISODD函數(shù)，最后執(zhí)行IF函數(shù)?！菊n后思考】如何正確引用單元格？任務二計算數(shù)據(jù)【任務分析】利用公式/函數(shù)計算工資表中的各項數(shù)據(jù)?！救蝿罩v演】1.計算獎金。利用if函數(shù)，根據(jù)績效評分計算獎金。選中J4，在J4中輸入公式:“=IF(I4>=90,5000,IF(I4>=80,3000,IF(I4>=70,2000,IF(I4>=60,1000,600))))”，按回車鍵，并利用填充柄填充其他員工獎金。2.計算應發(fā)工資。選中L4，在L4中輸入公式:“=SUM(F4:H4,J4:K4)”，按回車鍵，并利用填充柄填充其他員工應發(fā)工資。3.計算養(yǎng)老保險。選中M4，在M4中輸入公式:“=L4*扣款比例!$A$2”，按回車鍵，并利用填充柄填充其他員工養(yǎng)老保險。4.計算養(yǎng)老保險。選中N4，在N4中輸入公式:“=L4*扣款比例!$B$2”，按回車鍵，并利用填充柄填充其他員工醫(yī)療保險。5.計算養(yǎng)老保險。選中O4，在O4中輸入公式:“=L4*扣款比例!$C$2”，按回車鍵，并利用填充柄填充其他員工失業(yè)保險。6.計算養(yǎng)老保險。選中P4，在P4中輸入公式:“=L4*扣款比例!$D$2”，按回車鍵，并利用填充柄填充其他員工。7.計算扣稅。選中Q4，在Q4中輸入公式:“=IF(L4<=5000,0,IF(L4<=8000,(L4-5000-SUM(M4:P4))*3%,(L4-5000-SUM(M4:P4))*10%))”，按回車鍵，并利用填充柄填充其他員工扣稅。8.計算養(yǎng)扣款合計。選中R4，在R4中輸入公式:“=SUM(M4:Q4)”，按回車鍵，并利用填充柄填充其他員工扣款合計。9.計算實發(fā)工資。選中S4，在S4中輸入公式:“=L4-R4”，按回車鍵，并利用填充柄填充其他員工實發(fā)工資。10.選中S4:S43單元格區(qū)域，選擇“開始”→“數(shù)字”→“貨幣”命令?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導?！菊n堂提問】公式錯誤代碼分別有什么樣的含義？【教師提示】公式錯誤代碼含義代碼含義###輸入的數(shù)據(jù)或計算結果太長#DIV/0！除數(shù)引用了零值單元格或空單元格#N/A公式中沒有可用數(shù)值，或缺少函數(shù)參數(shù)#NAME？公式中引用了無法識別的名稱，或刪除了公式中正使用的名稱#NULL！使用了不正確的區(qū)域運算符或引用的單元格區(qū)域的交集為空#NUM！公式產(chǎn)生的結果數(shù)字太大或太小，Excel無法表達出來#RTF公式引用的單元格被刪除，并且系統(tǒng)無法自動調整#VALUE公式或函數(shù)中的參數(shù)數(shù)據(jù)類型不匹配【課后思考】怎樣使用IF函數(shù)的多層嵌套？任務三統(tǒng)計工資查詢【任務分析】使用VLOOKUP函數(shù)實現(xiàn)員工工資數(shù)據(jù)的查詢，利用員工工資表統(tǒng)計員工工資數(shù)據(jù)。【任務講演】1.查詢員工姓名。使用VLOOKUP函數(shù)查詢員工姓名，單擊選中E4單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”下輸入“VLOOKUP”，單擊“轉到(G)”按鈕，然后在“選擇函數(shù)”列表框中選擇“VLOOKUP”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)，單擊“確定”按鈕。2.查詢員工實發(fā)工資。使用VLOOKUP函數(shù)，單擊選中E5單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，然后在“選擇函數(shù)”列表框中選擇“VLOOKUP”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)，單擊“確定”按鈕。3.查詢工資明細。使用VLOOKUP函數(shù)查詢員工工資明細的相關數(shù)據(jù)。參照1和2步驟，查詢完成工資明細中所有欄目的工資數(shù)據(jù)查詢,然后在E3單元格選擇001員工號，員工工資查詢結果并保存工作簿。4.計算統(tǒng)計表1中的數(shù)據(jù)。1）使用AVERAGEIF函數(shù)計算統(tǒng)計表1中不同職稱的職工平均應發(fā)工資。單擊選中B3單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”下輸入“AVERAGEIF”，單擊“轉到(G)”按鈕，然后在“選擇函數(shù)”列表框中選擇“AVERAGEIF”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)（如圖2-48），單擊“確定”按鈕。利用填充柄向下填充到B5單元格。2）使用COUNTIF函數(shù)計算統(tǒng)計表1中不同職稱的職工人數(shù)。選中C3，在C3中輸入公式:“=COUNTIF(員工工資表!$D$4:$D$43,A3)”，按回車鍵，利用填充柄向下填充到C5單元格。3）使用公式計算相應職稱人數(shù)占三種職稱人數(shù)的百分比。選中D3，在D3中輸入公式:“=C3/SUM($C$3:$C$5)”，按回車鍵，利用填充柄向下填充到D5單元格。5.計算統(tǒng)計表2中的數(shù)據(jù)。1）使用COUNTIFS函數(shù)計算統(tǒng)計表2中應發(fā)工資小于等于9000,并且職稱是助理工程師的人數(shù)。單擊選中C11單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“選擇函數(shù)”列表框中選擇“COUNTIFS”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)，單擊“確定”按鈕。2）使用COUNTIFS函數(shù)計算統(tǒng)計表2中應發(fā)工資大于9000小于等于10000,并且職稱是工程師的人數(shù)。單擊選中C12單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“選擇函數(shù)”列表框中選擇“COUNTIFS”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)，在設置好第三個條件區(qū)域后，第三個對應的條件參數(shù)需要移動右側的垂直滾動條或向下小箭頭，顯示第三個設置條件后，輸入第三個條件B12，單擊“確定”按鈕。3）使用COUNTIFS函數(shù)計算統(tǒng)計表2中應發(fā)工資大于10000,并且職稱是高級工程師的人數(shù)。單擊選中C13單元格，在C13中輸入公式:“=COUNTIFS(員工工資表!$L$4:$L$43,A13,員工工資表!$D$4:$D$43,B13)”，按回車鍵。4）使用AVERAGEIFS函數(shù)計算符合統(tǒng)計表2中應發(fā)工資小于等于9000,并且職稱是助理工程師的職工平均實發(fā)工資。單擊選中D11單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”下輸入“AVERAGEIFS”，單擊“轉到(G)”按鈕，然后在“選擇函數(shù)”列表框中選擇“AVERAGEIFS”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)，單擊“確定”按鈕。5）使用AVERAGEIFS函數(shù)計算統(tǒng)計表2中應發(fā)工資大于9000小于等于10000,并且職稱是工程師的職工平均實發(fā)工資。單擊選中D12單元格，選擇“公式”→“函數(shù)庫”→“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，在“選擇函數(shù)”列表框中選擇“AVERAGEIFS”函數(shù)，單擊“確定”按鈕。在彈出的“函數(shù)參數(shù)”對話框中設置函數(shù)參數(shù)，在設置第二個條件區(qū)域和條件后，顯示第三個對應的條件區(qū)域和條件參數(shù)需要移動右側的垂直滾動條或向下小箭頭，輸入第三個條件區(qū)域和條件B12后，單擊“確定”按鈕。6）使用AVERAGEIFS函數(shù)計算統(tǒng)計表2中應發(fā)工資大于10000,并且職稱是高級工程師的職工平均實發(fā)工資。單擊選中D13單元格，在D13中輸入公式:“=AVERAGEIFS(員工工資表!S4:S43,員工工資表!L4:L43,A13,員工工資表!D4:D43,B13)”，按回車鍵。員工工資統(tǒng)計結果并保存工作簿?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導?！菊n堂提問】運算符優(yōu)先級別？什么情況下必需使用高級篩選而不能使用自動篩選？【教師提示】運算符的優(yōu)先級運算符含義優(yōu)先級:（冒號）引用運算符1（空格）,（逗號）-（負號）負數(shù)2%（百分號）百分比3（脫字號）乘方4*和/乘和除5+和-加和減6&字符連接符7=比較運算符8<和><=>=<>任務四篩選數(shù)據(jù)【任務分析】利用自動篩選功能，篩選出職稱是“高級工程師”或“工程師”，并且“應發(fā)工資”在值范圍為8000～11000元的記錄；用高級篩選功能，篩選出基本工資大于等于4000元或應發(fā)工資大于10000元的員工信息?！救蝿罩v演】1.單擊行號“3”，即選中第3行所有數(shù)據(jù)。選擇“數(shù)據(jù)”→“排序和篩選”→“篩選”命令，此時會發(fā)現(xiàn)標題行上的每一個列字段單元格的右側都出現(xiàn)了一個“自動篩選”按鈕2.單擊列標題“職稱”右側的自動篩選按鈕，彈出相應的下拉列表，單擊在“文本篩選”全選按鈕前復選框，單擊選中“高級工程師”和“工程師”復選框前的復選框。會自動篩選出職稱是“高級工程師”或“工程師”的所有員工。3.再單擊列標題“應發(fā)工資”右側的自動篩選按鈕，彈出相應的下拉列表，并從中選擇“數(shù)字篩選”→“介于”選項，在彈出的“自定義自動篩選方式”對話框中設置篩選條件。4.使用高級篩選功能，篩選出基本工資大于等于4000元或應發(fā)工資大于11000元的員工信息。5.復制“員工工資表”。在“員工工資表”的工作表標簽上，單擊鼠標右鍵，在彈出的快捷菜單中選擇“移動或復制工作表”命令，單擊選中“建立副本”，單擊“確定”按鈕，默認復制的“員工工資表(2)”顯示在“員工工資表”前。單擊“數(shù)據(jù)”→“排序和篩選”→“篩選”命令，取消“自動篩選”。修改“員工工資表(2)”工作表名為“高級篩選”。6.在“高級篩選”工作表中，在與源數(shù)據(jù)空一行或空一列的區(qū)域設置高級篩選參數(shù)，例如在V3輸入“基本工資”，V4輸入“>=4000”，在W3輸入“應發(fā)工資”，W5輸入“>11000”,V2:W5為高級篩選的條件區(qū)域。7.單擊選中源數(shù)據(jù)任意一個單元格或選中A2:S45單元格區(qū)域，選擇“數(shù)據(jù)”→“排序和篩選”→“高級”命令，彈出“高級篩選”對話框，使用默認篩選方式“在原有區(qū)域顯示篩選結果(F)”單選按鈕，設置高級篩選參數(shù)，設置好參數(shù)后，單擊“確定”按鈕?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導?！菊n堂提問】什么情況下必需使用高級篩選而不能使用自動篩選？【教師提示】自動篩選和高級篩選都是用來篩選表格中的數(shù)據(jù)和文字,但是區(qū)別是自動篩選:只是表格中的數(shù)據(jù)在篩選出來后,還是在原來的表格上中。但是高級篩選是把表格中的數(shù)據(jù)篩選出來后,把數(shù)據(jù)復制到空白的單元格中。利用“自動篩選”查找合乎準則的記錄既方便又快速，但該命令的查找條件不能太復雜，如果要執(zhí)行比較復雜的查找，就必須使用高級篩選命令。單元小結本項目案例是完成一個員工工資查詢表的制作。該項目案例的主要任務包括利用IF函數(shù)的嵌套輸入員工基本數(shù)據(jù)，根據(jù)績效計算員工的獎金，使用公式和函數(shù)計算員工應發(fā)工資、扣款、實發(fā)工資等，根據(jù)員工號可以查詢員工姓名、實發(fā)工資及工資明細，最后利用自動篩選功能和高級篩選功能篩選出符合要求員工的信息。課后練習完成中國大學MOOC（愛課程）平臺上的全部測驗和課程討論話題，具體網(wǎng)址：《信息技術基礎》教學設計案例名稱銷售統(tǒng)計表制作學時4案例簡介本項目案例是對商品銷售數(shù)據(jù)進行分析，主要利用Excel中的分類匯總、圖表和數(shù)據(jù)透視表功能實現(xiàn)。知識目標（1）了解數(shù)據(jù)分析的工具。（2）理解圖表和數(shù)據(jù)透視表的應用。能力目標（1）能選擇合適的公式或函數(shù)參與運算。（2）能排序Excel電子表格的數(shù)據(jù)。（3）能分類匯總Excel電子表格的數(shù)據(jù)。（4）能創(chuàng)建與編輯Excel電子表格的圖表。（5）能制作Excel電子表格的數(shù)據(jù)透視表。素養(yǎng)目標（1）具備數(shù)據(jù)分析的能力，具有主人翁意識和競爭精神。（2）養(yǎng)成簡化數(shù)據(jù)表達的能力，增強數(shù)據(jù)可視化表達能力。教學內(nèi)容教學過程【問題導入】用什么樣的方法可以更加直觀地分析數(shù)據(jù)？（目的：引導學生進入課程，從整體上把握本單元將要講述的核心問題；教學手段：提問與講解相結合）【案例分析】2023年6月初，公司根據(jù)銷售部門5月份的某賣場終端機所傳送的銷售數(shù)據(jù)表，對銷售的各類商品在該賣場5月份的銷售額進行匯總分析，并制作圖表直觀反映銷售情況，利用數(shù)據(jù)透視表分析銷售情況，通過分析確定后半年的銷售策略。案例分析：在Excel中要完成該項目案例需要首先完善工作表的數(shù)據(jù)，通過對銷售數(shù)據(jù)排序、分類匯總、圖表創(chuàng)建、數(shù)據(jù)透視表等制作。其具體操作可分為5個任務：完善工作表數(shù)據(jù)、銷售數(shù)據(jù)排序、分類匯總數(shù)據(jù)、高級篩選和創(chuàng)建數(shù)據(jù)透視表。任務一完善數(shù)據(jù)【任務分析】根據(jù)“商品銷售情況表”中的數(shù)據(jù)。計算各商品的降價幅度，降價幅度=（市場定價-成交單價）/市場定價（數(shù)值百分比格式，保留兩位小數(shù)）；計算各商品的銷售額置于“銷售額(萬元)”列，銷售額(萬元)=數(shù)量*成交單價/10000。利用RANK.EQ函數(shù)銷售額排名由高到低的排名。利用IF函數(shù)給出“業(yè)績表現(xiàn)”列的內(nèi)容:如果銷售額大于50萬，在相應單元格內(nèi)填入“業(yè)績優(yōu)秀”，如果銷售額大于30萬，在相應單元格內(nèi)填入“業(yè)績良好”，如果銷售額大于20萬，在相應單元格內(nèi)填入"業(yè)績合格"否則在相應單元格內(nèi)填入"業(yè)績差"?！救蝿罩v演】1．計算各商品的降價幅度。將光標定位到H3單元格→在編輯欄輸入公式“=(E3-G3)/E3”→回車→將光標移動到H3單元格右下角，待光標變?yōu)楹谏旨軙r，雙擊即可；選中H3:H34，單擊“開始”→“數(shù)字”→“%”（或者Ctrl+Shift+%）,再單擊“增加小數(shù)位數(shù)”兩次。2．計算各商品銷售額（萬元）。將光標定位到J3單元格→在編輯欄輸入“=G3*I3/10000”→回車→將光標移動到J3單元格右下角，待光標變?yōu)楹谏旨軙r，雙擊即可。選中J3:J34設置小數(shù)位數(shù)兩位。3．計算銷售額排名。將光標定位到K3單元格→在編輯欄輸入“=RANK.EQ(J3,$J$3:$J$34,0)”→回車→將光標移動到K3單元格右下角，待光標變?yōu)楹谏旨軙r，雙擊即可。4．利用if函數(shù)，根據(jù)銷售額計算業(yè)績表現(xiàn)。將光標定位到L3單元格→在編輯欄輸入“=IF(J3>50,"業(yè)績優(yōu)秀",IF(J3>30,"業(yè)績優(yōu)良",IF(J3>20,"業(yè)績合格","業(yè)績差")))”→回車→將光標移動到L3單元格右下角，待光標變?yōu)楹谏旨軙r，雙擊即可。5．復制“商品銷售情況表”（移至最后）4次，依次命名為“銷售數(shù)據(jù)排序”、“分類匯總數(shù)據(jù)”、“高級篩選”、“數(shù)據(jù)透視表”?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導?！菊n堂提問】為什么在RANK函數(shù)參數(shù)J3和J34單元格的列標和行號前都加上一個“$”符號呢？【教師提示】這是Excel中的絕對引用，其形式為在行號和列標的前面加上“$”?？梢酝ㄟ^選中單元格地址后按【F4】鍵在相對地址、絕對地址和混合地址之間自動轉換。單元格的絕對引用不會隨著公式的變化而變化。這里是為了在利用填充柄進行公式的填充時使J3:J34單元格的位置保持不變。若此時使用相對引用（J3:J34），在利用填充柄進行公式的填充時將會得到錯誤的計算結果，或是錯誤提示。任務二排序數(shù)據(jù)【任務分析】在“銷售數(shù)據(jù)排序”工作表中，按主要關鍵字“銷售部門”自定義序列（銷售一部、銷售二部、銷售三部）排序和次要關鍵字“類別”的降序進行排序。本任務是對商品銷售數(shù)據(jù)進行排序?！救蝿罩v演】1．在“銷售數(shù)據(jù)排序”工作表中，選中A2:L34或單擊選中其中任意一個單元格，選擇“數(shù)據(jù)”→“排序和篩選”→“排序”命令，在彈出的“排序”對話框中設置“主要關鍵字”為“銷售部門”→“排序依據(jù)”默認數(shù)值→“次序”→“自定義序列”→彈出“自定義序列”對話框，輸入“銷售一部”→回車，輸入“銷售二部”→回車，輸入“銷售三部”→單擊“添加(A)”按鈕→單擊“確定”按鈕→返回到“排序”對話框。2．在上面的“排序”對話框中，單擊“添加條件(A)”按鈕→“次要關鍵字”右側的下拉小三角按鈕→選擇“類別”→“排序依據(jù)”默認數(shù)值→“次序”→“降序”→單擊“確定”按鈕。【任務操作】根據(jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導。任務三分類匯總【任務分析】在對數(shù)據(jù)進行分析時，可以用Excel提供的“分類匯總”功能，按要求進行匯總。在“分類匯總數(shù)據(jù)”工作表中，按主要關鍵字“銷售部門”自定義序列（銷售一部、銷售二部、銷售三部）排序和次要關鍵字“類別”的降序進行排序，目的是完成數(shù)據(jù)分類，然后按照要求進行數(shù)據(jù)分類匯總。本任務是對“分類匯總數(shù)據(jù)”工作表中的數(shù)據(jù)按“銷售部門”對“銷售額（萬元）”進行“求和”匯總，并對匯總數(shù)據(jù)進行數(shù)據(jù)圖表分析?！救蝿罩v演】1．按照任務2在“分類匯總數(shù)據(jù)”工作表中，按主要關鍵字“銷售部門”自定義序列（銷售一部、銷售二部、銷售三部）排序和次要關鍵字“類別”的降序進行排序，完成數(shù)據(jù)分類。2．將光標置于數(shù)據(jù)區(qū)域內(nèi)，選擇“數(shù)據(jù)”→“分級顯示”→“分類匯總”命令，彈出“分類匯總”對話框。3．在“分類字段”下拉列表中選擇“銷售部門”選項，在“匯總方式”下拉列表中選擇“求和”選項，在“選定匯總項”下拉列表中勾選“銷售額(萬元)”復選框，單擊“確定”按鈕，完成分類匯總。4．單擊工作表的左上方符號“2”，則可以顯示各個“銷售部門”的“銷售額（萬元）”匯總，而將其他的數(shù)據(jù)隱藏；單擊工作表的左上方符號“1”，則只顯示“銷售部門”的總計，“銷售額（萬元）”匯總，而將其他的數(shù)據(jù)隱藏。5．若要去掉分類匯總的顯示信息，再次選擇“數(shù)據(jù)”→“分級顯示”→“分類匯總”命令，在彈出的“分類匯總”對話框中單擊“全部刪除”按鈕。6.插入圖表分析數(shù)據(jù)。單擊工作表的左上方符號“2”，按住【Ctrl】鍵的同時選中“銷售部門”數(shù)據(jù)和“銷售額（萬元）”的匯總結果，按【Alt】+【；】組合鍵。7.選擇“插入”→“圖表”→“柱形圖”→“簇狀柱形圖”命令，即可生成圖表8.修改圖表標題。單擊圖表標題區(qū)，將“銷售額（萬元）”更改為“各銷售部門銷售額對比”。9.添加數(shù)據(jù)標簽?！皥D表工具/設計”選項卡→“圖表布局”組→點擊“添加圖表元素”→點擊”數(shù)據(jù)標簽”→選擇”數(shù)據(jù)標簽外”，在數(shù)據(jù)點結尾之外添加數(shù)據(jù)標簽。10.添加主要縱坐標標題“單位（萬元）”，【圖表工具/設計】選項卡【圖表布局】組→點擊【添加圖表元素】→點擊“軸標題(A)”→選擇“主要縱坐標(V)”,修改“軸坐標標題”為“單位（萬元）”。11.選中圖表，選擇“圖表工具-設計”→“數(shù)據(jù)”→“選擇數(shù)據(jù)”命令，在彈出的“選擇數(shù)據(jù)源”對話框中單擊“水平（分類）軸標簽”的“編輯”按鈕。在彈出的“軸標簽”對話框的“軸標簽區(qū)域”文本框中輸入“銷售一部,銷售二部,銷售三部”，（注意：分隔符逗號必須是英文半角狀態(tài)）。單擊“確定”按鈕，將水平（分類）軸標簽修改為“銷售一部”“銷售二部”“銷售三部”。12.設置繪圖區(qū)填充效果為“花束”的紋理填充。單擊“圖表工具/格式”選項卡→“當前所選內(nèi)容”組→點擊“圖表元素”下拉按鈕→選擇“繪圖區(qū)”→點擊“設置所選內(nèi)容格式”→填充選項卡選中“圖片或紋理填充”→點擊“紋理”下拉按鈕→選擇“花束”→點擊【關閉】。13.將圖表插入到“分類匯總數(shù)據(jù)”工作表的“B40:F55”單元格區(qū)域內(nèi)。調整圖表大小并移動到B40:F55單元格區(qū)域內(nèi)?！救蝿詹僮鳌扛鶕?jù)教師的操作演示，讓學生按照任務要求進行相應操作，教師根據(jù)學生操作實際進行巡回指導?！菊n堂提問】分類匯總有哪些類型？【教師提示】分類匯總包括以