1、EXCEL在財務(wù)中的應(yīng)用在財務(wù)中的應(yīng)用模塊模塊任務(wù)任務(wù)三三 EXCEL在工資管理中在工資管理中 的應(yīng)用的應(yīng)用一一 設(shè)置工資管理中的表設(shè)置工資管理中的表格體系并計算工資格體系并計算工資任務(wù)任務(wù)引出：引出： 利用利用EXCEL系統(tǒng)進(jìn)行工資業(yè)務(wù)管理，首先系統(tǒng)進(jìn)行工資業(yè)務(wù)管理，首先需要建立工資管理的相關(guān)表格，以便收集、錄需要建立工資管理的相關(guān)表格，以便收集、錄入工資管理中所需的基本數(shù)據(jù)并計算出當(dāng)月各入工資管理中所需的基本數(shù)據(jù)并計算出當(dāng)月各項工資數(shù)據(jù)。項工資數(shù)據(jù)。任務(wù)分析：任務(wù)分析： 一般企業(yè)在工資管理中，對員工工資的管理會涉及一般企業(yè)在工資管理中，對員工工資的管理會涉及到員工的基本檔案，在工資計算的內(nèi)

2、容中，還包括員工到員工的基本檔案，在工資計算的內(nèi)容中，還包括員工的基本工資、獎金、出勤情況、應(yīng)繳納的社會保險等多的基本工資、獎金、出勤情況、應(yīng)繳納的社會保險等多項內(nèi)容。因此員工工資的計算實際上可以理解為獲取以項內(nèi)容。因此員工工資的計算實際上可以理解為獲取以上基本數(shù)據(jù)并進(jìn)行統(tǒng)計、匯總和計算。有效地借助于上基本數(shù)據(jù)并進(jìn)行統(tǒng)計、匯總和計算。有效地借助于EXCELEXCEL的公式與函數(shù)、數(shù)據(jù)表單等操作，可以大大提高的公式與函數(shù)、數(shù)據(jù)表單等操作，可以大大提高工作效率，科學(xué)地計算與管理工資。工作效率，科學(xué)地計算與管理工資。知識講解：知識講解： 1.1 函數(shù)函數(shù)VLOOKUP 1. 作用：使用作用：使用 V

3、LOOKUP 函數(shù)搜索某個單元格區(qū)函數(shù)搜索某個單元格區(qū)域的第一列，然后返回該區(qū)域相同行上任何單元格域的第一列，然后返回該區(qū)域相同行上任何單元格中的值。中的值。 2. 語法：語法：VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)知識講解：知識講解：（1 1）lookup_valuelookup_value必需。必需。是要在表格或區(qū)域的第一列中搜索的值。 lookup_value 參數(shù)可以是值或引用。如果為 lookup_value 參數(shù)提供的值小于 table_array 參數(shù)第一列中的最小值，則 VLOOKUP 將返

4、回錯誤值 #N/A。（2 2）table_arraytable_array必需。必需。是包含數(shù)據(jù)的單元格區(qū)域。 可以使用對區(qū)域（例如A2:D8）或區(qū)域名稱的引用。table_array 第一列中的值是由 lookup_value 搜索的值。這些值可以是文本、數(shù)字或邏輯值。文本不區(qū)分大小寫。 知識講解：知識講解：（3 3）col_index_numcol_index_num必需。必需。 table_array 參數(shù)中必須返回的匹配值的列號。 col_index_num 參數(shù)為 1 時，返回 table_array 第一列中的值；col_index_num 為 2 時，返回 table_array

5、 第二列中的值，依此類推。 如果 col_index_num 參數(shù)小于 1，則 VLOOKUP 返回錯誤值 #VALUE!；大于 table_array 的列數(shù)，則 VLOOKUP 返回錯誤值 #REF!。知識講解：知識講解：（4 4）range_lookuprange_lookup可選。可選。其值是一個邏輯值，指定希望 VLOOKUP 查找精確匹配值還是近似匹配值。 如果 range_lookup 為 TRUE 或1或被省略，則返回精確匹配值或近似匹配值。如果找不到精確匹配值，則返回小于 lookup_value 的最大值。 如果 range_lookup 參數(shù)為 FALSE或0，VLOOK

6、UP 將只查找精確匹配值。如果 table_array 的第一列中有兩個或更多值與 lookup_value 匹配，則使用第一個找到的值。 如果找不到精確匹配值，則返回錯誤值 #N/A。知識講解：知識講解：3. 3. 使用注意事項：使用注意事項： 如果 range_lookup 為 TRUE 或1或被省略，則必須按升序排列 table_array 第一列中的值；否則，VLOOKUP 可能無法返回正確的值。 如果 range_lookup 為 FALSE或0，則不需要對 table_array 第一列中的值進(jìn)行排序。知識講解：知識講解：例例1：根據(jù)“基本工資”、“員工信息表”中的相關(guān)數(shù)據(jù)，利用V

