Excel在人力資源管理的應(yīng)用

1、Excel在人力資源管理中的應(yīng)用,用好工具，高效工作,數(shù)據(jù)錄入 單元格的引用 數(shù)據(jù)有效性 條件格式 認識Excel函數(shù) Excel函數(shù)的結(jié)構(gòu) 常見的幾類函數(shù) 應(yīng)用實例 認識數(shù)據(jù)透視表 數(shù)據(jù)透視表是什么 數(shù)據(jù)透視表的幾個簡單應(yīng)用實例,課程大綱,1-1 單元格的引用,1.相對引用 相對引用指公式中的單元格位置將隨著公式單元格的位置而改變 2. 絕對引用 絕對引用是指公式和函數(shù)中的位置是固定不變的. 絕對引用是在列字母和行數(shù)字之前都加上美元符號”$”,如$A$4,$C$6 3. 混合引用GO 混合引用是指在一個單元格引用中,既有絕對引用,也有相對引用. 例: 行變列不變: $A4 列變行不變:A$4

2、,1-2 數(shù)據(jù)的有效性,控制單元格輸入文本長度 GO 選中區(qū)域-數(shù)據(jù)-數(shù)據(jù)有效性-允許-文本長度 可防止身份證/銀行卡號/手機號碼輸入多一位或者少一位數(shù) 序列 選中區(qū)域-數(shù)據(jù)-數(shù)據(jù)有效性-序列-在“來源”輸入（A,B,C,D) 注意逗號要半角格式 禁止輸入重復(fù)數(shù)據(jù) =countif(區(qū)域,起始單元格)=1 選中區(qū)域-數(shù)據(jù)-數(shù)據(jù)有效性- 允許（自定義) - 公式填寫=COUNTIF(A:A,A1)=1,1-2 數(shù)據(jù)的有效性,如何取消對于“數(shù)據(jù)有效性”的設(shè)置？ 選中區(qū)域-數(shù)據(jù)-數(shù)據(jù)有效性-允許-任何值 GO,1-3 條件格式,利用突出的格式，經(jīng)常能起到提醒的功能 如：突出不滿勤的人GO 突出年齡大

3、于30的人等,如何取消條件格式？,認識Excel函數(shù),2-1 EXCEL函數(shù)的結(jié)構(gòu),Excel 函數(shù)即是預(yù)先定義，執(zhí)行計算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。,Excel 函數(shù)結(jié)構(gòu):,也有一些函數(shù)是沒有參數(shù)的,如NOW(),左右括號成對出現(xiàn),單一結(jié)構(gòu),嵌套結(jié)構(gòu),參數(shù)與參數(shù)之間使用半角逗號進行分隔,應(yīng)用實例 從身份證號碼提取出生日期 在C2單元格中輸入 =MID(B2,7,4)&-&MID(B2,11,2)&-&MID(B2,13,2) 公式解析：MID(B2,7,4)表示提取B2單元格中從第7位開始連續(xù)4位的字符，即出生年；MID(B2,11,2)表示提取B2單元格中從第11位開始連續(xù)2位的字符，

4、即出生月； MID(B2,11,2)表示提取B2單元格中從第13位開始連續(xù)2位的字符，即出生日； 在公式中加入&可以用來連接兩個文本字符串。,常用函數(shù)之查找類函數(shù),函數(shù)VOOKUP =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) (以從8月工資表導(dǎo)入考勤到8月考核表為例） GO,常用函數(shù)之查找類函數(shù),函數(shù)VOOKUP 選擇區(qū)域里最好不要有合并的單元格（先“取消單元格合并”） 如果單元格里有空格，有可能導(dǎo)不出來 （提示：函數(shù)TRIM（A1)可以去除字符串首部和尾部的空格） 注意重復(fù)的名字 查找區(qū)域首列必須含有查找的內(nèi)容（

