Excel在財務(wù)中的應(yīng)用 第3版 課件 項目四 Excel在薪資管理中的應(yīng)用

Excel在薪酬管理中的應(yīng)用

原先公司也有工資計算的表格，但是一旦增加或減少了員工，或者獎金等發(fā)生了調(diào)整，工資表就又要重新編制，這就失去了Excel的作用。

因此李燕決定重新編制職工薪資管理系統(tǒng)表格，通過設(shè)置多個原始表格，利用Excel表格強(qiáng)大的數(shù)據(jù)處理能力，讓薪資管理變得簡單便捷。工作情境工作任務(wù)分析

李燕決定要完成以下設(shè)置：1.建立工資管理框架，包括編制員工信息表、編制員工考勤信息表、編制固定薪資信息表等；2.完成工資信息輸入、個稅的計算；3.完成工資條的查詢與打印。任務(wù)一建立工資管理系統(tǒng)表格任務(wù)二編制“職工當(dāng)月工資匯總表”任務(wù)三薪資的查詢與工資條制作建立工資管理系統(tǒng)表格o1由于職工薪酬管理需要用到很多信息，包括公司職工信息、薪資獎懲規(guī)定信息、考勤信息、個稅稅率等，這些表格不能全部放在一張工作表中，必須要進(jìn)行分類，只有各個表格的內(nèi)容都相對獨立又有相互聯(lián)系，才能使表格在符合特定規(guī)范的同時，又能利用公式對Excel表格之間的數(shù)據(jù)進(jìn)行統(tǒng)計和計算。任務(wù)一建立工資管理系統(tǒng)表格1.編制“公司職工檔案”工作表(1)創(chuàng)建超級表復(fù)制項目一中做過的“萬隆燈具有限公司職工信息表”表格，刪除薪資管理中不需要用到“聯(lián)系號碼”、“身份證號碼”和“出生日期”（H、I、J三列）后，創(chuàng)建超級表。任務(wù)一建立工資管理系統(tǒng)表格(2)套用表格格式2.計算職工的通信費補(bǔ)貼通訊費補(bǔ)貼標(biāo)準(zhǔn)：總經(jīng)理500元，學(xué)歷研究生及以上且職稱為高級或者是部門經(jīng)理都是300元，其他職工150元。=IF(G2="總經(jīng)理",500,IF(OR(AND(D2="研究生",E2="高級"),G2="部門經(jīng)理"),300,150))

在表格狀態(tài)下，公式是自動套用的“[@職務(wù)]”代表G列“[@最高學(xué)歷]”代表D列“[@職稱]”代表E列知識技能4-1認(rèn)識表格要簡化一組相關(guān)數(shù)據(jù)的管理和分析，可以將單元格范圍轉(zhuǎn)換為表格。表格功能的好處如下：

（1）表格包含一定的格式

可以通過“套用表格格式”使表格數(shù)據(jù)內(nèi)容顯示得更為清晰，打印效果更為美觀。（2）表格樣式選項知識技能4-1認(rèn)識表格

勾選篩選按鈕，可實現(xiàn)對標(biāo)題行進(jìn)行排序和篩選；還可勾選匯總行，實現(xiàn)在不輸入公式的情況下快速匯總表中的相關(guān)數(shù)據(jù)信息。知識技能4-1認(rèn)識表格（3）表格會使得引用結(jié)構(gòu)化在進(jìn)行引用時可以不使用“A1”之類的引用，而是在公式中引用表名或列名，實現(xiàn)數(shù)據(jù)公式的自動擴(kuò)展，極大提高了用戶管理和分析多表數(shù)據(jù)內(nèi)容的效率?！癧@職務(wù)]”代表G列“[@最高學(xué)歷]”代表D列“[@職稱]”代表E列（4）自動生成表格名稱

表格在生成的同時，會自動在“公式”功能區(qū)“定義的名稱”組“名稱管理器”中生成該表的名稱，使跨工作表引用更加便捷。如果想更改該表的名稱，除了通過“名稱管理器”編輯，還能在表格的“設(shè)計”選項卡中修改。知識技能4-1認(rèn)識表格表的名稱在“名稱管理器”中只能編輯，不能刪除。3.編制“薪資獎懲標(biāo)準(zhǔn)表”工作表3.編制“薪資獎懲標(biāo)準(zhǔn)表”工作表

