人力資源管理篇

Excel人力資源篇項目4制作員工人事檔案管理表2.1項目5制作員工培訓(xùn)管理表2.2項目6制作員工工資管理表2.32.1工程4制作員工人事檔案管理表 2.1.1工程背景 員工人事檔案的整理分類、匯總、分析、查詢等是一件比較復(fù)雜的工作。 使用Excel可以實現(xiàn)人事管理的自動化，提高人事檔案管理的條理化。 本工程通過制作“員工人事檔案管理表〞，來介紹Excel軟件在人力資源管理中的應(yīng)用。 2.1.2工程效果圖2-1員工人事檔案篩選效果圖圖2-2公司各部門人員分配效果圖 2.1.3知識與技能 ●工作簿的創(chuàng)立 ●工作表重命名 ●導(dǎo)入外部數(shù)據(jù) ●數(shù)據(jù)鏈接 ●數(shù)據(jù)的篩選 ●COUNTIF函數(shù)的使用 ●創(chuàng)立圖表 2.1.4解決方案 任務(wù)1創(chuàng)立工作簿，重命名工作表〔1〕啟動Excel2003，新建一個空白工作簿?！?〕將創(chuàng)立的工作薄以“員工人事檔案管理表〞為名保存?！?〕將“員工人事檔案管理表〞工作簿中的Sheet1工作表重命名為“員工檔案信息〞。 任務(wù)2導(dǎo)入“員工信息〞 將行政篇中導(dǎo)出的“員工信息〞數(shù)據(jù)導(dǎo)入到當(dāng)前的文件中，輕松獲取外部人事檔案信息?！?〕選中“員工人事檔案管理表〞工作簿中的“員工檔案信息〞工作表。〔2〕選擇【數(shù)據(jù)】菜單中【導(dǎo)入外部數(shù)據(jù)】命令的子命令【導(dǎo)入數(shù)據(jù)】，翻開【選取數(shù)據(jù)源】對話框?！?〕選擇數(shù)據(jù)源文件“員工信息.prn〞，〔4〕從對話框的“原始數(shù)據(jù)類型〞中選擇“固定長度〞作為最適宜的文件類型，在“導(dǎo)入起始行〞文本框中保持默認(rèn)值“1〞，在文件原始格式下拉列表中選擇“936：簡體中文〔GB2312〕〞?！?〕按文本導(dǎo)入向?qū)нM(jìn)行操作。在“數(shù)據(jù)預(yù)覽〞區(qū)內(nèi)設(shè)置字段寬度，也就是列間隔；設(shè)置每列的數(shù)據(jù)類型。〔6〕單擊【確定】按鈕，完成數(shù)據(jù)導(dǎo)入。 任務(wù)3創(chuàng)立“員工個人詳細(xì)資料〞鏈接〔1〕鏈接到已有文件?！?〕鏈接到新建文件。 任務(wù)4查閱“員工個人詳細(xì)資料〞 當(dāng)需要查閱某個員工的個人詳細(xì)資料時，我們可通過激活超鏈接進(jìn)行訪問。 任務(wù)5復(fù)制“員工檔案信息〞工作表〔1〕選中“員工檔案信息〞工作表?！?〕將該工作表復(fù)制三份，并分別重命名為“女員工檔案信息〞、“入職時間最早的5名員工信息〞、“2005年-2007年入職的員工〞。

