Excel在財(cái)務(wù)中的應(yīng)用課件：應(yīng)用Excel核算和管理工資

Excel

在財(cái)務(wù)中的應(yīng)用

任務(wù)5應(yīng)用Excel核算和管理工資子任務(wù)5.1建立員工工資表子任務(wù)5.2計(jì)算員工工資項(xiàng)目子任務(wù)5.3查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.4打印員工工資發(fā)放條任務(wù)5重點(diǎn)和難點(diǎn)1.員工工資表的制作;2.工資項(xiàng)目的計(jì)算;3.工資數(shù)據(jù)的查詢與統(tǒng)計(jì)。應(yīng)用Excel核算和管理工資學(xué)習(xí)目標(biāo)【知識(shí)目標(biāo)】1.了解員工工資核算標(biāo)準(zhǔn);2.掌握員工工資表的建立過(guò)程;3.掌握員工工資表各工資項(xiàng)目的計(jì)算?！炯寄苣繕?biāo)】1.能夠制作員工工資表;2.能夠計(jì)算員工工資表各工資項(xiàng)目;3.能夠查詢、匯總分析工資數(shù)據(jù);4.能夠打印工資發(fā)放條。應(yīng)用Excel核算和管理工資任務(wù)5任務(wù)概述工資是企業(yè)在一定時(shí)間內(nèi)直接支付給本單位員工的勞動(dòng)報(bào)酬,也是企業(yè)進(jìn)行各種費(fèi)用計(jì)提的基礎(chǔ)。工資管理是企業(yè)管理的重要組成部分,是財(cái)務(wù)部門最基本的業(yè)務(wù)之一,不僅關(guān)系到每個(gè)員工的切身利益,也是直接影響產(chǎn)品成本核算的重要因素。手工進(jìn)行工資核算,需要占用財(cái)務(wù)人員大量的精力和時(shí)間,并且容易出錯(cuò),采用計(jì)算機(jī)進(jìn)行工資核算可以有效提高準(zhǔn)確性和工作效率。通過(guò)本任務(wù)的學(xué)習(xí),學(xué)生可掌握工資表的建立以及工資項(xiàng)目的計(jì)算,了解Excel在工資核算中的處理流程。應(yīng)用Excel核算和管理工資任務(wù)5任務(wù)要求1.掌握員工工資表的格式;2.能夠制作員工工資表。建立員工工資表子任務(wù)5.1任務(wù)資料大連百大公司是一家小型工業(yè)企業(yè),主要有管理部、生產(chǎn)部和銷售部三個(gè)部門。該公司主要有管理人員、生產(chǎn)工人、銷售人員三種員工類別。每個(gè)員工的工資項(xiàng)目有基本工資、崗位工資、住房補(bǔ)貼、獎(jiǎng)金、事假扣款、病假扣款、養(yǎng)老保險(xiǎn)扣款和醫(yī)療保險(xiǎn)扣款等。除基本工資因人而異外(要求必須輸入),其他工資項(xiàng)目將根據(jù)員工類別和部門確定,而且隨時(shí)間的變化而變化。2019年12月員工基本工資情況和出勤情況如表5-1所示。建立員工工資表子任務(wù)5.1員工編號(hào)姓名部門性別員工類別基本工資(元)事假天數(shù)病假天數(shù)1001周星星管理部男管理人員4400

1002張子儀管理部女管理人員45003

1003李明管理部男管理人員3800

31004龔麗管理部女管理人員3300

1005王亞管理部女管理人員4300

1006劉軍管理部男管理人員5300

2001祝鈞管理部男管理人員3500

2002楊達(dá)生產(chǎn)部男生產(chǎn)工人2900

2003牛良生產(chǎn)部男生產(chǎn)工人290016

2004馬濤生產(chǎn)部男生產(chǎn)工人2900

2005侯清生產(chǎn)部女生產(chǎn)工人2900

3001呂丹銷售部女銷售人員2900

3002趙本善銷售部男銷售人員4500

153003范偉銷售部男銷售人員2900

3004程龍銷售部男銷售人員3500

3005張東建銷售部男銷售人員4500

