本科Excel在會計和財務(wù)中的應(yīng)用第七章教學(xué)課件

1、YCF正版可修改PPT(本科）Excel在會計和財務(wù)中的應(yīng)用第七章教學(xué)課件第七章 Excel在職工薪酬管理中的應(yīng)用010203目錄04工資總額匯總表的編制職工薪酬管理基礎(chǔ)知識基礎(chǔ)表格的建立工資結(jié)算單的建立與工資條的制作05工資的查詢與統(tǒng)計分析01第一節(jié)職工薪酬管理基礎(chǔ)知識學(xué)習(xí)目標(biāo)010203掌握應(yīng)付職工薪酬的基本內(nèi)容和貨幣性職工薪酬的基本核算方法了解非貨幣性職工薪酬的特點了解五險一金的種類，掌握五險一金的繳納方法和比例1. 短期薪酬短期薪酬，是指企業(yè)在職工提供相關(guān)服務(wù)的年度報告期間結(jié)束后十二個月內(nèi)需要全部予以支付的職工薪酬，因解除與職工的勞動關(guān)系給予的補償除外。短期薪酬具體包括以下內(nèi)容：（1）

2、 職工工資、獎金、津貼和補貼。（2） 職工福利費。（3） 醫(yī)療保險費、工傷保險費和生育保險費等社會保險費。注意，不包括養(yǎng)老保險和失業(yè)保險。（4） 住房公積金。（5） 工會經(jīng)費和職工教育經(jīng)費。（6） 短期帶薪缺勤。（7） 短期利潤分享計劃。（8） 其他短期薪酬。一、 應(yīng)付職工薪酬的內(nèi)容第一節(jié) 職工薪酬管理基礎(chǔ)知識2. 離職后福利離職后福利，是指企業(yè)為獲得職工提供的服務(wù)而在職工退休或與企業(yè)解除勞動關(guān)系后，提供的各種形式的報酬和福利，短期薪酬和辭退福利除外。離職后福利計劃分為設(shè)定提存計劃和設(shè)定受益計劃。設(shè)定提存計劃，是指向獨立的基金繳存固定費用后，企業(yè)不再承擔(dān)進(jìn)一步支付義務(wù)的離職后福利計劃，如養(yǎng)老保

3、險和失業(yè)保險。設(shè)定受益計劃，是指除設(shè)定提成計劃以外的離職后福利計劃。第一節(jié) 職工薪酬管理基礎(chǔ)知識一、 應(yīng)付職工薪酬的內(nèi)容3. 辭退福利辭退福利，是指企業(yè)在職工勞動合同到期之前解除與職工的勞動關(guān)系或者為鼓勵職工自愿接受裁減而給予職工的補償。第一節(jié) 職工薪酬管理基礎(chǔ)知識一、 應(yīng)付職工薪酬的內(nèi)容4. 其他長期職工福利其他長期職工福利，是指除短期薪酬、離職后福利、辭退福利以外所有的職工薪酬，包括長期帶薪缺勤、長期殘疾福利、長期利潤分享計劃等。第一節(jié) 職工薪酬管理基礎(chǔ)知識一、 應(yīng)付職工薪酬的內(nèi)容1. 計提貨幣性職工薪酬借：管理費用 生產(chǎn)成本 制造費用 勞務(wù)成本等 貸：應(yīng)付職工薪酬工資、獎金、津貼和補貼二

4、、 貨幣性職工薪酬的核算第一節(jié) 職工薪酬管理基礎(chǔ)知識2. 支付工資借：應(yīng)付職工薪酬工資、獎金、津貼和補貼（支付工資、獎金、津貼、補貼等） 貸：銀行存款 庫存現(xiàn)金 其他應(yīng)收款（扣還代墊的各種款項） 應(yīng)交稅費應(yīng)交個人所得稅（代扣個人所得稅）二、 貨幣性職工薪酬的核算第一節(jié) 職工薪酬管理基礎(chǔ)知識1. 企業(yè)以其自產(chǎn)產(chǎn)品作為非貨幣性福利發(fā)放給職工（1） 確認(rèn)收入：借：應(yīng)付職工薪酬非貨幣性福利 貸：主營業(yè)務(wù)收入 應(yīng)交稅費應(yīng)交增值稅（銷項稅額）（2） 結(jié)轉(zhuǎn)產(chǎn)品成本：借：主營業(yè)務(wù)成本 貸：庫存商品（3） 確認(rèn)應(yīng)付職工薪酬：借：生產(chǎn)成本 管理費用 銷售費用等 貸：應(yīng)付職工薪酬非貨幣性福利三、 非貨幣性職工薪酬的

5、核算第一節(jié) 職工薪酬管理基礎(chǔ)知識2. 將企業(yè)擁有的房屋等資產(chǎn)無償提供給職工使用借：管理費用 生產(chǎn)成本 制造費用等 貸：應(yīng)付職工薪酬非貨幣性福利同時：借：應(yīng)付職工薪酬非貨幣性福利 貸：累計折舊三、 非貨幣性職工薪酬的核算第一節(jié) 職工薪酬管理基礎(chǔ)知識 3. 租賃住房等資產(chǎn)供職工無償使用（1） 確認(rèn)應(yīng)付職工薪酬時：借：管理費用 生產(chǎn)成本 制造費用等 貸：應(yīng)付職工薪酬非貨幣性福利（2） 支付房租時：借：應(yīng)付職工薪酬非貨幣性福利 貸：銀行存款三、 非貨幣性職工薪酬的核算第一節(jié) 職工薪酬管理基礎(chǔ)知識四、 五險的管理第一節(jié) 職工薪酬管理基礎(chǔ)知識1. 五險的種類五險包括基本養(yǎng)老保險、基本醫(yī)療保險、工傷保險、失

6、業(yè)保險和生育保險。其中，需要單位及個人共同繳納的有三種，即基本養(yǎng)老保險、基本醫(yī)療保險和失業(yè)保險；只需單位繳納而個人不需繳納的有兩種，即工傷保險和生育保險。2. 基本養(yǎng)老保險的繳納（1） 單位繳費。原繳費比例超過企業(yè)工資總額20%的?。▍^(qū)、市）降至20%。（2） 個人繳費。一般情況：以職工本人“上年度”月平均工資（新職工第一年以起薪當(dāng)月工資作為繳費基數(shù)）為繳費基數(shù)，按8%的比例繳納。特殊情況：繳費基數(shù)低于當(dāng)?shù)芈毠ぴ缕骄べY60%的，按當(dāng)?shù)芈毠ぴ缕骄べY的60%作為繳費基數(shù)；繳費基數(shù)高于當(dāng)?shù)芈毠ぴ缕骄べY300%的，按當(dāng)?shù)芈毠ぴ缕骄べY的300%作為繳費基數(shù)。3. 基本醫(yī)療保險的繳納（1） 單位按