選擇【視圖】選項卡，將網(wǎng)格線前面的小方框的勾去掉，整個工作表就會呈現(xiàn)白紙狀態(tài)。4.編制“職工考勤獎懲表”工作表（1）設(shè)置表格表頭單元格公式含義B2=VLOOKUP($A2,表1,2,0)根據(jù)序號從“表1”名稱范圍中找對應(yīng)的職工姓名C2=VLOOKUP($A2,表1,6,0)根據(jù)序號從“表1”名稱范圍中找對應(yīng)的職工部門D2=VLOOKUP($A2,表1,7,0)根據(jù)序號從“表1”名稱范圍中找對應(yīng)的職工職務(wù)E2=VLOOKUP($A2,表1,10,0)根據(jù)序號從“表1”名稱范圍中找對應(yīng)的職工工資（含津貼）（2）引用“公司職工檔案”工作表的數(shù)據(jù)4.編制“職工考勤獎懲表”工作表（3）輸入職工的考勤情況并計算獎懲工資根據(jù)職工本月的請假及加班天數(shù)在“職工考勤獎懲表”的“病假”“事假”“曠工”（I:M列）中進(jìn)行填列，并通過這些考勤數(shù)據(jù)計算“全勤工資”“病事假/曠工扣款”“加班工資”（F:H列）。4.編制“職工考勤獎懲表”工作表（3）輸入職工的考勤情況并計算獎懲工資單元格公式F2=IF((I2=0)*(J2=0)*(K2=0),薪資獎懲標(biāo)準(zhǔn)表!$C$4,0)