任務(wù)6通過數(shù)據(jù)篩選查詢女員工檔案信息 任務(wù)7通過數(shù)據(jù)篩選查詢?nèi)肼殨r間最早的5名員工信息 任務(wù)8通過數(shù)據(jù)篩選查詢2005年-2007年入職的員工 任務(wù)9統(tǒng)計公司各部門員工人數(shù)〔1〕將Sheet2工作表重命名為“公司各部門員工人數(shù)〞?！?〕建立表格框架?！?〕選中“公司各部門員工人數(shù)〞工作表的B3單元格。〔4〕輸入公式“=COUNTIF(員工檔案信息!$D$2:$D$26,"行政部")〞，按【Enter】鍵，在B3中顯示“行政部〞人數(shù)?！?〕同樣地，統(tǒng)計出其他部門的人數(shù)。 任務(wù)10創(chuàng)立公司各部門人員比例圖〔1〕選中“公司各部門員工人數(shù)〞工作表中的A2:B9單元格區(qū)域?！?〕創(chuàng)立“三位餅圖〞?！?〕設(shè)置圖表的標(biāo)題格式。 2.1.5工程小結(jié) 本工程通過制作“員工人事檔案管理表〞，主要介紹了工作簿的創(chuàng)立、工作表重命名、導(dǎo)入外部數(shù)據(jù)創(chuàng)立員工檔案信息表、利用超鏈接查閱員工個人詳細(xì)資料、使用篩選查詢員工檔案信息、使用COUNTIF函數(shù)進(jìn)行各部門人數(shù)統(tǒng)計。 在此根底上，利用統(tǒng)計數(shù)據(jù)創(chuàng)立各部門人員比例圖表。 2.1.6拓展工程 1.通過數(shù)據(jù)篩選查詢80后的員工檔案信息。 2.通過數(shù)據(jù)篩選查詢“市場部〞的男員工檔案信息。 3.統(tǒng)計公司員工學(xué)歷情況并制作比例圖。2.2工程5制作員工培訓(xùn)管理表 2.2.1工程背景 企業(yè)在對員工進(jìn)行培訓(xùn)后，通常要根據(jù)培訓(xùn)工程進(jìn)行相應(yīng)的考核。 人力資源部需要針對考核成績制定相應(yīng)的培訓(xùn)成績管理表。 本工程通過制作“員工培訓(xùn)管理表〞，來介紹Excel軟件在培訓(xùn)管理方面的應(yīng)用。 2.2.2工程效果圖2-3員工培訓(xùn)管理表圖2-4員工培訓(xùn)考核數(shù)據(jù)透視圖 2.2.3知識與技能 ●工作簿的創(chuàng)立 ●工作表重命名 ●數(shù)據(jù)有效性 ●函數(shù)AVERAGE、SUM、COUNTIF和RANK的應(yīng)用 ●美化工作表 ●數(shù)據(jù)透視表 ●數(shù)據(jù)透視圖 2.2.4解決方案 任務(wù)1創(chuàng)立工作簿，重命名工作表〔1〕啟動Excel2003，新建一個空白工作簿?！?〕將創(chuàng)立的工作薄以“員工培訓(xùn)管理表〞為名保存?！?〕將“員工人事檔案管理表〞工作簿中的Sheet1工作表重命名為“員工培訓(xùn)成績表〞。 任務(wù)2創(chuàng)立“員工培訓(xùn)成績表〞框架〔1〕在“員工培訓(xùn)成績表〞中輸入工作表標(biāo)題。〔2〕輸入表格標(biāo)題字段。 任務(wù)3輸入“序號〞 利用填充句柄，輸入序號。 任務(wù)4輸入“姓名〞和“部門〞〔1〕輸入員工“姓名〞?！?〕輸入員工“部門〞。 任務(wù)5設(shè)置各培訓(xùn)工程成績的數(shù)據(jù)有效性 在輸入數(shù)據(jù)的過程中，為盡量減小數(shù)據(jù)輸入的錯誤，提高數(shù)據(jù)的有效性，可對要輸入數(shù)據(jù)的單元格區(qū)域設(shè)置有效性規(guī)那么，使輸入的數(shù)據(jù)在規(guī)定的范圍之內(nèi)。 即輸入的成績范圍在0～100之間。 任務(wù)6統(tǒng)計“平均成績〞和總成績 任務(wù)7利用RANK函數(shù)排列員工培訓(xùn)成績的名次〔1〕選中I3單元格?！?〕輸入公式“=RANK(H3,$H$3:$H$21)〞按【Enter】鍵確認(rèn)?！?〕選中I3單元格，拖動填充句柄至I21單元格，將公式復(fù)制到I4:H21單元格區(qū)域中，排列出所有名次。 任務(wù)8統(tǒng)計各培訓(xùn)工程的平均成績〔1〕在A22單元格中輸入“各培訓(xùn)工程平均成績〞?！?〕選中D22單元格，輸入公式“=AVERAGE(D3:D21)〞，按【Enter】鍵確認(rèn)?！?〕選中D22單元格，拖動填充句柄至F22單元格，將公式復(fù)制到E22:F22單元格區(qū)域中。 任務(wù)9利用COUNTIF函數(shù)統(tǒng)計考核不合格的員工人數(shù) 這里，假定員工的所有工程平均成績低于80分，那么認(rèn)定為不合格?！?〕在G22單元格中輸入“不合格人數(shù)〞?！?〕選中I22單元格，輸入公式“=COUNTIF(G3:G21,"<80")〞。〔3〕按【Enter】鍵確認(rèn)。 任務(wù)10設(shè)置單元格數(shù)據(jù)格式 將“平均成績〞和“各培訓(xùn)工程平均成績〞的數(shù)據(jù)保存為1位小數(shù)。 任務(wù)11美化員工培訓(xùn)成績表〔1〕設(shè)置工作表標(biāo)題格式?！?〕合并單元格?！?〕將表格中所有數(shù)據(jù)區(qū)域的內(nèi)容設(shè)置為居中對齊。〔4〕適當(dāng)調(diào)整表格的行高和列寬，能顯示表格內(nèi)容?！?〕為表格設(shè)置邊框。 任務(wù)12制作員工培訓(xùn)考核數(shù)據(jù)透視圖〔1〕選中“員工培訓(xùn)成績表〞?！?〕選擇【數(shù)據(jù)】菜單中的【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】命令，翻開【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)υ捒?，按提示進(jìn)行操作?！?〕將“姓名〞作為“行〞字段，將“計算機技能〞作為“數(shù)據(jù)〞項?！?〕選擇【匯總方式】為“最大值〞?！?〕單擊【完成】按鈕，系統(tǒng)將在工作簿中插入一張工作表Sheet4和一張工作表Chart1。在Sheet4中生成數(shù)據(jù)透視表，將Sheet4工作表重命名為“員工培訓(xùn)成績數(shù)據(jù)透視表〞，在Chart1中生成數(shù)據(jù)透視圖，將Chart1工作命名為“員工培訓(xùn)考核數(shù)據(jù)透視圖〞。 2.2.5工程小結(jié) 本工程通過制作“員工培訓(xùn)管理表〞，主要介紹了工作簿的創(chuàng)立、工作表重命名、數(shù)據(jù)有效性設(shè)置、函數(shù)AVERAGE、SUM、COUNTIF和RANK的應(yīng)用、美化工作表等。 在此根底上，利用培訓(xùn)成績表中的數(shù)據(jù)，通過數(shù)據(jù)透視表和數(shù)據(jù)透視圖對員工培訓(xùn)成績進(jìn)行匯總、分析，使表格中的數(shù)據(jù)更為直觀、生動。 2.2.6拓展工程 1.按培訓(xùn)成績名次進(jìn)行升序排列。 2.制作各培訓(xùn)工程平均成績比較圖。 3.制作公司各部門培訓(xùn)成績數(shù)據(jù)透視圖。2.3工程6制作員工工資管理表

