哈嘍，小伙伴們，發(fā)工資啦！

打工人的快樂又回來了~

不過，看了看工資數(shù)以及銀行卡余額，短暫的快樂也能立刻消失！

說起發(fā)工資，就不得不提一下工資表，你們知道一張 自動(dòng)化的工資表到底是怎么做出來的嗎？

這里面的門路可多著呢，今天就用超大篇幅來給大家詳細(xì)講解一下。

前方請(qǐng)注意：此文章巨巨巨巨巨長(zhǎng)，有需要的朋友，可以先收藏，慢慢看。

涉及Excel知識(shí)點(diǎn)：

◎自動(dòng)提取工齡工資、提成數(shù)據(jù)、獎(jiǎng)金數(shù)據(jù)

◎自動(dòng)判定全勤獎(jiǎng)

◎自動(dòng)核算加班、請(qǐng)假、曠工、遲到早退金額

◎自動(dòng)統(tǒng)計(jì)應(yīng)發(fā)工資

◎自動(dòng)核算五險(xiǎn)一金和實(shí)發(fā)工資

工資表的核算建立在 考勤匯總表、 員工信息表（本文中的工齡工資表就是一種員工信息表）、 業(yè)績(jī)提成表、 獎(jiǎng)金發(fā)放表的基礎(chǔ)上。

全自動(dòng)的工資表模板，掃碼進(jìn)群領(lǐng)取

本文將用到《由打卡記錄一鍵生成考勤匯總表，加班遲到早退全統(tǒng)計(jì)好?。ㄏ缕?中的考勤匯總表。

在本文學(xué)到的表格配置、公式搭建可以借用到任何工資表里。

一、 工資模板表必備構(gòu)件準(zhǔn)備

工資的組成有很多。要實(shí)現(xiàn)自動(dòng)核算，就必須提供必要的組件供工資表調(diào)用。通常一份工資表核算包括：

◎工資表（最終呈現(xiàn)工資數(shù)據(jù)的主表）

◎員工信息表。提供員工的基本信息，如工齡、基本工資、社保檔次等等。有了這些數(shù)據(jù)，工資表才能自動(dòng)根據(jù)員工姓名核算基本工資、工齡工資、五險(xiǎn)一金等等。

◎五險(xiǎn)一金基數(shù)與比例表。如果不是按照應(yīng)發(fā)工資作為繳費(fèi)基數(shù)，必須要單獨(dú)提供繳費(fèi)基數(shù)。

◎月度考勤匯總表。提供員工的上班天數(shù)、請(qǐng)假天數(shù)、遲到早退數(shù)據(jù)、加班數(shù)據(jù)、曠工數(shù)據(jù)等。有了這些數(shù)據(jù)，工資表才能自動(dòng)計(jì)算員工當(dāng)月的考勤工資部分。

展開全文

◎業(yè)績(jī)提成表。提供員工的業(yè)績(jī)數(shù)據(jù)、提成比例數(shù)據(jù)。

◎獎(jiǎng)金表。提供員工每月的獎(jiǎng)金等數(shù)據(jù)。

二、 輸入當(dāng)月員工數(shù)據(jù)

打開準(zhǔn)備好的各種表格。如果各位沒有準(zhǔn)備，可以用本文提供的配套文件：工資表模板、12月（考勤）匯總表。

復(fù)制12月考勤匯總表中的姓名、編號(hào)粘貼到工資表中備用。

三、引用工齡工資和基本工資

根據(jù)公司的規(guī)定，員工的工齡補(bǔ)貼計(jì)算方法如下，以每年的1月1日為計(jì)算基準(zhǔn)（如果讀者所在的公司規(guī)定不同，以此為例修改即可）：

1.入職年限少于2年，工齡補(bǔ)助為100元

2.入職年限在2~5年（含5年），工齡補(bǔ)助=入職年限*100。

3.入職年限在5年以上的，工齡補(bǔ)助封頂為500元。

繼續(xù)定位到F單元格輸入公式“=IFS(E2=1,100,AND(1e2,e2=5),e2*100,e2 5,500)/e2,e2=5),e2*100,e2 ”，下拉完成工齡工資的計(jì)算。

公式解釋：

使用IFS函數(shù)，按照入職年限的分類，依次為不同年限的工齡賦予不同的金額，相比IF函數(shù)多層嵌套要簡(jiǎn)潔一些。

接著返回“工資表”工作表，C5單元格輸入公式=工齡工資!C2，D5單元格輸入公式=工齡工資!F2 ，選中C5:D5下拉填充完成基本工資和工齡工資引用。

提示：

因?yàn)楣べY表和工齡工資表都是根據(jù)考勤匯總表輸入（或粘貼）的，姓名和編號(hào)相同并且排列順序是一致的，所以直接用等號(hào)引用數(shù)據(jù)，結(jié)構(gòu)非常簡(jiǎn)潔。

如果“工齡工資表”并非根據(jù)考勤表匯總創(chuàng)建，而是人事依據(jù)入職、離職員工統(tǒng)計(jì)的，其人數(shù)、姓名排序不一定與考勤匯總表一樣，那么基本工資公式改為=IFERROR(VLOOKUP(A5,工齡工資!A2:F201,3,0),"")，工齡工資公式改為=IFERROR(VLOOKUP(A5,工齡工資!A2:F201,6,0),"")。