F2單元格，如果沒有“病假”“事假”和“曠工”的話，那么就從“薪資獎懲標(biāo)準(zhǔn)表”的C4單元格（全勤獎）取數(shù)200元。4.編制“職工考勤獎懲表”工作表（3）輸入職工的考勤情況并計算獎懲工資單元格公式G2=ROUND($E2/30*(0.1*$I2+0.5*$J2+$K2,2)根據(jù)E2單元格先計算出日工資（假定一個月30天），然后再分別與“病假”“事假”“曠工”的獎金計算標(biāo)準(zhǔn)相乘。其中，病假缺勤系數(shù)（0.1）、病假天數(shù)（I2）”；事假缺勤系數(shù)（0.5）、事假天數(shù)（J2）”；曠工缺勤工資不需要系數(shù)，直接與曠工天數(shù)（K2）相乘。最后用ROUND函數(shù)對結(jié)果取兩位小數(shù)。知識技能4-2ROUND函數(shù)含義：

將數(shù)字四舍五入到指定的位數(shù)，也就是我們常說的四舍五入。

語法格式：ROUND（number,num_digits）其中：number是要四舍五入的數(shù)字，num_digits是四舍五入的位數(shù)，即計算精度。如果num_digits大于零，則將數(shù)字四舍五入到指定的小數(shù)位數(shù)；等于零，則將數(shù)字四舍五入到最接近的整數(shù)；小于零，則將數(shù)字四舍五入到小數(shù)點左邊的相應(yīng)位數(shù)。單元格公式H2=300*$L2+200*$M2如果是在法定節(jié)假日加班，則用法定假日加班天數(shù)（L2）乘以法定假日加班工資（300）；如果是普通雙休日加班，則用普通假日加班天數(shù)（M2）乘以日常假日加班工資（200）。

最后將兩者相加得到總的加班工資。（3）輸入職工的考勤情況并計算獎懲工資4.編制“職工考勤獎懲表”工作表4.編制“職工考勤獎懲表”工作表編制“職工當(dāng)月工資匯總表”o2任務(wù)二編制“職工當(dāng)月工資匯總表”1.職工工資匯總表表頭2.職工工資匯總表取數(shù)“職工當(dāng)月工資匯總表”工作表的取數(shù)來源于“職工考勤獎懲表”，可將“職工考勤獎懲表”的A1:M16單元格區(qū)域定義為名稱“考勤”，因為后面在函數(shù)取數(shù)中需要用到這塊數(shù)據(jù)。2.職工工資匯總表取數(shù)單元格公式B2=VLOOKUP($A2,考勤,2,0)C2=VLOOKUP($A2,考勤,3,0)D2=VLOOKUP($A2,考勤,4,0)E2=VLOOKUP($A2,考勤,5,0)F2=VLOOKUP($A2,考勤,6,0)G2=VLOOKUP($A2,考勤,7,0)H2=VLOOKUP($A2,考勤,8,0)I2=VLOOKUP($A2,表1,11,0)取數(shù)方法不唯一，供參考3.“職工當(dāng)月工資匯總表”中“三險一金”的計算“四險一金”是指用人單位給予勞動者的幾種保障性待遇的合稱，包括養(yǎng)老保險、醫(yī)療保險、失業(yè)保險、工傷保險、住房公積金；其中，生育保險基金并入職工基本醫(yī)療保險基金，統(tǒng)一征繳。“四險一金”通常是根據(jù)應(yīng)納稅所得額乘以計提比例計算得到的，而計提比例各個省份也會有所不同。這里會涉及養(yǎng)老保險、醫(yī)療保險、失業(yè)保險個人計提的部分，進(jìn)而會影響到職工的薪資計算，因此“三險一金”的計算非常重要。3.“職工當(dāng)月工資匯總表”中“三險一金”的計算(1)“應(yīng)發(fā)工資”的計算

根據(jù)應(yīng)發(fā)工資的含義，J2單元格的公式設(shè)定為“

E2

F2

G2

H2

I2”，并向下填充該列；也就是說應(yīng)發(fā)工資等于該職工的工資（含津貼）加上獎金、補(bǔ)貼后扣除缺勤工資的金額。3.“職工當(dāng)月工資匯總表”中“三險一金”的計算(2)“社?！钡挠嬎愎景?0.5%的個人計提比例（即養(yǎng)老保險8%、醫(yī)療保險2%、失業(yè)保險0.5%），通信費不參與計提。因此，K2單元格=ROUND(($J2-$I2)*8%,2)，L2單元格=ROUND(($J2-$I2)*2%,2)，M2單元格=ROUND(($J2-$I2)*0.5%,2)，并向下填充該列。3.“職工當(dāng)月工資匯總表”中“三險一金”的計算(3)“公積金”的計算單位和職工繳存比例最低不低于5%，原則上不高于12%。萬隆燈具有限公司目前一律采用10%的比例來計提，所以，公積金=（應(yīng)發(fā)工資-通信費）*10%。L2單元格=ROUND(($J2-$I2)*10%,2)，并向下填充該列。3.“職工當(dāng)月工資匯總表”中“三險一金”的計算4.編制“職工個稅計算表”工作表從2019年開始，扣繳義務(wù)人向居民個人支付工資、薪金所得時，按照累計預(yù)扣法計算預(yù)扣稅款，并按月辦理扣繳申報，采用“累計預(yù)扣法”。也就是說，先把截止當(dāng)月所有工資，相當(dāng)于“年”算個稅，然后再減去截止到上月已累計繳納金額，剩下的就是當(dāng)月需要交稅金額。因為是預(yù)繳，需要到第二年3-6月，再進(jìn)行“匯算清繳”。（1）編制“職工個稅計算表”表頭（2）“職工個稅計算表”的取數(shù)

A列、B列數(shù)據(jù)可以參照前文的方法，實現(xiàn)跨表格取數(shù)。4.編制“職工個稅計算表”工作表（2）“職工個稅計算表”的取數(shù)

C列以后的數(shù)據(jù)因為涉及到累計的概念，作為連續(xù)性，年初第一期時，就應(yīng)將“職工當(dāng)月工資匯總表”的相關(guān)信息摘錄到該表中，到第二個月時，將上一期的金額和本期金額相加，就能得到累計值了。所以，2023年12月的“職工個稅計算表”取數(shù)即為前11個月的數(shù)據(jù)加上本月計算的值。

考慮到李燕所在的工資之前沒有用Excel計算過個數(shù)，我們直接從“自然人稅收管理系統(tǒng)扣繳客戶端”中取數(shù)。（2）“職工個稅計算表”的取數(shù)（3）“累計應(yīng)納稅所得額”的計算累計預(yù)扣預(yù)繳應(yīng)納稅所得額=累計收入-累計減除費用-累計專項扣除-累計專項附加扣除-累計依法確定的其他扣除?！袄塾嫓p除費用”該列數(shù)值即按照5000元/月乘以納稅人當(dāng)年截至本月在本單位的任職受雇月份數(shù)計算的數(shù)額。“累計專項扣除”具體包括社會保險費和住房公積金，也就是我們上一節(jié)中“五險一金”計算金額的累計值。

“累計專項附加扣除”包括“累計子女教育支出扣除”“累計繼續(xù)教育支出扣除”“累計住房貸款利息支出扣除”“累計住房租金支出扣除““累計贍養(yǎng)老人支出扣除”“累計3歲以下嬰幼兒照護(hù)支出扣除”“累計其他扣除”。（3）“累計應(yīng)納稅所得額”的計算選擇M2單元格輸入公式“=IF(C2-SUM(D2:M2)>0,C2-SUM(D2:M2),0)”，確保應(yīng)納稅所得額均大于零。5.“個人所得稅”的計算個人所得稅=應(yīng)納稅所得額*稅率-速算扣除數(shù)。

從“自然人稅收管理系統(tǒng)扣繳客戶端”導(dǎo)出的表格可以看到有“稅率”和“速算扣除數(shù)”兩個輔助列，因為Excel具有強(qiáng)大的計算功能，所以我們的表格中拿掉了這兩列，直接計算“累計應(yīng)納稅額”。

通?？梢钥紤]使用IF系列函數(shù)、VLOOKUP函數(shù)、MAX函數(shù)或數(shù)組實現(xiàn)“個人所得稅”的計算。（1）利用IF函數(shù)計算“個人所得稅”單元格公式O2=IF(N2<36000,N2*3%,IF(N2<144000,N2*10%-2520,IF(N2<300000,N2*20%-16920,IF(N2<420000,N2*25%-31920,IF(N2<660000,N2*30%-52920,IF(N2<960000,N2*35%-85920,N2*45%-181920))))))IFS函數(shù)該如何實現(xiàn)呢

