Excel在人力資源管理的應用極其精美實用課件

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

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

3、突出年齡大于30的人等如何取消條件格式？第6頁，共45頁。認識Excel函數(shù)第7頁，共45頁。2-1.EXCEL函數(shù)的結(jié)構(gòu)Excel 函數(shù)即是預先定義，執(zhí)行計算、分析等處理數(shù)據(jù)任務的特殊公式。Excel 函數(shù)結(jié)構(gòu):也有一些函數(shù)是沒有參數(shù)的,如NOW()左右括號成對出現(xiàn)單一結(jié)構(gòu)嵌套結(jié)構(gòu)參數(shù)與參數(shù)之間使用半角逗號進行分隔第8頁，共45頁。函數(shù)名語法函數(shù)結(jié)構(gòu)函數(shù)作用例子運行結(jié)果1、Left1、LEFT(text,num_chars)其中：Text是包含要提取字符的文本字符串。Num_chars 指定要由 LEFT 所提取的字符數(shù)。=LEFT(A1,2)從A1最左邊向右取兩位2、Right2、RIGH

4、T(text,num_chars) 其中：Text是包含要提取字符的文本字符串。Num_chars指定希望 RIGHT 提取的字符數(shù)。=RIGHT(A1,2)從A1最右邊向左取兩位3、Mid3、MID(text,start_num,num_chars)其中：Text是包含要提取字符的文本字符串。Start_num 是文本中要提取的第一個字符的位置。Num_chars 指定希望 MID 從文本中返回字符的個數(shù)。=MID(A1,2,3)從A1左邊第2位開始取3位4、&（連接符號）XX&XX=A2&B2將A2和B2內(nèi)容合并在一起常用函數(shù)之截取、合并類函數(shù)第9頁，共45頁。應用實例從身份證號碼提取出生

5、日期 在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位的字符，即出生月； MID(B2,11,2)表示提取B2單元格中從第13位開始連續(xù)2位的字符，即出生日； 在公式中加入&可以用來連接兩個文本字符串。第10頁，共45頁。函數(shù)名語法函數(shù)結(jié)構(gòu)函數(shù)作用FIND=FIND(龍,C6)在c6中查找龍，結(jié)果顯示龍在c6中的位數(shù)，查不到顯示#VALUE!常用函數(shù)之查找類函數(shù)第11頁，共45頁。常

6、用函數(shù)之查找類函數(shù)函數(shù)VOOKUP=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)(以從8月工資表導入考勤到8月考核表為例） GO參數(shù)簡單說明輸入數(shù)據(jù)類型lookup_value要查找的值數(shù)值、引用或文本字符串table_array要查找的區(qū)域數(shù)據(jù)表區(qū)域col_index_num返回數(shù)據(jù)在查找區(qū)域的第幾列數(shù)正整數(shù)range_lookup精確匹配/模糊匹配0或1第12頁，共45頁。常用函數(shù)之查找類函數(shù)函數(shù)VOOKUP選擇區(qū)域里最好不要有合并的單元格（先“取消單元格合并”）如果單元格里有空格，有可能導不出來 （提示：函數(shù)TR

7、IM（A1)可以去除字符串首部和尾部的空格）注意重復的名字查找區(qū)域首列必須含有查找的內(nèi)容（第一個參數(shù)）介紹vookup函數(shù)與IFERROR函數(shù)組合使用 (以從8月工資表導入考勤到8月考核表為例）GO =IFERROR(VLOOKUP(A4,8月工資!B:F,5,0),0) 如果VOOKUP函數(shù)運行后找不到對應的數(shù)據(jù)，就會顯示為#N/A，IFERROR的作用就是把這些#N/A都變?yōu)榈?個參數(shù)。第13頁，共45頁。函數(shù)名語法/例子函數(shù)結(jié)構(gòu)函數(shù)作用Value=value(b1)將b1數(shù)值化Numberstring=NumberString(7890,1）運行結(jié)果：七千八百九十=NumberStrin

8、g(7890,2) 運行結(jié)果：柒仟捌佰玖拾=NumberString(7890,3) 運行結(jié)果：七八九=numberstring(b1,2)將b1數(shù)字轉(zhuǎn)為大寫漢字Row=ROW(reference)其中：Reference 為需要得到其行號的單元格或單元格區(qū)域。=row(b1)求b1是第幾行（值為1)Column=COLUMN(reference)=column(b1)求b1是第幾列(值為2)常用函數(shù)之文本類函數(shù)第14頁，共45頁。函數(shù)名語法函數(shù)結(jié)構(gòu)函數(shù)作用len=LEN(text)其中：Text 是要查找其長度的文本?？崭駥⒆鳛樽址M行計數(shù)。=len(C2)計算C2的字符長度rankRANK