7、職工工資總額的6%繳費。（2） 個人賬戶資金來源：個人繳費（本人工資收入的2%）與單位繳費劃入（單位繳費的30%）。四、 五險的管理第一節(jié) 職工薪酬管理基礎(chǔ)知識4. 工傷保險的繳納工傷保險費由用人單位繳納，職工不繳納。國家根據(jù)不同行業(yè)的工傷風(fēng)險程度確定行業(yè)的差別費率，并根據(jù)使用工傷保險基金、工傷發(fā)生率等情況在每個行業(yè)內(nèi)確定費率檔次。5. 失業(yè)保險的繳納單位繳費比例階段性降至1%1.5%，個人繳費比例不超過0.5%。6. 生育保險的繳納生育保險由用人單位繳納，職工不繳納。生育保險和基本醫(yī)療保險合并實施工作，待國務(wù)院制定出臺相關(guān)規(guī)定后統(tǒng)一組織實施。1. 住房公積金的構(gòu)成住房公積金由兩部分組成：一部

8、分由職工所在單位繳存，另一部分由職工個人繳存。職工個人繳存部分由單位代扣后，連同單位繳存部分一并繳存到住房公積金個人賬戶內(nèi)。五、 住房公積金的管理第一節(jié) 職工薪酬管理基礎(chǔ)知識2. 住房公積金繳費比例單位應(yīng)當(dāng)于每月發(fā)放職工工資之日起5日內(nèi)將單位繳存的和為職工代繳的住房公積金匯繳到住房公積金專戶內(nèi)，由受委托銀行計入職工住房公積金賬戶。具體的繳納比例與繳納基數(shù)如下：(1) 繳納比例。根據(jù)建設(shè)部、財政部、中國人民銀行關(guān)于住房公積金管理若干具體問題的指導(dǎo)意見(建金管20055號)的規(guī)定，設(shè)區(qū)城市(主要指地級市)單位和職工繳存比例最低不低于5%，原則上不高于12%(合計繳存比例為10%24%)，具體標(biāo)準(zhǔn)由

9、各地根據(jù)實際情況確定。也就是說，住房公積金繳納比例只有最低限制，上不封頂。財政部、國家稅務(wù)總局發(fā)布的關(guān)于基本養(yǎng)老保險費、基本醫(yī)療保險費、失業(yè)保險費、住房公積金有關(guān)個人所得稅政策的通知(財稅200610號)中規(guī)定，單位和個人分別在不超過職工本人上一年度月平均工資12%的幅度內(nèi)，其實際繳存的住房公積金，允許在個人應(yīng)納稅所得額中扣除。(2) 繳納基數(shù)。根據(jù)住房公積金管理條例的規(guī)定，職工住房公積金的月繳存基數(shù)為職工本人上一年度月平均工資。單位為職工繳存的住房公積金的月繳存額為職工本人上一年度月平均工資。新參加工作的職工從參加工作的第二個月開始繳存住房公積金，月繳存基數(shù)為職工本人當(dāng)月工資。單位新調(diào)入的職

10、工從調(diào)入單位發(fā)放工資之日起繳存住房公積金，月繳存基數(shù)為職工本人當(dāng)月工資。根據(jù)建設(shè)部、財政部、中國人民銀行關(guān)于住房公積金管理若干具體問題的指導(dǎo)意見的規(guī)定，繳存住房公積金的月工資基數(shù)，原則上不應(yīng)超過職工工作地所在設(shè)區(qū)城市統(tǒng)計部門公布的上一年度職工月平均工資的2倍或3倍。超過職工工作地所在設(shè)區(qū)城市上一年度職工月平均工資3倍的，應(yīng)將超過部分并入個人當(dāng)期的工資、薪金收入，計征個人所得稅。五、 住房公積金的管理第一節(jié) 職工薪酬管理基礎(chǔ)知識02第二節(jié)基礎(chǔ)表格的建立學(xué)習(xí)目標(biāo)0102能正確設(shè)計有關(guān)職工工資的基礎(chǔ)表格，制作出本章“引導(dǎo)案例”中的表7-1（華光公司職工基本情況表）、表7-2（華光公司職工基本工資表）

11、、表7-3（2016年12月華光公司職工考勤表）和表7-4（2015年華光公司職工月平均工資表）掌握“五險一金”的計提標(biāo)準(zhǔn)和比例，根據(jù)上年月平均工資標(biāo)準(zhǔn)，制作“三險一金”計提表和“五險一金”計提表啟動Microsoft Excel 2013新建一個工作簿，將工作簿命名為“華光公司工資管理”。一、 新建并打開“華光公司工資管理”工作簿第二節(jié) 基礎(chǔ)表格的建立1. 建立職工基本情況表在打開的“華光公司工資管理”工作簿中，將Sheet 1工作表重命名為“職工基本情況表”。二、 建立并編制職工基本情況表第二節(jié) 基礎(chǔ)表格的建立2. 設(shè)計職工基本情況表（1） 選中單元格A1，輸入“華光公司職工基本情況表”。