7、LOOKUP函數(shù)，將“工資表”中的“工資級別”和“基本工資 ”列數(shù)據(jù)填寫完整。如圖3-1-1所示。知識講解：知識講解：知識講解：知識講解：解決方案：解決方案： 根據(jù)員工姓名在根據(jù)員工姓名在“員工信息表員工信息表”中查到對應(yīng)的工資級別中查到對應(yīng)的工資級別值填入值填入“工資表工資表”C列，然后根據(jù)工資級別在列，然后根據(jù)工資級別在“基本工資基本工資”表中查找到對應(yīng)的基本工資數(shù)據(jù)填入表中查找到對應(yīng)的基本工資數(shù)據(jù)填入“工資表工資表”D列即可。列即可。在本題中，人員姓名和工資級別、工資級別和基本工資均在本題中，人員姓名和工資級別、工資級別和基本工資均是精確匹配。是精確匹配。知識講解：知識講解：操作步驟：操

8、作步驟：第一步，選擇第一步，選擇C3單元，錄入公式單元，錄入公式“=VLOOKUP(B3,$F$10:$G$13,2,FALSE)”或或“=VLOOKUP(B3,$F$10:$G$13,2,0)”，確認(rèn)后，將此公式，確認(rèn)后，將此公式向下填充至向下填充至C6，如圖，如圖 3-1-2所示；所示；知識講解：知識講解：知識講解：知識講解：操作步驟：操作步驟：第二步，選擇第二步，選擇D3單元，錄入公式單元，錄入公式“=VLOOKUP(C3,$F$3:$G$7,2, FALSE )”或或“=VLOOKUP(C3,$F$3:$G$7,2,0)”，確認(rèn)后，將此公式，確認(rèn)后，將此公式向下填充至向下填充至D6，如

9、圖，如圖3-1-3所示。所示。知識講解：知識講解：知識講解：知識講解：例例2：已知某單位銷售額對應(yīng)的獎金提成比例如下表：已知某單位銷售額對應(yīng)的獎金提成比例如下表3-1-1所示所示:銷售額銷售額提成比例提成比例049990500099990.0310000199990.0620000399990.0840000以上0.12要求：根據(jù)銷售人員的銷售額統(tǒng)計數(shù)據(jù)計算其銷售獎金要求：根據(jù)銷售人員的銷售額統(tǒng)計數(shù)據(jù)計算其銷售獎金 （如表（如表3-1-2銷售提成計算表）銷售提成計算表）。知識講解：知識講解：解決方案：解決方案： 此題實質(zhì)是根據(jù)“銷售提成計算表”中的銷售額在“提成比例表”中查找出對應(yīng)的提成比例，

10、填入“銷售提成計算表”的“提成比例”列，然后用銷售額乘以提成比例計算出“提成獎金額”即可。 考慮到“銷售提成計算表”中的銷售額為數(shù)值型，而“提成比例表”中的銷售額為表示數(shù)值區(qū)間的字符串，因此，第一步，對提成比例表進(jìn)行改進(jìn)，添加 “參照銷售額”列，如圖3-1-4 所示，而且，由于vlookup的查找規(guī)則為“找到小于等于被查找值的最大值”，故參照值取提成比例對應(yīng)區(qū)間的最小數(shù)；知識講解：知識講解：知識講解：知識講解：第二步，選定G3單元，錄入公式“=VLOOKUP(F3,$B$3:$C$7,2)”，然后，將該公式向下填充至G6；如圖3-1-5 所示.知識講解：知識講解：任務(wù)實施：任務(wù)實施：1.1獲取

11、員工檔案數(shù)據(jù) 通常，企業(yè)的員工檔案是由人事部門負(fù)責(zé)管理的，財務(wù)部門在計算員工工資之前，必須先獲取人事檔案表。如果人事部門使用ACCESS等數(shù)據(jù)庫文件進(jìn)行員工檔案的管理，則可以使用EXCEL所提供的【數(shù)據(jù)】|【獲取外部數(shù)據(jù)】直接導(dǎo)入員工檔案，否則，手工設(shè)置員工檔案文件并錄入相關(guān)數(shù)據(jù)。本企業(yè)員工檔案表是人事部門提供的EXCEL文件“職工人事檔案”。任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：1.新建一個工作簿“工資管理系統(tǒng)”，在工作表sheet1中選定A1單元，然后單擊菜單欄【數(shù)據(jù)】|【自其他來源】|【來自XML數(shù)據(jù)導(dǎo)入】，如圖3-1-7所示。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟

12、：2.選取數(shù)據(jù)源。選取數(shù)據(jù)源。 在彈出的“選取數(shù)據(jù)源”對話框中，從“文件類型”下拉列表中選擇“所有文件”，將“查找范圍”中確定為“職工人事檔案”所在的文件夾“企業(yè)人事檔案”，選定列表中的目標(biāo)文件“職工人事檔案”，單擊【打開】按鈕或雙擊目標(biāo)文件“職工人事檔案”。如圖3-1-8所示。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：3.選擇表格在彈出的“選擇表格”對話框中，選擇列表中的第一項，單擊【確定】按鈕。如圖3-1-9所示。任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：4.導(dǎo)入數(shù)據(jù)導(dǎo)入數(shù)據(jù) 在彈出的“導(dǎo)入數(shù)據(jù)”對話框中，選定“該數(shù)據(jù)在工作簿中的顯示方式”為“表”，“數(shù)據(jù)的放置位置”為“現(xiàn)

