EXCEL教程之人力資源篇

1、項目項目4 4 制作員工人事檔案管理表制作員工人事檔案管理表2.12.1項目項目5 5 制作員工培訓管理表制作員工培訓管理表2.22.2項目項目6 6 制作員工工資管理表制作員工工資管理表2.32.32.1.1 項目背景項目背景員工人事檔案的整理分類、匯總、分員工人事檔案的整理分類、匯總、分析、查詢等是一件比較復雜的工作。析、查詢等是一件比較復雜的工作。使用使用Excel可以實現(xiàn)人事管理的自動化，可以實現(xiàn)人事管理的自動化，提高人事檔案管理的條理化。提高人事檔案管理的條理化。本項目通過制作本項目通過制作“員工人事檔案管理員工人事檔案管理表表”，來介紹，來介紹Excel軟件在人力資源管理中軟件在人

2、力資源管理中的應用。的應用。2.1.2 項目效果項目效果圖圖2-1 員工人事檔案篩選效果圖員工人事檔案篩選效果圖圖圖2-2 公司各部門人員分配效果圖公司各部門人員分配效果圖2.1.3 知識與技能知識與技能工作簿的創(chuàng)建工作簿的創(chuàng)建工作表重命名工作表重命名導入外部數(shù)據(jù)導入外部數(shù)據(jù)數(shù)據(jù)鏈接數(shù)據(jù)鏈接數(shù)據(jù)的篩選數(shù)據(jù)的篩選 COUNTIF函數(shù)的使用函數(shù)的使用創(chuàng)建圖表創(chuàng)建圖表2.1.4 解決方案解決方案任務任務1 創(chuàng)建工作簿，重命名工作表創(chuàng)建工作簿，重命名工作表（1）啟動）啟動Excel 2003，新建一個空白，新建一個空白工作簿。工作簿。（2）將創(chuàng)建的工作薄以）將創(chuàng)建的工作薄以“員工人事員工人事檔案管理表

3、檔案管理表”為名保存。為名保存。（3）將）將“員工人事檔案管理表員工人事檔案管理表”工工作簿中的作簿中的Sheet1工作表重命名為工作表重命名為“員員工檔案信息工檔案信息”。任務任務2 導入導入“員工信息員工信息”將行政篇中導出的將行政篇中導出的“員工信息員工信息”數(shù)據(jù)數(shù)據(jù)導入到當前的文件中，輕松獲取外部人事導入到當前的文件中，輕松獲取外部人事檔案信息。檔案信息。（1）選中）選中“員工人事檔案管理表員工人事檔案管理表”工作簿中的工作簿中的“員工檔案信息員工檔案信息”工作表。工作表。（2）選擇）選擇【數(shù)據(jù)數(shù)據(jù)】菜單中菜單中【導入外導入外部數(shù)據(jù)部數(shù)據(jù)】命令的子命令命令的子命令【導入數(shù)據(jù)導入數(shù)據(jù)】，

4、打開打開【選取數(shù)據(jù)源選取數(shù)據(jù)源】對話框。對話框。（3）選擇數(shù)據(jù)源文件）選擇數(shù)據(jù)源文件“員工信員工信息息.prn”，（4）從對話框的）從對話框的“原始數(shù)據(jù)類型原始數(shù)據(jù)類型”中選擇中選擇“固定長度固定長度”作為最合適的文作為最合適的文件類型，在件類型，在“導入起始行導入起始行”文本框中文本框中保持默認值保持默認值“1”，在文件原始格式，在文件原始格式下拉列表中選擇下拉列表中選擇“936 ：簡體中文：簡體中文（GB2312）”。（5）按文本導入向?qū)нM行操作。在）按文本導入向?qū)нM行操作。在“數(shù)據(jù)預覽數(shù)據(jù)預覽”區(qū)內(nèi)設置字段寬度，也區(qū)內(nèi)設置字段寬度，也就是列間隔；設置每列的數(shù)據(jù)類型。就是列間隔；設置每列的