12、（2） 分別選中單元格A2、B2、C2、D2、E2、F2、G2、H2、I2，在對應(yīng)單元格中依次輸入“職工代碼”“姓名”“性別”“出生年月”“所屬部門”“工作崗位”“職工類別”“銀行卡號”“進(jìn)廠時間”，如圖7-1 所示。（3） 按住Ctrl鍵，單擊列字母A、H，在所選區(qū)域內(nèi)右擊選擇“設(shè)置單元格格式”命令，在彈出的對話框中選擇“文本”選項，將“職工代碼”和“銀行卡號”所在列設(shè)置為“文本”格式，如圖7-2所示。（4） 按住Ctrl鍵，單擊列字母D、I，在所選區(qū)域內(nèi)右擊選擇“設(shè)置單元格格式”命令，在對話框中選擇“日期”選項，將“出生年月”和“進(jìn)廠時間”所在列設(shè)置為日期格式。二、 建立并編制職工基本情況

13、表第二節(jié) 基礎(chǔ)表格的建立（5） 選中單元格C3，切換到“數(shù)據(jù)”選項卡，在“數(shù)據(jù)工具”組中選擇“數(shù)據(jù)驗證”命令調(diào)出“數(shù)據(jù)驗證”對話框，在“數(shù)據(jù)驗證”對話框“設(shè)置”標(biāo)簽“允許”欄中選“序列”，在“來源”欄中輸入“男,女”（中間用英文逗號分隔），如圖7-3所示。通過“數(shù)據(jù)驗證”的設(shè)置，在單元格C3右側(cè)出現(xiàn)下拉菜單箭頭，單擊該箭頭即可選擇錄入，避免重復(fù)輸入。然后，利用填充柄填充“性別”列其他單元格。（6） 按上述“數(shù)據(jù)驗證”的設(shè)置方法，將“所屬部門”所在列的“來源”設(shè)置為“綜合管理科,財務(wù)科,采購科,銷售科,生產(chǎn)科”，如圖7-4所示。（7） 按上述“數(shù)據(jù)驗證”的設(shè)置方法，將“職工類別”所在列的“來源”

14、設(shè)置為“管理人員,購銷人員,生產(chǎn)人員”，如圖7-5所示。二、 建立并編制職工基本情況表第二節(jié) 基礎(chǔ)表格的建立3. 適當(dāng)調(diào)整列寬后輸入職工基本信息（1） 在單元格A3中輸入“001”，單元格B3中輸入“張一平”，單元格D3中輸入“1965-11-1”或“1965/11/1”，單元格F3中輸入“總經(jīng)理”，單元格H3中輸入“62828800001”，單元格I3中輸入“2000-1-1”或“2000/1/1”；在單元格C3中選擇“男”，單元格E3中選擇“綜合管理部”，單元格G3中選擇“管理人員”。結(jié)果如圖7-6所示。（2） 鼠標(biāo)指向單元格A3右下角，出現(xiàn)“+”時向下拖動鼠標(biāo)，復(fù)制結(jié)果如圖7-7所示。（

15、3） 按所給資料輸入其他各行數(shù)據(jù)，結(jié)果如圖7-8所示。二、 建立并編制職工基本情況表第二節(jié) 基礎(chǔ)表格的建立4. 修飾職工基本情況表（1） 拖動鼠標(biāo)選擇A1：I1區(qū)域，單擊“合并后居中”按鈕，指向行序號1、2間拖動鼠標(biāo)調(diào)整增加第一行行高，將標(biāo)題行設(shè)置為水平、垂直居中，將“華光公司職工基本情況表”設(shè)置為“黑體、16號”并加雙下畫線，結(jié)果如圖7-9所示。（2） 拖動鼠標(biāo)選擇A2：I17區(qū)域，右擊選擇“設(shè)置單元格格式”命令，在“設(shè)置單元格格式”對話框中設(shè)置添加內(nèi)外邊框。（3） 最終形成的職工基本情況表如圖7-10所示。二、 建立并編制職工基本情況表第二節(jié) 基礎(chǔ)表格的建立1. 新建職工基本工資表打開“華

16、光公司工資管理”工作簿，在工作表標(biāo)簽區(qū)域單擊“新工作表”按鈕新建一張工作表，并重命名為“職工基本工資表”。三、 建立并編制職工基本工資表第二節(jié) 基礎(chǔ)表格的建立2. 設(shè)計職工基本工資表（1） 選中單元格A1，輸入“華光公司職工基本工資表”。（2） 分別選中單元格A2、B2、C2、D2，在對應(yīng)單元格中依次輸入“員工代碼”“姓名”“所屬部門”“基礎(chǔ)工資”，如圖7-11所示。圖7-11 新建并設(shè)計職工基本工資表（3） 單擊列字母D，右擊選擇“設(shè)置單元格格式”命令，將基礎(chǔ)工資所在的D列設(shè)置為“會計專用（不帶貨幣符號）”格式。三、 建立并編制職工基本工資表第二節(jié) 基礎(chǔ)表格的建立3. 鏈接導(dǎo)入職工基本信息“

17、職工基本工資表”工作表中的員工代碼等信息已在“職工基本情況表”工作表中錄入，通過鏈接導(dǎo)入即可。（1） 分別選中單元格A3、B3、C3，在對應(yīng)單元格中依次輸入公式“=職工基本情況表!A3”“=職工基本情況表!B3”“=職工基本情況表!E3”后回車。（2） 鼠標(biāo)分別指向單元格A3、B3、C3右下角，當(dāng)出現(xiàn)“+”填充柄時向下拖動，結(jié)果如圖7-12 所示。三、 建立并編制職工基本工資表第二節(jié) 基礎(chǔ)表格的建立4. 輸入職工基礎(chǔ)工資并匯總（1） 由于基礎(chǔ)工資項在其他表中沒有輸入過，本表中只能逐個輸入，結(jié)果如圖7-13所示。（2） 選中單元格D18，輸入公式“=SUM(D3:D17)”后回車，求出基礎(chǔ)工資合

