Excel在財務中的應用第三章

情景三工資管理任務一創建工資核算系統一、學習目標0102了解工資核算的基本流程。理解工資管理中的常用函數。03掌握工資核算系統的創建。工資核算方面的數據表格很多，如果逐項輸入計算，不僅耗費時間，還極易出錯Excel提供了大量的公式和相關功能，掌握了這些內容，人事工資管理會比較專業且簡潔易行。下面將詳細介紹如何利用相關公式和功能為工資核算服務。一、工作流程基本思路:人事變動、工資調整，以及全勤、缺勤、加班、遲到等信息是工資結算的基礎，有了這些原始數據，就可以根據一定的公式，進行工資結算和費用分配了。二、工作流程1.COUNTIFO

計算區域中滿足給定條件的單元格個數

語法:COUNTIF(range,criteria)2.ROUNDO

返回某個按指定位數取整后的數字。

語法:ROUND(numbernumdigits)3.查找與引用函數VLOOKUP()VLOOKUP函數在表格數組的首列查找值，并由此返回表格數組當前行中其他列的值。VLOOKUP中的V表示重直方向。當比較值位于需要查找的數據左邊的一列時，可以使用VLOOKUP，而不用HLOOKUP.。三、實踐操作圖3-1工資明細表1.建立工資明細表員工基本工資項目的建立主要包括基本工資項目的輸入，以及對員工所屬部門的有效性設置。三、實踐操作1.建立工資明細表（1）新建Excel工作簿，將其命名為“工資核算系統”。然后雙擊工作表Sheet1，重命名為“工資明細表”。（2）輸入標題及各個工資項目，并對輸入的內容進行格式化。（3）為了防止輸入錯誤，下面對“所屬部門”列應用“數據驗證”功能加以控制。選中單元格C4，單擊“數據”選項卡│“數據工具”工作組│“數據驗證”按鈕，彈出“數據驗證”對話框。三、實踐操作1.建立工資明細表（4）單擊“設置”選項卡，在“允許”下拉列表框中選中“序列”選項，然后在“來源”參數框中輸入“企劃部，財務部，銷售部，生產部”，各個部門的名稱要用英文狀態下的逗號隔開。（5）單擊“確定”按鈕，返回工作表中，然后將C4單元格的格式填充到該列的其他單元格中。（6）輸入員工的其他相關信息。三、實踐操作2.統計部門員工人數公司發展越來越大，員工也會越來越多，各部門的員工數量也在不斷變動，統計各部門人數便成了一個問題。逐項查找恐怕不太可能，用篩選功能雖然可以減輕一部分工作量，但花費的時間也不少，統計的時候一不小心還有可能出錯。這里介紹一個新的統計函數--COUNTIF()函數，利用該函數能迅速統計出符合條件的單元格的數量。假設統計ABC公司各部門員工總數和男女員工人數，操作步驟如下：（1）雙擊“工資核算系統”工作簿中的工作表標簽Sheet2，將其重命名為“基本資料表”。（2）打開員工工資明細表，將其中的員工的主要數據復制到基本資料表中并補充相應信息，然后按部門排序。三、實踐操作2.統計部門員工人數（3）選擇單元格區域A4：G7，單擊“公式”選項卡│“定義的名稱”工作組│“定義名稱”按鈕，如圖3-5所示，彈出“新建名稱”對話框。在“名稱”文本框中輸入“財務部”，單擊“確定”按鈕。已選中的區域就被定義為“財務部”了。（4）用同樣的方法分別定義A8：G11為“企劃部”，A12：G14為“生產部”，A15：G18為“銷售部”。單擊“公式”選項卡│“定義的名稱”工作組│“名稱管理器”按鈕，彈出“名稱管理器”對話框，可以對所定義的內容進行編輯及刪除操作。三、實踐操作2.統計部門員工人數（5）在“工資核算系統”工作簿中新建“部門統計”工作表。（6）選擇單元格C5，單擊工具欄中的按鈕，彈出“插入函數”對話框，在“或選擇類別”下拉列表框中選擇“統計”選項，在“選擇函數”列表框中選擇COUNTIF函數。（7）單擊“確定”按鈕，彈出“函數參數”對話框，在Range文本框中輸入“財務部”，在Criteria文本框中輸入“男”。（8）單擊“確定”按鈕，單元格C5中就會顯示出財務部男員工的人數。（9）按照同樣的方法，統計出財務部女員工的人數，只需在“函數參數”對話框中將Criteria文本框的“男”改為“女”即可。三、實踐操作2.統計部門員工人數（10）選中合并后的單元格D5，在編輯欄中輸入公式“=COUNTIF(基本資料表!E4:E18,財務部)”。按回車鍵，單元格D5中即可顯示財務部的總人數4。統計財務部的總人數也可直接用SUM函數直接計算，或者在編輯欄中輸入公式“=C5+C6”。（11）按照同樣的方法，統計其他部門的人數。（12）選中單元格D13，在編輯欄中輸入公式“=SUM(D5:D12)”，按回車鍵，公司總人數就計算出來了。三、實踐操作3.統計員工年假一般單位的員工都有年假，只要在公司工作一年就可以每年享受一定天數的帶薪假期。下面以ABC公司為例，根據前面創建的“基本資料表”，為該公司的員工計算年假天數。假設該公司規定，任職滿一年的員工，年假為15天，以后工齡每增加一年，年假增加一天，滿6年后年假均為20天，滿10年后均為30天，滿20年后每增加1年工齡，年假增加1天。統計員工年假的具體操作步驟如下：（1）在“工資核算系統”工作簿中新建一張工作表，并將其重命名為“年假規則表”，以方便統計年假。（2）選中作為年假規則的單元格區域，單擊“公式”選項卡│“定義的名稱”工作組│“定義名稱”按鈕，如圖3-14所示。彈出“新建名稱”對話框，將選中區域定義為“年假規則”。三、實踐操作3.統計員工年假（3）為方便操作，在基本資料表中添加兩列：“工齡”和“年假(天)”。（4）選中單元格H4，在編輯欄中輸入公式“=YEAR(NOW())-YEAR(D4)”，假定當前年份為2023年，則按回車鍵后計算結果為15。（5）選中單元格H4，將鼠標指針移到單元格的右下角，待鼠標指針變成“+”形狀時，按住鼠標左鍵并向下拖動鼠標將公式填充到該列中的其他單元格，釋放鼠標時，其他員工的工齡就自動顯示出來了。（6）選中單元格I4，輸入公式“=VLOOKUP(H4,年假規則,2,1)”，按回車鍵后即可得出對應的年假天數。（7）選中單元格I4，將公式填充到該列的其他單元格中，即可自動顯示其他員工的年假天數。三、實踐操作4.自動更新基本工資每個員工都會有調整基本工資的時候，但是各人情況不同，基本工資調整的時間和幅度也不一樣，每月計算工資時，不可能把所有人的調薪記錄都查找一遍，這就有必要建立一個自動更新的數據庫，以方便準確及時地更新數據。在Excel中可以利用列查找函數VLOOPUP0)來自動更新每位員工的基本工資。具體操作步驟如下：（1）在“工資核算系統”工作簿中創建一張新的工作表，并將其重命名為“工資調整”。（2）輸入標題，然后切換到該工作簿中的“基本資料表”工作表，將部分信息復制到“工資調整”工作表，包括員工編號、姓名、性別、入職時間、所屬部門和職工類別等三、實踐操作4.自動更新基本工資（3）在工作表中的G4:I18單元格中添加如圖3-21所示的項目和內容。（4）將“工資調整”進行排序，以“員工編號”為第一關鍵字升序排序，“調整年”為第二關鍵字降序排序，“調整月”為第三關鍵字降序排序。（5）單擊“公式”選項卡│“定義的名稱”工作組│“定義名稱”按鈕，彈出“新建名稱”對話框，將A3:I18區域定義為“工資調整”。（6）切換到“工資明細表”，刪除原基本工資數據。選中單元格D4，輸入公式“=VLOOKUP(A4,工資調整,9,0)”，按回車鍵即可顯示該員工最新調整的工資了。（7）拖曳單元格右下角的填充柄，將公式填充到該列的其他單元格中，即可顯示其他員工的最新基本工資。三、實踐操作5.核算加班費每個員工都會有調整基本工資的時候，但是各人情況不同，基本工資調整的時間和幅度也不一樣，每月計算工資時，不可能把所有人的調薪記錄都查找一遍，這就有必要建立一個自動更新的數據庫，以方便準確及時地更新數據。在Excel中可以利用列查找函數VLOOPUP0)來自動更新每位員工的基本工資。步驟如下：（1）在“工資核算系統”工作簿中創建一個新的工作表，并將其命名為“加班記錄”。（2）在該工作表中輸入包括員工編號、姓名、性別、所屬部門、職工類別、加班的起止時間等各項信息。三、實踐操作圖3-2

