1、Excel在人力資源管理中的應用在人力資源管理中的應用 用好工具，高效工作用好工具，高效工作 最新Excel在人力資源管理的應用 v數據錄入 單元格的引用 數據有效性 條件格式 v認識Excel函數 Excel函數的結構 常見的幾類函數 應用實例 v認識數據透視表 數據透視表是什么 數據透視表的幾個簡單應用實例 課程大綱課程大綱 最新Excel在人力資源管理的應用 1-1 單元格的引用單元格的引用 1.相對引用 相對引用指公式中的單元格位置將隨著公式單元格的位置而改變 2. 絕對引用 絕對引用是指公式和函數中的位置是固定不變的. 絕對引用是在列字母和行數字之前都加上美元符號”$”,如 $A$4

2、,$C$6 3. 混合引用GO 混合引用是指在一個單元格引用中,既有絕對引用,也有相對引用. 例: 行變列不變: $A4 列變行不變:A$4 最新Excel在人力資源管理的應用 1-2 數據的有效性數據的有效性 控制單元格輸入文本長度 GO 選中區域-數據-數據有效性-允許-文本長度 可防止身份證/銀行卡號/手機號碼輸入多一位或者少一位數 序列 選中區域-數據-數據有效性-序列-在“來源”輸入 （A,B,C,D) 注意逗號要半角格式 禁止輸入重復數據 =countif(區域,起始單元格起始單元格)=1 選中區域-數據-數據有效性- 允許（自定義) - 公式填寫=COUNTIF(A:A,A1)=

3、1 最新Excel在人力資源管理的應用 1-2 數據的有效性數據的有效性 v如何取消對于“數據有效性”的設置？ 選中區域-數據-數據有效性-允許-任何值 GO 最新Excel在人力資源管理的應用 1-3 條件格式條件格式 利用突出的格式，經常能起 到提醒的功能 如：突出不滿勤的人GO 突出年齡大于30的人等 如何取消條件格式？ 最新Excel在人力資源管理的應用 認識Excel函數 最新Excel在人力資源管理的應用 2-1 EXCEL2-1 EXCEL函數的結構函數的結構 vExcel 函數即是預先定義，執行計算、分析等處理數 據任務的特殊公式特殊公式。 Excel 函數結構: 也有一些函數

4、也有一些函數 是沒有參數的是沒有參數的, 如如NOW() 左右括號成左右括號成 對出現對出現 單 一 結 構 嵌 套 結 構 參數與參數之間參數與參數之間 使用使用半角逗號半角逗號進進 行分隔行分隔 最新Excel在人力資源管理的應用 最新Excel在人力資源管理的應用 v應用實例 從身份證號碼提取出生日期從身份證號碼提取出生日期 在C2單元格中輸入 =MID(B2,7,4)&-&MID(B2,11,2)&-&MID(B2,13,2) 公式解析： MID(B2,7,4)表示提取B2單元格中從第7位開始連續4位的字符，即出生 年； MID(B2,11,2)表示提取B2單元格中從第11位開始連續2

