干部年報(bào)統(tǒng)計(jì)自動化在Ｅｘｃｅｌ中的實(shí)現(xiàn)

［摘 要］ 本文在完全Excel環(huán)境下，利用代碼置換原理，通過信息代碼為人員信息庫自動賦值，再根據(jù)統(tǒng)計(jì)的需要將人員信息轉(zhuǎn)換成不同類別、不同層次的統(tǒng)計(jì)碼，最后通過Excel函數(shù)以數(shù)據(jù)連接的方式對各類、各層次統(tǒng)計(jì)碼進(jìn)行自動統(tǒng)計(jì)，從而實(shí)現(xiàn)對干部年報(bào)、工資年報(bào)等復(fù)雜報(bào)表的自動填報(bào)以及數(shù)據(jù)源和報(bào)表之間動態(tài)數(shù)據(jù)的同步更新。該設(shè)計(jì)既滿足了年度報(bào)表工作的需要，也為日常的人力資源信息管理提供了一種行之有效的方法。

［關(guān)鍵詞］ 干部年報(bào)；統(tǒng)計(jì)自動化；Excel；實(shí)現(xiàn)

［中圖分類號］F270.7［文獻(xiàn)標(biāo)識碼］A［文章編號］1673-0194（2008）09-0094-04

人事報(bào)表是為管理決策提供支持的有效的數(shù)據(jù)集合，報(bào)表工作在人力資源管理中占有重要位置。隨著人力資源管理信息化進(jìn)程的逐步加快，人事報(bào)表需求日漸增多，統(tǒng)計(jì)工作量也越來越大，而目前應(yīng)用的報(bào)表控件又存在價(jià)格昂貴、剛性強(qiáng)、靈活性差等問題。為此已有人將目光投向了Microsoft Office系統(tǒng)的Excel平臺上，或?qū)xcel作為Visual Studio.Net 2003/2005的動態(tài)數(shù)據(jù)輸出平臺設(shè)計(jì)報(bào)表模板；或在Excel背景下利用VBA編程語言實(shí)現(xiàn)賬務(wù)與報(bào)表等信息的自動生成等。上述方法均需要借助于編程語言來實(shí)現(xiàn)，對一般的人事工作者存在難度，筆者嘗試完全在Excel環(huán)境下，不借助任何編程語言，巧妙利用Excel函數(shù)、自定義公式來解決干部年報(bào)等復(fù)雜報(bào)表的自動統(tǒng)計(jì)問題，取得了較好的效果。

1 設(shè)計(jì)思路

以干部年報(bào)為例，Excel環(huán)境下干部年報(bào)統(tǒng)計(jì)自動化流程設(shè)計(jì)思路如圖1所示。上述流程都在一個(gè)Excel工作簿中進(jìn)行，包括信息代碼表、干部年報(bào)和人員信息庫3個(gè)Excel工作表。具體運(yùn)行流程是：通過信息代碼為人員信息庫自動賦值，再根據(jù)統(tǒng)計(jì)的需要將人員信息轉(zhuǎn)換成不同類別、不同層次的統(tǒng)計(jì)碼，最后在干部年報(bào)工作表中通過Excel函數(shù)以數(shù)據(jù)連接的方式對各類、各層次統(tǒng)計(jì)碼進(jìn)行自動統(tǒng)計(jì)，從而實(shí)現(xiàn)對干部年報(bào)的自動填報(bào)及其與人員信息庫之間動態(tài)數(shù)據(jù)的同步更新。

2 設(shè)計(jì)過程

2. 1統(tǒng)計(jì)指標(biāo)收集