計算其他員工的事假扣款6.核算缺勤扣款ABC公司規定，請事假要按天扣工資，而請病假一天只扣半天的工資;遲到15分鐘以內扣10元超過15分鐘扣半天的工資;每月按實際天數計算，如2月份28天，每天的工資為基本工資除以28。(1)病假扣款(2)事假扣款<如圖3-2所示>(3)遲到扣款(4)數據鏈接。三、實踐操作6.核算缺勤扣款（1）病假扣款計算病假扣款的操作步驟如下：①在“工資核算系統”工作簿中創建一張新的工作表，并將其命名為“請假記錄”，輸入相應的數據并按“員工編號”升序排序。②將“工資明細表”拖動到“請假記錄”工作表旁邊，以便于下面的操作。③選中單元格區域G4:I18，將單元格格式設置為帶兩位小數的數值格式。三、實踐操作6.核算缺勤扣款④選中“請假記錄”表中的G4單元格，輸入公式“=ROUND(工資調整!I3/31/2*請假記錄!D4,0)”，如圖3-27所示。按回車鍵，顯示結果該員工病假扣款金額為97.00。⑤用鼠標拖曳單元格G4的填充柄，將公式填充到該列的其他單元格中，計算出其他員工的病假扣款。三、實踐操作6.核算缺勤扣款（2）事假扣款根據ABC公司的規定，請事假按天扣工資。計算事假扣款的操作步驟如下：①選中“請假記錄”工作表中的H4單元格，輸入公式“=ROUND(工資調整!I3/31*請假記錄!E4,0)”。②按回車鍵，顯示該員工事假扣款結果為0。由于該員工在12月份沒有請過事假，因此事假扣款金額為0。③用鼠標拖曳單元格H4，將公式填充到該列的其他單元格中，計算出其他員工的事假扣款。三、實踐操作6.核算缺勤扣款（3）遲到扣款ABC公司規定遲到15分鐘以內扣10元，超過15分鐘扣半天的工資。計算遲到扣款的操作步驟如下：①選中“請假記錄”工作表中的I4單元格，輸入公式“=ROUND(IF(F4>15,工資調整!I3/31/2,IF(F4=0,0,10)),0)”。②按回車鍵，即可顯示扣款金額。因為該員工本月遲到時間為0分鐘，因此不扣款。③用鼠標拖曳單元格I4的填充柄，將公式填充到該列的其他單元格中，計算出其他員工的遲到扣款。（4）數據鏈接應用Excel2019的數據鏈接功能，可以使計算結果隨著數據源的變化自動更新。三、實踐操作6.核算缺勤扣款具體操作步驟如下：①在“工資明細表”中選中單元格I4，在編輯欄中輸入“=”。②切換到“請假記錄”工作表，選中單元格G4。這時，在單元格G4的四周有虛線閃動，而編輯欄的公式并不顯示G4中的公式，而是變成了“=請假記錄!G4”。③不切換工作表，繼續在編輯欄中輸入“+”，公式變為“=請假記錄!G4+”，選中單元格H4，這時，編輯欄公式變為“=請假記錄!G4+請假記錄!H4”，單元格H4四周虛線閃動。④按照同樣的方法，在編輯欄中繼續輸入“+”。選中“請假記錄”工作表中的單元格I4，切換到“工資明細表”中，單元格I4的公式變為“=請假記錄!G4+請假記錄!H4+請假記錄!I4”。三、實踐操作6.核算缺勤扣款⑤按回車鍵，此時在單元格I4中的數據顯示為97，該員工的缺勤扣款總共為97元。⑥用鼠標拖曳單元格I4的填充柄，將公式填充到該列的其他單元格中，計算出其他員工的缺勤扣款。上述用填充方式自動計算其他單元格結果的方法雖然簡便易行，但是以各工作表排序方式和員工資料相同為前提，一旦某個工作表中的員工數量或內容和其他工作表不一致，則填充后顯示的結果就是錯誤的。為謹慎起見，最好還是結合VLOOKUP函數進行計算。三、實踐操作6.核算缺勤扣款下面以鏈接缺勤扣款為例，介紹一下VLOOKUP函數的應用。為了更清楚地說明問題，先將前面創建的“工資明細表”中“缺勤扣款”列的公式刪除。具體操作步驟如下：①在“請假記錄”工作表中加一列“缺勤總扣款”，然后選中單元格J4，輸入公式“=G4+H4+I4”。②按回車鍵，顯示結果為97，說明該員工的缺勤總扣款為97元。三、實踐操作6.核算缺勤扣款③用鼠標拖曳單元格J4的填充柄，將公式填充到該列的其他單元格中，計算出其他員工的缺勤總扣款。④單擊“公式”選項卡│“定義的名稱”工作組│“定義名稱”按鈕，彈出“新建名稱”對話框，將區域A4:J18定義名稱為“缺勤總扣款”。⑤切換到“工資明細表”，選中單元格I4，輸入公式“=VLOOKUP(A4,缺勤總扣款,10,0)”。⑥按回車鍵，顯示結果為97，結果正確。向下拖曳填充柄，將公式填充到該列的其他單元格中，顯示出其他員工的缺勤扣款。對照“請假記錄”工作表中的數據，可以發現其他員工的缺勤扣款均正確。由于使用了VLOOKUP()函數，只要員工號準確無誤，就能保證缺勤扣款數據的準確無誤。三、實踐操作7.核算出勤獎金ABC公司規定:只要員工每月請假天數不超過2天且遲到不超過30分鐘，月底時就可以拿到出勤獎金200元。下面看一下如何計算：(1)為方便計算，在“請假記錄”工作表中添加“出勤獎金”列。(2)選中單元格K4，輸入公式“=IF(IF((D4+E4)<=2,0,1)+IF(F4<=30,0,1)=0,200,0)”。(3)按回車鍵，顯示結果為200。因為該員工請假時間為一天，沒有遲到，所以拿到了200元的出勤獎金。(4)向下拖曳單元格K4的填充句柄，將公式填充到該列的其他單元格中，即可得到其他員工的出勤獎金。(5)切換到“工資明細表”，選中單元格E4，輸入公式“=請假記錄!K4”。(6)按回車鍵，顯示結果為200。該員工符合月底獎金的條件，因此獎勵200元。(7)向下拖曳單元格E4的填充句柄，將公式填充到該列的其他單元格中，即可得到其他員工的出勤獎金。三、實踐操作8.合計應發工資圖3-3