任務(wù)資料3.設(shè)置員工工資數(shù)據(jù)。將F:J列、L列、N:V列的數(shù)字格式設(shè)置為“會(huì)計(jì)專用”,小數(shù)位2位,貨幣符號(hào)為“無(wú)”,并進(jìn)行適當(dāng)調(diào)整和修飾。4.在“大連百大公司工資核算”工作簿插入一個(gè)新的工作表,并命名為“銷售總額表”。5.按如圖5-2所示格式建立“銷售總額表”工作表,并錄入員工銷售額信息。6.對(duì)“銷售總額表”工作表進(jìn)行格式修飾,并將F列的數(shù)字格式設(shè)置為“會(huì)計(jì)專用”,小數(shù)位2位,貨幣符號(hào)為“無(wú)”。建立員工工資表子任務(wù)5.1圖5-2任務(wù)資料1.新建一個(gè)文件名為“大連百大公司工資核算”的工作簿,將Sheet1工作表重命名為“員工工資表”。步驟1打開Excel電子表格軟件,新建一個(gè)工作簿,另存為“大連百大公司工資核算.xlsx”。步驟2選擇Sheet1工作表,雙擊標(biāo)簽名,背景反白,輸入工作表名稱“員工工資表”,按Enter鍵確認(rèn)。2.建立如圖5-1所示的員工工資表,并錄入員工基本工資數(shù)據(jù)。步驟1打開“大連百大公司工資核算”工作簿,選擇“員工工資表”工作表。步驟2在“員工工資表”工作表中,按圖5-1所示依次錄入員工編號(hào)、姓名、部門、性別、員工類別、基本工資、崗位工資、住房補(bǔ)貼、獎(jiǎng)金、應(yīng)發(fā)合計(jì)、事假天數(shù)、事假扣款、病假天數(shù)、病假扣款、其他扣款、扣款合計(jì)、養(yǎng)老保險(xiǎn)、醫(yī)療保險(xiǎn)、應(yīng)扣社保合計(jì)、應(yīng)發(fā)工資、代扣稅以及實(shí)發(fā)合計(jì)。步驟3在“員工工資表”工作表中,按圖5-1所示依次錄入員工基本工資信息。操作過(guò)程略。建立員工工資表子任務(wù)5.1任務(wù)資料3.設(shè)置員工工資數(shù)據(jù)。將F:J列、L列、N:V列的數(shù)字格式設(shè)置為“會(huì)計(jì)專用”,小數(shù)位2位,貨幣符號(hào)為“無(wú)”,并進(jìn)行適當(dāng)調(diào)整和修飾。步驟1

在“員工工資表”工作表中,依次選擇F:J列、L列、N:V列,右擊鼠標(biāo),在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令,打開“設(shè)置單元格格式”對(duì)話框,在對(duì)話框中選擇“數(shù)字”選項(xiàng)卡,在下方的分類中選擇“會(huì)計(jì)專用”,設(shè)置小數(shù)位數(shù)為“2”,貨幣符號(hào)為“無(wú)”,單擊“確定”按鈕。步驟2