在統(tǒng)計(jì)指標(biāo)的收集上，本著實(shí)用和最小冗余的原則。干部年報(bào)表式復(fù)雜，對人員信息在類別和層次上劃分詳細(xì)，所需信息量多。但在Excel平臺上，部分信息完全可憑借Excel強(qiáng)大、靈活的數(shù)據(jù)處理功能間接獲得，從而省去了對這部分信息的收集環(huán)節(jié)，減輕工作量。通過對干部年報(bào)所需信息的分析，將其分為兩類：一類是固有信息，主要有姓名、身份證號、性別、民族、政治面貌、學(xué)歷、專業(yè)資格、任職崗位、薪資標(biāo)準(zhǔn)和黨政職務(wù)等；另一類是派生信息，包括出生日期、年齡、身份和兼職4項(xiàng)指標(biāo)，稱其為派生信息，是因?yàn)檫@部分信息可通過Excel函數(shù)、預(yù)設(shè)的代碼等從固有信息中派生出來，不必手工錄入。

2. 2指標(biāo)代碼設(shè)定（見表1）

設(shè)定指標(biāo)代碼的目的是通過代碼向人員信息庫對應(yīng)的指標(biāo)賦值，將指標(biāo)數(shù)據(jù)自動轉(zhuǎn)換為代碼，為下一步的統(tǒng)計(jì)碼設(shè)定做準(zhǔn)備。可設(shè)定代碼的指標(biāo)應(yīng)具有固定的備選值，且備選值數(shù)量較多，這樣才能充分發(fā)揮代碼的作用。表1中，學(xué)歷和學(xué)位代碼按照學(xué)歷層次設(shè)定（列I），政治面貌代碼按照黨派類別設(shè)定（列L），年齡代碼按照區(qū)間段設(shè)定（列O）。考慮到2006年新工資制的施行、事業(yè)單位聘用制改革，以及實(shí)際工作中兼職情況的存在，崗位代碼在設(shè)置上較復(fù)雜一些：表中C列為身份代碼（專業(yè)人員為1，管理人員為2，工勤人員為3）；D列為崗位級別代碼，與新工資制相對應(yīng)；E列為崗位層次代碼，其中專業(yè)技術(shù)崗位從正高級到見習(xí)分為6個(gè)層次，管理崗位從正處級到辦事員分為6個(gè)層次（層次的多少與單位的行政級別有關(guān)），工勤崗位層次則從高級技師到學(xué)徒分為8個(gè)層次；F列為專業(yè)類別代碼，該類代碼與單位所屬行業(yè)、專業(yè)結(jié)構(gòu)設(shè)置有關(guān)（根據(jù)本單位實(shí)際情況，表1中專業(yè)人員僅設(shè)置了科研人員和會計(jì)人員兩類）。

2. 3人員信息庫建立

人員信息庫工作表的結(jié)構(gòu)設(shè)計(jì)見表2，表中部分顯示包含了干部年報(bào)統(tǒng)計(jì)所需的全部信息。

信息的輸入方式有3種：

① 姓名、身份證號兩項(xiàng)信息需手工錄入，其中身份證號可通過設(shè)定單元格的條件格式，對輸入的數(shù)據(jù)長度不符合要求的特殊顯示，以提醒用戶糾錯(cuò)；

② 性別、民族、政治面貌、學(xué)歷、專業(yè)資格、任職崗位、薪資標(biāo)準(zhǔn)、黨政職務(wù)等8項(xiàng)信息通過Excel的數(shù)據(jù)有效性功能創(chuàng)建下拉列表，列表中備選值與代碼表中一致，數(shù)據(jù)錄入時(shí)從備選值中提取，這樣既保證了數(shù)據(jù)輸入的準(zhǔn)確性，又為其后的代碼賦值提供了保證；

③ 出生日期、年齡代碼等派生信息利用Excel函數(shù)通過表內(nèi)和表間數(shù)據(jù)連接的方法自動獲得，第② ③輸入方式的計(jì)算公式舉例如下：

E4(出生日期) = if(len($B4)=15，19*power(10，6)+mid($B4，7，6)，mid($B4，7，8))*0.0001

F4(年齡) = int((year(today())+mouth(today())*0.01+day(today())*0.0001)-$E4)

