Excel在財(cái)務(wù)報(bào)表中的應(yīng)用

第三章工資管理

隨著社會(huì)的不斷開展，李芳經(jīng)常遇見員工的工資變動(dòng)和人員變更的問題。利用Excel創(chuàng)立員工工資核算系統(tǒng)，將會(huì)極大的減少財(cái)務(wù)工作的工作量。任務(wù)一創(chuàng)立工資核算系統(tǒng)【工作情境】由于ABC公司員工眾多，因此工資的管理就變得非常重要，以前的工資核算及管理方法多為手工計(jì)算，雖然局部數(shù)據(jù)也用Excel來(lái)進(jìn)行計(jì)算，但都較為凌亂，不成體系，因此給李芳的工作帶來(lái)了巨大壓力。為了更加方便工資的管理，防止失誤，小李決定使用Excel建立一個(gè)完整的工資核算系統(tǒng)，從而減輕財(cái)務(wù)部門的工作量，提高工作效率。一、學(xué)習(xí)目標(biāo)1.了解工資核算的根本流程。2.理解工資管理中的常用函數(shù)。3.掌握工資核算系統(tǒng)的創(chuàng)立。二、工作流程根本思路：人事變動(dòng)、工資調(diào)整，以及全勤、缺勤、加班、遲到等信息是工資結(jié)算的根底，有了這些原始數(shù)據(jù)，就可以根據(jù)一定的公式，進(jìn)行工資結(jié)算和費(fèi)用分配了。三、實(shí)踐操作任何企事業(yè)單位都離不開工資管理，它不僅是一個(gè)單位管理薪資的重要手段，也是財(cái)務(wù)人員的一項(xiàng)重要工作內(nèi)容。舊的工作模式下，制作工資明細(xì)表是一項(xiàng)非常繁重的工作，難度大且容易出錯(cuò)。利用Excel2003那么可以快速制作出各種工資明細(xì)表、工資匯總表和工資條，使得這些難題迎刃而解。1.建立工資明細(xì)表員工根本工資工程的建立主要包括根本工程的輸入，以及對(duì)員工所屬部門的有效性設(shè)置。具體操作步驟如下：(1)新建Excel工作簿，將其命名為“工資核算系統(tǒng)〞。然后雙擊工作表Sheet1,重命名為“工資明細(xì)表〞。(2)輸入標(biāo)題及各個(gè)工資工程，并對(duì)輸入的內(nèi)容格式化，如圖3-1所示。圖3-1工資明細(xì)表(3)為了防止輸入錯(cuò)誤，下面對(duì)“所屬部門〞列應(yīng)用“數(shù)據(jù)有效性〞功能加以控制。選中單元格C4，單擊“數(shù)據(jù)〞︱“有效性〞命令，彈出“數(shù)據(jù)有效性〞對(duì)話框。(4)在“允許〞下拉列表框中選中“序列〞選項(xiàng)卡，然后在“來(lái)源〞文本框中輸入“企劃部，財(cái)務(wù)部，銷售部，生產(chǎn)部〞，各個(gè)部門名稱應(yīng)用英文狀態(tài)下的逗號(hào)隔開，如圖3-2所示。圖3-2“設(shè)置〞選項(xiàng)卡(5)單擊“確定〞按鈕，返回工作表中，然后將C4填充到該列的其他單元格中。(6)輸入員工的其他相關(guān)信息，最終結(jié)果如圖3-3所示。圖3-3輸入其他相關(guān)信息2.統(tǒng)計(jì)部門員工人數(shù)公司開展越來(lái)越大，員工也會(huì)越來(lái)越多，各部門的員工數(shù)量也在不斷變動(dòng)，統(tǒng)計(jì)各部門人數(shù)便成了一個(gè)問題。逐項(xiàng)查找恐怕不太可能，用篩選功能雖然可以減輕一局部工作量，但花費(fèi)的時(shí)間也不少，統(tǒng)計(jì)的時(shí)候一不小心還有可能出錯(cuò)。此時(shí)可以使用COUNTIF函數(shù)迅速統(tǒng)計(jì)出符合條件的單元格的數(shù)量。此函數(shù)的根本功能是計(jì)算區(qū)域中滿足給定條件的單元格個(gè)數(shù)。其語(yǔ)法格式為：COUNTIF(range,criteria)，其中range為需要計(jì)算滿足條件的單元格數(shù)目的單元格區(qū)域。criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為"32"、">32"或"apples"。假設(shè)想統(tǒng)計(jì)ABC公司各部門員工總數(shù)和男女員工人數(shù)，操作步驟如下：(1)雙擊“工資核算系統(tǒng)〞工作簿中的工作表標(biāo)簽Sheet2,將其重命名為“根本資料表〞。(2)翻開員工工資明細(xì)表，將其中的員工的主要數(shù)據(jù)復(fù)制到根本資料表中并補(bǔ)充相應(yīng)信息，然后按部門排序。結(jié)果如圖3-4所示。圖3-4按部門排序(3)選擇單元格區(qū)域A4：G7，如圖3-5所示。單擊“插入〞︱“名稱〞︱“定義〞命令，在彈出的“定義名稱〞對(duì)話框的“在當(dāng)前工作簿中的名稱〞文本框輸入“財(cái)務(wù)部〞，如圖3-6所示。單擊“添加〞按鈕，已選中的區(qū)域就被定義為“財(cái)務(wù)部〞了。圖3-5選擇單元格區(qū)域圖3-6定義財(cái)務(wù)部(4)在該對(duì)話框的“在當(dāng)前工作簿中的名稱〞文本框中輸入“企劃部〞，單擊對(duì)話框下方“引用位置〞文本框右邊的按鈕，在員工根本資料表中選擇區(qū)域A8：G11，如圖3-7所示再單擊，回到“定義名稱〞對(duì)話框。圖3-7定義企劃部(5)單擊“添加〞按鈕，已選中的區(qū)域被定義為“企劃部〞，如圖3-8所示。用同樣的方法分別定義A12:G14為“生產(chǎn)部〞，A15:G18為“銷售部〞，單擊“確定〞按鈕。這樣就定義好了四個(gè)部門，如圖3-9所示。它可以方便下面的統(tǒng)計(jì)工作。圖3-8定義企劃部