如果覺得IF嵌套函數(shù)掌握得不好，也可以用IF結(jié)合AND實現(xiàn)“個人所得稅”的計算。單元格公式O2=IF(AND(N2>=0,N2<=36000),N2*3%,"")&IF(AND(N2>36000,N2<=144000),N2*10%-2520,"")&IF(AND(N2>144000,N2<=300000),N2*20%-16920,"")&IF(AND(N2>300000,N2<=420000),N2*25%-31920,"")&IF(AND(N2>420000,N2<=660000),N2*30%-52920,"")&IF(AND(N2>660000,N2<=960000),N2*35%85920,"")&IF(N2>960000,N2*45%-181920,"")連接區(qū)間段時，一定要確保連續(xù)性（2）利用VLOOKUP函數(shù)計算“個人所得稅”單元格公式O2=N2*(VLOOKUP(N2,薪資獎懲標(biāo)準(zhǔn)表!$J$3:$L$9,2,1))-VLOOKUP(N2,薪資獎懲標(biāo)準(zhǔn)表!$J$3:$L$9,3,1)（3）利用MAX函數(shù)計算“個人所得稅”單元格公式O2=MAX(N2*0.03,N2*0.1-2520,N2*0.2-16920,N2*0.25-31920,N2*0.3-52920,N2*0.35-85920,N2*0.45-181920)只有在符合級次的范圍內(nèi)計算出的“個人所得稅”才是最大的。知識技能4-3極值統(tǒng)計1．MAX函數(shù)和MIN函數(shù)含義：MAX是maximum的簡寫，通常用于返回一組值中的最大值。

語法格式：MAX(number1,[number2],...)。其中，參數(shù)number1是必需的，后續(xù)參數(shù)是可選的，最多可包含255個參數(shù)。參數(shù)可以是數(shù)字或者是包含數(shù)字的名稱、數(shù)組或引用。

有最大值就會有最小值，MIN是minimum的簡寫，通常用于返回一組值中的最小值，語法格式和參數(shù)設(shè)定同MAX函數(shù)。知識技能4-3極值統(tǒng)計2．LARGE函數(shù)和SMALL函數(shù)其中，array表示需要確定最大值的數(shù)組或數(shù)據(jù)區(qū)域；k表示返回的數(shù)據(jù)在array區(qū)域中從大到小排列的位置。如果區(qū)域中數(shù)據(jù)點的個數(shù)為n，則函數(shù)LARGE(array,1)返回最大值，函數(shù)LARGE(array,n)返回最小值?？梢?，LARGE函數(shù)比MAX函數(shù)的應(yīng)用范圍更廣。含義：LARGE函數(shù)用于返回數(shù)據(jù)集中第k個最大值。

