人力資源知識(shí)_excel人力資源篇

Excel人力資源篇 2 1項(xiàng)目4制作員工人事檔案管理表 2 1 1項(xiàng)目背景員工人事檔案的整理分類 匯總 分析 查詢等是一件比較復(fù)雜的工作 使用Excel可以實(shí)現(xiàn)人事管理的自動(dòng)化 提高人事檔案管理的條理化 本項(xiàng)目通過(guò)制作 員工人事檔案管理表 來(lái)介紹Excel軟件在人力資源管理中的應(yīng)用 2 1 2項(xiàng)目效果 圖2 1員工人事檔案篩選效果圖 圖2 2公司各部門人員分配效果圖 2 1 3知識(shí)與技能 工作簿的創(chuàng)建 工作表重命名 導(dǎo)入外部數(shù)據(jù) 數(shù)據(jù)鏈接 數(shù)據(jù)的篩選 COUNTIF函數(shù)的使用 創(chuàng)建圖表 2 1 4解決方案任務(wù)1創(chuàng)建工作簿 重命名工作表 1 啟動(dòng)Excel2003 新建一個(gè)空白工作簿 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ù) 打開(kāi) 選取數(shù)據(jù)源 對(duì)話框 3 選擇數(shù)據(jù)源文件 員工信息 prn 4 從對(duì)話框的 原始數(shù)據(jù)類型 中選擇 固定長(zhǎng)度 作為最合適的文件類型 在 導(dǎo)入起始行 文本框中保持默認(rèn)值 1 在文件原始格式下拉列表中選擇 936 簡(jiǎn)體中文 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)建 員工個(gè)人詳細(xì)資料 鏈接 1 鏈接到已有文件 2 鏈接到新建文件 任務(wù)4查閱 員工個(gè)人詳細(xì)資料 當(dāng)需要查閱某個(gè)員工的個(gè)人詳細(xì)資料時(shí) 我們可通過(guò)激活超鏈接進(jìn)行訪問(wèn) 任務(wù)5復(fù)制 員工檔案信息 工作表 1 選中 員工檔案信息 工作表 2 將該工作表復(fù)制三份 并分別重命名為 女員工檔案信息 入職時(shí)間最早的5名員工信息 2005年 2007年入職的員工 任務(wù)6通過(guò)數(shù)據(jù)篩選查詢女員工檔案信息任務(wù)7通過(guò)數(shù)據(jù)篩選查詢?nèi)肼殨r(shí)間最早的5名員工信息任務(wù)8通過(guò)數(shù)據(jù)篩選查詢2005年 2007年入職的員工 任務(wù)9統(tǒng)計(jì)公司各部門員工人數(shù) 1 將Sheet2工作表重命名為 公司各部門員工人數(shù) 2 建立表格框架 3 選中 公司各部門員工人數(shù) 工作表的B3單元格 4 輸入公式 COUNTIF 員工檔案信息 D 2 D 26 行政部 按 Enter 鍵 在B3中顯示 行政部 人數(shù) 5 同樣地 統(tǒng)計(jì)出其他部門的人數(shù) 任務(wù)10創(chuàng)建公司各部門人員比例圖 1 選中 公司各部門員工人數(shù) 工作表中的A2 B9單元格區(qū)域 2 創(chuàng)建 三位餅圖 3 設(shè)置圖表的標(biāo)題格式 2 1 5項(xiàng)目小結(jié)本項(xiàng)目通過(guò)制作 員工人事檔案管理表 主要介紹了工作簿的創(chuàng)建 工作表重命名 導(dǎo)入外部數(shù)據(jù)創(chuàng)建員工檔案信息表 利用超鏈接查閱員工個(gè)人詳細(xì)資料 使用篩選查詢員工檔案信息 使用COUNTIF函數(shù)進(jìn)行各部門人數(shù)統(tǒng)計(jì) 在此基礎(chǔ)上 利用統(tǒng)計(jì)數(shù)據(jù)創(chuàng)建各部門人員比例圖表 2 1 6拓展項(xiàng)目1 通過(guò)數(shù)據(jù)篩選查詢80后的員工檔案信息 2 通過(guò)數(shù)據(jù)篩選查詢 市場(chǎng)部 的男員工檔案信息 3 統(tǒng)計(jì)公司員工學(xué)歷情況并制作比例圖 2 2項(xiàng)目5制作員工培訓(xùn)管理表 2 2 1項(xiàng)目背景企業(yè)在對(duì)員工進(jìn)行培訓(xùn)后 通常要根據(jù)培訓(xùn)項(xiàng)目進(jìn)行相應(yīng)的考核 人力資源部需要針對(duì)考核成績(jī)制定相應(yīng)的培訓(xùn)成績(jī)管理表 本項(xiàng)目通過(guò)制作 員工培訓(xùn)管理表 來(lái)介紹Excel軟件在培訓(xùn)管理方面的應(yīng)用 2 2 2項(xiàng)目效果 圖2 3員工培訓(xùn)管理表 圖2 4員工培訓(xùn)考核數(shù)據(jù)透視圖 2 2 3知識(shí)與技能 工作簿的創(chuàng)建 工作表重命名 數(shù)據(jù)有效性 函數(shù)AVERAGE SUM COUNTIF和RANK的應(yīng)用 美化工作表 數(shù)據(jù)透視表 數(shù)據(jù)透視圖 2 2 4解決方案任務(wù)1創(chuàng)建工作簿 重命名工作表 1 啟動(dòng)Excel2003 新建一個(gè)空白工作簿 2 將創(chuàng)建的工作薄以 員工培訓(xùn)管理表 為名保存 3 將 員工人事檔案管理表 工作簿中的Sheet1工作表重命名為 員工培訓(xùn)成績(jī)表 任務(wù)2創(chuàng)建 員工培訓(xùn)成績(jī)表 框架 1 在 員工培訓(xùn)成績(jī)表 中輸入工作表標(biāo)題 2 輸入表格標(biāo)題字段 任務(wù)3輸入 序號(hào) 利用填充句柄 輸入序號(hào) 任務(wù)4輸入 姓名 和 部門 1 輸入員工 姓名 2 輸入員工 部門 任務(wù)5設(shè)置各培訓(xùn)項(xiàng)目成績(jī)的數(shù)據(jù)有效性在輸入數(shù)據(jù)的過(guò)程中 為盡量減小數(shù)據(jù)輸入的錯(cuò)誤 提高數(shù)據(jù)的有效性 可對(duì)要輸入數(shù)據(jù)的單元格區(qū)域設(shè)置有效性規(guī)則 使輸入的數(shù)據(jù)在規(guī)定的范圍之內(nèi) 即輸入的成績(jī)范圍在0 100之間 任務(wù)6統(tǒng)計(jì) 平均成績(jī) 和總成績(jī)?nèi)蝿?wù)7利用RANK函數(shù)排列員工培訓(xùn)成績(jī)的名次 1 選中I3單元格 2 輸入公式 RANK H3 H 3 H 21 按 Enter 鍵確認(rèn) 3 選中I3單元格 拖動(dòng)填充句柄至I21單元格 將公式復(fù)制到I4 H21單元格區(qū)域中 排列出所有名次 任務(wù)8統(tǒng)計(jì)各培訓(xùn)項(xiàng)目的平均成績(jī) 1 在A22單元格中輸入 各培訓(xùn)項(xiàng)目平均成績(jī) 2 選中D22單元格 輸入公式 AVERAGE D3 D21 按 Enter 鍵確認(rèn) 5 選中D22單元格 拖動(dòng)填充句柄至F22單元格 將公式復(fù)制到E22 F22單元格區(qū)域中 任務(wù)9利用COUNTIF函數(shù)統(tǒng)計(jì)考核不合格的員工人數(shù)這里 假定員工的所有項(xiàng)目平均成績(jī)低于80分 則認(rèn)定為不合格 1 在G22單元格中輸入 不合格人數(shù) 2 選中I22單元格 輸入公式 COUNTIF G3 G21 80 3 按 Enter 鍵確認(rèn) 任務(wù)10設(shè)置單元格數(shù)據(jù)格式將 平均成績(jī) 和 各培訓(xùn)項(xiàng)目平均成績(jī) 的數(shù)據(jù)保留為1位小數(shù) 任務(wù)11美化員工培訓(xùn)成績(jī)表 1 設(shè)置工作表標(biāo)題格式 2 合并單元格 3 將表格中所有數(shù)據(jù)區(qū)域的內(nèi)容設(shè)置為居中對(duì)齊 4 適當(dāng)調(diào)整表格的行高和列寬 能顯示表格內(nèi)容 5 為表格設(shè)置邊框 任務(wù)12制作員工培訓(xùn)考核數(shù)據(jù)透視圖 1 選中 員工培訓(xùn)成績(jī)表 2 選擇 數(shù)據(jù) 菜單中的 數(shù)據(jù)透視表和數(shù)據(jù)透視圖 命令 打開(kāi) 數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?duì)話框 按提示進(jìn)行操作 3 將 姓名 作為 行 字段 將 計(jì)算機(jī)技能 作為 數(shù)據(jù) 項(xiàng) 4 選擇 匯總方式 為 最大值 5 單擊 完成 按鈕 系統(tǒng)將在工作簿中插入一張工作表Sheet4和一張工作表Chart1 在Sheet4中生成數(shù)據(jù)透視表 將Sheet4工作表重命名為 員工培訓(xùn)成績(jī)數(shù)據(jù)透視表 在Chart1中生成數(shù)據(jù)透視圖 將Chart1工作命名為 員工培訓(xùn)考核數(shù)據(jù)透視圖 2 2 5項(xiàng)目小結(jié)本項(xiàng)目通過(guò)制作 員工培訓(xùn)管理表 主要介紹了工作簿的創(chuàng)建 工作表重命名 數(shù)據(jù)有效性設(shè)置 函數(shù)AVERAGE SUM COUNTIF和RANK的應(yīng)用 美化工作表等 在此基礎(chǔ)上 利用培訓(xùn)成績(jī)表中的數(shù)據(jù) 通過(guò)數(shù)據(jù)透視表和數(shù)據(jù)透視圖對(duì)員工培訓(xùn)成績(jī)進(jìn)行匯總 分析 使表格中的數(shù)據(jù)更為直觀 生動(dòng) 2 2 6拓展項(xiàng)目1 按培訓(xùn)成績(jī)名次進(jìn)行升序排列 2 制作各培訓(xùn)項(xiàng)目平均成績(jī)比較圖 3 制作公司各部門培訓(xùn)成績(jī)數(shù)據(jù)透視圖 2 3項(xiàng)目6制作員工工資管理表 2 3 1項(xiàng)目背景制作工資表示通常需要綜合大量的數(shù)據(jù) 如基本工資 績(jī)效工資 補(bǔ)貼 扣款項(xiàng)等 如果用手工做賬 不僅工作量非常大 而且還往往容易出錯(cuò) 本項(xiàng)目通過(guò)制作 員工工資管理表 來(lái)介紹Excel軟件在工資管理方面的應(yīng)用 2 3 2項(xiàng)目效果 圖2 5員工工資明細(xì)表效果圖 圖2 6員工工資條效果圖 2 3 3知識(shí)與技能 工作簿的創(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 啟動(dòng)Excel2003 新建一個(gè)空白工作簿 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 刪除 身份證號(hào)碼 學(xué)歷 職稱 性別 和 出生日期 列的數(shù)據(jù) 圖2 60刪除數(shù)據(jù)后的工作表 3 分別在E1 F1 G1單元格中輸入標(biāo)題字段名稱 基本工資 績(jī)效工資 和 工齡工資 4 輸入 基本工資數(shù)據(jù) 5 計(jì)算 績(jī)效工資 這里 績(jī)效工資 基本工資 50 6 計(jì)算 工齡工資 這里 如果 工齡 超過(guò)10年 則工齡工資為500元 否則 按每年50元計(jì)算 任務(wù)4創(chuàng)建 加班費(fèi)結(jié)算表 1 復(fù)制 工資基礎(chǔ)信息表 將復(fù)制后的工作表重命名為 加班費(fèi)結(jié)算表 2 刪除 入職時(shí)間 績(jī)效工資 和 工齡工資 列 3 在E1 F1單元格中分別輸入標(biāo)題 加班時(shí)間 和 加班費(fèi) 4 輸入加班時(shí)間 5 計(jì)算機(jī)加班費(fèi) 加班費(fèi)的計(jì)算方法為 加班費(fèi) 基本工資 30 8 1 5 加班時(shí)間 任務(wù)5創(chuàng)建 考勤扣款結(jié)算表 1 復(fù)制 工資基礎(chǔ)信息表 將復(fù)制后的工作表重命名為 考勤扣款結(jié)算表 2 刪除 入職時(shí)間 績(jī)效工資 和 工齡工資 列 3 在E1 K1單元格中分別輸入標(biāo)題 遲到 遲到扣款 病假 病假扣款 事假 事假扣款 和 扣款合計(jì) 4 輸入 遲到 病假 事假 的數(shù)據(jù) 5 計(jì)算 遲到扣款 這里 假設(shè)每遲到一次扣款為50元 6 計(jì)算 病假扣款 這里 假設(shè)每病假一天扣款為當(dāng)日工資收入的50 即 病假扣款 基本工資 30 0 5 病假天數(shù) 7 計(jì)算 事假扣款 這里 假設(shè)每事假一天扣款為當(dāng)日工資收入 即 事假扣款 基本工資 30 事假天數(shù) 8 計(jì)算 扣款合計(jì) 任務(wù)6創(chuàng)建 員工工資明細(xì)表 1 將Sheet2工作表重命名為 員工工資明細(xì)表 2 參創(chuàng)建員工工資明細(xì)表的框架 3 填充 編號(hào) 姓名 和 部門 數(shù)據(jù) 4 導(dǎo)入 基本工資 績(jī)效工資 工齡工資 和 加班費(fèi) 數(shù)據(jù) 選中D3單元格 選擇 插入 菜單中的 函數(shù) 命令 從函數(shù)列表中選擇 VLOOKUP 函數(shù) 打開(kāi) 函數(shù)參數(shù) 對(duì)話框 設(shè)置參數(shù) 單擊 確定 按鈕 導(dǎo)入相應(yīng)的 基本工資 數(shù)據(jù) 選中D3單元格 用鼠標(biāo)拖動(dòng)其填充句柄至D26單元格 將公式復(fù)制到D3 D26單元格區(qū)域中 可導(dǎo)入所有員工的基本工資 5 同樣地方式 分別導(dǎo)入 績(jī)效工資 工齡工資 和 加班費(fèi) 數(shù)據(jù) 6 導(dǎo)入 加班費(fèi) 數(shù)據(jù) 7 計(jì)算 應(yīng)發(fā)工資 8 填充 養(yǎng)老保險(xiǎn) 數(shù)據(jù) 這里 養(yǎng)老保險(xiǎn)的數(shù)據(jù)為個(gè)人繳納部分 計(jì)算方法為 養(yǎng)老保險(xiǎn) 基本工資 績(jī)效工資 8 9 填充 醫(yī)療保險(xiǎn) 數(shù)據(jù) 這里 醫(yī)療保險(xiǎn)的數(shù)據(jù)為個(gè)人繳納部分 計(jì)算方法為 醫(yī)療保險(xiǎn) 基本工資 績(jī)效工資 2 10 填充 失業(yè)保險(xiǎn) 數(shù)據(jù) 這里 失業(yè)保險(xiǎn)的數(shù)據(jù)為個(gè)人繳納部分 計(jì)算方法為 失業(yè)保險(xiǎn) 基本工資 績(jī)效工資 1 11 導(dǎo)入 考勤扣款 數(shù)據(jù) 12 計(jì)算 稅前工資 13 計(jì)算 個(gè)人所得稅 應(yīng)交個(gè)人所得稅額 應(yīng)納稅所得額 適用稅率 速算扣除數(shù) 個(gè)人所得稅稅率見(jiàn)表2 1所示 表2 1個(gè)人所得稅稅率表 選中N3單元格 輸入公式 IF M3 2000 IF M3 2000 500 M3 2000 2000 M3 2000 0 1 25 0 按 Enter 鍵確認(rèn) 可計(jì)算出相應(yīng)的個(gè)人所得稅 選中N3單元格 用鼠標(biāo)拖動(dòng)其填充句柄至N26單元格 將公式復(fù)制到N3 N26單元格區(qū)域中 可計(jì)算出所有員工的個(gè)人所得稅 13 計(jì)算 實(shí)發(fā)工資 任務(wù)7格式化 員工工資明細(xì)表 1 設(shè)置工作表的標(biāo)題格式 2 將表中所有的數(shù)據(jù)項(xiàng)格式設(shè)置為 會(huì)計(jì)專用 格式 保留2位小數(shù) 貨幣符號(hào)為 無(wú) 3 為表格設(shè)置內(nèi)細(xì)外粗的藍(lán)色邊框 4 為 應(yīng)發(fā)工資 和 稅前工資 兩列的數(shù)據(jù)區(qū)域添加 淡藍(lán) 色底紋 為 實(shí)發(fā)工資 列數(shù)據(jù)區(qū)域添加 天藍(lán) 色底紋 任務(wù)8制作 工資條 1 將Sheet3工作表重命名為 工資條 2 選中 工資條 工作表的A1單元格 3 在 編輯欄 中輸入下面的公式 然后按 Enter 鍵確認(rèn) IF MOD ROW 3 0 IF MOD ROW 3 1 員工工資明細(xì)表 A 2 INDEX 員工工資明細(xì)表 A O INT ROW 1 3 3 COLUMN 4 選中A1單元格 用鼠標(biāo)拖動(dòng)其填充句柄至O1單元格 將公式復(fù)制到B1 O1單元格區(qū)域中 5 選中A1 O1單元格區(qū)域 用鼠標(biāo)拖動(dòng)其填充句柄至O74單元格 將公式復(fù)制到A2 O74單元格區(qū)域中 6 適當(dāng)調(diào)整單元格的列寬 使單元格