3-項(xiàng)目三-excel在財(cái)務(wù)中的應(yīng)用

1、項(xiàng)目三Excel在工資核算中的應(yīng)用Excel在財(cái)務(wù)中的應(yīng)用 學(xué)習(xí)目標(biāo)知識(shí)目標(biāo)了解工資的構(gòu)成熟練掌握 VLOOKUP函數(shù)的格式能力目標(biāo)掌握如何利用 Excel輸入工資數(shù)據(jù)掌握如何利用 Excel進(jìn)行工資數(shù)據(jù)的處理與查詢掌握如何利用 Excel編制工資費(fèi)用分配表掌握如何利用 Excel進(jìn)行工資賬務(wù)處理 學(xué)習(xí)內(nèi)容任務(wù)一 輸入工資數(shù)據(jù)任務(wù)二 數(shù)據(jù)處理與查詢?nèi)蝿?wù)三 工資費(fèi)用分配任務(wù)四 工資賬務(wù)處理任務(wù)小結(jié)技能訓(xùn)練 任務(wù)一 輸入工資數(shù)據(jù)基本知識(shí)一.工資總額的構(gòu)成1.計(jì)時(shí)工資2.計(jì)件工資3.加班加點(diǎn)工資4.經(jīng)常性獎(jiǎng)金5.工資性津貼和補(bǔ)貼6.特殊情況下支付的工資二.應(yīng)發(fā)工資與實(shí)發(fā)工資的計(jì)算應(yīng)發(fā)工資基本工資加班

2、工資獎(jiǎng)金津貼其他 實(shí)發(fā)工資應(yīng)發(fā)工資社會(huì)保險(xiǎn)費(fèi)個(gè)人所得稅其他代扣款項(xiàng) 任務(wù)一輸入工資數(shù)據(jù)工作情境與分析 江西利民責(zé)任有限公司成立于2000年1月1日，設(shè)立了行政人事部、財(cái)務(wù)部、采購(gòu) 部、生產(chǎn)部、質(zhì)檢部、營(yíng)銷部六個(gè)部門，共11人，由人事部門提供了各員工的基本信息， 見表3-1。根據(jù)工資核算的操作流程，李躍華要完成的主要任務(wù)包括：輸入工資數(shù)據(jù)工資數(shù)據(jù) 的處理與查詢工資費(fèi)用分配工資賬務(wù)處理。 任務(wù)一輸入工資數(shù)據(jù) 任務(wù)一輸入工資數(shù)據(jù)任務(wù)實(shí)施步驟建立職工基本信息表 （1）新建 Excel工作簿，命名為 “工資核算表”，將Sheet1命名為 “職工基本信息 表”，如圖3-1所示，并據(jù)此設(shè)置職工基本信息的項(xiàng)目

3、。 （2）由于部門的數(shù)目不多，采用下列表的形式輸入。基本方法為：選定部門單元格區(qū) 域B3：B24，然后選擇 “數(shù)據(jù)數(shù)據(jù)工具”功能區(qū)中的 “數(shù)據(jù)有效性”命令，出現(xiàn) “數(shù)據(jù) 有效性”對(duì)話框，在 “設(shè)置”選項(xiàng)卡中 “有效性條件”下的條件中選擇 “序列”，并在 “來源”中輸入部門的名稱，如圖3-2所示。單擊 “確定”按鈕后，結(jié)果如圖3-3所示。 （3）在 “性別”項(xiàng)目欄也采用數(shù)據(jù)序列表的形式產(chǎn)生數(shù)據(jù)，方法如上。其他數(shù)據(jù)的輸 入采用直接輸入法，同時(shí)注意在 “入職時(shí)間”項(xiàng)目欄輸入日期時(shí)，把格式調(diào)整為如圖3-3 所示的日期格式。 任務(wù)一輸入工資數(shù)據(jù)小提示：在 “數(shù)據(jù)有效性”對(duì)話框的 “來源”中輸入數(shù)據(jù)時(shí)，兩