13、有工作表”，單元格位置為“$A$1”，然后單擊【屬性】按鈕。 任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：5.連接屬性設(shè)置連接屬性設(shè)置 在彈出的“連接屬性”對話框中，對導(dǎo)入數(shù)據(jù)的屬性進(jìn)行設(shè)置，設(shè)置完畢，單擊【確定】按鈕，返回“導(dǎo)入數(shù)據(jù)”對話框。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：6在 “導(dǎo)入數(shù)據(jù)”對話框中單擊【確定】按鈕，導(dǎo)入數(shù)據(jù)后的EXCEL工作表如圖所示。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：7.根據(jù)需要設(shè)置導(dǎo)入數(shù)據(jù)表的字體、字號等格式，取消排序和篩選設(shè)置，將員工編號一列的數(shù)據(jù)類型設(shè)置為字符型，并重新錄入長度為4位的員工順序編碼

14、，然后將工作表名稱改為“員工檔案”，最后將工作簿以“員工工資管理系統(tǒng)”文件名保存。如圖所示。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：1.2創(chuàng)建基本工資標(biāo)準(zhǔn)表和崗位工資標(biāo)準(zhǔn)表創(chuàng)建基本工資標(biāo)準(zhǔn)表和崗位工資標(biāo)準(zhǔn)表 一般情況下，員工的基本工資和崗位工資是相對比較固定的，本企業(yè)員工的基本工資是根據(jù)不同部門制定，即同一部門的員工基本工資相同；崗位工資是和員工的崗位職務(wù)掛鉤，同一職務(wù)的員工崗位工資相同。因此，為了快速填入每位員工的基本工資和崗位工資，應(yīng)該先建立基本工資標(biāo)準(zhǔn)表和崗位工資標(biāo)準(zhǔn)表。任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：1.打開“員工工資管理系統(tǒng)”工作簿，選擇sheet2,在合適位置分別建立基本工

15、資標(biāo)準(zhǔn)表和崗位工資標(biāo)準(zhǔn)表，并錄入基本數(shù)據(jù)。如圖 所示。任務(wù)實施：任務(wù)實施：注：由于兩個表格數(shù)據(jù)不多，可以放在一個工作表中。注：由于兩個表格數(shù)據(jù)不多，可以放在一個工作表中。任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：2.修改sheet2工作表名稱為“工資標(biāo)準(zhǔn)”，然后保存。如圖修改工作表名稱任務(wù)實施：任務(wù)實施：1.3創(chuàng)建績效考核表創(chuàng)建績效考核表 一般企業(yè)獎金是工資構(gòu)成中必不可少的一個項目，但員工獎金與企業(yè)的獎勵制度有密切關(guān)系，可能每個月都會發(fā)生變動。在本企業(yè)，獎金是與績效掛鉤的，績效考核為“優(yōu)”的獎2000，“良”的獎1500，“中”的獎800，“合格”的獎500，“不合格”無獎勵。因此，可以創(chuàng)建一個

16、績效考核表，該表中可以包含員工的編號、姓名、所屬部門、績效考核結(jié)果及獎金字段，每月通過此表提供員工的獎金數(shù)據(jù)。任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：1. 打開“員工工資管理系統(tǒng)”工作簿，選擇sheet3,設(shè)計出“績效考核表”格式并錄入員工編號數(shù)據(jù)。由于績效考核按月進(jìn)行，因此日期的具體數(shù)據(jù)可以通過在E2單元輸入公式“=NOW()”獲得，選定E2，輸入公式，并將單元格式設(shè)置為合適的日期型，如圖所示。 任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：2. 利用VLOOKUP函數(shù)，從“員工檔案”中提取員工“姓名”、“所屬部門”數(shù)據(jù)。 選擇D4單元，插入公式：“=VLOOKUP(C4,員工

17、檔案!$A$2:$K$26,2,0)”，如圖3-1-18所示，然后，向下填充該公式至D28; 任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：選擇E3單元，插入公式“=VLOOKUP(C4,員工檔案!$A$2:$K$27,4,0)”，如圖所示。然后，向下填充該公式至E28。得到結(jié)果如圖所示。 任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：然后，向下填充該公式至E28。得到結(jié)果如圖所示。 任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：3. 利用數(shù)據(jù)有效性功能錄入員工當(dāng)月績效考核結(jié)果。選定F4:F28,選擇【數(shù)據(jù)】|【數(shù)據(jù)有效性】下拉列表中的【數(shù)據(jù)有效性】，在彈出的“數(shù)據(jù)有效性”對話框“設(shè)置”選項

