Excel在財務(wù)管理中的應(yīng)用課件：薪酬管理

薪酬管理薪酬管理,即企業(yè)管理者對員工報酬的支付標準、發(fā)放水平、要素結(jié)構(gòu)進行確定、分配和調(diào)整的過程。不僅影響勞動者的生活保障、工作狀態(tài),而且影響企業(yè)組織戰(zhàn)略的實現(xiàn)和盈利能力的提升。因此,建立科學(xué)的薪酬管理體系對企業(yè)十分重要。本章的重點包括:(1)構(gòu)建員工基礎(chǔ)信息表;(2)創(chuàng)建工資計算表與考勤統(tǒng)計表;(3)制作薪酬信息查詢框;(4)利用Excel從薪酬相關(guān)數(shù)據(jù)的不同角度進行分析。通過學(xué)習(xí)本章,讀者能夠了解便捷的薪酬管理方法。8.1構(gòu)建員工基礎(chǔ)信息表進行薪酬管理,首先需要獲取基礎(chǔ)數(shù)據(jù)?；A(chǔ)數(shù)據(jù)主要包括當前日期、員工代碼、姓名、身份證號、部門、職務(wù)、性別、年齡、入職時間、工齡、宿舍等級等。打開Excel2019,新建工作表并命名為“員工基礎(chǔ)信息表”。1.利用日期函數(shù)確定當前日期首先,單擊A1單元格,并輸入“當前日期:”。然后在B1單元格內(nèi)利用TODAY函數(shù)返回系統(tǒng)當前日期,即輸入“=TODAY()”,此時,B1單元格將自動更新日期,如圖8-1所示。

圖8-12.利用數(shù)據(jù)驗證規(guī)范基礎(chǔ)信息第一步,在A2至E2單元格內(nèi)分別輸入:員工代碼、姓名、身份證號、部門、職務(wù)、性別、年齡、入職時間、工齡、宿舍等級等,如圖8-2所示。

圖8-2第二步,錄入員工代碼。①在A3單元格中輸入“1”;②利用填充柄向下填充單元格;③選擇“填充序列”,如圖8-3所示;④根據(jù)企業(yè)要求,利用“單元格格式”設(shè)置員工代碼格式,這里假設(shè)員工代碼為三位數(shù),例如1號員工的代碼應(yīng)為“001”,則自定義單元格格式為“00#”,如圖8-4所示。然后錄入員工姓名。

圖8-3

圖8-4第三步,設(shè)置數(shù)據(jù)驗證,保證錄入的身份證號為18位,并錄入身份證信息。①選中C3單元格。②在“數(shù)據(jù)”選項卡下選擇“數(shù)據(jù)驗證”功能。③在“數(shù)據(jù)驗證”對話框中選擇驗證條件為“自定義”。④在“公式”中輸入“=AND(LEN(C3)=18,COUNTIF(C:C,C3)<=1)=TRUE”,代表C3單元格中字符長度為18,且C列中C3單元格的內(nèi)容只出現(xiàn)一次。如圖8-5所示。⑤使用填充柄向下填充,使數(shù)據(jù)驗證功能覆蓋C列的每一個單元格。⑥錄入員工身份證信息。

圖8-5第四步,設(shè)置數(shù)據(jù)驗證,并錄入部門信息。①選擇D3單元格。②在“數(shù)據(jù)”選項卡下選擇“數(shù)據(jù)驗證”功能。③設(shè)置驗證條件為“允許序列”。④輸入序列來源,即輸入公司內(nèi)的部門名稱,本例輸入“行政,研發(fā),銷售,財務(wù),人力資源”,如圖8-6所示。⑤利用填充柄向下填充,使數(shù)據(jù)驗證功能覆蓋D列的每一個單元格。⑥錄入部門信息。注意:在輸入序列來源時,每個名稱應(yīng)以英文狀態(tài)下的逗號隔開。

圖8-6在輸入部門信息時,單元格右側(cè)將出現(xiàn)下拉箭頭,單擊下拉箭頭可以看到輸入的內(nèi)容僅限于“來源”中的設(shè)定,如圖8-7所示。第五步,設(shè)置數(shù)據(jù)驗證,并錄入職務(wù)信息。設(shè)置數(shù)據(jù)驗證方法與第四步相同,在“來源”中輸入本公司的相關(guān)職務(wù),這里以“總監(jiān),經(jīng)理,職員,見習(xí)”為例,如圖8-8所示。

圖8-7