4、個(gè)序列之間用 逗號(hào)隔開，并且逗號(hào)一定是半角狀態(tài)下的。 同時(shí)，在 “數(shù)據(jù)有效性”對(duì)話框的 “來源”中的數(shù)據(jù)也可以通過引用單元格的方式獲取。 任務(wù)一輸入工資數(shù)據(jù)2建立工資初始信息表 在 “工資核算表”工作簿中，將Sheet2命名為 “工資初始信息表”。根據(jù)企業(yè)工資核 算制度，建立職工工資標(biāo)準(zhǔn)表，如圖3-4所示。3建立職工考勤表 在 “工資核算表”工作簿中，將Sheet3命名為 “職工考勤表”。根據(jù)人事部門的考勤 記錄，匯總得到職工考勤表，如圖3-5所示。 任務(wù)一輸入工資數(shù)據(jù)4建立銷售業(yè)績(jī)表 在 “工資核算表”工作簿中，插入工作表，重命名為 “銷售業(yè)績(jī)表”。從企業(yè)銷售部門得到各銷售人員的銷售業(yè) 績(jī)，

5、完成銷售業(yè)績(jī)表，如圖3-6所示。5建立職工工資表 在 “工資核算表”工作簿中，插入工作表，重命名為 “職工工資表”，如圖3-7所示。根據(jù)工資項(xiàng)目的組成設(shè)置工資表項(xiàng)目，姓名、部門、職 工類別、性別、年齡數(shù)據(jù)通過 VLOOKUP函數(shù)從 “職工基本信息表”自動(dòng)獲取。 任務(wù)二數(shù)據(jù)處理與查詢基本知識(shí)一.五險(xiǎn)一金的計(jì)提 “五險(xiǎn)”指的是五種保險(xiǎn)，包括養(yǎng)老保險(xiǎn)、醫(yī)療保險(xiǎn)、失業(yè)保險(xiǎn)、工傷保險(xiǎn)和生育保 險(xiǎn)；“一金”指的是住房公積金。其中養(yǎng)老保險(xiǎn)、醫(yī)療保險(xiǎn)和失業(yè)保險(xiǎn)這三種保險(xiǎn)由企業(yè)和個(gè)人共同繳納，工傷保險(xiǎn)和生育保險(xiǎn)完全由企業(yè)承擔(dān)，個(gè)人不需要繳納。表3-2為職 工五險(xiǎn)一金繳納比例參考表。注意：五險(xiǎn)一金是按照職工月繳費(fèi)

6、工資的一定繳納比例繳納的，月繳費(fèi)工資一般是指 職工當(dāng)月的工資總額，但最低不低于當(dāng)?shù)卦缕骄べY的60，最高不高于當(dāng)?shù)卦缕骄べY的300。 任務(wù)二數(shù)據(jù)處理與查詢二.個(gè)人所得稅的計(jì)算 職工的工資收入所得，按稅法規(guī)定，超過3500元時(shí)，超出的部分按稅率表繳納個(gè)人所得稅，所納稅金額由公司代扣代繳。工資、薪金所得個(gè)人所得稅級(jí)累進(jìn)稅率表見表3-3。個(gè)人所得稅的計(jì)算公式為： 個(gè)人所得稅（工資五險(xiǎn)一金起征點(diǎn)金額）*稅率 速算扣除數(shù) 任務(wù)二數(shù)據(jù)處理與查詢工作情境與分析 根據(jù)公司職工薪資管理制度的規(guī)定，江西利民責(zé)任有限公司職工的工資由以下幾部分 構(gòu)成： （1）基本工資和崗位工資。與職工工作崗位相關(guān)，具體標(biāo)準(zhǔn)如圖3-

