巧用Excel 提升工資管理數(shù)據(jù)處理能力

摘 要：Excel是Office辦公室軟件中非常重要的組成部分，它不僅能夠方便地處理表格和進(jìn)行圖形分析，其更強大的功能體現(xiàn)在對數(shù)據(jù)的處理上。通過工資管理實踐，總結(jié)數(shù)據(jù)處理中常用的幾個函數(shù)及其他工具，希望能夠幫助工資管理人員提高數(shù)據(jù)處理能力。

關(guān)鍵詞：工資管理；數(shù)據(jù)處理；函數(shù)；分類匯；數(shù)據(jù)透視表

中圖分類號：F244 文獻(xiàn)標(biāo)志碼：A 文章編號：1673-291X（2013）22-0055-02

讀過《杜拉拉升職記》的人一定記得，沈喬治為匯總SH中國兩千多人的加薪數(shù)據(jù)熬得一宿未睡，最終卻因為函數(shù)使用上的一個細(xì)節(jié)而功虧一簣，相信作為工資管理人員，你一定也有過如此慘痛的經(jīng)歷。那么如何做，才能使海量數(shù)據(jù)處理工作變得既輕松又有高效呢？學(xué)會巧用常用的辦公軟件——Excel表格，提升工資管理中數(shù)據(jù)處理能力。

一、充分利用函數(shù)功能

Excel表格里面有11類三百多個函數(shù)，種類非常豐富，功能非常強大。工資管理數(shù)據(jù)處理中常用的有以下幾個：

1.從已有數(shù)據(jù)中提取所需數(shù)據(jù)。（1）從中間提取。使用MID函數(shù)。假如從身份證號中提取出生日期，可以使用公式“=MID（C1，7，8）”，C1代表職工的身份證號，采用拖動或復(fù)制的方法即可提取全部職工的出生日期。（2）從兩端開始提取。從左端提取使用Left函數(shù)，右端提取使用Right函數(shù)。例如，在一個工作表中，某一列的資料是地址，錄有省、市、街道、電話號碼等。如果你想單獨獲得省份，可利用公式“=Left（A2，3）”，“A2”代表地址，“3”表示從第一位開始，共截取3個字符。如果你想單獨獲得電話號碼，可利用公式“=Right（A2，8）”，“A2”代表地址，“8”表示從最后一位開始，共截取8個字符。

2.將已有數(shù)據(jù)進(jìn)行連接或關(guān)聯(lián)。（1）數(shù)據(jù)連接。可以使用連接符“”，在表格中直接公式“=a2b2”，即可將a2和b2兩個單元格的數(shù)據(jù)連接在一起。（2）數(shù)據(jù)關(guān)聯(lián)。將兩張具有相同列或行數(shù)據(jù)的sheet表中的數(shù)據(jù)關(guān)聯(lián)在一張sheet表中，分以下兩種情況：1）如果兩張sheet表的行或列數(shù)據(jù)數(shù)量完全一樣，可以以這一行或列數(shù)據(jù)作為主要關(guān)鍵字進(jìn)行排序，排序后，復(fù)制粘貼即可。2）如果兩張sheet表的行或列數(shù)據(jù)數(shù)量不完全一樣，則要使用vlookup函數(shù)或hlookup函數(shù)。前者適用關(guān)聯(lián)列數(shù)據(jù)，后者適用關(guān)聯(lián)行數(shù)據(jù)。

舉例：sheet1表中有兩列：姓名、籍貫，sheet2表中有兩列：姓名，身份證號碼，sheet1表中的姓名和sheet2表中的姓名部分一致，假若想要使sheet1表的姓名同時具有身份證號，則可以在sheet1表中的新一列寫入公式“=vlookup（A2，Sheet2！$A$1：$B$50，2，0）”，使用填充柄填充其他單元格即可完成關(guān)聯(lián)操作。切記vlookup函數(shù)中第四個參數(shù)，精確匹配用“1”或者“0”，模糊匹配用“true” 或者“1”。

3.獲得“年齡”、“虛工齡”、“實工齡”等，以及補發(fā)工資要用到的天數(shù)、月數(shù)。

（1）“年齡”、“虛工齡”、“實工齡” 的獲得。1）“年齡”、“虛工齡”獲得可以利用year函數(shù)，計算公式是“= year（B1）-year（A1））”，公式中的A1和B1分別存放起止日期。若A1和B1分別代表出生日期，現(xiàn)在日期，則計算所得為“年齡”；若A1和B1分別代表參加工作日期，現(xiàn)在日期，則計算所得為“虛工齡”。2）“實工齡”計算要使用trunc和days360函數(shù)，days360函數(shù)主要是計算兩個日期之間間隔的天數(shù)，trunc是一個取整函數(shù)。計算公式是“= trunc（days360（B1，A1）/360，0）”，公式中的A1和B1分別代表參加工作日期，截止日期。