四、引用提成金額

切換到“提成表”工作表，首先根據(jù)F列的業(yè)績(jī)提取規(guī)則，在G1:H3創(chuàng)建查找輔助列。

公式解釋：

使用VLOOKUP函數(shù)進(jìn)行分區(qū)段模糊查找得到狀態(tài)值，然后和指定比例相乘得到提成金額。VLOOKUP分區(qū)段模糊查找可以看《多條件判斷，勸你用VLOOKUP函數(shù)模糊查找取代IF函數(shù)的一長(zhǎng)串公式！》。

返回“工資表”工作表，定位到E6單元格輸入公式“=IFERROR(VLOOKUP(A5,提成表!A2:D201,4,0),"")”，完成提成數(shù)據(jù)的引用。

五、全勤獎(jiǎng)的判斷

公司規(guī)定，每月的全勤獎(jiǎng)是100元，但是員工只要有一次遲到、早退或者請(qǐng)假、曠工，就沒有。定位到F5單元格輸入公式“=IF(AND(A5"",H5=0,I5=0,J5=0),100,0)”，下拉填充完成設(shè)置。

公式解釋：

使用IF函數(shù)嵌套AND函數(shù)，只有滿足A5不為空（即有員工姓名），并且H5、I5、J5為空（即沒有遲到/早退/曠工/請(qǐng)假數(shù)據(jù)），那么就顯示為100。否則顯示為0。

六、加班、遲到/早退數(shù)據(jù)引用

加班和遲到早退金額可以直接引用考勤匯總表中的統(tǒng)計(jì)。

相比《由打卡記錄一鍵生成考勤匯總表，加班遲到早退全統(tǒng)計(jì)好?。ㄏ缕分械目记趨R總表模板，12月匯總表把遲到早退、加班進(jìn)行了分別統(tǒng)計(jì)。想了解分別統(tǒng)計(jì)過程的見編輯提供的附件操作。

在G5單元格輸入公式“=[12月匯總表.xlsm]匯總表!F5”、在H5單元格輸入公式“=[12月匯總表.xlsm]匯總表!E5”，依次引用考勤匯總表的當(dāng)月加班和當(dāng)月扣款的數(shù)據(jù)即可。

七、請(qǐng)假扣款計(jì)算

公司規(guī)定職工月計(jì)薪天數(shù)21.75天，職工請(qǐng)假扣費(fèi)=“請(qǐng)假天數(shù)*基本工資/21.75”。請(qǐng)假天數(shù)可以引用考勤匯總表中的統(tǒng)計(jì)。定位到I5單元格輸入公式“=-ROUND([12月匯總表.xlsm]匯總表!C5*C5/21.75,2)”，下拉填充完成統(tǒng)計(jì)。

八、曠工扣款計(jì)算

每曠工1天扣費(fèi)=“2*基本工資/21.75”。定位到I5單元格輸入公式“=- ROUND([12月匯總表.xlsm]匯總表!D5*2*C5/21.75,2)”，下拉公式完成統(tǒng)計(jì)。

九、應(yīng)發(fā)小計(jì)

在K5中輸入公式=SUM(C5:J5)然后下拉填充即可。

十、五險(xiǎn)一金的代扣計(jì)算

五險(xiǎn)一金的計(jì)算涉及兩個(gè)數(shù)：繳費(fèi)基數(shù)與繳費(fèi)比例。

各企業(yè)員工五險(xiǎn)的繳費(fèi)比例統(tǒng)一；公積金的繳費(fèi)比例在符合各地規(guī)定的范圍內(nèi)，每個(gè)企業(yè)可能不一樣。

繳費(fèi)基數(shù)更復(fù)雜，每年一變，具體要看所在地與公司的規(guī)定。

本處按以下規(guī)則計(jì)算五險(xiǎn)一金。

（1）繳費(fèi)基數(shù)：五險(xiǎn)與公積金都以應(yīng)發(fā)工資作為繳費(fèi)基數(shù)；同時(shí)滿足筆者所在地區(qū)的規(guī)定，3488元≤繳費(fèi)基數(shù)≤17442元。

（2）繳費(fèi)比例：五險(xiǎn)與公積金個(gè)人繳費(fèi)比例合計(jì)22.02%。

在L5單元格中輸入以下公式并下拉填充：

=ROUND(IFS(K5=3488,3488*0.2202,AND(K53488,K5=17442),K5*0.2202,K517442,17442*0.2202),2)

公式解釋：

繳存比例是一個(gè)小數(shù)，為了避免計(jì)算后出現(xiàn)小數(shù)誤差，這里使用ROUND函數(shù)進(jìn)行四舍五入的計(jì)算。

提示：

十一、個(gè)稅統(tǒng)計(jì)

