《Excel在財務會計中的應用（第2版）》項目四 工資管理課件

《Excel在財務會計中的應用》項目四

工資管理《Excel在財務會計中的應用》《Excel在財務會計中的應用》模塊二工資管理項目情境

完成了基于Excel賬務處理模塊的電算化工作之后，李悅深刻體會到Excel電算化的快速和高效，同時他的工作也得到了領(lǐng)導們的一致認可。但李悅并不滿足，為了隨時了解職工工資情況，及時對工資數(shù)據(jù)進行匯總分析，便于公司進行薪酬控制與管理，李悅開始嘗試用Excel設計東方公司的工資管理系統(tǒng)。

他首先整理出與工資管理模塊相關(guān)的部門設置情況、員工基本情況及公司崗位設置情況等信息，具體如下：

東方公司現(xiàn)設8個部門，包括：行政部、財務部、技術(shù)部、采購部、銷售部、生產(chǎn)一車間、生產(chǎn)二車間、生產(chǎn)三車間。職工類別6類，包括：總經(jīng)理、部門經(jīng)理、管理人員、采購人員、銷售人員、生產(chǎn)人員。員工人數(shù)25名?！禘xcel在財務會計中的應用》模塊二工資管理知識鏈接

新個人所得稅法于2019年1月1日起全面實施，主要變化有：

（1）建立對綜合所得稅按年計稅的制度此次改革，將工資薪金、勞務報酬、稿酬和特許權(quán)使用費4項勞務性所得納入綜合征稅范圍，在年計稅的基礎上，實行“代扣代繳、自行申報、匯算清繳、多退少補”的征管制度。（2）適當提高基本減除費用標準將綜合所得的基本減除費用從原來的3500元/月提高至5000元/月（每年6萬元）。（3）首次設立專項附加扣除在提高基本減除費用的基礎上增加子女教育、繼續(xù)教育、大病醫(yī)療、住房貸款利息或者住房租金、贍養(yǎng)老人等支出，進一步增強稅制的公平性。（4）調(diào)整優(yōu)化個人所得稅稅率結(jié)構(gòu)以改革前工薪所得3%-45%七級超額累進稅率為基礎，擴大3%、10%、20%三檔低稅率的級距，縮小25%稅率的級距，30%、35%、45%三檔較高稅率級距不變?！禘xcel在財務會計中的應用》模塊二工資管理【項目分析】12345錄入員工基礎工資數(shù)據(jù)編制工資結(jié)算單工資數(shù)據(jù)的查詢與統(tǒng)計分析編制工資費用分配表打印工資發(fā)放條《Excel在財務會計中的應用》模塊二工資管理項目目標能力目標學會使用Excel設置工資項目公式的能力學會使用Excel篩選功能進行工資數(shù)據(jù)查詢的能力學會使用Excel數(shù)據(jù)透視表進行工資數(shù)據(jù)分析的能力素質(zhì)目標培養(yǎng)學生全面嚴謹?shù)膽B(tài)度培養(yǎng)學生廉潔自律的立場知識目標了解工資核算的業(yè)務處理流程掌握工資項目的組成及計算方法熟練掌握IF函數(shù)、OR函數(shù)等函數(shù)的使用方法

《Excel在財務會計中的應用》模塊二工資管理任務4編制工資費用分配表2.1設置工資結(jié)算單格式2.2設置工資項目3.1工資表數(shù)據(jù)排序3.2工資表數(shù)據(jù)分類匯總3.3工資表數(shù)據(jù)篩選3.4工資表數(shù)據(jù)統(tǒng)計分析4.1設置“工資費用分配表”格式4.2設置“工資費用分配表”公式任務3工資數(shù)據(jù)的查詢與統(tǒng)計分析任務2編制工資結(jié)算單任務1錄入員工基礎工資數(shù)據(jù)1.1建立基本工資信息表1.2建立專項附加扣除表任務5打印工資條5.1利用排序功能生成工資條后打印5.2頁面設置后打印工資條《Excel在財務會計中的應用》模塊二工資管理建立基本工資信息表建立專項附加扣除表任務1錄入員工基礎工資數(shù)據(jù)《Excel在財務會計中的應用》模塊二工資管理任務1.1建立基本工資信息表一、定義基本工資信息表格（1）打開Excel2010，在D盤“東方公司”文件夾下建立“1905”文件夾。在“1905”文件夾下新建名為“工資管理.xlsx”的工作簿。（2）雙擊工作表標簽Sheet1，輸入一個新的工作表表名“基本工資信息表”。（3）選擇A1單元格，輸入工作表標題“東方公司員工基本工資表”。（4）選中單元格A1:E1，設置字體為“宋體”，字號為“12”，加粗。選擇對齊方式為“跨列居中”。（5）在A2至E2單元格內(nèi)分別錄入“員工編號”、“員工姓名”、“所屬部門”、“職工類別”、“基本工資”項目，設置其字體為“宋體”，字號為“12”，加粗，黃色底紋。設置A至E列的對齊方式為“居中”。（6）設置“員工編號”所在A列的格式為“文本”類型，設置“基本工資”所在E列的格式為“會計專用”類型，小數(shù)位數(shù)為2，貨幣符號無?！禘xcel在財務會計中的應用》模塊二工資管理二、定義單元格的有效性1.對“所屬部門”所在列添加有效性控制（1）選擇C列，單擊功能區(qū)中的“數(shù)據(jù)”—“數(shù)據(jù)工具”—“數(shù)據(jù)有效性”按鈕，打開“數(shù)據(jù)有效性”對話框。（2）在“設置”選項卡中的“允許”下拉列表框中選擇“序列”選項；在“來源”框中輸入“行政部，財務部，技術(shù)部，采購部，銷售部，生產(chǎn)一車間，生產(chǎn)二車間，生產(chǎn)三車間”。（3）最后單擊“確定”按鈕，則完成對“所屬部門”數(shù)據(jù)有效性的設置，如圖4—3所示。