圖8-8此時,選中E3單元格,單元格右側(cè)出現(xiàn)下拉箭頭,單擊下拉箭頭可以看到,錄入數(shù)據(jù)僅限于總監(jiān)、經(jīng)理、職員、見習(xí)這四個職務(wù)。這樣可以保證數(shù)據(jù)的有效性和規(guī)范性。第六步,設(shè)置數(shù)據(jù)驗證,并錄入宿舍等級。設(shè)置數(shù)據(jù)驗證方法與第四步相同,在“來源”中輸入本公司提供的宿舍等級。這里設(shè)置的宿舍等級包括無宿舍、1級和2級,因此,在“來源”中輸入“無,1,2”。利用填充柄向下填充J列,結(jié)果如圖8-9所示。

圖8-9身份證號第17位為奇數(shù)代表性別為男,偶數(shù)代表性別為女,因此,如果身份證號的第17位除以2的余數(shù)為1,則代表該員工為男,否則為女。利用IF函數(shù)可以實現(xiàn):輸入“=IF(MOD(MID(C3,17,1),2)=1,"男","女")”,即如果C3單元格中從第17個字符開始數(shù)的第一個字符除以2的余數(shù)為1,則返回“男”,否則返回“女”。結(jié)果如圖8-10所示。

圖8-103.利用函數(shù)完善基礎(chǔ)信息表第一步,利用IF、MOD和MID嵌套函數(shù)根據(jù)身份證號自動生成性別。①選中F3單元格。②輸入“=IF(MOD(MID(C3,17,1),2)=1,"男","女")”。③利用填充柄向下填充F列。MID函數(shù),代表返回文本字符串中從指定位置開始的特定數(shù)目的字符,該數(shù)目由用戶指定。其語法為:MID(text,start_num,num_chars),其中,text代表要提取字符的文本字符串;start_num代表文本中要提取的第一個字符的位置;num_chars代表希望MID從文本中返回字符的個數(shù)。本例中,“MID(C3,17,1)”代表從第17個字符開始提取C3單元格中的1位字符,此時,MID的返回結(jié)果為0。MOD函數(shù),代表返回兩數(shù)相除的余數(shù),結(jié)果的符號與除數(shù)相同。其語法為:MOD(number,divisor),其中,number代表要計算余數(shù)的被除數(shù);divisor代表除數(shù)。本例中,“MOD(MID(C3,17,1),2)”代表返回“MID(C3,17,1)”除以2的余數(shù),此時,MOD返回的結(jié)果為0.第二步,利用YEAR和MID函數(shù)計算員工年齡。①選中G3單元格。②輸入“=YEAR(B1)-MID(C3,7,4)”。③利用填充柄進行填充時,B1單元格不應(yīng)隨單元格的移動而變化,因此,將光標放在“B1”后并按“F4”鍵,將B1單元格設(shè)置為絕對引用,此時,編輯欄中的公式應(yīng)為“=YEAR($B$1)-MID(C3,7,4)”。④利用填充柄向下填充G列。結(jié)果如圖8-11所示。函數(shù)中,“YEAR($B$1)”代表返回B1單元格中的年份,即返回系統(tǒng)日期的年份;“MID(C3,7,4)”代表返回C3單元格中從第7個字符開始的4位數(shù),本例中返回的是“1973”四個代表員工出生年份的數(shù)字;“=YEAR($B$1)-MID(C3,7,4)”代表返回當前系統(tǒng)日期與員工出生年份的差,即年齡。

圖8-11第三步,手動錄入員工入職時間。第四步,利用ROUND函數(shù)計算員工工齡。①選中I3單元格。②輸入“=ROUND(($B$1-H3)/365,0)”。③利用填充柄向下填充I列。結(jié)果如圖8-12所示。通過“($B$1-H3)/365”計算得出當前日期與入職時間相差的年份,“=ROUND(($B$1-H3)/365,0)”代表對上式計算得出的年份四舍五入至整數(shù)位。注意:本例中,員工工齡為入職時間與當前日期的時間間隔,工作滿半年的按一年計算。在計算員工工齡時可參考企業(yè)要求。

