Excel在人力資源管理的應(yīng)用-極其精美實(shí)用

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

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

3、醒的功能如：突出不滿勤的人GO 突出年齡大于30的人等如何取消條件格式？LOGO認(rèn)識(shí)Excel函數(shù)LOGO2-1.EXCEL函數(shù)的結(jié)構(gòu)vExcel 函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式特殊公式。Excel 函數(shù)結(jié)構(gòu):也有一些函數(shù)也有一些函數(shù)是沒(méi)有參數(shù)的是沒(méi)有參數(shù)的,如如NOW()左右括號(hào)成左右括號(hào)成對(duì)出現(xiàn)對(duì)出現(xiàn)單一結(jié)構(gòu)嵌套結(jié)構(gòu)參數(shù)與參數(shù)之間參數(shù)與參數(shù)之間使用使用半角逗號(hào)半角逗號(hào)進(jìn)進(jìn)行分隔行分隔LOGO函數(shù)名語(yǔ)法函數(shù)結(jié)構(gòu)函數(shù)作用例子運(yùn)行結(jié)果1、Left1、LEFT(text,num_chars)其中：其中：Text是包含要提取字符的文本字符是包含要提取字符的文本字符串。串。N

4、um_chars 指定要由指定要由 LEFT 所提取所提取的字符數(shù)。的字符數(shù)。=LEFT(A1,2)從從A1A1最左邊最左邊向右取兩位向右取兩位2、Right2、RIGHT(text,num_chars) 其中：其中：Text是包含要提取字符的文本字符是包含要提取字符的文本字符串。串。Num_chars指定希望指定希望 RIGHT 提取提取的字符數(shù)。的字符數(shù)。=RIGHT(A1,2)從從A1A1最右邊最右邊向左取兩位向左取兩位3、Mid3、MID(text,start_num,num_chars)其中：其中：Text是包含要提取字符的文本字符是包含要提取字符的文本字符串。串。Start_num

5、 是文本中要提取的第一是文本中要提取的第一個(gè)字符的位置。個(gè)字符的位置。Num_chars 指定希望指定希望 MID 從文本中返回字符的個(gè)數(shù)。從文本中返回字符的個(gè)數(shù)。=MID(A1,2,3)從從A1A1左邊第左邊第2 2位開(kāi)始取位開(kāi)始取3 3位位4、&（連接符號(hào)）（連接符號(hào)）XX&XX=A2&B2將將A2A2和和B2B2內(nèi)內(nèi)容合并容合并在一起在一起LOGOv應(yīng)用實(shí)例v 從身份證號(hào)碼提取出生日期從身份證號(hào)碼提取出生日期 在C2單元格中輸入 =MID(B2,7,4)&-&MID(B2,11,2)&-&MID(B2,13,2) 公式解析：MID(

6、B2,7,4)表示提取B2單元格中從第7位開(kāi)始連續(xù)4位的字符，即出生年；MID(B2,11,2)表示提取B2單元格中從第11位開(kāi)始連續(xù)2位的字符，即出生月； MID(B2,11,2)表示提取B2單元格中從第13位開(kāi)始連續(xù)2位的字符，即出生日； 在公式中加入&可以用來(lái)連接兩個(gè)文本字符串。LOGO函數(shù)名函數(shù)名語(yǔ)法語(yǔ)法函數(shù)結(jié)構(gòu)函數(shù)結(jié)構(gòu)函數(shù)作用函數(shù)作用FIND=FIND(龍龍,C6)在在c6c6中查找龍，中查找龍，結(jié)果顯示龍?jiān)诮Y(jié)果顯示龍?jiān)赾6c6中的位數(shù)，中的位數(shù)，查不到顯示查不到顯示#VALUE!#VALUE!LOGOv函數(shù)函數(shù)VOOKUP=VLOOKUP(lookup_value,tabl

7、e_array,col_index_num,range_lookup)(以從8月工資表導(dǎo)入考勤到8月考核表為例） GO參數(shù)參數(shù)簡(jiǎn)單說(shuō)明簡(jiǎn)單說(shuō)明輸入數(shù)據(jù)類型輸入數(shù)據(jù)類型lookup_value要查找的值要查找的值數(shù)值、引用或文本字符串?dāng)?shù)值、引用或文本字符串table_array要查找的區(qū)域要查找的區(qū)域數(shù)據(jù)表區(qū)域數(shù)據(jù)表區(qū)域col_index_num返回?cái)?shù)據(jù)在查找區(qū)域的第幾列返回?cái)?shù)據(jù)在查找區(qū)域的第幾列數(shù)數(shù)正整數(shù)正整數(shù)range_lookup精確匹配精確匹配/模糊匹配模糊匹配0或或1LOGOv函數(shù)函數(shù)VOOKUPv選擇區(qū)域里最好不要有合并的單元格（先“取消單元格合并”）v如果單元格里有空格，有可能導(dǎo)不