圖3-9定義企劃部(6)在“工資核算系統(tǒng)〞工作簿中新建“部門統(tǒng)計(jì)〞工作表，如圖3-10所示。圖3-10“部門統(tǒng)計(jì)〞工作表(7)選擇單元格C5，單擊工具欄中的按鈕，彈出“插入函數(shù)〞對(duì)話框，在“或選擇類別〞下拉列表框中選擇“統(tǒng)計(jì)〞選項(xiàng)，在“選擇函數(shù)〞列表框中選擇COUNTIF函數(shù)，如圖3-11所示。(8)單擊“確定〞按鈕，彈出“函數(shù)參數(shù)〞對(duì)話框，在Range文本框中輸入“財(cái)務(wù)部〞，在Criteria文本框中輸入“男〞，如圖3-12所示。圖3-11選擇函數(shù)

圖3-12設(shè)置參數(shù)函數(shù)(9)單擊“確定〞按鈕，單元格C5中就會(huì)顯示出財(cái)務(wù)部男員工的人數(shù)。(10)按照同樣的方法，統(tǒng)計(jì)出財(cái)務(wù)部女員工的人數(shù)，只需在“函數(shù)參數(shù)〞對(duì)話框中將Criteria文本框的“男〞改為“女〞即可。(11)選中合并后的單元格D5，在編輯欄中輸入公式“=COUNTIF(根本資料表!E4:E18,財(cái)務(wù)部)〞。按回車鍵，單元格D5中即可顯示財(cái)務(wù)部的總?cè)藬?shù)5。統(tǒng)計(jì)財(cái)務(wù)部的總?cè)藬?shù)也可直接用SUM函數(shù)直接計(jì)算，或者在編輯欄中輸入公式“=C5+C6〞。(12)按照同樣的方法，統(tǒng)計(jì)其他部門的人數(shù)，結(jié)果如圖3-13所示。圖3-13統(tǒng)計(jì)各部門人數(shù)圖3-14統(tǒng)計(jì)公司總?cè)藬?shù)(13)選中單元格D13，在編輯欄中輸入公式“=SUM(D5:D12)〞，按回車鍵，公司總?cè)藬?shù)就計(jì)算出來(lái)了，如圖3-14所示。3.統(tǒng)計(jì)員工年假一般單位的員工都有年假，只要在公司工作一年，就可以每年享受一定天數(shù)的帶薪假期。下面以ABC公司為例，根據(jù)前面創(chuàng)立的“根本資料表〞，為該公司的員工計(jì)算年假天數(shù)。假設(shè)該公司規(guī)定，任職滿一年的員工，年假為15天，以后工齡每增加一年，年假增加一天，滿6年后年假均為20天，滿10年后均為30天。統(tǒng)計(jì)員工年假的具體操作步驟如下：(1)在“工資核算系統(tǒng)〞工作簿中新建一張年假規(guī)那么表，并將其重命名為“年假規(guī)那么表〞，以方便統(tǒng)計(jì)年假，如圖3-15所示。(2)選中作為規(guī)那么的單元格區(qū)域，如圖3-16所示。在“定義名稱〞對(duì)話框中將其定義為“年假規(guī)那么〞。圖3-15“年假規(guī)那么表〞工作表圖3-16選中“年假規(guī)那么表〞中的單元格區(qū)域(3)為方便操作，在根本資料表中添加兩列——“工齡〞和“年假(天)〞，如圖3-17所示。(4)選中單元格H4，在編輯欄中輸入公式“=YEAR(NOW())-YEAR(D4)〞，假定當(dāng)前年份為2021年，那么按回車鍵后計(jì)算結(jié)果為16，如圖3-18所示。圖3-17“根本資料表“添加兩列圖3-18輸入工齡計(jì)算公式(5)選中單元格H4，將鼠標(biāo)指針移到單元格的右下角，待鼠標(biāo)指針變成“+〞形狀時(shí)，按住鼠標(biāo)左鍵并向下拖動(dòng)鼠標(biāo)將公式填充到該列中的其他單元格，釋放鼠標(biāo)時(shí)，其他員工的工齡就自動(dòng)顯示出來(lái)了，如圖3-19所示(6)選中單元格I4，輸入公式“=VLOOKUP(H4,年假規(guī)那么,2,1)〞,按回車鍵后即可得出對(duì)應(yīng)的年假天數(shù)，結(jié)果如圖3-20所示。圖3-19計(jì)算其他員工工齡