在“員工工資表”工作表中,設(shè)置字體、字形、字號(hào)、行高、列寬以及邊框等,對(duì)工作表進(jìn)行格式調(diào)整和修飾(操作過(guò)程略)。4.在“大連百大公司工資核算”工作簿插入一個(gè)新的工作表,并命名為“銷售總額表”。打開“大連百大公司工資核算”工作簿,選擇“開始—單元格”命令組,再選擇“插入”下拉列表中的“插入工作表”命令,系統(tǒng)自動(dòng)在當(dāng)前工作表前插入一個(gè)空白工作表。雙擊新插入工作表標(biāo)簽名,背景反白,輸入“銷售總額表”,按Enter鍵完成。5.按如圖5-2所示格式建立“銷售總額表”工作表,并錄入員工銷售額信息。在“銷售總額表”工作表中,按如圖5-2所示格式錄入相關(guān)信息(操作過(guò)程略)。建立員工工資表子任務(wù)5.1任務(wù)資料6.對(duì)“銷售總額表”工作表進(jìn)行格式修飾,并將F列的數(shù)字格式設(shè)置為“會(huì)計(jì)專用”,小數(shù)位2位,貨幣符號(hào)為“無(wú)”。步驟1打開“銷售總額表”工作表,設(shè)置字體、字形、字號(hào)、行高、列寬以及邊框等,對(duì)工作表進(jìn)行格式調(diào)整和修飾(操作過(guò)程略)。步驟2在“銷售總額表”工作表中,選定F列,右擊鼠標(biāo),在快捷菜單中選擇“設(shè)置單元格格式”命令,在對(duì)話框中選擇“數(shù)字”選項(xiàng)卡,在分類列表框中選擇“會(huì)計(jì)專用”,并設(shè)置小數(shù)位數(shù)為“2”,貨幣符號(hào)為“無(wú)”。建立員工工資表子任務(wù)5.1任務(wù)要求1.掌握用函數(shù)計(jì)算工資項(xiàng)目的方法;2.能夠計(jì)算員工工資項(xiàng)目。計(jì)算員工工資項(xiàng)目子任務(wù)5.2任務(wù)要求在大連百大公司員工工資數(shù)據(jù)表中,除員工編號(hào)、姓名、部門、性別、員工類別、基本工資、病假天數(shù)、事假天數(shù)項(xiàng)目外,其他項(xiàng)目按如下標(biāo)準(zhǔn)發(fā)放:(1)崗位工資:根據(jù)員工類別發(fā)放,管理人員為4000元,生產(chǎn)工人為3500元,銷售人員為4500元。(2)住房補(bǔ)貼:根據(jù)員工類別發(fā)放,管理人員為1000元,生產(chǎn)工人為800元,銷售人員為900元。(3)獎(jiǎng)金:根據(jù)部門的效益確定,本月管理部獎(jiǎng)金為2000元,生產(chǎn)部獎(jiǎng)金為2500元,銷售部獎(jiǎng)金與個(gè)人銷售額有關(guān),完成基本銷售額30萬(wàn)元的,獎(jiǎng)金為500元,超額完成的,按超出金額的1%提成,未完成基本銷售額的,沒(méi)有獎(jiǎng)金。(4)事假扣款:規(guī)定如果事假少于14天,將應(yīng)發(fā)工資平均到每天(每月按22天計(jì)算),按天扣款;如果事假多于14天,扣除應(yīng)發(fā)工資的80%。(5)病假扣款:規(guī)定如果病假少于14天,工人扣款500元,非工人扣款800元;如果病假多于14天,工人扣款800元,非工人扣款1200元。(6)養(yǎng)老保險(xiǎn)扣款:按基本工資與崗位工資之和的8%扣除。(7)醫(yī)療保險(xiǎn)扣款:按基本工資與崗位工資之和的2%扣除。(8)個(gè)人所得稅:依據(jù)個(gè)人所得稅稅率表(見表5-3)進(jìn)行扣除。計(jì)算員工工資項(xiàng)目子任務(wù)5.2任務(wù)要求計(jì)算員工工資項(xiàng)目子任務(wù)5.2級(jí)數(shù)月度應(yīng)納稅所得額稅率(%)速算扣除數(shù)1不超過(guò)3000元的302超過(guò)3000元至12000元的部分102103超過(guò)12000元至25000元的部分2014104超過(guò)25000元至35000元的部分2526605超過(guò)35000元至55000元的部分3044106超過(guò)55000元至80000元的部分3571607超過(guò)80000元的部分4515160任務(wù)要求根據(jù)上述員工工資項(xiàng)目發(fā)放標(biāo)準(zhǔn),完成如下工資項(xiàng)目的計(jì)算:1.根據(jù)員工類別的不同,設(shè)置“崗位工資”項(xiàng)目的計(jì)算公式。2.根據(jù)員工類別的不同,設(shè)置“住房補(bǔ)貼”項(xiàng)目的計(jì)算公式。3.根據(jù)各部門的效益,設(shè)置“獎(jiǎng)金”項(xiàng)目的計(jì)算公式。4.設(shè)置“應(yīng)發(fā)合計(jì)”項(xiàng)目的計(jì)算公式。5.根據(jù)事假天數(shù),設(shè)置“事假扣款”項(xiàng)目的計(jì)算公式。6.根據(jù)病假天數(shù),設(shè)置“病假扣款”項(xiàng)目的計(jì)算公式。7.設(shè)置“扣款合計(jì)”項(xiàng)目的計(jì)算公式。8.根據(jù)基本工資和崗位工資之和的百分比計(jì)算扣除“養(yǎng)老保險(xiǎn)”“醫(yī)療保險(xiǎn)”項(xiàng)目,并設(shè)置“應(yīng)扣社保合計(jì)”項(xiàng)目的計(jì)算公式。9.設(shè)置“應(yīng)發(fā)工資”項(xiàng)目的計(jì)算公式。10.根據(jù)應(yīng)發(fā)工資的數(shù)額設(shè)置“代扣稅”項(xiàng)目的計(jì)算公式,并設(shè)置“實(shí)發(fā)合計(jì)”項(xiàng)目的計(jì)算公式。計(jì)算員工工資項(xiàng)目子任務(wù)5.2任務(wù)實(shí)施1.根據(jù)員工類別的不同,設(shè)置“崗位工資”項(xiàng)目的計(jì)算公式。步驟1打開“員工工資表”工作表,選定G2單元格。步驟2在G2單元格輸入計(jì)算“崗位工資”項(xiàng)目的計(jì)算公式“=IF(E2="管理人員",4000,IF(E2="生產(chǎn)工人",3500,4500))”,按Enter鍵確定公式輸入。選擇G2單元格,在編輯欄中顯示公式,在G2單元格顯示計(jì)算結(jié)果,如圖5-3所示。

【小提示】G2單元格計(jì)算公式“=IF(E2="管理人員",4000,IF(E2="生產(chǎn)工人",3500,4500))”的意義:先判斷E2單元格的值是否為“管理人員”,如果為管理人員,則崗位工資為4000元,否則,判斷G2單元格的值是否為“生產(chǎn)工人”,如果為生產(chǎn)工人,則崗位工資為3500元,否則為銷售人員,崗位工資為4500元。計(jì)算員工工資項(xiàng)目子任務(wù)5.2圖5-3任務(wù)實(shí)施步驟3選定G2單元格,鼠標(biāo)指向G2單元格右下角的填充柄,按下鼠標(biāo)左鍵向下拖動(dòng)至G17單元格,結(jié)果如圖5-4所示。2.根據(jù)員工類別的不同,設(shè)置“住房補(bǔ)貼”項(xiàng)目的計(jì)算公式。步驟1

