1、Excel 人力資源篇,2.1 項目4 制作員工人事檔案管理表,2.1.1 項目背景 員工人事檔案的整理分類、匯總、分析、查詢等是一件比較復雜的工作。 使用Excel可以實現人事管理的自動化，提高人事檔案管理的條理化。,本項目通過制作“員工人事檔案管理表”，來介紹Excel軟件在人力資源管理中的應用。,2.1.2 項目效果,圖2-1 員工人事檔案篩選效果圖,圖2-2 公司各部門人員分配效果圖,2.1.3 知識與技能 工作簿的創(chuàng)建 工作表重命名 導入外部數據 數據鏈接 數據的篩選 COUNTIF函數的使用 創(chuàng)建圖表,2.1.4 解決方案 任務1 創(chuàng)建工作簿，重命名工作表 （1）啟動Excel 2

2、003，新建一個空白工作簿。 （2）將創(chuàng)建的工作薄以“員工人事檔案管理表”為名保存。 （3）將“員工人事檔案管理表”工作簿中的Sheet1工作表重命名為“員工檔案信息”。,任務2 導入“員工信息” 將行政篇中導出的“員工信息”數據導入到當前的文件中，輕松獲取外部人事檔案信息。,（1）選中“員工人事檔案管理表”工作簿中的“員工檔案信息”工作表。 （2）選擇【數據】菜單中【導入外部數據】命令的子命令【導入數據】，打開【選取數據源】對話框。,（3）選擇數據源文件“員工信息.prn”， （4）從對話框的“原始數據類型”中選擇“固定長度”作為最合適的文件類型，在“導入起始行”文本框中保持默認值“1”，在

3、文件原始格式下拉列表中選擇“936 ：簡體中文（GB2312）”。,（5）按文本導入向導進行操作。在“數據預覽”區(qū)內設置字段寬度，也就是列間隔；設置每列的數據類型。 （6）單擊【確定】按鈕，完成數據導入。,任務3 創(chuàng)建“員工個人詳細資料”鏈接 （1）鏈接到已有文件。 （2）鏈接到新建文件。,任務4 查閱“員工個人詳細資料” 當需要查閱某個員工的個人詳細資料時，我們可通過激活超鏈接進行訪問。,任務5 復制“員工檔案信息”工作表 （1）選中“員工檔案信息”工作表。 （2）將該工作表復制三份，并分別重命名為“女員工檔案信息”、“入職時間最早的5名員工信息”、“2005年-2007年入職的員工”。,任

4、務6 通過數據篩選查詢女員工檔案信息 任務7 通過數據篩選查詢入職時間最早的5名員工信息 任務8 通過數據篩選查詢2005年-2007年入職的員工,任務9 統(tǒng)計公司各部門員工人數 （1）將Sheet2工作表重命名為“公司各部門員工人數”。 （2）建立表格框架。 （3）選中“公司各部門員工人數”工作表的B3單元格。,（4）輸入公式“=COUNTIF(員工檔案信息!$D$2:$D$26,行政部)”，按【Enter】鍵，在B3中顯示“行政部”人數。 （5）同樣地，統(tǒng)計出其他部門的人數。,任務10 創(chuàng)建公司各部門人員比例圖 （1）選中“公司各部門員工人數”工作表中的A2:B9單元格區(qū)域。 （2）創(chuàng)建“

5、三位餅圖”。 （3）設置圖表的標題格式。,2.1.5 項目小結 本項目通過制作“員工人事檔案管理表”，主要介紹了工作簿的創(chuàng)建、工作表重命名、導入外部數據創(chuàng)建員工檔案信息表、利用超鏈接查閱員工個人詳細資料、使用篩選查詢員工檔案信息、使用COUNTIF函數進行各部門人數統(tǒng)計。 在此基礎上，利用統(tǒng)計數據創(chuàng)建各部門人員比例圖表。,2.1.6 拓展項目 1.通過數據篩選查詢80后的員工檔案信息。 2.通過數據篩選查詢“市場部”的男員工檔案信息。 3.統(tǒng)計公司員工學歷情況并制作比例圖。,2.2 項目5 制作員工培訓管理表,2.2.1 項目背景 企業(yè)在對員工進行培訓后，通常要根據培訓項目進行相應的考核。 人

6、力資源部需要針對考核成績制定相應的培訓成績管理表。 本項目通過制作“員工培訓管理表”，來介紹Excel軟件在培訓管理方面的應用。,2.2.2 項目效果,圖2-3 員工培訓管理表,圖2-4 員工培訓考核數據透視圖,2.2.3 知識與技能 工作簿的創(chuàng)建 工作表重命名 數據有效性 函數AVERAGE、SUM、COUNTIF和RANK的應用 美化工作表 數據透視表 數據透視圖,2.2.4 解決方案 任務1 創(chuàng)建工作簿，重命名工作表 （1）啟動Excel 2003，新建一個空白工作簿。 （2）將創(chuàng)建的工作薄以“員工培訓管理表”為名保存。 （3）將“員工人事檔案管理表”工作簿中的Sheet1工作表重命名為

