EXCEL教程人力資源篇

1、第2篇人力弈源篇:人民郵電出版社POSTS & TELECOM PRESS2.1項目4制作員工人事松余管理表2.1.1項目背景員工人事檔案的整理分類、匯總、分 析、查詢等是一件比較復雜的工作。使用Excel可以實現(xiàn)人事管理的自動化， 提高人事檔案管理的條理化ora本項目通過制作“員工人事檔案管理 表”,來介紹Exce嗽件在人力資源管理中 的應用。2.1.2項目效果人民郵電出版社POSTS & TELECOM PRESSI J 出生日期E 1962-11-419807-151380-7-5斬D |3人力謹源韶0市場部曙訓鄰入力熒源部410121621104626二編號Bas G）身份證號fiS

2til 620121600724348柏國力 5101-21195703138215 菖盔 2101210X5158021964-8-51960-7-241957-3-131960-5-15離級經(jīng)麗 無離級婭濟師 工程師J_KY0024 KYOO35 KY00415* KY014比專士專士專 學大碩中隕大 間倉E 廿1-l?sr a/J_KY00611 KY010 林菱51O1211St85O62O8452 財務部5211211983042984 市場諾0EF I GH IEj入IRBJ間學fl習職稱 二 性也出生日IS E2001-7-20本科 會計師男

3、 1985-6-20200L6-28 大專 無,! I 1983-4-29圖2-1員工人事檔案篩選效果圖:人民郵電出版社POSTS & TELECOM PRESS12 5統(tǒng)計表2部門人數(shù)3行政部34人力資源部35市場部76物流部37財務部48物業(yè)部29培訓部310孫司各部門人員比例12% 12%圖22公司各部門人員分配效果圖2J.3知識與技能工作簿的創(chuàng)建工作表重命名導入外部數(shù)據(jù)數(shù)據(jù)鏈接數(shù)據(jù)的篩選 COUNTIF函數(shù)的使用創(chuàng)建圖表214解決方案任務1創(chuàng)建工作簿，重命名工作表(1) 啟動Excel2003,新建一個空白 工作簿。(2) 將創(chuàng)建的工作薄以“員工人事 檔案管理表”為名保存。(3) 將“

4、員工人事檔案管理表”工 作簿中的Sheetl工作表重命名為“員 工檔案信息”。任務2導入“員工信息”將行政篇中導出的“員工信息”數(shù)據(jù) 導入到當前的文件中，輕松獲取外部人事 檔案信息。(1) 選中“員工人事檔案管理表” 工作簿中的“員工檔案信息”工作表。(2) 選擇【數(shù)據(jù)】菜單中【導入外 部數(shù)據(jù)】命令的子命令【導入數(shù)據(jù)】， 打開【選取數(shù)據(jù)源】對話框。（3）選擇數(shù)據(jù)源文件“員工信 息prn”，（4）從對話框的“原始數(shù)據(jù)類型”中選擇“固定長度”作為最合適的文 件類型，在“導入起始行”文本框中 保持默認值“1，在文件原始格式下 拉列表中選擇“936：簡體中文（GB2312） ” o人民郵電出版社POS

5、TS & TELECOM PRESS（5）按文本導入向?qū)нM行操作。在 “數(shù)據(jù)預覽”區(qū)內(nèi)設置字段寬度，也就是列間隔；設置每列的數(shù)據(jù)類型。（6）單擊【確定】按鈕，完成數(shù)據(jù) 導入。任務3創(chuàng)建“員工個人詳細資料”鏈(1) 鏈接到已有文件。(2) 鏈接到新建文件o:人民郵電出版社POSTS & TELECOM PRESSIH j I ! r+H卜卜十十十卜十十十卜十十十TTT任務4查閱“員工個人詳細資料” 當需要查閱某個員工的個人詳細資料時，我們可通過激活超鏈接進行訪問。任務5復制“員工檔案信息”工作表（1）選中“員工檔案信息”工作表。（2）將該工作表復制三份，并分別 重命名為“女員工檔案信息”、“入

6、職時間最早的5名員工信息”、“2005年2007年入職的員工”。人民郵電出版社POSTS & TELECOM PRESS任務6通過數(shù)據(jù)篩選查詢女員工檔案信息任務7通過數(shù)據(jù)篩選查詢?nèi)肼殨r間最 早的5名員工信息任務8通過數(shù)據(jù)篩選查詢2005年2007年入職的員工人民郵電出版社POSTS & TELECOM PRESST任務9統(tǒng)計公司各部門員工人數(shù)(1) 將Sheet2工作表重命名為“公 司各部門員工人數(shù)”。(2) 建立表格框架。(3) 選中“公司各部門員工人數(shù)” 工作表的B3單元格。:人民郵電出版社POSTS & TELECOM PRESS人民郵電出版社POSTS & TELECOM PRESST