計算其他員工的應發工資圖根據基本工資、出勤獎金、補貼和加班費，就可以計算出員工的應發工資。三、實踐操作9.代繳養老保險核算養老保險金的操作步驟如下：（1）打開“工資明細表”，選中單元格J4，輸入公式“=H4*10%”。（2）按回車鍵，顯示結果為934.00，說明該員工應繳納的養老保險金為934元。（3）選中單元格J4，向下拖曳填充柄，顯示出其他員工應繳納的養老保險金額。圖3-4計算其他員工的養老保險金三、實踐操作10.代扣個人所得稅圖3-5計算其他員工所得稅具體步驟如下：（1）在實發工資右側增加應稅基數（應稅基數=應發工資-保險金），計算應稅基數的值。對“工資明細表”中員工按應稅基數進行自動篩選。選中工作表中的單元格區域A3:M18，單擊“數據”選項卡│“排序和篩選”工作組│“篩選”按鈕，進入自動篩選狀態。三、實踐操作10.代扣個人所得稅（2）單擊“應稅基數”列的篩選按鈕，在彈出的下拉列表中選擇“數字篩選”│“自定義篩選”選項，即可打開“自定義自動篩選方式”對話框。（3）單擊“確定”按鈕，篩選出個人所得稅率為0，即不扣稅的員工（4）沒有符合條件的員工，都超過了免征額。（5）在“應稅基數”下拉列表中選擇“全選”復選框，顯示全部資料。然后選擇“數字篩選”│“自定義篩選”選項，打開“自定義自動篩選方式”對話框。三、實踐操作10.代扣個人所得稅（6）單擊“確定”按鈕，篩選出所得稅率為3%的員工。（7）選中單元格K5，輸入公式“=(M6-5000)*0.03”，按回車鍵顯示結果為40.08，該員工應扣所得稅40.08元，然后拖曳填充柄將公式填充到下面的單元格。（8）依次計算其他員工的所得稅。（9）單擊“數據”選項卡│“排序和篩選”工作組│“篩選”按鈕，退出自動篩選狀態。三、實踐操作11.合計實發工資圖3-6計算其他員工的實發工資每個員工月底能拿到的工資，其實就是扣完缺勤、稅費后的實得工資。操作步驟如下：（1）在“工資明細表”中選中單元格L4，輸入公式“=H4-I4-J4-K4”。四、小結問題深究個人所得稅計算深入探討個人所得稅的計算方法，包括累進稅率、速算扣除數的應用，以及如何在Excel中實現自動化計算。四、小結知識拓展COUNTIF函數學習COUNTIF函數的使用方法，包括條件設置、范圍選擇等，以及它在工資管理中的實際應用。ROUND函數掌握ROUND函數的使用方法，用于對工資數據進行四舍五入處理，確保數據的準確性。查找與引用函數VLOOKUP學習VLOOKUP函數的使用方法，包括查找范圍、查找值、返回列數的設置，以及它在工資數據匹配與引用中的應用。四、小結利用函數公式計算工資數據通過實際案例，練習使用Excel中的函數公式進行工資數據的計算，包括基本工資、加班費、缺勤扣款、出勤獎金等的計算，以及應發工資、實發工資的合計。課后訓練謝謝任務二員工工資的管理一、學習目標0102學會工資表的分類匯總工資表的公式隱藏03制作工資條04創建“工資核算系統”的模板05系統模板的應用二、工作流程工作表制作完畢后，用戶需要對表中的數據進行編輯、更新和管理。工資條是發放工資時交給員工的工資項目清單，其數據來源于工資表。由于工資條是發放給員工個人的，所以工資條應該包括工資中各個組成部分的項目名稱和數值。三、實踐操作圖3-5“分類匯總”對話框