圖8-128.2工資計算表與考勤統(tǒng)計表8.2.1建立薪酬管理規(guī)則薪酬管理規(guī)則由公司章程制定,財務(wù)人員按照公司制定的薪酬管理規(guī)則進行薪酬核算。為方便財務(wù)人員查閱相關(guān)規(guī)則,可以將規(guī)則的重點內(nèi)容錄入Excel當中。下面以某公司薪酬管理制度為例進行講解,使用者可根據(jù)實際工作要求自行制定薪酬管理規(guī)則。在Excel2019中,新建工作表并命名為“規(guī)則”?；诠疽?guī)定重點記錄薪酬管理制度的要求。假定規(guī)則如下:公司規(guī)定員工可選擇住宿或不住宿,住宿分為1、2個等級,其中,等級1的住宿費為400元/月;等級2的住宿費為800元/月。信息錄入在規(guī)則工作表中A3至B6單元格區(qū)域內(nèi)。公司給予員工工齡工資,其中,工齡小于1年的,工齡工資為0;工齡為1~2年的,工齡工資為100元/月;工齡為3~6年的,工齡工資為200元/月;工齡為7~10年的,工齡工資為300元/月;工齡大于10年的,工齡工資為500元/月。信息錄入在規(guī)則工作表中A10至D15單元格區(qū)域內(nèi)。公司各部門工作人員按工作崗位劃分工資水平,如圖8-13所示,信息錄入在規(guī)則工作表中A18至E23單元格區(qū)域內(nèi)。出勤的考核獎懲辦法如下:病假扣除日工資的1/2,事假扣除日工資,遲到早退,第一次扣20元,超過一次,每次扣100元,整月全勤獎勵200元。8.2.1建立薪酬管理規(guī)則三險一金及個人所得稅計算方法如圖8-13所示。2018年《中華人民共和國個人所得稅法》(第七次修正)第十一條規(guī)定:“居民個人取得綜合所得,按年計算個人所得稅;有扣繳義務(wù)人的,由扣繳義務(wù)人按月或者按次預(yù)扣預(yù)繳稅款;需要辦理匯算清繳的,應(yīng)當在取得所得的次年三月一日至六月三十日內(nèi)辦理匯算清繳?！币虼?企業(yè)作為扣減義務(wù)人應(yīng)按月或按次預(yù)扣預(yù)繳稅款,月度個人所得稅稅率表(綜合所得適用)如圖8-13所示。

圖8-138.2.1建立薪酬管理規(guī)則完成薪酬管理工作簿的制作后,若不需要顯示規(guī)則工作表,可以將其隱藏。操作方法是:選中規(guī)則工作表,單擊鼠標右鍵,選擇“隱藏”功能,如圖8-14所示。

圖8-148.2.2構(gòu)建工資計算表和考勤統(tǒng)計表工資計算表中的內(nèi)容主要包括員工基礎(chǔ)信息、崗位工資、工齡工資、績效工資、全勤獎、工資總額、住宿費、缺勤扣款、養(yǎng)老保險、醫(yī)療保險、失業(yè)保險、住房公積金、應(yīng)發(fā)工資、個人所得稅、實發(fā)合計。為了使制作的表格簡潔明了,下面單獨制作考勤統(tǒng)計表,再將其與工資計算表結(jié)合使用?？记诮y(tǒng)計表中的內(nèi)容主要包括員工基礎(chǔ)信息、事假、病假、遲到早退、全勤獎、工資總額和缺勤扣款。新建兩個工作表,分別命名為“工資計算表”和“考勤統(tǒng)計表”。然后分別在工資計算表和考勤統(tǒng)計表中錄入各項標題,如圖8-15和圖8-16所示。

圖8-15

圖8-168.2.2構(gòu)建工資計算表和考勤統(tǒng)計表第一步,選擇A2單元格,并輸入“=員工基礎(chǔ)信息表!A3”。第二步,使用填充柄功能向下填充A列。此時,可以將員工基礎(chǔ)信息表中的員工代碼信息導(dǎo)入考勤統(tǒng)計表,如圖8-17所示。

圖8-178.2.2構(gòu)建工資計算表和考勤統(tǒng)計表第三步,利用VLOOKUP和MATCH嵌套函數(shù)查找員工基礎(chǔ)信息表匯總的員工基礎(chǔ)信息并返回至考勤統(tǒng)計表。①選擇B2單元格,并輸入“=VLOOKUP($A2,員工基礎(chǔ)信息表!$1:$1048576,MATCH(B$1,員工基礎(chǔ)信息表!$2:$2,0),FALSE)”。②將鼠標放至B2單元格右下角,當光標變?yōu)槭中螘r,利用填充柄功能向下填充B列,得到員工姓名。③選中B2至B19單元格,即全部員工姓名單元格;將鼠標放至B19單元格右下角,當光標變?yōu)槭中螘r,利用填充柄功能向右填充至E19單元格,得到員工身份證號、所在部門和職務(wù)。如圖8-18所示。