G4(年齡代碼) = if($A4=\"\"，0，vlookup($A4，代碼表!$N$3:$O$55，2))

P4(任職崗位代碼) = if($A4=\"\"，0，vlookup($A4，代碼表!$B$3:$F$68，5，1))

L4(身份代碼) = VLOOKUP($P4，代碼表!$B$3:$F$68，2，F(xiàn)ALSE)

M4(兼職) = VLOOKUP($R4，代碼表!$B$3:$F$68，2，F(xiàn)ALSE)

2. 4干部年報(bào)表式設(shè)計(jì)和統(tǒng)計(jì)碼設(shè)定

2. 4. 1干部年報(bào)表式設(shè)計(jì)

統(tǒng)計(jì)表的格式設(shè)計(jì)基本參照干部年報(bào)的表式，也可根據(jù)日常工作的需要對統(tǒng)計(jì)指標(biāo)的類別和層次做更詳細(xì)的劃分，如學(xué)歷和年齡，見表3（部分顯示）。

表3中，列標(biāo)題按人員類別即專業(yè)技術(shù)人員、管理人員和工勤人員劃分，其中專業(yè)人員中又分為科研人員和會計(jì)人員兩類。行標(biāo)題按人員的性別、民族、政治面貌、學(xué)歷、年齡等自然情況劃分。表中列C和行3中的數(shù)字分別是列標(biāo)題和行標(biāo)題中個(gè)指標(biāo)項(xiàng)的代碼值，該代碼是該表表式設(shè)計(jì)的關(guān)鍵，因?yàn)樗窍虏浇y(tǒng)計(jì)公式的數(shù)據(jù)源之一。其中，行3中政治面貌、學(xué)歷代碼等各指標(biāo)項(xiàng)代碼與代碼表中對應(yīng)的指標(biāo)代碼一致（表1中L列、I列）；列C中的代碼與崗位設(shè)置代碼中的專業(yè)代碼一致（表1中F列）。

2. 4. 2統(tǒng)計(jì)碼設(shè)定

統(tǒng)計(jì)碼位于人員信息工作表中，與人員一一對應(yīng)，分為3部分（見表4）：① 依據(jù)任職崗位設(shè)定的統(tǒng)計(jì)碼（AA列至AE列），作為干部年報(bào)中管理人員情況統(tǒng)計(jì)的數(shù)據(jù)源；② 依據(jù)薪資標(biāo)準(zhǔn)設(shè)定的統(tǒng)計(jì)碼（AF列至AJ列），作為干部年報(bào)中專業(yè)技術(shù)人員情況統(tǒng)計(jì)的數(shù)據(jù)源；③ 依據(jù)人員身份設(shè)定的統(tǒng)計(jì)碼（AK列至AO列），作為人員按干部、工人分類統(tǒng)計(jì)時(shí)的數(shù)據(jù)源。

統(tǒng)計(jì)碼的設(shè)定方法是：通過表內(nèi)鏈接將相關(guān)信息的代碼值組合為有序的可計(jì)算的數(shù)值，如表4中單元格AI4的值10103意為：職工某1的專業(yè)技術(shù)職務(wù)為研究員（1）、執(zhí)行薪資標(biāo)準(zhǔn)為研究員4（1）、學(xué)歷為碩士研究生（3）。具體設(shè)定公式如下（以第②類即專業(yè)技術(shù)人員統(tǒng)計(jì)碼為例）：

AF4(性別) = if($A4=\"\"，0，$M4*1000+$S4*10+IF($C4=\"男\(zhòng)"，1，2))

