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

Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

選定T2單元格,鼠標(biāo)指向T2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動至T17單元格,結(jié)果如圖5-15所示。計算員工工資項目子任務(wù)5.2圖5-15任務(wù)實施10.根據(jù)應(yīng)發(fā)工資的金額設(shè)置“代扣稅”項目的計算公式,并設(shè)置“實發(fā)合計”項目的計算公式。步驟1打開“員工工資表”工作表,選定U2單元格。步驟2在U2單元格輸入“代扣稅”項目的計算公式“=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鍵確認輸入。步驟3選定U2單元格,鼠標(biāo)指向U2單元格右下角的填充柄,按下鼠標(biāo)左鍵拖動至U17單元格,結(jié)果如圖5-16所示。步驟4選定V2單元格,輸入“實發(fā)工資”項目的計算公式“=T2-U2”,按Enter鍵確認輸入。步驟5

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

測試查詢。在A2單元格輸入姓名“李明”,按Enter鍵確認輸入,顯示的查詢結(jié)果如圖5-36所示。查詢與統(tǒng)計分析員工工資子任務(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

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

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

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

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

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

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

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

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

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

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