7、(4) 輸入公式a=COUNTIF(員工檔 案信息!$D$2:$D$26畀行政部”)，,按Enter鍵，在B3中顯示“行政部” 人數(shù)。(5) 同樣地，統(tǒng)計出其他部門的人 數(shù)。任務10創(chuàng)建公司各部門人員比例圖(1) 選中“公司各部門員工人數(shù)” 工作表中的A2:B9單元格區(qū)域。(2) 創(chuàng)建“三位餅圖”。(3) 設置圖表的標題格式。參人民郵電出版社鄉(xiāng) N)卜II I 1()i rm 淞醞瀰215項目小結本項目通過制作“員工人事檔案管理 表”,主要介紹了工作簿的創(chuàng)建、工作表 重命名、導入外部數(shù)據(jù)創(chuàng)建員工檔案信息 表、利用超鏈接查閱員工個人詳細資料、 使用篩選查詢員工檔案信息、使用 COUNTIF函數(shù)進

8、行各部門人數(shù)統(tǒng)計。1=在此基礎上，利用統(tǒng)計數(shù)據(jù)創(chuàng)建各部 門人員比例圖表。:人民郵電出版社POSTS & TELECOM PRESS2.1.6拓展項目/通過救鴉晞選麥諭后的員工 郴案信息。z通過敎鴉韓選圭詢WWW 的男員工郴案信息。M免針公司員工曇歷精況畀制 作比神|圏。2.2項韓5制作員工塔洌管理表2.2.1項目背景企業(yè)在對員工進行培訓后，通常要根 據(jù)培訓項目進行相應的考核。 人力資源部需要針對考核成績制定相 應的培訓成績管理表。r Vlookup IF、 INDEX、ROW、COLUMN INT的使用公式的使用數(shù)據(jù)透視表數(shù)據(jù)透視圖2.3.4解決方案任務1創(chuàng)建工作簿，重命名工作表:人民郵電出版

9、社POSTS & TELECOM PRESS(1)啟動Excel 2003,新建一個空白 工作簿。(2) 將創(chuàng)建的工作薄以“員工工資管理表”為名保存。(3) 將“員工人事檔案管理表”工作簿中的Sheetl工作表重命名為“工 資基礎信息表”。任務2導入“員工信息”將行政篇中導出的“員工信息”數(shù)據(jù) 導入到當前工作表中，作為員工“工資基 礎信息表的藪據(jù)。任務3創(chuàng)建“工資基礎信息表(1) 選中“工資基礎信息表”工作O(2) 刪除“身份證號碼”、“學 歷”、“職稱”、“性別”和“出生 日期”列的數(shù)據(jù)。人民郵電出版社POSTS & TELECOM PRESS|ABCDE1編號姓名部門入職時間2KY001方

10、成建市場韶1993-7-103KY002人力資源部1981-6-284KY003市場韶1983-3-205KY004M光利行政部1980-7-156KY005錢新財務部1995-7-17KY006曾科財務削2007-7-208KY007李莫莠物流制2003-7-109KY008周蘇嘉行政韶2001-6-3010KY009黃雅玲市場韶1985-7-511KY010市場部2005-6-2812KYOU行政部1995-7-213KY012培訓韶1984-5-1014KY013財務韶1984-6-2515KY014培訓韶1980-7-516KY015物流部1993-8-117KY016市場削1993-

11、7-1018KY017爾阿物流削1986-7-2019KY018審藍人力資源部1982-7-320KY019皮桂華物業(yè)部1987-6-2921KY020培訓部1993-7-1822KY021財務部1987-6-3023KY022高並玲物業(yè)韶2001-7-1524KY023市場韶1999-4-1525KY024人力資源部1990-7-626KY025揃偉市場韶2002-8-1527圖260刪除數(shù)據(jù)后的工作表7(3) 分別在El、Fl、G1單元格中輸 入標題字段名稱“基本工資”、“績 效工資”和“工齡工資”。(4) 輸入“基本工資數(shù)據(jù)”。(5) 計算“績效工資”。這里，績效工資二基本工資*50%。

12、(6) 計算“工齡工資”。這里，如果“工齡”超過10年，則工齡工資為500元，否則，按每年50元計算。任務4創(chuàng)建“加班費結算表”(1)復制“工資基礎信息表”,將晝黔的工作表重命名為“加班費結(2) 刪除“入職時間”、“績效工 資”和“工齡工資”列。(3) 在El、F1單元格中分別輸入標 題“加班時間”和“加班費”。人民郵電出版社POSTS & TELECOM PRESSS j卜十十十TT十”卜十十(4) 輸入加班時間。(5) 計算機加班費。加班費的計算方法為：加班費二基本工資/30/8*15*加班時間任務5創(chuàng)建“考勤扣款結算表”(1)復制“工資基礎信息表”，將 復制后的工作表重命名為“考勤扣款

13、 結算表” O(2)刪除“入職時間”、“績效工資”和“工齡工資”列。倉人民郵電出版社(3)在E1:K1單元格中分別輸入標 題“遲到”、“遲到扣款”、“病 假”、“病假扣款”、“事假”、“事假扣款”和“扣款合計” o:人民郵電出版社POSTS & TELECOM PRESS(4) 輸入“遲到”、“病假”、 “事假”的數(shù)據(jù)。(5) 計算“遲到扣款”。這里，假設每遲到一次扣款為50元。（6）計算“病假扣款”。這里，假設每病假一天扣款為當日工 資收入的50%,即“病假扣款二基本工資 /30*05*病假天數(shù)”。（7）計算“事假扣款”。FA這里，假設每事假一天扣款為當日工 資收入，即“事假扣款=基本工資/