打開“員工工資表”工作表,選定H2單元格。步驟2

在H2單元格,輸入“住房補(bǔ)貼”項(xiàng)目的計(jì)算公式“=IF(E2="管理人員",1000,IF(E2="生產(chǎn)工人",800,900))”,按Enter鍵確定公式輸入。選擇H2單元格,在編輯欄中顯示公式,在H2單元格顯示計(jì)算結(jié)果,如圖5-5所示。計(jì)算員工工資項(xiàng)目子任務(wù)5.2圖5-4圖5-5任務(wù)實(shí)施步驟3選定H2單元格,鼠標(biāo)指向H2單元格右下角的填充柄,按下鼠標(biāo)左鍵向下拖動(dòng)至H17單元格,結(jié)果如圖5-6所示。3.根據(jù)各部門的效益,設(shè)置“獎(jiǎng)金”項(xiàng)目的計(jì)算公式。步驟1設(shè)置管理部和生產(chǎn)部的獎(jiǎng)金。打開“員工工資表”工作表,選定I2單元格。步驟2在I2單元格輸入“獎(jiǎng)金”項(xiàng)目的公式“=IF(C2="管理部",2000,IF(C2="生產(chǎn)部",2500,"銷售部"))”,按Enter鍵確定輸入。步驟3選定I2單元格,鼠標(biāo)指向I2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動(dòng)至I17單元格,結(jié)果如圖5-7所示?！拘√崾尽吭凇皢T工工資表”工作表的I13:I17單元格顯示“銷售部”,表示這些人員為銷售人員,銷售人員的獎(jiǎng)金根據(jù)本月銷售額計(jì)算。計(jì)算員工工資項(xiàng)目子任務(wù)5.2圖5-6圖5-7任務(wù)實(shí)施步驟4

選定I13單元格,輸入銷售人員“獎(jiǎng)金”項(xiàng)目的計(jì)算公式“=IF(AND(C13="銷售部",銷售總額表!F2>=30),2000+100*(銷售總額表!F2-30),0)”,按Enter鍵確定輸入,在I13單元格顯示計(jì)算結(jié)果,在編輯欄中顯示I13單元格的計(jì)算公式,如圖5-8所示?！拘√崾尽縄13單元格計(jì)算公式“=IF(AND(C13="銷售部",銷售總額表!F2>=30),2000+100*(銷售總額表!F2-30),0)”,其中AND是邏輯函數(shù),用于判斷當(dāng)“C13="銷售部"”和“銷售總額表!F2>=30”兩個(gè)條件均為真值時(shí),IF函數(shù)返回“2000+超額30萬(wàn)元后計(jì)算的獎(jiǎng)金額”,“100”(10000×0.01)表示萬(wàn)元轉(zhuǎn)為元乘以提成比率。步驟5

選定I13單元格,鼠標(biāo)指向I13單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動(dòng)至I17單元格,結(jié)果如圖5-9所示。計(jì)算員工工資項(xiàng)目子任務(wù)5.2圖5-8圖5-9任務(wù)實(shí)施4.設(shè)置“應(yīng)發(fā)合計(jì)”項(xiàng)目的計(jì)算公式?！皯?yīng)發(fā)合計(jì)”項(xiàng)目金額為基本工資、崗位工資、住房補(bǔ)貼和獎(jiǎng)金的合計(jì)數(shù)。步驟1

打開“員工工資表”工作表,選定J2單元格。步驟2

在J2單元格,輸入“應(yīng)發(fā)合計(jì)”項(xiàng)目的計(jì)算公式“=SUM(F2:I2)”,按Enter鍵確認(rèn)輸入。步驟3

選定J2單元格,鼠標(biāo)指向J2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動(dòng)至J17單元格,結(jié)果如圖5-10所示。計(jì)算員工工資項(xiàng)目子任務(wù)5.2圖5-10任務(wù)實(shí)施5.根據(jù)事假天數(shù),設(shè)置“事假扣款”項(xiàng)目的計(jì)算公式。步驟1

打開“員工工資表”工作表,選定L2單元格。步驟2

在L2單元格輸入“事假扣款”項(xiàng)目的計(jì)算公式“=IF(K2>14,J2*0.8,J2/22*K2)”,按Enter鍵確認(rèn)輸入。步驟3選定L2單元格,鼠標(biāo)指向L2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動(dòng)至L17單元格,結(jié)果如圖5-11所示?！拘√崾尽縇2單元格的計(jì)算公式“=IF(K2>14,J2*0.8,J2/22*K2)”,表示使用IF函數(shù)判斷K2單元格,也就是事假天數(shù)是否大于14天,如果為真值,則返回應(yīng)發(fā)合計(jì)(J2單元格)的80%的值,否則,返回應(yīng)發(fā)合計(jì)(J2單元格)的值除以每月工作日數(shù)22再乘以事假天數(shù)的值。計(jì)算員工工資項(xiàng)目子任務(wù)5.2圖5-11任務(wù)實(shí)施6.根據(jù)病假天數(shù),設(shè)置“病假扣款”項(xiàng)目的計(jì)算公式。步驟1打開“員工工資表”工作表,選定N2單元格。步驟2