2.對“職工類別”所在列添加有效性控制同理，設置D列的數(shù)據(jù)有效性為序列“總經(jīng)理,部門經(jīng)理,管理人員,采購人員,銷售人員,生產(chǎn)人員”。圖4—3設置“所屬部門”數(shù)據(jù)有效性任務1.1建立基本工資信息表《Excel在財務會計中的應用》模塊二工資管理三、錄入基本工資信息表方法一：直接輸入法分別錄入“員工編號”、“員工姓名”、“所屬部門”、“職工類別”、“基本工資”項目基本數(shù)據(jù)。方法二：記錄單輸入法（1）將鼠標指針移動到將要輸入新數(shù)據(jù)的單元格，然后單擊“記錄單”功能按鈕，打開“記錄單”對話框（2）在“記錄單”對話框的左側(cè)列出了數(shù)據(jù)庫結(jié)構(gòu)的內(nèi)容，其中每一項內(nèi)容對應一個文本框，用戶只需要在文本框中輸入數(shù)據(jù)的內(nèi)容即可。（3）每輸入一條記錄后單擊“新建”按鈕即可接著輸入下一條記錄，單擊“關(guān)閉”按鈕即可完成記錄的輸入。（4）如果用戶對輸入的數(shù)據(jù)進行修改，可以先選定數(shù)據(jù)表中的任意單元格，再單擊“記錄單”功能按鈕，通過單擊“上一條”或“下一條”按鈕，或者拖動滾動條來進行記錄定位。選定要修改的記錄后，對其進行修改。（5）如果需要刪除某條記錄，在選定某條記錄后，單擊“記錄單”對話框中的“刪除”按鈕即可。（6）完成東方公司所有員工基本工資信息錄入工作后，關(guān)閉“記錄單”對話框。任務1.1建立基本工資信息表《Excel在財務會計中的應用》模塊二工資管理四、美化工作表本案例中，工作表標題及列標題的美化(字體、字號、底紋等操作)在錄入數(shù)據(jù)的過程中已經(jīng)完成，表格邊框及列寬和行高設置需要在全部數(shù)據(jù)錄入完成后進行。操作步驟如下：（1）選擇單元格區(qū)域A2:E27，設置網(wǎng)格線為所有框線。（2）調(diào)整A:E列至合適的列寬。同理，根據(jù)表4—2定義“崗位工資標準”,根據(jù)表4—3定義“績效工資標準”,操作方法與“基本工資信息表”類同。任務1.1建立基本工資信息表《Excel在財務會計中的應用》模塊二工資管理基本工資信息表(最終效果)《Excel在財務會計中的應用》模塊二工資管理設置工資結(jié)算單格式設置工資項目任務2編制工資結(jié)算單《Excel在財務會計中的應用》任務2.1設置工資結(jié)算單格式

