淺談如何利用Excel為人力資源統計工作效力

張娟音

摘要：人力資源統計工作大多可以從人力資源管理系統直接導出統計結果，但對于跨模塊或跨系統或者系統都不具備統計所需信息時，如果人工來完成，不僅大量耗費工時，且效率、質量、效果都不敢保證。對于這類復雜統計可以利用Excel的強大功能來幫助實現，下面舉例說明如何利用Excel來幫助實現人力資源復雜又機械的統計要求。

關鍵詞：Excel VBA語言 統計

對于企業來說，人力資源管理是非常重要的工作，而在人力資源管理中，最通常的應用是基于Excel進行數據統計，通過excel可以方便快捷的處理數據的收集、整合、呈報和傳輸，也可以保證人力資源管理工作的備案和追溯，從而促進人力資源管理工作的效率提升。

本文從一個較簡單的案例入手，淺淡excel如何統計人力資源信息。

例子：將企業人員信息按照表1的格式上報，每人一表;并將人員基本情況匯成表2及生成表3人員統計表，表1電子版以“姓名+身份證”命名上報，表格的格式和內容見附錄。

一、觀察各表信息情況

表1所需的信息有：姓名、性別、身份證、職工證號、畢業學校、所學專業、職稱/技能證、發證機構、所屬信息局、工作簡歷、照片等。

表2的信息包含了表1的信息，但非全部信息。其中身份證、畢業學校、所學專業未包含。

表3是對表2的統計，包含了表2部分信息。

二、分析各表信息的獲取途徑

（一）途徑一：從表1匯總成表2再生成表3 。

表1中除“所屬信息局”外的其他信息都可以從人力資源管理系統中獲得，而“所屬信息局”的信息無法從人力資源管理系統中直接獲取，需要從人員的能力裝備庫中去獲得。如果管理系統能夠以“姓名+身份證”為文件名生成所需表1，但對于成千上萬人的企業，就得打開成千上萬的Excel去添加“所需信息局”信息，這方法不可取。

（二）途徑二：從表2生成表1和表3

如果將表1、表2、表3放在統一Excel文件，先制作好表2，再生成表1和表3。必須解決的問題：一是表2只包含表1的部分信息而非全部信息，如何獲表1全部信息問題;二是表2的“所屬信息局”信息是人力資源管理系統不具體的信息，該信息如何獲取？三是照片批量插入問題;四是生成以“姓名+身份證”命名的文件問題。

（三）途徑二的可行性分析

問題一：表2不含表1的全部信息，但表1的大多信息是人力資源管理系統具備的，可以通過在表2增加輔助列，待生成表1之后再把輔助列刪除來解決。

問題二：對于表2的“所屬信息局”信息是管理系統沒有的，但又可以從人員能力裝備庫中取獲取，也就是解決將信息從一個Excel引入到另一個Excel中的問題，這種引用是可行。

問題三、問題四是關聯的。因為Excel有將各個Sheets生成獨立文件的功能，那么我們只需將表2的人員生成以“姓名+身份證”命名的sheets就有途徑生成獨立的Excel文件，而從總表生成各個Sheets且統一命名sheets名是有辦法的;而Excel也有批量插入各個sheets照片的功能，至此采用途徑二是可行的。

三、實現過程分解

（一）從人力資源管理系統導出信息項

從人力資源管理系統導出表2+輔助列的信息項，如表4，格式和內容見附錄。

表4中（13）列至（17）列是輔助列，在生成表1后刪除形成表2。因為文件名是以“姓名+身份證”命名，所以表4輔助列（17）是為文件命名用的。

（二）從人員能力裝備庫中提取信息局信息

人員能力裝備庫中是姓名+裝備信息，而裝備信息又與信息局對應，則可以通過裝備信息作為引用項，使用Vlookup（）公式將信息局的信息引入到人員能力裝備庫，形成“姓名+裝備信息+信息局”信息，也就實現了人員與信息局的對應。將信息局通過“姓名”這個引用項，引入到表4中，這里要注意的是同姓名的情況，Vlookup（）的引用只引用第一個出現的姓名的信息，若出現同名的情況會名引用源文件第一出現姓名的信息，需人工加以修正。

自此生成表1、表2、表3所需的信息都齊全，接下來四步就可以完成，第一步生成多個Sheets，第二步以“姓名+身份證”統一命名Sheets名，第三步插入照片，第四步生成以“姓名+身份證”命名的獨立文件。

（四）生成多個Sheets