18、計數(shù)。三、 建立并編制職工基本工資表第二節(jié) 基礎(chǔ)表格的建立5. 修飾職工基本工資表（1） 拖動鼠標(biāo)選擇A1：D1區(qū)域，單擊“合并后居中”按鈕，指向行序號1、2間拖動鼠標(biāo)調(diào)整增加第一行行高，將標(biāo)題行設(shè)置為水平、垂直居中，將“華光公司職工基本工資表”設(shè)置為“黑體，16號”并加雙下畫線。（2） 拖動鼠標(biāo)選擇A2：D2區(qū)域，水平居中并加粗。（3） 拖動鼠標(biāo)選擇A18：C18區(qū)域，單擊“合并后居中”按鈕后輸入“合計”。（4） 拖動鼠標(biāo)選擇A2：D18區(qū)域，右擊選擇“設(shè)置單元格格式”命令，在“設(shè)置單元格格式”對話框中設(shè)置添加內(nèi)外邊框，結(jié)果如圖7-14所示。三、 建立并編制職工基本工資表第二節(jié) 基礎(chǔ)表格的建

19、立1. 新建職工考勤表打開“華光公司工資管理”工作簿，在工作表標(biāo)簽區(qū)域單擊“新工作表”按鈕新建一張工作表，重命名為“職工考勤表”。四、 建立并編制職工考勤表第二節(jié) 基礎(chǔ)表格的建立2. 編制職工考勤表（1） 選中單元格A1，輸入“華光公司職工考勤表”。（2） 選中單元格A2、B2、C2、D2、E2、F2、G2、H2、I2，分別輸入“員工代碼”“姓名”“所屬部門”“應(yīng)出勤天數(shù)”“病假天數(shù)”“事假天數(shù)”“實際出勤天數(shù)”“日常加班天數(shù)”“節(jié)日加班天數(shù)”，將其設(shè)置為水平居中、垂直居中、自動換行后加粗，如圖7-15所示。（3） 選中單元格A3、B3、C3，分別鏈接導(dǎo)入“職工基本情況表”工作表中的員工代碼、

20、姓名、所屬部門。（4） 參照所給資料輸入應(yīng)出勤天數(shù)、病假天數(shù)、事假天數(shù)、日常加班天數(shù)、節(jié)日加班天數(shù)。（5） 用公式計算實際出勤天數(shù)，實際出勤天數(shù)=應(yīng)出勤天數(shù)-病假天數(shù)-事假天數(shù)。（6） 拖動鼠標(biāo)選擇A1：I1區(qū)域，單擊“合并后居中”按鈕，將標(biāo)題設(shè)置為“黑體、16號”后加雙下畫線。（7） 添加內(nèi)外邊框后形成最終的職工考勤表，結(jié)果如圖7-16所示。四、 建立并編制職工考勤表第二節(jié) 基礎(chǔ)表格的建立按照國家有關(guān)法律制度的規(guī)定，職工的社保和住房公積金應(yīng)按上年度職工平均工資的一定比例計提。所以，在編制三險一金和五險一金計提表前，應(yīng)編制上年度月平均工資表。因為上年度月平均工資已在資料中給出，按上述建立并編制

21、職工考勤表的方法建立并編制上年度月平均工資表，并在最后一行進(jìn)行合計，得到最終的“上年度月平均工資表”，結(jié)果如圖7-17 所示。五、 建立并編制上年度月平均工資表第二節(jié) 基礎(chǔ)表格的建立1. 新建三險一金表打開“華光公司工資管理”工作簿，在工作表標(biāo)簽區(qū)域單擊“新工作表”按鈕新建一張工作表，重命名為“三險一金表”。六、 建立并編制三險一金計提表第二節(jié) 基礎(chǔ)表格的建立 2. 編制三險一金表（1） 選中單元格A1，輸入“華光公司三險一金計提表”。（2） 選中單元格A2、B2、C2、D2、E2、F2、G2、H2，分別輸入“員工代碼”“姓名”“所屬部門”“上年月平工資”“養(yǎng)老保險（8%）”“醫(yī)療保險（2%）

22、”“失業(yè)保險（1%）”“住房公積金（10%）”，并適當(dāng)調(diào)整列寬。（3） 選中單元格A3、B3、C3、D3，分別鏈接導(dǎo)入“上年度月平均工資表”工作表中的員工代碼、姓名、所屬部門、上年月平工資。（4） 選擇單元格E3、F3、G3、H3，根據(jù)繳費計提比例分別輸入公式“=D3*0.08”“=D3*0.02”“=D3*0.01”“=D3*0.1”后回車，結(jié)果如圖7-18所示。六、 建立并編制三險一金計提表第二節(jié) 基礎(chǔ)表格的建立（5） 將鼠標(biāo)指向第3行，拖動選擇A3：H3區(qū)域，再指向區(qū)域右下角，出現(xiàn)“+”填充柄后向下拖動至第17行；選中單元格A18，輸入“合計”；選中單元格D18，輸入公式“=SUM（D3