5、位的字符，即出 生月； MID(B2,11,2)表示提取B2單元格中從第13位開始連續2位的字符，即出 生日； 在公式中加入&可以用來連接兩個文本字符串。 最新Excel在人力資源管理的應用 最新Excel在人力資源管理的應用 常用函數之查找類函數常用函數之查找類函數 v函數函數VOOKUP =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) (以從8月工資表導入考勤到8月考核表為例） GO 最新Excel在人力資源管理的應用 常用函數之查找類函數常用函數之查找類函數 v函數函數VOOKUP 選擇區域里最好不要有合并的單元

6、格（先“取消單元格合并”） 如果單元格里有空格，有可能導不出來 （提示：函數TRIM（A1)可以去除字符串首部和尾部的空格） 注意重復的名字 查找區域首列必須含有查找的內容（第一個參數） 介紹vookup函數與IFERROR函數組合使用 (以從8月工資表導入考勤到8月考核表為例）GO =IFERROR(VLOOKUP(A4,8月工資!B:F,5,0),0) 如果VOOKUP函數運行后找不到對應的數據，就會顯示為#N/A， IFERRORIFERROR的作用就是把這些的作用就是把這些#N/A#N/A都變為第都變為第2 2個參數。個參數。 最新Excel在人力資源管理的應用 最新Excel在人力資

7、源管理的應用 最新Excel在人力資源管理的應用 v應用實例 在C1輸入=A1&+&B1 如何根據如何根據A1得出得出B1和和C1？ 在B1輸入=LEFT(A1,FIND(+,A1)-1) 分析：思路是從左邊開始，截取分析：思路是從左邊開始，截取“+”號前的位數。號前的位數。 在C1輸入=RIGHT(A1,LEN(A1)-FIND(+,A1) 分析：思路是截取分析：思路是截取“+”號后的位數。號后的位數。 如何根據如何根據 A1、B1得得 出出C1？ 最新Excel在人力資源管理的應用 最新Excel在人力資源管理的應用 IF函數的應用實例函數的應用實例 區域段自動算出對應等級區域段自動算出對

8、應等級 =IF(K4=90,A,IF(K4=80,B,IF(K4=70,C,D) 嵌套了兩個IF函數 最新Excel在人力資源管理的應用 最新Excel在人力資源管理的應用 v應用實例 從身份證號碼中提取性別從身份證號碼中提取性別 （身份證倒數第2位若為偶數為女性，奇數為男性） 在C2單元格中輸入=IF(MOD(MID(B2,17,1),2)=1,男,女) 公式分析： MID(B2,17,1)是從身份證第17位起取1位字符；在本例即為數字5， MOD函數就是取余數的函數，在本例中就是MOD(5，2),5除以2的余 數，最外面是一個IF函數，當這個余數為1時，即為奇數，奇數就是 男性，否則為女性

9、。 最新Excel在人力資源管理的應用 最新Excel在人力資源管理的應用 最新Excel在人力資源管理的應用 常用函數之日期類函數常用函數之日期類函數 DATEDIF函數 l常用于計算兩日期的時間差 =DATEDIF(較早的日期，較晚的日期，第三個參數） 第三個參數： l“Y” -兩日期間共多少年。 l“M” -兩日期間共多少個月。 l“D” -兩日期間共多少天。 l“MD” 是兩日期中天數的差。忽略日期中的月和年。 l“YM” 是兩日期中月數的差。忽略日期中的年。 l“YD” 是兩日期中天數的差。忽略日期中的年。 最新Excel在人力資源管理的應用 常用函數之日期類函數常用函數之日期類函數

10、 舉例:算兩個日期間差幾年、幾月、幾日 開始時間是2012-2-21 結束時間是2014-2-19 C2=DATEDIF(A2,B2,Y) D2=DATEDIF(A2,B2,YM) E2=DATEDIF(A2,B2,MD) F2=DATEDIF(A2,B2,D) G2=DATEDIF(A2,B2,M) H2=DATEDIF(A2,B2,“YD) I2=C2&年&D2&個月 I2=DATEDIF(A2,B2,“y”)&“年”&DATEDIF(A2,B2,“ym”)&“個月 最新Excel在人力資源管理的應用 常用函數之日期類函數常用函數之日期類函數 v應用實例3 從出生日得出年齡從出生日得出年齡

11、 在D2單元格中輸入=DATEDIF(C2,NOW(),“y”) 因為從身份證提取出生日的C2 =MID(B2,7,4)&-&MID(B2,11,2)&-&MID(B2,13,2) 所以若直接從身份證得出年齡直接從身份證得出年齡 D2= DATEDIF(MID(B2,7,4)&-&MID(B2,11,2)&-&MID(B2,13,2),NOW(),y) 最新Excel在人力資源管理的應用 v應用實例 培訓協議服務期限完成提醒培訓協議服務期限完成提醒 思路：過期了可以顯示“協議期限完成” 沒過期的，顯示“沒過期” =IFERROR(IF(DATEDIF(B2,TODAY(),d)=0, 協議期限

12、完成, ),沒過期) 分析：DATEDIF(B2,TODAY(),“d”)可以 求出今天到B2的日期共多少天，如果“今天” 在B2之前，就會產生錯誤值，如果“今天”在 B2之后，這個函數就會算出B2距離“今天”有 多少天。當這個天數0時，就會執行IF函數， 顯示“協議期限完成”，當DATEDIF運行是一 個錯誤值時，這時候就執行IFERROR函數， 顯示“沒過期”。 如果我想到期如果我想到期 前一個月就提前一個月就提 醒呢？醒呢？ 最新Excel在人力資源管理的應用 思路： =IFERROR(IF(DATEDIF(TODAY (),B2,D)=50) 20歲年齡40歲的人數： =COUNTIF

13、(F:F,=20)-COUNTIF(F:F,=40) （注意：如果要統計的數據是有一個綠色的左角的，（注意：如果要統計的數據是有一個綠色的左角的， 要先選擇要統計的數據，點擊要先選擇要統計的數據，點擊 符號，選擇符號，選擇 “轉換為數字轉換為數字”，不然是統計不了個數的），不然是統計不了個數的） GO 最新Excel在人力資源管理的應用 認識數據透視表認識數據透視表 最新Excel在人力資源管理的應用 認識數據透視表認識數據透視表 v數據透視表是一種交互，交叉制作的報表，一組 具有多個字段的數據進行多立體的分析匯總，用 于對多種來源的數據進行匯總和分析，從而可以 快速合并和比較大量數據。當數據

14、規模比較大時， 這種分析的意義就顯得尤為突出。 說人話 一種快速分析數據的工具 最新Excel在人力資源管理的應用 v前提 這些數據使用數段表進行管理 最新Excel在人力資源管理的應用 統計9月入職的招聘來源分布 統計離職原因分布個數 統計各部門平均工資 數據透視表 可以快速解 決這些事情 統計各部門學歷分布情況 認識數據透視表認識數據透視表 最新Excel在人力資源管理的應用 統計9月入職的招聘來源分布 步驟1：打開工作表-選擇數據來源區域（A1：i79）-插入 -數據透視表-確定 統計9月入職的招聘來源分布 最新Excel在人力資源管理的應用 步驟2：把招聘來源拖到下 面的“行標簽”框，把 序號拖到下面的“數值” 框，就能快速生成下圖。 最新Excel在人力資源管理的應用 統計離職原因分布個數統計離職原因分布個數 v 同理，可根據前面介紹的步驟在辭工名單的表格中快速做 出離職原因分布離職原因分布的表格GO v 拖“離職原因”到“行標簽”框，拖“序號”到“數值” 框 v 注意：要留意“數值”框，是求和，還是計數，還是其他， 左圖的默認是“求和”，而我們是想“計數”。 通過雙擊通過雙擊B3，出，出 來來“值字