要計算參加工作到系統(tǒng)當(dāng)前時間的實工齡，可以將公式修改為“= trunc（days360（A1，now（））/360，0）。其中now（）函數(shù)返回當(dāng)前的系統(tǒng)日期和時間。

（2）補發(fā)工資經(jīng)常要用到的“天數(shù)”、“月數(shù)”。可以利用函數(shù)days360獲得“天數(shù)”、“月數(shù)”。“天數(shù)”計算公式是“= days360（B1，A1）”。“月數(shù)” 計算公式是“= trunc（days360（B1，A1）/30，0）”，公式中的A1和B1分別代表起薪日期，截止日期。如果截止日期現(xiàn)在無法確定，只需要把B1改為now（），無論什么時間打開，都會計算為截止當(dāng)前日期需要補發(fā)的“天數(shù)”、“月數(shù)”，數(shù)據(jù)自動更新，這一點非常方便。

這個公式對補發(fā)工資計算非常有用，當(dāng)你遇到需要計算中途退休、死亡、離職職工需要補發(fā)的月數(shù)、天數(shù)的時候，使用它非常方便。

4.新老工資標(biāo)準(zhǔn)之間的替換。假設(shè)進(jìn)行工資調(diào)整，工資管理人員將面臨相應(yīng)的增資測算。因為不同職級對應(yīng)不同的工資標(biāo)準(zhǔn)，若使用篩選方式一個個填入數(shù)據(jù)，職級數(shù)量少還可以，多的話，就費時費力，這里利用if或choose函數(shù)可以輕松實現(xiàn)不同職級新老工資標(biāo)準(zhǔn)的套入。

（1）如果職級少于7個（含7個），可使用if函數(shù)（在Excel 2003及以前的版本中，最多允許7層IF函數(shù)嵌套，在Excel 2007中允許使用64層IF函數(shù)嵌套）。

例如工作表的B列存放著職級，要在C列中放入工資標(biāo)準(zhǔn)，一至七級對應(yīng)的工資標(biāo)準(zhǔn)分別為5 500，5 000，4 750，

4 500，4 000，3 800，3 500。在C列的空白單元格輸入公式“=if（B2=\"一級\"，5 000，if（B2=\"二級\"，5 000，if（B2=\"三級\"，4 750，if（B2=\"四級\"，4 500，if（B2=\"五級\"，4 000，if（B2=\"六級\"，3 800，3 500））））））”，回車后，使用填充柄填充其他單元格即可得出各職級對應(yīng)的工資標(biāo)準(zhǔn)。

（2）如果職級多于7個，則要用lookup函數(shù)或choose函數(shù)。

使用lookup函數(shù)時，要先在兩個空白單元格區(qū)域分別放置職級和對應(yīng)的工資標(biāo)準(zhǔn)。職級需要用代碼表示，作升序排列。假設(shè)我們把職級代碼放到G2：G15單元格區(qū)域，對應(yīng)的工資標(biāo)準(zhǔn)放在H2：H15單元格區(qū)域。假設(shè)我們要查詢的職級代碼在A列，那么我們只需要在B2單元格輸入公式“=lookup（A2，$G$2：$G$15，$H$2：$H$15）”，回車后，使用填充柄填充其他單元格即可得出各職級對應(yīng)的工資標(biāo)準(zhǔn)。

使用choose函數(shù)時，職級需要用代碼表示，但不需作升序排列。假設(shè)職級所在列為C列，職級高低分別用1，2，3…數(shù)值表示，那么我們在另外一個新的列中寫入公式“=choose （C1：C1000，a1，b1，c1…）”（ a1，b1，c1，d1…分別代表1，2，3…所對應(yīng)職級對應(yīng)的工資標(biāo)準(zhǔn)），回車后，使用填充柄填充其他單元格即可得出各職級對應(yīng)的工資標(biāo)準(zhǔn)。

If函數(shù)是個功能非常強大的函數(shù)，除了以上功能，對于各種條件的分類計算，比如計算個人所得稅，計算年休假天數(shù)等等是非常好用的。

二、利用表格自帶的統(tǒng)計功能