23、：D17）”后回車，求出上年月平工資合計數(shù)；將鼠標(biāo)指向單元格D18右下角，當(dāng)出現(xiàn)“+”填充柄時向右拖動至單元格H18，結(jié)果如圖7-19所示。（6） 選擇A1：H1區(qū)域合并居中，標(biāo)題設(shè)置為“黑體、16號”后加雙下畫線。（7） 選擇A2：H2區(qū)域設(shè)置為水平居中、垂直居中，自動換行后加粗。（8） 選擇A18：C18區(qū)域，單擊“合并后居中”按鈕。（9） 添加內(nèi)外邊框后形成最終的“三險一金表”工作表，結(jié)果如圖7-20所示。六、 建立并編制三險一金計提表第二節(jié) 基礎(chǔ)表格的建立參照上述編制“三險一金表”工作表的方法編制五險一金計提表，結(jié)果如圖7-21所示。七、 建立并編制五險一金計提表第二節(jié) 基礎(chǔ)表格的建立

24、03第三節(jié)工資結(jié)算單的建立與工資條的制作學(xué)習(xí)目標(biāo)010203學(xué)會制作工資結(jié)算單在工資結(jié)算單的基礎(chǔ)上制作工資條進(jìn)一步熟悉VLOOKUP函數(shù)的用途一、 建立并編制工資結(jié)算單1. 新建工資結(jié)算單打開“華光公司工資管理”工作簿，在工作表標(biāo)簽區(qū)域單擊“新工作表”按鈕新建一張工作表，重命名為“工資結(jié)算單”。第三節(jié) 工資結(jié)算單的建立與工資條的制作2. 設(shè)計工資結(jié)算單（1） 選中單元格A1，輸入“華光公司工資結(jié)算單”。（2） 分別選中單元格A2、B2、C2、D2、E2、F2、G2、H2、I2、J2、K2、L2、M2、N2、O2、P2、Q2、R2、S2、T2、U2、V2、W2、X2、Y2、Z2，在對應(yīng)單元格中依

25、次輸入“員工代碼”“姓名”“所屬部門”“工作崗位”“職工類別”“進(jìn)廠時間”“基礎(chǔ)工資”“崗位工資”“年工工資”“日常加班天數(shù)”“日常加班費”“節(jié)假日加班天數(shù)”“節(jié)假日加班費”“事假天數(shù)”“事假扣款”“病假天數(shù)”“病假扣款”“應(yīng)發(fā)工資”“養(yǎng)老保險”“醫(yī)療保險”“失業(yè)保險”“住房公積金”“三險一金小計”“應(yīng)納稅所得額”“個人所得稅”“實發(fā)工資”。第三節(jié) 工資結(jié)算單的建立與工資條的制作一、 建立并編制工資結(jié)算單3. 鏈接導(dǎo)入職工基本情況表和職工基本工資表中數(shù)據(jù)（1） 分別選中單元格A3、B3、C3、D3、E3、F3，在對應(yīng)單元格中依次輸入公式“=職工基本情況表!A3”“=職工基本情況表!B3”“=職

26、工基本情況表!E3”“=職工基本情況表!F3”“=職工基本情況表!G3”“=職工基本情況表!I3”后回車。（2） 選中單元格G3，輸入公式“=職工基本工資表!D3”后回車。（3） 拖動鼠標(biāo)選擇A3：G3區(qū)域，鼠標(biāo)指向該區(qū)域右下角，當(dāng)出現(xiàn)“+”填充柄時，向下拖動至第17行，結(jié)果如圖7-22所示。第三節(jié) 工資結(jié)算單的建立與工資條的制作一、 建立并編制工資結(jié)算單4. 設(shè)置第二行列寬由于本表列次較多，可將第二行各列標(biāo)題設(shè)置為自動換行，再調(diào)整其列寬。（1） 選擇A2：Z2區(qū)域，右擊選擇“設(shè)置單元格格式”命令，在“對齊”標(biāo)簽下設(shè)置水平居中、垂直居中、自動換行。（2） 將A2：Z2區(qū)域字體加粗后，調(diào)整各列列

27、寬，如圖7-23所示。第三節(jié) 工資結(jié)算單的建立與工資條的制作一、 建立并編制工資結(jié)算單5. 鏈接導(dǎo)入職工出勤表和三險一金表中數(shù)據(jù)（1） 分別選中單元格J3、L3、N3、P3，在對應(yīng)單元格中依次輸入公式“=職工考勤表!H3”“=職工考勤表!I3”“=職工考勤表!F3”“=職工考勤表!E3”后回車。（2） 分別選擇單元格S3、T3、U3、V3，在對應(yīng)單元格中依次輸入公式“=三險一金表!E3”“=三險一金表!F3”“=三險一金表!G3”“=三險一金表!H3”后回車。（3） 拖動鼠標(biāo)選擇相應(yīng)單元格所在區(qū)域，鼠標(biāo)指向該區(qū)域右下角，當(dāng)出現(xiàn)“+”填充柄時，向下拖動至第17行，結(jié)果如圖7-24所示。第三節(jié) 工

28、資結(jié)算單的建立與工資條的制作一、 建立并編制工資結(jié)算單6. 修飾表頭本例是在已知華光公司2015年度工資并計算了2015年度月平均工資的基礎(chǔ)上生成2016年12月的工資結(jié)算單。在表頭部分還要插入一行，輸入發(fā)放工資的日期2016年12月31日，并將其設(shè)置為日期型。（1） 修飾第一行。拖動鼠標(biāo)選擇A1：Z1區(qū)域，先合并居中，再設(shè)置垂直居中、黑體、16號并加雙下畫線。（2） 插入形成第二行。右擊行序號2，選擇插入命令后合并A2：Z2區(qū)域。（3） 設(shè)置第二行為日期型。（4） 輸入“2016-12-31”或“2016/12/31”，結(jié)果如圖7-25所示。第三節(jié) 工資結(jié)算單的建立與工資條的制作一、 建立并