18、卡“允許”下拉列表中選擇“序列”。如圖所示。 任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：在“來源”列表中錄入序列值“優(yōu)，良，中，合格，不合格”，如圖所示。然后單擊【確定】按鈕。 任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：績效考核表中，F(xiàn)列數(shù)據(jù)即可通過單擊單元格右側(cè)的下拉按鈕在列表中選擇依次錄入，如圖3-1-23所示。 任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：4. 利用IF函數(shù)，根據(jù)績效考核結(jié)果計算得出員工獎金。選定G4單元，錄入公式“=IF(F4=優(yōu),2000,IF(F4=良,1500,IF(F4=中,800,IF(F4=合格,500,0)”，按回車確認(rèn)后，向下填充至F28，即

19、可計算出員工獎金。如圖所示。 任務(wù)實施：任務(wù)實施：F4單元的公式公式計算結(jié)果任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：5. 修改sheet3工作表頁簽為“本月績效考核表”，然后保存。 任務(wù)實施：任務(wù)實施：1.4 創(chuàng)建員工考勤統(tǒng)計表。創(chuàng)建員工考勤統(tǒng)計表。 員工考勤統(tǒng)計表是用來統(tǒng)計員工的出勤情況的。一般在每月的月末進(jìn)行統(tǒng)計，然后根據(jù)公司的考勤制度和員工的出勤情況，計算員工的考勤扣款和加班工資，最后用在員工當(dāng)月工資的計算中。本公司考勤制度如下： 考勤類型分為遲到、病假、事假、曠工、婚假、產(chǎn)假、年假、加班； 任務(wù)實施：任務(wù)實施： 遲到扣款制度：月累計遲到10分鐘以內(nèi)不扣款，超過10分鐘但不超過半小時，扣

20、款15元，累計超過半小時但在1小時內(nèi)，扣款30元，超過1小時，扣款60元。 請假扣款制度：婚假、產(chǎn)假、年假工資照發(fā)；病假期間支付日基本工資的50%，事假期間不支付日基本工資；曠工扣款制度：曠工一天按日基本工資的1.2倍扣款。 任務(wù)實施：任務(wù)實施：每月天數(shù)均按30天計算。根據(jù)以上資料，在EXCEL中創(chuàng)建員工考勤統(tǒng)計表。 任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：1. 打開“員工工資管理系統(tǒng)”工作簿，插入新工作表，命名為“本月考勤統(tǒng)計”，然后在該工作表中錄入統(tǒng)計表框架并錄入員工編號。如圖3-1-25所示。本月考勤表包含的報表項目有員工編號、員工姓名、職務(wù)、基本工資、累計遲到（分鐘）、病假天數(shù)、事假天

21、數(shù)、曠工天數(shù)、加班時數(shù)、遲到扣款、事假扣款、曠工扣款、缺勤扣款、加班工資。 任務(wù)實施：任務(wù)實施：注意：表頭日期注意：表頭日期H2H2單元應(yīng)錄入公式單元應(yīng)錄入公式“=NOW()=NOW()”并設(shè)置其日期格式顯示為并設(shè)置其日期格式顯示為“* * * * *年年* * *月月”任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：2. 利用VLOOKUP函數(shù)從“員工檔案”表中提取員工姓名、所屬部門、職務(wù)數(shù)據(jù)填入本表，從“工資標(biāo)準(zhǔn)表”中提取基本工資填入本表。（1）選定B4:B28,錄入公式“=VLOOKUP(A4:A28,員工檔案!$A$2:$K$26,2,0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，

22、得到職工姓名；（2）選定C4:C28,錄入公式“=VLOOKUP(A4:A28,員工檔案!$A$2:$K$26,4,0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到所屬部門；（3）選定D4:D28,錄入公式“=VLOOKUP(A4:A28,員工檔案!$A$2:$K$26,5,0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到職務(wù)； 任務(wù)實施：任務(wù)實施： 注意：這里的公式均是以數(shù)組公式的注意：這里的公式均是以數(shù)組公式的方式錄入的，也可以先錄入每列第一方式錄入的，也可以先錄入每列第一個單元的公式，然后向下填充。個單元的公式，然后向下填充。任務(wù)實施：任務(wù)實施：實施步驟：實施

23、步驟：3. 手工錄入遲到（分鐘）、病假天數(shù)、事假天數(shù)、曠工天數(shù)、加班時數(shù)等統(tǒng)計數(shù)字。任務(wù)實施：任務(wù)實施：根據(jù)考勤結(jié)果手根據(jù)考勤結(jié)果手工輸入數(shù)據(jù)工輸入數(shù)據(jù)任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：4. 利用IF函數(shù)計算遲到扣款、病假扣款、事假扣款和曠工扣款、加班工資，并統(tǒng)計缺勤扣款。（1）選擇K4:K28，錄入公式“=IF(F4:F2860,60,IF(F4:F2830,30,IF(F4:F2810,15,0)”, 同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，計算得到遲到扣款金額。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（2）選擇L4:L28,錄入公式“=ROUND(E4

24、:E28/30/2*G4:G28+E4:E28/30*H4:H28,0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，計算得到病事假扣款。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（3）選擇M4:M28,錄入公式“=ROUND(E4:E28/30*1.2*I4:I28,0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，計算得到曠工扣款。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（4）選擇N4,錄入公式“=SUM(K4:M4)”按回車確認(rèn)，向下填充直到N28，得到缺勤扣款數(shù)。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（5）選擇

25、O4:O28,錄入公式“=ROUND(IF(D4:D28=經(jīng)理,0,IF(J4:J282,J4:J28*30,IF(J4:J280,J4:J28*25,0),0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，計算得到加班工資。然后保存文件。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：1.5 創(chuàng)建工資明細(xì)表并計算工資創(chuàng)建工資明細(xì)表并計算工資。 前面已經(jīng)將計算工資所需要的一些基本數(shù)據(jù)和每月需單獨提供的數(shù)據(jù)設(shè)計表格計算完成，下來即可按照公司工資制度所規(guī)定的工資項目和計算方法創(chuàng)建工資明細(xì)表，計算工資。任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：1. 打開“員工工資管理系統(tǒng)”工作簿，添加一張表頁，并將其

26、命名為“工資明細(xì)表”，在該工作表中設(shè)計工資明細(xì)表的框架，并錄入職工編號。工資明細(xì)表中包含的工資項目有：員工編號、姓名、所屬部門、基本工資、崗位工資、獎金、加班工資、缺勤扣款、應(yīng)發(fā)工資、代扣社會保險、應(yīng)稅所得、代扣個人所得稅、實發(fā)工資。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：2. 利用LOOKUP函數(shù)設(shè)計公式，分別從 “員工檔案”中提取姓名、所屬部門、職務(wù)數(shù)據(jù)；從“工資標(biāo)準(zhǔn)”中提取基本工資、崗位工資；從“本月績效考核表”中提取獎金；從“本月考勤統(tǒng)計”中提取加班工資、缺勤扣款等填入本表，然后計算出應(yīng)發(fā)工資。（1）選定B4:B28,錄入公式“=VLOOKUP(A4:A28,員工檔

27、案!$A$2:$K$26,2,0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到職工姓名；任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（3）選定D4:D28,錄入公式“=VLOOKUP(A4:A28,員工檔案!$A$2:$K$26,5,0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到職務(wù)；（4）選定E4:E28,錄入公式“=VLOOKUP(C4:C28,工資標(biāo)準(zhǔn)!$A$3:$B$6,2,0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到基本工資；任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（5）選定F4:F28,錄入公式“=VLOOKUP(D4:D28,工資標(biāo)準(zhǔn)!

28、$D$3:$E$9,2,0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到崗位工資；（6）選定G4:G28,錄入公式“=VLOOKUP(A4:A28,本月績效考核表!$C$4:$G$28,5,0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到獎金；（7）選定H4:H28,錄入公式“=VLOOKUP(A4:A28,本月考勤統(tǒng)計!$A$4:$O$28,15,0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到加班工資；任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（8）選定I4:I28,錄入公式“=VLOOKUP(A4:A28,本月考勤統(tǒng)計!$A$4:$O$28,15,

29、0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到缺勤扣款。（9）應(yīng)發(fā)工資由前幾項數(shù)據(jù)計算得來，選定J4:J28,錄入公式“=E4:E28+F4:F28+G4:G28+H4:H28-I4:I28”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到應(yīng)發(fā)合計。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：3. 根據(jù)單位所遵循的住房公積金和社會保險計算管理辦法，計算“代扣社會保險”。本公司住房公積金和社會保險計算管理辦法如下：應(yīng)由個人繳納住房公積金、養(yǎng)老保險、醫(yī)療保險和失業(yè)保險部分，統(tǒng)一由企業(yè)按月代扣代繳。三險一金的繳納基數(shù)為個人當(dāng)月的基本工資與崗位工資之和，繳納比例

30、分別為5%、6%、2%、1%。按照以上計算辦法設(shè)置代扣社會保險的計算公式：選定K4:K28，錄入公式“=(E4:E28+F4:F28)*0.14”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到代扣社會保險。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：4. 計算應(yīng)稅所得額及代扣個人所得稅公司按照現(xiàn)行稅收法律規(guī)定按月代扣代繳員工工資、薪金個人所得稅，起征點為3500元，即應(yīng)稅所得額超過3500，須繳納個人所得稅，否則，不繳個人所得稅。個人所得稅計算時使用超額累進(jìn)稅率。如果運用速算扣除法計算，其稅率及對應(yīng)速算扣除數(shù)如圖3-1-36所示，本單位代扣個人所得稅計算方法為：代扣個

31、人所得稅=應(yīng)稅所得額*適用稅率-速算扣除數(shù)其中，應(yīng)稅所得額=應(yīng)發(fā)工資-代扣社會保險-起征點任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟： 為此，可以先計算出應(yīng)稅所得額,選定L4:L28，錄入公式“=IF(J4:J28-K4:K28-35000,J4:J28-K4:K28-3500,0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到應(yīng)稅所得額。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟： 為此，可以先計算出應(yīng)稅所得額,選定L4:L28，錄入公式“=IF(J4:J28-K4:K28-35000,J4:J28-K4:K28-3500,0)”，同時按CTRL、

32、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到應(yīng)稅所得額。任務(wù)實施：任務(wù)實施：實施步驟：實施步驟： 然后，創(chuàng)建稅率查詢表，用IF函數(shù)與VLOOKUP函數(shù)相結(jié)合來計算代扣個人所得稅。（1）在員工工資管理系統(tǒng)中增加一個新工作表，命名為“個稅稅率查詢表”，在該工作表中設(shè)計個稅稅率查詢表如圖所示。任務(wù)實施：任務(wù)實施：添加添加“參考標(biāo)準(zhǔn)參考標(biāo)準(zhǔn)”列，列，且數(shù)據(jù)為該級距范圍且數(shù)據(jù)為該級距范圍內(nèi)的最小值！內(nèi)的最小值！任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（2）選中C3:E9,單擊【公式】|【定義名稱】，為該區(qū)域命名為“參考標(biāo)準(zhǔn)”，單擊【確定】按鈕。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（3）

33、單擊【工資明細(xì)表】頁簽，在“工資明細(xì)表”中，選中M4:M28,錄入公式“=IF(L4:L280,L4:L28*VLOOKUP(L4:L28,參考標(biāo)準(zhǔn),2,1)-VLOOKUP(L4:L28,參考標(biāo)準(zhǔn),3,1),0)”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到代扣個人所得稅。如圖所示。任務(wù)實施：任務(wù)實施：注意：在此處的注意：在此處的VLOOKUPVLOOKUP函數(shù)使用中，函數(shù)使用中，VLOOKUP(VLOOKUP(lookup_valuelookup_value, , table_arraytable_array, , col_index_numcol_index_num, , r

34、ange_lookuprange_lookup) ,) ,參數(shù)參數(shù) range_lookuprange_lookup 的值設(shè)為的值設(shè)為1 1或省略，表示近似匹配。或省略，表示近似匹配。任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：5.計算實發(fā)工資實發(fā)工資=應(yīng)發(fā)工資-代扣社會保險-代扣個人所得稅。在“工資明細(xì)表”中，選中N4:N28,錄入公式“=J4:J28-K4:K28-M4:M28”，同時按CTRL、SHIFT和ENTER鍵進(jìn)行確認(rèn)，得到實發(fā)工資。保存工作簿。任務(wù)實施：任務(wù)實施：小結(jié)評價：小結(jié)評價：熟練掌握工資管理中各類工資基礎(chǔ)數(shù)據(jù)表格的設(shè)置；熟練掌握工資管理中基本工資、崗位工資、工齡工資的來源分

35、析與生成；熟練掌握績效工資、獎金數(shù)據(jù)的計算、保險與公積金的計算、個人所得稅的計算；EXCEL在財務(wù)中的應(yīng)用在財務(wù)中的應(yīng)用模塊模塊任務(wù)任務(wù)三三 EXCEL工資管理中工資管理中 的應(yīng)用的應(yīng)用二二 員工工資的管理員工工資的管理任務(wù)任務(wù)引出：引出： 當(dāng)工資明細(xì)表制作完成后，企業(yè)根據(jù)工資數(shù)據(jù)安全、發(fā)放工資、當(dāng)工資明細(xì)表制作完成后，企業(yè)根據(jù)工資數(shù)據(jù)安全、發(fā)放工資、工資匯總分配等的需要在表中數(shù)據(jù)的基礎(chǔ)上進(jìn)一步進(jìn)行工資管理。工資匯總分配等的需要在表中數(shù)據(jù)的基礎(chǔ)上進(jìn)一步進(jìn)行工資管理。 為了保障工資明細(xì)表中數(shù)據(jù)的安全，一般除了每月需要手工錄入為了保障工資明細(xì)表中數(shù)據(jù)的安全，一般除了每月需要手工錄入的變動數(shù)據(jù)外，其

36、余根據(jù)公式計算產(chǎn)生的數(shù)據(jù)是不允許隨意修改的。的變動數(shù)據(jù)外，其余根據(jù)公式計算產(chǎn)生的數(shù)據(jù)是不允許隨意修改的。但是在系統(tǒng)默認(rèn)狀態(tài)下，當(dāng)選中公式所在單元時，公式會顯示在欄但是在系統(tǒng)默認(rèn)狀態(tài)下，當(dāng)選中公式所在單元時，公式會顯示在欄中并允許隨意修改，因此為了保證數(shù)據(jù)安全，需要對公式隱藏并加中并允許隨意修改，因此為了保證數(shù)據(jù)安全，需要對公式隱藏并加密；每月發(fā)放工資時，需要為每位員工提供工資條；會計人員每月密；每月發(fā)放工資時，需要為每位員工提供工資條；會計人員每月需要按部門或人員類別對工資進(jìn)行分類匯總以便按匯總結(jié)果進(jìn)行費需要按部門或人員類別對工資進(jìn)行分類匯總以便按匯總結(jié)果進(jìn)行費用分配；可以隨時提供對工資數(shù)據(jù)的

37、查詢。用分配；可以隨時提供對工資數(shù)據(jù)的查詢。任務(wù)分析：任務(wù)分析： 為了完成上述工資管理任務(wù)，可以選擇使用為了完成上述工資管理任務(wù)，可以選擇使用EXCEL系統(tǒng)提供的相應(yīng)功能來實現(xiàn)：利用數(shù)據(jù)保護(hù)功能實現(xiàn)公系統(tǒng)提供的相應(yīng)功能來實現(xiàn)：利用數(shù)據(jù)保護(hù)功能實現(xiàn)公式的隱藏及加密；利用式的隱藏及加密；利用VLOOKUP等函數(shù)來實現(xiàn)工資條等函數(shù)來實現(xiàn)工資條的生成；利用數(shù)據(jù)篩選、匯總功能或數(shù)據(jù)透視功能來實的生成；利用數(shù)據(jù)篩選、匯總功能或數(shù)據(jù)透視功能來實現(xiàn)工資數(shù)據(jù)的匯總、分配和查詢。現(xiàn)工資數(shù)據(jù)的匯總、分配和查詢。任務(wù)實施：任務(wù)實施：1.1工資數(shù)據(jù)的保護(hù)。工資數(shù)據(jù)的保護(hù)。 保護(hù)“工資明細(xì)表”中的所有工資數(shù)據(jù)和公式，可以

38、使用EXCEL所提供的【保護(hù)】功能來實現(xiàn)。任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：1. 隱藏公式。打開員工工資管理系統(tǒng)工作簿，將“工資明細(xì)表”作為當(dāng)前工作表，選中A2：N28區(qū)域，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊選擇【單元格格式】命令，在彈出的對話框中單擊【保護(hù)】選項卡，單擊選中【隱藏】復(fù)選框，單擊【確定】按鈕。 任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：2. 保護(hù)工作表。單擊【審閱】|【保護(hù)工作表】命令，在彈出的“保護(hù)工作表”對話框中輸入密碼，單擊【確定】按鈕，在彈出的“確認(rèn)密碼”對話框中再次錄入相同的密碼，然后單擊【確定】按鈕返回工作表。此時，被保護(hù)單元格的公式被隱藏

39、。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：注意：注意： 單元鎖定和隱藏公式只有在工作表保護(hù)狀態(tài)下才是有效的。若要撤銷公式隱藏，只需取消工作表保護(hù)。 單擊【審閱】|【撤銷工作表保護(hù)】命令，在彈出的“撤銷工作表保護(hù)”密碼框中輸入正確密碼，單擊【確定】按鈕即可。 任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：1.2制作工資條制作工資條 發(fā)放工資時一同發(fā)放給員工的工資條的內(nèi)容與工資明細(xì)表基本一致，不同的是每一條記錄都帶有表頭項目，因此需根據(jù)工資明細(xì)表重新制作工資條。任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：1. 打開員工工資管理系統(tǒng)工作簿，在“工資明細(xì)表”選中A4:N28區(qū)域，單擊【公式】|【定義名稱】，在彈

40、出的“新建名稱”對話框的“名稱”框中錄入該區(qū)域名稱“工資數(shù)據(jù)”，單擊【確定】按鈕。然后按員工編號的升序?qū)τ涗涍M(jìn)行排序。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：2. 在工作簿中插入一張工作表，并修改名稱為“工資條”，將該表移至“工資明細(xì)表”右邊。將“工資明細(xì)表”的A3:N3區(qū)域復(fù)制到 “工資條”表頁上的B2：O2區(qū)域，在“工資條”表中A2單元輸入“年月”；合并A1:O1區(qū)域，輸入“員工工資條”，并設(shè)置表頭格式。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：3. 在A3單元中錄入公式“=NOW()”,然后將該單元格格式設(shè)置為“*年*月”格式；在B3單元中錄入第一個

41、員工的編號“0001”；依次在C3到O3單元中分別錄入公式：“=VLOOKUP(B3,工資數(shù)據(jù),2,0)”;“=VLOOKUP(B3,工資數(shù)據(jù),3,0)”“=VLOOKUP(B3,工資數(shù)據(jù),4,0)”“=VLOOKUP(B3,工資數(shù)據(jù),5,0)”任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：“=VLOOKUP(B3,工資數(shù)據(jù),7,0)”“=VLOOKUP(B3,工資數(shù)據(jù),8,0)”“=VLOOKUP(B3,工資數(shù)據(jù),9,0)”“=VLOOKUP(B3,工資數(shù)據(jù),10,0)”“=VLOOKUP(B3,工資數(shù)據(jù),11,0)”“=VLOOKUP(B3,工資數(shù)據(jù),12,0)”“=VLOOKUP(B3,工資數(shù)

42、據(jù),13,0)”得到結(jié)果如圖所示。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：4. 選中A1:M3區(qū)域，向下填充至第75行（員工記錄為25條，每條記錄對應(yīng)的工資條數(shù)據(jù)占3行，共占75行。）結(jié)果如圖所示。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：5. 對制作好的工資條進(jìn)行打印設(shè)置，需要時即可打印輸出。對制作好的工資條進(jìn)行打印設(shè)置，需要時即可打印輸出。【問題思考問題思考】是否可用其他方法制作工資條？是否可用其他方法制作工資條？任務(wù)實施：任務(wù)實施：1.3工資的分類匯總工資的分類匯總 根據(jù)工資數(shù)據(jù)核算與管理的需要，每月要按職工所屬部門或人員類別等對工資數(shù)據(jù)進(jìn)行分類匯總，以便分配工資費用或

43、進(jìn)行費用的統(tǒng)計分析。本單位每月按所屬部門計算應(yīng)發(fā)工資和實發(fā)工資的部門總數(shù)和平均數(shù)。該任務(wù)可以采用系統(tǒng)提供的分類匯總功能實現(xiàn)，也可以使用透視表功能來實現(xiàn)。任務(wù)實施：任務(wù)實施：1. 采用分類匯總功能進(jìn)行分類匯總。采用分類匯總功能進(jìn)行分類匯總。實施步驟：實施步驟：（1）打開“員工工資管理”工作簿，插入一張新工作表，更改工作表名為“工資匯總表”。（2）復(fù)制“工資明細(xì)表”工作表中的數(shù)據(jù)到“工資匯總表”，對“工資匯總表”中的數(shù)據(jù)按“所屬部門”的升序進(jìn)行排序，如圖3-2-11所示。（注意：如果工資明細(xì)表“所屬部門”一列的數(shù)據(jù)由數(shù)組公式生成，則不允許按其重新排序，故可將“工資明細(xì)表”中的相應(yīng)的數(shù)組公式改為單元

44、公式形式，或在復(fù)制數(shù)據(jù)時采用“選擇性粘貼”|“值和數(shù)字格式”命令。）任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（3）選定A3:N28單元區(qū)域，單擊【數(shù)據(jù)】|【分類匯總】命令，在打開的【分類匯總】對話框中依次選擇“分類字段”為“所屬部門”，“匯總方式”為“求和”，“選定匯總項”為“應(yīng)發(fā)工資”和“實發(fā)工資”，然后單擊“確定”按鈕。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（4）繼續(xù)單擊【數(shù)據(jù)】|【分類匯總】命令，在打開的【分類匯總】對話框中依次選擇“分類字段”為“所屬部門”，“匯總方式”為“求平均值”，“選定匯總項”為“應(yīng)發(fā)工資”和“實發(fā)工資

45、”，取消“替換當(dāng)前分類匯總”復(fù)選框中的對勾，然后單擊“確定”按鈕。 任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：說明：說明： 在此分類匯總結(jié)果中，即可以看到每個部門應(yīng)發(fā)工資和實發(fā)工資的匯總數(shù)據(jù)，也可以看到每個部門應(yīng)發(fā)工資和實發(fā)工資的平均數(shù)據(jù)。 如果要取消分類匯總的結(jié)果，只需選定分類匯總數(shù)據(jù)區(qū)域，單擊【數(shù)據(jù)】|【分類匯總】命令，在打開的【分類匯總】對話框中單擊“全部刪除”按鈕即可。 任務(wù)實施：任務(wù)實施：2. 利用透視表功能實現(xiàn)分類匯總。利用透視表功能實現(xiàn)分類匯總。實施步驟：實施步驟：（1）打開工資明細(xì)表，選定A3:A28單元區(qū)域，單擊【插入】|【數(shù)據(jù)透視表】命

46、令，如圖1-2-16所示，打開“創(chuàng)建數(shù)據(jù)透視表”對話框。 任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（2）在“創(chuàng)建數(shù)據(jù)透視表”對話框中，確認(rèn)“請選擇要分析的數(shù)據(jù)區(qū)域”選項正確（如原來所選區(qū)域有誤，可在此重新選擇），然后確定“選擇放置數(shù)據(jù)透視表的位置”為“新工作表”（也可放在現(xiàn)有工作表中），然后單擊“確定”按鈕。得到結(jié)果如圖。任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（3）在“數(shù)據(jù)透視表字段列表”選項卡中，先在“選擇要添加到報表的字段”的選項列表中選定“所屬部門”，系統(tǒng)將其確認(rèn)為行標(biāo)簽，如圖3-2-19所示；繼續(xù)依次選定需要匯總的項目“應(yīng)發(fā)

47、工資”和“實發(fā)工資”，系統(tǒng)將其確認(rèn)為列標(biāo)簽，匯總結(jié)果則隨之顯示，如圖 任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（4）在表中數(shù)據(jù)區(qū)域以外空白處單擊或單擊“數(shù)據(jù)透視表字段列表”的關(guān)閉按鈕，“數(shù)據(jù)透視表字段列表”被關(guān)閉；如需對“數(shù)據(jù)透視表”內(nèi)容進(jìn)行修改，可右擊任意數(shù)據(jù)區(qū)域在彈出的快捷菜單中選擇執(zhí)行相應(yīng)命令，如圖。 任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（5）如要對應(yīng)發(fā)工資按部門平均值進(jìn)行匯總，可右擊應(yīng)發(fā)工資列數(shù)據(jù)，在彈出的快捷菜單中依次單擊【數(shù)據(jù)匯總依據(jù)】|【平均值】，如圖。 任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：任務(wù)實施：實施步驟：實施步驟：（6）如需將B4及C4的顯示名稱分別改為“應(yīng)發(fā)工資匯總”、“實發(fā)工資匯總”，可先雙擊B4單元或右擊