AG4(民族) = if($A4=\"\"，0，$M4*1000+$S4*10+IF($D4=\"漢\"，1，2))

AH4(政治面貌) = $M4*1000+$S4*10+$I4

AI4(學(xué)歷) = $M4*10000+$S4*100+$K4

AJ4(年齡) = $M4*10000+$S4*100+$G4

上述公式中，M為兼職代碼，S為薪資標(biāo)準(zhǔn)代碼，I為政治面貌代碼，K為學(xué)歷代碼，G為年齡代碼（見表2）。

2. 5干部年報(bào)數(shù)據(jù)的自動生成

年報(bào)（表3）數(shù)據(jù)的自動生成借助于表中行3和列C的代碼、表4中的統(tǒng)計(jì)碼以及表內(nèi)、表間的鏈接公式來實(shí)現(xiàn)。例如要統(tǒng)計(jì)本單位專業(yè)技術(shù)職務(wù)為研究員、薪資標(biāo)準(zhǔn)為研究員4、學(xué)歷為碩士研究生的人員數(shù)量，只需計(jì)算表4中AI列中值為“10103”的單元格數(shù)量即可。具體的統(tǒng)計(jì)公式如下（以表3中“科研人員”為例）：

按性別分組統(tǒng)計(jì)： E16 = Countif(人員信息庫!$AF:$AF，1000+$C16*10+E$3)

按民族分組統(tǒng)計(jì)： G16 = Countif(人員信息庫!$AG:$AG，1000+$C16*10+G$3)

按政治面貌分組統(tǒng)計(jì)： H16 = Countif(人員信息庫!$AI:$AI，1000+$C16*10+H$3)

按學(xué)歷分組統(tǒng)計(jì)： L16 = Countif(人員信息庫!$AJ:$AJ，10000+$C16*100+L$3)

上述公式中，Countif(range，criteria)函數(shù)的條件值即criteria值的組合順序正與人員信息庫中統(tǒng)計(jì)碼的組合順序相對應(yīng)。因?qū)Ρ?中行3和列C中代碼的單元格引用均為混合引用，所以只需將不同分組的統(tǒng)計(jì)公式設(shè)置在科研人員該分組的左上角單元格（研究員），再通過“自動填充柄”分別向右、向下填充至科研人員在該分組的末尾單元格即可，即同類人員的各統(tǒng)計(jì)分組只需一個(gè)統(tǒng)一的鏈接公式（僅“小計(jì)”除外）。在信息代碼表中，科研、管理和工勤人員的身份代碼分別是1、2和3，所以要實(shí)現(xiàn)對管理人員和工勤人員的情況統(tǒng)計(jì)，只需將上述公式中的1 000和10 000分別置換為2 000、

20 000和3 000、30 000即可。

因?qū)Ω刹磕陥?bào)中人員信息的統(tǒng)計(jì)劃分較詳盡，且統(tǒng)計(jì)數(shù)據(jù)始終處于動態(tài)更新中，所以該方案不僅能夠滿足年度報(bào)表的需求，也能滿足日常人事報(bào)表對統(tǒng)計(jì)數(shù)據(jù)的需求。

3 討 論

本設(shè)計(jì)的核心是信息的代碼置換，依照上述原理和方法，建立人員工資信息庫并以其為數(shù)據(jù)源，通過對代碼、報(bào)表表式和鏈接公式的合理設(shè)計(jì)，同樣可實(shí)現(xiàn)對工資年報(bào)、離退休年報(bào)等報(bào)表的自動統(tǒng)計(jì)，如此可大大地節(jié)省相關(guān)報(bào)表的勞動量，提高工作效率，將人事工作者從繁雜的數(shù)據(jù)統(tǒng)計(jì)中解放出來。該設(shè)計(jì)的不足之處在于：統(tǒng)計(jì)結(jié)果僅停留在Excel平臺上，只能做紙質(zhì)輸出，還不能實(shí)現(xiàn)基層單位與上級部門之間的數(shù)據(jù)交換，這有待在以后的管理實(shí)踐中繼續(xù)探索。

主要參考文獻(xiàn)

［1］ 朱順泉. 基于Excel 2003環(huán)境下的賬務(wù)與報(bào)表會計(jì)信息系統(tǒng)的設(shè)計(jì)方案［J］. 中國管理信息化，2007，（4）：3-5.

［2］ 楊建宏，王曉東，張琴琴. 基于Excel通用報(bào)表系統(tǒng)的設(shè)計(jì)與實(shí)現(xiàn)［J］. 電腦知識與技術(shù)，2007，（1）.

［3］ 龐敏. 用Excel生成報(bào)表的通用組件模型［J］. 交通與計(jì)算機(jī)，2005，（6）：112-114.

［4］ 周健，孟昭峰. 利用Excel進(jìn)行自動分析計(jì)算的方法［J］. 中國管理信息化，2007，（4）：43-45.