張娟音
摘要:人力資源統(tǒng)計(jì)工作大多可以從人力資源管理系統(tǒng)直接導(dǎo)出統(tǒng)計(jì)結(jié)果,但對(duì)于跨模塊或跨系統(tǒng)或者系統(tǒng)都不具備統(tǒng)計(jì)所需信息時(shí),如果人工來完成,不僅大量耗費(fèi)工時(shí),且效率、質(zhì)量、效果都不敢保證。對(duì)于這類復(fù)雜統(tǒng)計(jì)可以利用Excel的強(qiáng)大功能來幫助實(shí)現(xiàn),下面舉例說明如何利用Excel來幫助實(shí)現(xiàn)人力資源復(fù)雜又機(jī)械的統(tǒng)計(jì)要求。
關(guān)鍵詞:Excel VBA語言 統(tǒng)計(jì)
對(duì)于企業(yè)來說,人力資源管理是非常重要的工作,而在人力資源管理中,最通常的應(yīng)用是基于Excel進(jìn)行數(shù)據(jù)統(tǒng)計(jì),通過excel可以方便快捷的處理數(shù)據(jù)的收集、整合、呈報(bào)和傳輸,也可以保證人力資源管理工作的備案和追溯,從而促進(jìn)人力資源管理工作的效率提升。
本文從一個(gè)較簡單的案例入手,淺淡excel如何統(tǒng)計(jì)人力資源信息。
例子:將企業(yè)人員信息按照表1的格式上報(bào),每人一表;并將人員基本情況匯成表2及生成表3人員統(tǒng)計(jì)表,表1電子版以“姓名+身份證”命名上報(bào),表格的格式和內(nèi)容見附錄。
一、觀察各表信息情況
表1所需的信息有:姓名、性別、身份證、職工證號(hào)、畢業(yè)學(xué)校、所學(xué)專業(yè)、職稱/技能證、發(fā)證機(jī)構(gòu)、所屬信息局、工作簡歷、照片等。
表2的信息包含了表1的信息,但非全部信息。其中身份證、畢業(yè)學(xué)校、所學(xué)專業(yè)未包含。
表3是對(duì)表2的統(tǒng)計(jì),包含了表2部分信息。
二、分析各表信息的獲取途徑
(一)途徑一:從表1匯總成表2再生成表3 。
表1中除“所屬信息局”外的其他信息都可以從人力資源管理系統(tǒng)中獲得,而“所屬信息局”的信息無法從人力資源管理系統(tǒng)中直接獲取,需要從人員的能力裝備庫中去獲得。如果管理系統(tǒng)能夠以“姓名+身份證”為文件名生成所需表1,但對(duì)于成千上萬人的企業(yè),就得打開成千上萬的Excel去添加“所需信息局”信息,這方法不可取。
(二)途徑二:從表2生成表1和表3
如果將表1、表2、表3放在統(tǒng)一Excel文件,先制作好表2,再生成表1和表3。必須解決的問題:一是表2只包含表1的部分信息而非全部信息,如何獲表1全部信息問題;二是表2的“所屬信息局”信息是人力資源管理系統(tǒng)不具體的信息,該信息如何獲?。咳钦掌坎迦雴栴};四是生成以“姓名+身份證”命名的文件問題。
(三)途徑二的可行性分析
問題一:表2不含表1的全部信息,但表1的大多信息是人力資源管理系統(tǒng)具備的,可以通過在表2增加輔助列,待生成表1之后再把輔助列刪除來解決。
問題二:對(duì)于表2的“所屬信息局”信息是管理系統(tǒng)沒有的,但又可以從人員能力裝備庫中取獲取,也就是解決將信息從一個(gè)Excel引入到另一個(gè)Excel中的問題,這種引用是可行。
問題三、問題四是關(guān)聯(lián)的。因?yàn)镋xcel有將各個(gè)Sheets生成獨(dú)立文件的功能,那么我們只需將表2的人員生成以“姓名+身份證”命名的sheets就有途徑生成獨(dú)立的Excel文件,而從總表生成各個(gè)Sheets且統(tǒng)一命名sheets名是有辦法的;而Excel也有批量插入各個(gè)sheets照片的功能,至此采用途徑二是可行的。
三、實(shí)現(xiàn)過程分解
(一)從人力資源管理系統(tǒng)導(dǎo)出信息項(xiàng)
從人力資源管理系統(tǒng)導(dǎo)出表2+輔助列的信息項(xiàng),如表4,格式和內(nèi)容見附錄。
表4中(13)列至(17)列是輔助列,在生成表1后刪除形成表2。因?yàn)槲募且浴靶彰?身份證”命名,所以表4輔助列(17)是為文件命名用的。
(二)從人員能力裝備庫中提取信息局信息
人員能力裝備庫中是姓名+裝備信息,而裝備信息又與信息局對(duì)應(yīng),則可以通過裝備信息作為引用項(xiàng),使用Vlookup()公式將信息局的信息引入到人員能力裝備庫,形成“姓名+裝備信息+信息局”信息,也就實(shí)現(xiàn)了人員與信息局的對(duì)應(yīng)。將信息局通過“姓名”這個(gè)引用項(xiàng),引入到表4中,這里要注意的是同姓名的情況,Vlookup()的引用只引用第一個(gè)出現(xiàn)的姓名的信息,若出現(xiàn)同名的情況會(huì)名引用源文件第一出現(xiàn)姓名的信息,需人工加以修正。
自此生成表1、表2、表3所需的信息都齊全,接下來四步就可以完成,第一步生成多個(gè)Sheets,第二步以“姓名+身份證”統(tǒng)一命名Sheets名,第三步插入照片,第四步生成以“姓名+身份證”命名的獨(dú)立文件。
(四)生成多個(gè)Sheets
將表1的打印格式設(shè)置好并設(shè)置好打印區(qū)域,然后在打印區(qū)域外,增加“序號(hào)”(G1單元格)和“姓名+身份證”(H1單元格)兩項(xiàng)信息,通過“序號(hào)”這個(gè)引用項(xiàng)將表4的信息引入到表1,包括“姓名+身份證”信息。這樣“序號(hào)”輸入幾,表2“序號(hào)”所對(duì)應(yīng)的信息就會(huì)引入到表1中。然后通過“移動(dòng)或者復(fù)制工作表”的功能把表1復(fù)制,將復(fù)制生成的表格的“序號(hào)”通過公式設(shè)置成前一表格的“序號(hào)”+1,這樣復(fù)制的生成的表就是序號(hào)首尾相連的表格(如20個(gè)),再將這20個(gè)首尾相連的表格選中復(fù)制,則又是另20個(gè)首尾相連的表格,將第21個(gè)表1的序號(hào)設(shè)置成第20個(gè)表1的“序號(hào)”+1,則生成了40個(gè)“序號(hào)”首尾相連的表1,這樣反復(fù)操作,將以2n的速度生成表1,就很快可以把表2的所有人員生成Sheets。
(五)統(tǒng)一命名Sheets名
使用VBA語言,對(duì)Sheets進(jìn)行統(tǒng)一命名,VBA語言(代號(hào)A)如下:
Sub 統(tǒng)一命名()
For?Each?Sheet?In?Sheets
If?Trim(Sheet.Cells(1,?8).Text?)<>?""
Then
Sheet.Name?=?Trim(Sheet.Cells(1,?8).Text?)
End?If
Next
End?Sub
注意:因Sheets名不能為空,所以必須保證各表H1內(nèi)容非空。
因?yàn)镠1單元格的值是“姓名+身份證”,通過上述VBA語言的運(yùn)行,各Sheets的名就變成了“姓名+身份證”的形式。
(六)各表插入照片
插入照片的VBA語言(代號(hào)B)如圖1 。
代號(hào)B的VBA語言是指以C3的左邊和頂端為標(biāo)準(zhǔn)插入照片,照片以B3單元格命名的jpg照片。B3單元格內(nèi)容為姓名,所以照片是姓名命名的jpg照片。照片的存儲(chǔ)路徑與Excel文件所在位置一致。因表3、表4的B3單元格不一定就是姓名,所以必須有以表3、表4的B3單元格內(nèi)容命名的jpg照片,不然會(huì)顯示圖2的錯(cuò)誤。
所有照片插入后,刪除表3、表4的照片。
(七)生成以“姓名+身份證”命名的獨(dú)立文件
從上一步可以看出,各表的信息已完整齊全,具備生成以“姓名+身份證”命名的獨(dú)立文件的條件,但各表內(nèi)容是有公式引用的,需變成無公式無鏈接文本。首先,全選所有表格,按住Ctrl反選表3和表4 ;然后選擇其中一個(gè)表1,選擇整表內(nèi)容,復(fù)制,選擇性粘貼成無公式文本;第三,統(tǒng)一刪除各個(gè)表1的G1和H1的輔助列,使用VBA生成以“姓名+身份證”命名的獨(dú)立文件。
VBA語言(代號(hào)C)如下:
Private Sub 分拆工作表()
Dim sht As Worksheet
Dim MyBook As Workbook
Set MyBook = ActiveWorkbook
For Each sht In MyBook.Sheets
sht.Copy
ActiveWorkbook.SaveAs Filename:=MyBook.Path & "\" & sht.Name, FileFormat:=xlNormal '將工作簿另存為EXCEL默認(rèn)格式
ActiveWorkbook.Close
Next
MsgBox "文件已經(jīng)被分拆完畢!"
End Sub
最后刪除表4(13)-(17)輔助列形成表2。
(八)統(tǒng)計(jì)生成表3
用counta()公式從表2統(tǒng)計(jì)形成表3的總?cè)藬?shù),用countif()公式從表2統(tǒng)計(jì)形成表3信息局?jǐn)?shù)量。
至此,統(tǒng)計(jì)任務(wù)完成。
四、本次任務(wù)的關(guān)鍵點(diǎn)
本此任務(wù)有四個(gè)關(guān)鍵點(diǎn)。一是把所需的信息在表4收集齊;二是把表1模板設(shè)置好,因它是N多個(gè)表的模板,事先設(shè)置好避免重復(fù)的操作動(dòng)作;三是插入照片之前做好備份工作,因插入照片之前幾乎是單個(gè)表操作,而插入照片后就是多個(gè)表操作,為保證萬無一失,插入照片前做好備份,若后面的操作中有不合心意的情況,有補(bǔ)救辦法;四是確保照片齊全??梢酝ㄟ^工具來確認(rèn)照片是否齊全。首先,在照片所在的文件夾,新建文本文檔;其次,打開創(chuàng)建的txt文件,輸入:dir *.jpg>jpg.txt之后保存;再次,進(jìn)入文件夾選項(xiàng)選擇隱藏已知文件類型的擴(kuò)展名,去掉勾選;第四,將創(chuàng)建的txt文件后綴名修改為bat,雙擊運(yùn)行bat文件,就自動(dòng)生成文件夾及子目錄下的所有文件清單了。第五,從清單中提取姓名,用Vlookup()公式與表4的人員姓名作匹配,就可以查出哪些人員照片缺失,補(bǔ)齊照片即可。
當(dāng)遇到復(fù)雜的統(tǒng)計(jì)任務(wù),一是先觀察、再分析、尋找最簡便可行的解決辦法,盡量把一些細(xì)節(jié)想周全,避免尾部工作的繁瑣。二是當(dāng)任務(wù)完成,對(duì)此次工作過程進(jìn)行復(fù)盤,將所用的工具方法和關(guān)鍵點(diǎn)進(jìn)行歸納總結(jié),匯總進(jìn)入Excel工具集,以備它用或者再次啟動(dòng)此類工作可以輕松應(yīng)對(duì)。三是實(shí)施的過程中遇到困難時(shí),要不恥下問,百度也好,咨詢Excel專家也好,尋求解決辦法,下次就會(huì)有提高。這樣經(jīng)過幾次這種大型的統(tǒng)計(jì),經(jīng)驗(yàn)將會(huì)大大增加,遇到更大的問題都容易解決。
結(jié)束語:Excel具備強(qiáng)大的功能,對(duì)一些復(fù)雜繁瑣機(jī)械的任務(wù)有妙招,可以適當(dāng)考慮。
參考文獻(xiàn)
【1】作者:揣素榮,《淺議統(tǒng)計(jì)在人力資源管理工作中的應(yīng)用》,2017
【2】作者:陳長偉,《Excel在人力資源管理中的應(yīng)用》,2013
【3】作者:林金燦,《Excel在統(tǒng)計(jì)中的應(yīng)用技巧》,2003
作者:樊娟玲,《信息系統(tǒng)在人力資源社會(huì)保障管理中的應(yīng)用》,2015
廣州市長島光電機(jī)械廠