語法格式：LARGE(array,k,...)。知識技能4-3極值統(tǒng)計2．LARGE函數(shù)和SMALL函數(shù)其中，k表示返回的數(shù)據(jù)在array區(qū)域中從小到大排列的位置。同樣，如果區(qū)域中數(shù)據(jù)點的個數(shù)為n，則函數(shù)SMALL（array，1）返回最小值，函數(shù)SMALL（array，n）返回最大值。含義：SMALL函數(shù)和LARGE函數(shù)相對應(yīng)，用于返回數(shù)據(jù)集中第k個最小值。。

語法格式：SMALL(array,k,...)。（4）利用數(shù)組計算“個人所得稅”單元格公式O2=ROUND(MAX(N2*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,2520,16920,31920,52920,85920,181920}),2)如果想用已有的“薪資獎懲標(biāo)準(zhǔn)表”中的數(shù)據(jù)，需要注意絕對引用該區(qū)域數(shù)值，同時按住<Ctrl+Shift+Enter>組合鍵，才能實現(xiàn)向下拖動。知識技能4-4數(shù)組

數(shù)組是指按規(guī)則排列的一組數(shù)據(jù)的集合，包括常量數(shù)組和內(nèi)存數(shù)組。其中，常量數(shù)組是常量的集合，用花括號括起來；內(nèi)存數(shù)組是指通過計算得到的數(shù)組，存在內(nèi)存中，并可作為函數(shù)的參數(shù)參與下一步運算。按維度分，Excel公式涉及一維數(shù)組和二維數(shù)組。一維數(shù)組按呈現(xiàn)狀態(tài)不同，又可進(jìn)一步分為橫向一維數(shù)組和縱向一維數(shù)組。橫向一維數(shù)組在Excel中只占用一行，并用逗號作為分隔符；縱向一維數(shù)組則是只占用一列，并用分號作為分隔符。知識技能4-4數(shù)組橫向一維數(shù)組縱向一維數(shù)組知識技能4-4數(shù)組常量與一維數(shù)組的乘法計算應(yīng)納稅所得額*稅率需要同時按住<Ctrl+Shift+Enter>組合鍵，才能實現(xiàn)數(shù)組的乘法計算。知識技能4-4數(shù)組一維數(shù)組的減法計算應(yīng)納稅所得額*稅率-速算扣除數(shù)（5）“職工個稅計算表”中其它項目計算（6）“職工當(dāng)月工資匯總表”中“實發(fā)工資”的計算薪資的查詢與工資條制作o31.薪資信息的查詢（1）自動篩選如果是表格的創(chuàng)建中，會在首行自動創(chuàng)建帶有“篩選”按扭。如果是普通單元格區(qū)域，可以選擇需要篩選的標(biāo)題行，執(zhí)行【數(shù)據(jù)】|【篩選】，也可以直接在【開始】選項卡中執(zhí)行【排序和篩選】|【篩選】。任務(wù)三薪資的查詢與工資條制作1.薪資信息的查詢（1）自動篩選查詢該月“生產(chǎn)車間”的薪資水平若想還原成初始狀態(tài)，可單擊“部門”列標(biāo)簽后的“篩選”按鈕，并在彈出的對話框中勾選“全選”；也可按<Ctrl+Shift+L>的組合鍵，退出篩選狀態(tài)。1.薪資信息的查詢（2）自定義篩選篩選該公司實發(fā)工資前五名的員工通配符1.薪資信息的查詢（2）自定義篩選（3）高級篩選在篩選的源數(shù)據(jù)區(qū)域外手動設(shè)定篩選條件區(qū)域：

1.條件區(qū)域的第一行為條件的列標(biāo)簽行，需要與篩選的源數(shù)據(jù)區(qū)域的篩選條件列標(biāo)簽相同；在該列標(biāo)簽下方，至少應(yīng)包含一行具體的篩選條件。2.如果字段具有兩個或以上篩選條件時，可以在對應(yīng)的列標(biāo)簽下方單元格依次列出各個條件，其邏輯關(guān)系為“或”；但若在同一行對應(yīng)的列標(biāo)簽下輸入各個條件，則各條件間的邏輯關(guān)系為“與”。

因此，若要篩選滿足多組條件之一的情況，可