7、4所示（2）補(bǔ)貼。即公司提供給職工的伙食補(bǔ)貼，為職工基本工資和崗位工資的 20。 （3）獎(jiǎng)金。營(yíng)銷部職工按銷售業(yè)績(jī)的1發(fā)放獎(jiǎng)金，其他部門職工獎(jiǎng)金為：公司經(jīng)理 1000元，部門經(jīng)理800元，管理人員500元，其他職工300元。 （4）加班工資。按加班時(shí)間計(jì)算，每小時(shí)30元。 （5）全勤獎(jiǎng)。當(dāng)月沒有病事假，可發(fā)放全勤獎(jiǎng)200元。如果有病假，按日基本工資的 一半扣款，事假則扣除當(dāng)日的基本工資。 公司幫職工繳納了醫(yī)療保險(xiǎn)，其中個(gè)人繳納比例為2，公司繳納比例為6。 李躍華在了解了公司薪資制度后，著手開始編制職工本月的工資表，并為每名職工做了工資條，以便大家了解個(gè)人的工資發(fā)放情況。 任務(wù)二數(shù)據(jù)處理與查詢?nèi)?/p>

8、務(wù)實(shí)施步驟1. “基本工資”“崗位工資”數(shù)據(jù)的輸入 （1） “基本工資”數(shù)據(jù)單元格 G4中輸入公式 如圖3-8所示。（2） “崗位工資”數(shù)據(jù)單元格 H4中輸入公式 如圖3-9所示。 任務(wù)二數(shù)據(jù)處理與查詢2.“補(bǔ)貼”數(shù)據(jù)的輸入根據(jù)公司規(guī)定，補(bǔ)貼為基本工資和崗位工資的20。在“補(bǔ)貼”數(shù)據(jù)單元格I4中輸 入如圖3-10所示。本列其他單元格公式通過復(fù)制單元格I4公式得到。3.“獎(jiǎng)金”數(shù)據(jù)的輸入在“獎(jiǎng)金”數(shù)據(jù)單元格J4中輸入如圖3-11所示。本列其他單元格公式通過復(fù)制單元格J4公式得到。 任務(wù)二數(shù)據(jù)處理與查詢4“加班工資”數(shù)據(jù)的輸入 根據(jù)公司規(guī)定，加班工資為每小時(shí)30元。在 “加班工資”數(shù)據(jù)單元格K4中

9、輸入如圖3-12所示。5“缺勤扣款”數(shù)據(jù)的輸入 根據(jù)公司規(guī)定，病假按日基本工資的一半扣款，事假則扣除當(dāng)日的基本工資，當(dāng)月沒 有病事假，可發(fā)放全勤獎(jiǎng)200元。 在 “缺勤扣款”數(shù)據(jù)單元格L4中輸入公式如圖3-13所示。 任務(wù)二數(shù)據(jù)處理與查詢6 “全勤獎(jiǎng)”數(shù)據(jù)的輸入 在 “全勤獎(jiǎng)”數(shù)據(jù)單元格 M4中輸入公式 如圖3-14所示。7“應(yīng)發(fā)工資”數(shù)據(jù)的輸入 在 “應(yīng)發(fā)工資”數(shù)據(jù)單元格 N4中輸入公式 如圖3-15所示。 任務(wù)二數(shù)據(jù)處理與查詢8“醫(yī)療保險(xiǎn)”數(shù)據(jù)的輸入 根據(jù)公司規(guī)定，醫(yī)療保險(xiǎn)個(gè)人繳納部分為應(yīng)發(fā)工資的2。在 “醫(yī)療保險(xiǎn)”數(shù)據(jù)單元 格 O4中輸入公式 如圖3-16所示。9“個(gè)人所得稅”數(shù)據(jù)的輸入

