利用excel函數功能實現快速工資條的制作

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