8、出來(lái) （提示：函數(shù)TRIM（A1)可以去除字符串首部和尾部的空格）v注意重復(fù)的名字v查找區(qū)域首列必須含有查找的內(nèi)容（第一個(gè)參數(shù)）v介紹vookup函數(shù)與IFERROR函數(shù)組合使用 (以從8月工資表導(dǎo)入考勤到8月考核表為例）GO =IFERROR(VLOOKUP(A4,8月工資!B:F,5,0),0) 如果VOOKUP函數(shù)運(yùn)行后找不到對(duì)應(yīng)的數(shù)據(jù)，就會(huì)顯示為#N/A，IFERRORIFERROR的作用就是把這些的作用就是把這些#N/A#N/A都變?yōu)榈诙甲優(yōu)榈? 2個(gè)參數(shù)。個(gè)參數(shù)。LOGO函數(shù)名函數(shù)名語(yǔ)法語(yǔ)法/例子例子函數(shù)結(jié)構(gòu)函數(shù)結(jié)構(gòu)函數(shù)作用函數(shù)作用Value=value(b1)將將b1數(shù)值化數(shù)值化

9、Numberstring=NumberString(7890,1）運(yùn)行結(jié)果：七千八百九十=NumberString(7890,2) 運(yùn)行結(jié)果：柒仟捌佰玖拾=NumberString(7890,3) 運(yùn)行結(jié)果：七八九=numberstring(b1,2)將將b1數(shù)字轉(zhuǎn)為大寫(xiě)數(shù)字轉(zhuǎn)為大寫(xiě)漢字漢字Row=ROW(reference)其中：其中：ReferenceReference 為需為需要得到其行號(hào)的單元要得到其行號(hào)的單元格或單元格區(qū)域。格或單元格區(qū)域。=row(b1)求求b1是第幾行是第幾行（值為值為1)Column= =COLUMN(reference)=column(b1)求求b1是第幾列是

10、第幾列(值值為為2)LOGO函數(shù)名函數(shù)名語(yǔ)法語(yǔ)法函數(shù)結(jié)構(gòu)函數(shù)結(jié)構(gòu)函數(shù)作用函數(shù)作用len=LEN(text)=LEN(text)其中：其中：TextText 是要查找其是要查找其長(zhǎng)度的文本。空格將長(zhǎng)度的文本?？崭駥⒆鳛樽址M(jìn)行計(jì)數(shù)。作為字符進(jìn)行計(jì)數(shù)。=len(C2)計(jì)算計(jì)算C2的字符長(zhǎng)的字符長(zhǎng)度度rankRANK(number,ref,order)RANK(number,ref,order)NumberNumber為需要找到排位為需要找到排位的數(shù)字。的數(shù)字。RefRef 為數(shù)字列表數(shù)組或?yàn)閿?shù)字列表數(shù)組或?qū)?shù)字列表的引用。對(duì)數(shù)字列表的引用。Ref Ref 中的非數(shù)值型參中的非數(shù)值型參數(shù)將被忽略。數(shù)

