第三章Excel與工資管理一、工資管理流程二、工資初始數(shù)據(jù)設(shè)置三、工資表四、工資查詢五、工資匯總分析?第三章Excel與工資管理一、工資管理流程?1一、工資管理流程員工管理原始數(shù)據(jù)工資計算工資查詢工資匯總職工基本情況表基本工資表、崗位工資表、考勤表、計件表等工資表個人查詢表、部門查詢表工資匯總表?一、工資管理流程員工管理原始數(shù)據(jù)工資計算工資查詢工資匯總職工2二、工資初始數(shù)據(jù)設(shè)置?二、工資初始數(shù)據(jù)設(shè)置?3（一）職工基本情況表1:有效性設(shè)置對“所屬部門”、“職工類別”、“職務(wù)”、“性別”列進行有效性設(shè)置?（一）職工基本情況表1:有效性設(shè)置對“所屬部門”、“職工類別4（一）職工基本情況表2:年齡計算1、輸入公式：=YEAR(TODAY())-YEAR(H3)2、設(shè)置“年齡”列格式為文本、數(shù)值、常規(guī)中的任一種3、設(shè)置“出生日期”列格式為日期型?（一）職工基本情況表2:年齡計算1、輸入公式：?5（一）職工基本情況表3:YEAR函數(shù)返回某日期對應(yīng)的年份。返回值為1900到9999之間的整數(shù)。YEAR(serial_number)日期值例：=YEAR(2008-7-5)結(jié)果：2008=YEAR(10-7-5)結(jié)果：2010?（一）職工基本情況表3:YEAR函數(shù)返回某日期對應(yīng)的年份。返6（二）基本工資表1:相對引用法1、利用單元格兩表相對引用法輸入編號、姓名、所屬部門、職工類別、職務(wù)的數(shù)據(jù)：=職工基本情況表!A32、當(dāng)職工基本情況表的數(shù)據(jù)變化時，基本工資表的對應(yīng)數(shù)據(jù)自動改變?（二）基本工資表1:相對引用法1、利用單元格兩表相對引用法輸7（二）基本工資表2:直接輸入法例：以職務(wù)確定基本工資總經(jīng)理5000副總經(jīng)理4000財務(wù)主管3500總賬會計、辦公室主、銷售部經(jīng)理3000出納、職員2000司機1500?（二）基本工資表2:直接輸入法例：以職務(wù)確定基本工資?8（二）基本工資表3:公式法=IF(E3="總經(jīng)理",5000,IF(E3="副總經(jīng)理",4000,IF(E3="財務(wù)主管",3500,IF(OR(E3="總賬會計",E3="辦公室主任",E3="銷售部經(jīng)理"),3000,IF(OR(E3="出納",E3="職員"),2000,1500)))))?（二）基本工資表3:公式法=IF(E3="總經(jīng)理",50009（二）基本工資表4:邏輯函數(shù)AND(條件1，條件2，...)：所有條件同時滿足即為真，否則為假OR(條件1，條件2，...)：滿足任一條件即為真，否則為假NOT(條件)：當(dāng)條件為真時，返回假注：OR、AND函數(shù)所帶條件不超過30個?（二）基本工資表4:邏輯函數(shù)AND(條件1，條件2，...)10（三）崗位工資表例:按職工類別確定管理人員1000工人:500=IF(D3="管理人員",1000,500)?（三）崗位工資表例:按職工類別確定=IF(D3="管理人員"11（四）考勤表?（四）考勤表?12（五）計件表=D7*E7?（五）計件表=D7*E7?13（六）補貼表例:按部門不同以基本工資為基數(shù)的比例辦公室10%財務(wù)部10%銷售部15%生產(chǎn)部20%=IF(C3=“辦公室”,基本工資表!F3*10%,IF(C3="財務(wù)部",基本工資表!F3*10%,IF(C3="銷售部",基本工資表!F3*15%,基本工資表!F3*20%)))?（六）補貼表例:按部門不同以基本工資為基數(shù)的比例=IF(C314（七）獎金表例:按所屬部門確定辦公室500財務(wù)部600銷售部800生產(chǎn)部1000=IF(C3="辦公室",500,IF(C3="財務(wù)部",600,IF(C3="銷售部",800,1000)))?（七）獎金表例:按所屬部門確定=IF(C3="辦公室",5015三、工資表?三、工資表?16（一）應(yīng)發(fā)工資1:輸入基本工資數(shù)據(jù)崗位工資、獎金、補貼、計件工資同理操作=VLOOKUP(A4,基本工資表!$A$3:$F$15,6)?（一）應(yīng)發(fā)工資1:輸入基本工資數(shù)據(jù)崗位工資、獎金、補貼、計件17（一）應(yīng)發(fā)工資2:VLOOKUP函數(shù)在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)需要在數(shù)組第一列中查找的數(shù)值需要在其中查找數(shù)據(jù)的數(shù)據(jù)表，可以使用對區(qū)域或區(qū)域名稱的引用。待返回的匹配值的列序號。為一邏輯值，指明函數(shù)VLOOKUP返回時是精確匹配還是近似匹配。如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果為FALSE（0），函數(shù)VLOOKUP將返回精確匹配值。如果找不到，則返回錯誤值#N/A。?（一）應(yīng)發(fā)工資2:VLOOKUP函數(shù)在表格或數(shù)值數(shù)組的首列查18（一）應(yīng)發(fā)工資3:計算應(yīng)發(fā)工資=SUM(E4:I4)?（一）應(yīng)發(fā)工資3:計算應(yīng)發(fā)工資=SUM(E4:I4)?19（二）事假缺勤扣款例:事假一天扣10元，缺勤一天扣30元方法1：=考勤表!E3*10+考勤表!F3*30方法2：=VLOOKUP(A4,考勤表!A3:F15,5)*10+VLOOKUP(A4,考勤表!A3:F15,6)*30?（二）事假缺勤扣款例:事假一天扣10元，缺勤一天扣30元方法20（三）養(yǎng)老保險金1:計算計提數(shù)例:按應(yīng)發(fā)工資的5%計提=ROUND(J4*5%,2)?（三）養(yǎng)老保險金1:計算計提數(shù)例:按應(yīng)發(fā)工資的5%計提=RO21（三）養(yǎng)老保險金2:ROUND函數(shù)按指定的位數(shù)對數(shù)值進行四舍五入ROUND(number，num_digits)數(shù)值位數(shù)例：=ROUND(2.15,1)結(jié)果2.2=ROUND(21.5,-1)結(jié)果20?（三）養(yǎng)老保險金2:ROUND函數(shù)按指定的位數(shù)對數(shù)值進行四舍22（四）個所得稅1:稅率個人所得稅稅率表級數(shù)全月應(yīng)納稅所得額稅率（%）速算扣除數(shù)1不超過500元的502500—2000的部分102532000—5000的部分1512545000—20000的部分20375520000—40000的部分251375640000—60000的部分303375760000—80000的部分356375880000—100000的部分40103759超過100000的部分4515375應(yīng)納個人所得稅稅額=（應(yīng)納稅所得額－扣除標(biāo)準(zhǔn)）*適用稅率-速算扣除數(shù)

扣除標(biāo)準(zhǔn)2000元/月（2008年3月1日起）?（四）個所得稅1:稅率個人所得稅稅率表級數(shù)全月應(yīng)納稅所得額稅23（四）個所得稅2:計算=ROUND(IF((J4-2000)<0,0,IF((J4-2000)<500,(J4-2000)*5%,IF((J4-2000)<2000,(J4-2000)*10%-25,IF((J4-2000)<5000,(J4-2000)*15%-125,(J4-2000)*20%-375)))),2)注:因其應(yīng)發(fā)工資最大不超過20000元，所以只計算到第四級?（四）個所得稅2:計算=ROUND(IF((J4-2000)24（五）實發(fā)工資=J4-SUM(K4:M4)?（五）實發(fā)工資=J4-SUM(K4:M4)?25（六）工資條1：制作第一條記錄=NOW()設(shè)為文本型，輸入“001”。不要采用引用法，否則在第二步復(fù)制時會出錯。=VLOOKUP(B3,工資表!$A$4:$O$16,2，0)采用精確匹配，其他列的數(shù)據(jù)同理。NOW()：返回當(dāng)前日期和時間所對應(yīng)的序列號。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，則結(jié)果將設(shè)為日期格式。?（六）工資條1：制作第一條記錄=NOW()設(shè)為文本型，輸入“26（六）工資條2：復(fù)制其他記錄1、選定A1：P4區(qū)域2、拖動填充柄?（六）工資條2：復(fù)制其他記錄1、選定A1：P4區(qū)域?27四、工資查詢（一）個人工資查詢（二）單條件工資查詢（三）多條件工資查詢?四、工資查詢（一）個人工資查詢?28（一）個人工資查詢1：記錄單法①：選定區(qū)域②：單擊菜單欄”數(shù)據(jù)“——”記錄單“③：在對話框中單擊”條件“按鈕④：輸入查詢的信息，按回車鍵?（一）個人工資查詢1：記錄單法①：選定區(qū)域②：單擊菜單欄”數(shù)29（一）個人工資查詢2：VLOOKUP法設(shè)為文本型=VLOOKUP($B$2,職工基本情況表!$A$3:$K$15,2,0)=VLOOKUP($B$2,工資表!$A$4:$N$16,5,0)?（一）個人工資查詢2：VLOOKUP法設(shè)為文本型=VLOOK30（二）單條件工資查詢：篩選法①：選定區(qū)域②：單擊菜單欄”數(shù)據(jù)“——”篩選“——“自動篩選”③：在下拉框中選擇查詢的條件例：查詢生產(chǎn)部員工的工資?（二）單條件工資查詢：篩選法①：選定區(qū)域②：單擊菜單欄”數(shù)據(jù)31（三）多條件工資查詢：自定義篩選法設(shè)置條件例1：查詢生產(chǎn)部和財務(wù)部全體員工的工資例2：查詢銷售部實發(fā)工資為5000元的員工?（三）多條件工資查詢：自定義篩選法設(shè)置條件例1：查詢生產(chǎn)部和32五、工資匯總分析（一）透視表法（二）計算法?五、工資匯總分析（一）透視表法?33（一）透視表法1:打開數(shù)據(jù)透視表向?qū)?（一）透視表法1:打開數(shù)據(jù)透視表向?qū)?34（一）透視表法2:選定類型?（一）透視表法2:選定類型?35（一）透視表法3:選擇區(qū)域?（一）透視表法3:選擇區(qū)域?36（一）透視表法4:保存位置和布局?（一）透視表法4:保存位置和布局?37（一）透視表法5:布局設(shè)置?（一）透視表法5:布局設(shè)置?38（一）透視表法6:透視表?（一）透視表法6:透視表?39（一）透視表法7:透視圖?（一）透視表法7:透視圖?40（二）計算法1:建立表頭?（二）計算法1:建立表頭?41（二）計算法2:進行計算=SUMIF(工資表!$D$4:$D$16,"=管理人員",工資表!E4:E16)=SUMIF(工資表!$D$4:$D$16,"=工人",工資表!E4:E16)=SUM(B3:B4)?（二）計算法2:進行計算=SUMIF(工資表!$D$4:$D42每一次的加油，每一次的努力都是為了下一次更好的自己。11月-2211月-22Monday,November7,2022天生我材必有用，千金散盡還復(fù)來。00:28:0500:28:0500:2811/7/202212:28:05AM安全象只弓，不拉它就松，要想保安全，常把弓弦繃。11月-2200:28:0500:28Nov-2207-Nov-22得道多助失道寡助，掌控人心方位上。00:28:0500:28:0500:28Monday,November7,2022安全在于心細，事故出在麻痹。11月-2211月-2200:28:0500:28:05November7,2022加強自身建設(shè)，增強個人的休養(yǎng)。2022年11月7日12:28上午11月-2211月-22擴展市場，開發(fā)未來，實現(xiàn)現(xiàn)在。07十一月202212:28:05上午00:28:0511月-22做專業(yè)的企業(yè)，做專業(yè)的事情，讓自己專業(yè)起來。十一月2212:28上午11月-2200:28November7,2022時間是人類發(fā)展的空間。2022/11/70:28:0500:28:0507November2022科學(xué)，你是國力的靈魂；同時又是社會發(fā)展的標(biāo)志。12:28:05上午12:28上午00:28:0511月-22每天都是美好的一天，新的一天開啟。11月-2211月-2200:2800:28:0500:28:05Nov-22人生不是自發(fā)的自我發(fā)展，而是一長串機緣。事件和決定，這些機緣、事件和決定在它們實現(xiàn)的當(dāng)時是取決于我們的意志的。2022/11/70:28:05Monday,November7,2022感情上的親密，發(fā)展友誼；錢財上的親密，破壞友誼。11月-222022/11/70:28:0511月-22謝謝大家！每一次的加油，每一次的努力都是為了下一次更好的自己。11月-43第三章Excel與工資管理一、工資管理流程二、工資初始數(shù)據(jù)設(shè)置三、工資表四、工資查詢五、工資匯總分析?第三章Excel與工資管理一、工資管理流程?44一、工資管理流程員工管理原始數(shù)據(jù)工資計算工資查詢工資匯總職工基本情況表基本工資表、崗位工資表、考勤表、計件表等工資表個人查詢表、部門查詢表工資匯總表?一、工資管理流程員工管理原始數(shù)據(jù)工資計算工資查詢工資匯總職工45二、工資初始數(shù)據(jù)設(shè)置?二、工資初始數(shù)據(jù)設(shè)置?46（一）職工基本情況表1:有效性設(shè)置對“所屬部門”、“職工類別”、“職務(wù)”、“性別”列進行有效性設(shè)置?（一）職工基本情況表1:有效性設(shè)置對“所屬部門”、“職工類別47（一）職工基本情況表2:年齡計算1、輸入公式：=YEAR(TODAY())-YEAR(H3)2、設(shè)置“年齡”列格式為文本、數(shù)值、常規(guī)中的任一種3、設(shè)置“出生日期”列格式為日期型?（一）職工基本情況表2:年齡計算1、輸入公式：?48（一）職工基本情況表3:YEAR函數(shù)返回某日期對應(yīng)的年份。返回值為1900到9999之間的整數(shù)。YEAR(serial_number)日期值例：=YEAR(2008-7-5)結(jié)果：2008=YEAR(10-7-5)結(jié)果：2010?（一）職工基本情況表3:YEAR函數(shù)返回某日期對應(yīng)的年份。返49（二）基本工資表1:相對引用法1、利用單元格兩表相對引用法輸入編號、姓名、所屬部門、職工類別、職務(wù)的數(shù)據(jù)：=職工基本情況表!A32、當(dāng)職工基本情況表的數(shù)據(jù)變化時，基本工資表的對應(yīng)數(shù)據(jù)自動改變?（二）基本工資表1:相對引用法1、利用單元格兩表相對引用法輸50（二）基本工資表2:直接輸入法例：以職務(wù)確定基本工資總經(jīng)理5000副總經(jīng)理4000財務(wù)主管3500總賬會計、辦公室主、銷售部經(jīng)理3000出納、職員2000司機1500?（二）基本工資表2:直接輸入法例：以職務(wù)確定基本工資?51（二）基本工資表3:公式法=IF(E3="總經(jīng)理",5000,IF(E3="副總經(jīng)理",4000,IF(E3="財務(wù)主管",3500,IF(OR(E3="總賬會計",E3="辦公室主任",E3="銷售部經(jīng)理"),3000,IF(OR(E3="出納",E3="職員"),2000,1500)))))?（二）基本工資表3:公式法=IF(E3="總經(jīng)理",500052（二）基本工資表4:邏輯函數(shù)AND(條件1，條件2，...)：所有條件同時滿足即為真，否則為假OR(條件1，條件2，...)：滿足任一條件即為真，否則為假NOT(條件)：當(dāng)條件為真時，返回假注：OR、AND函數(shù)所帶條件不超過30個?（二）基本工資表4:邏輯函數(shù)AND(條件1，條件2，...)53（三）崗位工資表例:按職工類別確定管理人員1000工人:500=IF(D3="管理人員",1000,500)?（三）崗位工資表例:按職工類別確定=IF(D3="管理人員"54（四）考勤表?（四）考勤表?55（五）計件表=D7*E7?（五）計件表=D7*E7?56（六）補貼表例:按部門不同以基本工資為基數(shù)的比例辦公室10%財務(wù)部10%銷售部15%生產(chǎn)部20%=IF(C3=“辦公室”,基本工資表!F3*10%,IF(C3="財務(wù)部",基本工資表!F3*10%,IF(C3="銷售部",基本工資表!F3*15%,基本工資表!F3*20%)))?（六）補貼表例:按部門不同以基本工資為基數(shù)的比例=IF(C357（七）獎金表例:按所屬部門確定辦公室500財務(wù)部600銷售部800生產(chǎn)部1000=IF(C3="辦公室",500,IF(C3="財務(wù)部",600,IF(C3="銷售部",800,1000)))?（七）獎金表例:按所屬部門確定=IF(C3="辦公室",5058三、工資表?三、工資表?59（一）應(yīng)發(fā)工資1:輸入基本工資數(shù)據(jù)崗位工資、獎金、補貼、計件工資同理操作=VLOOKUP(A4,基本工資表!$A$3:$F$15,6)?（一）應(yīng)發(fā)工資1:輸入基本工資數(shù)據(jù)崗位工資、獎金、補貼、計件60（一）應(yīng)發(fā)工資2:VLOOKUP函數(shù)在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)需要在數(shù)組第一列中查找的數(shù)值需要在其中查找數(shù)據(jù)的數(shù)據(jù)表，可以使用對區(qū)域或區(qū)域名稱的引用。待返回的匹配值的列序號。為一邏輯值，指明函數(shù)VLOOKUP返回時是精確匹配還是近似匹配。如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果為FALSE（0），函數(shù)VLOOKUP將返回精確匹配值。如果找不到，則返回錯誤值#N/A。?（一）應(yīng)發(fā)工資2:VLOOKUP函數(shù)在表格或數(shù)值數(shù)組的首列查61（一）應(yīng)發(fā)工資3:計算應(yīng)發(fā)工資=SUM(E4:I4)?（一）應(yīng)發(fā)工資3:計算應(yīng)發(fā)工資=SUM(E4:I4)?62（二）事假缺勤扣款例:事假一天扣10元，缺勤一天扣30元方法1：=考勤表!E3*10+考勤表!F3*30方法2：=VLOOKUP(A4,考勤表!A3:F15,5)*10+VLOOKUP(A4,考勤表!A3:F15,6)*30?（二）事假缺勤扣款例:事假一天扣10元，缺勤一天扣30元方法63（三）養(yǎng)老保險金1:計算計提數(shù)例:按應(yīng)發(fā)工資的5%計提=ROUND(J4*5%,2)?（三）養(yǎng)老保險金1:計算計提數(shù)例:按應(yīng)發(fā)工資的5%計提=RO64（三）養(yǎng)老保險金2:ROUND函數(shù)按指定的位數(shù)對數(shù)值進行四舍五入ROUND(number，num_digits)數(shù)值位數(shù)例：=ROUND(2.15,1)結(jié)果2.2=ROUND(21.5,-1)結(jié)果20?（三）養(yǎng)老保險金2:ROUND函數(shù)按指定的位數(shù)對數(shù)值進行四舍65（四）個所得稅1:稅率個人所得稅稅率表級數(shù)全月應(yīng)納稅所得額稅率（%）速算扣除數(shù)1不超過500元的502500—2000的部分102532000—5000的部分1512545000—20000的部分20375520000—40000的部分251375640000—60000的部分303375760000—80000的部分356375880000—100000的部分40103759超過100000的部分4515375應(yīng)納個人所得稅稅額=（應(yīng)納稅所得額－扣除標(biāo)準(zhǔn)）*適用稅率-速算扣除數(shù)

扣除標(biāo)準(zhǔn)2000元/月（2008年3月1日起）?（四）個所得稅1:稅率個人所得稅稅率表級數(shù)全月應(yīng)納稅所得額稅66（四）個所得稅2:計算=ROUND(IF((J4-2000)<0,0,IF((J4-2000)<500,(J4-2000)*5%,IF((J4-2000)<2000,(J4-2000)*10%-25,IF((J4-2000)<5000,(J4-2000)*15%-125,(J4-2000)*20%-375)))),2)注:因其應(yīng)發(fā)工資最大不超過20000元，所以只計算到第四級?（四）個所得稅2:計算=ROUND(IF((J4-2000)67（五）實發(fā)工資=J4-SUM(K4:M4)?（五）實發(fā)工資=J4-SUM(K4:M4)?68（六）工資條1：制作第一條記錄=NOW()設(shè)為文本型，輸入“001”。不要采用引用法，否則在第二步復(fù)制時會出錯。=VLOOKUP(B3,工資表!$A$4:$O$16,2，0)采用精確匹配，其他列的數(shù)據(jù)同理。NOW()：返回當(dāng)前日期和時間所對應(yīng)的序列號。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，則結(jié)果將設(shè)為日期格式。?（六）工資條1：制作第一條記錄=NOW()設(shè)為文本型，輸入“69（六）工資條2：復(fù)制其他記錄1、選定A1：P4區(qū)域2、拖動填充柄?（六）工資條2：復(fù)制其他記錄1、選定A1：P4區(qū)域?70四、工資查詢（一）個人工資查詢（二）單條件工資查詢（三）多條件工資查詢?四、工資查詢（一）個人工資查詢?71（一）個人工資查詢1：記錄單法①：選定區(qū)域②：單擊菜單欄”數(shù)據(jù)“——”記錄單“③：在對話框中單擊”條件“按鈕④：輸入查詢的信息，按回車鍵?（一）個人工資查詢1：記錄單法①：選定區(qū)域②：單擊菜單欄”數(shù)72（一）個人工資查詢2：VLOOKUP法設(shè)為文本型=VLOOKUP($B$2,職工基本情況表!$A$3:$K$15,2,0)=VLOOKUP($B$2,工資表!$A$4:$N$16,5,0)?（一）個人工資查詢2：VLOOKUP法設(shè)為文本型=VLOOK73（二）單條件工資查詢：篩選法①：選定區(qū)域②：單擊菜單欄”數(shù)據(jù)“——”篩選“——“自動篩選”③：在下拉框中選擇查詢的條件例：查詢生產(chǎn)部員工的工資?（二）單條件工資查詢：篩選法①：選定區(qū)域②：單擊菜單欄”數(shù)據(jù)74（三）多條件工資查詢：自定義篩選法設(shè)置條件例1：查詢生產(chǎn)部和財務(wù)部全體員工的工資例2：查詢銷售部實發(fā)工資為5000元的員工?（三）多條件工資查詢：自定義篩選法設(shè)置條件例1：查詢生產(chǎn)部和75五、工資匯總分析（一）透視表法（二）計算法?五、工資匯總分析（一）透視表法?76（一）透視表法1:打開數(shù)據(jù)透視表向?qū)?（一）透視表法1:打開數(shù)據(jù)透視表向?qū)?77（一）透視表法2:選定類型?（一）透視表法2:選定類型?78（一）透視表法3:選擇區(qū)域?（一）透視表法3:選擇區(qū)域?79（一）透視表法4:保存位置和布局?（一）透視表法4:保存位置和布局?80（一）透視表法5:布局設(shè)置?（一）透視表法5:布局設(shè)置?81（一）透視表法6:透視表?