在一個(gè)企業(yè)中，每一個(gè)月人事部門都需要統(tǒng)計(jì)員工的工資。由于員工的工資涉及到員工的出勤量、福利以及獎金等數(shù)據(jù)，還需要對個(gè)人所得稅等進(jìn)行計(jì)算，所以，這個(gè)時(shí)候如果手工計(jì)算員工工資的工作量是非常大的。本章將介紹利用Excel來制作一個(gè)員工工資管理系統(tǒng)，并能夠自動生成工資條。1創(chuàng)建員工出勤統(tǒng)計(jì)表創(chuàng)建員工出勤統(tǒng)計(jì)表 員工出勤統(tǒng)計(jì)表是用來記錄企業(yè)員工出勤情況的表格，可以參考上一章內(nèi)容創(chuàng)建。2創(chuàng)建員工福利表創(chuàng)建員工福利表 “員工福利表”是記錄員工福利情況的基本表格，主要應(yīng)包括“員工編號”、“員工姓名”、“所在部門”、“住房補(bǔ)貼”、“車費(fèi)補(bǔ)貼”和“保險(xiǎn)金”等。其中，“員工編號”、“員工姓名”、和“所在部門”的字段值應(yīng)與“員工出勤統(tǒng)計(jì)表”中的數(shù)據(jù)一致。3創(chuàng)建員工獎金表創(chuàng)建員工獎金表 “員工獎金表”是記錄員工獎金情況的基本表格，主要應(yīng)包括“員工姓名”、“所在部門”和“獎金”等。同樣，這里的“員工姓名”和“所在部門”的字段值也要與“員工出勤統(tǒng)計(jì)表”和“員工福利表”中的數(shù)據(jù)保持一致。4創(chuàng)建員工工資表“員工工資表”的作用就是匯總上述3個(gè)表格中的數(shù)據(jù)，然后計(jì)算出每一位員工最后應(yīng)該獲得的實(shí)際工資。因此，“員工工資表”應(yīng)該包括“員工編號”、“員工姓名”、“所在部門”、“基本工資”、“獎金”、“住房補(bǔ)助”、“保險(xiǎn)金”、“應(yīng)發(fā)金額”、“扣稅所得額”、“個(gè)人所得稅”以及“實(shí)發(fā)工資”等字段。根據(jù)上述內(nèi)容創(chuàng)建“員工工資表”的具體步驟如下：插入一個(gè)新工作表，并將其更名為“員工工資表”。在“員工工資表”中輸入相應(yīng)的字段名稱。創(chuàng)建完“員工工資表”后，就需要輸入數(shù)據(jù)了。首先，用自動填充功能輸入員工編號。1輸入出勤數(shù)據(jù)輸入出勤數(shù)據(jù)因?yàn)榘ā皢T工姓名”、“所在部門”、“請假扣款”在內(nèi)的數(shù)據(jù)都在“員工出勤統(tǒng)計(jì)表”中，所以，可以直接引用其中的數(shù)據(jù)。但如果對每一組數(shù)據(jù)都進(jìn)行搜索數(shù)據(jù)設(shè)置的話將非常麻煩，為此，可以使用Excel中提供的“定義名稱”功能來簡化操作。定義名稱的具體步驟如下：定義名稱的具體步驟如下：切換到“員工出勤統(tǒng)計(jì)表”中，選擇單元格區(qū)域“A2：E22”，然后單擊【插入】/【名稱】/【定義】菜單項(xiàng)，打開【定義名稱】對話框。在“在當(dāng)前工作簿中的名稱”文本框中輸入“出勤表”.然后單擊“添加”按鈕即可完成數(shù)據(jù)名稱的添加。單擊“關(guān)閉”按鈕返回工作表中。注：定義名稱還有一種更簡潔的方法：在工作表中選中單元格區(qū)域“A2：M22”，在名稱框中輸入“出勤表”，即可定義該名稱。下面利用定義VLOOKUP函數(shù)輸入出勤數(shù)據(jù)，具體的操作步驟如下：切換到“員工工資表”中，選中單元格B3，單擊【插入】/【函數(shù)】菜單項(xiàng)打開“插入函數(shù)”對話框，然后在“或選擇類別”下拉列表中選擇“查找與引用”選項(xiàng)，在“選擇函數(shù)”列表框中選擇“VLOOKUP”選項(xiàng) 單擊“確定”按鈕打開“函數(shù)參數(shù)”對話框，然后在“Lookup_value”文本框中輸入“A3”，在“Table_array”文本框中輸入“出勤表”，在“Col_index_num”文本框中輸入“2”，在“Range_lookup”文本框中輸入“0”.單擊“確定”按鈕返回工作表中，此時(shí)在單元格B3中使會顯示計(jì)算結(jié)果。用同樣的方法為單元格C3插入公式“=VLOOKUP(A3，出勤表，4，0)”，便會得到入“所在部門”的值。用同樣的方法為單元格I3插入公式“=VLOOKUP(A3，出勤表，12，0)”，便會得到“請假扣款”的值。2輸入獎金數(shù)據(jù)按照前面的方法為“員工獎金表”中的單元格區(qū)域定義區(qū)域名稱“獎金表”。在“員工工資表”中選中單元格E3，在其中輸入公式“=VLOOKUP(A3，獎金表，5，0)”，便會得到“獎金”的值。3輸入福利數(shù)據(jù)按照前面的方法為“員工獎金表”中的單元格區(qū)域定義名稱“福利表”。在“員工工資表”中選中單元格F3，在其中輸入公式“=VLOOKUP(A3，福利表，4，0)”，然后按下“回車”鍵，即可在單元格F3中顯示“住房補(bǔ)助”的計(jì)算結(jié)果。選中單元格G3，在其中輸入公式“=VLOOKUP(A3，福利表，5，0)”，在單元格G3中便會得到“車費(fèi)補(bǔ)助”列的值。選中單元格H3，在其中輸入公式“=VLOOKUP(A3，福利表，6，0)”，在單元格H3中便會得到“保險(xiǎn)金”的值。選擇單元格區(qū)域“B3：I3”，然后利用自動填充功能將公式復(fù)制到其他的單元格區(qū)域即可得到結(jié)果 4計(jì)算應(yīng)發(fā)金額員工工資應(yīng)發(fā)金額的計(jì)算公式為：應(yīng)發(fā)金額=基本工資+獎金+住房補(bǔ)助+車費(fèi)補(bǔ)助-保險(xiǎn)金-請假扣款根據(jù)上述公式，在單元格J3中輸入公式“=D3+E3+F3+G3-H3-I3”即可計(jì)算出“應(yīng)發(fā)金額”的值。然后利用自動填充功能將該公式填充到該列的其他單元格中得到結(jié)果。5計(jì)算扣稅所得額在工資表中對個(gè)人所得稅的計(jì)算相對要復(fù)雜一些。不是所有的人都按照相同的稅率來納稅，也不是全部的收入都要納稅。“扣稅所得額”就是需要納稅的那一部分收入。不同的“扣稅所得額”有不同的稅率和速算扣除數(shù)。個(gè)人所得稅計(jì)算表扣稅所得額稅率速算扣除數(shù)（元）5005%0200010%25500015%1252000020%3754000025%13756000030%33758000035%6375=10000045%15375“扣稅所得額”為“應(yīng)發(fā)工資”減去1600元個(gè)稅起征點(diǎn)后超出的數(shù)額，如果這個(gè)值小于零（應(yīng)發(fā)工資少于1600元）就不必交納個(gè)人所得稅，若這個(gè)值大于零（應(yīng)發(fā)工資多于1600元）就要交納個(gè)人所得稅。根據(jù)上述內(nèi)容計(jì)算“扣稅所得額”的具體步驟如下：選中單元格K3，單擊【插入】/【函數(shù)】菜單項(xiàng)，打開“插入函數(shù)”對話框 單擊“確定”按鈕打開“函數(shù)參數(shù)”對話框，然后在“Logical_test”文本框中輸入“J31600”，在“Value_if_true”文本框中輸入“0”，在“Value_if_false”文本框中輸入“J3-1600”，如圖8-10所示。單擊“確定”按鈕返回工作表中，此時(shí)在單元格K3中便會顯示計(jì)算結(jié)果。利用自動填充功能將公式復(fù)制到該列的其他單元格中。6計(jì)算個(gè)人所得稅計(jì)算出了“扣稅所得額”，下面再來求“個(gè)人所得稅”，即需要交納的稅款。在單元格L3中輸入如下公式：=IF(K3=500,K3=2000,K3=5000,k320000),k3*20%-375)7計(jì)算實(shí)發(fā)金額“實(shí)發(fā)金額”就是“應(yīng)發(fā)金額”減去“個(gè)人所得稅”。只要在單元格M3中輸入“=J3-L3”，然后再對該列的其他單元格進(jìn)行公式的復(fù)制即可。DSUM函數(shù)的作用是返回?cái)?shù)據(jù)列表或者數(shù)據(jù)庫的列中滿足指定條件的數(shù)字的和。DSUM函數(shù)的語法結(jié)構(gòu)如下：DSUM(database,field,criteria)各個(gè)參數(shù)的含義如下：database是構(gòu)成列表或者數(shù)據(jù)庫的單元格區(qū)域。數(shù)據(jù)庫是包含一組相關(guān)數(shù)據(jù)的列表，其中包含相關(guān)信息的行為記錄，而包含數(shù)據(jù)的列為字段。列表的第一行包含著每一列的標(biāo)志項(xiàng)。field用于指定函數(shù)所使用的數(shù)據(jù)列。列表中的數(shù)據(jù)列必須在第一行具有標(biāo)志項(xiàng)。field可以是文本，即兩端帶引號的標(biāo)志項(xiàng)；此外field也可以是代表列表中數(shù)據(jù)列位置的數(shù)字，其中1表示第一列，2表示第二列，依此類推。criteria為一組包含給定條件的單元格區(qū)域?？梢詾閰?shù)criteria指定任意區(qū)域，只要它至少包含一個(gè)列標(biāo)志和列標(biāo)志下方用于設(shè)定條件的單元格。了解了函數(shù)的作用后，首先在工資表中的單元格區(qū)域“C25：D29”中輸入如下圖所示的數(shù)據(jù)，然后對該區(qū)域進(jìn)行簡單的格式化設(shè)置。利用函數(shù)DSUM計(jì)算工資額在各個(gè)部門中的匯總的具體步驟如下：選中單元格D26，單擊【插入】/【函數(shù)】菜單項(xiàng)，打開“插入函數(shù)”對話框，然后在“或選擇類別”下拉列表中選擇“數(shù)據(jù)庫”選項(xiàng)，在“選擇函數(shù)”列表柜中選擇“DSUM”選項(xiàng)，單擊“確定”按鈕打開“函數(shù)參數(shù)”對話框，然后在“Database”文本框中輸入“C2：M22”，在“Field”文本框中輸入“M2”，在“Criteria”文本框中輸入“C25：C26”，單擊“確定”按鈕返回工作表中，此時(shí)在單元格D26中便會顯示計(jì)算結(jié)果。在單元格D27中輸入如下公式：=SUM(DSUM(C2:M22,M2,C25:C27),-D26)然后按下“回車”鍵，在單元格D27中便會顯示計(jì)算結(jié)果。注：在上面的公式中，DSUM函數(shù)的設(shè)定條件為單元格區(qū)域“C25：27”，即計(jì)算了數(shù)據(jù)區(qū)域“C2：M22”中所有人事部和財(cái)務(wù)部員工的工資總和，因此要用SUM函數(shù)將人事部員工的工資總和減去后才能夠得到財(cái)務(wù)部員工的工資總和。同樣的道理，在單元格D28中輸入如下公式：=SUM(DSUM(C2:M22,M2,C25:C28),-D26-D27)然后按下“回車”鍵，在單元格D28中便會顯示“銷售部”計(jì)算結(jié)果。在單元格D29中輸入如下公式：=SUM(DSUM(C2:M22,M2,C25:C29),-D26-D27-D28)然后按下“回車”鍵，在單元格D29中便會顯示“業(yè)務(wù)部”的計(jì)算結(jié)果。制作工資條制作工資條1INDEX函數(shù)該函數(shù)返回的是表或者區(qū)域中的值或值的引用。INDEX函數(shù)有兩種形式：數(shù)組合引用。數(shù)組形式通常返回豎直或者數(shù)值數(shù)組，引用形式通常返回引用。當(dāng)函數(shù)INDEX的第一個(gè)參數(shù)為數(shù)組常數(shù)時(shí)則使用數(shù)組形式。數(shù)組形式的INDEX函數(shù)的語法結(jié)構(gòu)如下：INDEX(array,row_num,column_num)2ROW函數(shù)ROW函數(shù)的作用是返回引用的行號。該函數(shù)的語法如下：ROW(reference)3COLUMN函數(shù)COLUMN函數(shù)的作用是返回給定引用的列標(biāo)。該函數(shù)的語法結(jié)構(gòu)如下：COLUMN(reference)制作工資條的具體步驟如下：制作工資條的具體步驟如下：插入一個(gè)新的工作表并將其重命名為“工資條”，然后在“工資條”中的單元格Al中輸入如下公式：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!A$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()輸入完后按下“回車”鍵，在單元格A1中就會顯示計(jì)算結(jié)果。分別在單元格“B1：M1”單元格中輸入以下公式：B1：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!B$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()C1：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!C$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()D1：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!D$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()E1：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!E$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()F1：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!F$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()G1：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!G$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()H1：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!H$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()I1：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!I$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()J1：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!J$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()K1：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!K$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()L1：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!L$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()M1：=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資表!M$2,INDEX(員工工資表!$A:$M,(ROW()+4)/3+1,COLUMN()實(shí)例拓展實(shí)例拓展1建立基本表格建立基本表格單擊【插入】單擊【插入】/【工作表】菜單項(xiàng)在工作簿中插入工作表并將其【工作表】菜單項(xiàng)在工作簿中插入工作表并將其重命名為重命名為“員工工資詳情表員工工資詳情表”，然后根據(jù)，然后根據(jù)“員工工資表員工工資表”中的中的內(nèi)容建立表格并進(jìn)行格式化設(shè)置內(nèi)容建立表格并進(jìn)行格式化設(shè)置.2創(chuàng)建下拉列表框在這里需要對每一位員工的編號進(jìn)行輸入。如果是較大的企業(yè)，編號數(shù)據(jù)的輸入工作量將會非常大，為此可以使用窗體控件來創(chuàng)建下拉列表，選擇員工的編號。創(chuàng)建下拉列表的具體步驟如下：單擊【視圖】/【工具欄】/【窗體】菜單項(xiàng)，打開“窗體”工具欄.單擊“組合框”按鈕，然后用鼠標(biāo)在單元格C2的位置拖動出適當(dāng)大小的組合框。用鼠標(biāo)右鍵單擊添加的組合框，在彈出的快捷菜單中選擇【設(shè)置控件格式】菜單項(xiàng)，打開“設(shè)置控件格式”對話框。切換到“控制”選項(xiàng)卡中，然后在“數(shù)據(jù)源區(qū)域”文本框中輸入“員工工資表!$A$3：$A$22”，在“單元格鏈接”文本框中輸入“$A$2”，在“下拉顯示項(xiàng)數(shù)”文本框中輸入“6”，并選中“三維陰影”復(fù)選框。如圖8-20所示。單“確定”按鈕返回工作表中。單擊其他的任何一個(gè)單元格，然后再單擊添加的組合框，此時(shí)會彈出下拉列表框。在下拉列表中選擇員工編號“1008”，在單元格A2中則會顯示出鏈接值“8”.3設(shè)置其他項(xiàng)目利用【窗體】工具欄建立下拉列表后，下面使用函數(shù)INDEX對其他的項(xiàng)目設(shè)置自動輸入功能。具體步驟如下：選中單元格C3，單擊【插入】/【函數(shù)】菜單項(xiàng)，打開“插入函數(shù)”對話框，然后在“或選擇類別”下拉列表中選擇“查找與引用”選項(xiàng)，在“選擇函數(shù)”列表框中選擇“INDEX”選項(xiàng)。單擊“確定”按鈕打開“選定參數(shù)”對話框，然后在“參數(shù)”列表框中選擇“array，row_num，column_num”選項(xiàng)。單擊“確定”按鈕打開“函數(shù)參數(shù)”對話框，然后在“Array”文本框中輸入“員工工資表!A3：M22”，在“Row_num”文本框中輸入“A2”，在“Column_num”文本框中輸入“2”單擊“確定”接鈕返回工作表中，此時(shí)在單元格C3中則會顯示編號為1008員工的姓名。依次在單元格C4、C5、C6、C7、C8、C9、C10、E7、E8、E9、E10中輸入如下公式：=INDEX(員工工資表!A3:M22,A2,3)=INDEX(員工工資表!A3:M22,A2,4)=INDEX(員工工資表!A3:M22,A2,5)=INDEX(員工工資表!A3:M22,A2,6)=INDEX(員工工資表!A3:M22,A2,7)=INDEX(員工工資表!A3:M22,A2,8)=INDEX(員工工資表!A3:M22,A2,9)=INDEX(員工工資表!A3:M22,A2,10)=INDEX(員工工資表!A3:M22,A2,11)=INDEX(員工工資表!A3:M22,A2,12)=INDEX(員工工資表!A3:M22,A2,13)在單元格D11中輸入公式“=TODAY()”，然后按下“回車”鍵，此時(shí)單元格D11就會顯示系統(tǒng)的當(dāng)前時(shí)間。演講完畢，謝謝觀看！