5、數(shù)據(jù)類型。（6）單擊）單擊【確定確定】按鈕，完成數(shù)據(jù)按鈕，完成數(shù)據(jù)導入。導入。任務任務3 創(chuàng)建創(chuàng)建“員工個人詳細資料員工個人詳細資料”鏈鏈接接（1）鏈接到已有文件。）鏈接到已有文件。（2）鏈接到新建文件。）鏈接到新建文件。任務任務4 查閱查閱“員工個人詳細資料員工個人詳細資料”當需要查閱某個員工的個人詳細資料當需要查閱某個員工的個人詳細資料時，我們可通過激活超鏈接進行訪問。時，我們可通過激活超鏈接進行訪問。任務任務5 復制復制“員工檔案信息員工檔案信息”工作表工作表（1）選中）選中“員工檔案信息員工檔案信息”工作表。工作表。（2）將該工作表復制三份，并分別）將該工作表復制三份，并分別重命名為重

6、命名為“女員工檔案信息女員工檔案信息”、“入入職時間最早的職時間最早的5名員工信息名員工信息”、“2005年年-2007年入職的員工年入職的員工”。任務任務6 通過數(shù)據(jù)篩選查詢女員工檔案通過數(shù)據(jù)篩選查詢女員工檔案信息信息任務任務7 通過數(shù)據(jù)篩選查詢?nèi)肼殨r間最通過數(shù)據(jù)篩選查詢?nèi)肼殨r間最早的早的5名員工信息名員工信息任務任務8 通過數(shù)據(jù)篩選查詢通過數(shù)據(jù)篩選查詢2005年年-2007年入職的員工年入職的員工任務任務9 統(tǒng)計公司各部門員工人數(shù)統(tǒng)計公司各部門員工人數(shù)（1）將）將Sheet2工作表重命名為工作表重命名為“公公司各部門員工人數(shù)司各部門員工人數(shù)”。（2）建立表格框架。）建立表格框架。（3）選中

7、）選中“公司各部門員工人數(shù)公司各部門員工人數(shù)”工作表的工作表的B3單元格。單元格。（4）輸入公式）輸入公式“=COUNTIF(員工檔員工檔案信息案信息!$D$2:$D$26,行政部行政部)”，按按【Enter】鍵，在鍵，在B3中顯示中顯示“行政行政部部”人數(shù)。人數(shù)。（5）同樣地，統(tǒng)計出其他部門的人）同樣地，統(tǒng)計出其他部門的人數(shù)。數(shù)。任務任務10 創(chuàng)建公司各部門人員比例圖創(chuàng)建公司各部門人員比例圖（1）選中）選中“公司各部門員工人數(shù)公司各部門員工人數(shù)”工作表中的工作表中的A2:B9單元格區(qū)域。單元格區(qū)域。（2）創(chuàng)建）創(chuàng)建“三位餅圖三位餅圖”。（3）設置圖表的標題格式。）設置圖表的標題格式。2.1.

8、5 項目小結(jié)項目小結(jié)本項目通過制作本項目通過制作“員工人事檔案管理員工人事檔案管理表表”，主要介紹了工作簿的創(chuàng)建、工作表，主要介紹了工作簿的創(chuàng)建、工作表重命名、導入外部數(shù)據(jù)創(chuàng)建員工檔案信息重命名、導入外部數(shù)據(jù)創(chuàng)建員工檔案信息表、利用超鏈接查閱員工個人詳細資料、表、利用超鏈接查閱員工個人詳細資料、使用篩選查詢員工檔案信息、使用使用篩選查詢員工檔案信息、使用COUNTIF函數(shù)進行各部門人數(shù)統(tǒng)計。函數(shù)進行各部門人數(shù)統(tǒng)計。在此基礎上，利用統(tǒng)計數(shù)據(jù)創(chuàng)建各部在此基礎上，利用統(tǒng)計數(shù)據(jù)創(chuàng)建各部門人員比例圖表。門人員比例圖表。2.1.6 拓展項目拓展項目1. 1.通過數(shù)據(jù)篩選查詢通過數(shù)據(jù)篩選查詢8080后的員工

