《信息技術(shù)基礎(chǔ)》課件 《模塊二：wps表格軟件》任務(wù)2

模塊二：WPS表格處理《WPSOffice》《信息技術(shù)基礎(chǔ)》PWSO02公式與函數(shù)的使用知識目標(biāo)1.能夠掌握常用函數(shù)的基本操作。2.掌握公式計(jì)算數(shù)據(jù)的方法。3.掌握數(shù)據(jù)計(jì)算的規(guī)范流程及相關(guān)操作。學(xué)習(xí)目標(biāo)1.能夠?qū)⒂?jì)算出的數(shù)據(jù)總結(jié)歸納，實(shí)現(xiàn)舉一反三。2.能夠根據(jù)企業(yè)的客觀實(shí)際，正確匹配所需要的函數(shù)，找到合適的公式，操作過程規(guī)范，符合標(biāo)準(zhǔn)。能力目標(biāo)1．具有嚴(yán)謹(jǐn)?shù)挠?jì)算思維；2．具有良好的信息素養(yǎng)；3．提高信息倫理意識；4．具有職業(yè)行為自律和自我管理能力；5．提升分析問題和解決問題的能力。素質(zhì)目標(biāo)1任務(wù)描述2任務(wù)解析3知識鏈接4任務(wù)實(shí)施任務(wù)2：公式與函數(shù)的使用5能力拓展為“員工工資表”工作表增加新列，新列的標(biāo)題行包括“姓名”“工齡”“月工資/元”“獎金/元”“實(shí)發(fā)工資/元”“實(shí)發(fā)工資排名”。在下方增加新行，包括“所有員工實(shí)發(fā)工資總和/元”“所有員工實(shí)發(fā)工資平均值/元”“實(shí)發(fā)工資最高的員工工資/元”“實(shí)發(fā)工資最低的員工工資/元”“員工人數(shù)/人”“研發(fā)部員工人數(shù)/人”“銷售部員工實(shí)發(fā)工資的總和/元”“人事部員工實(shí)發(fā)工資的平均值/元”。使用公式和函數(shù)完成所有數(shù)據(jù)的計(jì)算。WPS表格強(qiáng)大的計(jì)算功能是該軟件最重要的功能之一，用戶可以使用各種計(jì)算公式或提供的函數(shù)在工作表中進(jìn)行復(fù)雜的數(shù)據(jù)計(jì)算。本任務(wù)通過使用公式或函數(shù)，完成“員工工資表”工作表中數(shù)據(jù)的計(jì)算。單元格引用使用公式運(yùn)算使用函數(shù)運(yùn)算公式與函數(shù)的使用單元格引用（一）單元格引用為使單元格的數(shù)值參與運(yùn)算，在公式或函數(shù)中使用單元格的地址作為參數(shù)，稱為單元格的引用。例如：計(jì)算公式“=65+78+85”，數(shù)據(jù)65在C3單元格，數(shù)據(jù)78在D3單元格，數(shù)據(jù)85在E3單元格。通過單元格地址的引用，該計(jì)算公式可以輸入為“=C3+D3+E3”，兩種計(jì)算的結(jié)果是相同的。單元格引用（二）單元格引用分類1.相對引用相對引用是指直接引用單元格的地址。2.絕對引用絕對引用使用“$”符號來引用單元格的地址。3.混合引用混合引用是指既包含相對引用又包含絕對引用的單元格引用。4.名稱引用對已命名的單元格或單元格區(qū)域，在引用其中的數(shù)據(jù)時(shí)也可以采用名稱引用。名稱引用是絕對引用。使用公式進(jìn)行運(yùn)算（一）公式中的運(yùn)算符和語法1.公式中的運(yùn)算符公式中的運(yùn)算符包括：算術(shù)運(yùn)算符、比較運(yùn)算符、文本連接運(yùn)算符和引用運(yùn)算符。使用公式運(yùn)算（一）公式中的運(yùn)算符和語法2.公式中的語法WPS表格中的公式是按照特定的順序進(jìn)行運(yùn)算的，這一特定順序即語法。該語法內(nèi)容為：輸入公式時(shí)以“=”作為開始，然后才是表達(dá)式；一個(gè)公式中，可以包含各種運(yùn)算符、常量、變量、函數(shù)、單元格地址等，如果公式中有多個(gè)運(yùn)算符，則需要按照運(yùn)算符的優(yōu)先級依次進(jìn)行運(yùn)算。使用公式運(yùn)算（二）運(yùn)算符的優(yōu)先級公式中擁有眾多運(yùn)算符，而它們的運(yùn)算優(yōu)先順序也各不相同，正是因?yàn)檫@樣它們才能默契合作實(shí)現(xiàn)各類復(fù)雜的運(yùn)算。使用公式運(yùn)算（三）使用公式進(jìn)行運(yùn)算的步驟使用公式計(jì)算每名學(xué)生的總成績。1.用鼠標(biāo)單擊F3單元格，選定此單元格。2.在編輯欄內(nèi)輸入“=C3+D3+E3”，“總成績”計(jì)算如圖所示，按【Enter】鍵或單擊編輯欄中的“√”完成輸入?；蛴檬髽?biāo)單擊需要計(jì)算的數(shù)值所在的單元格和輸入運(yùn)算符號，再按【Enter】鍵完成計(jì)算。使用函數(shù)運(yùn)算（一）函數(shù)的語法格式：函數(shù)名（參數(shù)1,參數(shù)2,……)功能：函數(shù)名指明要執(zhí)行的運(yùn)算，參數(shù)為函數(shù)運(yùn)算中需要接收的數(shù)據(jù)，運(yùn)算結(jié)果為返回的函數(shù)值。說明：函數(shù)是一種特殊的公式，所有的函數(shù)都要以“=”開始。函數(shù)區(qū)域的表示：1.不連續(xù)單元格：各單元格用“,”（英文輸入法的逗號）隔開;2.連續(xù)單元格區(qū)域：用“:”（英文輸入法的冒號）隔開;3.混合區(qū)域：如“=SUM(A2:D4,C5)”。使用函數(shù)運(yùn)算（二）常用的函數(shù)1.求和函數(shù)SUM函數(shù)格式：SUM(number1,number2,……)。功能：計(jì)算所有參數(shù)數(shù)值的和。2.求平均值函數(shù)AVERAGE函數(shù)格式：AVERAGE(number1,number2,……)。功能：計(jì)算所有參數(shù)的算術(shù)平均值。使用函數(shù)運(yùn)算（二）常用的函數(shù)3.求最大值函數(shù)MAX函數(shù)格式：MAX(number1,number2,……)。功能：返回一組數(shù)值中的最大值。4.求最小值函數(shù)MIN函數(shù)格式：MIN(number1,number2,……)。功能：返回一組數(shù)值中的最小值。使用函數(shù)運(yùn)算（二）常用的函數(shù)5.統(tǒng)計(jì)函數(shù)COUNT函數(shù)格式：COUNT(value1,value2,……)。功能：求各參數(shù)中數(shù)值參數(shù)和包含數(shù)值的單元格個(gè)數(shù)。6.條件判斷函數(shù)IF函數(shù)格式：IF(Logical_test,value_if_true,value_if_false)。功能：判斷一個(gè)條件是否滿足，如果滿足返回一個(gè)值，即value_if_true；如果不滿足則返回另一個(gè)值，即value_if_false。使用函數(shù)運(yùn)算（二）常用的函數(shù)7.求排位函數(shù)RANK函數(shù)格式：RANK(number,ref,order)功能：返回?cái)?shù)字number在區(qū)域ref中相對其它數(shù)值的大小排名。8.查找函數(shù)VLOOKUP函數(shù)格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。功能：在數(shù)據(jù)表的首列查找指定的數(shù)值，并由此返回?cái)?shù)據(jù)表當(dāng)前行中指定列處的數(shù)值。使用函數(shù)運(yùn)算（二）常用的函數(shù)9.取整函數(shù)INT函數(shù)格式：INT(number)。功能：取不大于數(shù)值number的最大整數(shù)。10.絕對值函數(shù)ABS函數(shù)格式：ABS(number)。功能：取number的絕對值。使用函數(shù)運(yùn)算（二）常用的函數(shù)11.四舍五入函數(shù)ROUND函數(shù)格式：ROUND(number,num_digits)。功能：對數(shù)值項(xiàng)number進(jìn)行四舍五入。12.條件統(tǒng)計(jì)函數(shù)COUNTIF函數(shù)格式：COUNTIF(Range,Criteria)功能：統(tǒng)計(jì)某個(gè)單元格區(qū)域中符合指定條件的單元格數(shù)目。使用函數(shù)運(yùn)算（二）常用的函數(shù)13.條件求和函數(shù)SUMIF函數(shù)格式：SUMIF(Range,Criteria,Sum_Range)功能：計(jì)算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值的和。14.條件求平均值函數(shù)AVERAGEIF函數(shù)格式：AVERAGEIF(Range,Criteria,Average_Range)功能：計(jì)算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值的平均值。使用函數(shù)運(yùn)算（三）使用函數(shù)運(yùn)算使用函數(shù)計(jì)算每名學(xué)生三門課程成績的平均分。1.用鼠標(biāo)單擊單元格F3，選定單元格。并在單元格中輸入等號“=”。2.接著輸入平均值函數(shù)AVERAGE，在函數(shù)列表中雙擊該函數(shù)，選擇AVERAGE函數(shù)如圖所示。使用函數(shù)運(yùn)算（三）使用函數(shù)運(yùn)算3.單擊編輯欄左側(cè)“FX”按鈕，打開“函數(shù)參數(shù)”對話框。4.在“數(shù)值1”輸入欄中輸入求平均值的范圍C3:E3，輸入計(jì)算參數(shù)如圖所示。5.單擊“確定”按鈕，拖動填充柄復(fù)制公式到其它的單元格。一、修改工作表