5、第一個參數(shù)） 介紹vookup函數(shù)與IFERROR函數(shù)組合使用 (以從8月工資表導(dǎo)入考勤到8月考核表為例）GO =IFERROR(VLOOKUP(A4,8月工資!B:F,5,0),0) 如果VOOKUP函數(shù)運行后找不到對應(yīng)的數(shù)據(jù)，就會顯示為#N/A，IFERROR的作用就是把這些#N/A都變?yōu)榈?個參數(shù)。,應(yīng)用實例 在C1輸入=A1&+&B1 如何根據(jù)A1得出B1和C1？ 在B1輸入=LEFT(A1,FIND(+,A1)-1) 分析：思路是從左邊開始，截取“+”號前的位數(shù)。 在C1輸入=RIGHT(A1,LEN(A1)-FIND(+,A1) 分析：思路是截取“+”號后的位數(shù)。,如何根據(jù)A1、B

6、1得出C1？,IF函數(shù)的應(yīng)用實例,區(qū)域段自動算出對應(yīng)等級 =IF(K4=90,A,IF(K4=80,B,IF(K4=70,C,D) 嵌套了兩個IF函數(shù),應(yīng)用實例 從身份證號碼中提取性別 （身份證倒數(shù)第2位若為偶數(shù)為女性，奇數(shù)為男性） 在C2單元格中輸入=IF(MOD(MID(B2,17,1),2)=1,男,女) 公式分析： MID(B2,17,1)是從身份證第17位起取1位字符；在本例即為數(shù)字5，MOD函數(shù)就是取余數(shù)的函數(shù)，在本例中就是MOD(5，2),5除以2的余數(shù)，最外面是一個IF函數(shù)，當(dāng)這個余數(shù)為1時，即為奇數(shù)，奇數(shù)就是男性，否則為女性。,常用函數(shù)之日期類函數(shù),DATEDIF函數(shù) 常用于

7、計算兩日期的時間差 =DATEDIF(較早的日期，較晚的日期，第三個參數(shù)） 第三個參數(shù)： “Y” -兩日期間共多少年。 “M” -兩日期間共多少個月。 “D” -兩日期間共多少天。 “MD” 是兩日期中天數(shù)的差。忽略日期中的月和年。 “YM” 是兩日期中月數(shù)的差。忽略日期中的年。 “YD” 是兩日期中天數(shù)的差。忽略日期中的年。,常用函數(shù)之日期類函數(shù),舉例:算兩個日期間差幾年、幾月、幾日 開始時間是2012-2-21 結(jié)束時間是2014-2-19 C2=DATEDIF(A2,B2,Y) D2=DATEDIF(A2,B2,YM) E2=DATEDIF(A2,B2,MD) F2=DATEDIF(A2

8、,B2,D) G2=DATEDIF(A2,B2,M) H2=DATEDIF(A2,B2,“YD) I2=C2&年&D2&個月 I2=DATEDIF(A2,B2,“y”)&“年”&DATEDIF(A2,B2,“ym”)&“個月,常用函數(shù)之日期類函數(shù),應(yīng)用實例3 從出生日得出年齡 在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

9、(),y),應(yīng)用實例 培訓(xùn)協(xié)議服務(wù)期限完成提醒 思路：過期了可以顯示“協(xié)議期限完成” 沒過期的，顯示“沒過期” =IFERROR(IF(DATEDIF(B2,TODAY(),d)=0, 協(xié)議期限完成, ),沒過期) 分析：DATEDIF(B2,TODAY(),“d”)可以 求出今天到B2的日期共多少天，如果“今天” 在B2之前，就會產(chǎn)生錯誤值，如果“今天”在 B2之后，這個函數(shù)就會算出B2距離“今天”有 多少天。當(dāng)這個天數(shù)0時，就會執(zhí)行IF函數(shù)， 顯示“協(xié)議期限完成”，當(dāng)DATEDIF運行是一 個錯誤值時，這時候就執(zhí)行IFERROR函數(shù)， 顯示“沒過期”。,如果我想到期前一個月就提醒呢？,思路

10、： =IFERROR(IF(DATEDIF(TODAY(),B2,D)=31,少于31天后就會過期,多于31天),已過期),DATEDIF函數(shù),能運行，說明B2晚于今天,能執(zhí)行IF函數(shù),當(dāng)B2晚于今天31天,顯示“少于31天后就會過期”,當(dāng)B2晚于今天31天,顯示“多于31天”,不能運行，B2早于今天（培訓(xùn)期限過期了）,DATEDIF函數(shù)會產(chǎn)生錯誤值,IF函數(shù)也會產(chǎn)生錯誤值,執(zhí)行IFERROR函數(shù),顯示“已過期”,常用函數(shù)之計數(shù)類函數(shù),COUNTIF函數(shù) COUNTIF(range,criteria) 區(qū)域 條件標(biāo)準(zhǔn) 是一個數(shù)數(shù)的函數(shù)，可以用于數(shù)出參數(shù)1區(qū)域內(nèi)，符合參數(shù)2的條件的單元格個數(shù)。

11、如：=countif(F3:F10, “女”) 統(tǒng)計F3:F10中女的個數(shù)。,COUNTIF函數(shù)的應(yīng)用實例,應(yīng)用實例 在9月流動表中找出有相同名字的人 步驟1：打開9月流動表，找出9月在職的工作表，在姓名右邊新建一列，插入函數(shù)，找到COUNTIF函數(shù)，選擇“確定”。,COUNTIF函數(shù)的應(yīng)用實例,步驟2：點擊Range的框格，選中E列，點擊Criteria的框格，點擊E2單元格，點擊“確定”。 步驟3：點擊F2單元格，把鼠標(biāo)移到F2右下角，鼠標(biāo)箭頭變?yōu)楹谏值臅r候雙擊。,這個的意思就是在9月在職的人中只有一個叫嚴邦平的,COUNTIF函數(shù)的應(yīng)用實例,步驟4：選中F列-排序與篩序-篩選-點擊F

12、1左上角小箭頭-去掉1前的小勾-確定GO,COUNTIF函數(shù)的應(yīng)用實例,統(tǒng)計一定區(qū)間的個數(shù) 如：統(tǒng)計9月流動表“9月在職”工作表中， 50歲以上的人數(shù)： =COUNTIF(F:F,=50) 20歲年齡40歲的人數(shù)： =COUNTIF(F:F,=20)-COUNTIF(F:F,=40) （注意：如果要統(tǒng)計的數(shù)據(jù)是有一個綠色的左角的， 要先選擇要統(tǒng)計的數(shù)據(jù)，點擊 符號，選擇 “轉(zhuǎn)換為數(shù)字”，不然是統(tǒng)計不了個數(shù)的）GO,認識數(shù)據(jù)透視表,認識數(shù)據(jù)透視表,數(shù)據(jù)透視表是一種交互，交叉制作的報表，一組具有多個字段的數(shù)據(jù)進行多立體的分析匯總，用于對多種來源的數(shù)據(jù)進行匯總和分析，從而可以快速合并和比較大量數(shù)據(jù)。

13、當(dāng)數(shù)據(jù)規(guī)模比較大時，這種分析的意義就顯得尤為突出。,說人話,一種快速分析數(shù)據(jù)的工具,前提 這些數(shù)據(jù)使用數(shù)段表進行管理,統(tǒng)計9月入職的招聘來源分布,統(tǒng)計離職原因分布個數(shù),統(tǒng)計各部門平均工資,數(shù)據(jù)透視表可以快速解決這些事情,統(tǒng)計各部門學(xué)歷分布情況,認識數(shù)據(jù)透視表,統(tǒng)計9月入職的招聘來源分布 步驟1：打開工作表-選擇數(shù)據(jù)來源區(qū)域（A1：i79）-插入-數(shù)據(jù)透視表-確定 統(tǒng)計9月入職的招聘來源分布,步驟2：把招聘來源拖到下面的“行標(biāo)簽”框，把序號拖到下面的“數(shù)值”框，就能快速生成下圖。,統(tǒng)計離職原因分布個數(shù),同理，可根據(jù)前面介紹的步驟在辭工名單的表格中快速做出離職原因分布的表格GO 拖“離職原因”到“行標(biāo)簽”框，拖“序號”到“數(shù)值”框 注意：要留意“數(shù)值”框，