9、后的員工檔案信息。檔案信息。2. 2.通過數(shù)據(jù)篩選查詢通過數(shù)據(jù)篩選查詢“市場部市場部”的男員工檔案信息。的男員工檔案信息。3.3.統(tǒng)計公司員工學歷情況并制統(tǒng)計公司員工學歷情況并制作比例圖。作比例圖。2.2.1 項目背景項目背景企業(yè)在對員工進行培訓后，通常要根企業(yè)在對員工進行培訓后，通常要根據(jù)培訓項目進行相應的考核。據(jù)培訓項目進行相應的考核。人力資源部需要針對考核成績制定相人力資源部需要針對考核成績制定相應的培訓成績管理表。應的培訓成績管理表。本項目通過制作本項目通過制作“員工培訓管理表員工培訓管理表”，來介紹來介紹Excel軟件在培訓管理方面的應用。軟件在培訓管理方面的應用。2.2.2 項目效

10、果項目效果圖圖2-3 員工培訓管理表員工培訓管理表圖圖2-4 員工培訓考核數(shù)據(jù)透視圖員工培訓考核數(shù)據(jù)透視圖2.2.3 知識與技能知識與技能工作簿的創(chuàng)建工作簿的創(chuàng)建工作表重命名工作表重命名數(shù)據(jù)有效性數(shù)據(jù)有效性函數(shù)函數(shù)AVERAGE、SUM、COUNTIF和和RANK的應用的應用美化工作表美化工作表數(shù)據(jù)透視表數(shù)據(jù)透視表數(shù)據(jù)透視圖數(shù)據(jù)透視圖2.2.4 解決方案解決方案任務任務1 創(chuàng)建工作簿，重命名工作表創(chuàng)建工作簿，重命名工作表（1）啟動）啟動Excel 2003，新建一個空白，新建一個空白工作簿。工作簿。（2）將創(chuàng)建的工作薄以）將創(chuàng)建的工作薄以“員工培訓員工培訓管理表管理表”為名保存。為名保存。（3

11、）將）將“員工人事檔案管理表員工人事檔案管理表”工工作簿中的作簿中的Sheet1工作表重命名為工作表重命名為“員員工培訓成績表工培訓成績表”。任務任務2 創(chuàng)建創(chuàng)建“員工培訓成績表員工培訓成績表”框架框架（1）在）在“員工培訓成績表員工培訓成績表”中輸入中輸入工作表標題。工作表標題。（2）輸入表格標題字段。）輸入表格標題字段。任務任務3 輸入輸入“序號序號”利用填充句柄，輸入序號。利用填充句柄，輸入序號。任務任務4 輸入輸入“姓名姓名”和和“部門部門”（1）輸入員工）輸入員工“姓名姓名”。（2）輸入員工）輸入員工“部門部門”。任務任務5 設置各培訓項目成績的數(shù)據(jù)有設置各培訓項目成績的數(shù)據(jù)有效性效

12、性在輸入數(shù)據(jù)的過程中，為盡量減小數(shù)在輸入數(shù)據(jù)的過程中，為盡量減小數(shù)據(jù)輸入的錯誤，提高數(shù)據(jù)的有效性，可對據(jù)輸入的錯誤，提高數(shù)據(jù)的有效性，可對要輸入數(shù)據(jù)的單元格區(qū)域設置有效性規(guī)則，要輸入數(shù)據(jù)的單元格區(qū)域設置有效性規(guī)則，使輸入的數(shù)據(jù)在規(guī)定的范圍之內(nèi)。使輸入的數(shù)據(jù)在規(guī)定的范圍之內(nèi)。即輸入的成績范圍在即輸入的成績范圍在0100之間。之間。任務任務6 統(tǒng)計統(tǒng)計“平均成績平均成績”和總成績和總成績?nèi)蝿杖蝿? 利用利用RANK函數(shù)排列員工培訓函數(shù)排列員工培訓成績的名次成績的名次（1）選中）選中I3單元格。單元格。（2）輸入公式）輸入公式“=RANK(H3,$H$3:$H$21)”按按【Enter】鍵確認。鍵確

13、認。（3）選中）選中I3單元格，拖動填充句柄單元格，拖動填充句柄至至I21單元格，將公式復制到單元格，將公式復制到I4:H21單元格區(qū)域中，排列出所有名次。單元格區(qū)域中，排列出所有名次。任務任務8 統(tǒng)計各培訓項目的平均成績統(tǒng)計各培訓項目的平均成績（1）在）在A22單元格中輸入單元格中輸入“各培訓各培訓項目平均成績項目平均成績”。（2）選中）選中D22單元格，輸入公式單元格，輸入公式“=AVERAGE(D3:D21)”，按，按【Enter】鍵確認。鍵確認。（5）選中）選中D22單元格，拖動填充句單元格，拖動填充句柄至柄至F22單元格，將公式復制到單元格，將公式復制到E22:F22單元格區(qū)域中。單