在N2單元格輸入“病假扣款”項(xiàng)目的計(jì)算公式“=IF(M2=0,0,IF(M2<=14,IF(E2="生產(chǎn)工人",500,800),IF(E2="生產(chǎn)工人",800,1200)))”,按Enter鍵確認(rèn)輸入。步驟3

選定N2單元格,鼠標(biāo)指向N2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動(dòng)至N17單元格,結(jié)果如圖5-12所示。計(jì)算員工工資項(xiàng)目子任務(wù)5.2圖5-12任務(wù)實(shí)施7.設(shè)置“扣款合計(jì)”項(xiàng)目的計(jì)算公式。“扣款合計(jì)”項(xiàng)目金額為事假扣款、病假扣款、其他扣款的合計(jì)數(shù)。其中,其他扣款由用戶手工輸入。步驟1

打開“員工工資表”工作表,選定P2單元格。步驟2

在P2單元格輸入“扣款合計(jì)”項(xiàng)目的計(jì)算公式“=SUM(L2,N2,O2)”,按Enter鍵確認(rèn)輸入。步驟3

選定P2單元格,鼠標(biāo)指向P2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動(dòng)至P17單元格,結(jié)果如圖5-13所示。計(jì)算員工工資項(xiàng)目子任務(wù)5.2圖5-13任務(wù)實(shí)施8.根據(jù)基本工資和崗位工資之和的百分比計(jì)算扣除“養(yǎng)老保險(xiǎn)”“醫(yī)療保險(xiǎn)”項(xiàng)目,并設(shè)置“應(yīng)扣社保合計(jì)”項(xiàng)目的計(jì)算公式。步驟1

打開“員工工資表”工作表,選定Q2單元格。步驟2

設(shè)置“養(yǎng)老保險(xiǎn)”項(xiàng)目計(jì)算公式。在Q2單元格輸入“養(yǎng)老保險(xiǎn)”項(xiàng)目的計(jì)算公式“=(F2+G2)*0.08”,按Enter鍵確認(rèn)輸入。

【小提示】Q2單元格計(jì)算公式“=(F2+G2)*0.08”,表示“養(yǎng)老保險(xiǎn)”項(xiàng)目按基本工資與崗位工資合計(jì)數(shù)的8%提取。步驟3

選定Q2單元格,鼠標(biāo)指向Q2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動(dòng)至Q17單元格。步驟4

設(shè)置“醫(yī)療保險(xiǎn)”項(xiàng)目計(jì)算公式。選定R2單元格,輸入“醫(yī)療保險(xiǎn)”項(xiàng)目的計(jì)算公式“=(F2+G2)*0.02”,按Enter鍵確認(rèn)輸入。步驟5

選定R2單元格,鼠標(biāo)指向R2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動(dòng)至R17單元格。步驟6

設(shè)置“應(yīng)扣社保合計(jì)”項(xiàng)目計(jì)算公式。選定S2單元格,輸入“應(yīng)扣社保合計(jì)”項(xiàng)目的計(jì)算公式“=Q2+R2”,按Enter鍵確認(rèn)輸入。計(jì)算員工工資項(xiàng)目子任務(wù)5.2任務(wù)實(shí)施步驟7選定S2單元格,鼠標(biāo)指向S2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動(dòng)至S17單元格。“養(yǎng)老保險(xiǎn)”“醫(yī)療保險(xiǎn)”“應(yīng)扣社保合計(jì)”項(xiàng)目的計(jì)算結(jié)果如圖5-14所示。計(jì)算員工工資項(xiàng)目子任務(wù)5.2圖5-14任務(wù)實(shí)施9.設(shè)置“應(yīng)發(fā)工資”項(xiàng)目的計(jì)算公式?！皯?yīng)發(fā)工資”項(xiàng)目金額為應(yīng)發(fā)合計(jì)減去扣款合計(jì)和應(yīng)扣社保合計(jì)的金額。步驟1打開“員工工資表”工作表,選定T2單元格。步驟2

在T2單元格輸入“應(yīng)發(fā)工資”項(xiàng)目的計(jì)算公式“=J2-P2-S2”,按Enter鍵確認(rèn)輸入。步驟3

