任務4-3：制作人事信息數(shù)據(jù)表人工智能學院授課教師：*****大學信息技術基礎FundamentalsofCollegeInformationTechnology任務目標本次任務學習目標010203掌握Excel2019中公式的使用；掌握Excel2019中常用函數(shù)的使用;掌握Excel2019中單元格的引用操作；大學信息技術基礎FundamentalsofCollegeInformationTechnology任務場景年底，各個部門都在忙著做總結，綜合管理部的小曹負責計算員工薪酬，他知道小傅擅長使用辦公軟件，于是向他請教人事信息數(shù)據(jù)的處理方法。人事信息數(shù)據(jù)是企業(yè)進行人事管理的基礎和依據(jù)，因此，公司要求對當前的員工信息表和工資表進行科學管理，小傅首先幫忙完善了人事數(shù)據(jù)表格的字段信息，然后利用高效的數(shù)學函數(shù)完成了信息查詢和薪資計算。任務準備——使用公式公式

公式是一個以等號開頭的等式，通過對數(shù)據(jù)進行加、減、乘、除等運算，對單元格中的數(shù)據(jù)進行分析。公式可以引用同一工作表中的其他單元格、同一工作簿中不同工作表的單元格，也可以引用其他工作簿中工作表的單元格。公式的運算按照運算符進行，Excel中的運算符有4類：算術運算符、比較運算符、文本運算符和引用運算符。任務準備——使用公式公式1．算術運算符算術運算符包括加（+）、減（-）、乘（*）、除（/）、百分數(shù)（%）和乘方（^），適合各種基本的數(shù)學運算。算術運算符中優(yōu)先級最高的是百分數(shù)（%），然后依次是乘方（^）、乘（*）和除（/）、加（+）和減（-）。2．比較運算符比較運算符包括等于（=）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）和不等于（<>）。比較運算符的作用是將兩個值進行比較，運算結果為一個邏輯值TRUE或FALSE。其中TRUE表示條件成立，F(xiàn)ALSE表示條件不成立。3．文本運算符文本運算符只有一個，即文本連接符（&），它的作用是將一個或多個文本數(shù)據(jù)連接成一個組合文本。例如，在單元格中輸入“="Office"&"2019"”后回車，產(chǎn)生的結果為“Office2019”。4．引用運算符引用運算符的作用是進行引用，使用它可以對單元格去取進行合并計算。引用運算符包括冒號（：）、逗號（,）、空格和感嘆號（！）。任務準備——函數(shù)的使用SUMIF()SUMIF函數(shù)的功能是對范圍中符合指定條件的值求和，語法格式如下：SUMIF(range,criteria,[sum_range])