圖3-20輸入年假天數(shù)計(jì)算公式注：VLOOKUP函數(shù)的功能是在表格數(shù)組的首列查找值，并由此返回表格數(shù)組當(dāng)前行中其他列的值。其語(yǔ)法格式是：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)其中Lookup_value為需要在表格數(shù)組第一列中查找的數(shù)值。Table_array為兩列或多列數(shù)據(jù)。Col_index_num為table_array中待返回的匹配值的列序號(hào)。Range_lookup為邏輯值，指定希望VLOOKUP查找精確的匹配值還是近似匹配值。(7)選中單元格I4，將公式填充到該列的其他單元格中，即可自動(dòng)顯示其他員工的年假天數(shù)，如圖3-21所示。圖3-21計(jì)算其他員工的年假天數(shù)4.自動(dòng)更新根本工資每個(gè)員工都會(huì)有調(diào)整根本工資的時(shí)候，但是各人情況不同，根本工資調(diào)整的時(shí)間和幅度也不一樣，每月計(jì)算工資時(shí)，不可能把所有人的調(diào)薪記錄都查找一遍，這就有必要建立一個(gè)自動(dòng)更新的數(shù)據(jù)庫(kù)，以方便準(zhǔn)確及時(shí)地更新數(shù)據(jù)。在Excel中可以利用列查找函數(shù)VLOOPUP來(lái)自動(dòng)更新每位員工的根本工資。具體操作步驟如下：〔1〕在“工資核算系統(tǒng)〞工作簿中創(chuàng)立一張新的工作表，并將其重命名為“工資調(diào)整〞?！?〕輸入標(biāo)題，然后切換到該工作簿中的“根本資料表〞，將局部信息復(fù)制到“工資調(diào)整〞工作表，包括員工編號(hào)、姓名、性別、入職時(shí)間、所屬部門和職工類別等，結(jié)果如圖3-22所示。圖3-22創(chuàng)立工資調(diào)整表〔3〕在工作表中的G4:I18單元格中添加如圖3-23所示的工程和內(nèi)容?！?〕將“工資調(diào)整〞工作表進(jìn)行排序，以“員工編號(hào)〞為第一關(guān)鍵字升序排序，“調(diào)整年〞為第二關(guān)鍵字降序排序，“調(diào)整月〞為第三關(guān)鍵字降序排序，排序結(jié)果如圖3-24所示。圖3-23添加工資調(diào)整數(shù)據(jù)圖3-24對(duì)“工資調(diào)整〞工作表進(jìn)行排序(5)單擊“插入〞︱“名稱〞︱“定義〞命令，在翻開“定義名稱〞對(duì)話框中定義單元格區(qū)域A3:I18為“工資調(diào)整〞。(6)切換到“工資明細(xì)表〞，刪除原根本工資數(shù)據(jù)。選中單元格D4，輸入公式“=VLOOKUP(A4,工資調(diào)整,9,0)〞，按回車鍵即可顯示該員工最新調(diào)整的工資了，如圖3-25所示(7)拖曳單元格右下角的填充句柄，將公式填充到該列的其他單元格中，即可顯示其他員工的最新基本工資。圖3-25顯示最新根本工資5.核算加班費(fèi)每個(gè)公司在出勤獎(jiǎng)罰制度上都有相應(yīng)的規(guī)定，一般包括加班費(fèi)、遲到扣款、病假扣款、事假扣款以及月底出勤獎(jiǎng)金等，這些都與員工的應(yīng)發(fā)工資息息相關(guān)。以ABC公司為例，假設(shè)公司規(guī)定每天加班時(shí)間在2個(gè)小時(shí)以內(nèi)，加班費(fèi)為40元，超過2個(gè)小時(shí)，加班費(fèi)為80元。按照這個(gè)標(biāo)準(zhǔn)給公司員工計(jì)算加班費(fèi)，步驟如下：(1)在“工資核算系統(tǒng)〞工作簿中創(chuàng)立一個(gè)新的工作表，并將其命名為“加班記錄〞。(2)在該工作表中輸入包括員工編號(hào)、姓名、性別、所屬部門、加班的起止時(shí)間等各項(xiàng)信息，如圖3-26所示。(3)切換到“工資明細(xì)表〞，在該表中選中單元格G4，輸入公式“=IF(HOUR(加班記錄!J4-加班記錄!I4)<=2,40,80)〞，按回車鍵，即可計(jì)算出加班費(fèi)，如圖3-27所示。注：IF〔〕函數(shù)的根本結(jié)構(gòu)為IF(logical_test,value_if_true,value_if_false)，其中Logical_test表示計(jì)算結(jié)果為TRUE或FALSE的任意值或表達(dá)式。Value_if_true即logical_test成立時(shí)返回的值。Value_if_false即為logical_test為FALSE時(shí)返回的值。本例中，第一個(gè)參數(shù)用來(lái)判斷加班時(shí)間是否小于2，如果小于2那么返回40的計(jì)算結(jié)果，否那么取第二個(gè)結(jié)果即加班費(fèi)為80。(4)將單元格G4中的公式填充到該列的其他單元格中，即可計(jì)算出其他員工的加班費(fèi)。圖3-27計(jì)算加班費(fèi)6.核算缺勤扣款A(yù)BC公司規(guī)定，請(qǐng)事假要按天扣工資，而請(qǐng)病假一天只扣半天的工資；遲到15分鐘以內(nèi)扣10元，超過15分鐘扣半天的工資；每月按實(shí)際天數(shù)計(jì)算，如6月份30天，每天的工資為根本工資除以30。下面就介紹一下缺勤扣款的核算方法?！?〕病假扣款計(jì)算病假扣款的操作步驟如下：1)在“工資核算系統(tǒng)〞工作簿中創(chuàng)立一張新的工作表，并將其命名為“請(qǐng)假記錄〞工作表，輸入相應(yīng)的數(shù)據(jù)并按“員工編號(hào)〞升序排序，如圖3-28所示。圖3-28“請(qǐng)假記錄〞工作表2)將“工資明細(xì)表〞拖動(dòng)到“請(qǐng)假記錄〞工作表旁邊，以便于下面的操作。3)選中單元格G4，輸入公式“=ROUND(工資調(diào)整!I4/30/2*請(qǐng)假記錄!D4,0)〞,如圖3-29所示。按回車鍵，顯示結(jié)果該員工病假扣款金額為33。圖3-29輸入病假扣款計(jì)算公式