選定T2單元格,鼠標(biāo)指向T2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動(dòng)至T17單元格,結(jié)果如圖5-15所示。計(jì)算員工工資項(xiàng)目子任務(wù)5.2圖5-15任務(wù)實(shí)施10.根據(jù)應(yīng)發(fā)工資的金額設(shè)置“代扣稅”項(xiàng)目的計(jì)算公式,并設(shè)置“實(shí)發(fā)合計(jì)”項(xiàng)目的計(jì)算公式。步驟1打開“員工工資表”工作表,選定U2單元格。步驟2在U2單元格輸入“代扣稅”項(xiàng)目的計(jì)算公式“=IF(U2-5000<=0,0,IF(U2-5000<=3000,(U2-5000)*0.03,IF(U2-5000<=12000,(U2-5000)*0.1-210,IF(U2-5000<=25000,(U2-5000)*0.2-1410,IF(U2-5000<=35000,(U2-5000)*0.25-2660,IF(U2-5000<=55000,(U2-5000)*0.3-4410,IF(U2-5000<=80000,(U2-5000)*0.35-7160,IF(U2-5000>85000,(U2-5000)*0.45-15160,"復(fù)核應(yīng)發(fā)工資"))))))))”,按Enter鍵確認(rèn)輸入。步驟3選定U2單元格,鼠標(biāo)指向U2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動(dòng)至U17單元格,結(jié)果如圖5-16所示。步驟4選定V2單元格,輸入“實(shí)發(fā)工資”項(xiàng)目的計(jì)算公式“=T2-U2”,按Enter鍵確認(rèn)輸入。步驟5

選定V2單元格,鼠標(biāo)指向V2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動(dòng)至V17單元格,結(jié)果如圖5-17所示。計(jì)算員工工資項(xiàng)目子任務(wù)5.2任務(wù)實(shí)施計(jì)算員工工資項(xiàng)目子任務(wù)5.2圖5-16圖5-17任務(wù)要求1.能夠使用篩選功能對(duì)員工工資進(jìn)行查詢;2.能夠使用VLOOKUP函數(shù)對(duì)員工工資進(jìn)行查詢;2.能夠使用數(shù)據(jù)透視表對(duì)員工工資進(jìn)行統(tǒng)計(jì)分析;查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3任務(wù)資料根據(jù)子任務(wù)5.1建立的員工工資數(shù)據(jù)清單,按下列要求完成員工工資的查詢與統(tǒng)計(jì)分析:1.在“員工工資表”工作表中,利用篩選功能查詢李明的工資信息。2.在“員工工資表”工作表中,利用篩選功能查詢銷售部的員工工資信息。3.在“員工工資表”工作表中,利用篩選功能查詢銷售人員中基本工資高于3000元的員工工資信息。4.在“員工工資表”工作表中,利用篩選功能查詢本月請(qǐng)假的員工工資信息,包括事假和病假兩種情況。5.使用數(shù)據(jù)透視表和數(shù)據(jù)透視圖功能統(tǒng)計(jì)各部門實(shí)發(fā)工資匯總數(shù)據(jù)。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3任務(wù)資料6.在“大連百大公司工資核算”工作簿中新建一個(gè)空白工作表,將工作表重命名“工資查詢”。在新建的“工資查詢”工作表中按如圖5-18所示建立工資查詢表,要求在姓名下方的A2單元格輸入員工姓名后,在對(duì)應(yīng)的其他單元格顯示該員工的工資項(xiàng)目數(shù)據(jù),如輸入“李明”,結(jié)果如圖5-19所示。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-19圖5-18任務(wù)實(shí)施1.在“員工工資表”工作表中,利用篩選功能查詢李明的工資信息。步驟1打開“大連百大公司工資核算”工作簿,選擇“員工工資表”工作表。步驟2

在“員工工資表”工作表中,選定員工工資數(shù)據(jù)清單任意單元格,選擇“數(shù)據(jù)—排序和篩選”命令組,單擊“篩選”按鈕。步驟3

單擊“員工工資表”工作表中“姓名”標(biāo)題列右側(cè)下拉列表,取消“全選”,再單擊“李明”,結(jié)果如圖5-20所示?！拘√崾尽坎襟E3也可以使用如下方法操作,單擊“姓名”標(biāo)題列右側(cè)下拉列表中的“文本篩選”下級(jí)菜單中的“等于”命令,如圖5-21所示。在彈出的“自定義自動(dòng)篩選方式”對(duì)話框中,在“姓名”項(xiàng)下選擇“等于”,在文本框中輸入“李明”,如圖5-22所示,單擊“確定”按鈕,工作表顯示如圖5-20所示的查詢結(jié)果。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-20任務(wù)實(shí)施查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-22圖5-21知識(shí)積累2.在“員工工資表”工作表中,利用篩選功能查詢銷售部的員工工資信息。步驟1

打開“大連百大公司工資核算”工作簿,選擇“員工工資表”工作表。步驟2

在“員工工資表”工作表中,選定員工工資數(shù)據(jù)清單任意單元格,選擇“數(shù)據(jù)—排序和篩選”命令組,單擊“篩選”按鈕。步驟3