其中，range表示按照條件求值的單元格區(qū)域；criteria表示條件，其形式可以是數(shù)字、表達式、單元格引用、文本或函數(shù)；sum_range是可選參數(shù)，表示實際求和區(qū)域，如果省略，默認是range指定的區(qū)域。任務準備——函數(shù)的使用COUNTIF()COUNTIF函數(shù)的功能是統(tǒng)計滿足某個條件的單元格的數(shù)量，語法格式如下：COUNTIF(range,criteria)其中，range表示按照條件統(tǒng)計的單元格區(qū)域。criteria表示條件，其形式可以是數(shù)字、表達式、單元格引用、文本或函數(shù)。任務準備——函數(shù)的使用IFS()IFS函數(shù)的功能是檢查是否滿足一個或多個條件，返回符合第一個TRUE條件的值。IFS可以取代多個嵌套IF語句。語法格式如下：IFS(logical_test1,value_if_true1[,logical_test1,value_if_true1][,logical_test1,value_if_true1]……)IFS函數(shù)允許測試最多127個不同的條件。任務準備——函數(shù)的使用IFERROR()IFERROR函數(shù)的功能是捕獲和處理公式中的錯誤IFERROR返回公式計算結果為錯誤時指定的值；否則，它將返回公式的結果。語法格式如下：IFERROR(value,value_if_error)其中，value表示檢查是否存在錯誤的參數(shù)；value_if_error表示公式計算結果為錯誤時要返回的值。任務準備——函數(shù)的使用REPLACE()REPLACE函數(shù)的功能是進行字符替換。語法格式如下：REPLACE(old_text,start_num,num_chars,new_text)其中，old_text表示要進行字符替換的文本；start_numvalue表示要替換為new_text的字符在舊文本中的位置；num_chars表示要從old_text中要替換的字符個數(shù)；new_text表示對old_text中字符進行替換的字符串。任務準備——函數(shù)的使用MID()MID函數(shù)的功能是提取文本字符串中指定位置開始的特定數(shù)目的字符。語法格式如下：MID(text,start_num,num_chars)其中，text表示目標文本字符串；start_num表示準備提取的以一個字符的位置，text中第一個字符位置為1；num_chars表示所要提取的字符串的長度任務準備——TEXT函數(shù)TEXT()TEXT函數(shù)的功能是根據(jù)指定的數(shù)字格式將數(shù)值轉(zhuǎn)化成文本，語法格式如下：TEXT(value,format_text)其中，value表示數(shù)字，能夠求值的數(shù)值公式，或者對數(shù)值單元格的引用；format_text表示文字形式的數(shù)據(jù)格式任務準備——函數(shù)的使用VLOOKUP()VLOOKUP函數(shù)是一個縱向查找函數(shù)，按列查找，最終返回該列所需查詢序列所對應的值。語法格式如下：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)其中，lookup_value表示需要在數(shù)據(jù)表首列進行搜索的值，可以是數(shù)值、引用或字符串；table_array表示要在其中搜索數(shù)字的區(qū)域；col_index_num表示返回匹配值在table_array中的列序號，range_lookup表示匹配方式，精確匹配用FALSE，模糊匹配用TRUE或省略，如圖4.59所示。任務準備——函數(shù)的使用HLOOKUP()HLOOKUP函數(shù)是一個橫向查找函數(shù)，按行查找，最終返回該行所需查詢序列所對應的值。語法格式如下：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)其中，lookup_value表示需要在數(shù)據(jù)表首行進行搜索的值，可以是數(shù)值、引用或字符串；table_array表示要在其中搜索數(shù)字的區(qū)域；row_index_num表示返回匹配值在table_array中的行序號，range_lookup表示匹配方式，精確匹配用FALSE，模糊匹配用TRUE或省略任務準備——單元格的引用單元格引用1．相對引用相對引用是指引用單元格的相對位置，它的引用形式直接用列表行號表示單元格。在列上填充時，列標不變，行號會隨著填充而變化；在行上填充時，行號不變，列表會隨著填充而變化。2．絕對引用絕對引用是指引用單元格的精確地址，與包含公式的單元格位置無關。它的引用形式為在行號或列標前都加一個符號$。$符號的作用是其固定作用，行號和列標都加上符號后再進行填充，引用的區(qū)域都不會發(fā)生變化。3．混合引用引用中既包含絕對引用又包含相對引用的稱為混合引用，它的引用形式為只在行號或列標前加符號$，進行行或列的固定?；旌弦糜袃煞N形式：一種是固定行，一種是固定列。任務演練——管理員工信息表小傅先幫小曹對人事數(shù)據(jù)信息完善了字段信息，但考慮到公司的快速發(fā)展，新晉員工人數(shù)逐年增長，小傅升級了員工編號；然后，根據(jù)已有的身份證信息，利用函數(shù)提煉每個員工的生日、年齡和工齡信息，并且對所有員工的學歷信息做了簡單統(tǒng)計。任務演練——管理員工信息表任務效果員工信息表效果

