EXCEL教程 人力資源篇

項目項目4 4 制作員工人事檔案管理表制作員工人事檔案管理表2.12.1項目項目5 5 制作員工培訓(xùn)管理表制作員工培訓(xùn)管理表2.22.2項目項目6 6 制作員工工資管理表制作員工工資管理表2.32.32.1.1 項目背景項目背景員工人事檔案的整理分類、匯總、分員工人事檔案的整理分類、匯總、分析、查詢等是一件比較復(fù)雜的工作。析、查詢等是一件比較復(fù)雜的工作。使用使用Excel可以實現(xiàn)人事管理的自動化，可以實現(xiàn)人事管理的自動化，提高人事檔案管理的條理化。提高人事檔案管理的條理化。本項目通過制作本項目通過制作“員工人事檔案管理員工人事檔案管理表表”，來介紹，來介紹Excel軟件在人力資源管理中軟件在人力資源管理中的應(yīng)用。的應(yīng)用。2.1.2 項目效果項目效果圖圖2-1 員工人事檔案篩選效果圖員工人事檔案篩選效果圖圖圖2-2 公司各部門人員分配效果圖公司各部門人員分配效果圖2.1.3 知識與技能知識與技能工作簿的創(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）啟動）啟動Excel 2003，新建一個空白，新建一個空白工作簿。工作簿。（2）將創(chuàng)建的工作薄以）將創(chuàng)建的工作薄以“員工人事員工人事檔案管理表檔案管理表”為名保存。為名保存。（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ù)】命令的子命令【導(dǎo)入數(shù)據(jù)】，打開【選取數(shù)據(jù)源】對話框。打開【選取數(shù)據(jù)源】對話框。（3）選擇數(shù)據(jù)源文件）選擇數(shù)據(jù)源文件“員工信員工信息息.prn”，（4）從對話框的）從對話框的“原始數(shù)據(jù)類型原始數(shù)據(jù)類型”中選擇中選擇“固定長度固定長度”作為最合適的文作為最合適的文件類型，在件類型，在“導(dǎo)入起始行導(dǎo)入起始行”文本框中文本框中保持默認(rèn)值保持默認(rèn)值“1”，在文件原始格式，在文件原始格式下拉列表中選擇下拉列表中選擇“936：簡體中文：簡體中文（GB2312）”。（5）按文本導(dǎo)入向?qū)нM行操作。在）按文本導(dǎo)入向?qū)нM行操作。在“數(shù)據(jù)預(yù)覽數(shù)據(jù)預(yù)覽”區(qū)內(nèi)設(shè)置字段寬度，也區(qū)內(nèi)設(shè)置字段寬度，也就是列間隔；設(shè)置每列的數(shù)據(jù)類型。就是列間隔；設(shè)置每列的數(shù)據(jù)類型。（6）單擊【確定】按鈕，完成數(shù)據(jù)）單擊【確定】按鈕，完成數(shù)據(jù)導(dǎo)入。導(dǎo)入。任務(wù)任務(wù)3 創(chuàng)建創(chuàng)建“員工個人詳細(xì)資料員工個人詳細(xì)資料”鏈鏈接接（1）鏈接到已有文件。）鏈接到已有文件。（2）鏈接到新建文件。）鏈接到新建文件。任務(wù)任務(wù)4 查閱查閱“員工個人詳細(xì)資料員工個人詳細(xì)資料”當(dāng)需要查閱某個員工的個人詳細(xì)資料當(dāng)需要查閱某個員工的個人詳細(xì)資料時，我們可通過激活超鏈接進行訪問。時，我們可通過激活超鏈接進行訪問。任務(wù)任務(wù)5 復(fù)制復(fù)制“員工檔案信息員工檔案信息”工作表工作表（1）選中）選中“員工檔案信息員工檔案信息”工作表。工作表。（2）將該工作表復(fù)制三份，并分別）將該工作表復(fù)制三份，并分別重命名為重命名為“女員工檔案信息女員工檔案信息”、“入入職時間最早的職時間最早的5名員工信息名員工信息”、“2005年年-2007年入職的員工年入職的員工”。任務(wù)任務(wù)6 通過數(shù)據(jù)篩選查詢女員工檔案通過數(shù)據(jù)篩選查詢女員工檔案信息信息任務(wù)任務(wù)7 通過數(shù)據(jù)篩選查詢?nèi)肼殨r間最通過數(shù)據(jù)篩選查詢?nèi)肼殨r間最早的早的5名員工信息名員工信息任務(wù)任務(wù)8 通過數(shù)據(jù)篩選查詢通過數(shù)據(jù)篩選查詢2005年年-2007年入職的員工年入職的員工任務(wù)任務(wù)9 統(tǒng)計公司各部門員工人數(shù)統(tǒng)計公司各部門員工人數(shù)（1）將）將Sheet2工作表重命名為工作表重命名為“公公司各部門員工人數(shù)司各部門員工人數(shù)”。（2）建立表格框架。）建立表格框架。（3）選中）選中“公司各部門員工人數(shù)公司各部門員工人數(shù)”工作表的工作表的B3單元格。單元格。（4）輸入公式）輸入公式“=COUNTIF(員工檔員工檔案信息案信息!$D$2:$D$26,行政部行政部)”，按【按【Enter】鍵，在】鍵，在B3中顯示中顯示“行政行政部部”人數(shù)。人數(shù)。（5）同樣地，統(tǒng)計出其他部門的人）同樣地，統(tǒng)計出其他部門的人數(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è)置圖表的標(biāo)題格式。2.1.5 項目小結(jié)項目小結(jié)本項目通過制作本項目通過制作“員工人事檔案管理員工人事檔案管理表表”，主要介紹了工作簿的創(chuàng)建、工作表，主要介紹了工作簿的創(chuàng)建、工作表重命名、導(dǎo)入外部數(shù)據(jù)創(chuàng)建員工檔案信息重命名、導(dǎo)入外部數(shù)據(jù)創(chuàng)建員工檔案信息表、利用超鏈接查閱員工個人詳細(xì)資料、表、利用超鏈接查閱員工個人詳細(xì)資料、使用篩選查詢員工檔案信息、使用使用篩選查詢員工檔案信息、使用COUNTIF函數(shù)進行各部門人數(shù)統(tǒng)計。函數(shù)進行各部門人數(shù)統(tǒng)計。在此基礎(chǔ)上，利用統(tǒng)計數(shù)據(jù)創(chuàng)建各部在此基礎(chǔ)上，利用統(tǒng)計數(shù)據(jù)創(chuàng)建各部門人員比例圖表。門人員比例圖表。2.1.6 拓展項目拓展項目1.1.通過數(shù)據(jù)篩選查詢通過數(shù)據(jù)篩選查詢8080后的員工后的員工檔案信息。檔案信息。2.2.通過數(shù)據(jù)篩選查詢通過數(shù)據(jù)篩選查詢“市場部市場部”的男員工檔案信息。的男員工檔案信息。3.3.統(tǒng)計公司員工學(xué)歷情況并制統(tǒng)計公司員工學(xué)歷情況并制作比例圖。作比例圖。2.2.1 項目背景項目背景企業(yè)在對員工進行培訓(xùn)后，通常要根企業(yè)在對員工進行培訓(xùn)后，通常要根據(jù)培訓(xùn)項目進行相應(yīng)的考核。據(jù)培訓(xùn)項目進行相應(yīng)的考核。人力資源部需要針對考核成績制定相人力資源部需要針對考核成績制定相應(yīng)的培訓(xùn)成績管理表。應(yīng)的培訓(xùn)成績管理表。本項目通過制作本項目通過制作“員工培訓(xùn)管理表員工培訓(xùn)管理表”，來介紹來介紹Excel軟件在培訓(xùn)管理方面的應(yīng)用。軟件在培訓(xùn)管理方面的應(yīng)用。2.2.2 項目效果項目效果圖圖2-3 員工培訓(xùn)管理表員工培訓(xùn)管理表圖圖2-4 員工培訓(xùn)考核數(shù)據(jù)透視圖員工培訓(xùn)考核數(shù)據(jù)透視圖2.2.3 知識與技能知識與技能工作簿的創(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）啟動）啟動Excel 2003，新建一個空白，新建一個空白工作簿。工作簿。（2）將創(chuàng)建的工作薄以）將創(chuàng)建的工作薄以“員工培訓(xùn)員工培訓(xùn)管理表管理表”為名保存。為名保存。（3）將）將“員工人事檔案管理表員工人事檔案管理表”工工作簿中的作簿中的Sheet1工作表重命名為工作表重命名為“員員工培訓(xùn)成績表工培訓(xùn)成績表”。任務(wù)任務(wù)2 創(chuàng)建創(chuàng)建“員工培訓(xùn)成績表員工培訓(xùn)成績表”框架框架（1）在）在“員工培訓(xùn)成績表員工培訓(xùn)成績表”中輸入中輸入工作表標(biāo)題。工作表標(biāo)題。（2）輸入表格標(biāo)題字段。）輸入表格標(biāo)題字段。任務(wù)任務(wù)3 輸入輸入“序號序號”利用填充句柄，輸入序號。利用填充句柄，輸入序號。任務(wù)任務(wù)4 輸入輸入“姓名姓名”和和“部門部門”（1）輸入員工）輸入員工“姓名姓名”。（2）輸入員工）輸入員工“部門部門”。任務(wù)任務(wù)5 設(shè)置各培訓(xùn)項目成績的數(shù)據(jù)有設(shè)置各培訓(xùn)項目成績的數(shù)據(jù)有效性效性在輸入數(shù)據(jù)的過程中，為盡量減小數(shù)在輸入數(shù)據(jù)的過程中，為盡量減小數(shù)據(jù)輸入的錯誤，提高數(shù)據(jù)的有效性，可對據(jù)輸入的錯誤，提高數(shù)據(jù)的有效性，可對要輸入數(shù)據(jù)的單元格區(qū)域設(shè)置有效性規(guī)則，要輸入數(shù)據(jù)的單元格區(qū)域設(shè)置有效性規(guī)則，使輸入的數(shù)據(jù)在規(guī)定的范圍之內(nèi)。使輸入的數(shù)據(jù)在規(guī)定的范圍之內(nèi)。即輸入的成績范圍在即輸入的成績范圍在0100之間。之間。任務(wù)任務(wù)6 統(tǒng)計統(tǒng)計“平均成績平均成績”和總成績和總成績?nèi)蝿?wù)任務(wù)7 利用利用RANK函數(shù)排列員工培訓(xùn)函數(shù)排列員工培訓(xùn)成績的名次成績的名次（1）選中）選中I3單元格。單元格。（2）輸入公式）輸入公式“=RANK(H3,$H$3:$H$21)”按按【Enter】鍵確認(rèn)?！挎I確認(rèn)。（3）選中）選中I3單元格，拖動填充句柄單元格，拖動填充句柄至至I21單元格，將公式復(fù)制到單元格，將公式復(fù)制到I4:H21單元格區(qū)域中，排列出所有名次。單元格區(qū)域中，排列出所有名次。任務(wù)任務(wù)8 統(tǒng)計各培訓(xùn)項目的平均成績統(tǒng)計各培訓(xùn)項目的平均成績（1）在）在A22單元格中輸入單元格中輸入“各培訓(xùn)各培訓(xùn)項目平均成績項目平均成績”。（2）選中）選中D22單元格，輸入公式單元格，輸入公式“=AVERAGE(D3:D21)”，按，按【Enter】鍵確認(rèn)?！挎I確認(rèn)。（5）選中）選中D22單元格，拖動填充句單元格，拖動填充句柄至柄至F22單元格，將公式復(fù)制到單元格，將公式復(fù)制到E22:F22單元格區(qū)域中。單元格區(qū)域中。任務(wù)任務(wù)9 利用利用COUNTIF函數(shù)統(tǒng)計考核函數(shù)統(tǒng)計考核不合格的員工人數(shù)不合格的員工人數(shù)這里，假定員工的所有項目平均成績這里，假定員工的所有項目平均成績低于低于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),可計算出相應(yīng)可計算出相應(yīng)的個人所得稅。的個人所得稅。選中選中N3單元格，用鼠標(biāo)拖動其填充單元格，用鼠標(biāo)拖動其填充句柄至句柄至N26單元格，將公式復(fù)制到單元格，將公式復(fù)制到N3:N26單元格區(qū)域中，可計算出所有員工的個人單元格區(qū)域中，可計算出所有員工的個人所得稅。所得稅。（13）計算）計算“實發(fā)工資實發(fā)工資”。任務(wù)任務(wù)7 格式化格式化“員工工資明細(xì)表員工工資明細(xì)表”（1）設(shè)置工作表的標(biāo)題格式。）設(shè)置工作表的標(biāo)題格式。（2）將表中所有的數(shù)據(jù)項格式設(shè)置）將表中所有的數(shù)據(jù)項格式設(shè)置為為“會計專用會計專用”格式，保留格式，保留2位小數(shù)，位小數(shù)，貨幣符號為貨幣符號為“無無”。（3）為表格設(shè)置內(nèi)細(xì)外粗的藍色邊）為表格設(shè)置內(nèi)細(xì)外粗的藍色邊框?？颉＃?）為）為“應(yīng)發(fā)工資應(yīng)發(fā)工資”和和“稅前工資稅前工資”兩列的數(shù)據(jù)區(qū)域添加兩列的數(shù)據(jù)區(qū)域添加“淡藍淡藍”色底紋，色底紋，為為“實發(fā)工資實發(fā)工資”列數(shù)據(jù)區(qū)域添加列數(shù)據(jù)區(qū)域添加“天天藍藍”色底紋。色底紋。任務(wù)任務(wù)8 制作制作“工資條工資條”（1）將）將Sheet3工作表重命名為工作表重命名為“工工資條資條”。（2）選中）選中“工資條工資條”工作表的工作表的A1單單元格。元格。（3）在）在“編輯欄編輯欄”中輸入下面的公中輸入下面的公式，然后按【式，然后按【Enter】鍵確認(rèn)。】鍵確認(rèn)。=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,員工工資明細(xì)員工工資明細(xì)表表!A$2,INDEX(員工工資明細(xì)員工工資明細(xì)表表!$A:$O,INT(ROW()-1)/3)+3,COLUMN()（4）選中）選中A1單元格，用鼠標(biāo)拖動其單元格，用鼠標(biāo)拖動其填充句柄至填充句柄至O1單元格，將公式復(fù)制單元格，將公式復(fù)制到到B1:O1單元格區(qū)域中。單元格區(qū)域中。（5）選中）選中A1:O1單元格區(qū)域，用鼠單元格區(qū)域，用鼠標(biāo)拖動其填充句柄至標(biāo)拖動其填充句柄至O74單元格，將單元格，將公式復(fù)制到公式復(fù)制到A2:O74單元格區(qū)域中。單元格區(qū)域中。（6）適當(dāng)調(diào)整單元格的列寬，使單）適當(dāng)調(diào)整單元格的列寬，使單元格的內(nèi)容能完整的顯示出來。元格的內(nèi)容能完整的顯示出來。（7）美化）美化“工資條工資條”工作表。工作表。2.3.5 項目小結(jié)項目小結(jié)本項目通過制作本項目通過制作“員工工資管理表員工工資管理表”，主要介紹了工作簿的創(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ǔ)信息表信息表”、“加班費結(jié)算表加班費結(jié)算表”和和“考勤扣考勤扣款結(jié)算表款結(jié)算表”。在此基礎(chǔ)上，使用在此基礎(chǔ)上，使用Vlookup、IF等函等函數(shù)以及公式創(chuàng)建出數(shù)以及公式創(chuàng)建出“員工工資明細(xì)表員工工資明細(xì)表”。此外使用此外使用INDEX、ROW、COLUMN、INT等函數(shù)制作出等函數(shù)制作出“工資條工資條”，實現(xiàn)了對員工工資的輕松、高效管理。實現(xiàn)了對員工工資的輕松、高效管理。2.3.6 拓展項目拓展項目1.制作各部門工資匯總表。制作各部門工資匯總表。2.制作各部門平均工資收入比較制作各部門平均工資收入比較圖。圖。演講完畢，謝謝觀看！