12345打開“工資管理.xlsx”工作簿，雙擊工作表標簽Sheet3，將Sheet3命名為“工資結(jié)算單”。打開“基本工資信息表”工作表，選中單元格A2:E2，按組合鍵“Ctrl+C”進行復制操作。打開“工資結(jié)算單”工作表，選中單元格A1，按組合鍵“Ctrl+V”進行粘貼，將內(nèi)容全部復制至“工資結(jié)算單”工作表中。選中單元格F1:Q1，依次輸入工資項目名稱:“崗位工資”、“績效工資”等項目使用格式刷，復制單元格A1:E1的格式到單元格F1:Q1、選擇H至Q列，設置格式為“會計專用”選中單元格A2:Q26，單擊功能區(qū)中的“開始”—“字體”—“邊框”—“所有框線”按鈕。設置網(wǎng)格線模塊二工資管理《Excel在財務會計中的應用》任務2.2設置工資項目操作步驟一、設置“崗位工資”項目二、設置“績效工資”項目三、設置“應發(fā)工資”項目四、設置“扣社保費”項目五、設置“扣公積金”項目六、設置“專項附加扣除”項目七、設置5月“累計應納稅所得額”項目八、設置“5月累計預繳納個稅”項目九、設置“4月累計應納稅所得額”項目十、設置“4月累計預繳納個稅”項目十一、設置“扣個稅”項目十二、設置“實發(fā)工資”項目模塊二工資管理《Excel在財務會計中的應用》工資結(jié)算單(最終效果)模塊二工資管理《Excel在財務會計中的應用》1.AND功能：返回邏輯值。所有參數(shù)的邏輯值為真時返回TRUE；只要一個參數(shù)的邏輯值為假即返回FALSE。簡言之，就是當AND的參數(shù)全部滿足某一條件時，返回結(jié)果為TRUE，否則為FALSE。語法：=AND(logical1，logical2，……)2.OR函數(shù)功能：返回邏輯值。在其參數(shù)組中，任何一個參數(shù)邏輯值為TRUE，即返回TRUE。它與AND函數(shù)的區(qū)別在于，AND函數(shù)要求所有函數(shù)邏輯值均為真，結(jié)果方為真。而OR函數(shù)僅需其中任何一個為真即可為真。語法：=OR(logical1，logical2，……）3.NOT函數(shù)

功能：返回邏輯值。用于對參數(shù)值求反，當要確保一個值不等于某一特定值時，可以使用NOT函數(shù)。簡言之，就是當參數(shù)值為TRUE時，NOT函數(shù)返回的結(jié)果恰與之相反，結(jié)果為FALSE。語法：=NOT(logical）拓展邏輯函數(shù)模塊二工資管理《Excel在財務會計中的應用》ROUND函數(shù)功能：按照指定的小數(shù)位數(shù)進行四舍五入計算。語法：=ROUND(number,num_digits)5.ROUNDDOWNROUNDDOWN函數(shù)是一個數(shù)學與三角函數(shù)。功能：按照指定的小數(shù)位數(shù)向下舍入。語法：=ROUNDDOWN(number,num_digits)6.ROUNDUP函數(shù)ROUNDUP函數(shù)是一個數(shù)學與三角函數(shù)。功能：按照指定的小數(shù)位數(shù)向上舍入。語法：=ROUNDUP(number,num_digits)模塊二工資管理拓展數(shù)學與三角函數(shù)《Excel在財務會計中的應用》工資表數(shù)據(jù)分類匯總工資表數(shù)據(jù)排序

工資表數(shù)據(jù)篩選工資表數(shù)據(jù)統(tǒng)計分析模塊二工資管理任務3工資數(shù)據(jù)的查詢與統(tǒng)計分析《Excel在財務會計中的應用》任務3.1工資表數(shù)據(jù)排序解1解2方法一利用功能區(qū)中的“升序”按鈕/“降序”按鈕排序。案例1：利用“降序”按鈕

對東方公司工資結(jié)算單按“基本工資”由高到低進行降序排列。方法二利用功能區(qū)中的“排序”命令排序案例2：利用“排序”命令對東方公司工資結(jié)算單按“基本工資”由低到高進行升序排序。模塊二工資管理《Excel在財務會計中的應用》3步驟2按“職工類別”對工作表進行排序1選擇數(shù)據(jù)清單內(nèi)的任一單元格，單擊功能區(qū)中的“數(shù)據(jù)”—“分級顯示”—“分類匯總”打開“分類匯總”對話框。在“分類字段”下拉列表框中選擇“職工類別”選項，在“匯總方式”下拉列表框中選擇“求和”選項，在“選定匯總項”選擇框中選中“實發(fā)工資”復選框單擊“確定”按鈕，分類匯總完成，要取消分類匯總，再執(zhí)行一次單擊功能區(qū)中的“數(shù)據(jù)”—“分級顯示”—“分類匯總”命令操作，在打開的“分類匯總”對話框中單擊“全部刪除”按鈕即可任務3.2工資表數(shù)據(jù)分類匯總要求：對東方公司工資結(jié)算單按“職工類別”為分類字段匯總顯示實發(fā)工資的合計數(shù)。

模塊二工資管理《Excel在財務會計中的應用》“分類匯總”結(jié)果（最終效果）模塊二工資管理《Excel在財務會計中的應用》任務3.3工資表數(shù)據(jù)篩選一、使用自動篩選功能查詢姓名為“李明”的員工的工資情況操作步驟如下：（1）選擇工資結(jié)算單數(shù)據(jù)清單內(nèi)的任一單元格，單擊功能區(qū)中的“數(shù)據(jù)”—“排序和篩選”—“篩選”按鈕，進入篩選狀態(tài)，在每個字段名稱右側(cè)出現(xiàn)一個下拉三角按鈕，（2）單擊字段名稱為“員工姓名”列右側(cè)的下拉三角按鈕，出現(xiàn)篩選對話框，單擊“文本篩選”選擇框中的“全選”復選框，去掉全部對號，再重新選擇“李明”復選框（3）單擊“確定”按鈕,完成篩選二、使用自動篩選功能查詢所屬部門為“采購部”的員工的工資情況操作步驟如下：（1）單擊“所屬部門”列右側(cè)的下拉三角按鈕，選擇“文本篩選”右側(cè)“等于(E)...”（2）打開“自定義自動篩選方式”對話框，選擇“所屬部門”為“等于”、“采購部”（3）單擊“確定”按鈕，完成篩選模塊二工資管理《Excel在財務會計中的應用》任務3.4工資表數(shù)據(jù)統(tǒng)計分析一、利用數(shù)據(jù)透視表按員工“所屬部門”和“職工類別”計算“應發(fā)工資”的匯總數(shù)操作步驟如下：（1）選擇工資結(jié)算單數(shù)據(jù)清單內(nèi)的任一單元格，單擊功能區(qū)中的“插入”—“數(shù)據(jù)透視表”命令，在彈出的“創(chuàng)建數(shù)據(jù)透視表”對話框中選擇需要匯總的數(shù)據(jù)區(qū)域，選擇放置透視表的位置為“新工作表”（2）單擊“確定”按鈕，新建工作表Sheet1。（3）將右側(cè)的“所屬部門”、“職工類別”、“應發(fā)工資”分別拖至下方的“行標簽”、“列標簽”、“數(shù)值”處，定義值字段設置的數(shù)字格式為會計專用，小數(shù)位數(shù)2，貨幣符號無。即產(chǎn)生“應發(fā)工資”按“所屬部門”和“職工類別”所生成的數(shù)據(jù)透視表。（4）將新建的工作表Sheet1更名為“工資總額透視表”，移到工資結(jié)算單后。

二、在數(shù)據(jù)透視表基礎上完成數(shù)據(jù)透視圖操作步驟如下：（1）把光標定位在數(shù)據(jù)透視表內(nèi)任一單元格，選擇功能區(qū)中的“插入”—“圖表”—“柱形圖”按鈕，在彈出的對話框中選擇“簇狀柱形圖”。（2）單擊“確定”按鈕,完成數(shù)據(jù)透視圖。（3）在數(shù)據(jù)透視圖的不同位置右擊，出現(xiàn)快捷菜單，可對數(shù)據(jù)透視圖的布局、位置、格式等進行調(diào)整。模塊二工資管理《Excel在財務會計中的應用》工資總額數(shù)據(jù)透視圖（最終效果）模塊二工資管理《Excel在財務會計中的應用》模塊二工資管理設置“工資費用分配表”格式設置“工資費用分配表”公式任務4編制工資費用分配表《Excel在財務會計中的應用》任務4.1設置“工資費用分配表”格式1234選擇單元格A2，右擊，在快捷菜單中選擇“設置單元格格式”命令，打開“設置單元格格式”對話框，打開“邊框”選項卡，單擊按鈕，即可對該單元格劃斜線。打開“工資管理.xlsx”工作簿，將“工資總額透視表”工作表后面插入新工作表，并更名為“工資費用分配表”在A2單元格內(nèi)輸入“分配項目部門”，單擊編輯欄，在“分配項目部門”前面加空格，加到自動換行即可選中第2行至第10行，執(zhí)行功能區(qū)中的“開始”—“單元格”—“格式”—“行高”。在“行高”對話框中，輸入“20”，單擊“確定”按鈕。。56選中A列，執(zhí)行功能區(qū)中的“開始”—“單元格”—“格式”—“列寬”。在“列寬”對話框中，輸入“15”單擊“確定”按鈕。選中B列至H列，設置列寬為“13”。選中A1:H10，設置邊框為所有框線。模塊二工資管理《Excel在財務會計中的應用》任務4.2設置“工資費用分配表”公式

操作步驟（1）定義“工資總額”項目計算公式（2）定義“職工福利費”項目計算公式（3）定義“工會經(jīng)費”項目計算公式（4）定義“職工教育經(jīng)費”項目計算公式（8）定義“合計”行計算公式（7）定義“合計”列計算公式（6）定義“住房公積金”項目計算公式（5）定義“社會保險費”項目計算公式模塊二工資管理《Excel在財務會計中的應用》工資費用分配表（最終效果）模塊二工資管理《Excel在財務會計中的應用》利用排序功能生成工資條后打印頁面設置后打印工資條模塊二工資管理任務5打印工資條《Excel在財務會計中的應用》任務5.1利