注：ROUND()函數(shù)的主要功能是返回某個(gè)按指定位數(shù)取整后的數(shù)字。其語(yǔ)法格式是：ROUND(number,num_digits)，其中number為需要進(jìn)行四舍五入的數(shù)字。number_digits為指定的位數(shù)，按此位數(shù)進(jìn)行四舍五入。4)用鼠標(biāo)拖曳單元格G4的填充句柄，將公式填充到該列的其他單元格中，計(jì)算出其他員工的病假扣款?！?〕事假扣款根據(jù)ABC公司的規(guī)定，請(qǐng)事假按天扣工資。計(jì)算事假扣款的操作步驟如下：1)選中“請(qǐng)假記錄〞工作表中的單元格H4，輸入公式“=ROUND(工資調(diào)整!I4/30*請(qǐng)假記錄!E4,0)〞。2)按回車鍵，顯示該員工事假扣款結(jié)果為0。由于該員工在6月份沒有請(qǐng)過事假，因此事假扣款金額為0。3)用鼠標(biāo)拖曳單元格H4，將公式填充到該列的其他單元格中，計(jì)算出其他員工的事假扣款，結(jié)果如圖3-30所示。圖3-30計(jì)算其他員工的事假扣款(3)遲到扣款A(yù)BC公司規(guī)定遲到15分鐘以內(nèi)扣10元，超過15分鐘扣半天的工資。計(jì)算遲到扣款的操作步驟如下：1)選中“請(qǐng)假記錄〞工作表中的單元格I4，輸入公式“=ROUND(IF(F4>15,工資調(diào)整!I4/30/2,10-IF(F4=0,10,0)),0)〞。2)按回車鍵，即可顯示扣款金額。因?yàn)樵搯T工本月遲到時(shí)間為0分鐘，因此不扣款。3)用鼠標(biāo)拖曳單元格I4的填充句柄，將公式填充到該列的其他單元格中，計(jì)算出其他員工的遲到扣款。4)選中單元格區(qū)域G4:I18,將單元格格式設(shè)置為帶兩位小數(shù)的數(shù)值格式?！?〕數(shù)據(jù)鏈接計(jì)算好了員工的缺勤扣款之后，可以利用復(fù)制粘貼的方法把這些數(shù)據(jù)復(fù)制到“工資明細(xì)表〞中，但這樣，一旦發(fā)現(xiàn)缺勤扣款計(jì)算有誤需要重新修改時(shí)，“工資明細(xì)表〞中缺勤扣款就要進(jìn)行更正，很費(fèi)時(shí)間和精力。應(yīng)用Excel2003的數(shù)據(jù)鏈接功能，可以使計(jì)算結(jié)果隨著數(shù)據(jù)源的變化自動(dòng)更新。下面把“請(qǐng)假記錄〞工作表的數(shù)據(jù)鏈接到“工資明細(xì)表〞中，具體操作步驟如下：1)在“工資明細(xì)表〞中選中單元格I4，在編輯欄中輸入“=〞，如圖3-31所示。圖3-31數(shù)據(jù)鏈接2)切換到“請(qǐng)假記錄〞工作表，選中單元格G4。這時(shí)，在單元格G4的四周有虛線閃動(dòng)，而編輯欄的公式并不顯示G4中的公式，而是變成了“=請(qǐng)假記錄!G4〞。3)不切換工作表，繼續(xù)在編輯欄中輸入“+〞，公式變?yōu)椤?請(qǐng)假記錄!G4+〞，選中單元格H4，這時(shí)，編輯欄公式變?yōu)椤?請(qǐng)假記錄!G4+請(qǐng)假記錄!H4〞，單元格H4四周虛線閃動(dòng)。4)按照同樣的方法，在編輯欄中繼續(xù)輸入“+〞。選中“請(qǐng)假記錄〞工作表中的單元格I4，切換到“工資明細(xì)表〞中，單元格I4的公式變?yōu)椤?請(qǐng)假記錄!G4+請(qǐng)假記錄!H4+請(qǐng)假記錄!I4〞。5)按回車鍵，此時(shí)在單元格I4中的數(shù)據(jù)顯示為33，該員工的缺勤扣款總共為33元，如圖3-32所示。5)按回車鍵，此時(shí)在單元格I4中的數(shù)據(jù)顯示為33，該員工的缺勤扣款總共為33元，如圖3-32所示。圖3-32顯示缺勤扣款6)用鼠標(biāo)拖曳單元格I4的填充句柄，將公式填充到該列的其他單元格中，計(jì)算出其他員工的缺勤扣款，結(jié)果如圖3-33所示。圖3-33計(jì)算其他員工缺勤扣款上述拖曳鼠標(biāo)自動(dòng)顯示其他單元格計(jì)算結(jié)果的方法雖然簡(jiǎn)便易行，但是以各工作表排序方式相同，且員工資料相同為前提，一旦某個(gè)工作表中的員工數(shù)量或內(nèi)容和其他工作表不一致，拖曳填充句柄顯示的結(jié)果就是錯(cuò)誤的。為謹(jǐn)慎起見，最好還是結(jié)合VLOOKUP函數(shù)進(jìn)行計(jì)算。下面以鏈接缺勤扣款為例，介紹一下VLOOKUP函數(shù)的應(yīng)用。為了更清楚地說(shuō)明問題，先將前面創(chuàng)立的“工資明細(xì)表〞中“缺勤扣款〞列的公式刪除。具體操作步驟如下：(1)在“請(qǐng)假記錄〞工作表中加一列“缺勤總扣款〞，然后選中單元格J4，輸入公式“=G4+H4+I4〞。(2)按回車鍵，顯示結(jié)果為33，說(shuō)明該員工的缺勤總扣款為33元。(3)用鼠標(biāo)拖曳單元格J4的填充句柄，將公式填充到該列的其他單元格中，計(jì)算出其他員工的缺勤總扣款，結(jié)果如圖3-34所示。(4)單擊“插入〞︱“名稱〞︱“定義〞命令，為區(qū)域A4:J18定義名稱為“缺勤總扣款〞。(5)切換到“工資明細(xì)表〞，選中單元格I4，輸入公式“=VLOOKUP(A4,缺勤總扣款，10，0)〞。圖3-34計(jì)算其他員工的缺勤總扣款(6)按回車鍵，顯示結(jié)果為33，結(jié)果正確。向下拖曳填充句柄，將公式填充到該列的其他單元格中，顯示出其他員工的缺勤扣款。對(duì)照“請(qǐng)假記錄〞工作表中的數(shù)據(jù)，可以發(fā)現(xiàn)其他員工的缺勤扣款均正確。由于使用了VLOOKUP()函數(shù)，只要員工號(hào)準(zhǔn)確無(wú)誤，就能保證缺勤扣款的準(zhǔn)確無(wú)誤。ABC公司規(guī)定：只要員工每月請(qǐng)假天數(shù)不超過2天且遲到不超過30分鐘，月底時(shí)就可以拿到出勤獎(jiǎng)金200元。下面看一下如何計(jì)算。(1)為方便計(jì)算，在“請(qǐng)假記錄〞工作表中添加“出勤獎(jiǎng)金〞列。(2)選中單元格K4，輸入公式“=IF(IF((D4+E4)<=2,0,1)+IF(F4<=30,0,1)=0,200,0)〞,如圖3-35所示。圖3-35計(jì)算出勤獎(jiǎng)金注：在上例中，出現(xiàn)了IF〔〕函數(shù)的嵌套使用。當(dāng)出現(xiàn)三種以上的結(jié)果時(shí)，就需要嵌套使用IF〔〕函數(shù)。在嵌套使用中，第二個(gè)IF語(yǔ)句同時(shí)也是第一個(gè)IF語(yǔ)句的參數(shù)value_if_false參數(shù)。同樣，第三個(gè)IF語(yǔ)句是第二個(gè)IF語(yǔ)句的參數(shù)value_if_false參數(shù)。如果第一個(gè)logical_test為TRUE，那么返回第一個(gè)結(jié)果；如果第一個(gè)logical_test為FALSE，那么計(jì)算第二個(gè)IF語(yǔ)句，以此類推。本例中，那么是將IF函數(shù)用在了第一個(gè)參數(shù)判斷條件中，本例中的logical_test為一個(gè)復(fù)雜的判斷條件，需將兩個(gè)內(nèi)嵌IF函數(shù)的計(jì)算結(jié)果相加，然后根據(jù)計(jì)算結(jié)果再來(lái)判斷員工的出勤獎(jiǎng)金。(3)按回車鍵，顯示結(jié)果為200。因?yàn)樵搯T工請(qǐng)假時(shí)間為一天，沒有遲到，所以拿到了200元的出勤獎(jiǎng)金。(4)向下拖曳單元格K4的填充句柄，將公式填充到該列的其他單元格中，即可得到其他員工的出勤獎(jiǎng)金，如圖3-36所示。圖3-36填充公式(5)切換到“工資明細(xì)表〞，選中單元格E4，輸入公式“=請(qǐng)假記錄!K4〞，如圖3-37所示。(6)按回車鍵，顯示結(jié)果為200。該員工符合月底獎(jiǎng)金的條件，因此獎(jiǎng)勵(lì)200元。(7)向下拖曳單元格E4的填充句柄，將公式填充到該列的其他單元格中，即可得到其他員工的出勤獎(jiǎng)金。圖3-37計(jì)算其他員工的出勤獎(jiǎng)金8.合計(jì)應(yīng)發(fā)工資根據(jù)根本工資、出勤獎(jiǎng)金、補(bǔ)貼和加班費(fèi)，就可以計(jì)算出員工的應(yīng)發(fā)工資。操作方法如下：(1)將“工資明細(xì)表〞按所屬部門排序，并添加表格邊框，結(jié)果如圖3-38所示。圖3-38設(shè)置工資明細(xì)表(2)在F列中輸入每個(gè)員工的補(bǔ)貼，如圖3-39所示。按公司規(guī)定，補(bǔ)貼金包括午餐補(bǔ)貼和話費(fèi)補(bǔ)貼等，部門不同，補(bǔ)貼也有所不同。圖3-39輸入補(bǔ)貼(3)選中單元格H4，輸入公式“=SUM(D4:G4)〞，按回車鍵即可得出該員工的應(yīng)發(fā)工資，如圖3-40所示。圖3-40計(jì)算應(yīng)發(fā)工資(4)用鼠標(biāo)向下拖曳單元格H4的填充句柄，將公式填充到該列的其他單元格中，即可得出其他員工的應(yīng)發(fā)工資，如圖3-41所示。圖3-41計(jì)算其他員工的應(yīng)發(fā)工資9.代扣個(gè)人所得稅具體步驟如下：(1)對(duì)“工資明細(xì)表〞中員工按應(yīng)發(fā)工資進(jìn)行自動(dòng)篩選。選中工作表中的單元格區(qū)域A3:L18，單擊“數(shù)據(jù)〞︱“篩選〞︱“自動(dòng)篩選〞命令，進(jìn)入自動(dòng)篩選狀態(tài)，如圖3-42所示。圖3-42自動(dòng)篩選數(shù)據(jù)(2)單擊“應(yīng)發(fā)工資〞列的篩選按鈕，在彈出的下拉列表中選擇“自定義〞選項(xiàng)，即可翻開“自定義自動(dòng)篩選方式〞對(duì)話框，參數(shù)設(shè)置如圖3-43所示。(3〕單擊“確定〞按鈕，篩選出個(gè)人所得稅率為0，即不扣稅的員工，如圖3-44所示。圖3-43設(shè)置篩選條件圖3-44篩選出局部員工(4〕直接在單元格中將所得稅金額設(shè)置為0即可。(5)在“應(yīng)發(fā)工資〞下拉列表中選擇“全部〞選項(xiàng)，顯示全部資料。然后再選擇“自定義〞選項(xiàng)，翻開“自定義自動(dòng)篩選方式〞對(duì)話框，參數(shù)設(shè)置如圖3-45所示。 圖3-45設(shè)置新篩選條件(6)單擊“確定〞按鈕，篩選出所得稅率為3%的員工，如圖3-46所示。(7)選中單元格K11，輸入公式“=(H11-3500)*0.03〞,按回車鍵顯示結(jié)果為1.2，該員工應(yīng)扣所得稅1.2元，然后拖曳填充句柄將公式填充到下面的單元格，如圖3-47所示。圖3-46篩選結(jié)果圖3-47輸入所得稅的計(jì)算公式〔8〕依次計(jì)算其他員工的所得稅，如圖3-48所示?！?〕單擊“數(shù)據(jù)〞|“篩選〞|“自動(dòng)篩選〞命令，退出自動(dòng)篩選狀態(tài)。圖3-48計(jì)算其他員工所得稅10.代繳養(yǎng)老保險(xiǎn)金一般的單位都會(huì)給員工繳納養(yǎng)老保險(xiǎn)，假設(shè)ABC公司為每個(gè)人交納的養(yǎng)老保險(xiǎn)金是根本工資的10%。核算養(yǎng)老保險(xiǎn)金的操作步驟如下：(1)翻開“工資明細(xì)表〞，選中單元格J4，輸入公式“=H4*10%〞，如圖3-49所示。圖3-49輸入保險(xiǎn)金的計(jì)算公式(2)按回車鍵，顯示結(jié)果為343.00，說(shuō)明該員工應(yīng)繳納的養(yǎng)老保險(xiǎn)金為343元。(3)選中單元格J4，向下拖曳填充句柄，顯示出其他員工應(yīng)繳納的養(yǎng)老保險(xiǎn)金額，結(jié)果如圖3-50所示。圖3-50計(jì)算其他員工的養(yǎng)老保險(xiǎn)金11.合計(jì)實(shí)發(fā)工資下面進(jìn)行薪資匯總。每個(gè)員工月底能拿到的工資，其實(shí)就是扣完缺勤、稅費(fèi)后的實(shí)得工資。操作步驟如下：(1)在“工資明細(xì)表〞中選中單元格L4，輸入公式“=H4-I4-J4-K4〞，如圖3-51所示。圖3-51輸入實(shí)發(fā)工資的計(jì)算公式(2)按回車鍵，顯示結(jié)果為3057.00，該員工實(shí)得薪資為3057元。(3)選中單元格L4，向下拖曳填充句柄，將公式填充到該列的其他單元格中，即可得出其他員工的實(shí)發(fā)工資，結(jié)果如圖3-52所示。圖3-52計(jì)算其他員工的實(shí)發(fā)工資四、問題深究依照我國(guó)稅法的最新規(guī)定，在職員工應(yīng)繳納個(gè)人所得稅。所以怎么計(jì)算所得稅呢？李芳認(rèn)真查閱了相關(guān)資料。首先李芳認(rèn)真了解了有關(guān)咱們國(guó)家對(duì)職工工資個(gè)人所得稅的起征點(diǎn)和計(jì)算方法。按月征收，國(guó)家新規(guī)定的起征點(diǎn)為3500元，即對(duì)每月超過3500元以上的局部征稅。由于工資、薪金所得在計(jì)算應(yīng)納所得稅時(shí)，應(yīng)用的是超額累進(jìn)稅率，所以計(jì)算比較繁瑣。運(yùn)用速算扣除數(shù)計(jì)算法，可以簡(jiǎn)化計(jì)算過程。工資、薪金所得適用的速算扣除數(shù)見表3-1。工資、薪金的計(jì)算公式為：應(yīng)納稅額=應(yīng)納稅所得額×適用稅率—速算扣除數(shù)。舉例如下：某納稅人2021年1月在中國(guó)境內(nèi)取得工資、薪金收入4800元，請(qǐng)計(jì)算其應(yīng)納稅額。應(yīng)納稅所得額=4800-3500=1300(元)應(yīng)納稅額=1300×3%-0=39(元)因此該納稅人1月份應(yīng)納稅額為39元。根據(jù)表3-1和工資、薪金計(jì)算公式，試計(jì)算一下最新規(guī)定下的個(gè)人所得稅。五、知識(shí)拓展在創(chuàng)立工資核算系統(tǒng)的過程中，會(huì)遇見很多需要計(jì)算的地方，其中Excel給予很大的幫助，其中的微妙之處就是函數(shù)的運(yùn)用和公式的計(jì)算。1.COUNTIF計(jì)算區(qū)域中滿足給定條件的單元格個(gè)數(shù)。語(yǔ)法：COUNTIF(range,criteria)range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為"32"、">32"或"apples"。2.ROUND返回某個(gè)按指定位數(shù)取整后的數(shù)字。語(yǔ)法：ROUND(number,num_digits)number為需要進(jìn)行四舍五入的數(shù)字。number_digits為指定的位數(shù)，按此位數(shù)進(jìn)行四舍五入。如果number_digits大于0，那么四舍五入到指定的小數(shù)位。如果number_digits等于0，那么四舍五入到最接近的整數(shù)。如過number_digits小于0，那么在小數(shù)點(diǎn)左側(cè)進(jìn)行四舍五入。3.查找與引用函數(shù)VLOOKUPVLOOKUP函數(shù)在表格數(shù)組的首列查找值，并由此返回表格數(shù)組當(dāng)前行中其他列的值。VLOOKUP中的V表示垂直方向。當(dāng)比較值位于需要查找的數(shù)據(jù)左邊的一列時(shí)，可以使用VLOOKUP，而不用HLOOKUP。語(yǔ)法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)說(shuō)明：Lookup_value為需要在表格數(shù)組〔數(shù)組：用于建立可生成多個(gè)結(jié)果或可對(duì)在行和列中排列的一組參數(shù)進(jìn)行運(yùn)算的單個(gè)公式。數(shù)組區(qū)域共用一個(gè)公式；數(shù)組常量是用作參數(shù)的一組常量?！车谝涣兄胁檎业臄?shù)值。Lookup_value可以為數(shù)值或引用。假設(shè)lookup_value小于table_array第一列中的最小值，VLOOKUP將返回錯(cuò)誤值#N/A。Table_array為兩列或多列數(shù)據(jù)?？墒褂脤?duì)區(qū)域的引用或區(qū)域名稱。table_array第一列中的值是由lookup_value搜索的值。這些值可以是文本、數(shù)字或邏輯值。不區(qū)分大小寫。Col_index_num為table_array中待返回的匹配值的列序號(hào)。Col_index_num為1時(shí)，返回table_array第一列中的數(shù)值；col_index_num為2，返回table_array第二列中的數(shù)值，以此類推。如果col_index_num小于1，VLOOKUP返回錯(cuò)誤值#VALUE!。大于table_array的列數(shù)，VLOOKUP返回錯(cuò)誤值#REF!。Range_lookup為邏輯值，指定希望VLOOKUP查找精確的匹配值還是近似匹配值：如果為TRUE或省略，那么返回精確匹配值或近似匹配值。也就是說(shuō)，如果找不到精確匹配值，那么返回小于lookup_value的最大數(shù)值。table_array第一列中的值必須以升序排序；否那么VLOOKUP可能無(wú)法返回正確的值。如果為FALSE，VLOOKUP將只尋找精確匹配值。在此情況下，table_array第一列的值不需要排序。如果table_array第一列中有兩個(gè)或多個(gè)值與lookup_value匹配，那么使用第一個(gè)找到的值。如果找不到精確匹配值，那么返回錯(cuò)誤值#N/A。注意：在table_array第一列中搜索文本值時(shí)，請(qǐng)確保table_array第一列中的數(shù)據(jù)沒有前導(dǎo)空格、尾隨空格、不一致的直引號(hào)〔'或"〕、彎引號(hào)〔‘或“〕或非打印字符。在上述情況下，VLOOKUP可能返回不正確或意外的值。在搜索數(shù)字或日期值時(shí)，請(qǐng)確保table_array第一列中的數(shù)據(jù)未保存為文本值。否那么，VLOOKUP可能返回不正確或意外的值。如果range_lookup為FALSE且lookup_value為文本，那么可以在lookup_value中使用通配符、問號(hào)(?)和星號(hào)(*)。問號(hào)匹配任意單個(gè)字符；星號(hào)匹配任意字符序列。如果您要查找實(shí)際的問號(hào)或星號(hào)本身，請(qǐng)?jiān)谠撟址版I入波形符(~)。六、課后訓(xùn)練利用Excel函數(shù)和公式計(jì)算工資表中的各項(xiàng)數(shù)據(jù)。具體要求如下：〔1〕啟動(dòng)Excel，將Sheet1重命名為“工資表〞，將表Sheet2重命名為“職務(wù)聘任表〞，參照?qǐng)D3-53和圖3-54輸入有關(guān)數(shù)據(jù)并設(shè)置格式。圖3-53“工資表〞數(shù)據(jù)及樣式圖3-54“職務(wù)聘任表〞數(shù)據(jù)及樣式(2)使用“職務(wù)聘任表〞中人員職務(wù)數(shù)據(jù)，結(jié)合IF函數(shù)，計(jì)算“工資表〞中人員的職務(wù)工資。標(biāo)準(zhǔn)為：教授600元、副教授500元、講師300元、助教200元。(3)計(jì)算Sheet1中的“應(yīng)發(fā)工資〞〔根本工資＋職務(wù)工資＋獎(jiǎng)金〕、實(shí)發(fā)工資〔應(yīng)發(fā)工資-水電費(fèi)〕及各項(xiàng)合計(jì)。所有數(shù)據(jù)保存2位小數(shù)。(4)將文件以“zy3-1〞為文件名保存在個(gè)人文件夾。任務(wù)二員工工資的管理【工作情境】ABC公司為了更好的鼓勵(lì)員工，提高經(jīng)濟(jì)效益。因此對(duì)工資管理工作極為重視，公司總經(jīng)理要求隨時(shí)掌握員工工資的變動(dòng)情況，并根據(jù)變化適時(shí)推出新的工資調(diào)整措施。這就要求李芳必須做好公司工資管理工作，以滿足企業(yè)管理的需求，從而更好的發(fā)揮會(huì)計(jì)的管理職能。一、學(xué)習(xí)目標(biāo)1.學(xué)會(huì)工資表的分類匯總2.工資表的公式隱藏3.制作工資條4.創(chuàng)立“工資核算系統(tǒng)〞的模板5.系統(tǒng)模板的應(yīng)用二、工作流程工作表制作完畢后，用戶需要對(duì)表中的數(shù)據(jù)進(jìn)行編輯、更新和管理。工資條是發(fā)放工資時(shí)交給員工的工資工程清單，其數(shù)據(jù)來(lái)源于工資表。由于工資條是發(fā)放給員工個(gè)人的，所以工資條應(yīng)該包括工資中各個(gè)組成局部的工程名稱和數(shù)值。三、實(shí)踐操作1.分類匯總要統(tǒng)計(jì)各個(gè)部門的月工資總額和平均值，雖然用SUM()函數(shù)和AVERAGE()函數(shù)可以做到，但數(shù)據(jù)較多且復(fù)雜時(shí)，這兩個(gè)函數(shù)的功能就顯得有局限性了。下面介紹Excel的另一項(xiàng)功能——分類匯總。分類匯總是對(duì)數(shù)據(jù)清單上的數(shù)據(jù)進(jìn)行分析的一種方法，它可以在數(shù)據(jù)清單上插入分類匯總行，然后按照選擇的方式對(duì)數(shù)據(jù)進(jìn)行匯總。同時(shí)，再插入分類匯總時(shí)，Excel還會(huì)自動(dòng)在數(shù)據(jù)清單底部插入一個(gè)總計(jì)行。下面就統(tǒng)計(jì)ABC公司各部門2021年1月發(fā)放的工資總額，并計(jì)算出各部門員工的月工資，其操作步驟如下：〔1〕分類匯總工資總額計(jì)算分類匯總薪資總額的操作步驟如下：1)翻開“工資明細(xì)表〞，將數(shù)據(jù)按部門排序，然后選擇A3:L18單元格區(qū)域。2)單擊“數(shù)據(jù)〞“分類匯總〞命令，彈出“分類匯總〞對(duì)話框。在“分類字段〞下拉列表框中選擇“所屬部門〞選項(xiàng)，在“匯總方式〞下拉列表框中選擇“求和〞選項(xiàng)，在“選定匯總項(xiàng)〞列表框中選中“實(shí)發(fā)工資〞復(fù)選框，如圖3-55所示。圖3-55“分類匯總〞對(duì)話框3)單擊“確定〞按鈕，分類匯總效果如圖3-56所示。圖3-56分類匯總結(jié)果4〕分類匯總的數(shù)據(jù)清單中，可以隱藏明細(xì)數(shù)據(jù)，以便更加清晰地顯示匯總信息。在第8行行號(hào)的左側(cè)單擊按鈕，此時(shí)，財(cái)務(wù)部明細(xì)數(shù)據(jù)被隱藏，只顯示該部門匯總數(shù)據(jù)，如圖3-57所示。如果要重新顯示明細(xì)數(shù)據(jù)，只要單擊旁邊的按鈕即可。圖3-57隱藏明細(xì)數(shù)據(jù)5〕單擊第23行行號(hào)旁邊的按鈕，也就是最外層的按鈕，可隱藏全部明細(xì)數(shù)據(jù)。明細(xì)數(shù)據(jù)全部隱藏后，只顯示所有部門的“總計(jì)〞數(shù)據(jù)，如圖3-58所示。圖3-58隱藏全部數(shù)據(jù)此時(shí)在行號(hào)旁邊有三個(gè)可以顯示數(shù)據(jù)匯總層次的按鈕

