人力資源管理篇

Excel人力資源篇項目4制作員工人事檔案管理表2.1項目5制作員工培訓管理表2.2項目6制作員工工資管理表2.32.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〕啟動Excel2003，新建一個空白工作簿?！?〕將創(chuàng)立的工作薄以“員工人事檔案管理表〞為名保存?！?〕將“員工人事檔案管理表〞工作簿中的Sheet1工作表重命名為“員工檔案信息〞。 任務2導入“員工信息〞 將行政篇中導出的“員工信息〞數據導入到當前的文件中，輕松獲取外部人事檔案信息?！?〕選中“員工人事檔案管理表〞工作簿中的“員工檔案信息〞工作表?！?〕選擇【數據】菜單中【導入外部數據】命令的子命令【導入數據】，翻開【選取數據源】對話框。〔3〕選擇數據源文件“員工信息.prn〞，〔4〕從對話框的“原始數據類型〞中選擇“固定長度〞作為最適宜的文件類型，在“導入起始行〞文本框中保持默認值“1〞，在文件原始格式下拉列表中選擇“936：簡體中文〔GB2312〕〞?！?〕按文本導入向導進行操作。在“數據預覽〞區(qū)內設置字段寬度，也就是列間隔；設置每列的數據類型。〔6〕單擊【確定】按鈕，完成數據導入。 任務3創(chuàng)立“員工個人詳細資料〞鏈接〔1〕鏈接到已有文件?！?〕鏈接到新建文件。 任務4查閱“員工個人詳細資料〞 當需要查閱某個員工的個人詳細資料時，我們可通過激活超鏈接進行訪問。 任務5復制“員工檔案信息〞工作表〔1〕選中“員工檔案信息〞工作表?！?〕將該工作表復制三份，并分別重命名為“女員工檔案信息〞、“入職時間最早的5名員工信息〞、“2005年-2007年入職的員工〞。