根據(jù)現(xiàn)在個(gè)稅政策，員工應(yīng)稅工資（應(yīng)發(fā)工資-五險(xiǎn)一金）超過5000部分需要按照不同稅率進(jìn)行繳存。定位到M5單元格輸入公式“=ROUND(MAX((K5-L5-5000)*5%*{0.6,2,4,5,6,7,9}-5*{0,21,111,201,551,1101,2701},0),2)”，下拉可以完成數(shù)據(jù)的計(jì)算。

公式解釋：

使用MAX函數(shù)根據(jù)稅務(wù)總局的規(guī)定，分段計(jì)算不同工資的扣稅記錄。最后在外層嵌套R(shí)OUND函數(shù)進(jìn)行四舍五入。公式含義大家不必深究。

十二、實(shí)發(fā)工資的計(jì)算

1.個(gè)人實(shí)發(fā)工資

定位到N5單元格輸入公式“=SUM(L5:M5)”，下拉完成應(yīng)扣金額的統(tǒng)計(jì)，定位到O5單元格輸入公式“=K5-N5”，下拉即可完成實(shí)發(fā)工資的計(jì)算。

2.公司整體實(shí)發(fā)工資

最后在B29輸入“=SUM(O5:O28)”完成實(shí)發(fā)工資總額的統(tǒng)計(jì)。

在B30單元格輸入公式“=TEXT(INT(C29),"[dbnum2]")"元"IF(INT(C29*10)-INT(C29)*10=0,"",TEXT(INT(C29*10)-INT(C29)*10,"[dbnum2]")"角")IF(INT(C29*100)-INT(C29*10)*10=0,"整",TEXT(INT(C29*100)-INT(C29*10)*10,"[dbnum2]")"分")”，轉(zhuǎn)換為大寫金額。

公式解釋：

大寫轉(zhuǎn)換公式可以參考《Excel最常用的4種格式轉(zhuǎn)換，添加前綴、單位，大小寫轉(zhuǎn)換等！》，大家只要替換“C29”單元格即可使用。

十三、工資模板實(shí)際運(yùn)用

完成上述的設(shè)計(jì)后保存模板。

小伙伴以后需要制作其他月份工資表，只需以下5步，Excel就可以自動(dòng)完成工資核算：

（1）更改一下“工資表”的標(biāo)題。

（2）利用考勤匯總模板生成月份考勤匯總表。

（3）復(fù)制月份考勤匯總表的員工名單到“工資表”?？梢栽O(shè)置公式等進(jìn)行自動(dòng)復(fù)制，想知道怎么設(shè)置的可以留言。

（4）在“工齡工資表”中添加新員工信息。

（5）復(fù)制提成數(shù)據(jù)復(fù)制到“提成表”。

在實(shí)際使用中，工資表制作還會(huì)涉及到很多方面，本期限于篇幅的限制就介紹到這里了。

如果大家有如下這些需求，歡迎在評(píng)論區(qū)留言。

如何把工資單制作成可裁剪的工資條？

將生成的工資條自動(dòng)發(fā)送到員工的郵箱？

使用函數(shù)自動(dòng)刪除離職員工？

自動(dòng)增加入職新員工名單？

附錄：考勤匯總模板的修改

Step 01 打開匯總表模板文件，進(jìn)入“整理模板表”工作表。

Step 02 在K列前添插入一列。J列為“當(dāng)日遲到/早退扣款”，K列為“當(dāng)日加班工資”。

Step 03 修改J列的扣款公式。在J2中輸入如下按公式并下拉填充。

Step 04 在K2中輸入如下按公式并下拉填充得到當(dāng)日加班工資。

Step 05 按Alt+F11進(jìn)入VBA編輯器，修改模塊1中的遍歷工作表代碼。很簡(jiǎn)單，將復(fù)制范圍C1:M200修改成C1:N200，因?yàn)槲覀冊(cè)黾恿艘涣袛?shù)據(jù)。修改后單擊VBA編輯器上的保存按鈕。

Step 06 返回到“匯總表”工作表。首先清除G列后所有數(shù)據(jù)。

Step 07 在“當(dāng)月扣款/加班”列前插入兩列，分別在“當(dāng)日扣款/加班”列和“9:00:00”列前插入一列，取消日期和星期的跨列居中。重新填寫各列的名稱如下。

Step 08 復(fù)制G2:G3，然后單擊H2進(jìn)行粘貼。

Step 09 定位到L2單元格輸入公式“=G2+1”，向右拖至FE2單元格，完成日期的填充。

Step 12 分別修改I5、J5、K5中的公式如下：

然后下拉填充。

Step 13選中G3:K28向右填充至FE28完成數(shù)據(jù)的引用。

單擊保存，完成考勤匯總表模板的修改。

修改模板后，按照《由打卡記錄一鍵生成考勤匯總表，加班遲到早退全統(tǒng)計(jì)好?。ㄏ缕分械姆椒ㄟ\(yùn)行模板即可快速得到某月，如2022年12月，的考勤匯總表文件——12月匯總表文件。

好的，以上就是今天要給大家分享的內(nèi)容，相信能一字不落看到這兒的人，都是勇士！

為你們鼓掌~~~

Excel基礎(chǔ)不扎實(shí)的同學(xué)，歡迎學(xué)習(xí)下面這套工作用得上的Excel訓(xùn)練營(yíng)