圖8-188.2.2構(gòu)建工資計算表和考勤統(tǒng)計表【函數(shù)講解】MATCH函數(shù)的功能是,在范圍單元格中搜索特定的項,然后返回該項在此區(qū)域中的相對位置。其語法是:MATCH(lookup_value,lookup_array,[match_type])。參數(shù)lookup_value代表要在lookup_array中匹配的值;lookup_array代表要搜索的單元格區(qū)域;match_type用來指定Excel如何將lookup_value與lookup_array中的值匹配。該參數(shù)可選填,默認值為1,此時lookup_array參數(shù)中的值必須以升序排列;該值為0時,lookup_array參數(shù)中的值可按任何順序排列;該值為-1時,lookup_array參數(shù)中的值必須按降序排列。MATCH函數(shù)可以為VLOOKUP函數(shù)提供col_index_num參數(shù),即匹配值的列序號。在本例中,MATCH(B$1,員工基礎(chǔ)信息表!$2:$2,0),代表在員工基礎(chǔ)信息表中的第二行查找B1單元格(即查找“姓名”),并返回“姓名”在員工基礎(chǔ)信息表第二行的位置,也就是“2”,這也代表了“姓名”在員工基礎(chǔ)信息表中的列序號——第二列。將MATCH函數(shù)與VLOOKUP函數(shù)嵌套,“=VLOOKUP($A2,員工基礎(chǔ)信息表!$1:$1048576,MATCH(B$1,員工基礎(chǔ)信息表!$2:$2,0),FALSE)”代表在員工基礎(chǔ)信息表中精確查找A2單元格,并返回員工基礎(chǔ)信息表的第二列。注意:為了保證向下和向右填充公式時的準確性,應(yīng)將相關(guān)單元格的行或列鎖定。8.2.2構(gòu)建工資計算表和考勤統(tǒng)計表2.制作考勤統(tǒng)計表——補充考勤信息第一步,將每一位員工請事假、病假及遲到早退的次數(shù)錄入考勤統(tǒng)計表,并利用SUM函數(shù)計算天數(shù)合計,如圖8-19所示。

圖8-198.2.2構(gòu)建工資計算表和考勤統(tǒng)計表第二步,自動確認獲得全勤獎的員工。①在I2單元格中輸入“=IF(SUM(F2:H2)=0,200,0)”。②將鼠標放在I2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充I列。③利用SUM函數(shù)計算合計數(shù),如圖8-20所示?！竞瘮?shù)講解】“SUM(F2:H2)”代表將F2至H2單元格區(qū)域內(nèi)數(shù)字相加求和;“=IF(SUM(F2:H2)=0,200,0)”代表如果F2至H2單元格區(qū)域內(nèi)數(shù)字相加為0,即該員工沒有請事假、病假或遲到早退的情況,則返回“200”,也就是得到全勤獎200元,否則就是沒有得到全勤獎。根據(jù)薪酬管理規(guī)則,“缺勤扣款”應(yīng)基于單日工資計算,因此,需要先計算工資總額,再計算缺勤扣款。

圖8-208.2.2構(gòu)建工資計算表和考勤統(tǒng)計表3.制作工資計算表——計算工資總額第一步,導(dǎo)入員工基礎(chǔ)信息,包括員工代碼、姓名、身份證號、部門、職務(wù)和工齡,同上述考勤統(tǒng)計表的操作方式。第二步,計算崗位工資。①利用VLOOKUP和MATCH嵌套函數(shù)自動填列崗位工資:選中“崗位工資”標題下G2單元格,并輸入“=VLOOKUP(D2,規(guī)則!$A$18:$E$23,MATCH(E2,規(guī)則!$A$18:$E$18,0),FALSE)”。②將鼠標放在G2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充G列。③利用SUM函數(shù)計算崗位工資合計數(shù),如圖8-21所示?！竞瘮?shù)講解】崗位工資的相關(guān)規(guī)定在規(guī)則工作表中A18至E23單元格區(qū)域當中列明。本例中,通過VLOOKUP函數(shù)和MATCH函數(shù)相結(jié)合,完成對崗位工資的匹配查找和返回。

圖8-218.2.2構(gòu)建工資計算表和考勤統(tǒng)計表第三步,利用VLOOKUP函數(shù)填列工齡工資。①選中“工齡工資”標題下H2單元格,并輸入“=VLOOKUP(F2,規(guī)則!$B$10:$D$15,3,TRUE)”。②將鼠標放在H2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充H列。③利用SUM函數(shù)計算工齡工資合計數(shù),如圖8-22所示?！竞瘮?shù)講解】工齡工資的相關(guān)規(guī)定在規(guī)則工作表中B10至D15單元格區(qū)域當中列明。本例中,通過VLOOKUP函數(shù)完成對工齡工資的匹配查找和返回。