單擊“員工工資表”工作表中“部門”標(biāo)題列右側(cè)下拉列表,取消“全選”,再單擊“銷售部”,結(jié)果如圖5-23所示。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-23知識(shí)積累3.在“員工工資表”工作表中,利用篩選功能查詢銷售人員中基本工資高于3000元的員工工資信息。步驟1打開“大連百大公司工資核算”工作簿,選擇“員工工資表”工作表。步驟2在“員工工資表”工作表中,選定員工工資數(shù)據(jù)清單任意單元格,選擇“數(shù)據(jù)—排序和篩選”命令組,單擊“篩選”按鈕。步驟3單擊“員工工資表”工作表中“員工類別”標(biāo)題列右側(cè)下拉列表,取消“全選”,再單擊“銷售人員”,結(jié)果如圖5-24所示。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-24知識(shí)積累步驟4

在步驟3查詢結(jié)果的基礎(chǔ)上,單擊“員工工資表”工作表中“基本工資”標(biāo)題列右側(cè)下拉列表中的“數(shù)字篩選”二級(jí)菜單中的“大于”命令,在彈出的“自定義自動(dòng)篩選方式”對(duì)話框中,在基本工資大于文本框中輸入“3000”,如圖5-25所示,單擊“確定”按鈕,顯示如圖5-26所示的工資信息查詢結(jié)果。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-26圖5-25知識(shí)積累4.在“員工工資表”工作表中,利用篩選功能查詢本月請(qǐng)假的員工工資信息,包括事假和病假兩種情況。步驟1

打開“大連百大公司工資核算”工作簿,選擇“員工工資表”工作表。步驟2

在“員工工資表”工作表下方預(yù)留一個(gè)空白行,然后將“員工工資表”工作表的標(biāo)題復(fù)制到第19行,如圖5-27所示。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-27知識(shí)積累步驟3

在K20單元格輸入查詢條件“>0”,表示查詢事假天數(shù)大于0的結(jié)果;在M21單元格輸入查詢條件“>0”,表示查詢病假天數(shù)大于0的結(jié)果,如圖5-28所示?！拘√崾尽咳绻麑⒉樵儣l件放在同一行,即在K20單元格輸入“>0”,在M20單元格輸入“>0”,則表示查詢事假天數(shù)大于0,并且病假天數(shù)也大于0的結(jié)果。如果將查詢條件分別放在不同行,則表示查詢事假天數(shù)大于0,或者病假天數(shù)大于0的結(jié)果,即事假天數(shù)或病假天數(shù)大于0的結(jié)果,也就是本月有事假或病假的員工信息。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-28知識(shí)積累步驟4

選定“員工工資表”數(shù)據(jù)清單的任意一個(gè)單元格,選擇“數(shù)據(jù)—排序和篩選”命令組,單擊“高級(jí)”按鈕,彈出“高級(jí)篩選”對(duì)話框,按如圖5-29所示選擇列表區(qū)域和條件區(qū)域,單擊“確定”按鈕,結(jié)果如圖5-30所示。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-29圖5-30知識(shí)積累5.使用數(shù)據(jù)透視表和數(shù)據(jù)透視圖功能統(tǒng)計(jì)各部門實(shí)發(fā)工資匯總數(shù)據(jù)。步驟1

打開“大連百大公司工資核算”工作簿,選擇“員工工資表”工作表。步驟2

在“員工工資表”工作表中,選定員工工資數(shù)據(jù)清單任意單元格,選擇“插入—表格”命令組,單擊“數(shù)據(jù)透視表”按鈕,彈出“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框,在對(duì)話框中選擇“表/區(qū)域”為“員工工資表!$A$1:$V$17”,選擇放置數(shù)據(jù)透視表的位置為“新工作表”,如圖5-31所示,單擊“確定”按鈕創(chuàng)建一個(gè)新的數(shù)據(jù)透視表工作表,并顯示“數(shù)據(jù)透視表字段”任務(wù)窗格,如圖5-32所示。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-31圖5-32知識(shí)積累步驟3

在“數(shù)據(jù)透視表字段”任務(wù)窗格,將“部門”字段拖動(dòng)至行標(biāo)簽,將實(shí)發(fā)工資拖動(dòng)至值標(biāo)簽。在生成的數(shù)據(jù)透視表任意位置單擊,選擇“數(shù)據(jù)透視表工具—設(shè)計(jì)—布局”命令組,單擊“報(bào)表布局”下拉列表中的“以表格形式顯示”命令,結(jié)果如圖5-33所示。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-33知識(shí)積累步驟4

在生成的數(shù)據(jù)透視表上單擊,選擇“數(shù)據(jù)透視表工具—分析—工具”命令組,單擊“數(shù)據(jù)透視圖”按鈕,彈出“插入圖表”對(duì)話框,如圖5-34所示,單擊“確定”按鈕,系統(tǒng)自動(dòng)根據(jù)數(shù)據(jù)透視表生成數(shù)據(jù)透視圖,如圖5-35所示。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-34圖5-35知識(shí)積累6.在“大連百大公司工資核算”工作簿中新建一個(gè)空白工作表,將工作表重命名“工資查詢”。在新建的“工資查詢”工作表中按如圖5-18所示建立工資查詢表,要求在姓名下方的A2單元格輸入員工姓名后,在對(duì)應(yīng)的其他單元格顯示該員工的工資項(xiàng)目數(shù)據(jù)。步驟1在“大連百大公司工資核算”工作簿中新建一張工作表,并將其重命名為“工資查詢”。步驟2

