編按：

你知道一張工資表里到底囊獲了多少Excel知識嗎？這里面包括計算工齡工資、考勤、社保等，麻煩而復(fù)雜，簡直是HR們的噩夢！沒關(guān)系，今天就來和我們一起學習自動計算工齡、考勤的工資表的制作吧！文中提供了最新個稅計算公式。

哈嘍，大家好呀！每個打工人都喜歡發(fā)工資的那一天，但是如何快速做出準確無誤的工資表，卻是很多HR們的噩夢。今天就教HR們制作自動核算工齡、考勤等的工資表，快速為每個伙伴算對工資。

功能包括：

◎自動計算并提取工齡工資、提成、獎金

◎自動判定全勤獎

◎自動核算加班、請假、曠工、遲到早退金額

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

◎自動核算五險一金和實發(fā)工資

工資表的核算建立在考勤匯總表、員工信息表（本文中的工齡工資表就是一種員工信息表）、業(yè)績提成表、獎金發(fā)放表的基礎(chǔ)上。文中將用到《由打卡記錄一鍵生成考勤匯總表，加班遲到早退全統(tǒng)計好！（下篇）》中的考勤匯總表。

雖然每個公司的工資表組成不一樣，但你在本文學到的表格配置、公式搭建可以借用到任何工資表里。

一、 完整的工資表組成

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

切換到“工齡工資”（員工信息表）工作表，在E2單元格輸入公式“=IFERROR(DATEDIF(D2,$G$2,"Y"),0)”（注意：$G$2使用絕對引用），下拉填充完成入職年限的計算。

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

公式解釋：

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

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

提示：

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

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

四、引用提成金額

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

然后在D2單元格輸入公式“=C2*VLOOKUP(C2,$G$2:$H$4,2)”，下拉完成提成數(shù)據(jù)的計算。

公式解釋：

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

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

五、全勤獎的判斷

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

公式解釋：

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

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

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

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

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

七、請假扣款計算

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

八、曠工扣款計算

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

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

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

十、五險一金的代扣計算

五險一金的計算涉及兩個數(shù)：繳費基數(shù)與繳費比例。

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

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

本處按以下規(guī)則計算五險一金。

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

（2）繳費比例：五險與公積金個人繳費比例合計22.02%。

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

=ROUND(IFS(K5<=3488,3488*0.2202,AND(K5>3488,K5<=17442),K5*0.2202,K5>17442,17442*0.2202),2)

公式解釋：

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

提示：

1.如果你所在公司每個人的繳費基數(shù)一致，但是養(yǎng)老保險、醫(yī)療保險、公積金的繳費基數(shù)不同，則可以新增一個“五險一金”工作表，列出繳費比例、繳費基數(shù)，供計算引用。五險一金公式=ROUND((五險一金!$C$2*0.082+五險一金!$C$3*0.02+五險一金!$C$5*0.12),2)

2.如果你所在公司繳費基數(shù)分成了多個檔次，則需要根據(jù)姓名查到檔次，再由檔次查到不同繳費基數(shù)。首先在“工齡工資”中增加一列檔次，然后在五險一金中分檔次列出繳費基數(shù)。五險一金公式=ROUND(VLOOKUP(VLOOKUP(A5,工齡工資!$A$2:$G$25,7,0),五險一金!$E$2:$H$4,2,0)*0.082+VLOOKUP(VLOOKUP(A5,工齡工資!$A$2:$G$25,7,0),五險一金!$E$2:$H$4,3,0)*0.02+VLOOKUP(VLOOKUP(A5,工齡工資!$A$2:$G$25,7,0),五險一金!$E$2:$H$4,4,0)*0.12,2)

十一、個稅統(tǒng)計

根據(jù)現(xiàn)在個稅政策，員工應(yīng)稅工資（應(yīng)發(fā)工資-五險一金）超過5000部分需要按照不同稅率進行繳存。定位到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ù)的計算。

公式解釋：

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

十二、實發(fā)工資的計算

1.個人實發(fā)工資

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

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

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

在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è)計后保存模板。

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

（1）更改一下“工資表”的標題。

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

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

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

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

當然在實際使用中，工資表制作還會涉及到很多方面，本期限于篇幅的限制就介紹到這里了。當前工資表適合在職員工的工資核算；如果是當月離職、新入職員工的核算，則需要小修改——增加實際出勤天數(shù)。歡迎關(guān)注下篇《離職和新入職員工工資核算》。

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

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

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

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

=SUMIFS($F$2:$F$200,$C$2:$C$200,I2,$F$2:$F$200,"<0")

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

=SUMIFS($F$2:$F$200,$C$2:$C$200,I2,$F$2:$F$200,">0")

Step 05 按Alt+F11進入VBA編輯器，修改模塊1中的遍歷工作表代碼。很簡單，將復(fù)制范圍C1:M200修改成C1:N200，因為我們增加了一列數(shù)據(jù)。修改后單擊VBA編輯器上的保存按鈕。

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

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

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

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

Step 10 單擊E4，輸入公式=SUMIF($G$4:$FE$4,G$4,$G5:$FE5),右拉填充到F4。選中E4:F4，再向下填充獲得當月遲到/早退扣款、當月加班工資。設(shè)置單元格格式為貨幣格式。

Step 11 單擊H5，輸入公式=IFERROR(INDIRECT(G$2&"!k"&ROW(A2)),"")并下拉填充獲得當日加班工資。

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

=IFERROR(INDIRECT(I$2&"!L"&ROW(A2)),"")

=IFERROR(INDIRECT(J$2&"!M"&ROW(B2)),"")

=IFERROR(INDIRECT(K$2&"!N"&ROW(A2)),"")

然后下拉填充。

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

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

修改模板后，按照《由打卡記錄一鍵生成考勤匯總表，加班遲到早退全統(tǒng)計好?。ㄏ缕?/span>中的方法運行模板即可快速得到某月，如2022年12月，的考勤匯總表文件——12月匯總表文件。

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學習Excel：

相關(guān)推薦：

Excel做庫存電子臺賬案例：倉庫出入庫臺賬匯總

《由打卡記錄一鍵生成考勤匯總表，加班遲到早退全統(tǒng)計好?。ㄏ缕?/span>

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者ITFANS；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。