。單擊

按鈕，隱藏全部明細(xì)數(shù)據(jù)，效果等同于單擊最外層的按鈕。6)單擊

按鈕，隱藏各部門明細(xì)數(shù)據(jù)，只顯示部門分類匯總和共計(jì)數(shù)據(jù)。結(jié)果如圖3-59所示。7)單擊

按鈕，顯示全部明細(xì)數(shù)據(jù)和分類匯總數(shù)據(jù)。圖3-59顯示部門匯總〔2〕嵌套平均值的分類匯總要顯示各部門的薪資匯總數(shù)據(jù)，并且顯示各部門薪資數(shù)據(jù)的平均值，就必須在現(xiàn)有分類匯總的根底上再添加一個(gè)嵌套的分類匯總。具體操作步驟如下：1)在現(xiàn)有分類匯總的根底上再次應(yīng)用分類匯總功能。單擊“數(shù)據(jù)〞︱“分類匯總〞命令，此時(shí)彈出“分類匯總〞對(duì)話框。2)在“匯總方式〞下拉列表框中選擇“平均值〞選項(xiàng)，并且取消選擇“替換當(dāng)前分類匯總〞復(fù)選框，如圖3-60所示。圖3-60設(shè)置平均值分類匯總3)單擊“確定〞按鈕，嵌套的分類匯總就創(chuàng)立完成了，數(shù)據(jù)清單中不僅有匯總值，還有平均值，如圖3-61所示。如果想取消分類匯總，可單擊“數(shù)據(jù)〞︱“分類匯總〞命令，在彈出的“分類匯總〞對(duì)話框中單擊“全部刪除〞按鈕，這樣分類匯總就被取消了。圖3-61嵌套平均值分類匯總2.制作工資條1〕定義單元格計(jì)算完畢工資，可以根據(jù)需要“工資明細(xì)表〞中的數(shù)據(jù)制作工資條，首先進(jìn)行單元格定義。具體操作步驟如下：1)在“工資明細(xì)表〞按員工編號(hào)升序排列，結(jié)果如圖3-62所示圖3-62將工作表按升序排序2)選中單元格區(qū)域A3:L18，將本單元格區(qū)域命名為“工資數(shù)據(jù)〞?！?〕制作工資條1)在“工資明細(xì)表〞的后面插入一張工作表，并將其重命名為“工資條〞。2)將“工資明細(xì)表〞中的工資工程復(fù)制到“工資條〞工作表中，插入一個(gè)“月份〞列，然后在第一行輸入標(biāo)題，并設(shè)置格式，如圖3-63所示。圖3-63創(chuàng)立“工資條〞工作表3)在單元格A3中輸入公式“=NOW()〞，然后翻開“單元格格式〞對(duì)話框，將單元格格式設(shè)置為“××年××月〞的格式。4)在單元格B3中輸入員工的編號(hào)0001，然后單擊單元格C3，輸入公式“=VLOOKUP(B3,工資數(shù)據(jù),2,0)〞，按回車鍵，結(jié)果如圖3-64所示。圖3-64引用相應(yīng)的公式5)依次在單元格D3～M3中輸入公式：=VLOOKUP(B3,工資數(shù)據(jù),3,0)、=VLOOKUP(B3,工資數(shù)據(jù),4,0)、=VLOOKUP(B3,工資數(shù)據(jù),5,0)......=VLOOKUP(B3,工資數(shù)據(jù),12,0)，那么數(shù)據(jù)全部引用過來(lái)，結(jié)果如圖3-65所示。圖3-65引用數(shù)