10、 個(gè)人所得稅根據(jù)應(yīng)發(fā)工資扣除個(gè)人繳納五險(xiǎn)一金后的數(shù)額來計(jì)算。在 “個(gè)人所得稅” 數(shù)據(jù)單元格P4中輸入公式 如圖3-17所示。 任務(wù)二數(shù)據(jù)處理與查詢10“實(shí)發(fā)工資”數(shù)據(jù)的輸入 在 “實(shí)發(fā)工資”數(shù)據(jù)單元格 Q4中輸入公式 如圖3-18所示。 任務(wù)二數(shù)據(jù)處理與查詢11工資數(shù)據(jù)的查詢 （1）利用篩選功能進(jìn)行工資數(shù)據(jù)的查詢 利用數(shù)據(jù)篩選的功能，則可以只顯示查詢的數(shù)據(jù)。具體操作為：在數(shù)據(jù)區(qū)域中單擊單元格區(qū)域 A3：Q3，然后選擇 “數(shù)據(jù)排序和篩 選”功能區(qū)中的 “篩選”命令，則每一列數(shù)據(jù)頂端的項(xiàng)目名稱右側(cè)會(huì)出現(xiàn)一個(gè)下拉列表按 鈕。 根據(jù)需要從中選擇需要顯示的數(shù)據(jù)要求后，符合條件的數(shù)據(jù)就被顯示出來了。如圖3

11、19所示。比如，要想查找財(cái)務(wù)部人員的工資，則在復(fù)選框中只選擇 “財(cái)務(wù)部”，然后單擊 “確 定”按鈕，查詢結(jié)果如圖3-20所示。如果想取消篩選，則點(diǎn)擊數(shù)據(jù)區(qū)域任一單元格，再次選擇 “數(shù)據(jù)排序和篩選”功能 區(qū)中的 “篩選”命令，即可取消。 任務(wù)二數(shù)據(jù)處理與查詢工作情境與分析 任務(wù)二數(shù)據(jù)處理與查詢（2）利用高級(jí)篩選功能進(jìn)行工資數(shù)據(jù)的查詢首先，進(jìn)行篩選條件的設(shè)置，比如查詢 生產(chǎn)部應(yīng)發(fā)工資小于3000元的人員工資數(shù)據(jù)，條件為 “部門生產(chǎn)部”，且 “應(yīng)發(fā)工資 3000”，如圖3-21所示。 然后選擇 “數(shù)據(jù)排序和篩選”功能區(qū)中的 “高級(jí)”命令，彈出 “高級(jí)篩選”對(duì)話 框，設(shè)置 “條件區(qū)域”，如圖3-22所

12、示。 最后單擊 “確定”按鈕，結(jié)果如圖3-23所示。小提示：設(shè)置條件區(qū)域時(shí)，字段名在上，條件在下，同時(shí)平行的條件為 “并且” 的意思，一個(gè)字段名下現(xiàn)列的條件為 “或”的意思。 任務(wù)二數(shù)據(jù)處理與查詢 任務(wù)二數(shù)據(jù)處理與查詢12工資條的制作 （1）打開 “工資核算表”工作簿，新建工作表，重命名為 “職工工資條”。 （2）選中單元格 A1，公式 “=F（MOD（ROW（），3）=1，職工工資表！A3，IF （MOD（ROW（），3）=2,OFFSET（職工工資表！A3，ROW（）31，0），）”。 （3）選中單元格 A1，鼠標(biāo)置于單元格右下角，當(dāng)箭頭變成十字形時(shí)，則向右拉至 Q1 單元格。然后再選中

13、A1：Q1向下拉，直至顯示出全部職工的工資條。 （4）選中單元格區(qū)域A1：Q2，選擇添加邊框工具欄下拉菜單 “所有框線”，如圖3-24所示。（5）選中 A3：Q3單元格，然后選擇 “合并后居中”，再選中 A1：Q3向下拉，給所有 職工工資條添加完邊框。如圖3-25所示。 任務(wù)二數(shù)據(jù)處理與查詢 任務(wù)三工資費(fèi)用分配基本知識(shí)一.職工福利費(fèi)1福利費(fèi)的開支范圍2提取福利費(fèi)的作用 二.工會(huì)經(jīng)費(fèi)1工會(huì)經(jīng)費(fèi)的來源2工會(huì)經(jīng)費(fèi)上繳的管理3工會(huì)經(jīng)費(fèi)的列支4暫不計(jì)提工會(huì)經(jīng)費(fèi)的情況三.職工教育經(jīng)費(fèi) 任務(wù)三工資費(fèi)用分配工作情境與分析 為了保障員工的經(jīng)濟(jì)利益，吸引優(yōu)秀員工為企業(yè)工作，江西利民責(zé)任有限公司決定： 按工資總額的

