Excel在工資管理中的應用電子教案項目四Excel在工資管理中的應用項目介紹本任務主要內容為工資結算單、工資數據的查詢與統計分析、工資費用分配表。項目目標1.知識目標了解工資核算的業務處理流程掌握工資項目的組成及計算方法熟練掌握IF函數、OR函數等函數的使用方法2.能力目標學會使用Excel設置工資項目公式的能力學會使用Excel篩選功能進行工資數據查詢的能力學會使用Excel數據透視表進行工資數據分析的能力3.素質目標培養學生嚴謹細致的工作態度培養學生誠信納稅的愛國精神培養學生勤于思考的學習習慣培養學生自主學習的學習能力項目重難點1.重點掌握編制工資結算單、編制工資費用分配表的操作方法。2.難點工資結算單公式的定義。項目內容任務1錄入員工基礎工資數據；任務2編制工資結算單任務3工資數據的查詢與統計分析任務4編制工資費用分配表相關知識任務1錄入員工基礎工資數據任務1-1建立基本工資信息表本任務是建立基本工資信息表，為后面的工資表中工資項目的計算提供依據。任務實施：一、定義基本工資信息表格式操作步驟如下：（1）打開Excel2010，新建空白工作簿，并另存為“D:\東方公司\工資管理2202.xlsx”。（2）雙擊工作表標簽Sheet1，更名為“基本工資信息表”。（3）選擇A1單元格，輸入工作表標題“東方公司員工基本工資表”。（4）選中單元格A1:E1，設置字體為“宋體”，字號為“12”，加粗。選擇對齊方式為“跨列居中”。（5）在A2至E2單元格內分別錄入“員工編號”、“員工姓名”、“所屬部門”、“職工類別”、“基本工資”項目，設置其字體為“宋體”，字號為“12”，加粗，黃色底紋。設置A至E列的對齊方式為“居中”。（6）設置“員工編號”所在A列的格式為“文本”類型，設置“基本工資”所在E列的格式為“會計專用”類型，小數位數為2，貨幣符號無。二、定義單元格的有效性為了輸入方便并防止出錯，可對某些數據列添加有效性控制。操作步驟如下：1.對“所屬部門”所在列添加有效性控制（1）選擇C列，單擊功能區中的“數據”—“數據工具”—“數據有效性”按鈕，打開“數據有效性”對話框。（2）在“設置”選項卡中的“允許”下拉列表框中選擇“序列”選項；在“來源”框中輸入“行政部，財務部，技術部，采購部，銷售部，生產一車間，生產二車間，生產三車間”。（3）最后單擊“確定”按鈕，則完成對“所屬部門”數據有效性的設置。2.對“職工類別”所在列添加有效性控制同理，設置D列的數據有效性為序列“總經理,部門經理,管理人員,采購人員,銷售人員,生產人員”。三、錄入基本工資信息表方法一：直接輸入法根據表2-1中所給數據分別錄入“員工編號”、“員工姓名”、“所屬部門”、“職工類別”、“基本工資”項目基本數據。方法二：記錄單輸入法Excel會將數據清單當成數據庫來處理，對數據清單的各種編輯操作常用到“記錄單”。通過“記錄單”功能不僅可以進行數據的錄入工作，還可以實現數據的添加、刪除、修改和查詢操作。操作步驟如下：（1）將鼠標指針移動到將要輸入新數據的單元格A11，然后單擊“記錄單”功能按鈕，打開“記錄單”對話框。（2）在“記錄單”對話框的左側列出了數據庫結構的內容，其中每一項內容對應一個文本框，用戶只需要在文本框中輸入數據的內容即可。（3）每輸入一條記錄后單擊“新建”按鈕即可接著輸入下一條記錄，單擊“關閉”按鈕即可完成記錄的輸入。（4）如果用戶對輸入的數據進行修改，可以先選定數據表中的任意單元格，再單擊“記錄單”功能按鈕，通過單擊“上一條”或“下一條”按鈕，或者拖動滾動條來進行記錄定位。選定要修改的記錄后，對其進行修改。（5）如果需要刪除某條記錄，在選定某條記錄后，單擊“記錄單”對話框中的“刪除”按鈕即可。（6）完成東方公司所有員工基本工資信息錄入工作后，關閉“記錄單”對話框。四、美化工作表本案例中，工作表標題及列標題的美化(字體、字號、底紋等操作)在錄入數據的過程中已經完成，表格邊框及列寬和行高設置需要在全部數據錄入完成后進行。操作步驟如下：（1）選擇單元格區域A2:E27，設置網格線為所有框線。（2）調整A:E列至合適的列寬。同理，定義“崗位工資標準”“績效工資標準”操作方法與“基本工資信息表”類同。任務1-2建立專項附加扣除表本任務是根據東方公司職工個人提供的“子女教育”、“繼續教育”、“大病醫療”、“住房貸款利息”、“住房租金”、“贍養老人”六項專項附加扣除信息建立專項附加扣除表。具體資料見教材表4-4。任務實施：操作步驟如下：（1）打開”工資管理2202.xlsx”工作簿，雙擊工作表標簽Sheet2，輸入一個新的工作表表名“專項附加扣除表”。（2）復制“基本工資信息表！A:D”列到“專項附加扣除表！A:D”列。（3）選中“專項附加扣除表”工作表，刪除第一行。（4）在E1:K1單元格內分別輸入“子女教育”、“繼續教育”、“大病醫療”、“住房貸款利息”、“住房租金”、“贍養老人”、“合計”。（5）選擇E:K列，設置數字格式為“會計專用”，小數位數為2，貨幣符號無。（6）按照表4-4的內容輸入每位職工六項專項附加扣除的信息。（7）定義K2單元格的公式為“=SUM(E2:J2)”，向下拖拉填充柄復制到K26單元格。（8）選擇單元格區域E1:K26，設置網格線為所有框線。任務2編制工資結算單任務2-1設置工資結算單格式結合相關資料，李悅設計東方公司的工資結算單不僅包括基本工資信息表中的工資項目，還包括“崗位工資”、“績效工資”、“請假天數”、“請假扣款”、“應發工資”、“扣社保費”、“扣公積金”、“扣個稅”及“實發工資”等工資項目。任務實施：操作步驟如下：（1）打開”工資管理2202.xlsx”工作簿，雙擊工作表標簽Sheet3，將Sheet3命名為“工資結算單”。（2）打開“基本工資信息表”工作表，選中單元格A2:E2，按組合鍵“Ctrl+C”進行復制操作。打開“工資結算單”工作表，選中單元格A1，按組合鍵“Ctrl+V”進行粘貼，將內容全部復制至“工資結算單”工作表中。（3）選中單元格F1:Q1，依次輸入工資項目名稱:“崗位工資”、“績效工資”、“應發工資”、“扣社保費”、“扣公積金”、“扣個稅”及“實發工資”、“專項附加扣除”、“5月累計應納稅所得額”、“5月累計預繳個稅”、“4月累計應納稅所得額”和“4月累計預繳個稅”。使用格式刷，復制單元格A1:E1的格式到單元格F1:Q1（4）選擇H至Q列，設置格式為“會計專用”。（5）選中單元格A2:Q26，單擊功能區中的“開始”—“字體”—“邊框”—“所有框線”按鈕，設置網格線。任務2-2設置工資項目本任務主要包括應發工資的計算、代扣款項的計算和實發工資的計算。任務實施：李悅設置相關工資項目公式如下：一、設置“崗位工資”項目根據公司規定,“崗位工資”是根據“職工類別”的不同來設置的，具體要求見表4—2。操作步驟如下：選擇單元格F2，單擊功能區中的“公式”—“插入函數”按鈕，打開“插入函數”對話框，選擇常用函數下的IF函數，單擊確定按鈕。設置IF函數嵌套的參數如下圖公式含義：如果D2單元格的值為“總經理”，則返回值為“4000”，否則又有4種情況，所以在第3個參數里繼續單擊IF函數作進一步判斷，如果D2單元格的值為“部門經理”，則返回值為“3500”，如果不是，則繼續單擊IF函數進行判斷，如果D2單元格的值為“管理人員”，則返回值為“3000”，如果不是，則繼續單擊IF函數進行判斷，如果D2單元格的值為“銷售人員”，則返回值為“2000”，如果不是，則IF函數值為“2500”。二、設置“績效工資”項目根據公司規定，績效工資是依據公司和各部門的效益決定的，具體要求見表4—3。操作步驟如下：選擇單元格G2，公式設置為“=IF(OR(C2="行政部",C2="技術部"),2000,IF(C2="銷售部",2500,1500))”三、設置“應發工資”項目“應發工資”項目的計算公式為：應發工資＝基本工資+崗位工資+績效工資操作步驟如下：選擇單元格H2，公式設置為“=E2+F2+G2”。四、設置“扣社保費”項目根據公司規定，“扣社保費”的計算基數是“應發工資”項目。四項社會保險費中，基本養老保險費個人繳費比例為8%，基本醫療保險費個人繳費比例為2%，失業保險費個人繳費比例為0.3%，合計10.3%。“扣社保費”項目的計算公式為：扣社保費＝應發工資*10.3%操作步驟如下：選擇單元格I2，公式設置為“=H2*0.103”五、設置“扣公積金”項目根據公司規定，“扣公積金”的計算基數也是“應發工資”項目。住房公積金個人繳費比例為6%。“扣公積金”項目的計算公式為：扣公積金＝應發工資*6%。操作步驟如下：選擇單元格J2，公式設置為“=H2*0.06”六、設置“專項附加扣除”項目“專項附加扣除”項目的數據來自“專項附加扣除表”。操作步驟如下：選擇單元格M2，公式設置為“=專項附加扣除表!L2”。七、設置“2月累計應納稅所得額”項目“2月累計應納稅所得額”項目的計算公式為：2月綜合所得累計應納稅所得額=應發工資×2-5000×2-扣社保費×2-扣公積金×2-專項附加扣除×2操作步驟如下：選擇單元格N2，公式設置為“=H2*2-5000*2-I2*2-J2*2-M2*2”八、設置“2月累計預繳納個稅”項目根據2月累計應納稅所得額，查找表2-6“個人所得稅稅率表”，計算2月累計預繳納個稅。操作步驟如下：選中單元格O2，公式設置為：=ROUND(IF(N2<=0,0,IF(N2<=36000,N2*0.03,N2*0.1-2520)),2)九、設置“1月累計應納稅所得額”項目選擇單元格P2，公式設置為“=H2-5000-I2-J2-M2”，結果如圖4-22所示。十、設置“1月累計預繳納個稅”項目選中單元格Q2，公式設置為：=ROUND(IF(P2<=0,0,IF(P2<=36000,P2*0.03,P2*0.1-2520)),2)十一、設置“扣個稅”項目“扣個稅”項目的計算公式為：扣個稅=2月累計預繳納個稅-1月累計預繳納個稅十二、設置“實發工資”項目“實發工資”項目的計算公式為：實發工資=應發工資-扣社保費-扣公積金-扣個稅操作步驟如下：選擇單元格L2，公式設置為“=H2-I2-J2-K2”。任務3工資數據的查詢與統計分析因為工作需要，財務部門或薪資管理人員經常需要了解一下某個部門或職工的工資情況，并對公司或部門員工的工資情況按照一定的標準進行排序、分類匯總。本任務將利用排序、篩選功能進行工資的查詢，使用數據透視表和數據透視圖進行數據的簡單處理和分析。任務3-1工資表數據排序排序是將數據區域中的記錄，按字段名的數據值大小進行排列，從小到大排序稱為升序，從大到小排序稱為降序，用來排序的字段或條件稱為排序關鍵字。在Excel中進行排序時，數字和日期按數值大小進行排列；字母按字母順序進行排列；漢字轉化為漢語拼音，并根據首個漢字字母比較規則進行比較排序。任務實施：一、方法一：利用功能區中的“升序”按鈕/“降序”按鈕排序案例1：利用“降序”按鈕對東方公司工資結算單按“基本工資”由高到低進行降序排列。操作步驟如下：（1）打開工資結算單，選擇“基本工資”E列，單擊功能區中的“數據”—“排序和篩選”—“降序”按鈕。（2）在彈出的“排序提醒”對話框中選中“擴展選定區域”單選按鈕。注:選中“擴展選定區域”單選按鈕，則基本工資對應的其他列也隨排序位置的變化而變化。（3）最后單擊“排序”按鈕，排序后人員編號的順序發生變化。二、方法二：利用功能區中的“排序”命令排序案例2：利用“排序”命令對東方公司工資結算單按“基本工資”由低到高進行升序排序。操作步驟如下：（1）選擇數據區域中的一個單元格，單擊功能區中的“數據”—“排序和篩選”—“排序”命令，彈出“排序”對話框。（2）單擊對話框中“主要關鍵字”下拉列表框的向下箭頭，選取主要關鍵字“基本工資”，排序依據選擇“數值”，次序選擇“升序”。（3）單擊“確定”按鈕，按基本工資“升序”排序。任務3-2工資表數據分類匯總分類匯總是指對所有資料分類進行匯總，即將所有資料按同一類別放到一起，再進行同類數據的計算、統計等操作。Excel的分類匯總首先需對工作表進行排序，分類匯總時選擇的分類項即為分類字段，其他需統計的字段稱為選定匯總項，匯總方式可以是求和、求最大最小值、統計個數等。對東方公司工資結算單按“職工類別”為分類字段匯總顯示實發工資的合計數。任務實施：操作步驟如下：（1）按“職工類別”對工作表進行排序。（2）選擇數據清單內的任一單元格，單擊功能區中的“數據”—“分級顯示”—“分類匯總”，打開“分類匯總”對話框。（3）在“分類字段”下拉列表框中選擇“職工類別”選項，在“匯總方式”下拉列表框中選擇“求和”選項，在“選定匯總項”選擇框中選中“實發工資”復選框。（4）單擊“確定”按鈕，分類匯總完成。（5）要取消分類匯總，再執行一次單擊功能區中的“數據”—“分級顯示”—“分類匯總”命令操作，在打開的“分類匯總”對話框中單擊“全部刪除”按鈕即可。任務3-3工資表數據篩選財務數據往往是復雜的、繁多的，工作人員經常需要在密密麻麻的數據中找出一些符合條件的數據，有沒有一種更加簡便的方法呢?這就需要用到Excel的篩選功能。篩選功能可以使Excel工作表只顯示符合條件的數據而隱藏其他數據，是一種查找數據的快速方法。任務實施：一、使用自動篩選功能查詢姓名為“李明”的員工的工資情況操作步驟如下：（1）選擇工資結算單數據清單內的任一單元格，單擊功能區中的“數據”—“排序和篩選”—“篩選”按鈕，進入篩選狀態，在每個字段名稱右側出現一個下拉三角按鈕。（2）單擊字段名稱為“員工姓名”列右側的下拉三角按鈕，出現篩選對話框，單擊“文本篩選”選擇框中的“全選”復選框，去掉全部對號，再重新選擇“李明”復選框。（3）單擊“確定”按鈕。二、使用自動篩選功能查詢所屬部門為“采購部”的員工的工資情況操作步驟如下：（1）單擊“所屬部門”列右側的下拉三角按鈕，選擇“文本篩選”右側“等于(E)...”。（2）打開“自定義自動篩選方式”對話框，選擇“所屬部門”為“等于”、“采購部”。（3）單擊“確定”按鈕。任務3-4工資表數據統計分析任務實施：一、利用數據透視表按員工“所屬部門”和“職工類別”計算“應發工資”的匯總數操作步驟如下：（1）選擇工資結算單數據清單內的任一單元格，單擊功能區中的“插入”—“數據透視表”命令，在彈出的“創建數據透視表”對話框中選擇需要匯總的數據區域，選擇放置透視表的位置為“新工作表”。（2）單擊“確定”按鈕，新建工作表Sheet1。（3）將右側的“所屬部門”、“職工類別”、“應發工資”分別拖至下方的“行標簽”、“列標簽”、“數值”處，定義值字段設置的數字格式為會計專用，小數位數2，貨幣符號無。即產生“應發工資”按“所屬部門”和“職工類別”所生成的數據透視表。（4）將新建的工作表Sheet1更名為“工資總額透視表”，移到工資結算單后。二、在數據透視表基礎上完成數據透視圖操作步驟如下：（1）把光標定位在數據透視表內任一單元格，選擇功能區中的“插入”—“圖表”—“柱形圖”按鈕，在彈出的對話框中選擇“簇狀柱形圖”。（2）單擊“確定”按鈕。在數據透視圖的不同位置右擊，出現快捷菜單，可對數據透視圖的布局、位置、格式等進行調整。任務4編制工資費用分配表工資費用的分配是將公司本月承擔的“三項經費”、“社會保險費”和“住房公積金”按照職工所在部門進行分配，編制工資費用分配表，為生成相關工資費用憑證提供數據。李悅設計本任務包括以下兩部分：（1）設置工資費用分配表格式；（2）設置工資費用分配表公式。本任務用到的知識主要有IF函數、OR函數等。任務4-1設置“工資費用分配表”格式任務實施：操作步驟如下：（1）打開”工資管理2202.xlsx”工作簿，將“工資總額透視表”工作表后面插入新工作表，并更名為“工資費用分配表”。（2）選擇單元格A2，右擊，在快捷菜單中選擇“設置單元格格式”命令，打開“設置單元格格式”對話框，打開“邊框”選項卡，單擊按鈕，即可對該單元格劃斜線。打開“對齊”選項卡，在“文本對齊方式”選項區域，在“水平對齊”選項中，選中“靠左(縮進)”選項，在“垂直對齊”選項中，選中“靠上”選項，在“文本控制”選項區域，選中“自動換行”復選框。（3）在A2單元格內輸入“分配項目部門”，單擊編輯欄，在“分配項目部門”前面加空格，加到自動換行即可。（4）選中第2行至第10行，執行功能區中的“開始”—“單元格”—“格式”—“行高”。在“行高”對話框中，輸入“20”，單擊“確定”按鈕。（5）選中A列，執行功能區中的“開始”—“單元格”—“格式”—“列寬”。在“列寬”對話框中，輸入“15”單擊“確定”按鈕。選中B列至H列，設置列寬為“13”。（6）選中A1:H10，設置邊框為所有框線。任務4-2設置“工資費用分配表”公式根據東方公司的資料，單位承擔的“三項經費”占“應發工資”項目的比例分別是:職工福利費為14%、工會經費為2%，職工教育經費為8%；單位承擔的社會保險費占“應發工資”項目的比例分別是:養老保