圖8-228.2.2構(gòu)建工資計算表和考勤統(tǒng)計表第四步,錄入每個員工的績效工資,并利用SUM函數(shù)計算績效工資合計數(shù)。第五步,利用VLOOKUP和MATCH嵌套函數(shù)填列全勤獎金額。①選中“全勤獎”標題下J2單元格,并輸入“=VLOOKUP($A2,考勤統(tǒng)計表!$A:$K,MATCH(J$1,考勤統(tǒng)計表!$1:$1,0),FALSE)”。②將鼠標放在J2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充J列。③利用SUM函數(shù)計算全勤獎合計數(shù),如圖8-23所示?！竞瘮?shù)講解】全勤獎的金額已在考勤統(tǒng)計表中計算過。本例中,通過VLOOKUP函數(shù)和MATCH函數(shù)相結(jié)合,完成對全勤獎的匹配查找和返回。

圖8-238.2.2構(gòu)建工資計算表和考勤統(tǒng)計表第六步,利用SUM函數(shù)計算工資總額。①單擊“工資總額”標題下K2單元格,并輸入“=SUM(G2:J2)”。②將鼠標放在K2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充K列。③利用SUM函數(shù)計算工資總額合計數(shù),如圖8-24所示。

圖8-248.2.2構(gòu)建工資計算表和考勤統(tǒng)計表4.制作考勤統(tǒng)計表——計算缺勤扣款在工資計算表中已計算出工資總額,在工資總額的基礎(chǔ)上可以在考勤統(tǒng)計表中計算缺勤扣款。第一步,利用VLOOKUP和MATCH函數(shù)填列工資總額。①選擇考勤統(tǒng)計表工作表,單擊“工資總額”標題下J2單元格,并輸入“=VLOOKUP($A2,工資計算表!$A:$T,MATCH(J$1,工資計算表!$1:$1,0),FALSE)”。②將鼠標放在J2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充J列。③利用SUM函數(shù)計算工資總額合計數(shù),如圖8-25所示。

圖8-258.2.2構(gòu)建工資計算表和考勤統(tǒng)計表第二步,利用IF函數(shù)計算缺勤扣款。①選擇“缺勤扣款”標題下K2單元格,并輸入“=J2/22*F2+J2/22/2*G2+IF(H2=1,20,IF(H2>1,H2*100,0))”。②將鼠標放在K2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充K列。③利用SUM函數(shù)計算缺勤扣款合計數(shù),如圖8-26所示。【函數(shù)講解】本例以一月份薪酬管理工作表為例進行講解,假設(shè)一月份工作日為22天。缺勤扣款的規(guī)則是:病假扣除一半的日工資;事假扣除日工資;遲到早退,一次扣20元,超過一次,每次扣100元。每個員工日工資=工資總額÷22?！癐F(H2=1,20,IF(H2>1,H2*100,0))”代表:如果遲到早退次數(shù)等于1,則返回20,如果遲到早退次數(shù)大于1,則返回100乘以遲到早退次數(shù),否則返回0?！?J2/22*F2+J2/22/2*G2+IF(H2=1,20,IF(H2>1,H2*100,0))”代表:請事假次數(shù)×日工資+請病假次數(shù)×一半日工資+遲到早退扣款。

圖8-268.2.2構(gòu)建工資計算表和考勤統(tǒng)計表5.制作工資計算表——計算應(yīng)發(fā)工資和實發(fā)合計第一步,利用IF與VLOOKUP嵌套函數(shù)填列住宿費。①單擊“住宿費”標題下L2單元格,并輸入“=IF(VLOOKUP($A2,員工基礎(chǔ)信息表!$A:$J,10,FALSE)="無",0,IF(VLOOKUP($A2,員工基礎(chǔ)信息表!$A:$J,10,FALSE)=1,400,800))”。②將鼠標放在L2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充L列。③利用SUM函數(shù)計算住宿費合計數(shù),如圖8-27所示。為便于數(shù)據(jù)展示,先將工資計算表中第C至J列隱藏?！竞瘮?shù)講解】基于員工基礎(chǔ)信息表中“宿舍等級”的信息,通過VLOOKUP函數(shù)查找并返回員工的住宿等級,如果返回的住宿等級是“無”,則住宿費為0;如果返回的住宿等級是“1”,則住宿費為400;否則住宿費為800?；谏鲜鏊悸窐?gòu)建IF與VLOOKUP的嵌套函數(shù),實現(xiàn)數(shù)據(jù)匹配和查找。

圖8-278.2.2構(gòu)建工資計算表和考勤統(tǒng)計表第二步,利用VLOOKUP和MATCH嵌套函數(shù)填列缺勤扣款。①單擊“缺勤扣款”標題下M2單元格,并輸入“=VLOOKUP($A2,考勤統(tǒng)計表!$A:$K,MATCH(M$1,考勤統(tǒng)計表!$1:$1,0),FALSE)”。②將鼠標放在M2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充M列。③利用SUM函數(shù)計算缺勤扣款合計數(shù),如圖8-28所示?！竞瘮?shù)講解】缺勤扣款金額已在考勤統(tǒng)計表當中計算得出,本例中,利用MATCH函數(shù)為VLOOKUP函數(shù)提供列序號,從而得到每個員工相對應(yīng)的缺勤扣款金額。

