EXCEL教程 人力資源篇

《EXCEL教程 人力資源篇》由會(huì)員分享，可在線閱讀，更多相關(guān)《EXCEL教程 人力資源篇（86頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

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

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

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

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

5、間隔；設(shè)置每列的數(shù)據(jù)類型。（6）單擊【確定】按鈕，完成數(shù)據(jù)）單擊【確定】按鈕，完成數(shù)據(jù)導(dǎo)入。導(dǎo)入。任務(wù)任務(wù)3 創(chuàng)建創(chuàng)建“員工個(gè)人詳細(xì)資料員工個(gè)人詳細(xì)資料”鏈鏈接接（1）鏈接到已有文件。）鏈接到已有文件。（2）鏈接到新建文件。）鏈接到新建文件。任務(wù)任務(wù)4 查閱查閱“員工個(gè)人詳細(xì)資料員工個(gè)人詳細(xì)資料”當(dāng)需要查閱某個(gè)員工的個(gè)人詳細(xì)資料當(dāng)需要查閱某個(gè)員工的個(gè)人詳細(xì)資料時(shí)，我們可通過(guò)激活超鏈接進(jìn)行訪問(wèn)。時(shí)，我們可通過(guò)激活超鏈接進(jìn)行訪問(wèn)。任務(wù)任務(wù)5 復(fù)制復(fù)制“員工檔案信息員工檔案信息”工作表工作表（1）選中）選中“員工檔案信息員工檔案信息”工作表。工作表。（2）將該工作表復(fù)制三份，并分別）將該工作表復(fù)制三

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

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

8、圖表的標(biāo)題格式。2.1.5 項(xiàng)目小結(jié)項(xiàng)目小結(jié)本項(xiàng)目通過(guò)制作本項(xiàng)目通過(guò)制作“員工人事檔案管理員工人事檔案管理表表”，主要介紹了工作簿的創(chuàng)建、工作表，主要介紹了工作簿的創(chuàng)建、工作表重命名、導(dǎo)入外部數(shù)據(jù)創(chuàng)建員工檔案信息重命名、導(dǎo)入外部數(shù)據(jù)創(chuàng)建員工檔案信息表、利用超鏈接查閱員工個(gè)人詳細(xì)資料、表、利用超鏈接查閱員工個(gè)人詳細(xì)資料、使用篩選查詢員工檔案信息、使用使用篩選查詢員工檔案信息、使用COUNTIF函數(shù)進(jìn)行各部門人數(shù)統(tǒng)計(jì)。函數(shù)進(jìn)行各部門人數(shù)統(tǒng)計(jì)。在此基礎(chǔ)上，利用統(tǒng)計(jì)數(shù)據(jù)創(chuàng)建各部在此基礎(chǔ)上，利用統(tǒng)計(jì)數(shù)據(jù)創(chuàng)建各部門人員比例圖表。門人員比例圖表。2.1.6 拓展項(xiàng)目拓展項(xiàng)目1.1.通過(guò)數(shù)據(jù)篩選查詢通過(guò)數(shù)據(jù)篩

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

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

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

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

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

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

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

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

17、明細(xì)員工工資明細(xì)表表!$A:$O,INT(ROW()-1)/3)+3,COLUMN()（4）選中）選中A1單元格，用鼠標(biāo)拖動(dòng)其單元格，用鼠標(biāo)拖動(dòng)其填充句柄至填充句柄至O1單元格，將公式復(fù)制單元格，將公式復(fù)制到到B1:O1單元格區(qū)域中。單元格區(qū)域中。（5）選中）選中A1:O1單元格區(qū)域，用鼠單元格區(qū)域，用鼠標(biāo)拖動(dòng)其填充句柄至標(biāo)拖動(dòng)其填充句柄至O74單元格，將單元格，將公式復(fù)制到公式復(fù)制到A2:O74單元格區(qū)域中。單元格區(qū)域中。（6）適當(dāng)調(diào)整單元格的列寬，使單）適當(dāng)調(diào)整單元格的列寬，使單元格的內(nèi)容能完整的顯示出來(lái)。元格的內(nèi)容能完整的顯示出來(lái)。（7）美化）美化“工資條工資條”工作表。工作表。2.3.

18、5 項(xiàng)目小結(jié)項(xiàng)目小結(jié)本項(xiàng)目通過(guò)制作本項(xiàng)目通過(guò)制作“員工工資管理表員工工資管理表”，主要介紹了工作簿的創(chuàng)建、工作表重命名、主要介紹了工作簿的創(chuàng)建、工作表重命名、外部數(shù)據(jù)的導(dǎo)入，使用函數(shù)外部數(shù)據(jù)的導(dǎo)入，使用函數(shù)Year、Datedif、Today、Round、Sum等構(gòu)建了等構(gòu)建了“工資基礎(chǔ)工資基礎(chǔ)信息表信息表”、“加班費(fèi)結(jié)算表加班費(fèi)結(jié)算表”和和“考勤扣考勤扣款結(jié)算表款結(jié)算表”。在此基礎(chǔ)上，使用在此基礎(chǔ)上，使用Vlookup、IF等函等函數(shù)以及公式創(chuàng)建出數(shù)以及公式創(chuàng)建出“員工工資明細(xì)表員工工資明細(xì)表”。此外使用此外使用INDEX、ROW、COLUMN、INT等函數(shù)制作出等函數(shù)制作出“工資條工資條”，實(shí)現(xiàn)了對(duì)員工工資的輕松、高效管理。實(shí)現(xiàn)了對(duì)員工工資的輕松、高效管理。2.3.6 拓展項(xiàng)目拓展項(xiàng)目1.制作各部門工資匯總表。制作各部門工資匯總表。2.制作各部門平均工資收入比較制作各部門平均工資收入比較圖。圖。演講完畢，謝謝觀看！