2.3.1工程背景 制作工資表示通常需要綜合大量的數(shù)據(jù)，如根本工資、績效工資、補貼、扣款項等。 如果用手工做賬，不僅工作量非常大，而且還往往容易出錯。 本工程通過制作“員工工資管理表〞來介紹Excel軟件在工資管理方面的應(yīng)用。 2.3.2工程效果圖2-5員工工資明細(xì)表效果圖圖2-6員工工資條效果圖 2.3.3知識與技能 ●工作簿的創(chuàng)立 ●工作表重命名 ●導(dǎo)入外部數(shù)據(jù) ●函數(shù)Year、Round、Vlookup、IF、INDEX、ROW、COLUMN、INT的使用 ●公式的使用 ●數(shù)據(jù)透視表 ●數(shù)據(jù)透視圖 2.3.4解決方案 任務(wù)1創(chuàng)立工作簿，重命名工作表〔1〕啟動Excel2003，新建一個空白工作簿?！?〕將創(chuàng)立的工作薄以“員工工資管理表〞為名保存?！?〕將“員工人事檔案管理表〞工作簿中的Sheet1工作表重命名為“工資根底信息表〞。 任務(wù)2導(dǎo)入“員工信息〞 將行政篇中導(dǎo)出的“員工信息〞數(shù)據(jù)導(dǎo)入到當(dāng)前工作表中，作為員工“工資根底信息表〞的數(shù)據(jù)。 任務(wù)3創(chuàng)立“工資根底信息表〞〔1〕選中“工資根底信息表〞工作表?！?〕刪除“身份證號碼〞、“學(xué)歷〞、“職稱〞、“性別〞和“出生日期〞列的數(shù)據(jù)。圖2-60刪除數(shù)據(jù)后的工作表〔3〕分別在E1、F1、G1單元格中輸入標(biāo)題字段名稱“根本工資〞、“績效工資〞和“工齡工資〞?！?〕輸入“根本工資數(shù)據(jù)〞?！?〕計算“績效工資〞。這里，績效工資=根本工資*50%。〔6〕計算“工齡工資〞。 這里，如果“工齡〞超過10年，那么工齡工資為500元，否那么，按每年50元計算。 任務(wù)4創(chuàng)立“加班費結(jié)算表〞〔1〕復(fù)制“工資根底信息表〞，將復(fù)制后的工作表重命名為“加班費結(jié)算表〞。〔2〕刪除“入職時間〞、“績效工資〞和“工齡工資〞列?！?〕在E1、F1單元格中分別輸入標(biāo)題“加班時間〞和“加班費〞?！?〕輸入加班時間?！?〕計算機加班費。 加班費的計算方法為：加班費=根本工資/30/8*1.5*加班時間 任務(wù)5創(chuàng)立“考勤扣款結(jié)算表〞〔1〕復(fù)制“工資根底信息表〞，將復(fù)制后的工作表重命名為“考勤扣款結(jié)算表〞?！?〕刪除“入職時間〞、“績效工資〞和“工齡工資〞列?！?〕在E1:K1單元格中分別輸入標(biāo)題“遲到〞、“遲到扣款〞、“病假〞、“病假扣款〞、“事假〞、“事假扣款〞和“扣款合計〞。〔4〕輸入“遲到〞、“病假〞、“事假〞的數(shù)據(jù)?！?〕計算“遲到扣款〞。 這里，假設(shè)每遲到一次扣款為50元?！?〕計算“病假扣款〞。 這里，假設(shè)每病假一天扣款為當(dāng)日工資收入的50%，即“病假扣款=根本工資/30*0.5*病假天數(shù)〞?！?〕計算“事假扣款〞。 這里，假設(shè)每事假一天扣款為當(dāng)日工資收入，即“事假扣款=根本工資/30*事假天數(shù)〞?！?〕計算“扣款合計〞。 任務(wù)6創(chuàng)立“員工工資明細(xì)表〞〔1〕將Sheet2工作表重命名為“員工工資明細(xì)表〞。〔2〕參創(chuàng)立員工工資明細(xì)表的框架。〔3〕填充“編號〞、“姓名〞和“部門〞數(shù)據(jù)?！?〕導(dǎo)入“根本工資〞、“績效工資〞、“工齡工資〞和“加班費〞數(shù)據(jù)。 ①選中D3單元格。 ②選擇【插入】菜單中的【函數(shù)】命令，從函數(shù)列表中選擇“VLOOKUP〞函數(shù)，翻開【函數(shù)參數(shù)】對話框，設(shè)置參數(shù)。 ③單擊【確定】按鈕，導(dǎo)入相應(yīng)的“根本工資〞數(shù)據(jù)。 ④選中D3單元格，用鼠標(biāo)拖動其填充句柄至D26單元格，將公式復(fù)制到D3:D26單元格區(qū)域中，可導(dǎo)入所有員工的根本工資?！?〕同樣地方式，分別導(dǎo)入“績效工資〞、“工齡工資〞和“加班費〞數(shù)據(jù)?！?〕導(dǎo)入“加班費〞數(shù)據(jù)?！?〕計算“應(yīng)發(fā)工資〞?！?〕填充“養(yǎng)老保險〞數(shù)據(jù)。 這里，養(yǎng)老保險的數(shù)據(jù)為個人繳納局部，計算方法為：養(yǎng)老保險=〔根本工資+績效工資〕*8%〔9〕填充“醫(yī)療保險〞數(shù)據(jù)。 這里，醫(yī)療保險的數(shù)據(jù)為個人繳納局部，計算方法為：醫(yī)療保險=〔根本工資+績效工資〕*2%〔10〕填充“失業(yè)保險〞數(shù)據(jù)。 這里，失業(yè)保險的數(shù)據(jù)為個人繳納局部，計算方法為：失業(yè)保險=〔根本工資+績效工資〕*1%〔11〕導(dǎo)入“考勤扣款〞數(shù)據(jù)?！?2〕計算“稅前工資〞?！?3〕計算“個人所得稅〞。 應(yīng)交個人所得稅額=應(yīng)納稅所得額*適用稅率-速算扣除數(shù)，個人所得稅稅率見表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】鍵確認(rèn),可計算出相應(yīng)的個人所得稅。 ④選中N3單元格，用鼠標(biāo)拖動其填充句柄至N26單元格，將公式復(fù)制到N3:N26單元格區(qū)域中，可計算出所有員工的個人所得稅。〔13〕計算“實發(fā)工資〞。 任務(wù)7格式化“員工工資明細(xì)表〞〔1〕設(shè)置工作表的標(biāo)題格式。〔2〕將表中所有的數(shù)據(jù)項格式設(shè)置為“會計專用〞格式，保存2位小數(shù)，貨幣符號為“無〞。〔3〕為表格設(shè)置內(nèi)細(xì)外粗的藍(lán)色邊框?！?〕為“應(yīng)發(fā)工資〞和“稅前工資〞兩列的數(shù)據(jù)區(qū)域添加“淡藍(lán)〞色底紋，為“實發(fā)工資〞列數(shù)據(jù)區(qū)域添加“天藍(lán)〞色底紋。