14、14計(jì)提福利費(fèi)，以便滿足各項(xiàng)福利開支；按工資總額的2計(jì)提工會(huì)經(jīng) 費(fèi)，用于開展員工活動(dòng)，豐富員工業(yè)余生活；按工資總額的1.5計(jì)提職工教育經(jīng)費(fèi)，用 于員工培訓(xùn)、繼續(xù)教育方面的開支。工資費(fèi)用的分配是財(cái)務(wù)工作中非常重要的一項(xiàng)工作，財(cái)務(wù)人員要根據(jù)各部門的人員工 資以及工作性質(zhì)進(jìn)行匯總分配，記入不同的會(huì)計(jì)科目。江西利民責(zé)任有限公司設(shè)計(jì)的工資 費(fèi)用分配表見表3-4。 任務(wù)三工資費(fèi)用分配 任務(wù)三工資費(fèi)用分配任務(wù)實(shí)施步驟1設(shè)計(jì)工資費(fèi)用分配表格式 打開 “工資核算表”工作簿，新建工作表，重命名為 “工資費(fèi)用分配表”，輸入應(yīng)發(fā) 工資總額、職工福利費(fèi)、工會(huì)經(jīng)費(fèi)、教育經(jīng)費(fèi)及社會(huì)保險(xiǎn)費(fèi)等項(xiàng)目，設(shè)置及調(diào)整格式,完成相應(yīng)操作

15、后，顯示結(jié)果如圖3-29所示。 任務(wù)三工資費(fèi)用分配2輸入工資分配表數(shù)據(jù) （1）“應(yīng)發(fā)工資總額”數(shù)據(jù)的輸入。 打開 “工資核算表”工作簿中 “職工工資表”工作表，選中要進(jìn)行數(shù)據(jù)分析的區(qū)域A3：Q25，選擇 “插入表格”功能區(qū)中的 “數(shù)據(jù)透視表”命令，選擇 “數(shù)據(jù)透視表”，彈 出 “創(chuàng)建數(shù)據(jù)透視表”對(duì)話框，在 “選擇放置數(shù)據(jù)透視表的位置”選項(xiàng)組中選擇 “新工作 表”選項(xiàng)，如圖3-30所示。 單擊 “確定”按鈕，進(jìn)入數(shù)據(jù)透視表界面，將 “應(yīng)發(fā)工資”項(xiàng)目拖到數(shù)據(jù)透視表布局 中的 “數(shù)值”區(qū)域，將 “部門”項(xiàng)目拖到 “行標(biāo)簽”處。數(shù)據(jù)透視表生成結(jié)果如圖3-31所示。設(shè)置完成后將工作表名改為 “應(yīng)發(fā)工資總

16、額匯總表”。 任務(wù)三工資費(fèi)用分配 任務(wù)三工資費(fèi)用分配 選擇C3單元格，輸入 “=”，點(diǎn)擊 “應(yīng)發(fā)工資總額匯總表”，選擇生產(chǎn)部對(duì)應(yīng)的 “應(yīng) 發(fā)工資總額”單元格，按回車鍵確認(rèn)之后， “應(yīng)發(fā)工資總額匯總表”中的數(shù)據(jù)就會(huì)引用過 來，其他部門的應(yīng)發(fā)工資總額采用同樣的方法去引用 “應(yīng)發(fā)工資總額匯總表”的數(shù)據(jù)。可輸入求和公式得到合計(jì)數(shù)，結(jié)果如圖3-32所示。 任務(wù)三工資費(fèi)用分配（2）“職工福利費(fèi)”數(shù)據(jù)的輸入。 在 “職工福利費(fèi)”單元格D3中輸入公式 “C3*14”，本列其他單元格公式通過 復(fù)制單元格D3公式得到。（3）“工會(huì)經(jīng)費(fèi)”數(shù)據(jù)的輸入。 在 “工會(huì)經(jīng)費(fèi)”單元格E3中輸入公式 “C3*2”，本列其他單元