圖8-288.2.2構(gòu)建工資計算表和考勤統(tǒng)計表第三步,計算三險一金。已知由員工個人負擔的養(yǎng)老保險、醫(yī)療保險、失業(yè)保險和住房公積金分別占其工資總額的8%、2%、1%和8%。因此,在利用Excel計算時,直接用對應(yīng)的工資總額數(shù)乘以給定比例即可。①單擊“養(yǎng)老保險”標題下N2單元格,并輸入“=K2*0.08”。②將鼠標放在N2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充N列。③利用SUM函數(shù)計算養(yǎng)老保險合計數(shù)。④更改占比數(shù),重復(fù)第①至③步,完成醫(yī)療保險、失業(yè)保險和住房公積金的計算,如圖8-29所示。

圖8-298.2.2構(gòu)建工資計算表和考勤統(tǒng)計表第四步,計算應(yīng)發(fā)工資。應(yīng)發(fā)工資=工資總額-住宿費-缺勤扣款-養(yǎng)老保險-醫(yī)療保險-失業(yè)保險-住房公積金。①單擊“應(yīng)發(fā)工資”標題下R2單元格,并輸入“=K2-L2-M2-N2-O2-P2-Q2”。②將鼠標放在R2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充R列。③利用SUM函數(shù)計算應(yīng)發(fā)工資合計數(shù),如圖8-30所示。

圖8-308.2.2構(gòu)建工資計算表和考勤統(tǒng)計表第五步,利用ROUND和MAX嵌套函數(shù)計算個人所得稅。根據(jù)個人所得稅稅率表(綜合所得適用)的稅率及速算扣除數(shù)(如圖8-13所示)計算個人所得稅。①單擊“個人所得稅”標題下S2單元格,并輸入“=ROUND(MAX((R2-5000)*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,210,1410,2660,4410,7160,15160},0),2)”。②將鼠標放在S2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充S列。③利用SUM函數(shù)計算預(yù)扣預(yù)繳的個人所得稅合計數(shù),如圖8-31所示。

圖8-318.2.2構(gòu)建工資計算表和考勤統(tǒng)計表【函數(shù)講解】“=ROUND(MAX((R2-5000)*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,210,1410,2660,4410,7160,15160},0),2)”代表:根據(jù)速算扣除方法計算個人所得稅,在以下幾組數(shù)當中取最大數(shù),保留兩位小數(shù):

0;

(R2-5000)×0.03-0;

(R2-5000)×0.1-210;

(R2-5000)×0.2-1410;

(R2-5000)×0.25-2660;

(R2-5000)×0.3-4410;

(R2-5000)×0.35-7160;

(R2-5000)×0.45-15160。8.2.2構(gòu)建工資計算表和考勤統(tǒng)計表第六步,計算實發(fā)合計數(shù)。①單擊“實發(fā)合計”標題下T2單元格,并輸入“=R2-S2”。②將鼠標放在T2單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向下填充T列。③利用SUM函數(shù)計算實發(fā)工資的合計數(shù),如圖8-32所示。

圖8-328.3制作信息查詢框企業(yè)員工人數(shù)一般較多,為了更快捷地查詢某個員工的薪酬情況,可以制作信息查詢框。信息查詢框可以通過查詢員工代碼獲得員工薪酬信息,也可以通過查詢員工姓名獲得員工薪酬信息。若無法準確查詢員工姓名,可以通過模糊查找的方式查找員工薪酬信息。首先,新建工作表并命名為“查詢框”。然后,將工資計算表中的內(nèi)容復(fù)制到查詢框工作表當中,如圖8-33所示。在查詢框工作表中制作信息查詢框。8.3.1通過員工代碼查找薪酬信息第一步,在適當單元格位置構(gòu)建信息查詢框,如圖8-34所示。通過后續(xù)操作,應(yīng)實現(xiàn)使用者在B22單元格輸入員工代碼,則顯示框中自動顯示員工代碼、姓名、身份證號、部門、工資總額、應(yīng)發(fā)工資、個人所得稅和實發(fā)合計。