將表1的打印格式設置好并設置好打印區域，然后在打印區域外，增加“序號”（G1單元格）和“姓名+身份證”（H1單元格）兩項信息，通過“序號”這個引用項將表4的信息引入到表1，包括“姓名+身份證”信息。這樣“序號”輸入幾，表2“序號”所對應的信息就會引入到表1中。然后通過“移動或者復制工作表”的功能把表1復制，將復制生成的表格的“序號”通過公式設置成前一表格的“序號”+1，這樣復制的生成的表就是序號首尾相連的表格（如20個），再將這20個首尾相連的表格選中復制，則又是另20個首尾相連的表格，將第21個表1的序號設置成第20個表1的“序號”+1，則生成了40個“序號”首尾相連的表1，這樣反復操作，將以2n的速度生成表1，就很快可以把表2的所有人員生成Sheets。

（五）統一命名Sheets名

使用VBA語言，對Sheets進行統一命名，VBA語言（代號A）如下：

Sub 統一命名（）

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內容非空。

因為H1單元格的值是“姓名+身份證”，通過上述VBA語言的運行，各Sheets的名就變成了“姓名+身份證”的形式。

（六）各表插入照片

插入照片的VBA語言（代號B）如圖1 。

代號B的VBA語言是指以C3的左邊和頂端為標準插入照片，照片以B3單元格命名的jpg照片。B3單元格內容為姓名，所以照片是姓名命名的jpg照片。照片的存儲路徑與Excel文件所在位置一致。因表3、表4的B3單元格不一定就是姓名，所以必須有以表3、表4的B3單元格內容命名的jpg照片，不然會顯示圖2的錯誤。

所有照片插入后，刪除表3、表4的照片。

（七）生成以“姓名+身份證”命名的獨立文件

從上一步可以看出，各表的信息已完整齊全，具備生成以“姓名+身份證”命名的獨立文件的條件，但各表內容是有公式引用的，需變成無公式無鏈接文本。首先，全選所有表格，按住Ctrl反選表3和表4 ;然后選擇其中一個表1，選擇整表內容，復制，選擇性粘貼成無公式文本;第三，統一刪除各個表1的G1和H1的輔助列，使用VBA生成以“姓名+身份證”命名的獨立文件。

VBA語言（代號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默認格式

ActiveWorkbook.Close

Next

MsgBox "文件已經被分拆完畢！"

End Sub

最后刪除表4（13）-（17）輔助列形成表2。

（八）統計生成表3

用counta（）公式從表2統計形成表3的總人數，用countif（）公式從表2統計形成表3信息局數量。

至此，統計任務完成。

四、本次任務的關鍵點

本此任務有四個關鍵點。一是把所需的信息在表4收集齊;二是把表1模板設置好，因它是N多個表的模板，事先設置好避免重復的操作動作;三是插入照片之前做好備份工作，因插入照片之前幾乎是單個表操作，而插入照片后就是多個表操作，為保證萬無一失，插入照片前做好備份，若后面的操作中有不合心意的情況，有補救辦法;四是確保照片齊全。可以通過工具來確認照片是否齊全。首先，在照片所在的文件夾，新建文本文檔;其次，打開創建的txt文件，輸入：dir *.jpg>jpg.txt之后保存;再次，進入文件夾選項選擇隱藏已知文件類型的擴展名，去掉勾選;第四，將創建的txt文件后綴名修改為bat，雙擊運行bat文件，就自動生成文件夾及子目錄下的所有文件清單了。第五，從清單中提取姓名，用Vlookup（）公式與表4的人員姓名作匹配，就可以查出哪些人員照片缺失，補齊照片即可。

當遇到復雜的統計任務，一是先觀察、再分析、尋找最簡便可行的解決辦法，盡量把一些細節想周全，避免尾部工作的繁瑣。二是當任務完成，對此次工作過程進行復盤，將所用的工具方法和關鍵點進行歸納總結，匯總進入Excel工具集，以備它用或者再次啟動此類工作可以輕松應對。三是實施的過程中遇到困難時，要不恥下問，百度也好，咨詢Excel專家也好，尋求解決辦法，下次就會有提高。這樣經過幾次這種大型的統計，經驗將會大大增加，遇到更大的問題都容易解決。

結束語：Excel具備強大的功能，對一些復雜繁瑣機械的任務有妙招，可以適當考慮。

參考文獻

【1】作者：揣素榮，《淺議統計在人力資源管理工作中的應用》，2017

【2】作者：陳長偉，《Excel在人力資源管理中的應用》，2013

【3】作者：林金燦，《Excel在統計中的應用技巧》，2003

作者：樊娟玲，《信息系統在人力資源社會保障管理中的應用》，2015

廣州市長島光電機械廠