7、“員工培訓成績表”。,任務2 創(chuàng)建“員工培訓成績表”框架 （1）在“員工培訓成績表”中輸入工作表標題。 （2）輸入表格標題字段。,任務3 輸入“序號” 利用填充句柄，輸入序號。 任務4 輸入“姓名”和“部門” （1）輸入員工“姓名”。 （2）輸入員工“部門”。,任務5 設置各培訓項目成績的數據有效性 在輸入數據的過程中，為盡量減小數據輸入的錯誤，提高數據的有效性，可對要輸入數據的單元格區(qū)域設置有效性規(guī)則，使輸入的數據在規(guī)定的范圍之內。 即輸入的成績范圍在0100之間。,任務6 統(tǒng)計“平均成績”和總成績 任務7 利用RANK函數排列員工培訓成績的名次 （1）選中I3單元格。,（2）輸入公式“=R

8、ANK(H3,$H$3:$H$21)”按【Enter】鍵確認。 （3）選中I3單元格，拖動填充句柄至I21單元格，將公式復制到I4:H21單元格區(qū)域中，排列出所有名次。,任務8 統(tǒng)計各培訓項目的平均成績 （1）在A22單元格中輸入“各培訓項目平均成績”。 （2）選中D22單元格，輸入公式“=AVERAGE(D3:D21)”，按【Enter】鍵確認。,（5）選中D22單元格，拖動填充句柄至F22單元格，將公式復制到E22:F22單元格區(qū)域中。,任務9 利用COUNTIF函數統(tǒng)計考核不合格的員工人數 這里，假定員工的所有項目平均成績低于80分，則認定為不合格。,（1）在G22單元格中輸入“不合格人

9、數”。 （2）選中I22單元格，輸入公式“=COUNTIF(G3:G21,80)”。 （3）按【Enter】鍵確認。,任務10 設置單元格數據格式 將“平均成績”和“各培訓項目平均成績”的數據保留為1位小數。,任務11 美化員工培訓成績表 （1）設置工作表標題格式。 （2）合并單元格。 （3）將表格中所有數據區(qū)域的內容設置為居中對齊。,（4）適當調整表格的行高和列寬，能顯示表格內容。 （5）為表格設置邊框。,任務12 制作員工培訓考核數據透視圖 （1）選中“員工培訓成績表”。 （2）選擇【數據】菜單中的【數據透視表和數據透視圖】命令，打開【數據透視表和數據透視圖向導對話框，按提示進行操作。,（

10、3）將“姓名”作為“行”字段，將“計算機技能”作為“數據”項。 （4）選擇【匯總方式】為“最大值”。,（5）單擊【完成】按鈕，系統(tǒng)將在工作簿中插入一張工作表Sheet4和一張工作表Chart1。在Sheet4中生成數據透視表，將Sheet4工作表重命名為“員工培訓成績數據透視表”，在Chart1中生成數據透視圖，將Chart1工作命名為“員工培訓考核數據透視圖”。,2.2.5 項目小結 本項目通過制作“員工培訓管理表”，主要介紹了工作簿的創(chuàng)建、工作表重命名、數據有效性設置、函數AVERAGE、SUM、COUNTIF和RANK的應用、美化工作表等。 在此基礎上，利用培訓成績表中的數據，通過數據透

11、視表和數據透視圖對員工培訓成績進行匯總、分析，使表格中的數據更為直觀、生動。,2.2.6 拓展項目 1.按培訓成績名次進行升序排列。 2.制作各培訓項目平均成績比較圖。 3.制作公司各部門培訓成績數據透視圖。,2.3 項目6 制作員工工資管理表,2.3.1 項目背景 制作工資表示通常需要綜合大量的數據，如基本工資、績效工資、補貼、扣款項等。 如果用手工做賬，不僅工作量非常大，而且還往往容易出錯。 本項目通過制作“員工工資管理表”來介紹Excel軟件在工資管理方面的應用。,2.3.2 項目效果,圖2-5 員工工資明細表效果圖,圖2-6 員工工資條效果圖,2.3.3 知識與技能 工作簿的創(chuàng)建 工作

12、表重命名 導入外部數據 函數Year、Round、Vlookup、IF、INDEX、ROW、COLUMN、INT的使用 公式的使用 數據透視表 數據透視圖,2.3.4 解決方案 任務1 創(chuàng)建工作簿，重命名工作表 （1）啟動Excel 2003，新建一個空白工作簿。 （2）將創(chuàng)建的工作薄以“員工工資管理表”為名保存。 （3）將“員工人事檔案管理表”工作簿中的Sheet1工作表重命名為“工資基礎信息表”。,任務2 導入“員工信息” 將行政篇中導出的“員工信息”數據導入到當前工作表中，作為員工“工資基礎信息表”的數據。,任務3 創(chuàng)建“工資基礎信息表” （1）選中“工資基礎信息表”工作表。 （2）刪除