11、將被忽略。OrderOrder為一數(shù)字，指明排為一數(shù)字，指明排位的方式。位的方式。=rank(b1,b1:b10,0)=rank(b1,b1:b10,1)計(jì)算計(jì)算b1在在b1:b10區(qū)域中從大到區(qū)域中從大到小排第幾名；小排第幾名；計(jì)算計(jì)算b1在在b1:b10區(qū)域中從小到區(qū)域中從小到大排第幾名。大排第幾名。LOGOv應(yīng)用實(shí)例 在C1輸入=A1&+&B1v 如何根據(jù)如何根據(jù)A1得出得出B1和和C1？在B1輸入=LEFT(A1,FIND(+,A1)-1)分析：思路是從左邊開(kāi)始，截取分析：思路是從左邊開(kāi)始，截取“+”號(hào)前的位數(shù)。號(hào)前的位數(shù)。在C1輸入=RIGHT(A1,LEN(A1)-

12、FIND(+,A1)分析：思路是截取分析：思路是截取“+”號(hào)后的位數(shù)。號(hào)后的位數(shù)。如何根據(jù)如何根據(jù)A1、B1得得出出C1？LOGO函數(shù)名函數(shù)名語(yǔ)法語(yǔ)法函數(shù)結(jié)構(gòu)函數(shù)結(jié)構(gòu)函數(shù)作用函數(shù)作用IFIF(logical_test,value_if_true,value_if_false)=if(a20,1,0)當(dāng)當(dāng)a20時(shí)，顯示時(shí)，顯示1，否則，否則顯示顯示0IFREEOR第一個(gè)參數(shù)一般為一個(gè)函數(shù)，當(dāng)這個(gè)第一個(gè)參數(shù)一般為一個(gè)函數(shù)，當(dāng)這個(gè)函數(shù)運(yùn)行是個(gè)錯(cuò)誤值時(shí)（函數(shù)運(yùn)行是個(gè)錯(cuò)誤值時(shí)（#N/A#N/A #DIV/0!#N/A#VALUE!#NAME#NULL!#NUM!#REF!），），運(yùn)行結(jié)果等于第二個(gè)參數(shù)運(yùn)

13、行結(jié)果等于第二個(gè)參數(shù)=iferror(vookup(A1,A:B,2,0),0) 如果如果VLOOKUP函數(shù)運(yùn)函數(shù)運(yùn)行值是個(gè)錯(cuò)誤值的話行值是個(gè)錯(cuò)誤值的話（找不到數(shù)據(jù)），就等（找不到數(shù)據(jù)），就等于于0，否則，就等于，否則，就等于VLOOKUP函數(shù)運(yùn)行的函數(shù)運(yùn)行的值。值。ANDAND(logical1,logical2, .)Logical1, logical2, . 表示待檢測(cè)的表示待檢測(cè)的 1 到到 30 個(gè)條件值，各條件值可為個(gè)條件值，各條件值可為 TRUE 或或 FALSE。=and(a30,b30,并且并且b30,b40,或者或者b4=90,A,IF(K4=80,B,IF(K4=70,C

14、,D) 嵌套了兩個(gè)IF函數(shù)分?jǐn)?shù)段分?jǐn)?shù)段等級(jí)等級(jí)分?jǐn)?shù)90分A80分分?jǐn)?shù)90分B70分分?jǐn)?shù)80分C分?jǐn)?shù)70分DLOGO函數(shù)名函數(shù)名語(yǔ)法語(yǔ)法函數(shù)結(jié)構(gòu)函數(shù)結(jié)構(gòu)函數(shù)作用函數(shù)作用SUMIFSUMIF(range,criteria,sum_range)其中：Range 為用于條件判斷的單元格區(qū)域。Criteria 為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為 32、32、32 或 apples。Sum_range 是需要求和的實(shí)際單元格。=sumif(B:B,H1,E:E) 當(dāng)當(dāng)B列滿足了條列滿足了條件（件（HI），對(duì)），對(duì)應(yīng)的應(yīng)的E列的單元列的單元格的數(shù)值之和格

15、的數(shù)值之和GOROW 求余數(shù)的函數(shù)，當(dāng)?shù)诙€(gè)參數(shù)為求余數(shù)的函數(shù)，當(dāng)?shù)诙€(gè)參數(shù)為2時(shí)，時(shí)，常用于判斷奇偶數(shù)，第二個(gè)參數(shù)為常用于判斷奇偶數(shù)，第二個(gè)參數(shù)為2時(shí)，運(yùn)行結(jié)果為時(shí)，運(yùn)行結(jié)果為0是偶數(shù)，是偶數(shù)，1是奇數(shù)是奇數(shù)=ROW(5，2)求求5除以除以2的余數(shù)是的余數(shù)是多少，本例的多少，本例的運(yùn)行結(jié)果是運(yùn)行結(jié)果是1LOGOv應(yīng)用實(shí)例v 從身份證號(hào)碼中提取性別從身份證號(hào)碼中提取性別（身份證倒數(shù)第2位若為偶數(shù)為女性，奇數(shù)為男性） 在C2單元格中輸入=IF(MOD(MID(B2,17,1),2)=1,男,女)公式分析： MID(B2,17,1)是從身份證第17位起取1位字符；在本例即為數(shù)字5，MOD函數(shù)就是取

16、余數(shù)的函數(shù)，在本例中就是MOD(5，2),5除以2的余數(shù)，最外面是一個(gè)IF函數(shù)，當(dāng)這個(gè)余數(shù)為1時(shí)，即為奇數(shù)，奇數(shù)就是男性，否則為女性。 LOGO函數(shù)名函數(shù)名語(yǔ)法語(yǔ)法函數(shù)結(jié)構(gòu)函數(shù)結(jié)構(gòu)函數(shù)作用函數(shù)作用1、NOW=now()顯示當(dāng)前的日期顯示當(dāng)前的日期+ +時(shí)間時(shí)間2、TODAY=today()顯示今天的日期顯示今天的日期3、WEEKDAY=weekday(b1,2)判斷判斷b1b1為星期幾為星期幾4、DAY=day(b1)顯示顯示b1b1的號(hào)數(shù)的號(hào)數(shù)5、MONTH=month(b1)顯示顯示b1b1的月數(shù)的月數(shù)6、YEAR=year(b1)顯示顯示b1b1的年數(shù)的年數(shù)LOGO函數(shù)名函數(shù)名以單元格以