14、元格區(qū)域中。任務任務9 利用利用COUNTIF函數(shù)統(tǒng)計考核函數(shù)統(tǒng)計考核不合格的員工人數(shù)不合格的員工人數(shù)這里，假定員工的所有項目平均成績這里，假定員工的所有項目平均成績低于低于80分，則認定為不合格。分，則認定為不合格。（1）在）在G22單元格中輸入單元格中輸入“不合格不合格人數(shù)人數(shù)”。（2）選中）選中I22單元格，輸入公式單元格，輸入公式“=COUNTIF(G3:G21,2000,IF(M3-2000)500,(M3-2000)=2000),(M3-2000)*0.1-25),0)按按【Enter】鍵確認鍵確認,可計算出相應可計算出相應的個人所得稅。的個人所得稅。選中選中N3單元格，用鼠標拖動

15、其填充單元格，用鼠標拖動其填充句柄至句柄至N26單元格，將公式復制到單元格，將公式復制到N3:N26單元格區(qū)域中，可計算出所有員工的個人單元格區(qū)域中，可計算出所有員工的個人所得稅。所得稅。（13）計算）計算“實發(fā)工資實發(fā)工資”。任務任務7 格式化格式化“員工工資明細表員工工資明細表”（1）設置工作表的標題格式。）設置工作表的標題格式。（2）將表中所有的數(shù)據(jù)項格式設置）將表中所有的數(shù)據(jù)項格式設置為為“會計專用會計專用”格式，保留格式，保留2位小數(shù)，位小數(shù)，貨幣符號為貨幣符號為“無無”。（3）為表格設置內(nèi)細外粗的藍色邊）為表格設置內(nèi)細外粗的藍色邊框?？颉＃?）為）為“應發(fā)工資應發(fā)工資”和和“稅前工

16、資稅前工資”兩列的數(shù)據(jù)區(qū)域添加兩列的數(shù)據(jù)區(qū)域添加“淡藍淡藍”色底紋，色底紋，為為“實發(fā)工資實發(fā)工資”列數(shù)據(jù)區(qū)域添加列數(shù)據(jù)區(qū)域添加“天天藍藍”色底紋。色底紋。任務任務8 制作制作“工資條工資條”（1）將）將Sheet3工作表重命名為工作表重命名為“工工資條資條”。（2）選中）選中“工資條工資條”工作表的工作表的A1單單元格。元格。（3）在）在“編輯欄編輯欄”中輸入下面的公中輸入下面的公式，然后按式，然后按【Enter】鍵確認。鍵確認。=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資明細員工工資明細表表!A$2,INDEX(員工工資明細員工工資明細表表!$A:$O

17、,INT(ROW()-1)/3)+3,COLUMN()（4）選中）選中A1單元格，用鼠標拖動其單元格，用鼠標拖動其填充句柄至填充句柄至O1單元格，將公式復制單元格，將公式復制到到B1:O1單元格區(qū)域中。單元格區(qū)域中。（5）選中）選中A1:O1單元格區(qū)域，用鼠單元格區(qū)域，用鼠標拖動其填充句柄至標拖動其填充句柄至O74單元格，將單元格，將公式復制到公式復制到A2:O74單元格區(qū)域中。單元格區(qū)域中。（6）適當調(diào)整單元格的列寬，使單）適當調(diào)整單元格的列寬，使單元格的內(nèi)容能完整的顯示出來。元格的內(nèi)容能完整的顯示出來。（7）美化）美化“工資條工資條”工作表。工作表。2.3.5 項目小結(jié)項目小結(jié)本項目通過制作本項目通過制作“員工工資管理表員工工資管理表”，主要介紹了工作簿的創(chuàng)建、工作表重命名、主要介紹了工作簿的創(chuàng)建、工作表重命名、外部數(shù)據(jù)的導入，使用函數(shù)外部數(shù)據(jù)的導入，使用函數(shù)Year、Datedif、Today、Round、Sum等