圖8-348.3.1通過員工代碼查找薪酬信息第二步,將B22單元格格式設(shè)置為自定義下的“00#”;將E25至H25單元格格式設(shè)置為“會計專用”。第三步,填列員工代碼。選中“員工代碼”標題下A25單元格,并輸入“=B22”。第四步,利用VLOOKUP和MATCH嵌套函數(shù)填列“姓名”“身份證號”“部門”“工資總額”“應(yīng)發(fā)工資”“個人所得稅”“實發(fā)合計”。①單擊“姓名”標題下B25單元格,并輸入“=VLOOKUP($B$22,$A$1:$T$20,MATCH(B24,$A$1:$T$1,0),FALSE)”。②將鼠標放在B25單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向右填充至H25單元格,如圖8-35所示。由于尚未輸入員工代碼,因此系統(tǒng)自動提示錯誤“#N/A”,但不影響后續(xù)使用。

圖8-358.3.1通過員工代碼查找薪酬信息【函數(shù)講解】查找對象是B22單元格中輸入的員工代碼;查找范圍是A1至T20單元格區(qū)域;利用MATCH函數(shù)為VLOOKUP函數(shù)提供“姓名”的列序號;查找方式為精確查找。為了公式填充后可自動更改返回的序列號,但不改變查找對象、查找范圍和查找方式,本例中單擊F4鍵,將查找對象和查找范圍單元格鎖定。完成表格制作后,在B22單元格輸入員工代碼,例如“2”,查詢框中將自動生成002號員工的員工代碼、姓名、身份證號、部門、工資總額、應(yīng)發(fā)工資、個人所得稅和實發(fā)合計,如圖8-36所示。

圖8-368.3.2通過員工姓名查找薪酬信息利用VLOOKUP和MATCH嵌套函數(shù),通過員工姓名查找薪酬信息的方法與上例類似。本例介紹利用INDEX函數(shù),通過員工姓名查找薪酬信息。第一步,在適當單元格位置構(gòu)建信息查詢框,如圖8-37所示。通過后續(xù)操作,應(yīng)實現(xiàn)使用者在B28單元格輸入員工姓名,則顯示框中自動顯示員工代碼、姓名、身份證號、部門、工資總額、應(yīng)發(fā)工資、個人所得稅和實發(fā)合計。

圖8-378.3.2通過員工姓名查找薪酬信息第二步,將A31單元格格式設(shè)置為自定義下的“00#”;將E31至H31單元格格式設(shè)置為“會計專用”。第三步,利用INDEX和MATCH嵌套函數(shù)填列“員工代碼”“姓名”“身份證號”“部門”“工資總額”“應(yīng)發(fā)工資”“個人所得稅”“實發(fā)合計”。①單擊“員工代碼”標題下A31單元格,并輸入“=INDEX($A$1:$T$20,MATCH($B$28,$B$1:$B$20,0),MATCH(A30,$A$1:$T$1,0))”。②將鼠標放在A31單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向右填充至H31單元格,如圖8-38所示。由于尚未輸入員工姓名,因此系統(tǒng)自動提示錯誤“#N/A”,但不影響后續(xù)使用。

圖8-388.3.2通過員工姓名查找薪酬信息【函數(shù)講解】INDEX函數(shù)代表返回表格或區(qū)域中的值或值的引用。其語法是:INDEX(array,row_num,[column_num])。參數(shù)array代表查找區(qū)域;row_num代表引用行的行數(shù);[column_num]代表引用列的列號。通過行、列的定位查找返回某一值或值的引用。在本例中,利用MATCH函數(shù)返回“姓名”所在行的行數(shù)和“員工代碼”所在列的列號,通過行列的定位可以準確返回B28單元格中輸入的姓名所對應(yīng)的員工代碼。為了公式填充后可自動更改查找范圍等,通過F4鍵對相關(guān)單元格進行鎖定。8.3.2通過員工姓名查找薪酬信息完成表格制作后,在B28單元格輸入員工姓名,例如“蔡淑賢”,查詢框中將自動生成蔡淑賢的員工代碼、姓名、身份證號、部門、工資總額、應(yīng)發(fā)工資、個人所得稅和實發(fā)合計,如圖8-39所示。

圖8-398.3.3通過不確定的員工姓名查找薪酬信息如果不清楚某位員工的全部姓名,但已知其姓名中不與他人姓名重復(fù)的部分名字,可利用HLOOKUP函數(shù)進行查找。第一步,在適當單元格位置構(gòu)建信息查詢框,如圖8-40所示。通過后續(xù)操作,應(yīng)實現(xiàn)使用者在B34單元格輸入員工部分名字,則顯示框中自動顯示員工代碼、姓名、身份證號、部門、工資總額、應(yīng)發(fā)工資、個人所得稅和實發(fā)合計。