任務6通過數據篩選查詢女員工檔案信息 任務7通過數據篩選查詢入職時間最早的5名員工信息 任務8通過數據篩選查詢2005年-2007年入職的員工 任務9統(tǒng)計公司各部門員工人數〔1〕將Sheet2工作表重命名為“公司各部門員工人數〞?！?〕建立表格框架?！?〕選中“公司各部門員工人數〞工作表的B3單元格?！?〕輸入公式“=COUNTIF(員工檔案信息!$D$2:$D$26,"行政部")〞，按【Enter】鍵，在B3中顯示“行政部〞人數?！?〕同樣地，統(tǒng)計出其他部門的人數。 任務10創(chuàng)立公司各部門人員比例圖〔1〕選中“公司各部門員工人數〞工作表中的A2:B9單元格區(qū)域?！?〕創(chuàng)立“三位餅圖〞?！?〕設置圖表的標題格式。 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è)在對員工進行培訓后，通常要根據培訓工程進行相應的考核。 人力資源部需要針對考核成績制定相應的培訓成績管理表。 本工程通過制作“員工培訓管理表〞，來介紹Excel軟件在培訓管理方面的應用。 2.2.2工程效果圖2-3員工培訓管理表圖2-4員工培訓考核數據透視圖 2.2.3知識與技能 ●工作簿的創(chuàng)立 ●工作表重命名 ●數據有效性 ●函數AVERAGE、SUM、COUNTIF和RANK的應用 ●美化工作表 ●數據透視表 ●數據透視圖 2.2.4解決方案 任務1創(chuàng)立工作簿，重命名工作表〔1〕啟動Excel2003，新建一個空白工作簿?！?〕將創(chuàng)立的工作薄以“員工培訓管理表〞為名保存?！?〕將“員工人事檔案管理表〞工作簿中的Sheet1工作表重命名為“員工培訓成績表〞。 任務2創(chuàng)立“員工培訓成績表〞框架〔1〕在“員工培訓成績表〞中輸入工作表標題?！?〕輸入表格標題字段。 任務3輸入“序號〞 利用填充句柄，輸入序號。 任務4輸入“姓名〞和“部門〞〔1〕輸入員工“姓名〞。〔2〕輸入員工“部門〞。 任務5設置各培訓工程成績的數據有效性 在輸入數據的過程中，為盡量減小數據輸入的錯誤，提高數據的有效性，可對要輸入數據的單元格區(qū)域設置有效性規(guī)那么，使輸入的數據在規(guī)定的范圍之內。 即輸入的成績范圍在0～100之間。 任務6統(tǒng)計“平均成績〞和總成績 任務7利用RANK函數排列員工培訓成績的名次〔1〕選中I3單元格?！?〕輸入公式“=RANK(H3,$H$3:$H$21)〞按【Enter】鍵確認。〔3〕選中I3單元格，拖動填充句柄至I21單元格，將公式復制到I4:H21單元格區(qū)域中，排列出所有名次。 任務8統(tǒng)計各培訓工程的平均成績〔1〕在A22單元格中輸入“各培訓工程平均成績〞?！?〕選中D22單元格，輸入公式“=AVERAGE(D3:D21)〞，按【Enter】鍵確認?！?〕選中D22單元格，拖動填充句柄至F22單元格，將公式復制到E22:F22單元格區(qū)域中。 任務9利用COUNTIF函數統(tǒng)計考核不合格的員工人數 這里，假定員工的所有工程平均成績低于80分，那么認定為不合格?！?〕在G22單元格中輸入“不合格人數〞?！?〕選中I22單元格，輸入公式“=COUNTIF(G3:G21,"<80")〞?！?〕按【Enter】鍵確認。 任務10設置單元格數據格式 將“平均成績〞和“各培訓工程平均成績〞的數據保存為1位小數。 任務11美化員工培訓成績表〔1〕設置工作表標題格式?！?〕合并單元格?！?〕將表格中所有數據區(qū)域的內容設置為居中對齊。〔4〕適當調整表格的行高和列寬，能顯示表格內容?！?〕為表格設置邊框。 任務12制作員工培訓考核數據透視圖〔1〕選中“員工培訓成績表〞?！?〕選擇【數據】菜單中的【數據透視表和數據透視圖】命令，翻開【數據透視表和數據透視圖向導對話框，按提示進行操作?！?〕將“姓名〞作為“行〞字段，將“計算機技能〞作為“數據〞項。〔4〕選擇【匯總方式】為“最大值〞。〔5〕單擊【完成】按鈕，系統(tǒng)將在工作簿中插入一張工作表Sheet4和一張工作表Chart1。在Sheet4中生成數據透視表，將Sheet4工作表重命名為“員工培訓成績數據透視表〞，在Chart1中生成數據透視圖，將Chart1工作命名為“員工培訓考核數據透視圖〞。 2.2.5工程小結 本工程通過制作“員工培訓管理表〞，主要介紹了工作簿的創(chuàng)立、工作表重命名、數據有效性設置、函數AVERAGE、SUM、COUNTIF和RANK的應用、美化工作表等。 在此根底上，利用培訓成績表中的數據，通過數據透視表和數據透視圖對員工培訓成績進行匯總、分析，使表格中的數據更為直觀、生動。 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)立 ●工作表重命名 ●導入外部數據 ●函數Year、Round、Vlookup、IF、INDEX、ROW、COLUMN、INT的使用 ●公式的使用 ●數據透視表 ●數據透視圖 2.3.4解決方案 任務1創(chuàng)立工作簿，重命名工作表〔1〕啟動Excel2003，新建一個空白工作簿?！?〕將創(chuàng)立的工作薄以“員工工資管理表〞為名保存?！?〕將“員工人事檔案管理表〞工作簿中的Sheet1工作表重命名為“工資根底信息表〞。 任務2導入“員工信息〞 將行政篇中導出的“員工信息〞數據導入到當前工作表中，作為員工“工資根底信息表〞的數據。 任務3創(chuàng)立“工資根底信息表〞〔1〕選中“工資根底信息表〞工作表?！?〕刪除“身份證號碼〞、“學歷〞、“職稱〞、“性別〞和“出生日期〞列的數據。圖2-60刪除數據后的工作表〔3〕分別在E1、F1、G1單元格中輸入標題字段名稱“根本工資〞、“績效工資〞和“工齡工資〞。〔4〕輸入“根本工資數據〞?！?〕計算“績效工資〞。這里，績效工資=根本工資*50%?！?〕計算“工齡工資〞。 這里，如果“工齡〞超過10年，那么工齡工資為500元，否那么，按每年50元計算。 任務4創(chuàng)立“加班費結算表〞〔1〕復制“工資根底信息表〞，將復制后的工作表重命名為“加班費結算表〞?！?〕刪除“入職時間〞、“績效工資〞和“工齡工資〞列?！?〕在E1、F1單元格中分別輸入標題“加班時間〞和“加班費〞?！?〕輸入加班時間?！?〕計算機加班費。 加班費的計算方法為：加班費=根本工資/30/8*1.5*加班時間 任務5創(chuàng)立“考勤扣款結算表〞〔1〕復制“工資根底信息表〞，將復制后的工作表重命名為“考勤扣款結算表〞?！?〕刪除“入職時間〞、“績效工資〞和“工齡工資〞列?！?〕在E1:K1單元格中分別輸入標題“遲到〞、“遲到扣款〞、“病假〞、“病假扣款〞、“事假〞、“事假扣款〞和“扣款合計〞?！?〕輸入“遲到〞、“病假〞、“事假〞的數據?！?〕計算“遲到扣款〞。 這里，假設每遲到一次扣款為50元?！?〕計算“病假扣款〞。 這里，假設每病假一天扣款為當日工資收入的50%，即“病假扣款=根本工資/30*0.5*病假天數〞?！?〕計算“事假扣款〞。 這里，假設每事假一天扣款為當日工資收入，即“事假扣款=根本工資/30*事假天數〞?！?〕計算“扣款合計〞。 任務6創(chuàng)立“員工工資明細表〞〔1〕將Sheet2工作表重命名為“員工工資明細表〞?！?〕參創(chuàng)立員工工資明細表的框架。〔3〕填充“編號〞、“姓名〞和“部門〞數據。〔4〕導入“根本工資〞、“績效工資〞、“工齡工資〞和“加班費〞數據。 ①選中D3單元格。 ②選擇【插入】菜單中的【函數】命令，從函數列表中選擇“VLOOKUP〞函數，翻開【函數參數】對話框，設置參數。 ③單擊【確定】按鈕，導入相應的“根本工資〞數據。 ④選中D3單元格，用鼠標拖動其填充句柄至D26單元格，將公式復制到D3:D26單元格區(qū)域中，可導入所有員工的根本工資?！?〕同樣地方式，分別導入“績效工資〞、“工齡工資〞和“加班費〞數據?！?〕導入“加班費〞數據?！?〕計算“應發(fā)工資〞?！?〕填充“養(yǎng)老保險〞數據。 這里，養(yǎng)老保險的數據為個人繳納局部，計算方法為：養(yǎng)老保險=〔根本工資+績效工資〕*8%〔9〕填充“醫(yī)療保險〞數據。 這里，醫(yī)療保險的數據為個人繳納局部，計算方法為：醫(yī)療保險=〔根本工資+績效工資〕*2%〔10〕填充“失業(yè)保險〞數據。 這里，失業(yè)保險的數據為個人繳納局部，計算方法為：失業(yè)保險=〔根本工資+績效工資〕*1%〔11〕導入“考勤扣款〞數據。〔12〕計算“稅前工資〞?！?3〕計算“個人所得稅〞。 應交個人所得稅額=應納稅所得額*適用稅率-速算扣除數，個人所得稅稅率見表2-1所示。表2-1個人所得稅稅率表 ①選中N3單元格。 ②輸入公式：=IF(M3>2000,IF((M3-2000)<=500,(M3-2000)*0.05,IF(AND((M3-2000)>500,(M3-2000)<=2000),(M3-2000)*0.1-25)),0) ③按【Enter】鍵確認,可計算出相應的個人所得稅。 ④選中N3單元格，用鼠標拖動其填充句柄至N26單元格，將公式復制到N3:N26單元格區(qū)域中，可計算出所有員工的個人所得稅?！?3〕計算“實發(fā)工資〞。 任務7格式化“員工工資明細表〞〔1〕設置工作表的標題格式。〔2〕將表中所有的數據項格式設置為“會計專用〞格式，保存2位小數，貨幣符號為“無〞。〔3〕為表格設置內細外粗的藍色邊框。〔4〕為“應發(fā)工資〞和“稅前工資〞兩列的數據區(qū)域添加“淡藍〞色底紋，為“實發(fā)工資〞列數據區(qū)域添加“天藍〞色底紋。