1.分類匯總要統計各個部門的月工資總額和平均值，雖然用SUM()函數和AVERAGE()函數可以做到，但數據較多且復雜時，這兩個函數的功能就顯得有局限性了。下面介紹Excel的另一項功能——分類匯總。分類匯總是對數據清單上的數據進行分析的一種方法，它可以在數據清單上插入分類匯總行，然后按照選擇的方式對數據進行匯總。同時，再插入分類匯總時，Excel還會自動在數據清單底部插入一個總計行。三、實踐操作圖3-6分類匯總結果1.分類匯總1)打開“工資明細表”，將數據按部門排序，然后選擇A3:M18單元格區域。2)單擊“數據”選項卡│“分級顯示”工作組│“分類匯總”按鈕，彈出“分類匯總”對話框，在“分類字段”下拉列表框中選擇“所屬部門”選項，在“匯總方式”下拉列表框中選擇“求和”選項，在“選定匯總項”列表框中選中“實發工資”復選框，分類匯總效果如圖3-6所示。（1）分類匯總工資總額三、實踐操作圖3-7嵌套平均值的分類匯總在現有分類匯總的基礎上再次應用分類匯總功能。在現有分類匯總的基礎上再次應用分類匯總功能。單擊“數據”選項卡│“分級顯示”工作組│“分類匯總”按鈕，彈出“分類匯總”對話框。在“匯總方式”下拉列表框中選擇“平均值”選項，并且取消“替換當前分類匯總”復選框，數據清單中不僅有匯總值，還有平均值，如圖3-7所示。如果想取消分類匯總，可單擊“數據”選項卡│“分級顯示”工作組│“分類匯總”按鈕，彈出“分類匯總”對話框，單擊“全部刪除”按鈕，這樣