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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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