13、“身份證號碼”、“學歷”、“職稱”、“性別”和“出生日期”列的數據。,圖2-60 刪除數據后的工作表,（3）分別在E1、F1、G1單元格中輸入標題字段名稱“基本工資”、“績效工資”和“工齡工資”。 （4）輸入“基本工資數據”。,（5）計算“績效工資”。 這里，績效工資=基本工資*50%。 （6）計算“工齡工資”。 這里，如果“工齡”超過10年，則工齡工資為500元，否則，按每年50元計算。,任務4 創(chuàng)建“加班費結算表” （1）復制“工資基礎信息表”，將復制后的工作表重命名為“加班費結算表”。 （2）刪除“入職時間”、“績效工資”和“工齡工資”列。 （3）在E1、F1單元格中分別輸入標題“加班時

14、間”和“加班費”。,（4）輸入加班時間。 （5）計算機加班費。 加班費的計算方法為：加班費=基本工資/30/8*1.5*加班時間,任務5 創(chuàng)建“考勤扣款結算表” （1）復制“工資基礎信息表”，將復制后的工作表重命名為“考勤扣款結算表”。 （2）刪除“入職時間”、“績效工資”和“工齡工資”列。,（3）在E1:K1單元格中分別輸入標題“遲到”、“遲到扣款”、“病假”、“病假扣款”、“事假”、“事假扣款”和“扣款合計”。,（4）輸入“遲到”、“病假”、“事假”的數據。 （5）計算“遲到扣款”。 這里，假設每遲到一次扣款為50元。,（6）計算“病假扣款”。 這里，假設每病假一天扣款為當日工資收入的50

15、%，即“病假扣款=基本工資/30*0.5*病假天數”。,（7）計算“事假扣款”。 這里，假設每事假一天扣款為當日工資收入，即“事假扣款=基本工資/30*事假天數”。 （8）計算“扣款合計”。,任務6 創(chuàng)建“員工工資明細表” （1）將Sheet2工作表重命名為“員工工資明細表”。 （2）參創(chuàng)建員工工資明細表的框架。 （3）填充“編號”、“姓名”和“部門”數據。,（4）導入“基本工資”、“績效工資”、“工齡工資”和“加班費”數據。,選中D3單元格。 選擇【插入】菜單中的【函數】命令，從函數列表中選擇“VLOOKUP”函數，打開【函數參數】對話框，設置參數。,單擊【確定】按鈕，導入相應的“基本工資”

16、數據。 選中D3單元格，用鼠標拖動其填充句柄至D26單元格，將公式復制到D3:D26單元格區(qū)域中，可導入所有員工的基本工資。,（5）同樣地方式，分別導入“績效工資”、“工齡工資”和“加班費”數據。 （6）導入“加班費”數據。 （7）計算“應發(fā)工資”。,（8）填充“養(yǎng)老保險”數據。 這里，養(yǎng)老保險的數據為個人繳納部分，計算方法為：養(yǎng)老保險=（基本工資+績效工資）*8%,（9）填充“醫(yī)療保險”數據。 這里，醫(yī)療保險的數據為個人繳納部分，計算方法為：醫(yī)療保險=（基本工資+績效工資）*2%,（10）填充“失業(yè)保險”數據。 這里，失業(yè)保險的數據為個人繳納部分，計算方法為：失業(yè)保險=（基本工資+績效工資）

17、*1% （11）導入“考勤扣款”數據。,（12）計算“稅前工資”。 （13）計算“個人所得稅”。 應交個人所得稅額=應納稅所得額*適用稅率-速算扣除數，個人所得稅稅率見表2-1所示。,表2-1 個人所得稅稅率表,選中N3單元格。 輸入公式： =IF(M32000,IF(M3-2000)500,(M3-2000)=2000),(M3-2000)*0.1-25),0),按【Enter】鍵確認,可計算出相應的個人所得稅。 選中N3單元格，用鼠標拖動其填充句柄至N26單元格，將公式復制到N3:N26單元格區(qū)域中，可計算出所有員工的個人所得稅。,（13）計算“實發(fā)工資”。,任務7 格式化“員工工資明細表

18、” （1）設置工作表的標題格式。 （2）將表中所有的數據項格式設置為“會計專用”格式，保留2位小數，貨幣符號為“無”。,（3）為表格設置內細外粗的藍色邊框。 （4）為“應發(fā)工資”和“稅前工資”兩列的數據區(qū)域添加“淡藍”色底紋，為“實發(fā)工資”列數據區(qū)域添加“天藍”色底紋。,任務8 制作“工資條” （1）將Sheet3工作表重命名為“工資條”。 （2）選中“工資條”工作表的A1單元格。,（3）在“編輯欄”中輸入下面的公式，然后按【Enter】鍵確認。 =IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資明細表!A$2,INDEX(員工工資明細表!$A:$O,INT(ROW()-1)/3)+3,COLUMN(),（4）選中A1單元格，用鼠標拖動其填充句柄至O1單元格，將公式復制到B1:O1單元格區(qū)域中。 （5）選中A1:O1單元格區(qū)域，用鼠標拖動其填充句柄至O74單元格，將公式復制到A2:O74單元格區(qū)域中。,（6）適當調整單元格的列