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

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

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

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

圖8-3

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

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

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

圖8-7

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

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

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

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

圖8-128.2工資計(jì)算表與考勤統(tǒng)計(jì)表8.2.1建立薪酬管理規(guī)則薪酬管理規(guī)則由公司章程制定,財(cái)務(wù)人員按照公司制定的薪酬管理規(guī)則進(jìn)行薪酬核算。為方便財(cái)務(wù)人員查閱相關(guān)規(guī)則,可以將規(guī)則的重點(diǎn)內(nèi)容錄入Excel當(dāng)中。下面以某公司薪酬管理制度為例進(jìn)行講解,使用者可根據(jù)實(shí)際工作要求自行制定薪酬管理規(guī)則。在Excel2019中,新建工作表并命名為“規(guī)則”?；诠疽?guī)定重點(diǎn)記錄薪酬管理制度的要求。假定規(guī)則如下:公司規(guī)定員工可選擇住宿或不住宿,住宿分為1、2個(gè)等級(jí),其中,等級(jí)1的住宿費(fèi)為400元/月;等級(jí)2的住宿費(fèi)為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)。出勤的考核獎(jiǎng)懲辦法如下:病假扣除日工資的1/2,事假扣除日工資,遲到早退,第一次扣20元,超過(guò)一次,每次扣100元,整月全勤獎(jiǎng)勵(lì)200元。8.2.1建立薪酬管理規(guī)則三險(xiǎn)一金及個(gè)人所得稅計(jì)算方法如圖8-13所示。2018年《中華人民共和國(guó)個(gè)人所得稅法》(第七次修正)第十一條規(guī)定:“居民個(gè)人取得綜合所得,按年計(jì)算個(gè)人所得稅;有扣繳義務(wù)人的,由扣繳義務(wù)人按月或者按次預(yù)扣預(yù)繳稅款;需要辦理匯算清繳的,應(yīng)當(dāng)在取得所得的次年三月一日至六月三十日內(nèi)辦理匯算清繳。”因此,企業(yè)作為扣減義務(wù)人應(yīng)按月或按次預(yù)扣預(yù)繳稅款,月度個(gè)人所得稅稅率表(綜合所得適用)如圖8-13所示。

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

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

圖8-15

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

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

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

圖8-198.2.2構(gòu)建工資計(jì)算表和考勤統(tǒng)計(jì)表第二步,自動(dòng)確認(rèn)獲得全勤獎(jiǎng)的員工。①在I2單元格中輸入“=IF(SUM(F2:H2)=0,200,0)”。②將鼠標(biāo)放在I2單元格右下角,當(dāng)光標(biāo)變?yōu)槭中魏?利用填充柄功能向下填充I列。③利用SUM函數(shù)計(jì)算合計(jì)數(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,即該員工沒(méi)有請(qǐng)事假、病假或遲到早退的情況,則返回“200”,也就是得到全勤獎(jiǎng)200元,否則就是沒(méi)有得到全勤獎(jiǎng)。根據(jù)薪酬管理規(guī)則,“缺勤扣款”應(yīng)基于單日工資計(jì)算,因此,需要先計(jì)算工資總額,再計(jì)算缺勤扣款。

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

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

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

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

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

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

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

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

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

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

圖8-308.2.2構(gòu)建工資計(jì)算表和考勤統(tǒng)計(jì)表第五步,利用ROUND和MAX嵌套函數(shù)計(jì)算個(gè)人所得稅。根據(jù)個(gè)人所得稅稅率表(綜合所得適用)的稅率及速算扣除數(shù)(如圖8-13所示)計(jì)算個(gè)人所得稅。①單擊“個(gè)人所得稅”標(biāo)題下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)”。②將鼠標(biāo)放在S2單元格右下角,當(dāng)光標(biāo)變?yōu)槭中魏?利用填充柄功能向下填充S列。③利用SUM函數(shù)計(jì)算預(yù)扣預(yù)繳的個(gè)人所得稅合計(jì)數(shù),如圖8-31所示。

圖8-318.2.2構(gòu)建工資計(jì)算表和考勤統(tǒng)計(jì)表【函數(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ù)速算扣除方法計(jì)算個(gè)人所得稅,在以下幾組數(shù)當(dāng)中取最大數(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)建工資計(jì)算表和考勤統(tǒng)計(jì)表第六步,計(jì)算實(shí)發(fā)合計(jì)數(shù)。①單擊“實(shí)發(fā)合計(jì)”標(biāo)題下T2單元格,并輸入“=R2-S2”。②將鼠標(biāo)放在T2單元格右下角,當(dāng)光標(biāo)變?yōu)槭中魏?利用填充柄功能向下填充T列。③利用SUM函數(shù)計(jì)算實(shí)發(fā)工資的合計(jì)數(shù),如圖8-32所示。

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

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

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

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

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

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

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

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

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

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