圖8-408.3.3通過不確定的員工姓名查找薪酬信息第二步,將A37單元格格式設(shè)置為自定義下的“00#”;將E37至H37單元格格式設(shè)置為“會計專用”。第三步,利用HLOOKUP和MATCH嵌套函數(shù)填列“員工代碼”“姓名”“身份證號”“部門”“工資總額”“應(yīng)發(fā)工資”“個人所得稅”“實發(fā)合計”。①單擊“員工代碼”標題下A37單元格,并輸入“=HLOOKUP(A36,$A$1:$T$20,MATCH($B$34,$B$1:$B$20,0),FALSE)”。②將鼠標放在A37單元格右下角,當光標變?yōu)槭中魏?利用填充柄功能向右填充至H37單元格,如圖8-41所示。由于尚未輸入員工姓名,因此系統(tǒng)自動提示錯誤“#N/A”,但不影響后續(xù)使用。

圖8-418.3.3通過不確定的員工姓名查找薪酬信息【函數(shù)講解】HLOOKUP函數(shù)的功能是在表格的首行或數(shù)值數(shù)組中搜索值,然后返回表格或數(shù)組中指定行的所在列中的值。其語法是:HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]),參數(shù)lookup_value代表要在表格的第一行中查找的值,可以是數(shù)值、引用或文本字符串;參數(shù)table_array代表要在其中查找數(shù)據(jù)的信息表;參數(shù)row_index_num代表將返回匹配值的行號;參數(shù)range_lookup代表一個邏輯值,指定希望HLOOKUP查找精確匹配值還是近似匹配值。如果為TRUE或省略,則返回近似匹配值。換言之,如果找不到精確匹配值,則返回小于lookup_value的最大值。如果為FALSE,則HLOOKUP將查找精確匹配值。如果找不到精確匹配值,則返回錯誤值“#N/A”。注意:①HLOOKUP與VLOOKUP最大的不同在于:當比較值位于數(shù)據(jù)表格的首行時,如果要向下查看指定的行數(shù),則使用HLOOKUP;當比較值位于所需查找數(shù)據(jù)的左邊一列時,則可使用VLOOKUP。②如果range_lookup為TRUE,則table_array的第一行數(shù)值必須按升序排列:…-2、-1、0、1、2、…、A-Z、FALSE、TRUE,否則,HLOOKUP不能給出正確的數(shù)值。如果range_lookup為FALSE,則table_array不必進行排序。③如果range_lookup為FALSE且lookup_value為文本,則可以在lookup_value中使用通配符(問號“?”和星號“*”)。問號匹配任意單個字符;星號匹配任意一串字符。如果要查找實際的問號或星號,請在字符前鍵入波形符“~”?；冖壑兴?可以利用HLOOKUP與MATCH嵌套函數(shù)模糊查找員工薪酬信息。在輸入員工姓名時,可以用“?”或“*”代表不確定字符。由于HLOOKUP用于查找的對象應(yīng)位于首行,因此本例使用HLOOKUP時,查找對象不是B34單元格中輸入的姓名,而是A36單元格中的“員工代碼”;再利用MATCH函數(shù)返回B34單元格中姓名所在行數(shù),來返回員工的薪酬信息。為了公式填充后可自動更改返回的序列號,但不改變查找對象、查找范圍和查找方式,本例中單擊F4鍵,將查找對象和查找范圍單元格鎖定。8.3.3通過不確定的員工姓名查找薪酬信息完成表格制作后,在B34單元格輸入員工姓名,例如“?欣欣”,查詢框中將自動生成名為“欣欣”的員工代碼、姓名、身份證號、部門、工資總額、應(yīng)發(fā)工資、個人所得稅和實發(fā)合計,如圖8-42所示。

圖8-428.4薪酬相關(guān)數(shù)據(jù)分析8.4.1分段統(tǒng)計薪酬情況分段統(tǒng)計薪酬情況可以快速統(tǒng)計某個工資范圍出現(xiàn)的頻率,即人數(shù)。為實現(xiàn)這一功能,要使用頻率函數(shù)——FREQUENCY函數(shù)。FREQUENCY函數(shù)的功能是,計算值在某個范圍內(nèi)出現(xiàn)的頻率,然后返回一個垂直的數(shù)字數(shù)組。例如,使用FREQUENCY函數(shù)可以在分數(shù)區(qū)域內(nèi)計算測驗分數(shù)的個數(shù)。由于FREQUENCY返回一個數(shù)組,所以它必須以數(shù)組公式的形式輸入。其語法是:FREQUENCY(data_array,bins_array)。參數(shù)data_array代表要對其頻率進行計數(shù)的一組數(shù)值或?qū)@組數(shù)值的引用。如果data_array中不包含任何數(shù)值,則FREQUENCY返回一個零數(shù)組。參數(shù)bins