打開“模塊二/任務(wù)二/職工工資素材.xlsx”中的“員工工資表”工作表。1.增加新“列”在原B列前方插入新列，在B2單元格中輸入“姓名”。使用相同的操作，在原F列前方插入新的F、G、H列，并在新的F、G、H列的第2行單元格中分別輸入“工齡”“月工資/元”“獎金/元”，J列、K列分別為“實(shí)發(fā)工資/元”列、“實(shí)發(fā)工資排名”列。一、修改工作表2.增加新“行”在原單元格區(qū)域下方新增行，選中相應(yīng)的單元格區(qū)域進(jìn)行單元格合并。在A53至A60單元格中分別輸入“所有員工實(shí)發(fā)工資總和/元”“所有員工實(shí)發(fā)工資平均值/元”“實(shí)發(fā)工資最高的員工工資/元”“實(shí)發(fā)工資最低的員工工資/元”“員工人數(shù)/人”“研發(fā)部員工人數(shù)/人”“銷售部員工實(shí)發(fā)工資的總和/元”“人事部員工實(shí)發(fā)工資的平均值/元”。一、修改工作表3.格式化工作表“員工工資表”合并“A1:H1”區(qū)域，并設(shè)置標(biāo)題為“宋體、16號、加粗、居中”，其他數(shù)據(jù)為“宋體、12號、居中”。為“A2:K60”區(qū)域添加簡單的黑色邊框線?！皢T工工資表”補(bǔ)充完成后，“員工工資表”新增行列后部分效果圖如圖所示。二、填充數(shù)據(jù)1.VLOOKUP函數(shù)使用VLOOKUP函數(shù)，按照員工的“工號”將“職工月工資統(tǒng)計(jì)表”中的“工號”所對應(yīng)的員工“姓名”和“月工資”填充到“員工工資表”中。（1）在“員工工資表”中選中“姓名”列B3單元格，使用“VLOOKUP”函數(shù)進(jìn)行姓名信息的填充，接著使用填充柄進(jìn)行其他員工相對應(yīng)數(shù)據(jù)的填充。二、填充數(shù)據(jù)1.VLOOKUP函數(shù)（2）選中“月工資/元”列選中G3單元格，輸入“=VLOOKUP”，單擊“編輯欄”中的“fx”按鈕，打開“函數(shù)參數(shù)”對話框，輸入?yún)?shù)進(jìn)行計(jì)算。二、填充數(shù)據(jù)2.INT函數(shù)使用INT函數(shù)，計(jì)算每位員工在當(dāng)前系統(tǒng)時(shí)間情況下的“工齡”。選中F3單元格，“編輯欄”中輸入“=INT((TODAY()-E3)/365)”，單擊“輸入”按鈕（編輯欄左側(cè)“√”），接著使用填充柄進(jìn)行其他員工相對應(yīng)數(shù)據(jù)的填充。二、填充數(shù)據(jù)3.IF函數(shù)使用IF函數(shù)，使“獎金”列對應(yīng)“工齡”大于或等于20年的員工獎金為500元，小于20年“工齡”則為200元。選中H3單元格，單擊“編輯欄”中的“fx”按鈕，打開“插入函數(shù)”對話框，輸入?yún)?shù)進(jìn)行計(jì)算。接著使用填充柄進(jìn)行其他員工相對應(yīng)數(shù)據(jù)的填充。二、填充數(shù)據(jù)4.公式計(jì)算使用公式計(jì)算所有員工的“實(shí)發(fā)工資/元”。選中J3單元格，在“編輯欄”中輸入“=G3+H3-I3”，單擊“輸入”按鈕（編輯欄左側(cè)“√”），接著使用填充柄進(jìn)行其他員工相對應(yīng)數(shù)據(jù)的填充，“編輯欄”輸入公式如圖2-66所示。二、填充數(shù)據(jù)5.RANK函數(shù)使用RANK函數(shù)計(jì)算所有員工的“實(shí)發(fā)工資/元”排名，排序方式“降序”。選中K3單元格，選擇“RANK”函數(shù)，單擊“確定”按鈕。在“數(shù)值”文本框中輸入“J3”，“引用”文本框中輸入“$J$3:$J$52”（絕對地址引用），單擊“確定”按鈕，接著使用填充柄進(jìn)行其他員工相對應(yīng)數(shù)據(jù)的填充。二、填充數(shù)據(jù)6.SUM函數(shù)使用SUM函數(shù)計(jì)算所有員工實(shí)發(fā)工資的總和。選中D53單元格，單擊“編輯欄”中的“fx”按鈕，選擇“SUM”函數(shù)，單擊“確定”按鈕。在“數(shù)值1”文本框中輸入“J3:J52”，單擊“確定”按鈕。二、填充數(shù)據(jù)7.AVERAGE函數(shù)使用AVERAGE函數(shù)計(jì)算所有員工實(shí)發(fā)工資的平均值。選中D54單元格，單擊“編輯欄”中的“fx”按鈕，“AVERAGE”函數(shù)，單擊“確定”按鈕。在“數(shù)值1”文本框中輸入“J3:J52”，單擊“確定”按鈕。二、填充數(shù)據(jù)8.MAX函數(shù)使用MAX函數(shù)計(jì)算所有員工實(shí)發(fā)工資最高的員工金額數(shù)。選中D55單元格，單擊“編輯欄”中的“fx”按鈕，打開“插入函數(shù)”對話框?！盎蜻x擇全部”類別里選擇“MAX”函數(shù)，單擊“確定”按鈕。在“數(shù)值1”文本框中輸入“J3:J52”，單擊“確定”按鈕，“MAX”函數(shù)參數(shù)對話框如圖所示。二、填充數(shù)據(jù)9.MIN函數(shù)使用MIN函數(shù)計(jì)算所有員工實(shí)發(fā)工資最低的員工金額數(shù)。選中D56單元格，選擇“MIN”函數(shù)，單擊“確定”按鈕。在“數(shù)值1”文本框中輸入“J3:J52”，單擊“確定”按鈕。二、填充數(shù)據(jù)10.COUNT函數(shù)使用COUNT函數(shù)計(jì)算“員工工資表”中的員工人數(shù)。選中D57單元格，選擇“COUNT”函數(shù)，單擊“確定”按鈕。在“數(shù)值1”文本框中輸入“J3:J52”，單擊“確定”按鈕。二、填充數(shù)據(jù)11.COUNTIF函數(shù)使用COUNTIF函數(shù)計(jì)算“員工工資表”中“研發(fā)部”的員工人數(shù)。選中D58單元格，選擇“COUNTIF”函數(shù)，在“區(qū)域”文本框中輸入“D3:D52”，在“條件”文本框中輸入“”研發(fā)部””，單擊“確定”按鈕。二、填充數(shù)據(jù)12.SUMIF函數(shù)使用SUMIF函數(shù)計(jì)算“員工工資表”中“銷售部”員工的實(shí)發(fā)工資總和。選中D59單元格，選擇“SUMIF”函數(shù)，單擊“確定”按鈕。在“區(qū)域”文本框中輸入“D3:D52”，在“條件”文本框中輸入“”銷售部””，在“求和區(qū)域”文本框中輸入“J3:J52”，單擊“確定”按鈕。二、填充數(shù)據(jù)13.AVERAGEIF函數(shù)使用AVERAGEIF函數(shù)計(jì)算“員工工資表”中“人事部”員工的實(shí)發(fā)工資平均值。選中D60單元格，選擇“AVERAGEIF”函數(shù)，在“區(qū)域”文本框中輸入“D3:D52”，在“條件”文本框中輸入“”人事部””，在“求平均值區(qū)域”文本框中輸入“J3:J52”，單擊“確定”按鈕。二、填充數(shù)據(jù)14.保存工作簿將“員工工資表”中除標(biāo)題外的其他數(shù)據(jù)格式設(shè)置為“宋體、12號、居中”，將D53:D60區(qū)域的數(shù)據(jù)保留為整數(shù)，并重新添加邊框線。將該工作簿另存到公司“職工檔案”文件夾內(nèi)，并命名為“員工工資統(tǒng)計(jì)”工作簿。TEXT+MID函數(shù)打開“員工工資表”，在C列（身份證列）后插入新的三列，分別為“出生日期”、“年齡”、“退休日期”列。完成后的嵌套函數(shù)計(jì)算部分效果圖圖如圖所示。使用TEXT和MID函數(shù)計(jì)算“員工工資表”中員工的出生日期。使用公式為“=TEXT(MID(C3,7,8),”0000年00月00日”)”，其中MID函數(shù)是從C3單元格的第7位開始（C3是第一名員工的身份證號單元格），提取出8個(gè)字符，是身份證號上的出生年月日數(shù)字。再使用TEXT函數(shù)將這個(gè)字符串變成日期類型格式的文本。DATEDIF+TODAY函數(shù)使用DATEDIF和TODAY函數(shù)計(jì)算“員工工資表”中員工的年齡。使用公式為“=DATEDIF(D3,TODAY(),”y”)”，其中DATEDIF函數(shù)用于計(jì)算兩個(gè)日期之