員工信息表中統(tǒng)計的效果任務演練——管理員工信息表員工編號升級1（1）選中C3單元格，單擊“公式”選項卡→“函數(shù)庫”組→“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”文本框中輸入“REPLACE”，單擊“轉(zhuǎn)到”按鈕，選中REPLACE函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框，在對話框中輸入?yún)?shù)內(nèi)容，如圖所示，單擊“確定”按鈕，得到員工“王新亮”升級后的員工編號（2）將光標指針移至C3單元格右下角，當光標指針變?yōu)閷嵭氖中螘r，雙擊鼠標，進行公式填充，升級其余員工編號。任務演練——管理員工信息表提取員工信息2（1）選中G3單元格，單擊“公式”選項卡→“函數(shù)庫”組→“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”文本框中輸入“MID”，單擊“轉(zhuǎn)到”按鈕，選中MID函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框，在對話框中輸入?yún)?shù)內(nèi)容，如圖所示，按Enter鍵得到員工“王新亮”的出生年月。任務演練——管理員工信息表提取員工信息2（2）選中G3單元格，在編輯欄中編輯TEXT公式“=TEXT(MID(F3,7,6),"0年00月")”，按Enter鍵得到員工“王新亮”指定格式的出生年月，如圖所示。（3）將鼠標指針移至G3單元格右下角，當鼠標指針變?yōu)閷嵭氖中螘r，雙擊鼠標，進行公式填充，提取和轉(zhuǎn)換其他員工的出生日期。任務演練——管理員工信息表計算年齡和工齡3（1）選中H3單元格，在編輯欄中輸入公式“=YEAR(TODAY())-YEAR(G3)”，按Enter鍵得到員工“王新亮”的年齡，并進行公式填充，計算出其他員工的年齡。（2）選中H3單元格，參考上面的操作進行工齡計算，并將計算結果數(shù)據(jù)類型設置成常規(guī)，得到員工的工齡，如圖所示。任務演練——管理員工信息表計算年齡和工齡3（1）選中H3單元格，在編輯欄中輸入公式“=YEAR(TODAY())-YEAR(G3)”，按Enter鍵得到員工“王新亮”的年齡，并進行公式填充，計算出其他員工的年齡。（2）選中H3單元格，參考上面的操作進行工齡計算，并將計算結果數(shù)據(jù)類型設置成常規(guī)，得到員工的工齡，如圖所示。任務演練——管理員工信息表提取性別4選中I3單元格，在編輯欄中輸入公式“=IF(MOD(MID(F3,17,1),2)=1,"男","女")”，按Enter鍵得到員工“王新亮”的性別，如圖所示，并進行公式填充，提取出其他員工的性別。任務演練——管理員工信息表學歷分布5選中T11單元格，單擊“公式”選項卡→“函數(shù)庫”組→“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”文本框中輸入“COUNTIF”，單擊“轉(zhuǎn)到”按鈕，選中COUNTIF函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框，在對話框中輸入?yún)?shù)內(nèi)容，如圖4.71其中，對計算區(qū)域M3:M42使用絕對引用。單擊“確定”按鈕，得到?？茖W歷的人數(shù)。任務演練——管理員工信息表年齡層分布6（1）選中T18單元格，在編輯欄中輸入公式：“=COUNTIF(H3:H42,"<=25")”，按Enter鍵得到“<=25歲”年齡層的人數(shù)。（2）選中T19單元格，在編輯欄中輸入公式：“=COUNTIF(H3:H42,"<35")-COUNTIF(H3:H42,"<=25")，按Enter鍵得到“25歲至35歲”年齡層的人數(shù)。（3）選中T20單元格，在編輯欄輸入公式：“=COUNTIF(H3:H42,”>=35“)”，按Enter鍵得到“>=35歲”年齡層的人數(shù)。任務拓展——管理員工工資表工資薪酬關乎員工基本利益，影響員工工作積極性和工作效率，關系到企業(yè)是否能良性發(fā)展，但是薪酬的計算規(guī)則復雜，項目繁多，工作量較大。小傅利用Excel中的VLOOKUP、HLOOKUP、IFS等函數(shù)，對員工工資表進行制作管理，簡單而且高效。任務拓展——管理員工工資表任務效果任務拓展——管理員工工資表員工崗級1選中E4單元格，單擊“公式”選項卡→“函數(shù)庫”組→“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”文本框中輸入“VLOOKUP”，單擊“轉(zhuǎn)到”按鈕，選中VLOOKUP函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框，在對話框中輸入?yún)?shù)內(nèi)容，按Enter鍵得到員工“王新亮”的崗級。任務拓展——管理員工工資表計算崗位工資2選中G4單元格，在編輯欄中輸入IFS公式“=IFS(E4="9級",1000,E4="8級",1500,E4="7級",2000,E4="6級",2500,E4="5級",3000)”，按Enter鍵得到員工“王新亮”的崗位工資任務拓展——管理員工工資表計算員工補貼3（1）選中H4單元格，單擊“公式”選項卡→“函數(shù)庫”組→“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”文本框中輸入“HLOOKUP”，單擊“轉(zhuǎn)到”按鈕，選中HLOOKUP函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框，在對話框中輸入?yún)?shù)內(nèi)容，如圖4.80所示，按Enter鍵得到員工“王新亮”的補貼金額。任務拓展——管理員工工資表計算員工補貼3（2）選中H4單元格，編輯公式為“=IFERROR(HLOOKUP