利用excel函數(shù)功能實現(xiàn)快速工資條的制作

利用excel函數(shù)功能實現(xiàn)快速工資條的制作

excel函數(shù)具有強大的數(shù)據(jù)分析和數(shù)據(jù)處理功能。此外，使用excel函數(shù)可以高效地管理和提取合法密碼的個人數(shù)據(jù)。這對于辦公室員工來說是一個必要的技能。一、話導(dǎo)致的錯誤在企業(yè)中,如何準(zhǔn)確、快速地統(tǒng)計出每個月的員工工資是一項繁重的任務(wù),如果采用手動統(tǒng)計的話會造成大量的時間浪費,所統(tǒng)計出的數(shù)據(jù)往往也容易出錯。對于大型企業(yè)而言,他們一般會購買專業(yè)的財務(wù)軟件來進行工資統(tǒng)計等工作,對于一些中小企業(yè)而言,對員工工資進行統(tǒng)計、分析時,使用Excel函數(shù)不僅可以減少公司運營成本,同樣能有效地完成工作、提高工作效率。(一)設(shè)計工作表的設(shè)計在Excel中,把工資表中所需要的項目輸入工作表中。我們首先設(shè)計出一個簡單的工作表,在工作表中依次將編號、月份、姓名、所屬部門、基本工資、獎金、應(yīng)發(fā)工資、個人所得稅、實發(fā)工資等項目輸入表格中。(二)利用if函數(shù)進行計算補貼1.使用IF函數(shù)計算獎金。IF函數(shù)可以依據(jù)指定條件的邏輯判斷計算真假值,輸出不同的結(jié)果。語法格式為:IF(Logical_test,Value_if_true,Value_if_false)。輸入Logical_test的目的是表示邏輯判斷的表達式;輸入Value_if_true的目的為表示當(dāng)判斷條件為邏輯“真(true)”時返回的結(jié)果,輸入Value_if_false的目的為表示當(dāng)判斷條件為邏輯“假(false)”時返回的結(jié)果。另外,IF函數(shù)可以嵌套使用,使用時最多可嵌套七層。比如,我們簡單設(shè)定每個部門獎金的發(fā)放金額為:人事部每人200元,業(yè)務(wù)部每人300元,行政部每人500元,財務(wù)部每人600元,只需要在F3單元格中輸入公式:“=IF(D3=“人事部”200,IF(D3=“業(yè)務(wù)部”300,IF(D3=“行政部”500,600)”,然后將公式填充到F3:F8單元格區(qū)域,這樣每名員工需要發(fā)多少獎金就會自動生成。如果公司是按照工齡或者職稱等標(biāo)準(zhǔn)來計算獎金,也可以用這種方法來進行數(shù)據(jù)運算。2.使用SUM函數(shù)計算應(yīng)發(fā)工資。SUM函數(shù)的功能是計算參數(shù)數(shù)值的和。做法是:在G3單元格輸入公式“=SUM(E3,F3)”,可以獲得G3單元格“金城武”的應(yīng)發(fā)工資數(shù)額,然后填充公式至G4:G8單元格區(qū)域即可。3.假設(shè)應(yīng)發(fā)工資額不超過1000元的員工工資需要按照5%的稅率征收個人所得稅,應(yīng)發(fā)工資超過1000元時,超出的部分按照10%的稅率征收。做法是:在H3單元輸入公式“=IF(G3<=1000,G3*0.05,(G3-000)*0.1+1000*0.05)”,可以獲得H3單元格“金城武”的個人所得稅數(shù)額,然后填充公式至H4:H8單元格區(qū)域即可。4.在I3單元格輸入公式:“=SUM(G3,H3)”可計算出“金城武”的實發(fā)工資數(shù)額,然后填充公式至I4:I8單元格區(qū)域,即可計算出所有員工的實發(fā)工資數(shù)額,如“圖1”所示。(三)在“工資條”的添加上,主要有以下幾種根據(jù)工資表制作工資條是辦公室或財務(wù)部等部門的日常工作之一。工資條作為員工工資項目的清單,其數(shù)據(jù)來源于已經(jīng)制作完成后的員工工資表。一般工資條由三行單元格構(gòu)成,第一行是工資項目,第二行是對應(yīng)的工資數(shù)據(jù),第三行留出空行以便于打印后裁剪。制作工資條需要四個函數(shù)的結(jié)合,下面按順序說明具體做法:1.ROW函數(shù)。ROW函數(shù)代表返回所選擇的某一個單元格的行數(shù)。ROW函數(shù)的語法格式為=ROW(reference)如果省略reference,則默認返回ROW函數(shù)所在單元格的行數(shù)。2.COLUMN函數(shù)。COLUMN函數(shù)可以返回引用的列標(biāo)號,COLUMN函數(shù)的語法格式為=COLUMN(reference),Reference的意思是需要得到其列標(biāo)的單元格或單元格區(qū)域。如果省略Reference,則默認返回為COLUMN函數(shù)所在單元格的引用。3.INDEX函數(shù)。INDEX函數(shù)的功能是返回區(qū)域或者表格中的值或?qū)χ档囊?。連續(xù)區(qū)域中INDEX函數(shù)的公式格式是=INDEX(array,ROW_num,COLUMN_num),其中array的功能是表示我們要引用的區(qū)域,ROW_num的功能是表示要引用的行數(shù),COLUMN_num的功能是表示要引用的列數(shù),最終的結(jié)果就是引用出區(qū)域內(nèi)行列交叉處的內(nèi)容。4.MOD函數(shù)。MOD函數(shù)是一個求余函數(shù),其格式為:=MOD(number,divisor),即是兩個數(shù)值表達式做除法運算后的余數(shù)。其中number是被除數(shù),divisor則是除數(shù)。通過以上函數(shù)和IF函數(shù)的結(jié)合,就可以通過Excel制作出工資條。第一步,打開如“圖2”所示的工資數(shù)據(jù)的Excel文件,這個表格存放了工資的原始數(shù)據(jù),共有I列。將sheet1重命名為“工資表”,將sheet2重命名為“工資條”,下面我們在“工資條”的工作表中設(shè)計工資條樣式。第二步,在“工資條”的A1單元格中輸入公式:“=IF(MOD,ROW,3)=0”“IF(MOD,ROW,3)=1”工資表A$1,INDEX(工資表$A:$I,(ROW+4)/3,COLUMN)。這個函數(shù)公式使用兩層判斷式:如果行號/3=0時,也就是當(dāng)行號是3的倍數(shù)時,返回空值;否則就是下一層結(jié)果:當(dāng)行號/3=1,也就是當(dāng)行號是3的倍數(shù)加1時,返回《工資表》工作表的A$2數(shù)據(jù);否則就返回“工資表”工作表的$A:$I區(qū)域的第“(ROW+4/3)”行與第“COLUMN”列交叉的數(shù)據(jù)。第三步,確認后選擇A1單元格,拖動A1單元格右下角的“+”,將公式橫向填充至I1單元格,這樣工資條中項目行就全部出來了。選定A3:I3,拖動I1單元格右下角的“+”,將公式豎向填充至最后一行,到此工資條的制作就全部完成了。如“圖2”所示。這樣就可以方便地將每個員工的工資條進行裁剪和發(fā)放了。以上通過一個簡單的例子介紹了如何使用Excel函數(shù)制作企業(yè)工資表和工資條,除此之外,我們還可以使用郵件合并、VBA編程等方法來完成工資表和工資條的制作。根據(jù)現(xiàn)實情況進行合理選擇和修改,就可以更加自如地面對以后工作中出現(xiàn)的更復(fù)雜的工資項目和計算過程,滿足不同性質(zhì)企業(yè)的工資管理需要。二、身份證號碼的性別Excel函數(shù)除了可以為企業(yè)在工資管理中提供便捷的操作方法,也可以為學(xué)校在收集學(xué)生信息時提供幫助,例如根據(jù)學(xué)生的身份證號碼自動提取出生年月日、籍貫或自動判斷性別。以現(xiàn)在使用的第二代的18位身份證為例,身份證號碼的第1到第2位表示所在的省份(直轄市或自治區(qū)),第3到第4位表示所在的地級市(自治州或盟),第5到第6位表示所在的縣(縣級市、區(qū)),第7到第14位表示出生年月日,第15到第17位表示順序號,其中第17位如果是奇數(shù)則表示性別為男,偶數(shù)表示性別為女,第18位是一個校驗碼,是為了防止前十七位出現(xiàn)重復(fù)。舉一個簡單的例子,首先建立一個Excel表格,輸入姓名、身份證號碼的項目和數(shù)據(jù)。(一)生成性別列在“性別”項目列C2單元格中輸入函數(shù):=IF(MOD,MID(B2,17,1,2)=0,女,男),輸出結(jié)果為“女”,確定C2單元格然后將鼠標(biāo)指針放至C2右下角,出現(xiàn)“+”后往下拉,就會自動生成性別列所有結(jié)果:李四性別為女,王五性別為男。(二)提取全國身份證號碼表在用Excel自動生成戶籍所在地前,我們需要先建立一個包含身份證前六位號碼和對應(yīng)戶籍所在地的表格,這個信息可以直接從全國身份證號碼表中直接提取。接下來的操作方法為:在“籍貫”列D2單元格中輸入公式:=LOOKUP(LEFT(D2,6),戶籍所在地$A$1:$B$547),可得結(jié)果張三籍貫為“安徽省碭山縣”,按照上述自動生成的方法,下拉整列“籍貫”列,輸出結(jié)果為:李四籍貫為“北京市密云縣”、王五籍貫為“江蘇省雨花臺區(qū)”。(三)出生年月日根據(jù)學(xué)生的身份證號碼提取出生年月日比較簡單,具體操作方法為:在“出生年月日”列E2單元格輸入公式:=MID(B2,7,4)&—&MID(B2,11,2)&—&MID(B2,13,2),輸出結(jié)果為:張三的出生年月日是1992—2—28,李四出生年月日是1993—4—17,王五出生年月日是1993—05—06。完成后的表格如“圖3”所示。按照這種方法不僅可以自動生成學(xué)生性別、籍貫和出生