14、30*事假 天數(shù)”。（8）計算“扣款合計”。參人民郵電出版社POSTS & TELECOM PRESS任務6創(chuàng)建“員工工資明細表”(1)將Sheet2工作表重命名為“員:人民郵電出版社POSTS & TELECOM PRESS工工資明細表” O(3)填充“編號”、“姓名”和 “部門”數(shù)據(jù)。人民郵電出版社POSTS & TELECOM PRESS(4) 導入“基本工資”、“績效工 資”、“工齡工資”和“加班費” 數(shù)據(jù)。 選中D3單元格。 選擇【插入】菜單中的【函數(shù)】命 令，從函數(shù)列表中選擇“VLOOKUP”函數(shù), 打開【函數(shù)參數(shù)】對話框，設置參數(shù)。 單擊【確定】按鈕，導入相應的 “基本工資”數(shù)據(jù)

15、。 選中D3單元格，用鼠標拖動其填充 句柄至D26單元格，將公式復制到D3:D26 單元格區(qū)域中，可導入所有員工的基本工 資。(5) 同樣地方式，分別導入“績效 工資”、“工齡工資”和“加班費” 數(shù)據(jù)。(6) 導入“加班費”數(shù)據(jù)。(7) 計算“應發(fā)工資”。:人民郵電出版社POSTS & TELECOM PRESSS j卜十十十十十十十十十十十十十十十.(8) 填充“養(yǎng)老保險”數(shù)據(jù)。這里，養(yǎng)老保險的數(shù)據(jù)為個人繳納部分，計算方法為：養(yǎng)老保險二(基本工資+績效工資)*8%S j卜十十十十十十十十十十十十十十十.(9) 填充“醫(yī)療保險”數(shù)據(jù)。這里，醫(yī)療保險的數(shù)據(jù)為個人繳納部 分，計算方法為：醫(yī)療保險二

16、(基本工資+績效工資)*2%(10) 填充“失業(yè)保險”數(shù)據(jù)。FA這里，失業(yè)保險的數(shù)據(jù)為個人繳納部 分，計算方法為：失業(yè)保險二(基本工資+ 績效工資)*1%(11) 導入“考勤扣款”數(shù)據(jù)。(12) 計算“稅前工資”。(13) 計算“個人所得稅” o應交個人所得稅額二應納稅所得額*適 用稅率速算扣除數(shù)，個人所得稅稅率見表 21所示。表個人所得稅稅率表人民郵電出版社POSTS & TELECOM PRESS不超過別0元的Q5%卩超過500元至2000元的部分Q10%P25Q342000元至5Q0Q元的部分小15%1254Q5000元至20000元的部分Q203755Q超過20000元至40000元的

17、部分025%P1375 選中N3單元格。 輸入公式：=IF(M32000,IF(M3-2000)500，(M3- 2000)v=2000)，(M32000)*0.125),0) 按L Enter 鍵確認，可計算出相應 的個人所得稅。 選中N3單元格，用鼠標拖動其填充 句柄至N26單元格，將公式復制到N3:N26 單元格區(qū)域中，可計算出所有員工的個人 所得稅。(13)計算“實發(fā)工資” o任務7格式化“員工工資明細表”(1) 設置工作表的標題格式。(2) 將表中所有的數(shù)據(jù)項格式設置 為“會計專用”格式，保留2位小數(shù), 貨幣符號為“無”。(3) 為表格設置內(nèi)細外粗的藍色邊 框。(4) 為“應發(fā)工資”

18、和“稅前工資” 兩列的數(shù)據(jù)區(qū)域添加“淡藍”色底紋, 為“實發(fā)工資”列數(shù)據(jù)區(qū)域添加“天 藍”色底紋。任務8制作“工資條”(1) 將Sheet3工作表重命名為“工 資條”。(2) 選中“工資條”工作表的A1單 元格。:人民郵電出版社POSTS & TELECOM PRESS(3) 在“編輯欄”中輸入下面的公 式，然后按【EnteiJ鍵確認。=IF(MOD(ROW(),3)=0；nIF(MOD( ROW()，3)=1，員工工資明細 表!A$2,INDEX(員工工資明細 !$A:$OJNT(ROW()- l)/3)+3,COLUMN()人民郵電出版社POSTS & TELECOM PRESS(4) 選中Al單元格，用鼠標拖動其 填充句柄至O1單元格，將公式復制 到81:01單元格區(qū)域中。(5) 選中A1:O1單元格區(qū)域，用鼠 標拖動其填充句柄至074單元格，將 公式復制到A2:O74單元格區(qū)域中。(6) 適當調(diào)整單元格的列寬，使單 元格的內(nèi)容能完整的顯示出來。(7) 美化“工資條”工作表。:人民郵電出版社POSTS & TELECO