人力資源篇2024/5/12人力資源篇項目4制作員工人事檔案管理表2.1項目5制作員工培訓(xùn)管理表2.2項目6制作員工工資管理表2.3人力資源篇2.1項目4制作員工人事檔案管理表 2.1.1項目背景 員工人事檔案的整理分類、匯總、分析、查詢等是一件比較復(fù)雜的工作。 使用Excel可以實現(xiàn)人事管理的自動化，提高人事檔案管理的條理化。人力資源篇人力資源篇 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，新建一個空白工作簿。（2）將創(chuàng)建的工作薄以“員工人事檔案管理表”為名保存。（3）將“員工人事檔案管理表”工作簿中的Sheet1工作表重命名為“員工檔案信息”。人力資源篇 任務(wù)2導(dǎo)入“員工信息” 將行政篇中導(dǎo)出的“員工信息”數(shù)據(jù)導(dǎo)入到當(dāng)前的文件中，輕松獲取外部人事檔案信息。人力資源篇（1）選中“員工人事檔案管理表”工作簿中的“員工檔案信息”工作表。（2）選擇【數(shù)據(jù)】菜單中【導(dǎo)入外部數(shù)據(jù)】命令的子命令【導(dǎo)入數(shù)據(jù)】，打開【選取數(shù)據(jù)源】對話框。人力資源篇（3）選擇數(shù)據(jù)源文件“員工信息.prn”，（4）從對話框的“原始數(shù)據(jù)類型”中選擇“固定長度”作為最合適的文件類型，在“導(dǎo)入起始行”文本框中保持默認(rèn)值“1”，在文件原始格式下拉列表中選擇“936：簡體中文（GB2312）”。人力資源篇（5）按文本導(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）鏈接到已有文件。（2）鏈接到新建文件。人力資源篇 任務(wù)4查閱“員工個人詳細(xì)資料” 當(dāng)需要查閱某個員工的個人詳細(xì)資料時，我們可通過激活超鏈接進(jìn)行訪問。人力資源篇 任務(wù)5復(fù)制“員工檔案信息”工作表（1）選中“員工檔案信息”工作表。（2）將該工作表復(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ù)”。（2）建立表格框架。（3）選中“公司各部門員工人數(shù)”工作表的B3單元格。人力資源篇（4）輸入公式“=COUNTIF(員工檔案信息!$D$2:$D$26,"行政部")”，按【Enter】鍵，在B3中顯示“行政部”人數(shù)。（5）同樣地，統(tǒng)計出其他部門的人數(shù)。人力資源篇 任務(wù)10創(chuàng)建公司各部門人員比例圖（1）選中“公司各部門員工人數(shù)”工作表中的A2:B9單元格區(qū)域。（2）創(chuàng)建“三位餅圖”。（3）設(shè)置圖表的標(biāo)題格式。人力資源篇 2.1.5項目小結(jié) 本項目通過制作“員工人事檔案管理表”，主要介紹了工作簿的創(chuàng)建、工作表重命名、導(dǎo)入外部數(shù)據(jù)創(chuàng)建員工檔案信息表、利用超鏈接查閱員工個人詳細(xì)資料、使用篩選查詢員工檔案信息、使用COUNTIF函數(shù)進(jìn)行各部門人數(shù)統(tǒng)計。 在此基礎(chǔ)上，利用統(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，新建一個空白工作簿。（2）將創(chuàng)建的工作薄以“員工培訓(xùn)管理表”為名保存。（3）將“員工人事檔案管理表”工作簿中的Sheet1工作表重命名為“員工培訓(xùn)成績表”。人力資源篇 任務(wù)2創(chuàng)建“員工培訓(xùn)成績表”框架（1）在“員工培訓(xùn)成績表”中輸入工作表標(biāo)題。（2）輸入表格標(biāo)題字段。人力資源篇 任務(wù)3輸入“序號” 利用填充句柄，輸入序號。 任務(wù)4輸入“姓名”和“部門”（1）輸入員工“姓名”。（2）輸入員工“部門”。人力資源篇 任務(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單元格。人力資源篇（2）輸入公式“=RANK(H3,$H$3:$H$21)”按【Enter】鍵確認(rèn)。（3）選中I3單元格，拖動填充句柄至I21單元格，將公式復(fù)制到I4:H21單元格區(qū)域中，排列出所有名次。人力資源篇 任務(wù)8統(tǒng)計各培訓(xùn)項目的平均成績（1）在A22單元格中輸入“各培訓(xùn)項目平均成績”。（2）選中D22單元格，輸入公式“=AVERAGE(D3:D21)”，按【Enter】鍵確認(rèn)。人力資源篇（5）選中D22單元格，拖動填充句柄至F22單元格，將公式復(fù)制到E22:F22單元格區(qū)域中。人力資源篇 任務(wù)9利用COUNTIF函數(shù)統(tǒng)計考核不合格的員工人數(shù) 這里，假定員工的所有項目平均成績低于80分，則認(rèn)定為不合格。人力資源篇（1）在G22單元格中輸入“不合格人數(shù)”。（2）選中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)題格式。（2）合并單元格。（3）將表格中所有數(shù)據(jù)區(qū)域的內(nèi)容設(shè)置為居中對齊。人力資源篇（4）適當(dāng)調(diào)整表格的行高和列寬，能顯示表格內(nèi)容。（5）為表格設(shè)置邊框。人力資源篇 任務(wù)12制作員工培訓(xùn)考核數(shù)據(jù)透視圖（1）選中“員工培訓(xùn)成績表”。（2）選擇【數(shù)據(jù)】菜單中的【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】命令，打開【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)υ捒颍刺崾具M(jìn)行操作。人力資源篇（3）將“姓名”作為“行”字段，將“計算機技能”作為“數(shù)據(jù)”項。（4）選擇【匯總方式】為“最大值”。人力資源篇（5）單擊【完成】按鈕，系統(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)用、美化工作表等。 在此基礎(chǔ)上，利用培訓(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，新建一個空白工作簿。（2）將創(chuàng)建的工作薄以“員工工資管理表”為名保存。（3）將“員工人事檔案管理表”工作簿中的Sheet1工作表重命名為“工資基礎(chǔ)信息表”。人力資源篇 任務(wù)2導(dǎo)入“員工信息” 將行政篇中導(dǎo)出的“員工信息”數(shù)據(jù)導(dǎo)入到當(dāng)前工作表中，作為員工“工資基礎(chǔ)信息表”的數(shù)據(jù)。人力資源篇 任務(wù)3創(chuàng)建“工資基礎(chǔ)信息表”（1）選中“工資基礎(chǔ)信息表”工作表。（2）刪除“身份證號碼”、“學(xué)歷”、“職稱”、“性別”和“出生日期”列的數(shù)據(jù)。人力資源篇圖2-60刪除數(shù)據(jù)后的工作表人力資源篇（3）分別在E1、F1、G1單元格中輸入標(biāo)題字段名稱“基本工資”、“績效工資”和“工齡工資”。（4）輸入“基本工資數(shù)據(jù)”。人力資源篇（5）計算“績效工資”。這里，績效工資=基本工資*50%。（6）計算“工齡工資”。 這里，如果“工齡”超過10年，則工齡工資為500元，否則，按每年50元計算。人力資源篇 任務(wù)4創(chuàng)建“加班費結(jié)算表”（1）復(fù)制“工資基礎(chǔ)信息表”，將復(fù)制后的工作表重命名為“加班費結(jié)算表”。（2）刪除“入職時間”、“績效工資”和“工齡工資”列。（3）在E1、F1單元格中分別輸入標(biāo)題“加班時間”和“加班費”。人力資源篇（4）輸入加班時間。（5）計算機加班費。 加班費的計算方法為：加班費=基本工資/30/8*1.5*加班時間人力資源篇 任務(wù)5創(chuàng)建“考勤扣款結(jié)算表”（1）復(fù)制“工資基礎(chǔ)信息表”，將復(fù)制后的工作表重命名為“考勤扣款結(jié)算表”。（2）刪除“入職時間”、“績效工資”和“工齡工資”列。人力資源篇（3）在E1:K1單元格中分別輸入標(biāo)題“遲到”、“遲到扣款”、“病假”、“病假扣款”、“事假”、“事假扣款”和“扣款合計”。人力資源篇（4）輸入“遲到”、“病假”、“事假”的數(shù)據(jù)。（5）計算“遲到扣款”。 這里，假設(shè)每遲到一次扣款為50元。人力資源篇（6）計算“病假扣款”。 這里，假設(shè)每病假一天扣款為當(dāng)日工資收入的50%，即“病假扣款=基本工資/30*0.5*病假天數(shù)”。人力資源篇（7）計算“事假扣款”。 這里，假設(shè)每事假一天扣款為當(dāng)日工資收入，即“事假扣款=基本工資/30*事假天數(shù)”。（8）計算“扣款合計”。人力資源篇 任務(wù)6創(chuàng)建“員工工資明細(xì)表”（1）將Sheet2工作表重命名為“員工工資明細(xì)表”。（2）參創(chuàng)建員工工資明細(xì)表的框架。（3）填充“編號”、“姓名”和“部門”數(shù)據(jù)。人力資源篇（4）導(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)入所有員工的基本工資。人力資源篇（5）同樣地方式，分別導(dǎo)入“績效工資”、“工齡工資”和“加班費”數(shù)據(jù)。（6）導(dǎo)入“加班費”數(shù)據(jù)。（7）計算“應(yīng)發(fā)工資”。人力資源篇（8）填充“養(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ù)。人力資源篇（12）計算“稅前工資”。（13）計算“個人所得稅”。 應(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)色邊框。（4）為“應(yīng)發(fā)工資”和“稅前工資”兩列的數(shù)據(jù)區(qū)域添加“淡藍(lán)”色底紋，為“實發(fā)工資”列數(shù)據(jù)區(qū)域