9、(number,ref,order)Number為需要找到排位的數(shù)字。Ref 為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用。Ref 中的非數(shù)值型參數(shù)將被忽略。Order為一數(shù)字，指明排位的方式。=rank(b1,b1:b10,0)=rank(b1,b1:b10,1)計算b1在b1:b10區(qū)域中從大到小排第幾名；計算b1在b1:b10區(qū)域中從小到大排第幾名。常用函數(shù)之文本類函數(shù)第15頁，共45頁。應用實例 在C1輸入=A1&+&B1如何根據(jù)A1得出B1和C1？在B1輸入=LEFT(A1,FIND(+,A1)-1)分析：思路是從左邊開始，截取“+”號前的位數(shù)。在C1輸入=RIGHT(A1,LEN(A1)-FI

10、ND(+,A1)分析：思路是截取“+”號后的位數(shù)。如何根據(jù)A1、B1得出C1？第16頁，共45頁。函數(shù)名語法函數(shù)結(jié)構(gòu)函數(shù)作用IFIF(logical_test,value_if_true,value_if_false)=if(a20,1,0)當a20時，顯示1，否則顯示0IFREEOR第一個參數(shù)一般為一個函數(shù)，當這個函數(shù)運行是個錯誤值時（#N/A #DIV/0! #N/A #VALUE! #NAME #NULL! #NUM! #REF!），運行結(jié)果等于第二個參數(shù)=iferror(vookup(A1,A:B,2,0),0) 如果VLOOKUP函數(shù)運行值是個錯誤值的話（找不到數(shù)據(jù)），就等于0，否則

11、，就等于VLOOKUP函數(shù)運行的值。ANDAND(logical1,logical2, .)Logical1, logical2, . 表示待檢測的 1 到 30 個條件值，各條件值可為 TRUE 或 FALSE。=and(a30,b30,并且b30,b40,或者b4=90,A,IF(K4=80,B,IF(K4=70,C,D)嵌套了兩個IF函數(shù)分數(shù)段等級分數(shù)90分A80分分數(shù)90分B70分分數(shù)80分C分數(shù)70分D第18頁，共45頁。函數(shù)名語法函數(shù)結(jié)構(gòu)函數(shù)作用SUMIFSUMIF(range,criteria,sum_range)其中：Range為用于條件判斷的單元格區(qū)域。Criteria 為確

12、定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達式或文本。例如，條件可以表示為 32、32、32 或 apples。Sum_range 是需要求和的實際單元格。=sumif(B:B,H1,E:E) 當B列滿足了條件（HI），對應的E列的單元格的數(shù)值之和GOROW 求余數(shù)的函數(shù)，當?shù)诙€參數(shù)為2時，常用于判斷奇偶數(shù)，第二個參數(shù)為2時，運行結(jié)果為0是偶數(shù)，1是奇數(shù)=ROW(5，2)求5除以2的余數(shù)是多少，本例的運行結(jié)果是1常用函數(shù)之求和類函數(shù)第19頁，共45頁。應用實例從身份證號碼中提取性別 （身份證倒數(shù)第2位若為偶數(shù)為女性，奇數(shù)為男性） 在C2單元格中輸入=IF(MOD(MID(B2,17

13、,1),2)=1,男,女)公式分析： MID(B2,17,1)是從身份證第17位起取1位字符；在本例即為數(shù)字5，MOD函數(shù)就是取余數(shù)的函數(shù)，在本例中就是MOD(5，2),5除以2的余數(shù)，最外面是一個IF函數(shù)，當這個余數(shù)為1時，即為奇數(shù)，奇數(shù)就是男性，否則為女性。 第20頁，共45頁。函數(shù)名語法函數(shù)結(jié)構(gòu)函數(shù)作用1、NOW=now()顯示當前的日期+時間2、TODAY=today()顯示今天的日期3、WEEKDAY=weekday(b1,2)判斷b1為星期幾4、DAY=day(b1)顯示b1的號數(shù)5、MONTH=month(b1)顯示b1的月數(shù)6、YEAR=year(b1)顯示b1的年數(shù)常用函數(shù)之

14、日期類函數(shù)第21頁，共45頁。函數(shù)名以單元格b1為2013-4-5為例的運行結(jié)果函數(shù)結(jié)構(gòu)函數(shù)作用WEEKDAY6=weekday(b1,2)判斷b1為星期幾DAY5=day(b1)顯示b1的號數(shù)MONTH4=month(b1)顯示b1的月數(shù)YEAR2013=year(b1)顯示b1的年數(shù)常用函數(shù)之日期類函數(shù)第22頁，共45頁。常用函數(shù)之日期類函數(shù)DATEDIF函數(shù)常用于計算兩日期的時間差=DATEDIF(較早的日期，較晚的日期，第三個參數(shù)）第三個參數(shù)：“Y” -兩日期間共多少年?！癕” -兩日期間共多少個月?！癉” -兩日期間共多少天?！癕D” 是兩日期中天數(shù)的差。忽略日期中的月和年?！癥M”

15、 是兩日期中月數(shù)的差。忽略日期中的年。“YD” 是兩日期中天數(shù)的差。忽略日期中的年。第23頁，共45頁。常用函數(shù)之日期類函數(shù)舉例:算兩個日期間差幾年、幾月、幾日開始時間是2012-2-21 結(jié)束時間是2014-2-19C2=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”)&“個月第24頁，共45頁。常

16、用函數(shù)之日期類函數(shù)應用實例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(),y)第25頁，共45頁。應用實例培訓協(xié)議服務期限完成提醒思路：過期了可以顯示“協(xié)議期限完成” 沒過期的，顯示“沒過期”=IFERROR(IF(DATEDIF(B2,TODAY(),d)=0, 協(xié)議期限完成, ),沒過期)分析：DA

17、TEDIF(B2,TODAY(),“d”)可以求出今天到B2的日期共多少天，如果“今天”在B2之前，就會產(chǎn)生錯誤值，如果“今天”在B2之后，這個函數(shù)就會算出B2距離“今天”有多少天。當這個天數(shù)0時，就會執(zhí)行IF函數(shù)，顯示“協(xié)議期限完成”，當DATEDIF運行是一個錯誤值時，這時候就執(zhí)行IFERROR函數(shù)，顯示“沒過期”。如果我想到期前一個月就提醒呢？第26頁，共45頁。思路：=IFERROR(IF(DATEDIF(TODAY(),B2,D)=50)20歲年齡40歲的人數(shù)：=COUNTIF(F:F,=20)-COUNTIF(F:F,=40)（注意：如果要統(tǒng)計的數(shù)據(jù)是有一個綠色的左角的， 要先選擇

18、要統(tǒng)計的數(shù)據(jù)，點擊 符號，選擇 “轉(zhuǎn)換為數(shù)字”，不然是統(tǒng)計不了個數(shù)的）GO第33頁，共45頁。認識數(shù)據(jù)透視表第34頁，共45頁。認識數(shù)據(jù)透視表數(shù)據(jù)透視表是一種交互，交叉制作的報表，一組具有多個字段的數(shù)據(jù)進行多立體的分析匯總，用于對多種來源的數(shù)據(jù)進行匯總和分析，從而可以快速合并和比較大量數(shù)據(jù)。當數(shù)據(jù)規(guī)模比較大時，這種分析的意義就顯得尤為突出。說人話一種快速分析數(shù)據(jù)的工具第35頁，共45頁。前提這些數(shù)據(jù)使用數(shù)段表進行管理第36頁，共45頁。統(tǒng)計9月入職的招聘來源分布統(tǒng)計離職原因分布個數(shù)統(tǒng)計各部門平均工資數(shù)據(jù)透視表可以快速解決這些事情統(tǒng)計各部門學歷分布情況認識數(shù)據(jù)透視表第37頁，共45頁。統(tǒng)計9月入

19、職的招聘來源分布步驟1：打開工作表-選擇數(shù)據(jù)來源區(qū)域（A1：i79）-插入-數(shù)據(jù)透視表-確定統(tǒng)計9月入職的招聘來源分布第38頁，共45頁。步驟2：把招聘來源拖到下面的“行標簽”框，把序號拖到下面的“數(shù)值”框，就能快速生成下圖。第39頁，共45頁。統(tǒng)計離職原因分布個數(shù)同理，可根據(jù)前面介紹的步驟在辭工名單的表格中快速做出離職原因分布的表格GO拖“離職原因”到“行標簽”框，拖“序號”到“數(shù)值”框注意：要留意“數(shù)值”框，是求和，還是計數(shù)，還是其他，左圖的默認是“求和”，而我們是想“計數(shù)”。通過雙擊B3，出來“值字段對話框”，選擇“計數(shù)”第40頁，共45頁。統(tǒng)計出不同部門工資總和、平均工資 行標簽-部門

20、 數(shù)值-求和項：工資，平均值項(值字段設(shè)置可找到）：工資這是原數(shù)據(jù) 這是生成的數(shù)據(jù)透視表第41頁，共45頁。統(tǒng)計各部門學歷分布情況行標簽-部門，列標簽-最高學歷 數(shù)值-計數(shù)項：最高學歷想想：如果在列標簽先拖入性別，再拖入最高學歷，數(shù)據(jù)透視表會怎樣第42頁，共45頁。Thank You !第43頁，共45頁。每一次的加油，每一次的努力都是為了下一次更好的自己。8月-228月-22Thursday, August 4, 2022天生我材必有用，千金散盡還復來。05:19:1305:19:1305:198/4/2022 5:19:13 AM安全象只弓，不拉它就松，要想保安全，常把弓弦繃。8月-220

21、5:19:1305:19Aug-2204-Aug-22得道多助失道寡助，掌控人心方位上。05:19:1305:19:1305:19Thursday, August 4, 2022安全在于心細，事故出在麻痹。8月-228月-2205:19:1305:19:13August 4, 2022加強自身建設(shè)，增強個人的休養(yǎng)。2022年8月4日5:19 上午8月-228月-22擴展市場，開發(fā)未來，實現(xiàn)現(xiàn)在。04 八月 20225:19:13 上午05:19:138月-22做專業(yè)的企業(yè)，做專業(yè)的事情，讓自己專業(yè)起來。八月 225:19 上午8月-2205:19August 4, 2022時間是人類發(fā)展的空間

22、。2022/8/4 5:19:1305:19:1304 August 2022科學，你是國力的靈魂；同時又是社會發(fā)展的標志。5:19:13 上午5:19 上午05:19:138月-22每天都是美好的一天，新的一天開啟。8月-228月-2205:1905:19:1305:19:13Aug-22人生不是自發(fā)的自我發(fā)展，而是一長串機緣。事件和決定，這些機緣、事件和決定在它們實現(xiàn)的當時是取決于我們的意志的。2022/8/4 5:19:13Thursday, August 4, 2022感情上的親密，發(fā)展友誼；錢財上的親密，破壞友誼。8月-222022/8/4 5:19:138月-22謝謝大家！第44頁，共45頁。每一次的加油，每一次的努力都是為了