在“工資查詢”工作表中,按圖5-18所示輸入各個(gè)工資項(xiàng)目。步驟3

在“工資查詢”工作表中,選定B2單元格,輸入查詢A2單元格(姓名)的公式“=IF(ISNA(VLOOKUP($A$2,員工工資表!$B$2:$V$17,2,FALSE)),"",VLOOKUP($A$2,員工工資表!$B$2:$V$17,2,FALSE))”,按Enter鍵確認(rèn)輸入。

【小提示】B2單元格的公式使用VLOOKUP函數(shù)在“員工工資表!$B$2:$V$17”區(qū)域內(nèi)查找$A$2單元格的值,也就是在員工工資表中查詢輸入的姓名。為了防止輸入的姓名不存在或?yàn)榭罩禃r(shí)找不到工資數(shù)據(jù),系統(tǒng)會(huì)提示“#N/A”錯(cuò)誤,在公式中使用IF函數(shù)判斷ISNA返回值是否為“#N/A”錯(cuò)誤,如果為真值,則返回空值,否則,查詢并返回查詢區(qū)域的第2列也就是部門的值。另外,B2單元格公式的輸入也可以使用插入函數(shù),利用“函數(shù)參數(shù)”對(duì)話框進(jìn)行設(shè)置,具體操作過(guò)程略。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3知識(shí)積累步驟4復(fù)制B2單元格的公式,選定C2單元格,點(diǎn)擊鼠標(biāo)右鍵進(jìn)行粘貼。雙擊C2單元格,修改公式,將第2列修改為第3列,也就是查詢到A2單元格姓名,則返回查找區(qū)域第3列的值,即性別列的值。步驟5

重復(fù)步驟4的操作,依次修改單元格D2:K2以及B4:K4單元格公式,注意返回值列(操作過(guò)程略)。步驟6

測(cè)試查詢。在A2單元格輸入姓名“李明”,按Enter鍵確認(rèn)輸入,顯示的查詢結(jié)果如圖5-36所示。查詢與統(tǒng)計(jì)分析員工工資子任務(wù)5.3圖5-36任務(wù)要求1.能夠生成工資發(fā)放條;2.能夠打印工資發(fā)放條。打印員工工資發(fā)放條子任務(wù)5.4任務(wù)資料根據(jù)子任務(wù)5.1建立的員工工資數(shù)據(jù)清單,按下列要求完成員工工資發(fā)放條的打印:1.生成員工工資發(fā)放條,如圖5-37所示。

2.打印工資發(fā)放條。打印員工工資發(fā)放條子任務(wù)5.4圖5-37任務(wù)資料1.生成員工工資發(fā)放條。步驟1

打開“大連百大公司工資核算”工作簿,新增一個(gè)空白工作表,并重命名為“工資發(fā)放條”。步驟2

復(fù)制“員工工資表”工作表的數(shù)據(jù)到“工資發(fā)放條”工作表。步驟3

在“工資發(fā)放條”工作表,選擇A列,右擊鼠標(biāo),在快捷菜單中選擇“插入”命令,在最前面插入一個(gè)空白列,并在A1單元格輸入“月份”,在A2單元格輸入“2019年12月”。步驟4

選定A2單元格,使用快速填充功能將A2單元格的值填充到A3:A17區(qū)域。步驟5

在“工資發(fā)放條”工作表的第3行前插入3個(gè)空白行。復(fù)制員工工資表的標(biāo)題(工作表第1行),將其粘貼到第5行,如圖5-38所示。打印員工工資發(fā)放條子任務(wù)5.4圖5-38任務(wù)資料步驟6

重復(fù)步驟5的操作過(guò)程,進(jìn)行插入行與粘貼標(biāo)題操作,結(jié)果如圖5-39所示。

打印員工工資發(fā)放條子任務(wù)5.4圖5-39任務(wù)資料2.打印工資發(fā)放條。步驟1

打開“大連百大公司工資核算”工作簿,選擇“工資發(fā)放條”工作表。步驟2

選擇要打印的工資發(fā)放條區(qū)域范圍,選擇“文件—打印”命令,打開如圖5-40所示的打印窗口。步驟3

在打印窗口,選擇要使用的打印機(jī),再選擇“設(shè)置”下方的“打印活動(dòng)工作表”下拉框中的“打印選定區(qū)域”,單擊“打印”按鈕即可。打印員工工資發(fā)放條子任務(wù)5.4圖5-40任務(wù)總結(jié)本任務(wù)介紹了如何利用Excel