17、格公式通過復(fù)制 單元格公式得到。 （4）“教育經(jīng)費(fèi)”數(shù)據(jù)的輸入。 在 “教育經(jīng)費(fèi)”單元格F3中輸入公式 “C3*1.5”，本列其他單元格公式通過復(fù) 制單元格F3公式得到。（5）“社會(huì)保險(xiǎn)費(fèi)”數(shù)據(jù)的輸入。 在 “社會(huì)保險(xiǎn)費(fèi)”單元格G3中輸入公式 “C3*2”，本列其他單元格公式通過復(fù)制單元格G3公式得到。以上結(jié)果如圖3-33所示。 任務(wù)三工資費(fèi)用分配 任務(wù)四工資賬務(wù)處理基本知識(shí)一.職工工資的核算借：生產(chǎn)成本 制造費(fèi)用 管理費(fèi)用 銷售費(fèi)用 貸：應(yīng)付職工薪酬工資二.職工福利費(fèi)的核算借：生產(chǎn)成本 制造費(fèi)用 管理費(fèi)用 銷售費(fèi)用 貸：應(yīng)付職工薪酬職工福利費(fèi) 任務(wù)四工資賬務(wù)處理三.職工工會(huì)經(jīng)費(fèi)、教育經(jīng)費(fèi)的核

18、算借：生產(chǎn)成本 制造費(fèi)用 管理費(fèi)用 銷售費(fèi)用 貸：應(yīng)付職工薪酬工會(huì)經(jīng)費(fèi) 職工教育經(jīng)費(fèi)四.職工社會(huì)保險(xiǎn)費(fèi)的核算 借：生產(chǎn)成本 制造費(fèi)用 管理費(fèi)用 銷售費(fèi)用 貸：應(yīng)付職工薪酬社會(huì)保險(xiǎn)費(fèi) 任務(wù)四工資賬務(wù)處理工作情境與分析 月底，李躍華像往常一樣，根據(jù)工資費(fèi)用分配表進(jìn)行了相應(yīng)的賬務(wù)處理。如下所示： 借：生產(chǎn)成本 制造費(fèi)用 管理費(fèi)用 銷售費(fèi)用 貸：應(yīng)付職工薪酬工資 福利費(fèi) 社會(huì)保險(xiǎn)費(fèi) 工會(huì)及教育經(jīng)費(fèi) 為了能在Excel中進(jìn)行賬務(wù)處理，需要設(shè)計(jì)常用的記賬憑證格式，并在Excel中進(jìn)行 編制記賬憑證的工作。 任務(wù)四工資賬務(wù)處理任務(wù)實(shí)施步驟1設(shè)置記賬憑證格式 打開 “工資核算表”工作簿，新建工作表，重命名為 “工資賬務(wù)處理”，設(shè)置類別編 號(hào)、憑證日期、附件、摘要、科目編碼、總賬科目、明細(xì)科目、借方金額、貸方金額、制 單人、審核人及記賬人欄目，在憑證右方設(shè)置借方金額合計(jì)數(shù)及貸方金額合計(jì)數(shù)兩欄，如 圖3-34所示。 任務(wù)四工資賬務(wù)處理2編制記賬憑證 根據(jù)工資費(fèi)用分配表，編制會(huì)計(jì)分錄，完成記賬憑證的錄入，