29、編制工資結(jié)算單7. 利用公式生成工資結(jié)算單中其他數(shù)據(jù)（1） 設(shè)置崗位工資公式。選中單元格H4，輸入公式“=IF(D4=總經(jīng)理,2000,IF(D4=主任,1800,IF(D4=科長,1500,1000)”后回車。（2） 設(shè)置年工工資公式。選中單元格I4，輸入公式“=DATEDIF(F4,$A$2,Y)*50”后回車。（3） 設(shè)置日常加班費公式。選中單元格K4，輸入公式“=J4*G4/24*2”后回車。（4） 設(shè)置節(jié)假日加班費公式。選中單元格M4，輸入公式“=L4*G4/24*3”后回車。（5） 設(shè)置事假扣款公式。選中單元格O4，輸入公式“=N4*G4/24”后回車。（6） 設(shè)置病假扣款公式。選

30、中單元格Q4，輸入公式“=P4*50”后回車。（7） 設(shè)置應(yīng)發(fā)工資公式。選中單元格R4，輸入公式“=G4+H4+I4+K4+M4-O4-Q4”后回車。（8） 設(shè)置三險一金小計公式。選中單元格W4，輸入公式“=S4+T4+U4+V4”后回車。（9） 設(shè)置應(yīng)納稅所得額公式。選中單元格X4，輸入公式“=IF(R4-W4-3500)0,R4-W4-3500,0)”后回車。（10） 設(shè)置個人所得稅公式。選中單元格Y4，輸入公式“=IF(X40,0,IF(X41500,0.03*X4,IF(X44500,0.1*X4-105,IF(X49000,0.2*X4-555,IF(X435000,0.25*X4-

31、1005)”后回車。（11） 設(shè)置實發(fā)工資公式。選中單元格Z4，輸入公式“=R4-W4-Y4”后回車。（12） 復(fù)制公式生成其他職工數(shù)據(jù)。（13） 利用SUM函數(shù)或求和按鈕求所有職工的合計數(shù)。第三節(jié) 工資結(jié)算單的建立與工資條的制作一、 建立并編制工資結(jié)算單8. 修飾工資結(jié)算單加內(nèi)外邊框并將相關(guān)行標(biāo)題加不同的背景色區(qū)分，最終生成的工資結(jié)算單如圖7-26所示。第三節(jié) 工資結(jié)算單的建立與工資條的制作一、 建立并編制工資結(jié)算單1. 新建工資發(fā)放表打開“華光公司工資管理”工作簿，在工作表標(biāo)簽區(qū)域單擊“新工作表”按鈕新建一張工作表，重命名為“工資發(fā)放表”。第三節(jié) 工資結(jié)算單的建立與工資條的制作二、 建立并

32、編制工資發(fā)放表2. 復(fù)制并修改工作表工資發(fā)放表作為制作工資條的依據(jù)，可將工資結(jié)算單中的數(shù)據(jù)全部復(fù)制過來，再刪除一些項目。（1） 復(fù)制工作表。切換到“工資結(jié)算單”工作表，復(fù)制整個表中內(nèi)容，再切換回“工資發(fā)放表”工作表，將數(shù)據(jù)選擇性粘貼。（2） 將“工作崗位”“職工類別”“進(jìn)廠時間”“日常加班天數(shù)”“節(jié)假日加班天數(shù)”“事假天數(shù)”病假天數(shù)“應(yīng)納稅所得額”所在列刪除。（3） 將表頭的第一行標(biāo)題重命名為“華光公司工資發(fā)放表”，再將A1：R1區(qū)域合并后居中；將第二行A2：R2區(qū)域合并后居中，并設(shè)置為日期型。（4） 將表體中的數(shù)字部分設(shè)置為“會計專用（不帶貨幣符號）”格式。（5） 為表格加內(nèi)外邊框并設(shè)置為水

33、平居中，最終生成的工資發(fā)放表結(jié)果如圖7-27所示。（6） 如要節(jié)省制作工資條紙張，也可以將上表復(fù)制，將表頭放在一行，得到的發(fā)放表（2）如圖7-28所示。第三節(jié) 工資結(jié)算單的建立與工資條的制作二、 建立并編制工資發(fā)放表1. 通過函數(shù)自動生成在新建工作表單元格A1中輸入公式“=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,工資發(fā)放表!A$3),INDEX(工資發(fā)放表!$A:$R,INT(ROW()-1)/3+4,COLUMN()”即可自動生成。第三節(jié) 工資結(jié)算單的建立與工資條的制作三、 制作工資條2. 通過公式完成（1） 單擊“新工作表”按鈕，并將工作表重命名為“12月工

34、資條1”。（2） 切換到“工資發(fā)放表（2）”，拖動鼠標(biāo)選擇A1：R2區(qū)域，右擊選擇“復(fù)制”命令。（3） 切換回“12月工資條1”工作表，右擊單元格A1選擇“粘貼”命令。（4） 將A列設(shè)置為文本型，并在單元格A3中輸入“001”。（5） 選中單元格B3并輸入公式“=VLOOKUP(A3,工資發(fā)放表 (2)!$A:$R,2,0)”后回車。（6） 選中單元格C3并輸入公式“=VLOOKUP(A3,工資發(fā)放表 (2)!$A:$R,3,0)”后回車。（7） 選中單元格D3并輸入公式“=VLOOKUP(A3,工資發(fā)放表 (2)!$A:$R,4,0)”后回車。第三節(jié) 工資結(jié)算單的建立與工資條的制作三、 制作

35、工資條（8） 依照上述規(guī)律，將第三行相應(yīng)單元格輸入公式，在最后一個單元格R3中輸入公式“=VLOOKUP(A3,工資發(fā)放表 (2)!$A:$R,18,0)”后回車。（9） 選擇A2:R3區(qū)域，加內(nèi)外邊框。（10） 右擊行序號1，選擇“插入”命令，插入一空白行，再將A1：R1區(qū)域合并后居中，結(jié)果如圖7-29所示。（11） 拖動鼠標(biāo)選擇A1：R4區(qū)域，鼠標(biāo)指向該區(qū)域右下角，出現(xiàn)“+”填充柄時向下拖動，如圖7-30 所示。（12） 通過設(shè)置第一名職工的取數(shù)公式，可將其公式拖動復(fù)制到其他職工進(jìn)行取數(shù)，則其他職工的工資明細(xì)全部自動生成，結(jié)果如圖7-31所示。（13） 如需打印，可設(shè)置紙張大小和橫向打印。

36、第三節(jié) 工資結(jié)算單的建立與工資條的制作三、 制作工資條3. 手動方式制作（1） 單擊“新工作表”按鈕，并將新工作表重命名為“12月工資條2”。（2） 將“工資發(fā)放表（2）”工作表中數(shù)據(jù)通過選擇性粘貼復(fù)制到“12月工資條2”工作表中。（3） 在表頭右擊行序號1選擇“插入”命令，插入一空白行并對其相應(yīng)區(qū)域A1：R1合并居中。（4） 在第1名職工下插入3個空白行，拖動選擇3行后復(fù)制，在第2名職工下右擊選擇“粘貼”命令粘貼，結(jié)果如圖7-32所示。（5） 按上述方法依次在每名職工后插入3行空白行，結(jié)果如圖7-33所示。（6） 拖動選擇A1：R3所在區(qū)域，右擊選擇“復(fù)制”命令。（7） 選擇各空白區(qū)域左上角

37、所在單元格，右擊選擇“粘貼”命令，手動制作的工資條如圖7-34 所示。第三節(jié) 工資結(jié)算單的建立與工資條的制作三、 制作工資條04第四節(jié)工資總額匯總表的編制學(xué)習(xí)目標(biāo)0102掌握在源工作表基礎(chǔ)上生成有關(guān)匯總數(shù)據(jù)的方法能夠靈活運用數(shù)據(jù)透視表、SUMIF函數(shù)、分類匯總等方法生成匯總數(shù)據(jù)，能夠準(zhǔn)確進(jìn)行工資費用的分配1. 在“工資結(jié)算單”工作表中操作（1） 打開“華光公司工資管理”工作簿，并選擇切換到“工資結(jié)算單”工作表中。（2） 選中單元格AA3，輸入“對應(yīng)會計科目”。（3） 選中單元格AA4，在功能區(qū)中切換到“數(shù)據(jù)”選項卡，選擇“數(shù)據(jù)工具”組中“數(shù)據(jù)驗證”命令。（4） 在彈出的對話框“允許”欄中選擇“

38、序列”，“來源”欄中輸入“生產(chǎn)成本，制造費用，管理費用，銷售費用”（用英文狀態(tài)逗號分隔），如圖7-35所示。（5） 確定后將設(shè)置向下復(fù)制。（6） 根據(jù)所屬部門、工作崗位、職工類別選擇科目，如圖7-36所示。（7） 在功能區(qū)中切換到“插入”選項卡，選擇“數(shù)據(jù)透視表”命令并打開“創(chuàng)建數(shù)據(jù)透視表”對話框。（8） 將對話框中的“區(qū)域”選擇為“A3：AA18”，如圖7-37所示。第四節(jié) 工資總額匯總表的編制一、 利用數(shù)據(jù)透視表的方式生成工資總額匯總表2. 在新工作表中操作在上述“創(chuàng)建數(shù)據(jù)透視表”對話框設(shè)置數(shù)據(jù)透視表位置為“新工作表”后單擊“確定”按鈕，將自動生成一張新工作表并出現(xiàn)“數(shù)據(jù)透視表字段”對話框

39、。（1） 在新工作表中的“數(shù)據(jù)透視表字段”對話框中，將“所屬部門”字段拖到行區(qū)域，“對應(yīng)會計科目”字段拖到列區(qū)域，“應(yīng)發(fā)工資”“實發(fā)工資”字段拖到值區(qū)域，結(jié)果如圖7-38所示。（2） 將前兩行對應(yīng)區(qū)域合并居中，數(shù)值區(qū)域設(shè)置為“會計專用（不帶貨幣符號）”格式并調(diào)整列寬，將工作表重命名為“工資匯總表1”，結(jié)果如圖7-39所示。第四節(jié) 工資總額匯總表的編制一、 利用數(shù)據(jù)透視表的方式生成工資總額匯總表1. 新建工資匯總表打開“華光公司工資管理”工作簿，在工作表標(biāo)簽區(qū)域單擊“新工作表”按鈕新建一張工作表，重命名為“工資匯總表2”。第四節(jié) 工資總額匯總表的編制二、 利用SUMIF函數(shù)生成工資總額匯總表2.

40、 設(shè)計工資匯總表（1） 選中單元格A1，輸入“華光公司工資匯總表”。（2） 分別選中單元格A2、B2、C2、D2、E2、F2、G2、H2、I2，在對應(yīng)單元格中依次輸入“所屬部門”“應(yīng)發(fā)工資”“養(yǎng)老保險”“醫(yī)療保險”“失業(yè)保險”“住房公積金”“三險一金小計”“個人所得稅”“實發(fā)工資”，如圖7-40所示。（3） 選中單元格A3，切換到“數(shù)據(jù)”選項卡，在“數(shù)據(jù)工具”組中選擇“數(shù)據(jù)驗證”命令調(diào)出“數(shù)據(jù)驗證”對話框，在“數(shù)據(jù)驗證”對話框“設(shè)置”標(biāo)簽中“允許”欄中選“序列”，在“來源”欄中輸入“綜合管理科,財務(wù)科,采購科,銷售科,生產(chǎn)科”（用英文狀態(tài)逗號分隔）并確定。（4） 選中單元格A3，鼠標(biāo)指向右下角

41、拖動填充柄到第7行，復(fù)制上一步數(shù)據(jù)驗證的設(shè)置。（5） 將A列的所屬部門選擇填入，最后一行中輸入“合計”，結(jié)果如圖7-41所示。第四節(jié) 工資總額匯總表的編制二、 利用SUMIF函數(shù)生成工資總額匯總表3. 利用SUMIF函數(shù)匯總（1） 選中單元格B3,輸入公式“=SUMIF(工資發(fā)放表!$C:$C,A3,工資發(fā)放表!$K:$K)”后回車。（2） 選中單元格C3,輸入公式“=SUMIF(工資發(fā)放表!$C:$C,A3,工資發(fā)放表!$L:$L)”后回車。（3） 選中單元格D3,輸入公式“=SUMIF(工資發(fā)放表!$C:$C,A3,工資發(fā)放表!$M:$M)”后回車。（4） 選中單元格E3,輸入公式“=SU

42、MIF(工資發(fā)放表!$C:$C,A3,工資發(fā)放表!$N:$N)”后回車。（5） 選中單元格F3,輸入公式“=SUMIF(工資發(fā)放表!$C:$C,A3,工資發(fā)放表!$O:$O)”后回車。（6） 選中單元格G3,輸入公式“=SUMIF(工資發(fā)放表!$C:$C,A3,工資發(fā)放表!$P:$P)”后回車。（7） 選中單元格H3,輸入公式“=SUMIF(工資發(fā)放表!$C:$C,A3,工資發(fā)放表!$Q:$Q)”后回車。（8） 選中單元格I3,輸入公式“=SUMIF(工資發(fā)放表!$C:$C,A3,工資發(fā)放表!$R:$R)”后回車。（9） 通過以上公式的錄入回車后，綜合管理科匯總結(jié)果如圖7-42所示。（10）

43、復(fù)制公式到下面各行的其他部門。選擇已設(shè)置好公式的第3行 B3:I3區(qū)域，鼠標(biāo)指向該區(qū)域的右下角，出現(xiàn)“+”填充柄時向下拖動至第7行，結(jié)果如圖7-43所示。第四節(jié) 工資總額匯總表的編制二、 利用SUMIF函數(shù)生成工資總額匯總表4. 匯總各部門工資（1） 拖動鼠標(biāo)選擇B3：I8區(qū)域，如圖7-44所示。（2） 在功能區(qū)的“開始”選項卡中單擊求和按鈕。（3） 各部門工資合計結(jié)果如圖7-45所示，實發(fā)工資合計數(shù)為“71 121.56”，與其他表格一致。第四節(jié) 工資總額匯總表的編制二、 利用SUMIF函數(shù)生成工資總額匯總表5. 修飾工資匯總表（1） 選擇A1：I1區(qū)域合并居中，標(biāo)題設(shè)置為“黑體、16號”后

44、加雙下畫線。（2） 選擇A2：I2區(qū)域設(shè)置為水平居中、垂直居中、加粗。（3） 將數(shù)字區(qū)域設(shè)置為“會計專用（不帶貨幣符號）”格式。（4） 調(diào)整各列寬度。（5） 添加內(nèi)外邊框。（6） 應(yīng)發(fā)工資和實發(fā)工資的合計數(shù)加底紋，與其他表格合計數(shù)對比。（7） 修飾后最終的工資匯總表如圖7-46所示。第四節(jié) 工資總額匯總表的編制二、 利用SUMIF函數(shù)生成工資總額匯總表1. 切換到“工資發(fā)放表（2）”中操作（1） 排序。拖動鼠標(biāo)選擇不含合并單元格的區(qū)域，在“數(shù)據(jù)”選項卡中單擊“排序”按鈕，在“排序”對話框中選擇按“所屬部門”升序排列，如圖7-47所示。（2） 設(shè)置分類匯總選項。在“數(shù)據(jù)”選項卡中單擊“分類匯總”

45、按鈕，在彈出的對話框中按圖7-48 所示設(shè)置。第四節(jié) 工資總額匯總表的編制三、 利用傳統(tǒng)的分類匯總生成工資總額匯總表（3） 單擊確定生成分類匯總表，結(jié)果如圖7-49所示。（4） 查看分類匯總結(jié)果，如圖7-50所示。第四節(jié) 工資總額匯總表的編制三、 利用傳統(tǒng)的分類匯總生成工資總額匯總表2. 取消分類匯總當(dāng)不需要分類匯總的數(shù)據(jù)時，可將分類匯總?cè)∠?。具體的操作步驟如下：（1） 單擊“分類匯總”按鈕，彈出“分類匯總”對話框。（2） 在對話框中單擊“全部刪除”按鈕。第四節(jié) 工資總額匯總表的編制三、 利用傳統(tǒng)的分類匯總生成工資總額匯總表04第五節(jié)工資的查詢與統(tǒng)計分析學(xué)習(xí)目標(biāo)010203進(jìn)一步熟悉VLOOK

46、UP函數(shù)的用途掌握篩選的基本方法及應(yīng)用能夠建立數(shù)據(jù)透視圖對數(shù)據(jù)進(jìn)行初步分析1. 復(fù)制工資發(fā)放表為了保證工資管理數(shù)據(jù)的原始性，將最初不含公式的“工資發(fā)放表”工作表復(fù)制，供篩選用。（1） 打開“華光公司工資管理”工作簿。（2） 將“工資發(fā)放表”工作表復(fù)制并重命名為“篩選用表”。以下在“篩選用表”中操作。第五節(jié) 工資的查詢與統(tǒng)計分析一、 通過篩選查詢2. 排序選擇不含合并單元格的區(qū)域，按先排序再篩選的步驟進(jìn)行操作。（1） 在功能區(qū)中的“數(shù)據(jù)”選項卡中單擊“排序”按鈕。（2） 在“排序”對話框中可根據(jù)情況選擇排序的關(guān)鍵字，如按“姓名”排序，結(jié)果如圖7-51所示。第五節(jié) 工資的查詢與統(tǒng)計分析一、 通過篩選查詢3.