17、單元格b1為為2013-4-5為例為例的運(yùn)行結(jié)果的運(yùn)行結(jié)果函數(shù)結(jié)構(gòu)函數(shù)結(jié)構(gòu)函數(shù)作用函數(shù)作用WEEKDAY6=weekday(b1,2)判斷判斷b1b1為星期幾為星期幾DAY5=day(b1)顯示顯示b1b1的號(hào)數(shù)的號(hào)數(shù)MONTH4=month(b1)顯示顯示b1b1的月數(shù)的月數(shù)YEAR2013=year(b1)顯示顯示b1b1的年數(shù)的年數(shù)LOGODATEDIF函數(shù)v 常用于計(jì)算兩日期的時(shí)間差=DATEDIF(較早的日期，較晚的日期，第三個(gè)參數(shù)）第三個(gè)參數(shù)：v “Y” -兩日期間共多少年。v “M” -兩日期間共多少個(gè)月。v “D” -兩日期間共多少天。v “MD” 是兩日期中天數(shù)的差。忽略日期

18、中的月和年。v “YM” 是兩日期中月數(shù)的差。忽略日期中的年。v “YD” 是兩日期中天數(shù)的差。忽略日期中的年。LOGO舉例:算兩個(gè)日期間差幾年、幾月、幾日開(kāi)始時(shí)間是2012-2-21 結(jié)束時(shí)間是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&個(gè)月I2=DATEDIF(A2,B2,“y”)&“年”&DATEDIF(A2,B2,“y

19、m”)&“個(gè)月LOGOv應(yīng)用實(shí)例3v 從出生日得出年齡從出生日得出年齡 在D2單元格中輸入=DATEDIF(C2,NOW(),“y”) 因?yàn)閺纳矸葑C提取出生日的C2 =MID(B2,7,4)&-&MID(B2,11,2)&-&MID(B2,13,2)v 所以若直接從身份證得出年齡直接從身份證得出年齡D2= DATEDIF(MID(B2,7,4)&-&MID(B2,11,2)&-&MID(B2,13,2),NOW(),y)LOGOv應(yīng)用實(shí)例v培訓(xùn)協(xié)議服務(wù)期限完成提醒培訓(xùn)協(xié)議服務(wù)期限完成提醒思路：過(guò)期了可以顯示“協(xié)議期限完成”

20、 沒(méi)過(guò)期的，顯示“沒(méi)過(guò)期”=IFERROR(IF(DATEDIF(B2,TODAY(),d)=0, 協(xié)議期限完成, ),沒(méi)過(guò)期)分析：DATEDIF(B2,TODAY(),“d”)可以求出今天到B2的日期共多少天，如果“今天”在B2之前，就會(huì)產(chǎn)生錯(cuò)誤值，如果“今天”在B2之后，這個(gè)函數(shù)就會(huì)算出B2距離“今天”有多少天。當(dāng)這個(gè)天數(shù)0時(shí)，就會(huì)執(zhí)行IF函數(shù)，顯示“協(xié)議期限完成”，當(dāng)DATEDIF運(yùn)行是一個(gè)錯(cuò)誤值時(shí)，這時(shí)候就執(zhí)行IFERROR函數(shù)，顯示“沒(méi)過(guò)期”。如果我想到期如果我想到期前一個(gè)月就提前一個(gè)月就提醒呢？醒呢？LOGO思路：=IFERROR(IF(DATEDIF(TODAY(),B2,D)=50)20歲年齡40歲的人數(shù)：=COUNTIF(F:F,=20)-COUNTIF(F:F,=40)（注意：如果要統(tǒng)計(jì)的數(shù)據(jù)是有一個(gè)綠色的左角的，（注意：如果要統(tǒng)計(jì)的數(shù)據(jù)是有一個(gè)綠色的左角的， 要先選擇要統(tǒng)計(jì)的數(shù)據(jù)，點(diǎn)擊要先選擇要統(tǒng)計(jì)的數(shù)據(jù)，點(diǎn)擊 符號(hào)，選擇符號(hào)，選擇 “轉(zhuǎn)換為數(shù)字轉(zhuǎn)換為數(shù)字”，不然是統(tǒng)計(jì)不了個(gè)數(shù)的），不然是統(tǒng)計(jì)不了個(gè)數(shù)的）GOLOGO認(rèn)識(shí)數(shù)據(jù)透視表LOGO認(rèn)識(shí)數(shù)據(jù)透視表認(rèn)識(shí)數(shù)據(jù)透