Excel具有強大的數(shù)據(jù)庫功能，能對包含大量數(shù)據(jù)的表格進(jìn)行排序、篩選、分類匯總等處理。

1.利用自帶的分類匯總功能實現(xiàn)求和、計數(shù)、平均值。統(tǒng)計年報中一般要對各職級當(dāng)年的人數(shù)和年度發(fā)生的工資額進(jìn)行統(tǒng)計上報，使用Excel的分類匯總統(tǒng)計工作將變得非常簡單。

為了獲得各職級的人數(shù)和年度發(fā)生工資額，你應(yīng)該形成年度工資發(fā)放基礎(chǔ)數(shù)據(jù)表。基礎(chǔ)數(shù)據(jù)表應(yīng)該有以下列：姓名、職級、工資發(fā)放月份、基本工資、國家統(tǒng)一的津補貼、地方津補貼、改革性補貼等，并有對應(yīng)的數(shù)據(jù)。

統(tǒng)計各職級人數(shù)：首先點擊“數(shù)據(jù)”下的“分類匯總”，“分類字段”選“職級”，“匯總方式”選擇“計數(shù)”，“選擇匯總項”選“工資發(fā)放月份”。選中“匯總結(jié)果顯示在數(shù)據(jù)下放”，單擊“確定”按鈕后匯總結(jié)果會顯示在工作表數(shù)據(jù)的下面。將匯總數(shù)據(jù)選擇性粘貼到另一張工作表中，選擇性粘貼時要點擊“數(shù)值”，對“職級”字段進(jìn)行篩選，篩選時自定義篩選方式為等于“*計數(shù)”，即可得到各職級當(dāng)年總共有多少人次發(fā)放工資，全部選定，再次選擇性粘貼數(shù)值到另一張工作表中，將所得數(shù)據(jù)除以12，即可得到為其發(fā)放工資的各職級的年平均人數(shù)。

統(tǒng)計各職級年度發(fā)生的工資額方法如上，只是“匯總方式”選擇“求和”，“選擇匯總項”選“基本工資”、“國家統(tǒng)一的津補貼”、“地方津補貼”和“改革性補貼”。勿需除以12，即可得到各職級各個工資項目年度發(fā)生的額度。

2.利用數(shù)據(jù)透視表匯總數(shù)據(jù)。數(shù)據(jù)透視表是Excel數(shù)據(jù)庫功能的重要組成部分，它能夠非常靈活的匯總數(shù)據(jù)。如果你要對一個工資工作表進(jìn)行統(tǒng)計，計算職工的工總額，采用數(shù)據(jù)透視表也可以快速完成任務(wù)。

單擊“數(shù)據(jù)”菜單下的“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令打開向?qū)В紫冗x擇數(shù)據(jù)源類型和創(chuàng)建的報表類型，完成后單擊“下一步”按鈕。這時要選擇建立數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域，你只要用鼠標(biāo)在工作表中拖動即可將引用的數(shù)據(jù)區(qū)域放入對話框。接著選擇數(shù)據(jù)透視表的位置，可以選擇“新建工作表”或“現(xiàn)有工作表”。單擊“完成”按鈕，將“數(shù)據(jù)透視表字段列表”中的“工資合計”拖入工作表的指定位置，Excel會立刻計算出所有職工的工資合計數(shù)。

3、利用“宏”。“宏”的用途是使常用任務(wù)自動化。比如說你要實現(xiàn)某些運算（如相對復(fù)雜的減乘除運算），并經(jīng)常用到，而運算過程本身太過復(fù)雜，這時你就可以自定義一個函數(shù)，專門用來存放這個運算。這樣以后再次用到的時候就可以直接調(diào)用這個函數(shù)了，但這種情況需要你對VBA代碼有一定的熟悉程度。

Excel表格作為數(shù)字處理專用軟件，功能強大，只要你善于利用它，并不斷思索，一切紛繁的數(shù)據(jù)統(tǒng)計、計算工作都會變得簡單、易行。

參考文獻(xiàn)：

[1] 任廷琦，王世純.計算機文化基礎(chǔ)[M].濟(jì)南：山東大學(xué)出版社，2005.

[2] 汪洪祥.企業(yè)管理中Excel函數(shù)之應(yīng)用[J].中小企業(yè)管理與科技，2009，（6）.

[3] 柴方艷.Excel函數(shù)在工資管理中的應(yīng)用[J].農(nóng)業(yè)網(wǎng)絡(luò)信息，2007，（5）.

[責(zé)任編輯 陳麗敏]